回复的好详细！而且引出了相关的测试用例 Thanks very much！
在 2020-05-28 14:23:33，"Leonard Xu" <xbjt...@gmail.com> 写道： > >> |INSERT INTO dwdCatalog.dwd.t1_copy partition (`p_year` = p_year, >> `p_month` = p_month) >> |select id,name from dwdCatalog.dwd.t1 where `p_year` = 2020 and >> `p_month` = 4 > >动态分区不是这样指定的，和hive的语法是一样的，下面两种应该都可以，flink这边文档少了点，可以参考[1][2] > >INSERT INTO dwdCatalog.dwd.t1_copy > select id,name,`p_year`,`p_month` from dwdCatalog.dwd.t1 where `p_year` = > 2020 and `p_month` = 4 > >INSERT INTO dwdCatalog.dwd.t1_copy > select * from dwdCatalog.dwd.t1 where `p_year` = 2020 and `p_month` = 4 > >Best, >Leonard Xu >[1] >https://ci.apache.org/projects/flink/flink-docs-master/dev/table/sql/insert.html#examples > ><https://ci.apache.org/projects/flink/flink-docs-master/dev/table/sql/insert.html#examples> >[2] >https://github.com/apache/flink/blob/master/flink-connectors/flink-connector-hive/src/test/java/org/apache/flink/connectors/hive/TableEnvHiveConnectorTest.java#L294 > ><https://github.com/apache/flink/blob/master/flink-connectors/flink-connector-hive/src/test/java/org/apache/flink/connectors/hive/TableEnvHiveConnectorTest.java#L294> > > > >> 在 2020年5月28日，13:59，Zhou Zach <wander...@163.com> 写道： >> >> 多谢指点，可以了。 >> 但是换成动态插入，有问题： >> org.apache.flink.client.program.ProgramInvocationException: The main method >> caused an error: SQL parse failed. Encountered "p_year" at line 3, column 58. >> Was expecting one of: >> "DATE" ... >> "FALSE" ... >> "INTERVAL" ... >> "NULL" ... >> "TIME" ... >> "TIMESTAMP" ... >> "TRUE" ... >> "UNKNOWN" ... >> <UNSIGNED_INTEGER_LITERAL> ... >> <APPROX_NUMERIC_LITERAL> ... >> <DECIMAL_NUMERIC_LITERAL> ... >> <BINARY_STRING_LITERAL> ... >> <QUOTED_STRING> ... >> <PREFIXED_STRING_LITERAL> ... >> <UNICODE_STRING_LITERAL> ... >> <LBRACE_D> ... >> <LBRACE_T> ... >> <LBRACE_TS> ... >> "+" ... >> "-" ... >> >> >> at >> org.apache.flink.client.program.PackagedProgram.callMainMethod(PackagedProgram.java:335) >> at >> org.apache.flink.client.program.PackagedProgram.invokeInteractiveModeForExecution(PackagedProgram.java:205) >> at org.apache.flink.client.ClientUtils.executeProgram(ClientUtils.java:138) >> at >> org.apache.flink.client.cli.CliFrontend.executeProgram(CliFrontend.java:664) >> at org.apache.flink.client.cli.CliFrontend.run(CliFrontend.java:213) >> at >> org.apache.flink.client.cli.CliFrontend.parseParameters(CliFrontend.java:895) >> at >> org.apache.flink.client.cli.CliFrontend.lambda$main$10(CliFrontend.java:968) >> at java.security.AccessController.doPrivileged(Native Method) >> at javax.security.auth.Subject.doAs(Subject.java:422) >> at >> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1875) >> at >> org.apache.flink.runtime.security.HadoopSecurityContext.runSecured(HadoopSecurityContext.java:41) >> at org.apache.flink.client.cli.CliFrontend.main(CliFrontend.java:968) >> >> >> >> >> Query： >> tableEnv.sqlUpdate( >> """ >> | >> |INSERT INTO dwdCatalog.dwd.t1_copy partition (`p_year` = p_year, >> `p_month` = p_month) >> |select id,name from dwdCatalog.dwd.t1 where `p_year` = 2020 and >> `p_month` = 4 >> | >> |""".stripMargin) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> 在 2020-05-28 13:39:49，"Leonard Xu" <xbjt...@gmail.com> 写道： >>> Hi, >>>> |select * from dwdCatalog.dwd.t1 where `p_year` = 2020 and `p_month` >>>> = 5 >>> >>> 应该是 select * 会把分区字段一起带出来吧，你字段就不匹配了，select里加上你需要的字段吧 >>> >>> 祝好， >>> Leonard Xu >>> >>>> 在 2020年5月28日，12:57，Zhou Zach <wander...@163.com> 写道： >>>> >>>> org.apache.flink.client.program.ProgramInvocationException: The main >>>> method caused an error: Field types of query result and registered >>>> TableSink dwdCatalog.dwd.t1_copy do not match. >>>> >>>> Query schema: [id: BIGINT, name: STRING, p_year: INT, p_month: INT, >>>> EXPR$4: INT NOT NULL, EXPR$5: INT NOT NULL] >>>> >>>> Sink schema: [id: BIGINT, name: STRING, p_year: INT, p_month: INT] >>>> >>>> at >>>> org.apache.flink.client.program.PackagedProgram.callMainMethod(PackagedProgram.java:335) >>>> >>>> at >>>> org.apache.flink.client.program.PackagedProgram.invokeInteractiveModeForExecution(PackagedProgram.java:205) >>>> >>>> at org.apache.flink.client.ClientUtils.executeProgram(ClientUtils.java:138) >>>> >>>> at >>>> org.apache.flink.client.cli.CliFrontend.executeProgram(CliFrontend.java:664) >>>> >>>> at org.apache.flink.client.cli.CliFrontend.run(CliFrontend.java:213) >>>> >>>> at >>>> org.apache.flink.client.cli.CliFrontend.parseParameters(CliFrontend.java:895) >>>> >>>> at >>>> org.apache.flink.client.cli.CliFrontend.lambda$main$10(CliFrontend.java:968) >>>> >>>> at java.security.AccessController.doPrivileged(Native Method) >>>> >>>> at javax.security.auth.Subject.doAs(Subject.java:422) >>>> >>>> at >>>> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1875) >>>> >>>> at >>>> org.apache.flink.runtime.security.HadoopSecurityContext.runSecured(HadoopSecurityContext.java:41) >>>> >>>> at org.apache.flink.client.cli.CliFrontend.main(CliFrontend.java:968) >>>> >>>> >>>> >>>> >>>> hive分区表： >>>> CREATE TABLE `dwd.t1`( >>>> `id` bigint, >>>> `name` string) >>>> PARTITIONED BY ( >>>> `p_year` int, >>>> `p_month` int) >>>> >>>> >>>> CREATE TABLE `dwd.t1_copy`( >>>> `id` bigint, >>>> `name` string) >>>> PARTITIONED BY ( >>>> `p_year` int, >>>> `p_month` int) >>>> >>>> >>>> Flink sql: >>>> tableEnv.sqlUpdate( >>>> """ >>>> | >>>> |INSERT INTO dwdCatalog.dwd.t1_copy partition (`p_year` = 2020, >>>> `p_month` = 5) >>>> |select * from dwdCatalog.dwd.t1 where `p_year` = 2020 and `p_month` >>>> = 5 >>>> | >>>> |""".stripMargin) >>>> >>>> >>>> thanks for your help >
