By | April 17, 2017

Trick 1 – Running a hive query command from outside of hive CLI shell.

$ hive –e “SELECT * FROM toodey”

-e command in hive will help in running above hive query.

Trick 2 – Run the query in silent mode, this means if you don’t want to see the query processing output just use the –S in your command with –e and query

$ hive –S –e “SELECT * FROM toodey”

Trick 3 – Running or executing multiple queries. You can write multiple hive queries bunch them into single file and save it using .q or .hql extension and use –f command to run the file.

$ hive –f /my/queryfile/path/toodey.q

If you are inside the hive CLI shell use SOURCE instead if hive –f

Hive> SOURCE /my/queryfile/path/toodey.q

Remember you need to put your .q or hql file into HDFS to make it work

Tip – If you want to set some system variables before or set some dependency jar before entering into hive CLI shell, you need to use –i command with hive.

$hive –i

This will automatically load the .hiverc file. If you want to edit this file point to file location from your bash. You can set or add something like

ADD JAR /my/jarfiles/extension-ETL.jar

set hive.cli.print.current.db=true;

Tip – You can also run linux bash command into hive CLI shell, just enter into your hive CLI shell and use, “!” and then linux command. The complex linux commands might not work but the basic one will.

Hive>! ls;

Hive>! pwd;

Tip – Not only linux bash command but you can also run Hadoop dfs commands inside hive CLI shell.

Hive> dfs –ls /mydir/;

Tip – When you do the select * from table name in hive, you are only able to see result and not the column header as well so how do I see column header in hive ?

You need to set one property to get it work.

Hive> set hive.cli.print.header=true;

Hive> SELECT * FROM toodey;