问题解决了就好， > 找到原因了，flink 把year 当成关键字了。。。。
YEAR在时间相关类型中会使用，在 FLINK SQL 是关键字的，在一些DB里也是关键字，使用时需要转义，类似的还有DAY, MONTH等[1]。 [1] https://ci.apache.org/projects/flink/flink-docs-master/dev/table/types.html#interval-year-to-month <https://ci.apache.org/projects/flink/flink-docs-master/dev/table/types.html#interval-year-to-month> 祝好 Leonard Xu > > > > > > > > > > > > > > At 2020-05-27 19:09:43, "Zhou Zach" <[email protected]> wrote: >> The program finished with the following exception: >> >> >> org.apache.flink.client.program.ProgramInvocationException: The main method >> caused an error: SQL parse failed. Encountered "year =" at line 4, column 51. >> Was expecting one of: >> "ARRAY" ... >> "CASE" ... >> "CURRENT" ... >> "CURRENT_CATALOG" ... >> "CURRENT_DATE" ... >> "CURRENT_DEFAULT_TRANSFORM_GROUP" ... >> "CURRENT_PATH" ... >> "CURRENT_ROLE" ... >> "CURRENT_SCHEMA" ... >> "CURRENT_TIME" ... >> "CURRENT_TIMESTAMP" ... >> "CURRENT_USER" ... >> "DATE" ... >> "EXISTS" ... >> "FALSE" ... >> "INTERVAL" ... >> "LOCALTIME" ... >> "LOCALTIMESTAMP" ... >> "MULTISET" ... >> "NEW" ... >> "NEXT" ... >> "NOT" ... >> "NULL" ... >> "PERIOD" ... >> "SESSION_USER" ... >> "SYSTEM_USER" ... >> "TIME" ... >> "TIMESTAMP" ... >> "TRUE" ... >> "UNKNOWN" ... >> "USER" ... >> <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> ... >> <LBRACE_FN> ... >> "?" ... >> "+" ... >> "-" ... >> <BRACKET_QUOTED_IDENTIFIER> ... >> <QUOTED_IDENTIFIER> ... >> <BACK_QUOTED_IDENTIFIER> ... >> <IDENTIFIER> ... >> <UNICODE_QUOTED_IDENTIFIER> ... >> "CAST" ... >> "EXTRACT" ... >> "POSITION" ... >> "CONVERT" ... >> "TRANSLATE" ... >> "OVERLAY" ... >> "FLOOR" ... >> "CEIL" ... >> "CEILING" ... >> "SUBSTRING" ... >> "TRIM" ... >> "CLASSIFIER" ... >> "MATCH_NUMBER" ... >> "RUNNING" ... >> "PREV" ... >> "JSON_EXISTS" ... >> "JSON_VALUE" ... >> "JSON_QUERY" ... >> "JSON_OBJECT" ... >> "JSON_OBJECTAGG" ... >> "JSON_ARRAY" ... >> "JSON_ARRAYAGG" ... >> "MAP" ... >> "SPECIFIC" ... >> "ABS" ... >> "AVG" ... >> "CARDINALITY" ... >> "CHAR_LENGTH" ... >> "CHARACTER_LENGTH" ... >> "COALESCE" ... >> "COLLECT" ... >> "COVAR_POP" ... >> "COVAR_SAMP" ... >> "CUME_DIST" ... >> "COUNT" ... >> "DENSE_RANK" ... >> "ELEMENT" ... >> "EXP" ... >> "FIRST_VALUE" ... >> "FUSION" ... >> "GROUPING" ... >> "HOUR" ... >> "LAG" ... >> "LEAD" ... >> "LEFT" ... >> "LAST_VALUE" ... >> "LN" ... >> "LOWER" ... >> "MAX" ... >> "MIN" ... >> "MINUTE" ... >> "MOD" ... >> "MONTH" ... >> "NTH_VALUE" ... >> "NTILE" ... >> "NULLIF" ... >> "OCTET_LENGTH" ... >> "PERCENT_RANK" ... >> "POWER" ... >> "RANK" ... >> "REGR_COUNT" ... >> "REGR_SXX" ... >> "REGR_SYY" ... >> "RIGHT" ... >> "ROW_NUMBER" ... >> "SECOND" ... >> "SQRT" ... >> "STDDEV_POP" ... >> "STDDEV_SAMP" ... >> "SUM" ... >> "UPPER" ... >> "TRUNCATE" ... >> "VAR_POP" ... >> "VAR_SAMP" ... >> "YEAR" ... >> "YEAR" "(" ... >> >> >> 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) >> Caused by: org.apache.flink.table.api.SqlParserException: SQL parse failed. >> Encountered "year =" at line 4, column 51. >> Was expecting one of: >> "ARRAY" ... >> "CASE" ... >> "CURRENT" ... >> "CURRENT_CATALOG" ... >> "CURRENT_DATE" ... >> "CURRENT_DEFAULT_TRANSFORM_GROUP" ... >> "CURRENT_PATH" ... >> "CURRENT_ROLE" ... >> "CURRENT_SCHEMA" ... >> "CURRENT_TIME" ... >> "CURRENT_TIMESTAMP" ... >> "CURRENT_USER" ... >> "DATE" ... >> "EXISTS" ... >> "FALSE" ... >> "INTERVAL" ... >> "LOCALTIME" ... >> "LOCALTIMESTAMP" ... >> "MULTISET" ... >> "NEW" ... >> "NEXT" ... >> "NOT" ... >> "NULL" ... >> "PERIOD" ... >> "SESSION_USER" ... >> "SYSTEM_USER" ... >> "TIME" ... >> "TIMESTAMP" ... >> "TRUE" ... >> "UNKNOWN" ... >> "USER" ... >> <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> ... >> <LBRACE_FN> ... >> "?" ... >> "+" ... >> "-" ... >> <BRACKET_QUOTED_IDENTIFIER> ... >> <QUOTED_IDENTIFIER> ... >> <BACK_QUOTED_IDENTIFIER> ... >> <IDENTIFIER> ... >> <UNICODE_QUOTED_IDENTIFIER> ... >> "CAST" ... >> "EXTRACT" ... >> "POSITION" ... >> "CONVERT" ... >> "TRANSLATE" ... >> "OVERLAY" ... >> "FLOOR" ... >> "CEIL" ... >> "CEILING" ... >> "SUBSTRING" ... >> "TRIM" ... >> "CLASSIFIER" ... >> "MATCH_NUMBER" ... >> "RUNNING" ... >> "PREV" ... >> "JSON_EXISTS" ... >> "JSON_VALUE" ... >> "JSON_QUERY" ... >> "JSON_OBJECT" ... >> "JSON_OBJECTAGG" ... >> "JSON_ARRAY" ... >> "JSON_ARRAYAGG" ... >> "MAP" ... >> "SPECIFIC" ... >> "ABS" ... >> "AVG" ... >> "CARDINALITY" ... >> "CHAR_LENGTH" ... >> "CHARACTER_LENGTH" ... >> "COALESCE" ... >> "COLLECT" ... >> "COVAR_POP" ... >> "COVAR_SAMP" ... >> "CUME_DIST" ... >> "COUNT" ... >> "DENSE_RANK" ... >> "ELEMENT" ... >> "EXP" ... >> "FIRST_VALUE" ... >> "FUSION" ... >> "GROUPING" ... >> "HOUR" ... >> "LAG" ... >> "LEAD" ... >> "LEFT" ... >> "LAST_VALUE" ... >> "LN" ... >> "LOWER" ... >> "MAX" ... >> "MIN" ... >> "MINUTE" ... >> "MOD" ... >> "MONTH" ... >> "NTH_VALUE" ... >> "NTILE" ... >> "NULLIF" ... >> "OCTET_LENGTH" ... >> "PERCENT_RANK" ... >> "POWER" ... >> "RANK" ... >> "REGR_COUNT" ... >> "REGR_SXX" ... >> "REGR_SYY" ... >> "RIGHT" ... >> "ROW_NUMBER" ... >> "SECOND" ... >> "SQRT" ... >> "STDDEV_POP" ... >> "STDDEV_SAMP" ... >> "SUM" ... >> "UPPER" ... >> "TRUNCATE" ... >> "VAR_POP" ... >> "VAR_SAMP" ... >> "YEAR" ... >> "YEAR" "(" ... >> >> >> at >> org.apache.flink.table.planner.calcite.CalciteParser.parse(CalciteParser.java:50) >> at >> org.apache.flink.table.planner.delegation.ParserImpl.parse(ParserImpl.java:64) >> at >> org.apache.flink.table.api.internal.TableEnvironmentImpl.sqlUpdate(TableEnvironmentImpl.java:484) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> 在 2020-05-27 19:08:09，"Rui Li" <[email protected]> 写道： >>> 读hive分区表报的什么错啊，把stacktrace贴一下？ >>> >>> On Wed, May 27, 2020 at 6:08 PM Zhou Zach <[email protected]> wrote: >>> >>>> >>>> >>>> hive partition table: >>>> >>>> >>>> 1CREATE TABLE `dwd.bill`( >>>> 2 `id` bigint, >>>> 3 `gid` bigint, >>>> 4 `count` bigint, >>>> 5 `price` bigint, >>>> 6 `srcuid` bigint, >>>> 7 `srcnickname` string, >>>> 8 `srcleftmoney` bigint, >>>> 9 `srcwealth` bigint, >>>> 10 `srccredit` decimal(10,0), >>>> 11 `dstnickname` string, >>>> 12 `dstuid` bigint, >>>> 13 `familyid` int, >>>> 14 `dstleftmoney` bigint, >>>> 15 `dstwealth` bigint, >>>> 16 `dstcredit` decimal(10,0), >>>> 17 `addtime` bigint, >>>> 18 `type` int, >>>> 19 `getmoney` decimal(10,0), >>>> 20 `os` int, >>>> 21 `bak` string, >>>> 22 `getbonus` decimal(10,0), >>>> 23 `unionbonus` decimal(10,0)) >>>> 24PARTITIONED BY ( >>>> 25 `year` int, >>>> 26 `month` int) >>>> 27ROW FORMAT SERDE >>>> 28 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' >>>> 29STORED AS INPUTFORMAT >>>> 30 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' >>>> 31OUTPUTFORMAT >>>> 32 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' >>>> >>>> >>>> >>>> >>>> Query： >>>> >>>> >>>> tableEnv.sqlUpdate( >>>> """ >>>> | >>>> |INSERT INTO catalog2.dwd.orders >>>> |select srcuid, price from catalog2.dwd.bill where year = 2020 >>>> | >>>> | >>>> |""".stripMargin) >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> 在 2020-05-27 18:01:19，"Leonard Xu" <[email protected]> 写道： >>>>> Flink 支持hive分区表的，看你在另外一个邮件里贴了，你能把你的hive表和query在邮件里贴下吗？ >>>>> >>>>> 祝好 >>>>> Leonard Xu >>>>> >>>>>> 在 2020年5月27日，17:40，Zhou Zach <[email protected]> 写道： >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> 感谢回复，表名前加上Catalog和db前缀可以成功访问了。 >>>>>> 现在遇到个问题，flink 读hive >>>> 分区表时，如果where子句用分区键，比如year过滤就会报错，用表中其他字段过滤是没问题的，是flink 不支持 >>>> hive分区表，还是哪个地方没设置对 >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> 在 2020-05-27 17:33:11，"Leonard Xu" <[email protected]> 写道： >>>>>>> Hi， >>>>>>>> 因为一个HiveCatalog只能关联一个库 >>>>>>> 一个Catalog是可以关联到多个db的，不同catalog，不同db中表都可以访问的. >>>>>>> >>>>>>> Flink SQL> show catalogs; >>>>>>> default_catalog >>>>>>> myhive >>>>>>> Flink SQL> use catalog myhive; >>>>>>> Flink SQL> show databases; >>>>>>> default >>>>>>> hive_test >>>>>>> hive_test1 >>>>>>> Flink SQL> select * from hive_test.db2_table union select * from >>>> myhive.hive_test1.db1_table; >>>>>>> 2020-05-27 17:25:48,565 INFO org.apache.hadoop.hive.conf.HiveConf >>>>>>> >>>>>>> >>>>>>> >>>>>>> 祝好 >>>>>>> Leonard Xu >>>>>>> >>>>>>> >>>>>>>> 在 2020年5月27日，10:55，Zhou Zach <[email protected]> 写道： >>>>>>>> >>>>>>>> hi all， >>>>>>>> Flink sql 的HiveCatalog 是不是不能跨库操作啊，就是一个flink >>>> sql中join的两个表涉及到两个不同到库，因为一个HiveCatalog只能关联一个库 >>>> >>> >>> >>> -- >>> Best regards! >>> Rui Li
