You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@flink.apache.org by lz...@apache.org on 2023/03/01 09:34:41 UTC

[flink-table-store] branch master updated: [FLINK-31128] Add Create Table As for flink table store

This is an automated email from the ASF dual-hosted git repository.

lzljs3620320 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/flink-table-store.git


The following commit(s) were added to refs/heads/master by this push:
     new 4adf18df [FLINK-31128] Add Create Table As for flink table store
4adf18df is described below

commit 4adf18df258bc3c55026053d94cd3e4a6c281896
Author: JunZhang <zh...@126.com>
AuthorDate: Wed Mar 1 17:34:34 2023 +0800

    [FLINK-31128] Add Create Table As for flink table store
    
    This closes #547
---
 docs/content/docs/how-to/creating-tables.md        | 122 ++++++++++++++++++
 .../shortcodes/generated/core_configuration.html   |  12 ++
 .../org/apache/flink/table/store/CoreOptions.java  |  16 +++
 .../table/store/file/schema/SchemaManager.java     |  36 ++++++
 .../flink-table-store-flink-common/pom.xml         |  29 +++++
 .../table/store/connector/CatalogTableITCase.java  | 139 ++++++++++++++++++++
 .../flink/table/store/hive/HiveCatalogITCase.java  | 140 +++++++++++++++++++++
 .../flink/table/store/spark/SparkReadITCase.java   | 134 ++++++++++++++++++++
 8 files changed, 628 insertions(+)

diff --git a/docs/content/docs/how-to/creating-tables.md b/docs/content/docs/how-to/creating-tables.md
index c7afbcff..416f0876 100644
--- a/docs/content/docs/how-to/creating-tables.md
+++ b/docs/content/docs/how-to/creating-tables.md
@@ -114,6 +114,128 @@ Partition keys must be a subset of primary keys if primary keys are defined.
 By configuring [partition.expiration-time]({{< ref "docs/maintenance/manage-partition" >}}), expired partitions can be automatically deleted.
 {{< /hint >}}
 
+## Create Table As
+
+Table can be created and populated by the results of a query, for example, we have a sql like this: `CREATE TABLE table_b AS SELECT id, name FORM table_a`,
+The resulting table `table_b` will be equivalent to create the table and insert the data with the following statement:
+`CREATE TABLE table_b (id INT, name STRING); INSERT INTO table_b SELECT id, name FROM table_a;`
+
+We can specify the primary key or partition when use `CREATE TABLE AS SELECT`, for syntax, please refer to the following sql.
+
+{{< tabs "create-table-as" >}}
+
+{{< tab "Flink" >}}
+
+```sql
+
+/* For streaming mode, you need to enable the checkpoint. */
+
+CREATE TABLE MyTable (
+    user_id BIGINT,
+    item_id BIGINT
+);
+CREATE TABLE MyTableAs AS SELECT * FROM MyTable;
+
+/* partitioned table */
+CREATE TABLE MyTablePartition (
+     user_id BIGINT,
+     item_id BIGINT,
+     behavior STRING,
+     dt STRING,
+     hh STRING
+) PARTITIONED BY (dt, hh);
+CREATE TABLE MyTablePartitionAs WITH ('partition' = 'dt') AS SELECT * FROM MyTablePartition;
+    
+/* change options */
+CREATE TABLE MyTableOptions (
+       user_id BIGINT,
+       item_id BIGINT
+) WITH ('file.format' = 'orc');
+CREATE TABLE MyTableOptionsAs WITH ('file.format' = 'parquet') AS SELECT * FROM MyTableOptions;
+
+/* primary key */
+CREATE TABLE MyTablePk (
+      user_id BIGINT,
+      item_id BIGINT,
+      behavior STRING,
+      dt STRING,
+      hh STRING,
+      PRIMARY KEY (dt, hh, user_id) NOT ENFORCED
+) ;
+CREATE TABLE MyTablePkAs WITH ('primary-key' = 'dt,hh') AS SELECT * FROM MyTablePk;
+
+
+/* primary key + partition */
+CREATE TABLE MyTableAll (
+      user_id BIGINT,
+      item_id BIGINT,
+      behavior STRING,
+      dt STRING,
+      hh STRING,
+      PRIMARY KEY (dt, hh, user_id) NOT ENFORCED 
+) PARTITIONED BY (dt, hh);
+CREATE TABLE MyTableAllAs WITH ('primary-key' = 'dt,hh', 'partition' = 'dt') AS SELECT * FROM MyTableAll;
+```
+
+{{< /tab >}}
+
+{{< tab "Spark3" >}}
+
+```sql
+CREATE TABLE MyTable (
+     user_id BIGINT,
+     item_id BIGINT
+);
+CREATE TABLE MyTableAs AS SELECT * FROM MyTable;
+
+/* partitioned table*/
+CREATE TABLE MyTablePartition (
+      user_id BIGINT,
+      item_id BIGINT,
+      behavior STRING,
+      dt STRING,
+      hh STRING
+) PARTITIONED BY (dt, hh);
+CREATE TABLE MyTablePartitionAs PARTITIONED BY (dt) AS SELECT * FROM MyTablePartition;
+
+/* change TBLPROPERTIES */
+CREATE TABLE MyTableOptions (
+       user_id BIGINT,
+       item_id BIGINT
+) TBLPROPERTIES ('file.format' = 'orc');
+CREATE TABLE MyTableOptionsAs TBLPROPERTIES ('file.format' = 'parquet') AS SELECT * FROM MyTableOptions;
+
+
+/* primary key */
+CREATE TABLE MyTablePk (
+     user_id BIGINT,
+     item_id BIGINT,
+     behavior STRING,
+     dt STRING,
+     hh STRING
+) TBLPROPERTIES (
+    'primary-key' = 'dt,hh,user_id'
+);
+CREATE TABLE MyTablePkAs TBLPROPERTIES ('primary-key' = 'dt') AS SELECT * FROM MyTablePk;
+
+/* primary key + partition */
+CREATE TABLE MyTableAll (
+    user_id BIGINT,
+    item_id BIGINT,
+    behavior STRING,
+    dt STRING,
+    hh STRING
+) PARTITIONED BY (dt, hh) TBLPROPERTIES (
+    'primary-key' = 'dt,hh,user_id'
+);
+CREATE TABLE MyTableAllAs PARTITIONED BY (dt) TBLPROPERTIES ('primary-key' = 'dt,hh') AS SELECT * FROM MyTableAll;
+```
+
+{{< /tab >}}
+
+{{< /tabs >}}
+
+
 ### Create Table Like
 
 {{< tabs "create-table-like" >}}
