Hive useful commands

Regularly used common commands:

DESCRIPTION	COMMAND
Autocomplete	hive> Press Tab key Display all 436 possibilities? (y or n)If you enter y, you’ll get a long list of all the keywords
Navigation Keystrokes	Use the up arrow and down arrow keys to scroll through previous commandsCtrl+A goes to the beginning of the lineCtrl+E goes to the end of the lineDelete key will delete the character to the left of the cursor
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”; Hello World hive> ! pwd;/home/me/hiveplay (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) set hiveconf:hive.cli.print.current.db=true;
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 .hqlfile	source /unix-path/to/file/withqueries.hql;
Print Column Headers	set hive.cli.print.header=true;
Show complete details of a table	SHOW CREATE TABLE mytable; (or) DESCRIBE [FORMATTED] [db_name.]table_name[.complex_col_name …] (or) DESCRIBE EXTENDED mytable; hive> SHOW CREATE TABLE employees; OK CREATE TABLE `employees`(   `emplid` string COMMENT 'from deserializer',   `name` string COMMENT 'from deserializer',   `age` string COMMENT 'from deserializer',   `salary` string COMMENT 'from deserializer',   `dept` string COMMENT 'from deserializer') ROW FORMAT SERDE   'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' WITH SERDEPROPERTIES (   'input.regex'='(.{4})(.{35})(.{3})(.{11})(.{4})') STORED AS INPUTFORMAT   'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION   'hdfs://sandbox.hortonworks.com:8020/user/hue/tmp/fixed_employees' TBLPROPERTIES (   'COLUMN_STATS_ACCURATE'='true',   'numFiles'='0',   'totalSize'='0',   'transient_lastDdlTime'='1455035524') Time taken: 3.399 seconds, Fetched: 21 row(s)
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: >> TINYINT (1-byte signed integer, from -128 to 127) >> SMALLINT (2-byte signed integer, from -32,768 to 32,767) >> INT (4-byte signed integer, from -2,147,483,648 to 2,147,483,647) >> BIGINT (8-byte signed integer, from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807) >> FLOAT (4-byte single precision floating point number) >> DOUBLE (8-byte double precision floating point number) >> DECIMAL (or) DECIMAL(precision, scale) (Precision of 38 digits. User definable precision and scale) Date/Time Types: >> TIMESTAMP (UTC time. Format ‘YYYY-MM-DD HH:MM:SS.fffffffff’ (9 decimal place precision) Ex: ‘2012-02-03 12:34:56.123456789’ >> DATE (Format: ‘YYYY- MM- DD’ The range of values supported for the Date type is be 0000- 01- 01 to 9999- 12- 31, dependent on support by the primitive Java Date type.) String Types: >> STRING >> VARCHAR (Length specifier between 1 and 65355) >> CHAR (Fixed-length. The maximum length is fixed at 255) Misc Types: >> BOOLEAN >> BINARY Complex Types: >> arrays: ARRAY >> maps: MAP >> structs: STRUCT >> union: UNIONTYPE

Dynamic Partition
Given below are the configuration properties for dynamic partition inserts. Note by default 

dynamic partition inserts are disabled.

CONFIGURATION PROPERTY	DEFAULT	NOTE
hive.exec.dynamic.partition	false	Needs to be set to true to enable dynamic partition inserts
hive.exec.dynamic.partition.mode	strict	In strict mode, the user must specify at least one static partition in case the user accidentally overwrites all partitions, in nonstrict mode all partitions are allowed to be dynamic
hive.exec.max.dynamic.partitions.pernode	100	Maximum number of dynamic partitions allowed to be created in each mapper/reducer node
hive.exec.max.dynamic.partitions	1000	Maximum number of dynamic partitions allowed to be created in total
hive.exec.max.created.files	100000	Maximum number of HDFS files created by all mappers/reducers in a MapReduce job
hive.error.on.empty.partition	false	Whether to throw an exception if dynamic partition insert generates empty results

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 set hive.cli.print.current.db=true; set system:user.name; $ hive -i hiveproperties.txt system:user.name=yourusername hive>
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 starts with double hyphen (‐‐) followed by space and then comment description. Hive scripts have the extension .hql	$ cat hivescript.hql ‐‐ Comment line1 ‐‐ Comment line2 SELECT * FROM mytable LIMIT 3;
Executing Hive Queries from .hql 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}”;