CODEWITHSUNDEEP
hivehiveql
Vishnu
Vishnu

Reputation: 11

Column names with numbers in a file and creating hive table

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

Upvotes: 0

Views: 1996

Answers (1)

notNull
notNull

Reputation: 31540

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");

Upvotes: 3

Related Questions