ApsaraDB for SelectDB:Stream Load

Parameter

Required

Description

--location-trusted

Yes

If authentication is required, this passes the username and password to the server where the request is redirected.

-u

Yes

Specify the username and password for the SelectDB instance.

• username: The username.

• password: The password.

-H

No

Specify the content of the request header (Header) for this Stream Load import request. The format is as follows:

-H "key1:value1"

Common parameters are as follows:

• label: The unique ID of the import task.

• column_separator: Specifies the column delimiter in the import file. The default is \t.

  You can also use a combination of multiple characters as the column delimiter.

  For invisible characters, you need to add \x as a prefix and use hexadecimal to represent the separator.

For more information about request header parameters, see Request header parameters.

-T

Yes

Specify the path of the data file to import.

file_path: The path of the object file.

-XPUT

Yes

The method of the HTTP request. Use the PUT request method to specify the data import address of SelectDB. The specific parameters are as follows:

• host: The VPC endpoint or public endpoint of the SelectDB instance.

  • Use a public endpoint if not in the same VPC: If the device where you run the command is not in the same VPC as the target SelectDB instance, you must use a public endpoint. To apply for a public endpoint, see Apply for and release a public endpoint.

  • Use a VPC endpoint if in the same VPC: If the device where you run the command is an Alibaba Cloud product and is in the same VPC as the target SelectDB instance, we recommend that you use a VPC endpoint.

• port: The HTTP port number of the SelectDB instance. The default is 8080.

  You can view the endpoint and port number of the instance on the instance's product page in the SelectDB console.

• db_name: The database name.

• table_name: The table name.

Parameter

Description

label

The unique ID of the import task.

Label purposes:

• A custom name in the import command.

• Can be used to view the execution status of the corresponding import task.

• Can be used to prevent importing the same data repeatedly.

• Can be reused if the corresponding import job's status is CANCELLED.

format

Specifies the import data format.

• Supported formats: CSV, JSON, PARQUET, ORC, csv_with_names (skips the first row of a CSV file), and csv_with_names_and_types (skips the first two rows of a CSV file).

• Default format: CSV.

For more information about file format requirements and related parameters, see File Formats.

line_delimiter

Specifies the row delimiter in the import file.

You can also use a combination of multiple characters as a row delimiter. For example, in Windows systems, use \r\n as the row delimiter.

• Default row delimiter: \n.

• Scenario: If the imported data contains the specified row delimiter, you must specify a new row delimiter.

  For an example, see How do I handle column and row delimiters that exist in the data?.

column_separator

Specifies the column delimiter in the import file.

You can also use a combination of multiple characters as a column delimiter. For example, you can use a double vertical line || as the column delimiter.

For invisible characters, you need to add \x as a prefix and use hexadecimal to represent the separator. For example, for the Hive file separator \x01, you need to specify -H"column_separator:\x01".

• Default separator: \t.

• Scenario: If the imported data contains the specified column delimiter, you must specify a new column delimiter.

  For an example, see How do I handle column and row delimiters that exist in the data?.

compress_type

Specifies the compression format of the file. Compression is supported only for CSV and JSON files.

Supported compression formats: gz, lzo, bz2, lz4, lzop, and deflate.

max_filter_ratio

Specifies the maximum error tolerance for the import task.

When the import error rate exceeds this threshold, the import fails. To ignore error rows, you must set this parameter to a value greater than 0 to ensure the import succeeds.

• Default value: 0, which means zero tolerance.

• Value range: [0, 1].

strict_mode

Specifies whether to enable strict mode.

• false (default): Does not enable strict mode.

• true: Enables strict mode. When enabled, strict filtering is applied to column type conversions during the import process.

  • Incorrect data will be filtered out.

  • If a column type conversion of non-null source data results in a NULL value, it will also be filtered out.

cloud_cluster

Specifies the cluster to use for the import.

By default, the instance's default cluster is used. If the instance does not have a default cluster set, a cluster for which you have permissions is automatically selected.

load_to_single_tablet

Specifies whether to import data into only one tablet of the corresponding partition. This parameter can only be set when importing data into a Duplicate Key table with random bucketing.

• false (default): When importing data into a Duplicate Key model table with random bucketing, data is not written to only one bucket of the corresponding partition.

• true: When importing data into a Duplicate Key model table with random bucketing, data is written to only one bucket of the corresponding partition. This can improve the concurrency and throughput of data import.

where

Specifies the filter condition for the import task.

You can specify a where statement to filter the source data. The filtered data will not be imported or included in the filter ratio calculation. However, it will be counted in the number of rows filtered by the where condition,

num_rows_unselected.

partitions

Specifies the partition information for the data to be imported.

If the data to be imported does not belong to the specified partition, it will not be imported. These data rows are counted in dpp.abnorm.ALL.

dpp.abnorm.ALL is a counter metric in SelectDB. It represents the total number of rows filtered out during the data pre-processing stage. The NumberFilteredRows in the import result includes the number of abnormal rows counted by dpp.abnorm.ALL.

columns

Specifies the function transformation configuration for the data to be imported.

Supported function transformation methods include column order changes and expression transformations. The method for expression transformation is the same as in a search statement.

merge_type

Specifies the data merge type.

• APPEND (default): Indicates that this import is a normal append write operation.

• MERGE: Must be used with the delete parameter to mark the Delete Flag column.

• DELETE: Indicates that all data in this import are delete operations.

delete

This is only meaningful when the merge_type is set to MERGE. It specifies the condition for deleting data.

