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