Loading CSV Data into StarRocks from Local Disk or from S3 Bucket

[alberttwong]

This tutorial describes how you can loading CSV data using the "routine load" feature.

Prerequisites

For this tutorial you need to:

• A StarRocks or CelerData database cluster.
• Create the Sample CSV Data File.
• Access to an Object Store and upload the CSV data file.
• Create a Database and Create a Database Table
• Load the CSV file using HTTP Put
• Load the CSV file using broker
• Load the single record using HTTP Put
• Load the data from S3

A StarRocks or CelerData database cluster

This is out of scope for the tutorial.

Create the Sample CSV Data File

Create a example.csv file with the following data

1,Lily,23
2,Rose,23
3,Alice,24
4,Julia,25

Access to an Object Store and upload the CSV data file

This is out of scope for the tutorial. Please store the URI to the file and any credential needed to login for future usage.

Create a Database and Create a Database Table

create database demo;
CREATE TABLE `table1`
(
   `id` int(11) NOT NULL COMMENT "user ID",
    `name` varchar(65533) NULL COMMENT "user name",
    `score` int(11) NOT NULL COMMENT "user score"
)
ENGINE=OLAP
PRIMARY KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10;

Output should look like:

StarRocks > create database demo;
Query OK, 0 rows affected (0.04 sec)
StarRocks > use demo;
Database changed
StarRocks > CREATE TABLE `table1`
    -> (
    ->    `id` int(11) NOT NULL COMMENT "user ID",
    ->     `name` varchar(65533) NULL COMMENT "user name",
    ->     `score` int(11) NOT NULL COMMENT "user score"
    -> )
    -> ENGINE=OLAP
    -> PRIMARY KEY(`id`)
    -> DISTRIBUTED BY HASH(`id`) BUCKETS 10;
Query OK, 0 rows affected (0.10 sec)

Load the CSV file using HTTP

curl --location-trusted --user admin -H "label:csvload" \
    -H "column_separator:," \
    -H "columns: id, name, score" \
    -T example1.csv -XPUT \
    https://6dihxzedg.cloud-app.celerdata.com/api/demo/table1/_stream_load

Here's how the output would look:

atwong@Alberts-MacBook-Pro Downloads % curl --location-trusted --user admin -H "label:csvload" \
    -H "column_separator:," \
    -H "columns: id, name, score" \
    -T example1.csv -XPUT \
    https://6dihxzedg.cloud-app.celerdata.com/api/demo/table1/_stream_load
Enter host password for user 'admin':
{
    "TxnId": 11075,
    "Label": "csvload",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 4,
    "NumberLoadedRows": 4,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 41,
    "LoadTimeMs": 99,
    "BeginTxnTimeMs": 7,
    "StreamLoadPlanTimeMs": 37,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 27,
    "CommitAndPublishTimeMs": 26
}%

Load the CSV file using broker

Here's an example of the sql statements needed. You'll need an environment with the broker in it. In situations with docker, you may need to copy the file into the container using docker cp.

alter system add broker local_load "172.26.199.40:8000";
load label abc(data infile("file:///home/disk1/sr/*") into table t1 format as "csv") with broker local_load properties("timeout"="3600");

Load the single record using HTTP

    curl --location-trusted --user admin -H "label:csvload" \
    -H "Expect:100-continue" \
    -H "column_separator:," \
    -H "columns: id, name, score" \
    --data '5,Albert,30' -XPUT \
    https://6dihxzedg.cloud-app.celerdata.com/api/demo/table1/_stream_load

Here's how the output would look:

atwong@Alberts-MacBook-Pro sandbox %     curl --location-trusted --user admin -H "label:csvload" \
    -H "Expect:100-continue" \
    -H "column_separator:," \
    -H "columns: id, name, score" \
    --data '5,Albert,30' -XPUT \
    https://6dihxzedg.cloud-app.celerdata.com/api/demo/table1/_stream_load
Enter host password for user 'admin':
{
    "TxnId": 20033,
    "Label": "csvload",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 1,
    "NumberLoadedRows": 1,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 11,
    "LoadTimeMs": 38,
    "BeginTxnTimeMs": 1,
    "StreamLoadPlanTimeMs": 3,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 16,
    "CommitAndPublishTimeMs": 17
}%

Load the data from S3

