Database Reference
In-Depth Information
To remove a partition, you can use the
DROP PARTITION
clause:
ALTER TABLE MsBigData.customer DROP PARTITION(state =
'AL')
LOCATION 'hdfs://myserver/data/state/AL';
Moving a partition can be done using the
SET LOCATION
option:
ALTER TABLE MsBigData.customer PARTITION(state = 'AL')
SET LOCATION 'hdfs://myserver/data/new_state/AL';
Loading Partitioned Tables
Whenloadingdataintoapartitionedtable,youmusttellHivewhatpartition
the data belongs to. For example, to load the AL partition of the customer
table, you specify the target partition:
LOAD DATA LOCAL INPATH 'C:/MsBigData/TestData/
customers_al'
OVERWRITE INTO TABLE MsBigData.customer
PARTITION (state = 'AL');
If you want to insert data into a partition from an existing table, you must
still define the partition that is being loaded:
INSERT INTO TABLE customer
PARTITION (state = 'AL')
SELECT * FROM customer_import ci
WHERE ci.state_code = 'AL';
However, this may not work well if you have a large number of partitions.
The
INSERT INTO…SELECT
statement for each partition would have to
scan the source table for the data, which would be very inefficient. An
alternative approach is to use the
FROM…INSERT
format:
FROM customer_import ci
INSERT INTO TABLE customer
PARTITION (state = 'AL')
SELECT * WHERE ci.state_code = 'AL'
PARTITION (state = 'AK')