Use Insert Into to import data - E-MapReduce

INSERT INTO loads data into Doris tables using standard SQL syntax. Each statement runs as an independent import job over the MySQL protocol and returns results synchronously.

Some information in this topic is sourced from Apache Doris. For more information, see Introduction to Apache Doris.

Syntax

Doris supports two INSERT INTO syntax forms:

Syntax	Use case
`INSERT INTO tbl SELECT ...`	Extract, transform, and load (ETL) workflows and production data pipelines
`INSERT INTO tbl (col1, col2, ...) VALUES (1, 2, ...)`	Demos and feature verification only

Important

The VALUES syntax is for demos only. Do not use it in test or production environments.

Common table expressions

When using common table expressions (CTEs) in an INSERT INTO statement, either specify WITH LABEL and the column list, or wrap the CTEs in a subquery:

-- Option 1: WITH LABEL + column list
INSERT INTO tbl1 WITH LABEL label1
WITH cte1 AS (SELECT * FROM tbl1), cte2 AS (SELECT * FROM tbl2)
SELECT k1 FROM cte1 JOIN cte2 WHERE cte1.k1 = 1;

INSERT INTO tbl1 (k1)
WITH cte1 AS (SELECT * FROM tbl1), cte2 AS (SELECT * FROM tbl2)
SELECT k1 FROM cte1 JOIN cte2 WHERE cte1.k1 = 1;

-- Option 2: Wrap CTEs in a subquery
INSERT INTO tbl1 (k1)
SELECT * FROM (
  WITH cte1 AS (SELECT * FROM tbl1), cte2 AS (SELECT * FROM tbl2)
  SELECT k1 FROM cte1 JOIN cte2 WHERE cte1.k1 = 1
) AS ret;

For full syntax details, run HELP INSERT.

Run an import job

Submit INSERT INTO statements using the MySQL protocol:

INSERT INTO tbl2 WITH LABEL label1 SELECT * FROM tbl3;
INSERT INTO tbl1 VALUES ("qweasdzxcqweasdzxc"), ("a");

Interpret the result

INSERT INTO returns a result synchronously. The format varies by outcome.

Decision logic

Result	Meaning	Next step
`ERROR 1064 (HY000)`	Import failed	Check the error URL in the message
`Query OK, 0 rows affected`	Empty result set; no data imported	—
`Query OK, N rows affected` + `status: visible`	Import successful	—
`Query OK, N rows affected` + `status: committed`	Data imported but temporarily invisible	Run `SHOW TRANSACTION` to poll for visibility
`Query OK, N rows affected` + `warnings > 0`	Some rows filtered out	Run `SHOW LOAD` to get the error URL

Empty result set

If the SELECT clause returns no rows:

mysql> INSERT INTO tbl1 SELECT * FROM empty_tbl;
Query OK, 0 rows affected (0.02 sec)

Query OK confirms the statement executed. 0 rows affected means no data was imported.

Successful import

If data is imported, Doris returns a summary line and a JSON string:

mysql> INSERT INTO tbl1 SELECT * FROM tbl2;
Query OK, 4 rows affected (0.38 sec)
{'label':'insert_8510c568-9eda-4173-9e36-6adc7d35****', 'status':'visible', 'txnId':'4005'}

mysql> INSERT INTO tbl1 WITH LABEL my_label1 SELECT * FROM tbl2;
Query OK, 4 rows affected (0.38 sec)
{'label':'my_label1', 'status':'visible', 'txnId':'4005'}

mysql> INSERT INTO tbl1 SELECT * FROM tbl2;
Query OK, 2 rows affected, 2 warnings (0.31 sec)
{'label':'insert_f0747f0e-7a35-46e2-affa-13a235f4****', 'status':'committed', 'txnId':'4005'}

The JSON fields are:

Field	Description
`label`	Import job identifier. Auto-generated if not specified with `WITH LABEL`. Unique per import job within a single database.
`status`	Data visibility: `visible` (data is readable) or `committed` (temporarily invisible; will become visible).
`txnId`	Transaction ID for the import.
`err`	Error message, if any unexpected error occurred.

