Impala Insert statement

Impala's INSERT statement has two clauses - into and overwrite. T he Insert statementwith into clause is used to add new records to an existing table in the database.

Grammar

Insert statements have two basic syntaxes, as follows:

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

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

Here, column1, column2,... T he name of the column in the table in which the column N is to insert the data.

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

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

CREATE TABLE is a keyword that tells the database system to create a new table. T he unique name or identifier of the table is after the CREATE TABLE statement. Optionally, you can specify database_name and table_name.

Cases

Let's say we created a table called student in Impala, as shown below.

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

The following is an example of creating a record in a table called employee.

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

When you execute the above statement, the record is inserted into a table named employee and the following message is displayed.

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 name, as shown below.

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

When you execute the above statement, the record is inserted into a table named employee and the following message is displayed.

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

You can insert more records into the employee table, as shown below.

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

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

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

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

After the value is inserted, the employee table in Impala looks like this.

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

Override the data in the table

We can use the override clause to override the record of the table. O verwritten records are permanently deleted from the table. T he following is the syntax for using the overwrite clause.

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

Cases

The following is an example of using clause overrides.

[quickstart.cloudera:21000] > Insert overwrite employee values (1, 'Ram', 26, 
   'Vishakhapatnam', 37000 );

When you execute the query above, this overrides the table data, and the specified record displays the following message.

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

When you validate a table, you can observe that all records in the table employee are overwritten by the new record, as shown below.

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

Use The Hue browser to insert data

Open the Impala query editor and type the insert statement in it. T hen click the execute button, as shown in the screenshot below.

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