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** Parameter descriptions
Parameter	Description
tableIndentifier	Name of the Hudi table.
select query	SELECT statement.

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;