Hive Partition External Tables

To create partitions on external tables in Hive is a bit tricky, as it is different from creating partitions on normal tables. To do it, you have to:

1. create the external table first
2. load data with partition info
3. load other data in with different partition key.

# create table with partition column first
CREATE EXTERNAL TABLE table1 ... PARTITIONED BY (p int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

# then load the data in with a specific partition information
ALTER TABLE table1 ADD PARTITION (p=1) LOCATION 'hdfs://';

# and then insert other data with different partition key
INSERT OVERWRITE TABLE table1 PARTITION (p=0) SELECT * FROM some_table;

If you try to load data while creating the table itself will result no data loaded because the number of columns are not matching with the ones in the file (extra partition column).