Impala INSERT Statement – Overwriting the Data in Table

While it comes to Insert into tables and partitions in  Impala, we use Impala INSERT Statement. There is much more to learn about Impala INSERT Statement. So, let’s learn it from this article. Apart from its introduction, it includes its syntax, type as well as its example, to understand it well.

 Introduction to Impala INSERT Statement

Impala supports inserting into tables and partitions that you create with the Impala CREATE TABLE statement or pre-defined tables and partitions created through Hive.

Basically, there is two clause of Impala INSERT Statement. Such as into and overwrite.
Let us discuss both in detail;

I. INTO/Appending
According to its name, INSERT INTO syntax appends data to a table. Basically,  to add new records into an existing table in a database we use INTO syntax.

ii. OVERWRITE/replacing
Moreover, this syntax replaces the data in a table. However, the overwritten data files are deleted immediately. Also, they do not go through the HDFS trash mechanism, currently.

Syntax of Impala Insert Statements

So, the syntax for using Impala INSERT Statement is-

[with_clause]
INSERT { INTO | OVERWRITE } [TABLE] table_name
 [(column_list)]
 [ PARTITION (partition_clause)]
{
   [hint_clause] select_statement
 | VALUES (value [, value ...]) [, (value [, value ...]) ...]
}
partition_clause ::= col_name [= constant] [, col_name [= constant] ...]
hint_clause ::= [SHUFFLE] | [NOSHUFFLE]    (Note: the square brackets are part of the syntax.)

Statement Type

Impala INSERT Statement is of DML Type.

Example of Impala Insert Statements

Assume we have created a table, employee1 in Impala.
create table

employee1 (Id INT, name STRING, age INT,address STRING, salary BIGINT);

Here, is the example of creating a record in the table named employee2.

[quickstart.cloudera:21000] > insert into employee2
(ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, ' shubham', 32, 'Delhi', 20000 );

A record is inserted into the table named employee2 displaying the following message, On executing the above statement.

Query: insert into employee2 (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ' shubham',
  32, 'Delhi', 20000 )

Inserted 1 row(s) in 1.32s
Now, without specifying the column names,  we can insert another record

[quickstart.cloudera:21000] > insert into employee2 values (2, 'monika', 25,
  'mumbai', 15000 );

A record is inserted into the table named employee2 displaying the following message, on executing the above statement.

Query: insert into employee2 values (2, ‘monika’, 25, ‘mumbai’, 15000 )
Inserted 1 row(s) in 0.31s

You can insert a few more records in the employee2 table as shown below.

Insert into employee2 values (3, ‘kajal’, 23, ‘alirajpur’, 30000 );

Insert into employee2 values (4, ‘revti’, 25, ‘Indore’, 35000 );

Insert into employee2 values (5, ‘Shreyash’, 27, ‘pune’, 40000 );

Insert into employee2 values (6, ‘Mehul’, 22, ‘Hyderabad’, 32000 );
After inserting the values, the employee2 table in Impala will be as shown below.

Overwriting the Data in a Table

We can overwrite the records of a table using overwrite clause. The overwritten records will be permanently deleted from the table. Following is the syntax of using the overwrite clause.
Insert overwrite table_name values (value1, value2, value2);

• Example

Following is an example of using the clause overwrite.

[quickstart.cloudera:21000] > Insert overwrite employee2 values (1, 'Sagar', 26,
  'Rajasthan', 37000 );

This will overwrite the table data with the specified record displaying the following message on executing the above query.
Query: insert overwrite employee2 values (1, ‘Sagar’, 26, ‘Rajasthan’, 37000 )
Inserted 1 row(s) in 0.31s
We can observe that all the records of the table employee2 are overwritten by new records on verifying the table.

Inserting Data using Hue Browser

However, to insert data using Hue Browser, there are some following steps. they are

• At first, type the insert Statement in Impala Query editor. Then click on the execute button.
• Further, you will see that this record is added to the table after executing the query/statement.

Conclusion – Impala Insert Statements

As a result, we have seen the whole concept of Impala INSERT Statement. Still, if any doubt occurs, feel free to ask in the comment section.