找到原因了，flink 把year 当成关键字了。。。。
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
