question Re: Creating hive table with columns from a text file in Archives of Support Questions (Read Only)

Creating hive table with columns from a text file

prabhat_ratnala — Wed, 24 May 2017 00:30:22 GMT

Hi All,

I am trying to create a table in Hive from a txt file using a shell script in this format.

My t_cols.txt has data as below:

id string, name string, city string, lpd timestamp

I want to create hive table whose columns should be coming from this text file.

This is how my hive query looks like:

table_cols=`cat t_cols.txt`
hive --hiveconf t_name=${table_cols} -e 'create table leap_frog_snapshot.LINKED_OBJ_TRACKING (\${hiveconf:t_name}) stored as orc tblproperties ("orc.compress"="SNAPPY") ; '

This is not working somehow.

Can someone explain how to achieve this?

Re: Creating hive table with columns from a text file

balavignesh_nag — Wed, 24 May 2017 00:39:49 GMT

@Prabhat Ratnala

Ideally it should work. But what's the error message? I think the value substituted in the parameter are not properly assigned. try running the script using set -x so that it will be easy to debug. Even after running it if throw an error then paste the error message. It would be helpful to solve your issue.

Re: Creating hive table with columns from a text file

prabhat_ratnala — Wed, 24 May 2017 02:39:01 GMT

Hi @Bala Vignesh N V

I am getting the below error:

Logging initialized using configuration in file:/etc/hive/2.4.3.0-227/0/hive-log4j.properties
NoViableAltException(307@[])
        at org.apache.hadoop.hive.ql.parse.HiveParser.type(HiveParser.java:38618)
        at org.apache.hadoop.hive.ql.parse.HiveParser.colType(HiveParser.java:38375)
        at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameType(HiveParser.java:38059)
        at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameTypeList(HiveParser.java:36183)
        at org.apache.hadoop.hive.ql.parse.HiveParser.createTableStatement(HiveParser.java:5222)
        at org.apache.hadoop.hive.ql.parse.HiveParser.ddlStatement(HiveParser.java:2648)
        at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1658)
        at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1117)
        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:432)
        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:316)
        at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1202)
        at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1250)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1139)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1129)
        at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:216)
        at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:168)
        at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:379)
        at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:314)
        at org.apache.hadoop.hive.cli.CliDriver.processReader(CliDriver.java:412)
        at org.apache.hadoop.hive.cli.CliDriver.processFile(CliDriver.java:428)
        at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:717)
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:684)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:624)
        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 1:60 cannot recognize input near ')' 'stored' 'as' in column type

Re: Creating hive table with columns from a text file

ksuresh — Wed, 24 May 2017 05:16:33 GMT

@Prabhat Ratnala

This worked for me. 2 changes were required-

table_cols="`cat t_cols.txt`"
Remove '\' before '\${hiveconf:t_name}'

hive --hiveconf t_name="`cat t_cols.txt`" -e 'create table leap_frog_snapshot.LINKED_OBJ_TRACKING (${hiveconf:t_name}) stored as orc tblproperties ("orc.compress"="SNAPPY") ; '

Re: Creating hive table with columns from a text file

prabhat_ratnala — Wed, 24 May 2017 16:35:34 GMT

Perfect!!! Worked like a charm... Thank you