Impala INSERT Statement – Overwriting the Data in Table

1.Impala Insert Statement – Objective

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.

Impala Insert statement

Introduction to Impala INSERT Statement

 2. 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.

If these professionals can make a switch to Big Data, so can you:
Rahul Doddamani Story - DataFlair
Rahul Doddamani
Java → Big Data Consultant, JDA
Follow on
Mritunjay Singh Success Story - DataFlair
Mritunjay Singh
PeopleSoft → Big Data Architect, Hexaware
Follow on
Rahul Doddamani Success Story - DataFlair
Rahul Doddamani
Big Data Consultant, JDA
Follow on
I got placed, scored 100% hike, and transformed my career with DataFlair
Enroll now
Deepika Khadri Success Story - DataFlair
Deepika Khadri
SQL → Big Data Engineer, IBM
Follow on
DataFlair Web Services
You could be next!
Enroll now

3. 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.)

4. Statement Type

Impala INSERT Statement is of DML Type.

5. 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

Join DataFlair on Telegram
[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.

idnameageaddresssalary
1Shubham32Delhi 20000
2Monika25Mumbai15000
3kajal27Alirajpur40000
4revti25inodore35000
5shreyash23pune 30000
6mehul22Heydrabad32000

6. 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.

id name ageaddresssalary
1Sagar26Rajasthan37000
Hadoop Quiz

7. 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.

8. 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.

Reference for Impala

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.