INSERT INTO

Function

This command is used to insert the output of the SELECT statement to a Hudi table.

Syntax

INSERT INTO tableIndentifier select query;

Parameter Description

Caveats

• Insert mode: Hudi supports three insert modes for tables with primary keys. You can set hoodie.sql.insert.mode to specify the insert mode. The default value is upsert.

  hoodie.sql.insert.mode = upsert

  • In strict mode, the INSERT statement retains the primary key constraint of COW tables and is not allowed to insert duplicate records. If a record already exists during data insertion, HoodieDuplicateKeyException is thrown for COW tables. For MOR tables, the behavior in this mode is the same as that in upsert mode.
  • In non-strict mode, records are inserted to primary key tables.
  • In upsert mode, duplicate values in the primary key table are updated.
• When submitting a Spark SQL job, you can configure the following parameters to switch bulk insert as the writing method for INSERT statements.

  hoodie.sql.bulk.insert.enable = true
  hoodie.sql.insert.mode = non-strict

• Alternatively, you can set hoodie.datasource.write.operation to control the writing method of INSERT statements, with options including bulk_insert, insert, and upsert. (Note: This will override the result of the configured hoodie.sql.insert.mode).

  hoodie.datasource.write.operation = upsert

Example

insert into h0 select 1, 'a1', 20;

-- insert static partition
insert into h_p0 partition(dt = '2021-01-02') select 1, 'a1';

-- insert dynamic partition
insert into h_p0 select 1, 'a1', dt;

-- insert dynamic partition
insert into h_p1 select 1 as id, 'a1', '2021-01-03' as dt, '19' as hh;

-- insert overwrite table
insert overwrite table h0 select 1, 'a1', 20;

-- insert overwrite table with static partition
insert overwrite h_p0 partition(dt = '2021-01-02') select 1, 'a1';

-- insert overwrite table with dynamic partition
insert overwrite table h_p1 select 2 as id, 'a2', '2021-01-03' as dt, '19' as hh;

Permission Requirements

Metadata service provided by DLI

• SQL permissions:

  database	table
  None	INSERT_INTO_TABLE

• Fine-grained permission: dli:table:insertIntoTable

Metadata services provided by LakeFormation. Refer to the LakeFormation documentation for details on permission configuration.

System Response

You can confirm the job status as successful and run a QUERY statement to view the data written in the table.