Impala - Insert Statement

The INSERT Statement of Impala has two clauses − into and overwrite. Insert statement with into clause is used to add new records into an existing table in a database.

Syntax

There are two basic syntaxes of INSERT statement as follows −

insert into table_name (column1, column2, column3,...columnN)

values (value1, value2, value3,...valueN);

Here, column1, column2,...columnN are the names of the columns in the table into which you want to insert data.

You can also add values without specifying the column names but, for that you need to make sure the order of the values is in the same order as the columns in the table as shown below.

Insert into table_name values (value1, value2, value2);

CREATE TABLE is the keyword telpng the database system to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Optionally you can specify database_name along with the table_name.

Example

Suppose we have created a table named student in Impala as shown below.

create table employee (Id INT, name STRING, age INT,address STRING, salary BIGINT);

Following is an example of creating a record in the table named employee.

[quickstart.cloudera:21000] > insert into employee 
(ID,NAME,AGE,ADDRESS,SALARY)VALUES (1,  Ramesh , 32,  Ahmedabad , 20000 );

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

Query: insert into employee (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1,  Ramesh ,
   32,  Ahmedabad , 20000 ) 
Inserted 1 row(s) in 1.32s

You can insert another record without specifying the column names as shown below.

[quickstart.cloudera:21000] > insert into employee values (2,  Khilan , 25, 
    Delhi , 15000 );

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

Query: insert into employee values (2,  Khilan , 25,  Delhi , 15000 ) 
Inserted 1 row(s) in 0.31s

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

Insert into employee values (3,  kaushik , 23,  Kota , 30000 );

Insert into employee values (4,  Chaitap , 25,  Mumbai , 35000 );

Insert into employee values (5,  Hardik , 27,  Bhopal , 40000 );

Insert into employee values (6,  Komal , 22,  MP , 32000 );

After inserting the values, the employee table in Impala will be as shown below.

+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
| 1  | Ramesh   | 32  | Ahmedabad | 20000  |
| 2  | Khilan   | 25  | Delhi     | 15000  |
| 5  | Hardik   | 27  | Bhopal    | 40000  |
| 4  | Chaitap | 25  | Mumbai    | 35000  |
| 3  | kaushik  | 23  | Kota      | 30000  |
| 6  | Komal    | 22  | MP        | 32000  |
+----+----------+-----+-----------+--------+

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 employee values (1,  Ram , 26, 
    Vishakhapatnam , 37000 );

On executing the above query, this will overwrite the table data with the specified record displaying the following message.

Query: insert overwrite employee values (1,  Ram , 26,  Vishakhapatnam , 37000 ) 
Inserted 1 row(s) in 0.31s

On verifying the table, you can observe that all the records of the table employee are overwritten by new records as shown below.

+----+------+-----+---------------+--------+
| id | name | age | address       | salary |
+----+------+-----+---------------+--------+
| 1  | Ram  | 26  | Vishakhapatnam| 37000  |
+----+------+-----+---------------+--------+

Inserting Data using Hue Browser

Open Impala Query editor and type the insert Statement in it. And cpck on the execute button as shown in the following screenshot.

After executing the query/statement, this record is added to the table.