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 .hql file | 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: String Types: Misc Types: Complex Types: |
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 |
| 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}”; |
Great content useful for all the candidates of Hadoop Training who want to kick start these career in Hadoop Training field.