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