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

**Table 1** Parameters
Parameter	Description
tableIndentifier	Name of the Hudi table.
select query	SELECT statement.

Precautions

Write mode: Hudi supports three write modes for tables with primary keys. You can set hoodie.sql.insert.mode to specify the insert mode. The default value is 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 executing a SQL statement, you can set hoodie.sql.bulk.insert.enable to true and hoodie.sql.insert.mode to non-strict to enable the bulk insert statement as the write mode of the insert statement.
You can also set hoodie.datasource.write.operation to control the write mode of the insert statement, including bulk_insert, insert, and upsert. If you use this setting method, you must run reset hoodie.datasource.write.operation; to reset the Hudi write mode after executing the SQL statement. Otherwise, this parameter may affect the execution of other SQL statements.

Examples

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;