在 Hive1.2.1 版本运行如下 HQL 时:
create table
stats_hourly_tmp1(
pl string,
ver string,
en string,
s_time bigint,
u_ud string,
u_sd string,
hour int,
date string
)
row format delimited
fields terminated by "\t";
会抛出如下异常:
FailedPredicateException(identifier,{useSQL11ReservedKeywordsForIdentifier()}?)
at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.identifier(HiveParser_IdentifiersParser.java:10924)
at org.apache.hadoop.hive.ql.parse.HiveParser.identifier(HiveParser.java:45850)
at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameType(HiveParser.java:38044)
at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameTypeList(HiveParser.java:36203)
at org.apache.hadoop.hive.ql.parse.HiveParser.createTableStatement(HiveParser.java:5214)
at org.apache.hadoop.hive.ql.parse.HiveParser.ddlStatement(HiveParser.java:2640)
at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1650)
at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1109)
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:202)
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:396)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:308)
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1122)
at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1170)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1059)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1049)
at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:213)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:165)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:376)
at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:736)
at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:681)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:621)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
FAILED: ParseException line 5:2 Failed to recognize predicate 'date'. Failed rule: 'identifier' in column specification
在 Hive1.2.0 版本开始增加了如下配置选项,默认值为 true。
hive.support.sql11.reserved.keywords=true
该选项的目的是:是否启用对 SQL2011 保留关键字的支持。 启用后,将支持部分 SQL2011 保留关键字。
从上面可以知道是因为启用了对保留关键字的支持导致的,上面语句中 date
是保留关键字.所以解决方案如下:
方案(1)直接弃用保留关键字 date
,即不使用留关键字,或者我们可以加上
反引号。推荐方案
create table
stats_hourly_tmp1(
pl string,
ver string,
en string,
s_time bigint,
u_ud string,
u_sd string,
hour int,
`date` string
)
row format delimited
fields terminated by "\t";
方案(2)弃用对保留关键字的支持,此方案临时生效。即当前命令行窗口有效。
hive (default)> set hive.support.sql11.reserved.keywords=false;
方案(3)或者在 /opt/module/hive/conf 目录下的 hive-site.xml
配置文件中修改配置选项,此方案永久生效。
<property>
<name>hive.support.sql11.reserved.keywords</name>
<value>false</value>
</property>