Column names with numbers in a file and creating hive table

Question

I am trying to create a table in hive. need help with it.

Sample code: CREATE EXTERNAL TABLE table1( id STRING, name STRING, "12489738" STRING, "12492628" STRING, "12492633" STRING, "12492638" STRING, "12492655" STRING, "12492659" STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LOCATION "" tblproperties ("skip.header.line.count"="1");

But it throws error:

Error info: NoViableAltException(320@[]) at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.identifier(HiveParser_IdentifiersParser.java:11633) at org.apache.hadoop.hive.ql.parse.HiveParser.identifier(HiveParser.java:49892) at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameType(HiveParser.java:40082) at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameTypeList(HiveParser.java:38241) at org.apache.hadoop.hive.ql.parse.HiveParser.createTableStatement(HiveParser.java:6726) at org.apache.hadoop.hive.ql.parse.HiveParser.ddlStatement(HiveParser.java:4122) at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1786) at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1152) at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:211) at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:171) at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:447) at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:330) at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1233) at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1274) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1170) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1160) at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:217) at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:169) at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:380) at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:740) at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:685) at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625) 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:233) at org.apache.hadoop.util.RunJar.main(RunJar.java:148) FAILED: ParseException line 4:0 cannot recognize input near '"12489738"' 'STRING' ',' in column specification

Shu Shu · Accepted Answer · 2018-12-19T00:40:03

Try with escaping numbered column names with `(backticks)

hive> CREATE EXTERNAL TABLE table1( id STRING, name STRING, `12489738` STRING, 
     `12492628` STRING, 
     `12492633` STRING, `12492638` STRING, `12492655` STRING, `12492659` STRING)
      ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LOCATION "" 
      tblproperties ("skip.header.line.count"="1");

Column names with numbers in a file and creating hive table

1 Answers