Hive CLI will soon be deprecated in favor of Beeline.
Check here for beeline commands.
Enter into hive shell by typing
$hive hive>
Hive Shell Commands
Description | Command |
---|---|
Autocomplete | hive> Press Tab key
|
Navigation Keystrokes | Use the up↑ and down↓ arrow keys to scroll through previous commands
|
Command History | Hive saves the last 100,00 lines into a file $HOME/.hivehistory |
Shell Execution | type ! followed by the command and terminate the line with a semicolon (;)hive> ! /bin/echo "Hello World"; (Note: Don’t invoke interactive commands that require user input. Shell “pipes” don’t work and neither do file “globs.” For example, ! ls *.hql; will look for a file named *.hql;, rather than all files that end with the .hql extension.) |
To Print Current DB in use | set hive.cli.print.current.db=true; (or) |
To remove current db name display in hive shell | set hiveconf:hive.cli.print.current.db=false; |
Specifying Metastore location for each user | set hive.metastore.warehouse.dir=/user/myname/hive/warehouse; |
System Namespace (provides read-write access to Java system properties) | set system:user.name; (or)set system:user.name=yourusername; |
env Namespace (provides read-only access to environment variables) | set env:HOME; |
Hadoop dfs commands inside Hive shell | Exclude hadoop keyword and end the command with semicolon(;) as below:hive> dfs -ls / ; (Note: This method of accessing hadoop commands is actually more efficient than using the hadoop dfs … equivalent at the bash shell, because the latter starts up a new JVM instance each time, whereas Hive just runs the same code in its current process.) |
Execute hive queries from a file | source /unix-path/to/file/withqueries.hql; |
Print Column Headers | SET hive.cli.print.header=true; |
Get columns names of the table | SHOW COLUMNS FROM mytable; |
Load data from a local file to the hive table | LOAD DATA LOCAL INPATH '/unix-path/myfile' INTO TABLE mytable; |
Load data from hdfs file to the hive table | LOAD DATA INPATH '/hdfs-path/myfile' INTO TABLE mytable; |
Data Types | Numeric Data Types:
Date/Time Types:
String Types:
Misc Types:
Complex Types:
|
|
|
|
|
|
Hive One Shot commands
Description | Command |
---|---|
To Print Current DB in use | $hive --hiveconf hive.cli.print.current.db=true
|
Specify a file of commands for the CLI to run as it starts, before showing you the prompt | $cat hiveproperties.txt |
Adding the -e execute Hive queries | $hive -e "SELECT * FROM mytable LIMIT 3"; |
Adding the -S for silent mode removes the OK and Time taken … lines, as well as other inessential output |
$hive -S -e "SELECT * FROM mytable LIMIT 3" |
Useful trick for finding a property name that you can’t quite remember | $hive -S -e "set" | grep warehouse_or_pattern |
Comments in Hive Scripts. Hive scripts have the extension .hql | $cat hivescript.hql
|
Executing Hive Queries from Files | $hive -f /unix-path/to/file/hivescript.hql |
Hive variables (The env namespace is useful as an alternative way to pass variable definitions to Hive) | $YEAR=2012 hive -e "SELECT * FROM mytable WHERE year = ${env:YEAR}"; |
|
|
|
|
|
|
|
Comment below if you find this blog useful.
Hello Puneetha, Nice work done by you…
I am new to this field, learning BigData, I have done M.Tech from New Horizon College, Bangalore
I saw your web page, u know lot of things, So if you can help me out with your knowledge I can also try to step into this field…
Reply with comment or mail to me…
Hi,
Very good to see this cheat sheet. Very clean and simple. Keep posting.
Thanks,
Deva
Great compilation. I appreciate it.
hello puneetha,
i have one query if you can help me .
when i run HQL from bash it returns entire log with map reduce process in console and also the error if we get it.But do u know how can i get SQL query as well in output console which executed by HQL file .
i am able to redirect those process detail in some log files but unable to find which SQL failed.
hope you got my query.
i cannot find ./hivehistory file in my hive home dir. How can i possibly find it.
cat $HOME/.hivehistory
it works… Great command… try it again brother
Awesome Work…Thanks a lot for this information…
Hi Sir
i cannot find ./hivehistory file in my hive home dir. How can i possibly find it.
Can you please please help me.
Search for .hivehistory in your machine with below command
Ideally it should be there in your user directory Ex: /home/user1/.hivehistory
Although you should be using beeline shell, instead of hive shell.
how to check whether hive.print.current.db is true or not. Can we check it?
Syntax for printing any setting in Hive
Ex: SET hive.print.current.db;
This should print the value associated with the key/setting.
Hi Puneetha, As others have already mentioned, this is a very good reference sheet on “hive”. I was wondering if you have created reference sheets on other topics. Appreciate your good work in sharing knowledge. Sri
Hello Puneetha… went through the sheet and it is really concise and clear. I have a couple of questions and would appreciate if you could provide your valuable inputs.
I am using orc format with partitioning . It results in too many files in the hive directory. I understand that the files produced is dependent on the data and if there is more data in that partition then more files will be created . I suspect that it will increase the response time. Pls suggest how should ai proceed.
The second question I believe is regarding using environment variable like hive create table location. Can I set these variables in a configure file and refer to the variable. I would like to avoid using Hivevar or Conf