Hive Commands

By | September 3, 2014

Run hive one shot command in background

$nohup hive -f sample.hql > output1.out 2>&1 & 
$nohup hive --database "default" -e "select * from tablename;" > output1.out 2>&1 & 

Replace delimiter in hive output from default delimiter to the character you wish ( In this example I am replacing it with comma(,)

hive --database "database_name" -f query.hql  2> err.txt | sed 's/[\t]/,/g' 1> output.txt 

Reference

Print only columns headers delimited by comma(,) and convert everything to uppercase

hive --database "default" -e "SHOW COLUMNS FROM table_name;" | tr '[:lower:]' '[:upper:]' | tr '\n' ',' 1> headers.txt
(or)
hive --database "default" -e "SET hive.cli.print.header=true; select * from table_name limit 0;" | tr '[:lower:]' '[:upper:]' | sed 's/[\t]/,/g'  1> headers.txt

Extract the take taken for the hive query


$nohup hive -e "select * from tablename;" 2> err.txt 1> out.txt &  
(or)
$hive -f query.hql 2> err.txt 1> out.txt  
(or)
$hive -e "select * from tablename;" 2> err.txt 1> out.txt 

$cat err.txt
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job =
.....
OK
Time taken: 15.602 seconds, Fetched: 5 row(s)

#To get execution time of the query
$cat err.txt | grep "Time taken:" | awk '{print }'
15.602

#To get execution time and number of rows fetched by the query:</strong>
$cat err.txt | grep "Time taken:" | awk '{print ,}'
15.602 5

Note: err.txt contains the stderr, out.txt contains the output of the query.
For more information on I/O Redirection, check out here

Comment below if you find this blog useful.

2 thoughts on “Hive Commands

  1. Kapil Sharma

    Hi Puneetha,
    can u please help me in suming up all the SET command used in hive or hadoop and their uses
    example : SET hive.cli.print.header=true

    thanks a lot in advance

    -Kapil Sharma

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *