How to load data into hive partition table

By | April 17, 2017

How to load data into hive partition table

LOAD DATA INPATH ‘/employee/emp.txt’ OVERWRITE  INTO TABLE employee PARTITION (countryName= ‘US’, state = ‘CH’)

How to insert data into hive tables using select query

INSERT OVERWRITE TABLE employee PARTITION (countryName = ‘US’, state = ‘CH’) SELECT * FROM old_emp oemp WHERE oemp.countryName = ‘US’ AND oemp.state = ‘CH’;

If we don’t want to overwrite the data into Hive table and simply wants to append it use INTO instead of OVERWRITE

INSERT INTO TABLE employee PARTITION (countryName = ‘US’, state = ‘CH’) SELECT * FROM old_emp oemp WHERE oemp.countryName = ‘US’ AND oemp.state = ‘CH’;

Hive insert the data into multiple hive partition

FROM old_emp oemp INSERT OVERWRITE TABLE emp PARTITION (countryName = ‘US’, state = ‘CH’) SELECT * WHERE oemp.countryName = ‘US’ AND oemp.state = ‘CH’ INSERT OVERWRITE TABLE emp PARTITION (countryName = ‘US’, state = ‘CH1’) SELECT * WHERE oemp.countryName = ‘US’ AND oemp.state = ‘CH1’;

This statement of hive will allow you to scan the input data once and split it multiple ways.




Hive Dynamic partition insert example

First, we need to set hive dynamic properties to true, set the property key and value in hive prompt and some extra properties which are useful during dynamic partitions.

hive> set hive.exec.dynamic.partition=true;

hive> set hive.exec.dynamic.partition.mode=nonstrict;

hive> set hive.exec.max.dynamic.partition.pernode=50;

hive> set hive.exec.max.dynamic.partition=500;

hive> set hive.exec.max.created.files=50000;

hive> INSERT OVERWRITE TABLE employee PARTITION (country, state) SELECT …,oemp.countryName, oemp.state FROM old_emp oemp;

Hive Dynamic and Static Partition example

INSERT OVERWRITE TABLE employee PARTITION (country = ‘US’, state) SELECT …, oemp.countryName, oemp.state from old_emp oemp WHERE oemp.countryName = ‘US’;

How to export or insert data into directories from hive table

FROM old_emp oemp INSERT OVERWRITE DIRECTORY ‘/tmp/us_employee’ SELECT * WHERE oemp.countryName = ‘US’

RECOMMENDED BOOK: PROGRAMMING HIVE