How to add multiple partition in hive

By | April 17, 2017

How to add multiple partition in hive

The simple example to add partition would be

ALTER TABLE employee ADD IF NOT EXISTS

PARTITION (countryName= ‘US’) LOCATION ‘/employee/US’

PARTITION (countryName= ‘EU’) LOCATION ‘/employee/EU’

How to drop a partition in hive

ALTER TABLE employee DROP IF EXISTS PARTITION(countryName= ‘US’)

How to rename a column in hive

ALTER TABLE employee CHANGE COLUMN name empName STRING;




How to change the position of column in hive

ALTER TABLE employee CHANGE COLUMN empName name STRING AFTER countryName;

How to add multiple columns in hive

ALTER TABLE employee ADD COLUMNS (empID INT, salary FLOAT);

How to update storage properties in hive or change file format from TEXTFILE to SEQUENCEFILE

ALTER TABLE employee PARTITION (countryName= ‘US’) SET FILEFORMAT SEQUENCEFILE;

How to restrict a partition in hive from being queried or dropped.

ALTER TABLE employee PARTITION ( countryName= ‘US’) ENABLE NO_DROP;

And to restrict from querying.

ALTER TABLE employee PARTITION (countryName= ‘US’ ) ENABLE OFFLINE;

Note – This operations are only used with partition tables with non-partitioned tables it will not work and to remove these restriction use DISABLE instead of ENABLE.

RECOMMENDED BOOK: PROGRAMMING HIVE