E-MapReduce:Stream Load

Syntax

curl --location-trusted -u <username>:<password> \
     -H "Expect:100-continue" \
     -XPUT <url> \
     (data_desc) \
     [opt_properties]

Include Expect:100-continue in the request header. This prevents unnecessary data transfer if the server rejects the task before the payload is sent.

URL

http://<fe_host>:<fe_http_port>/api/<database_name>/<table_name>/_stream_load

Source data parameters

The data_desc block describes the source file and controls how StarRocks maps and filters the data.

Load task options

opt_properties are optional settings that apply to the entire load task.

-H "label: <label_name>"
-H "where: <condition>"
-H "max_filter_ratio: <num>"
-H "timeout: <num>"
-H "strict_mode: true | false"
-H "timezone: <string>"
-H "load_mem_limit: <num>"
-H "partial_update: true | false"
-H "partial_update_mode: row | column"
-H "merge_condition: <column_name>"

In StarRocks SQL, reserved keywords must be enclosed in backticks. See Keywords.

Load status

Check error details

When Status is Fail, use ErrorURL to retrieve details about filtered rows:

# View error rows directly
curl "<ErrorURL>"

# Save error rows to a local file
wget "<ErrorURL>"

Example:

wget "http://172.17.**.**:18040/api/_load_error_log?file=error_log_b74dccdcf0ceb4de_e82b2709c6c013ad"

Load CSV data

This example loads data.csv into example_table in the load_test database:

curl --location-trusted -u "root:" \
     -H "Expect:100-continue" \
     -H "label:label2" \
     -H "column_separator:," \
     -T data.csv -XPUT \
     http://172.17.**.**:18030/api/load_test/example_table/_stream_load

Load JSON data

This example loads JSON records from json.data:

curl --location-trusted -u "root:" \
     -H "Expect:100-continue" \
     -H "label:label2" \
     -H "format:json" \
     -T json.data -XPUT \
     http://172.17.**.**:18030/api/load_test/example_table/_stream_load

Set error tolerance

To allow up to 20% of rows to be filtered out due to data quality issues:

curl --location-trusted -u "root:" \
     -H "Expect:100-continue" \
     -H "label:label3" \
     -H "column_separator:," \
     -H "max_filter_ratio:0.2" \
     -T data.csv -XPUT \
     http://172.17.**.**:18030/api/load_test/example_table/_stream_load

Filter rows by condition

To load only rows where k1 = 20180601:

curl --location-trusted -u "root:" \
     -H "Expect:100-continue" \
     -H "label:label4" \
     -H "column_separator:," \
     -H "where:k1 = 20180601" \
     -T data.csv -XPUT \
     http://172.17.**.**:18030/api/load_test/example_table/_stream_load

Configure column mapping

When the source file column order does not match the table schema, use the columns parameter to specify the mapping.

Example 1: The table has columns c1, c2, c3. The source file columns are in the order c3, c2, c1:

curl --location-trusted -u "root:" \
     -H "Expect:100-continue" \
     -H "label:label5" \
     -H "column_separator:," \
     -H "columns:c3, c2, c1" \
     -T data.csv -XPUT \
     http://172.17.**.**:18030/api/load_test/example_table/_stream_load

Example 2: The source file has an extra column that does not exist in the table. Use a placeholder for the extra column:

curl --location-trusted -u "root:" \
     -H "Expect:100-continue" \
     -H "label:label6" \
     -H "column_separator:," \
     -H "columns:c1, c2, c3, temp" \
     -T data.csv -XPUT \
     http://172.17.**.**:18030/api/load_test/example_table/_stream_load

Example 3: The table has columns year, month, day. The source file has a single timestamp column in 2018-06-01 01:02:03 format. Use derived column expressions:

curl --location-trusted -u "root:" \
     -H "Expect:100-continue" \
     -H "label:label7" \
     -H "column_separator:," \
     -H "columns:col, year=year(col), month=month(col), day=day(col)" \
     -T data.csv -XPUT \
     http://172.17.**.**:18030/api/load_test/example_table/_stream_load

Load compressed data

To load an LZ4-compressed JSON file:

curl --location-trusted -u "root:" \
     -H "Expect:100-continue" \
     -H "label:label8" \
     -H "format:json" \
     -H "compression:lz4_frame" \
     -T data.json.lz4 -XPUT \
     http://172.17.**.**:18030/api/load_test/example_table/_stream_load

Step 1: Create the target table

1. Log in to the master node of the StarRocks cluster using SSH. For details, see Log on to a cluster.

2. Connect to the StarRocks cluster using a MySQL client:

   mysql -h127.0.0.1 -P 9030 -uroot

3. Create the database and table:

   CREATE DATABASE IF NOT EXISTS load_test;
   USE load_test;
   
   CREATE TABLE IF NOT EXISTS example_table (
       id INT,
       name VARCHAR(50),
       age INT
   )
   DUPLICATE KEY(id)
   DISTRIBUTED BY HASH(id) BUCKETS 3
   PROPERTIES (
       "replication_num" = "1"  -- Set the number of replicas to 1.
   );

   Press Ctrl+D to exit the MySQL client.

Step 2: Prepare the source data

id,name,age
1,Alice,25
2,Bob,30
3,Charlie,35

{"id":1,"name":"Emily","age":25}
{"id":2,"name":"Benjamin","age":35}
{"id":3,"name":"Olivia","age":28}
{"id":4,"name":"Alexander","age":60}
{"id":5,"name":"Ava","age":17}

Step 3: Run the load task

curl --location-trusted -u "root:" \
     -H "Expect:100-continue" \
     -H "label:label1" \
     -H "column_separator:," \
     -T data.csv -XPUT \
     http://172.17.**.**:18030/api/load_test/example_table/_stream_load

curl --location-trusted -u "root:" \
     -H "Expect:100-continue" \
     -H "label:label2" \
     -H "format:json" \
     -T json.data -XPUT \
     http://172.17.**.**:18030/api/load_test/example_table/_stream_load