diff --git a/docs/layouts/shortcodes/generated/core_configuration.html b/docs/layouts/shortcodes/generated/core_configuration.html
index 32568bf3..442c68b7 100644
--- a/docs/layouts/shortcodes/generated/core_configuration.html
+++ b/docs/layouts/shortcodes/generated/core_configuration.html
@@ -326,5 +326,17 @@
             <td>Boolean</td>
             <td>If set to true, compactions and snapshot expiration will be skipped. This option is used along with dedicated compact jobs.</td>
         </tr>
+        <tr>
+            <td><h5>primary-key</h5></td>
+            <td style="word-wrap: break-word;">(none)</td>
+            <td>String</td>
+            <td>Define primary key by table options, cannot define primary key on DDL and table options at the same time.</td>
+        </tr>
+        <tr>
+            <td><h5>partition</h5></td>
+            <td style="word-wrap: break-word;">(none)</td>
+            <td>String</td>
+            <td>Define partition by table options, cannot define partition on DDL and table options at the same time.</td>
+        </tr>
     </tbody>
 </table>
diff --git a/flink-table-store-core/src/main/java/org/apache/flink/table/store/CoreOptions.java b/flink-table-store-core/src/main/java/org/apache/flink/table/store/CoreOptions.java
index b5aa4a69..d0617a54 100644
--- a/flink-table-store-core/src/main/java/org/apache/flink/table/store/CoreOptions.java
+++ b/flink-table-store-core/src/main/java/org/apache/flink/table/store/CoreOptions.java
@@ -490,6 +490,22 @@ public class CoreOptions implements Serializable {
                                                     + " avoid the disorder reading for partitions, you can open this option.")
                                     .build());
 
