Parse string variables from Unix Shell into HiveQL

Question

I have a shell script file which I parse both numeric and string variables. An example below:

Shell Script

hive --hiveconf time_1=34600 --hiveconf time_2=34588 --hiveconf message="hello_world" -f mytask.hql

Also I have a Hive query in the respective file 'mytask.hql' as follows:

HiveQL file

SELECT col1, col2, ${hiveconf:message} AS myMessage
FROM table1
WHERE trtime between ${hiveconf:time_1} and ${hiveconf:time_2};

The problem is that I want to have a column that contains the message "Hello world" or whatever the external - from Unix Shell Script - variable contains, in every line, but I got the following error:

[Error 10004]: Line xxx Invalid table alias or column reference 'hello_world': (possible column names are: col1, col2 ... (Etc.)

The output that I want to have is something like this:

try hivevar instead of hiveconf in the shell and call as ${message} in the script — serge_k
– serge_k, Commented Oct 26, 2018 at 14:20
@VamsiPrabhala I use command line. The suggestion of '${hiveconf:message}' worked. Thank you! — Vamkos
– Vamkos, Commented Oct 29, 2018 at 8:27
@octano It described the table1, no problem for this table, it is used correctly. I found the solution in the problem using the '${hiveconf:message}'. Thank you! — Vamkos
– Vamkos, Commented Oct 29, 2018 at 8:28

leftjoin · Accepted Answer · 2018-10-26 17:02:24Z

1

String constants in SQL should be quoted using single quotes: '${hiveconf:message}':

SELECT col1, col2, '${hiveconf:message}' AS myMessage
FROM table1
WHERE trtime between ${hiveconf:time_1} and ${hiveconf:time_2};

And without quotes ${hiveconf:message} is resolved in hello_world, without quotes it looks like column, not a constant, this is why you got such exception.

edited Oct 26, 2018 at 17:02

answered Oct 26, 2018 at 16:54

leftjoin

38.5k8 gold badges64 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Vamkos Over a year ago

If have an array, lets say: names=("Nick" "Helen") And I want to parse the first name from shell AND to use it in hiveql to create a table with the name mytable_'${hiveconf:names}', Could I use the same syntax?

leftjoin Over a year ago

@Vamkos it will work without quotes, and of course not with array, if you parse it in a shell as you said. You do not need quotes inside table_name.

Vamkos Over a year ago

I found that time_1 and time_2 are not parsed correctly into the HiveQL code. Do you have any idea?

leftjoin Over a year ago

@Vamkos Testing it: hive --hiveconf time_1=34600 --hiveconf time_2=34588 then in the hive:

hive> select ${hiveconf:time_1} as time_1, ${hiveconf:time_2} as time_2; OK 34600   34588 Time taken: 2.101 seconds, Fetched: 1 row(s)

leftjoin Over a year ago

@Vamkos So, it works as expected. Check your shell script, maybe it is some issue in it

|

Collectives™ on Stack Overflow

Parse string variables from Unix Shell into HiveQL

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related