question Passing parameters to multiple queries using beeline in Support Questions

Passing parameters to multiple queries using beeline

Aarth — Tue, 09 Nov 2021 03:56:28 GMT

Is it possible to send hive variables to multiple hql files/queries and execute them in a single hive session?

when I try something like this, only the first query is picking up the variables and other is returning no result.

$beeline -u "{$url}" --hivevar FROM=2020 --hivevar TO=2021 -e "$(cat loadTable1.hql loadTable2.hql)"

Where both the hqls take these two variables as parameters.

My goal is to run my hqls in parallel in one hive sessions, are there any cons to this approach?

-Thanks

Re: Passing parameters to multiple queries using beeline

Shifu — Tue, 09 Nov 2021 10:26:05 GMT

Hi @Aarth

You can add the hive properties in the HQL file so that it will take it at the session level.

Example:
beeline -u "jdbc:hive2://<FQDN:10000>" -f rajkumar.hql

Add all the required properties in the HQL file so it will be applied at the time of executing at a session level.
set hive.compute.query.using.stats=false;
set hive.fetch.task.conversion=none ;

If you are processing more than one HQL file add the needed properties to it.

If you are happy with the reply, mark it Accept as Solution

Re: Passing parameters to multiple queries using beeline

Aarth — Tue, 09 Nov 2021 11:40:39 GMT

Hive parameters are user input so it's not a fixed value that can be defined inside a hql. It must be passed as an argument. Pls advice.

Re: Passing parameters to multiple queries using beeline

ggangadharan — Wed, 19 Jan 2022 10:01:36 GMT

Hi,

beeline -e "query_string". the query string can contain multiple queries. Those queries will be executed sequentially.

In these case once loadTable1.hql completed after that loadTable2.hql will start.

One Solution -

Merge both the file as a single file and execute the target HQL by replacing hive variables like below,

[hive@node~]$ cat loadTable1.hql loadTable2.hql #HQL file 1 select * from sample1 where rownum between ${hivevar:FROM} and ${hivevar:TO}; #HQL file 2 select * from sample2 where rownum between ${hivevar:FROM} and ${hivevar:TO}; [hive@node~]$ cat loadTable1.hql loadTable2.hql > target.hql [hive@node ~]$ beeline -u "${jdbc_url}" --hivevar FROM=10 --hivevar TO=30 -f target.hql 0: jdbc:hive2> #HQL file 1 0: jdbc:hive2> select * from sample1 where rownum between ${hivevar:FROM} and ${hivevar:TO}; DEBUG : Acquired the compile lock. INFO : Compiling command(queryId=hive_20220119094048_055b8d53-a101-453f-902b-3b08b6ffd4d3): select * from sample1 where rownum between 10 and 30 . . . 0: jdbc:hive2> #HQL file 2 0: jdbc:hive2> select * from sample2 where rownum between ${hivevar:FROM} and ${hivevar:TO}; DEBUG : Acquired the compile lock. INFO : Compiling command(queryId=hive_20220119094048_9271ad34-64d1-424b-ad09-f3ca8cf630f9): select * from sample2 where rownum between 10 and 30 . . .