【Hive】FAILED: ParseException line 5:2 Failed to recognize predicate 'date'. Failed rule: 'identifier' in column specification

1. 問題描述

在 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      

2. 問題分析

在 Hive1.2.0 版本開始增加了如下配置選項，預設值為 ​

​true。​

​

hive.support.sql11.reserved.keywords=true      

該選項的目的是：是否啟用對 SQL2011 保留關鍵字的支援。 啟用後，将支援部分 SQL2011 保留關鍵字。

3.　解決方案

從上面可以知道是因為啟用了對保留關鍵字的支援導緻的，上面語句中 ​

​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>