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)

    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.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s