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");