+    @Immutable
+    public static final ConfigOption<String> PRIMARY_KEY =
+            key("primary-key")
+                    .stringType()
+                    .noDefaultValue()
+                    .withDescription(
+                            "Define primary key by table options, cannot define primary key on DDL and table options at the same time.");
+
+    @Immutable
+    public static final ConfigOption<String> PARTITION =
+            key("partition")
+                    .stringType()
+                    .noDefaultValue()
+                    .withDescription(
+                            "Define partition by table options, cannot define partition on DDL and table options at the same time.");
+
     private final Options options;
 
     public CoreOptions(Map<String, String> options) {
diff --git a/flink-table-store-core/src/main/java/org/apache/flink/table/store/file/schema/SchemaManager.java b/flink-table-store-core/src/main/java/org/apache/flink/table/store/file/schema/SchemaManager.java
index 9fa8c031..db3852ce 100644
--- a/flink-table-store-core/src/main/java/org/apache/flink/table/store/file/schema/SchemaManager.java
+++ b/flink-table-store-core/src/main/java/org/apache/flink/table/store/file/schema/SchemaManager.java
@@ -123,6 +123,42 @@ public class SchemaManager implements Serializable {
             Map<String, String> options = schema.options();
             int highestFieldId = RowType.currentHighestFieldId(fields);
 
+            List<String> columnNames =
+                    schema.fields().stream().map(DataField::name).collect(Collectors.toList());
+            if (options.containsKey(CoreOptions.PRIMARY_KEY.key())) {
+                if (!primaryKeys.isEmpty()) {
+                    throw new RuntimeException(
+                            "Cannot define primary key on DDL and table options at the same time.");
+                }
+                String pk = options.get(CoreOptions.PRIMARY_KEY.key());
+                primaryKeys = Arrays.asList(pk.split(","));
+                boolean exists = primaryKeys.stream().allMatch(columnNames::contains);
+                if (!exists) {
+                    throw new RuntimeException(
+                            String.format(
+                                    "Primary key column '%s' is not defined in the schema.",
+                                    primaryKeys));
+                }
+                options.remove(CoreOptions.PRIMARY_KEY.key());
+            }
+
+            if (options.containsKey(CoreOptions.PARTITION.key())) {
+                if (!partitionKeys.isEmpty()) {
+                    throw new RuntimeException(
+                            "Cannot define partition on DDL and table options at the same time.");
+                }
+                String partitions = options.get(CoreOptions.PARTITION.key());
+                partitionKeys = Arrays.asList(partitions.split(","));
+                boolean exists = partitionKeys.stream().allMatch(columnNames::contains);
+                if (!exists) {
+                    throw new RuntimeException(
+                            String.format(
+                                    "Partition column '%s' is not defined in the schema.",
+                                    partitionKeys));
+                }
+                options.remove(CoreOptions.PARTITION.key());
+            }
+
             TableSchema newSchema =
                     new TableSchema(
                             0,
diff --git a/flink-table-store-flink/flink-table-store-flink-common/pom.xml b/flink-table-store-flink/flink-table-store-flink-common/pom.xml
index b8eff4d7..b911fbe6 100644
--- a/flink-table-store-flink/flink-table-store-flink-common/pom.xml
+++ b/flink-table-store-flink/flink-table-store-flink-common/pom.xml
@@ -139,6 +139,35 @@ under the License.
             <scope>provided</scope>
         </dependency>
 
+        <dependency>
+            <groupId>org.apache.hadoop</groupId>
+            <artifactId>hadoop-mapreduce-client-core</artifactId>
+            <version>${hadoop.version}</version>
+            <scope>test</scope>
+            <exclusions>
+                <exclusion>
+                    <groupId>org.apache.curator</groupId>
+                    <artifactId>curator-test</artifactId>
+                </exclusion>
+                <exclusion>
+                    <groupId>org.apache.hadoop</groupId>
+                    <artifactId>hadoop-yarn-common</artifactId>
+                </exclusion>
+                <exclusion>
+                    <groupId>org.apache.avro</groupId>
+                    <artifactId>avro</artifactId>
+                </exclusion>
+                <exclusion>
+                    <groupId>org.codehaus.jackson</groupId>
+                    <artifactId>jackson-core-asl</artifactId>
+                </exclusion>
+                <exclusion>
+                    <groupId>org.slf4j</groupId>
+                    <artifactId>slf4j-log4j12</artifactId>
+                </exclusion>
+            </exclusions>
+        </dependency>
+
         <dependency>
             <groupId>org.apache.flink</groupId>
             <artifactId>flink-streaming-java</artifactId>
diff --git a/flink-table-store-flink/flink-table-store-flink-common/src/test/java/org/apache/flink/table/store/connector/CatalogTableITCase.java b/flink-table-store-flink/flink-table-store-flink-common/src/test/java/org/apache/flink/table/store/connector/CatalogTableITCase.java
index a4c378cf..156ba982 100644
--- a/flink-table-store-flink/flink-table-store-flink-common/src/test/java/org/apache/flink/table/store/connector/CatalogTableITCase.java
+++ b/flink-table-store-flink/flink-table-store-flink-common/src/test/java/org/apache/flink/table/store/connector/CatalogTableITCase.java
@@ -152,6 +152,145 @@ public class CatalogTableITCase extends CatalogITCaseBase {
                 .isEqualTo("[+I[0, [{\"id\":0,\"name\":\"a\",\"type\":\"INT\"}], [], [], {}, ]]");
     }
 
+    @Test
+    public void testCreateTableAs() throws Exception {
+        sql("CREATE TABLE t (a INT)");
+        sql("INSERT INTO t VALUES(1),(2)");
+        sql("CREATE TABLE t1 AS SELECT * FROM t");
+        List<Row> result = sql("SELECT * FROM t1$schemas s");
+        assertThat(result.toString())
+                .isEqualTo("[+I[0, [{\"id\":0,\"name\":\"a\",\"type\":\"INT\"}], [], [], {}, ]]");
+        List<Row> data = sql("SELECT * FROM t1");
+        assertThat(data).containsExactlyInAnyOrder(Row.of(1), Row.of(2));
+
+        // partition table
+        sql(
+                "CREATE TABLE t_p (\n"
+                        + "    user_id BIGINT,\n"
+                        + "    item_id BIGINT,\n"
+                        + "    behavior STRING,\n"
+                        + "    dt STRING,\n"
+                        + "    hh STRING\n"
+                        + ") PARTITIONED BY (dt, hh)");
+        sql("INSERT INTO t_p SELECT 1,2,'a','2023-02-19','12'");
+        sql("CREATE TABLE t1_p WITH ('partition' = 'dt' ) AS SELECT * FROM t_p");
+        List<Row> resultPartition = sql("SELECT * FROM t1_p$schemas s");
+        assertThat(resultPartition.toString())
+                .isEqualTo(
+                        "[+I[0, [{\"id\":0,\"name\":\"user_id\",\"type\":\"BIGINT\"},{\"id\":1,\"name\":\"item_id\",\"type\":\"BIGINT\"},"
+                                + "{\"id\":2,\"name\":\"behavior\",\"type\":\"STRING\"},{\"id\":3,\"name\":\"dt\",\"type\":\"STRING\"},"
+                                + "{\"id\":4,\"name\":\"hh\",\"type\":\"STRING\"}], [\"dt\"], [], {}, ]]");
+        List<Row> dataPartition = sql("SELECT * FROM t1_p");
+        assertThat(dataPartition.toString()).isEqualTo("[+I[1, 2, a, 2023-02-19, 12]]");
+
+        // change options
+        sql("CREATE TABLE t_option (a INT) WITH ('file.format' = 'orc')");
+        sql("INSERT INTO t_option VALUES(1),(2)");
+        sql("CREATE TABLE t1_option WITH ('file.format' = 'parquet') AS SELECT * FROM t_option");
+        List<Row> resultOption = sql("SELECT * FROM t1_option$options");
+        assertThat(resultOption).containsExactly(Row.of("file.format", "parquet"));
+        List<Row> dataOption = sql("SELECT * FROM t1_option");
+        assertThat(dataOption).containsExactlyInAnyOrder(Row.of(1), Row.of(2));
+
+        // primary key
+        sql(
+                "CREATE TABLE t_pk (\n"
+                        + "    user_id BIGINT,\n"
+                        + "    item_id BIGINT,\n"
+                        + "    behavior STRING,\n"
+                        + "    dt STRING,\n"
+                        + "    hh STRING,\n"
+                        + "    PRIMARY KEY (dt, hh, user_id) NOT ENFORCED\n"
+                        + ")");
+        sql("INSERT INTO t_pk VALUES(1,2,'aaa','2020-01-02','09')");
+        sql("CREATE TABLE t_pk_as WITH ('primary-key' = 'dt') AS SELECT * FROM t_pk");
+        List<Row> resultPk = sql("SHOW CREATE TABLE t_pk_as");
+        assertThat(resultPk.toString()).contains("PRIMARY KEY (`dt`)");
+        List<Row> dataPk = sql("SELECT * FROM t_pk_as");
+        assertThat(dataPk.toString()).isEqualTo("[+I[1, 2, aaa, 2020-01-02, 09]]");
+
+        // primary key + partition
+        sql(
+                "CREATE TABLE t_all (\n"
+                        + "    user_id BIGINT,\n"
+                        + "    item_id BIGINT,\n"
+                        + "    behavior STRING,\n"
+                        + "    dt STRING,\n"
+                        + "    hh STRING,\n"
+                        + "    PRIMARY KEY (dt, hh, user_id) NOT ENFORCED\n"
+                        + ") PARTITIONED BY (dt, hh)");
+        sql("INSERT INTO t_all VALUES(1,2,'login','2020-01-02','09')");
+        sql(
+                "CREATE TABLE t_all_as WITH ('primary-key' = 'dt,hh' , 'partition' = 'dt' ) AS SELECT * FROM t_all");
+        List<Row> resultAll = sql("SHOW CREATE TABLE t_all_as");
+        assertThat(resultAll.toString()).contains("PRIMARY KEY (`dt`, `hh`)");
+        assertThat(resultAll.toString()).contains("PARTITIONED BY (`dt`)");
+        List<Row> dataAll = sql("SELECT * FROM t_all_as");
+        assertThat(dataAll.toString()).isEqualTo("[+I[1, 2, login, 2020-01-02, 09]]");
+
+        // primary key do not exist.
+        sql(
+                "CREATE TABLE t_pk_not_exist (\n"
+                        + "    user_id BIGINT,\n"
+                        + "    item_id BIGINT,\n"
+                        + "    behavior STRING,\n"
+                        + "    dt STRING,\n"
+                        + "    hh STRING,\n"
+                        + "    PRIMARY KEY (dt, hh, user_id) NOT ENFORCED\n"
+                        + ")");
+
+        assertThatThrownBy(
+                        () ->
+                                sql(
+                                        "CREATE TABLE t_pk_not_exist_as WITH ('primary-key' = 'aaa') AS SELECT * FROM t_pk_not_exist"))
+                .hasRootCauseMessage("Primary key column '[aaa]' is not defined in the schema.");
+
+        // primary key in option and DDL.
+        assertThatThrownBy(
+                        () ->
+                                sql(
+                                        "CREATE TABLE t_pk_ddl_option ("
+                                                + "user_id BIGINT,"
+                                                + "item_id BIGINT,"
+                                                + "behavior STRING,"
+                                                + "dt STRING,"
+                                                + "hh STRING,"
+                                                + "PRIMARY KEY (dt, hh, user_id) NOT ENFORCED"
+                                                + ") WITH ('primary-key' = 'dt')"))
+                .hasRootCauseMessage(
+                        "Cannot define primary key on DDL and table options at the same time.");
+
+        // partition do not exist.
+        sql(
+                "CREATE TABLE t_partition_not_exist (\n"
+                        + "    user_id BIGINT,\n"
+                        + "    item_id BIGINT,\n"
+                        + "    behavior STRING,\n"
+                        + "    dt STRING,\n"
+                        + "    hh STRING\n"
+                        + ") PARTITIONED BY (dt, hh) ");
+
+        assertThatThrownBy(
+                        () ->
+                                sql(
+                                        "CREATE TABLE t_partition_not_exist_as WITH ('partition' = 'aaa') AS SELECT * FROM t_partition_not_exist"))
+                .hasRootCauseMessage("Partition column '[aaa]' is not defined in the schema.");
+
+        // partition in option and DDL.
+        assertThatThrownBy(
+                        () ->
+                                sql(
+                                        "CREATE TABLE t_partition_ddl_option ("
+                                                + "user_id BIGINT,"
+                                                + "item_id BIGINT,"
+                                                + "behavior STRING,"
+                                                + "dt STRING,"
+                                                + "hh STRING"
+                                                + ") PARTITIONED BY (dt, hh)  WITH ('partition' = 'dt')"))
+                .hasRootCauseMessage(
+                        "Cannot define partition on DDL and table options at the same time.");
+    }
+
     @Test
     public void testFilesTable() throws Exception {
         sql(
diff --git a/flink-table-store-hive/flink-table-store-hive-catalog/src/test/java/org/apache/flink/table/store/hive/HiveCatalogITCase.java b/flink-table-store-hive/flink-table-store-hive-catalog/src/test/java/org/apache/flink/table/store/hive/HiveCatalogITCase.java
index 4e761516..47388fc8 100644
--- a/flink-table-store-hive/flink-table-store-hive-catalog/src/test/java/org/apache/flink/table/store/hive/HiveCatalogITCase.java
+++ b/flink-table-store-hive/flink-table-store-hive-catalog/src/test/java/org/apache/flink/table/store/hive/HiveCatalogITCase.java
@@ -38,6 +38,7 @@ import com.klarna.hiverunner.annotations.HiveRunnerSetup;
 import com.klarna.hiverunner.annotations.HiveSQL;
 import com.klarna.hiverunner.config.HiveRunnerConfig;
 import org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
+import org.assertj.core.api.Assertions;
 import org.junit.After;
 import org.junit.Assert;
 import org.junit.Before;
@@ -281,6 +282,145 @@ public class HiveCatalogITCase {
         }
     }
 
+    @Test
+    public void testCreateTableAs() throws Exception {
+        tEnv.executeSql("CREATE TABLE t (a INT)").await();
+        tEnv.executeSql("INSERT INTO t VALUES(1)").await();
+        tEnv.executeSql("CREATE TABLE t1 AS SELECT * FROM t");
+        List<Row> result = collect("SELECT * FROM t1$schemas s");
+        Assertions.assertThat(result.toString())
+                .isEqualTo("[+I[0, [{\"id\":0,\"name\":\"a\",\"type\":\"INT\"}], [], [], {}, ]]");
+        List<Row> data = collect("SELECT * FROM t1");
+        Assertions.assertThat(data).contains(Row.of(1));
+
+        // change option
+        tEnv.executeSql("CREATE TABLE t_option (a INT)").await();
+        tEnv.executeSql("INSERT INTO t_option VALUES(1)").await();
+        tEnv.executeSql(
+                "CREATE TABLE t1_option WITH ('file.format' = 'parquet') AS SELECT * FROM t_option");
+        List<Row> resultOption = collect("SELECT * FROM t1_option$options");
+        Assertions.assertThat(resultOption).containsExactly(Row.of("file.format", "parquet"));
+        List<Row> dataOption = collect("SELECT * FROM t1_option");
+        Assertions.assertThat(dataOption).contains(Row.of(1));
+
+        // partition table
+        tEnv.executeSql(
+                "CREATE TABLE t_p (\n"
+                        + "    user_id BIGINT,\n"
+                        + "    item_id BIGINT,\n"
+                        + "    behavior STRING,\n"
+                        + "    dt STRING,\n"
+                        + "    hh STRING\n"
+                        + ") PARTITIONED BY (dt, hh)");
+        tEnv.executeSql("INSERT INTO t_p  SELECT 1,2,'a','2023-02-19','12'");
+        tEnv.executeSql("CREATE TABLE t1_p WITH ('partition' = 'dt') AS SELECT * FROM t_p");
+        List<Row> resultPartition = collect("SELECT * FROM t1_p$schemas s");
+        Assertions.assertThat(resultPartition.toString())
+                .isEqualTo(
+                        "[+I[0, [{\"id\":0,\"name\":\"user_id\",\"type\":\"BIGINT\"},{\"id\":1,\"name\":\"item_id\",\"type\":\"BIGINT\"},{\"id\":2,\"name\":\"behavior\",\"type\":\"STRING\"}"
+                                + ",{\"id\":3,\"name\":\"dt\",\"type\":\"STRING\"},{\"id\":4,\"name\":\"hh\",\"type\":\"STRING\"}], [\"dt\"], [], {}, ]]");
+        List<Row> dataPartition = collect("SELECT * FROM t1_p");
+        Assertions.assertThat(dataPartition.toString()).isEqualTo("[+I[1, 2, a, 2023-02-19, 12]]");
+
+        // primary key
+        tEnv.executeSql(
+                "CREATE TABLE t_pk (\n"
+                        + "    user_id BIGINT,\n"
+                        + "    item_id BIGINT,\n"
+                        + "    behavior STRING,\n"
+                        + "    dt STRING,\n"
+                        + "    hh STRING,\n"
+                        + "    PRIMARY KEY (dt, hh, user_id) NOT ENFORCED\n"
+                        + ")");
+        tEnv.executeSql("INSERT INTO t_pk VALUES(1,2,'aaa','2020-01-02','09')");
+        tEnv.executeSql("CREATE TABLE t_pk_as WITH ('primary-key' = 'dt') AS SELECT * FROM t_pk");
+        List<Row> resultPk = collect("SHOW CREATE TABLE t_pk_as");
+        Assertions.assertThat(resultPk.toString()).contains("PRIMARY KEY (`dt`)");
+        List<Row> dataPk = collect("SELECT * FROM t_pk_as");
+        Assertions.assertThat(dataPk.toString()).isEqualTo("[+I[1, 2, aaa, 2020-01-02, 09]]");
+
+        // primary key + partition
+        tEnv.executeSql(
+                "CREATE TABLE t_all (\n"
+                        + "    user_id BIGINT,\n"
+                        + "    item_id BIGINT,\n"
+                        + "    behavior STRING,\n"
+                        + "    dt STRING,\n"
+                        + "    hh STRING,\n"
+                        + "    PRIMARY KEY (dt, hh, user_id) NOT ENFORCED\n"
+                        + ") PARTITIONED BY (dt, hh)");
+        tEnv.executeSql("INSERT INTO t_all VALUES(1,2,'login','2020-01-02','09')");
+        tEnv.executeSql(
+                "CREATE TABLE t_all_as WITH ('primary-key' = 'dt,hh' , 'partition' = 'dt' ) AS SELECT * FROM t_all");
+        List<Row> resultAll = collect("SHOW CREATE TABLE t_all_as");
+        Assertions.assertThat(resultAll.toString()).contains("PRIMARY KEY (`dt`, `hh`)");
+        Assertions.assertThat(resultAll.toString()).contains("PARTITIONED BY (`dt`)");
+        List<Row> dataAll = collect("SELECT * FROM t_all_as");
+        Assertions.assertThat(dataAll.toString()).isEqualTo("[+I[1, 2, login, 2020-01-02, 09]]");
+
+        // primary key do not exist.
+        tEnv.executeSql(
+                "CREATE TABLE t_pk_not_exist (\n"
+                        + "    user_id BIGINT,\n"
+                        + "    item_id BIGINT,\n"
+                        + "    behavior STRING,\n"
+                        + "    dt STRING,\n"
+                        + "    hh STRING,\n"
+                        + "    PRIMARY KEY (dt, hh, user_id) NOT ENFORCED\n"
+                        + ")");
+
+        Assertions.assertThatThrownBy(
+                        () ->
+                                tEnv.executeSql(
+                                        "CREATE TABLE t_pk_not_exist_as WITH ('primary-key' = 'aaa') AS SELECT * FROM t_pk_not_exist"))
+                .hasRootCauseMessage("Primary key column '[aaa]' is not defined in the schema.");
+
+        // primary key in option and DDL.
+        Assertions.assertThatThrownBy(
+                        () ->
+                                tEnv.executeSql(
+                                        "CREATE TABLE t_pk_ddl_option ("
+                                                + "                            user_id BIGINT,"
+                                                + "                            item_id BIGINT,"
+                                                + "                            behavior STRING,"
+                                                + "                            dt STRING,"
+                                                + "                            hh STRING,"
+                                                + "                            PRIMARY KEY (dt, hh, user_id) NOT ENFORCED"
+                                                + "                        ) WITH ('primary-key' = 'dt')"))
+                .hasRootCauseMessage(
+                        "Cannot define primary key on DDL and table options at the same time.");
+
+        // partition do not exist.
+        tEnv.executeSql(
+                "CREATE TABLE t_partition_not_exist (\n"
+                        + "    user_id BIGINT,\n"
+                        + "    item_id BIGINT,\n"
+                        + "    behavior STRING,\n"
+                        + "    dt STRING,\n"
+                        + "    hh STRING\n"
+                        + ") PARTITIONED BY (dt, hh) ");
+
+        Assertions.assertThatThrownBy(
+                        () ->
+                                tEnv.executeSql(
+                                        "CREATE TABLE t_partition_not_exist_as WITH ('partition' = 'aaa') AS SELECT * FROM t_partition_not_exist"))
+                .hasRootCauseMessage("Partition column '[aaa]' is not defined in the schema.");
+
+        // partition in option and DDL.
+        Assertions.assertThatThrownBy(
+                        () ->
+                                tEnv.executeSql(
+                                        "CREATE TABLE t_partition_ddl_option ("
+                                                + "                            user_id BIGINT,"
+                                                + "                            item_id BIGINT,"
+                                                + "                            behavior STRING,"
+                                                + "                            dt STRING,"
+                                                + "                            hh STRING"
+                                                + "                        ) PARTITIONED BY (dt, hh)  WITH ('partition' = 'dt')"))
+                .hasRootCauseMessage(
+                        "Cannot define partition on DDL and table options at the same time.");
+    }
+
     @Test
     public void testRenameTable() throws Exception {
         tEnv.executeSql("CREATE TABLE t1 (a INT)").await();
diff --git a/flink-table-store-spark/src/test/java/org/apache/flink/table/store/spark/SparkReadITCase.java b/flink-table-store-spark/src/test/java/org/apache/flink/table/store/spark/SparkReadITCase.java
index 84de2f05..d830d91e 100644
--- a/flink-table-store-spark/src/test/java/org/apache/flink/table/store/spark/SparkReadITCase.java
+++ b/flink-table-store-spark/src/test/java/org/apache/flink/table/store/spark/SparkReadITCase.java
@@ -144,6 +144,140 @@ public class SparkReadITCase extends SparkReadTestBase {
                                 + "{\"id\":2,\"name\":\"c\",\"type\":\"CHAR(10)\"}]]]");
     }
 
+    @Test
+    public void testCreateTableAs() {
+        spark.sql(
+                "CREATE TABLE default.testCreateTable(\n"
+                        + "a BIGINT,\n"
+                        + "b VARCHAR(10),\n"
+                        + "c CHAR(10))");
+        spark.sql("INSERT INTO default.testCreateTable VALUES(1,'a','b')");
+        spark.sql(
+                "CREATE TABLE default.testCreateTableAs AS SELECT * FROM default.testCreateTable");
+        List<Row> result = spark.sql("SELECT * FROM default.testCreateTableAs").collectAsList();
+
+        assertThat(result.stream().map(Row::toString)).containsExactlyInAnyOrder("[1,a,b]");
+
+        // partitioned table
+        spark.sql(
+                "CREATE TABLE default.partitionedTable (\n"
+                        + "a BIGINT,\n"
+                        + "b STRING,"
+                        + "c STRING) USING tablestore\n"
+                        + "COMMENT 'table comment'\n"
+                        + "PARTITIONED BY (a,b)");
+        spark.sql("INSERT INTO default.partitionedTable VALUES(1,'aaa','bbb')");
+        spark.sql(
+                "CREATE TABLE default.partitionedTableAs PARTITIONED BY (a) AS SELECT * FROM default.partitionedTable");
+        assertThat(
+                        spark.sql("SHOW CREATE TABLE default.partitionedTableAs")
+                                .collectAsList()
+                                .toString())
+                .isEqualTo(
+                        String.format(
+                                "[[CREATE TABLE partitionedTableAs (\n"
+                                        + "  `a` BIGINT,\n"
+                                        + "  `b` STRING,\n"
+                                        + "  `c` STRING)\n"
+                                        + "PARTITIONED BY (a)\n"
+                                        + "TBLPROPERTIES(\n"
+                                        + "  'path' = '%s')\n"
+                                        + "]]",
+                                new Path(warehousePath, "default.db/partitionedTableAs")));
+        List<Row> resultPartition =
+                spark.sql("SELECT * FROM default.partitionedTableAs").collectAsList();
+        assertThat(resultPartition.stream().map(Row::toString))
+                .containsExactlyInAnyOrder("[1,aaa,bbb]");
+
+        // change TBLPROPERTIES
+        spark.sql(
+                "CREATE TABLE default.testTable(\n"
+                        + "a BIGINT,\n"
+                        + "b VARCHAR(10),\n"
+                        + "c CHAR(10))"
+                        + " TBLPROPERTIES("
+                        + " 'file.format' = 'orc'"
+                        + ")");
+        spark.sql("INSERT INTO default.testTable VALUES(1,'a','b')");
+        spark.sql(
+                "CREATE TABLE default.testTableAs TBLPROPERTIES ('file.format' = 'parquet') AS SELECT * FROM default.testTable");
+        assertThat(spark.sql("SHOW CREATE TABLE default.testTableAs").collectAsList().toString())
+                .isEqualTo(
+                        String.format(
+                                "[[CREATE TABLE testTableAs (\n"
+                                        + "  `a` BIGINT,\n"
+                                        + "  `b` STRING,\n"
+                                        + "  `c` STRING)\n"
+                                        + "TBLPROPERTIES(\n"
+                                        + "  'file.format' = 'parquet',\n"
+                                        + "  'path' = '%s')\n"
+                                        + "]]",
+                                new Path(warehousePath, "default.db/testTableAs")));
+        List<Row> resultProp = spark.sql("SELECT * FROM default.testTableAs").collectAsList();
+
+        assertThat(resultProp.stream().map(Row::toString)).containsExactlyInAnyOrder("[1,a,b]");
+
+        // primary key
+        spark.sql(
+                "CREATE TABLE default.t_pk (\n"
+                        + "a BIGINT,\n"
+                        + "b STRING,\n"
+                        + "c STRING\n"
+                        + ") TBLPROPERTIES ("
+                        + "  'primary-key' = 'a,b'"
+                        + ")\n"
+                        + "COMMENT 'table comment'");
+        spark.sql("INSERT INTO default.t_pk VALUES(1,'aaa','bbb')");
+        spark.sql(
+                "CREATE TABLE default.t_pk_as TBLPROPERTIES ('primary-key' = 'a') AS SELECT * FROM default.t_pk");
+        assertThat(spark.sql("SHOW CREATE TABLE  default.t_pk_as").collectAsList().toString())
+                .isEqualTo(
+                        String.format(
+                                "[[CREATE TABLE t_pk_as (\n"
+                                        + "  `a` BIGINT NOT NULL,\n"
+                                        + "  `b` STRING,\n"
+                                        + "  `c` STRING)\n"
+                                        + "TBLPROPERTIES(\n"
+                                        + "  'path' = '%s')\n"
+                                        + "]]",
+                                new Path(warehousePath, "default.db/t_pk_as")));
+        List<Row> resultPk = spark.sql("SELECT * FROM default.t_pk_as").collectAsList();
+
+        assertThat(resultPk.stream().map(Row::toString)).containsExactlyInAnyOrder("[1,aaa,bbb]");
+
+        // primary key + partition
+        spark.sql(
+                "CREATE TABLE t_all (\n"
+                        + "    user_id BIGINT,\n"
+                        + "    item_id BIGINT,\n"
+                        + "    behavior STRING,\n"
+                        + "    dt STRING,\n"
+                        + "    hh STRING\n"
+                        + ") PARTITIONED BY (dt, hh) TBLPROPERTIES (\n"
+                        + "    'primary-key' = 'dt,hh,user_id'\n"
+                        + ")");
+        spark.sql("INSERT INTO t_all VALUES(1,2,'bbb','2020-01-01','12')");
+        spark.sql(
+                "CREATE TABLE default.t_all_as PARTITIONED BY (dt) TBLPROPERTIES ('primary-key' = 'dt,hh') AS SELECT * FROM default.t_all");
+        assertThat(spark.sql("SHOW CREATE TABLE default.t_all_as").collectAsList().toString())
+                .isEqualTo(
+                        String.format(
+                                "[[CREATE TABLE t_all_as (\n"
+                                        + "  `user_id` BIGINT,\n"
+                                        + "  `item_id` BIGINT,\n"
+                                        + "  `behavior` STRING,\n"
+                                        + "  `dt` STRING NOT NULL,\n"
+                                        + "  `hh` STRING NOT NULL)\n"
+                                        + "PARTITIONED BY (dt)\n"
+                                        + "TBLPROPERTIES(\n"
+                                        + "  'path' = '%s')\n"
+                                        + "]]",
+                                new Path(warehousePath, "default.db/t_all_as")));
+        List<Row> resultAll = spark.sql("SELECT * FROM default.t_all_as").collectAsList();
+        assertThat(resultAll.stream().map(Row::toString))
+                .containsExactlyInAnyOrder("[1,2,bbb,2020-01-01,12]");
+    }
+
     @Test
     public void testCreateTableWithNullablePk() {
         spark.sql("USE tablestore");