CODEWITHSUNDEEP
hadoophive
Venkadesh Venkat
Venkadesh Venkat

Reputation: 175

how to get hive output in variable?

How to get hive query result into one variable? In SQL we use the below query.

Declare @cnt as int Select @cnt= count(*) from tabname

The @cnt will be used throughout the procedure. I don't know how to achieve the same in HIVE. Can someone tell me how to achieve this in hive prompt and Shell prompt?

Upvotes: 0

Views: 13478

Answers (5)

Renganathan Mayavan
Renganathan Mayavan

Reputation: 66

Well, that's pretty straightforward. In your shell,

countinfo=$(hdfs dfs -cat <-directory location}->) 

**hive -hivevar countNum="$countinfo" \
     -f '/Filepath/file.hql' **

Inside your file.hql use this ${countNum} in your query.

Upvotes: 0

sunil
sunil

Reputation: 1279

It's little difficult to do it shell script.

Might I suggest you use Perl script? Cause in Perl its fairly easy.

You can do it like this in Perl script.

$query_total_recs ="SELECT COUNT(*) FROM table";

Upvotes: 0

Revan
Revan

Reputation: 551

last_processed_time=`hive -S -e "USE db; select MAX(processed_time) from job_details where event_id = 'XXXX'"`

`echo $last_processed_time
pig -useHCatalog -Dmapred.job.queue.name=highPriority -Dtez.queue.name=highPriority -x tez -param last_processed_time=$last_processed_time test.pig`

> After executing above shell , It displays the required MAX value as
> well as pass the same value to Pig as external parameter for internal
> use within pig. Hope this will be helpful to make the same case with
> hive call using 'hiveconf'

Upvotes: 2

ankitbaldua
ankitbaldua

Reputation: 273

#!/bin/bash -e
hive -e "use schema_name; INSERT OVERWRITE DIRECTORY '/tmp/ank' row format delimited 
FIELDS TERMINATED BY ',' select * from table_name limit 1;"

a=$(hdfs dfs -cat /tmp/ank/000000_0)
echo $a

This will give comma separated output of first row from table in variable a

Upvotes: 0

Renganathan Mayavan
Renganathan Mayavan

Reputation: 66

There is No straightforward way to do this....

  1. One naive way thru shell is:

    catchData=$(beeline/hive -u jdbc:hive2://$hiveServer2:10000 -n $userName -e "select count(*) from table")

But this is not effective, as it will print even the hive connect statements, info & warn statements along with the data

2. The best way would be..

INSERT OVERWRITE DIRECTORY <-directory location}->
SELECT COUNT(*) FROM TABLE

countinfo=$(hdfs dfs -cat <-directory location}->)

hope this point 2 helps...

Upvotes: 0

Related Questions