Hive Table

Create Table

Create Table Statement

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name

[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[ROW FORMAT row_format]
[STORED AS file_format]

hive> CREATE TABLE IF NOT EXISTS employee ( eid int, name String,
salary String, destination String)
COMMENT 'Employee details'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

JDBC Program

  • HiveCreateTable

Load Data Statement

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename
[PARTITION (partcol1=val1, partcol2=val2 ...)]

sample.txt

1201    Gopal   45000   Technical manager
1202    Manisha 45000   Proof reader
1203    Masthanvali     40000   Technical writer
1204    Kiran   40000   Hr Admin
1205    Kranthi 30000   Op Admin

hive> LOAD DATA LOCAL INPATH '/home/hadoop/hive/sample.txt'
OVERWRITE INTO TABLE employee;

JDBC Program

  • HiveLoadData

Alter Table

Alter Table Statement

ALTER TABLE name RENAME TO new_name
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
ALTER TABLE name DROP [COLUMN] column_name
ALTER TABLE name CHANGE column_name new_name new_type
ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])

Rename To… Statement

hive> ALTER TABLE employee RENAME TO emp;

JDBC Program

  • HiveAlterRenameTo

Change Statement

Field Name Convert from Data Type Change Field Name Convert to Data Type
eid int eid int
name String ename String
salary String salary Double
designation String designation String 
hive> ALTER TABLE emp CHANGE name ename String;
hive> ALTER TABLE emp CHANGE salary salary Double;

JDBC Program

  • HiveAlterChangeColumn

Add Columns Statement

ALTER TABLE table_name 
  [PARTITION partition_spec]                 -- (Note: Hive 0.14.0 and later)
  ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
  [CASCADE|RESTRICT]                         -- (Note: Hive 1.1.0 and later)

hive> ALTER TABLE emp ADD COLUMNS (
dept STRING COMMENT 'Department name');

JDBC Program

  • HiveAlterAddColumn

Replace Statement

hive> ALTER TABLE emp REPLACE COLUMNS (empid Int,name String);

JDBC Program

  • HiveAlterReplaceColumn

Tips

show tables;
desc emp;

Drop Table

Drop Table Statement

DROP TABLE [IF EXISTS] table_name;
hive> DROP TABLE IF EXISTS employee;

JDBC Program

  • HiveDropTable

References