Hive中执行SQL语句时,出现类似于“Display all 469 possibilities? (y or n)”的错误,根本原因是因为SQL语句中存在tab键导致,tab键在linux系统中是有特殊含义的。
下面我们做个小demo测试一下,如下建表语句中各字段前均为tab缩进:
CREATE TABLE `test_Display_all_xxx_possibilities`(
`name` string,
`age` string,
`sex` string
)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'field.delim'='\u0001',
'serialization.format'='\u0001')
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://nameservice1/user/hive/warehouse/ti_z_wh_inf_20160928'
TBLPROPERTIES (
'COLUMN_STATS_ACCURATE'='true',
'numFiles'='1',
'numRows'='0',
'rawDataSize'='0',
'serialization.null.format'='',
'totalSize'='2326',
'transient_lastDdlTime'='1475101756');
hive环境执行结果如下:
hive> CREATE TABLE `test_Display_all_xxx_possibilities`(
> Display all 469 possibilities? (y or n)
> ame` string,
> Display all 469 possibilities? (y or n)
> g,
> Display all 469 possibilities? (y or n)
> g
> )
> ROW FORMAT SERDE
> 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
> WITH SERDEPROPERTIES (
> 'field.delim'='\u0001',
> 'serialization.format'='\u0001')
> STORED AS INPUTFORMAT
> 'org.apache.hadoop.mapred.TextInputFormat'
> OUTPUTFORMAT
> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
> LOCATION
> 'hdfs://nameservice1/user/hive/warehouse/ti_z_wh_inf_20160928'
> TBLPROPERTIES (
> 'COLUMN_STATS_ACCURATE'='true',
> 'numFiles'='1',
> 'numRows'='0',
> 'rawDataSize'='0',
> 'serialization.null.format'='',
> 'totalSize'='2326',
> 'transient_lastDdlTime'='1475101756');
NoViableAltException(-1@[])
at org.apache.hadoop.hive.ql.parse.HiveParser.type(HiveParser.java:38756)
at org.apache.hadoop.hive.ql.parse.HiveParser.colType(HiveParser.java:38521)
at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameType(HiveParser.java:38221)
at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameTypeList(HiveParser.java:36416)
at org.apache.hadoop.hive.ql.parse.HiveParser.createTableStatement(HiveParser.java:4863)
at org.apache.hadoop.hive.ql.parse.HiveParser.ddlStatement(HiveParser.java:2363)
at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1585)
at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1061)
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:201)
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:417)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:311)
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1189)
at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1284)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1115)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1103)
at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:220)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:172)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:383)
at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:775)
at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:693)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:628)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
FAILED: ParseException line 24:39 cannot recognize input near '<EOF>' '<EOF>' '<EOF>' in column type
修改建表语句,将字段前tab替换为空格:
CREATE TABLE `test_Display_all_xxx_possibilities`(
`name` string,
`age` string,
`sex` string
)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'field.delim'='\u0001',
'serialization.format'='\u0001')
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://nameservice1/user/hive/warehouse/ti_z_wh_inf_20160928'
TBLPROPERTIES (
'COLUMN_STATS_ACCURATE'='true',
'numFiles'='1',
'numRows'='0',
'rawDataSize'='0',
'serialization.null.format'='',
'totalSize'='2326',
'transient_lastDdlTime'='1475101756');
hive环境下重新执行,结果如下:
hive> CREATE TABLE `test_Display_all_xxx_possibilities`(
> `name` string,
> `age` string,
> `sex` string
> )
> ROW FORMAT SERDE
> 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
> WITH SERDEPROPERTIES (
> 'field.delim'='\u0001',
> 'serialization.format'='\u0001')
> STORED AS INPUTFORMAT
> 'org.apache.hadoop.mapred.TextInputFormat'
> OUTPUTFORMAT
> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
> LOCATION
> 'hdfs://nameservice1/user/hive/warehouse/ti_z_wh_inf_20160928'
> TBLPROPERTIES (
> 'COLUMN_STATS_ACCURATE'='true',
> 'numFiles'='1',
> 'numRows'='0',
> 'rawDataSize'='0',
> 'serialization.null.format'='',
> 'totalSize'='2326',
> 'transient_lastDdlTime'='1475101756');
OK
Time taken: 0.087 seconds
结论:hive环境中SQL执行语句中应避免使用tab键。
参考:https://blog.csdn.net/stefan_xiepj/article/details/53197212