STREAM LOAD

Description

CelerData provides the loading method HTTPS-based Stream Load to help you load data from a local file system or a streaming data source. After you submit a load job, CelerData synchronously runs the job, and returns the result of the job after the job finishes. You can determine whether the job is successful based on the job result.

NOTICE

You can load data into tables within CelerData only as a user who has the INSERT privilege on the tables. If you do not have the INSERT privilege, follow the instructions provided in GRANT to grant the INSERT privilege to the user that you use to connect to CelerData.

Syntax

curl --location-trusted -u <account_id>.<username>:<password> -XPUT <url>
(
    data_desc
)
[opt_properties]        

This topic uses curl as an example to describe how to load data by using Stream Load. In addition to curl, you can also use other HTTPS-compatible tools or languages to perform Stream Load. Load-related parameters are included in HTTPS request header fields. When you input these parameters, take note of the following points:

• You can use chunked transfer encoding, as demonstrated in this topic. If you do not choose chunked transfer encoding, you must input a Content-Length header field to indicate the length of content to be transferred, thereby ensuring data integrity.

  NOTE

  If you use curl to perform Stream Load, CelerData automatically adds a Content-Length header field and you do not need manually input it.

• You must add an Expect header field and specify its value as 100-continue, as in "Expect:100-continue". This helps prevent unnecessary data transfers and reduce resource overheads in case your job request is denied.

