Shiva's Weblog

Apache Hive

open hive shell

$ hive
hive>quit; <- to exit the hive shell
show a list of databases
hive> show databases;
show a list of tables
hive> show tables;
show details of the table
hive> describe >tablename< ;


create a table
hive> create table tablename (columnname datatype,columnname datatype,...columnname datatype)
row format delimited
fields terminated by 'delimiter'
escaped by '';
load local data
hive> load data local inpath '/mnt/local/data/file/path'
overwrite into table ;
load HDFS data
hive> load data inpath '/hdfs/data/file/path'
overwrite into table ;


create external table
hive> create external table tablename (columnname datatype,columnname datatype,...columnname datatype)
row format delimited
fields terminated by 'delimiter'
escaped by 'escape char'
location '/hdfs/data/file/path';


Create a CREATE statement for table that already exists in Hive
This is something that comes in vary handy at times
hive> SHOW CREATE TABLE tablename1;

metastore for hive:
The hive metastore stores the information about the tables, databases created in hive. The actual database is a Mysql database. Hence you need the mysql service to be running.
$ /sbin/service mysqld start( or stop or restart)
might need to use sudo like:
$ sudo /sbin/service mysqld start


 

 

Date Functions:

Date is treated as string, there is no DATE datatype in hive

UNIX_TIMESTAMP():returns Unix epoch local time
UNIX_TIMESTAMP('2000-01-01 00:00:00') returns 946713600
NIX_TIMESTAMP('2000-01-01 10:20:30','yyyy-MM-dd') returns 946713600
FROM_UNIXTIME( bigint number_of_seconds  [, string format] )
FROM_UNIXTIME( UNIX_TIMESTAMP() ) returns the current date including the time.
TO_DATE('2000-01-01 10:20:30') returns '2000-01-01'
YEAR('2000-01-01 10:20:30') returns 2000
MONTH('2000-03-01 10:20:30') returns 3
DAY('2000-03-01 10:20:30') returns 1
HOUR('2000-03-01 10:20:30') returns 10
MINUTE('2000-03-01 10:20:30') returns 20
SECOND('2000-03-01 10:20:30') returns 30
WEEKOFYEAR('2000-03-01 10:20:30') returns 9

DATEDIFF('2014-01-10', '2014-01-05')  returns 5

DATE_ADD('2014-01-10', 5) returns '2014-01-15'

DATE_SUB('2014-01-10', 5) returns ‘2014-01-05’

• Home