Inserting Data

Function

This statement is used to insert the SELECT query result or a certain data record into a table.

Syntax

• Insert the SELECT query result into a table.

  1 2	INSERT INTO [TABLE] [db_name.]table_name [PARTITION part_spec] select_statement;

  1 2	INSERT OVERWRITE TABLE [db_name.]table_name [PARTITION part_spec] select_statement;

  part_spec:  
    : (part_col_name1=val1 [, part_col_name2=val2, ...])

• Insert a data record into a table.

  1 2	INSERT INTO [TABLE] [db_name.]table_name [PARTITION part_spec] VALUES values_row [, values_row ...];

  1 2	INSERT OVERWRITE TABLE [db_name.]table_name [PARTITION part_spec] VALUES values_row [, values_row ...];

  values_row:
    : (val1 [, val2, ...])

Keyword

Precautions

• The target DLI table must exist.
• If no partition needs to be specified for dynamic partitioning, place part_spec in the SELECT statement as a common field.
• During creation of the target OBS table, only the folder path can be specified.
• The source table and the target table must have the same data types and column field quantity. Otherwise, data insertion fails.
• You are advised not to concurrently insert data into a table. If you concurrently insert data into a table, there is a possibility that conflicts occur, leading to failed data insertion.
• The INSERT INTO statement is used to add the query result to the target table.
• The INSERT OVERWRITE statement is used to overwrite existing data in the source table.
• The INSERT INTO statement can be batch executed, but the INSERT OVERWRITE statement can be batch executed only when data of different partitioned tables is inserted to different static partitions.
• The INSERT INTO and INSERT OVERWRITE statements can be executed at the same time. However, the result is unknown.
• When you insert data of the source table to the target table, you cannot import or update data of the source table.
• The dynamic INSERT OVERWRITE statement of Hive partitioned tables can overwrite the involved partition data but cannot overwrite the entire table data.
• To overwrite data in a specified partition of the datasource table, set dli.sql.dynamicPartitionOverwrite.enabled to true and run the insert overwrite statement. The default value of dli.sql.dynamicPartitionOverwrite.enabled is false, indicating that data in the entire table is overwritten. The following is an example:

  1	insert overwrite table tb1 partition(part1='v1', part2='v2') select * from ...

  

  On the DLI management console, click SQL Editor. In the upper right corner of the editing window, click Settings to configure parameters.

• You can configure the spark.sql.shuffle.partitions parameter to set the number of files to be inserted into the OBS bucket in the non-DLI table. In addition, to avoid data skew, you can add distribute by rand() to the end of the INSERT statement to increase the number of concurrent jobs. The following is an example:

  insert into table table_target select * from table_source distribute by cast(rand() * N as int);

Example

Before importing data, you must create a table. For details, see Creating an OBS Table or Creating a DLI Table.

• Insert the SELECT query result into a table.
  • Use the DataSource syntax to create a parquet partitioned table.

    CREATE TABLE data_source_tab1 (col1 INT, p1 INT, p2 INT)
      USING PARQUET PARTITIONED BY (p1, p2);

  • Insert the query result to the partition (p1 = 3, p2 = 4).

    INSERT INTO data_source_tab1 PARTITION (p1 = 3, p2 = 4)
      SELECT id FROM RANGE(1, 3);

  • Insert the new query result to the partition (p1 = 3, p2 = 4).

    INSERT OVERWRITE TABLE data_source_tab1 PARTITION (p1 = 3, p2 = 4)
      SELECT id FROM RANGE(3, 5);

• Insert a data record into a table.
  • Create a Parquet partitioned table with Hive format

    CREATE TABLE hive_serde_tab1 (col1 INT, p1 INT, p2 INT)
      USING HIVE OPTIONS(fileFormat 'PARQUET') PARTITIONED BY (p1, p2);

  • Insert two data records into the partition (p1 = 3, p2 = 4).

    INSERT INTO hive_serde_tab1 PARTITION (p1 = 3, p2 = 4)
      VALUES (1), (2);

  • Insert new data to the partition (p1 = 3, p2 = 4).

    INSERT OVERWRITE TABLE hive_serde_tab1 PARTITION (p1 = 3, p2 = 4)
      VALUES (3), (4);