Note that in CelerData some literals are used as reserved keywords by the SQL language. Do not directly use these keywords in SQL statements. If you want to use such a keyword in an SQL statement, enclose it in a pair of backticks (`). See Keywords.

Parameters

account_id, username, and password

Specify the ID, username, and password of the account that you use to connect to CelerData.

XPUT

Specifies the HTTPS request method. This is a required parameter. Stream Load supports only the XPUT method.

url

Specifies the URL of the destination table. Syntax:

https://<host_address>:host_port>/api/<database_name>/<table_name>/_stream_load

The following table describes the parameters in the URL.

data_desc

Describes the data file that you want to load. The data_desc descriptor can include the data file's name, format, column separator, row separator, destination partitions, and column mapping against the destination table. Syntax:

-H "warehouse:<warehouse_name>"
-T <file_path>
-H "format: CSV | JSON"
-H "column_separator: <column_separator>"
-H "row_delimiter: <row_delimiter>"
-H "columns: <column1_name>[, <column2_name>, ... ]"
-H "partitions: <partition1_name>[, <partition2_name>, ...]"
-H "temporary_partitions: <temporary_partition1_name>[, <temporary_partition2_name>, ...]"
-H "jsonpaths: [ \"<json_path1>\"[, \"<json_path2>\", ...] ]"
-H "strip_outer_array:  true | false"
-H "json_root: <json_path>"

The parameters in the data_desc descriptor can be divided into three types: common parameters, CSV parameters, and JSON parameters.

Common parameters

CSV parameters

NOTE

• For CSV data, you can use a UTF-8 string, such as a comma (,), tab, or pipe (|), whose length does not exceed 50 bytes as a text delimiter.
• Null values are denoted by using \N. For example, a data file consists of three columns, and a record from that data file holds data in the first and third columns but no data in the second column. In this situation, you need to use \N in the second column to denote a null value. This means the record must be compiled as a,\N,b instead of a,,b, because a,,b denotes that the second column of the record holds an empty string.
• The format options, including skip_header, trim_space, enclose, and escape, are supported in v3.0 and later.

JSON parameters

When you load JSON data, also note that the size per JSON object cannot exceed 4 GB. If an individual JSON object in the JSON data file exceeds 4 GB in size, an error "This parser can't support a document that big." is reported.

opt_properties

Specifies some optional parameters, which are applied to the entire load job. Syntax:

-H "label: <label_name>"
-H "where: <condition1>[, <condition2>, ...]"
-H "max_filter_ratio: <num>"
-H "timeout: <num>"
-H "strict_mode: true | false"
-H "timezone: <string>"
-H "load_mem_limit: <num>"
-H "merge_condition: <column_name>"

The following table describes the optional parameters.

Column mapping

Configure column mapping for CSV data loading

If the columns of the data file can be mapped one on one in sequence to the columns of the destination table, you do not need to configure the column mapping between the data file and the destination table.

If the columns of the data file cannot be mapped one on one in sequence to the columns of the destination table, you need to use the columns parameter to configure the column mapping between the data file and the destination table. This includes the following two use cases:

• Same number of columns but different column sequence. Also, the data from the data file does not need to be computed by functions before it is loaded into the matching table columns.

  In the columns parameter, you need to specify the names of the destination table columns in the same sequence as how the data file columns are arranged.

  For example, the destination table consists of three columns, which are col1, col2, and col3 in sequence, and the data file also consists of three columns, which can be mapped to the destination table columns col3, col2, and col1 in sequence. In this case, you need to specify "columns: col3, col2, col1".

• Different number of columns and different column sequence. Also, the data from the data file needs to be computed by functions before it is loaded into the matching table columns.

  In the columns parameter, you need to specify the names of the destination table columns in the same sequence as how the data file columns are arranged and specify the functions you want to use to compute the data. Two examples are as follows:

  • The destination table consists of three columns, which are col1, col2, and col3 in sequence. The data file consists of four columns, among which the first three columns can be mapped in sequence to the destination table columns col1, col2, and col3 and the fourth column cannot be mapped to any of the destination table columns. In this case, you need to temporarily specify a name for the fourth column of the data file, and the temporary name must be different from any of the destination table column names. For example, you can specify "columns: col1, col2, col3, temp", in which the fourth column of the data file is temporarily named temp.
  • The destination table consists of three columns, which are year, month, and day in sequence. The data file consists of only one column that accommodates date and time values in yyyy-mm-dd hh:mm:ss format. In this case, you can specify "columns: col, year = year(col), month=month(col), day=day(col)", in which col is the temporary name of the data file column and the functions year = year(col), month=month(col), and day=day(col) are used to extract data from the data file column col and loads the data into the mapping destination table columns. For example, year = year(col) is used to extract the yyyy data from the data file column col and loads the data into the destination table column year.

For detailed examples, see Configure column mapping.

Configure column mapping for JSON data loading

If the keys of the JSON document have the same names as the columns of the destination table, you can load the JSON-formatted data by using the simple mode. In simple mode, you do not need to specify the jsonpaths parameter. This mode requires that the JSON-formatted data must be an object as indicated by curly brackets {}, such as {"category": 1, "author": 2, "price": "3"}. In this example, category, author, and price are key names, and these keys can be mapped one on one by name to the columns category, author, and price of the destination table.

If the keys of the JSON document have different names than the columns of the destination table, you can load the JSON-formatted data by using the matched mode. In matched mode, you need to use the jsonpaths and COLUMNS parameters to specify the column mapping between the JSON document and the destination table:

• In the jsonpaths parameter, specify the JSON keys in the sequence as how they are arranged in the JSON document.
• In the COLUMNS parameter, specify the mapping between the JSON keys and the destination table columns:
  • The column names specified in the COLUMNS parameter are mapped one on one in sequence to the JSON keys.
  • The column names specified in the COLUMNS parameter are mapped one on one by name to the destination table columns.

For examples about loading JSON-formatted data by using the matched mode, see Load JSON data using matched mode.

Return value

After the load job finishes, CelerData returns the job result in JSON format. Example:

{
    "TxnId": 1003,
    "Label": "label123",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 1000000,
    "NumberLoadedRows": 999999,
    "NumberFilteredRows": 1,
    "NumberUnselectedRows": 0,
    "LoadBytes": 40888898,
    "LoadTimeMs": 2144,
    "BeginTxnTimeMs": 0,
    "StreamLoadPlanTimeMs": 1,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 11,
    "CommitAndPublishTimeMs": 16,
}

The following table describes the parameters in the returned job result.

If the load job fails, CelerData also returns ErrorURL. Example:

{"ErrorURL": "http://172.26.195.68:8045/api/_load_error_log?file=error_log_3a4eb8421f0878a6_9a54df29fd9206be"}

ErrorURL provides a URL from which you can obtain details about unqualified data records that have been filtered out. You can specify the maximum number of unqualified data rows that can be logged by using the optional parameter log_rejected_record_num, which is set when you submit a load job.

You can run curl "url" to directly view details about the filtered-out, unqualified data records. You can also run wget "url" to export the details about these data records:

wget http://172.26.195.68:8045/api/_load_error_log?file=error_log_3a4eb8421f0878a6_9a54df29fd9206be

The exported data record details are saved to a local file with a name similar to _load_error_log?file=error_log_3a4eb8421f0878a6_9a54df29fd9206be. You can use the cat command to view the file.

Then, you can adjust the configuration of the load job, and submit the load job again.

Examples

Load CSV data

This section CSV data as an example to describe how you can employ various parameter settings and combinations to meet your diverse loading requirements.

Set timeout period

Your database mydatabase contains a table named table1. The table consists of three columns, which are col1, col2, and col3 in sequence.

Your data file example1.csv also consists of three columns, which can be mapped in sequence onto col1, col2, and col3 of table1.

If you want to load all data from example1.csv into table1 within up to 100 seconds, run the following command:

curl --location-trusted -u <username>:<password> -H "label:label1" \
    -H "Expect:100-continue" \
    -H "timeout:100" \
    -H "max_filter_ratio:0.2" \
    -T example1.csv -XPUT \
    https://<host_address>:<host_port>/api/mydatabase/table1/_stream_load

Set error tolerance

Your database mydatabase contains a table named table2. The table consists of three columns, which are col1, col2, and col3 in sequence.

Your data file example2.csv also consists of three columns, which can be mapped in sequence onto col1, col2, and col3 of table2.

If you want to load all data from example2.csv into table2 with a maximum error tolerance of 0.2, run the following command:

curl --location-trusted -u <username>:<password> -H "label:label2" \
    -H "Expect:100-continue" \
    -H "max_filter_ratio:0.2" \
    -T example2.csv -XPUT \
    https://<host_address>:<host_port>/api/mydatabase/table2/_stream_load

Configure column mapping

Your database mydatabase contains a table named table3. The table consists of three columns, which are col1, col2, and col3 in sequence.

Your data file example3.csv also consists of three columns, which can be mapped in sequence onto col2, col1, and col3 of table3.

If you want to load all data from example3.csv into table3, run the following command:

curl --location-trusted -u <username>:<password>  -H "label:label3" \
    -H "Expect:100-continue" \
    -H "columns: col2, col1, col3" \
    -T example3.csv -XPUT \
    https://<host_address>:<host_port>/api/mydatabase/table3/_stream_load

NOTE

In the preceding example, the columns of example3.csv cannot be mapped onto the columns of table3 in the same sequence as how these columns are arranged in table3. Therefore, you need to use the columns parameter to configure the column mapping between example3.csv and table3.

Set filter conditions

Your database mydatabase contains a table named table4. The table consists of three columns, which are col1, col2, and col3 in sequence.

Your data file example4.csv also consists of three columns, which can be mapped in sequence onto col1, col2, and col3 of table4.

If you want to load only the data records whose values in the first column of example4.csv are equal to 20180601 into table4, run the following command:

curl --location-trusted -u <username>:<password> -H "label:label4" \
    -H "Expect:100-continue" \
    -H "columns: col1, col2, col3]"\
    -H "where: col1 = 20180601" \
    -T example4.csv -XPUT \
    https://<host_address>:<host_port>/api/mydatabase/table4/_stream_load

NOTE

In the preceding example, example4.csv and table4 have the same number of columns that can be mapped in sequence, but you need to use the WHERE clause to specify column-based filter conditions. Therefore, you need to use the columns parameter to define temporary names for the columns of example4.csv.

Set destination partitions

Your database mydatabase contains a table named table5. The table consists of three columns, which are col1, col2, and col3 in sequence.

Your data file example5.csv also consists of three columns, which can be mapped in sequence onto col1, col2, and col3 of table5.

If you want to load all data from example5.csv into partitions p1 and p2 of table5, run the following command:

curl --location-trusted -u <username>:<password>  -H "label:label5" \
    -H "Expect:100-continue" \
    -H "partitions: p1, p2" \
    -T example5.csv -XPUT \
    https://<host_address>:<host_port>/api/mydatabase/table5/_stream_load

Set strict mode and time zone

Your database mydatabase contains a table named table6. The table consists of three columns, which are col1, col2, and col3 in sequence.

Your data file example6.csv also consists of three columns, which can be mapped in sequence onto col1, col2, and col3 of table6.

If you want to load all data from example6.csv into table6 by using the strict mode and the time zone Africa/Abidjan, run the following command:

curl --location-trusted -u <username>:<password> \
    -H "Expect:100-continue" \
    -H "strict_mode: true" \
    -H "timezone: Africa/Abidjan" \
    -T example6.csv -XPUT \
    https://<host_address>:<host_port>/api/mydatabase/table6/_stream_load

Load data into tables containing HLL-type columns

Your database mydatabase contains a table named table7. The table consists of two HLL-type columns, which are col1 and col2 in sequence.

Your data file example7.csv also consists of two columns, among which the first column can be mapped onto col1 of table7 and the second column cannot be mapped onto any column of table7. The values in the first column of example7.csv can be converted into HLL-type data by using functions before they are loaded into col1 of table7.

If you want to load data from example7.csv into table7, run the following command:

curl --location-trusted -u <username>:<password> \
    -H "Expect:100-continue" \
    -H "columns: temp1, temp2, col1=hll_hash(temp1), col2=hll_empty()" \
    -T example7.csv -XPUT \
    https://<host_address>:<host_port>/api/mydatabase/table7/_stream_load

NOTE

In the preceding example, the two columns of example7.csv are named temp1 and temp2 in sequence by using the columns parameter. Then, functions are used to convert data as follows:

• The hll_hash function is used to convert the values in temp1 of example7.csv into HLL-type data and map temp1 of example7.csv onto col1 of table7.

• The hll_empty function is used to fill the specified default value into col2 of table7.

For usage of the functions hll_hash and hll_empty, see hll_hash and hll_empty.

Load data into tables containing BITMAP-type columns

Your database mydatabase contains a table named table8. The table consists of two BITMAP-type columns, which are col1 and col2, in sequence.

Your data file example8.csv also consists of two columns, among which the first column can be mapped onto col1 of table8 and the second column cannot be mapped onto any column of table8. The values in the first column of example8.csv can be converted by using functions before they are loaded into col1 of table8.

If you want to load data from example8.csv into table8, run the following command:

curl --location-trusted -u <username>:<password> \
    -H "Expect:100-continue" \
    -H "columns: temp1, temp2, col1=to_bitmap(temp1), col2=bitmap_empty()" \
    -T example8.csv -XPUT \
    https://<host_address>:<host_port>/api/mydatabase/table8/_stream_load

NOTE

In the preceding example, the two columns of example8.csv are named temp1 and temp2 in sequence by using the columns parameter. Then, functions are used to convert data as follows:

• The to_bitmap function is used to convert the values in temp1 of example8.csv into BITMAP-type data and map temp1 of example8.csv onto col1 of table8.

• The bitmap_empty function is used to fill the specified default value into col2 of table8.

For usage of the functions to_bitmap and bitmap_empty, see to_bitmap and bitmap_empty.

Setting skip_header, trim_space, enclose, and escape

Your database mydatabase contains a table named table9. The table consists of three columns, which are col1, col2, and col3 in sequence.

Your data file example9.csv also consists of three columns, which are mapped in sequence onto col2, col1, and col3 of table13.

If you want to load all data from example9.csv into table9, with the intention of skipping the first five rows of example9.csv, removing the spaces preceding and following column separators, and setting enclose to \ and escape to \, run the following command:

curl --location-trusted -u <username>:<password> -H "label:3875" \
    -H "Expect:100-continue" \
    -H "trim_space: true" -H "skip_header: 5" \
    -H "column_separator:," -H "enclose:\"" -H "escape:\\" \
    -H "columns: col2, col1, col3" \
    -T example9.csv -XPUT \
    https://<host_address>:<host_port>/api/mydatabase/tbl9/_stream_load

Load JSON data

This section describes the parameter settings that you need to take note of when you load JSON data.

Your database mydatabase contains a table named tbl1, whose schema is as follows:

`category` varchar(512) NULL COMMENT "",`author` varchar(512) NULL COMMENT "",`title` varchar(512) NULL COMMENT "",`price` double NULL COMMENT ""

Load JSON data using simple mode

Suppose that your data file example1.json consists of the following data:

{"category":"C++","author":"avc","title":"C++ primer","price":895}

To load all data from example1.json into tbl1, run the following command:

curl --location-trusted -u <username>:<password> -H "label:label6" \
    -H "Expect:100-continue" \
    -H "format: json" \
    -T example1.json -XPUT \
    https://<host_address>:<host_port>/api/mydatabase/tbl1/_stream_load

NOTE

In the preceding example, the parameters columns and jsonpaths are not specified. Therefore, the keys in example1.json are mapped by name onto the columns of tbl1.

To increase throughput, Stream Load supports loading multiple data records all at once. Example:

[{"category":"C++","author":"avc","title":"C++ primer","price":89.5},{"category":"Java","author":"avc","title":"Effective Java","price":95},{"category":"Linux","author":"avc","title":"Linux kernel","price":195}]

Load JSON data using matched mode

CelerData performs the following steps to match and process JSON data:

1. (Optional) Strips the outermost array structure as instructed by the strip_outer_array parameter setting.

   NOTE

   This step is performed only when the outermost layer of the JSON data is an array structure as indicated by a pair of square brackets []. You need to set strip_outer_array to true.

2. (Optional) Matches the root element of the JSON data as instructed by the json_root parameter setting.

   NOTE

   This step is performed only when the JSON data has a root element. You need to specify the root element by using the json_root parameter.

3. Extracts the specified JSON data as instructed by the jsonpaths parameter setting.

Load JSON data using matched without root element specified

Suppose that your data file example2.json consists of the following data:

[{"category":"xuxb111","author":"1avc","title":"SayingsoftheCentury","price":895},{"category":"xuxb222","author":"2avc","title":"SayingsoftheCentury","price":895},{"category":"xuxb333","author":"3avc","title":"SayingsoftheCentury","price":895}]

To load only category, author, and price from example2.json, run the following command:

curl --location-trusted -u <username>:<password> -H "label:label7" \
    -H "Expect:100-continue" \
    -H "format: json" \
    -H "strip_outer_array: true" \
    -H "jsonpaths: [\"$.category\",\"$.price\",\"$.author\"]" \
    -H "columns: category, price, author" \
    -T example2.json -XPUT \
    https://<host_address>:<host_port>/api/mydatabase/tbl1/_stream_load

NOTE

In the preceding example, the outermost layer of the JSON data is an array structure as indicated by a pair of square brackets []. The array structure consists of multiple JSON objects that each represent a data record. Therefore, you need to set strip_outer_array to true to strip the outermost array structure. The key title that you do not want to load is ignored during loading.

Load JSON data using matched mode with root element specified

Suppose your data file example3.json consists of the following data:

{"id": 10001,"RECORDS":[{"category":"11","title":"SayingsoftheCentury","price":895,"timestamp":1589191587},{"category":"22","author":"2avc","price":895,"timestamp":1589191487},{"category":"33","author":"3avc","title":"SayingsoftheCentury","timestamp":1589191387}],"comments": ["3 records", "there will be 3 rows"]}

To load only category, author, and price from example3.json, run the following command:

curl --location-trusted -u <username>:<password> \
    -H "Expect:100-continue" \
    -H "format: json" \
    -H "json_root: $.RECORDS" \
    -H "strip_outer_array: true" \
    -H "jsonpaths: [\"$.category\",\"$.price\",\"$.author\"]" \
    -H "columns: category, price, author" -H "label:label8" \
    -T example3.json -XPUT \
    https://<host_address>:<host_port>/api/mydatabase/tbl1/_stream_load

NOTE

In the preceding example, the outermost layer of the JSON data is an array structure as indicated by a pair of square brackets []. The array structure consists of multiple JSON objects that each represent a data record. Therefore, you need to set strip_outer_array to true to strip the outermost array structure. The keys title and timestamp that you do not want to load are ignored during loading. Additionally, the json_root parameter is used to specify the root element, which is an array, of the JSON data.