Inserting data into Hive tables from queries

In this recipe, you will learn how to insert data through queries into a table in Hive.

This is another variant of inserting data into a Hive table. Data can be appended into a Hive table that already contains data. Data can also be overwritten in the Hive table. Data can also be inserted into multiple tables through a single statement only. The general format of inserting data into a table from queries is as follows:

INSERT OVERWRITE TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select select_statement FROM from_statement;

Where:

• tablename: This is the name of the table

• OVERWRITE: This is used to overwrite existing data in the table

• [PARTITION (partcol1=val1]: This option is used when data needs to be inserted into a partitioned table

• [IF NOT EXISTS]: This is an optional clause

The second syntax of inserting the data into a Hive table is as follows:

INSERT INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2...