Dynamic Partitions in Hive

For detailed information about “Dynamic Partitions” please click here.

In this lesson, we’ll see how to create the dynamic partition. Consider the below data for the exercise. You can download the file from here (Credits to Paul Stenning).

Town	County	Country
Beaminster	Dorset	England
Blandford Forum	Dorset	England
Bournemouth	Dorset	England
Bridport	Dorset	England
Chickerell	Dorset	England
Christchurch	Dorset	England
Dorchester	Dorset	England
Ferndown	Dorset	England

While creating the partition, we used a static value for the partition column in the Static partition. In a dynamic partition, the partition will be created dynamically, based on the value of the partition column.

Inserting data into a dynamic partitioned table is a two-step process. To get the data into partitions, we must first dump it into a staging or temporary table. When it comes to dynamic partitioned tables, only INSERT statements will work, not the “LOAD” command.

Let’s see how to implement it.

    CREATE TABLE TownsList(Name String, County String, Country String)
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ‘,’;

    LOAD DATA LOCAL INPATH ‘Desktop/Docs/Towns_List.csv’ INTO TABLE TownsList;

    CREATE TABLE TownsList_Dynamic(Name String, County String)
    PARTITIONED BY (Country String);

    INSERT INTO TownsList_Dynamic PARTITION(Country)
    SELECT * FROM TownsList;

Let’s see the outcome.

I hope you found this post useful.

Please do subscribe for more interesting updates.