Since you need an S3 bucket, you can either host one locally using software like Minio (#22782) or you can use a cloud service like AWS S3.

LOAD LABEL s3upload
(
    DATA INFILE("s3a://s3loadtable1/example1.csv")
    INTO TABLE table1
    COLUMNS TERMINATED BY ","
    (id, name, score)

)
WITH BROKER
(
    "aws.s3.access_key" = "XXX",
    "aws.s3.secret_key" = "YYYYYY",
    "aws.s3.use_instance_profile" = "false",
    "aws.s3.region" = "us-west-2"
);

Here is the output:

StarRocks > LOAD LABEL s3upload (     DATA INFILE("s3a://s3loadtable1/example1.csv")     INTO TABLE table1     COLUMNS TERMINATED BY ","     (id, name, score)  ) WITH BROKER (     "aws.s3.access_key" = "XXXX",     "aws.s3.secret_key" = "YYYYY",     "aws.s3.use_instance_profile" = "false",     "aws.s3.region" = "us-west-2" );
Query OK, 0 rows affected (0.03 sec)

StarRocks > show load;
+-------+---------------------------------------------+-----------+---------------------+--------+----------+---------+------------------------------------------------------+--------------------------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JobId | Label                                       | State     | Progress            | Type   | Priority | EtlInfo | TaskInfo                                             | ErrorMsg                                                     | CreateTime          | EtlStartTime        | EtlFinishTime       | LoadStartTime       | LoadFinishTime      | URL  | JobDetails                                                                                                                                                                                                                                                                 |
+-------+---------------------------------------------+-----------+---------------------+--------+----------+---------+------------------------------------------------------+--------------------------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+                                                                             |
| 41139 | s3upload                                    | PENDING   | ETL:0%; LOAD:0%     | BROKER | NORMAL   | NULL    | resource:N/A; timeout(s):14400; max_filter_ratio:0.0 | NULL                                                         | 2023-05-17 14:59:03 | NULL                | NULL                | NULL                | NULL                | NULL | {"All backends":{},"FileNumber":0,"FileSize":0,"InternalTableLoadBytes":0,"InternalTableLoadRows":0,"ScanBytes":0,"ScanRows":0,"TaskNumber":0,"Unfinished backends":{}}                                                                                                    |
+-------+---------------------------------------------+-----------+---------------------+--------+----------+---------+------------------------------------------------------+--------------------------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.03 sec)

StarRocks > show load;
+-------+---------------------------------------------+-----------+---------------------+--------+----------+-----------------------------------------------------+------------------------------------------------------+--------------------------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JobId | Label                                       | State     | Progress            | Type   | Priority | EtlInfo                                             | TaskInfo                                             | ErrorMsg                                                     | CreateTime          | EtlStartTime        | EtlFinishTime       | LoadStartTime       | LoadFinishTime      | URL  | JobDetails                                                                                                                                                                                                                                                                 |
+-------+---------------------------------------------+-----------+---------------------+--------+----------+-----------------------------------------------------+------------------------------------------------------+--------------------------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 41139 | s3upload                                    | FINISHED  | ETL:100%; LOAD:100% | BROKER | NORMAL   | unselected.rows=0; dpp.abnorm.ALL=0; dpp.norm.ALL=4 | resource:N/A; timeout(s):14400; max_filter_ratio:0.0 | NULL                                                         | 2023-05-17 14:59:03 | 2023-05-17 14:59:07 | 2023-05-17 14:59:07 | 2023-05-17 14:59:07 | 2023-05-17 14:59:07 | NULL | {"All backends":{"4f166198-c413-4f5d-bf14-1d0448068b8d":[10006]},"FileNumber":1,"FileSize":41,"InternalTableLoadBytes":126,"InternalTableLoadRows":4,"ScanBytes":41,"ScanRows":4,"TaskNumber":1,"Unfinished backends":{"4f166198-c413-4f5d-bf14-1d0448068b8d":[]}}         |
+-------+---------------------------------------------+-----------+---------------------+--------+----------+-----------------------------------------------------+------------------------------------------------------+--------------------------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.03 sec)

StarRocks > select * from table1;
+------+-------+-------+
| id   | name  | score |
+------+-------+-------+
|    4 | Julia |    25 |
|    1 | Lily  |    23 |
|    3 | Alice |    24 |
|    2 | Rose  |    23 |
+------+-------+-------+
4 rows in set (0.05 sec)

StarRocks >

More information at https://docs.starrocks.io/en-us/latest/loading/StreamLoad