Saving the query result in a variable

Question

I have a query whose result I would like to save in a variable. How do I do this? I tried

./hive -e "use telecom;insert overwrite local directory '/tmp/result' select avg(a) from abc;" ./hive --hiveconf MY_VAR =`cat /tmp/result/000000_0`; 

I can get the average value in MY_VAR, but it takes me to the hive CLI, which is not required and is there a way to access unix commands inside the CLI CLI?

Answer 1

Use case: the following is valid in mysql:

set @max_date: = select max (date) from some_table;
select * from some_other_table, where date> @max_date;

This is very useful for scripts that must repeatedly call this variable, since you only need to query the maximum date once, and not every time the variable is called.

HIVE support is currently not supported. (please correct me if I'm wrong! I tried to figure out how to do this all day)

My workaround is to keep the required variable in a table that is small enough to map the connection to the query in which it is used. Since a connection is a card and not a broadcast connection, this should not significantly degrade performance. For instance:

drop table if var_table exists;

create var_table as table
select max (date) as max_date from some_table;

select some_other_table. *
from some_other_table
left join var_table
where some_other_table.date> var_table.max_date;

The proposed solution @visakh is not optimal, because it stores the string "select count (1) from table_name;" not a return value and therefore will not be useful in cases where you need to call var repeatedly during a script.

Answer 2

Saving the output of a bush request in a variable and using it in another request.

In the shell, create a variable with the desired value by doing:

 var=`hive -S -e "select max(datekey) from ....;"` echo $var 

Use the value of the variable in another request to the bush:

 hive -hiveconf MID_DATE=$var -f test.hql 

Answer 3

you can use

Answer 4

You can simply achieve this using a shell script.

create shell script file: avg_op.sh

 #!/bin/sh hive -e 'use telecom;select avg(a) from abc;' > avg.txt wait value=`cat avg.txt` hive --hiveconf avgval=$value -e "set avgval;set hiveconf:avgval; use telecom; select * from abc2 where avg_var=\${hiveconf:avgval};" 

execute the .sh file

 >bash avg_op.sh 

Answer 5

try below:

$ var = $ (bush -e "select" 12 ")

$ echo $ var

12 - exit

Answer 6

If you are trying to capture a number from a Hive request or an Impala request on Linux, this can be achieved by executing the request and selecting the numbers from the regular expression.

With the hive

 max='beeline -u ${hiveConnectionUrl} -e "select max(col1) from schema_name.table_name;" | sed 's/[^0-9]*//g'' 

The main part is extracting the number from the result. In addition, if you get too large a result, you can use the --silent=true flag to disable execution, which will reduce the number of log messages.