Passing array of string as an argument in Hive Query (HQL)

user_4964300 0
Viewed 588

I am working on HQL where I need to pass an array of strings as an argument:

select * from table_1 where id not in ('${idListToFilter}')

I want to pass idListToFilter as an argument in Hive query. Tried using values like

idListToFilter="'1','2','3'"

but getting an exception:

NoViableAltException(340@[319:1: constant : ( ( intervalLiteral )=> intervalLiteral | Number | dateLiteral | timestampLiteral | StringLiteral | stringLiteralSequence | IntegralLiteral | NumberLiteral | charSetStringLiteral | booleanValue | KW_NULL -> TOK_NULL );])

Can someone help?

amazon-emr hive amazon-web-services
2 Answers

The IN clause requires a list or a column, not a comma-separated string. One way to do what you want is to split and explode a string, something like this:

WITH values AS (
  select explode(split('${hivevar:idListToFilter}',',')) val
)
SELECT * FROM table_1 t
WHERE t.id NOT IN (
  SELECT trim(x.val) from values x
);

... where idListFilter is passed into HQL as a simple comma-separated string, via 

$ beeline --hivevar idListToFilter="id1,id2,id3" ...
user_6387640

You could try this from beeline:

my_sql.sql

select '${hivevar:my_val}';

command from beeline

beeline -u jdbc:hive2://localhost:10000 --hivevar my_val=ARRAY['1','2','3'] -f my_sql.sql

output

+---------------+--+
|      _c0      |
+---------------+--+
| ARRAY[1,2,3]  |
+---------------+--+

or pass an string with comma separated values and then split the array

my_file.sql

select SPLIT('${hivevar:my_val}',',');

command from beeline

beeline -u jdbc:hive2://localhost:10000 --hivevar my_val="rojo, amarillo, azul" -f my_sql.sql

ouput

+-------------------------------+--+
|              _c0              |
+-------------------------------+--+
| ["rojo"," amarillo"," azul"]  |
+-------------------------------+--+
user_85714980
Related