Hive Insert into Partition Table and Examples

The Hive INSERT command is used to insert data into Hive table already created using CREATE TABLE command. Inserting data into partition table is a bit different compared to normal insert or relation database insert command. There are many ways that you can use to insert data into a partitioned table in Hive. In this article, we will check Hive insert into Partition table and some examples.

Hive Insert into Partition Table

As mentioned earlier, inserting data into a partitioned Hive table is quite different compared to relational databases. You must specify the partition column in your insert command.

Below are the some methods that you can use when inserting data into a partitioned table in Hive.

• Insert into Hive partitioned Table using Values clause
• Inserting data into Hive Partition Table using SELECT clause
• Named insert data into Hive Partition Table

Let us discuss these different insert methods in detail.

Insert into Hive partitioned Table using Values Clause

This is one of the easiest methods to insert into a Hive partitioned table. You need to specify the partition column with values and the remaining records in the VALUES clause.

For example, below example demonstrates Insert into Hive partitioned Table using values clause.

Create sample table for demo.

CREATE TABLE insert_partition_demo (   
   id int,
   name varchar(10) )
 PARTITIONED BY (
   dept int)
 CLUSTERED BY (
   id)
 INTO 10 BUCKETS
 STORED AS ORC TBLPROPERTIES ('orc.compress'='ZLIB','transactional'='true'); 

Insert records into a Partitioned table using VALUES clause.

INSERT INTO insert_partition_demo PARTITION(dept=1) VALUES (1, 'abc');

Inserting data into Hive Partition Table using SELECT Clause

Things get a little more interesting when you want to use the SELECT clause to insert data into a partitioned table. Apache Hive will dynamically choose the values from select clause columns that you specify in partition clause.

For example, below command will use SELECT clause to get values from a table.

INSERT INTO insert_partition_demo PARTITION(dept) SELECT * FROM( SELECT  1 as id, 'bcd' as name, 1 as dept ) dual;

Related Articles

• Hive Insert from Select Statement and Examples

Named insert data into Hive Partition Table

Named insert is nothing but provide column names in the INSERT INTO clause to insert data into a particular column.

For example. consider below named insertion command.

INSERT INTO insert_partition_demo PARTITION(dept=1) (id, name)  VALUES (1, 'abc');

As you can see, you need to provide column names soon after PARTITION clause to name the columns in the source table.

Related Articles

• Hive Create Table Command and Examples
• Hadoop Hive Table Dynamic Partition and Examples
• Export Hive Query Output into Local Directory using INSERT OVERWRITE
• Apache Hive DUAL Table Support and Alternative
• How to Update or Drop Hive Partition? Steps and Examples

Hope this helps 🙂