INSERT DATA – In Apache Impala

Inserting rows into a table can happen in a variety of ways. Either supplying column values by column position, modifying column locations based on data/values while insertion, adding data from another table, overwriting data if it already exists in the table, or inserting data for a specific column(s) or partitions.

Here are some examples:

Examples:
CREATE TABLE Test1(Col1 INT, Col2 String, Col3 String);

Since we are not specifying in which column the value should be placed, the following command will insert a record depending on the column order as defined when the table was created.

INSERT INTO Test (1001,’Tahir Iqbal’,’2022-01-05′);

We specify which column the value should be placed in in the example below. The column values are scrambled, which means they are not in the same column order as in the table.

INSERT INTO Test (Col2, Col3, Col1 ) VALUES (‘Zafar Iqbal’,’2022-01-07′,1002);

Instead of supplying values in the statement itself, we try to insert data from a different table in the example below.

INSERT INTO Test
SELECT sID, sName, sAdmissionDate FROM TestRawTable;

If the provided values are already there, the command will replace them; otherwise, it will insert them.
INSERT OVERWRITE Test (Col2, Col3, Col1 ) VALUES (‘Zafar Iqbal’,’2022-01-07′,1002);

We’re going to create a table with a partition value in the example below. After that, we will attempt to enter data into it.

CREATE TABLE tbStudentData (
StudentID INT,
StudentName STRING,
AdmissionDate DATE,
strCity String)
PARTITIONED BY (strCountry STRING);

–Data can be inserted for a specific partition
INSERT INTO t1 PARTITION (strCountry=’UAE’) VALUES (1006,’Zafar Iqbal’,’2022-01-06′,’Dubai’);

–Data can be inserted from a difference source for a specific partition
INSERT INTO t1 PARTITION (strCountry=’UAE’)
SELECT sID, sName, sDate, sCity FROM studentRawData;

Hope you find this article helpful.

Please subscribe for more interesting updates.