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
|