function_column.sequence_col

This is only applicable to the Unique Key model. For the same key columns, it ensures that the value columns are replaced according to the source_sequence column. The source_sequence can be a column from the data source or a column in the table schema.

exec_mem_limit

Specifies the import memory limit.

• Unit: bytes.

• Default value: 2147483648, which is 2 GiB.

timeout

Specifies the import timeout.

• Unit: seconds.

• Default value: 600.

• Range: [1, 259200].

timezone

Specifies the time zone to use for this import. This parameter affects the results of all time zone-related functions involved in the import. For more information about time zones, see the IANA Time Zone Database.

Default value: Asia/Shanghai, which is UTC+8.

two_phase_commit

Specifies whether to enable two-phase commit mode.

• false (default): Does not enable two-phase commit.

• true: Enables two-phase commit. When enabled, information is returned to the user as soon as the data write is complete. At this point, the data is not visible, and the transaction status is PRECOMMITTED. The data becomes visible only after a user manually triggers a commit operation.

• When to enable or disable:

  When enabled, data import is atomic. It either succeeds completely or fails completely. This also prevents a situation where some data becomes visible during the import process.

  Enable in these scenarios:

  • Financial transaction data: Requires strict guarantees of data integrity and consistency.

  • Billing system data: Partial data import is not allowed.

  • Critical business data: Scenarios with extremely high data accuracy requirements.

  Disable in these scenarios:

  • Log analysis: Consistency requirements are not high, and import speed is a priority.

  • Large-scale batch processing: Resources are limited, and import needs to be completed quickly.

  • Re-importable data: Data that can be re-imported if the import fails.

jsonpaths

There are two ways to import data in JSON format:

• Basic mode: You do not need to specify jsonpaths. The keys in the JSON data must correspond one-to-one with the column names in the table, but the order can be different. For example, in the JSON data {"k1":1, "k2":2, "k3":"hello"}, k1, k2, and k3 correspond to the column names in the table.

• Matching mode: When the JSON data is complex, use the parameter jsonpaths to match keys to the corresponding table columns. For example, jsonpaths:["$.status", "$.res.id", "$.res.count"] can extract nested fields from the JSON data and write them to the corresponding table. By default, the fields extracted by jsonpaths are mapped to the table columns in order.

json_root

The json_root parameter can be used to specify a child object in the JSON data as the root node for parsing.

The default value is "", which means the entire JSON object is selected as the root node.

read_json_by_line

An important parameter in Stream Load for processing JSON data. It controls how to parse an input file that contains multiple lines of JSON data.

• false (default): The entire input file is treated as a single JSON value or array. The kernel attempts to parse the entire file content as one JSON object or array.

  For example, if the file contains the following content:

  [
   {"id":1, "name":"Alice", "age":25},
   {"id":2, "name":"Bob", "age":30},
   {"id":3, "name":"Charlie", "age":35}
  ]

  The entire file content is parsed as a single JSON array.

• true: Each line of the imported data is a JSON object.

  For example, if the file contains the following content:

  {"id":1, "name":"Alice", "age":25}
  {"id":2, "name":"Bob", "age":30}
  {"id":3, "name":"Charlie", "age":35}

  Each line in the file is parsed as a JSON object.

strip_outer_array

An important parameter in Stream Load for processing JSON data. It controls how to parse JSON data that contains an outer array.

• false (default): The original structure of the JSON data is preserved. The outer array is not stripped. The entire JSON array is imported as a single record.

  For example, for the sample data [{"k1" : 1, "k2" : 2},{"k1" : 3, "k2" : 4}], if strip_outer_array is set to false, it is parsed as a single array and imported into the table.

• true: When the imported data is a JSON array, you must set strip_outer_array to true.

  For example, for the sample data [{"k1" : 1, "k2" : 2},{"k1" : 3, "k2" : 4}], if strip_outer_array is set to true, it is parsed as two data records and imported into the table.

Parameter

Description

TxnId

The transaction ID of the import.

Label

The import ID.

You can specify a custom ID or have one generated by the system.

Status

The import status. Valid values are:

• Success: The import was successful.

• Publish Timeout: The import task is complete, but the data may be visible after a delay. Do not retry.

• Label Already Exists: The Label is a duplicate. You must change the Label.

• Fail: The import failed.

ExistingJobStatus

The status of the import job corresponding to the existing Label.

This field is displayed only when the Status is Label Already Exists.

You can use this status to determine the state of the import task corresponding to the existing Label.

• RUNNING: The task is still running.

• FINISHED: The task was successful.

Message

The error message.

NumberTotalRows

The total number of rows processed.

NumberLoadedRows

The number of rows successfully imported.

NumberFilteredRows

The number of rows with unqualified data quality.

NumberUnselectedRows

The number of rows filtered by the where condition.

LoadBytes

The number of bytes imported.

LoadTimeMs

The time taken for the import.

Unit: milliseconds.

BeginTxnTimeMs

The time spent requesting the FE to start a transaction.

Unit: milliseconds.

StreamLoadPutTimeMs

The time spent requesting the FE to get the import data execution plan.

Unit: milliseconds.

ReadDataTimeMs

The time spent reading data.

Unit: milliseconds.

WriteDataTimeMs

The time spent performing the write data operation.

Unit: milliseconds.

CommitAndPublishTimeMs

The time spent requesting the FE to commit and publish the transaction.

Unit: milliseconds.

ErrorURL

If there are data quality issues, you can access this URL to view the specific error rows.