Failed import

If the import fails, no data is loaded and an error is returned:

mysql> INSERT INTO tbl1 SELECT * FROM tbl2 WHERE k1 = "a";
ERROR 1064 (HY000): all partitions have no load data. url: http://10.74.xx.xx:8042/api/_load_error_log?file=__shard_2/error_log_insert_stmt_ba8bb9e158e4879-ae8de8507c0b****

The URL in the error message points to the error log for that import attempt.

Check import status

In the same session

Run SHOW LAST INSERT immediately after an INSERT INTO statement to get the result of the most recent import in the current session:

mysql> SHOW LAST INSERT\G
*************************** 1. row ***************************
    TransactionId: 640**
            Label: insert_ba8f33aea9544866-8ed77e2844d0****
         Database: default_cluster:db1
            Table: t1
TransactionStatus: VISIBLE
       LoadedRows: 2
     FilteredRows: 0

Important

SHOW LAST INSERT returns results only within the same session. If the session is closed or replaced, an empty result set is returned.

When using MySQL client libraries

Some MySQL client libraries do not expose the JSON string from INSERT INTO responses. In those cases, run SHOW LAST INSERT after each INSERT INTO statement to retrieve the result programmatically.

When rows are filtered out

If warnings > 0, run SHOW LOAD to get the URL for the error log:

SHOW LOAD WHERE label="xxx";

When status is `committed`

The committed state is temporary. Run SHOW TRANSACTION to poll until TransactionStatus becomes VISIBLE:

SHOW TRANSACTION WHERE id=4005;

Configuration

FE configuration

Parameter	Default	Description
`insert_load_default_timeout_second`	3600 (1 hour)	Timeout for all INSERT INTO jobs, in seconds. When exceeded, the job enters CANCELLED state. Applies to all jobs; no per-job override.

To increase the timeout, modify the FE configuration:

insert_load_default_timeout_second = <seconds>

Session variables

Variable	Default	Description
`enable_insert_strict`	`false`	Controls tolerance for import errors. `false`: succeeds if at least one row is imported (a label is returned even when some rows fail). `true`: fails if any row fails to import.
`query_timeout`	—	Query-level timeout in seconds. INSERT INTO statements are subject to this variable in addition to `insert_load_default_timeout_second`.

Set session variables with:

SET enable_insert_strict = true;
SET query_timeout = <seconds>;

Best practices

Choose the right syntax

Scenario	Recommended syntax
Verify Doris features with a small dataset	`INSERT INTO ... VALUES`
ETL processing within Doris (query one table, write to another)	`INSERT INTO ... SELECT`
Load data from an external source (MySQL foreign table, broker foreign table mapping Hadoop Distributed File System (HDFS))	`INSERT INTO ... SELECT`

Estimate and set the timeout

Doris imposes no data size limit on INSERT INTO imports. For large datasets, calculate whether the default 1-hour timeout is sufficient before running the job.

Use this formula as a starting point:

estimated time (s) = data size (GB) / import speed (MB/s)

Replace import speed with the actual average speed of your cluster. A common reference value is 10 MB/s, but actual throughput varies.

If the estimated time exceeds 3,600 seconds, increase insert_load_default_timeout_second on the frontend (FE) before submitting the job.

Complete example

Scenario: Import approximately 10 GB of data for the bj region from store_sales into bj_store_sales. Cluster average import speed is 5 MB/s.

Table schemas:

-- Source table
store_sales (id, total, user_id, sale_timestamp, region)

-- Target table
bj_store_sales (id, total, user_id, sale_timestamp)

Step 1. Calculate the estimated import time and adjust the timeout:

10 GB / 5 MB/s = 2000s

Set the FE configuration:

insert_load_default_timeout_second = 2000

Step 2. Run the import:

INSERT INTO bj_store_sales WITH LABEL `label`
SELECT id, total, user_id, sale_timestamp
FROM store_sales
WHERE region = "bj";