Loading Parquet Data into StarRocks from Local Disk or S3 Bucket

[alberttwong]

This tutorial describes how you can loading Parquet data from your remote or local (#22782) object store using the "broker load" feature.

Prerequisites

For this tutorial you need to:

• A StarRocks or CelerData database cluster.
• Download a Parquet data file.
• Access to an Object Store and upload the parquet data file.
• Create a database, a table and query the data.

A StarRocks or CelerData database cluster

This is out of scope for the tutorial.

Downloading the Sample Data File

To download the sample Parquet data file, click cities.parquet.

The Parquet data file includes sample continent data. The following is a representative example:

{
  "continent": "Europe",
  "country": {
    "city": [
      "Paris",
      "Nice",
      "Marseilles",
      "Cannes"
    ],
    "name": "France"
  }
}

Access to an Object Store and upload the parquet 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, a Table and Query the Data

The following commands create objects specifically for use with this tutorial. When you have completed the tutorial, you can drop these objects.

Step 0: Login to Database

To login into the database, you'll need the server name, host port, username and password.

mysql -P9030 -h6dihxzedg.cloud-app.celerdata.com -uadminuser -padminpasswd --prompt="StarRocks > "

Step 1: Create Database

Run the create database command.

create database testparquet;

Step 2: Create Table

Issue: We don't support the "variant" snowflake type as this time so that's why we use the "json" type. Github Issue #22781

use testparquet;
CREATE TABLE `cities`
(
    `continent` varchar(65533 ) NULL DEFAULT "" COMMENT "continent",
    `country` varchar(65533 ) NULL DEFAULT "" COMMENT "country",
    `city` JSON NULL  COMMENT "city"
)
ENGINE=OLAP
DUPLICATE KEY(`continent`)
DISTRIBUTED BY HASH(`continent`) BUCKETS 
10
;

Step 3A: Load Table with "broker load"

Issue: There is an outstanding github issue with bucket names with "." in them. Github Issue #22711
Issue: We don't support the "variant" snowflake type as this time so that's why we don't import into the "city" column. Github Issue #22781

AWS S3 and Minio

LOAD LABEL testparquet.s3upload
(
    DATA INFILE("s3a://celerdata-cities/cities.parquet")
    INTO TABLE cities
    (continent, country)
)
WITH BROKER
(
    "aws.s3.access_key" = "XXXX",
    "aws.s3.secret_key" = "YYYY",
    "aws.s3.use_instance_profile" = "false",
    "aws.s3.region" = "us-west-2"
)
;

Google Cloud Storage

LOAD LABEL testparquet.s3upload
(
    DATA INFILE("s3a://celerdata-cities/cities.parquet")
    INTO TABLE cities
    (continent, country)
)
WITH BROKER
(
    "fs.s3a.access.key" = "XXX",
    "fs.s3a.secret.key" = "XXX",
    "fs.s3a.endpoint" = "https://storage.googleapis.com/"
)
;

Azure Object Store

LOAD LABEL testparquet.s3upload
(
    DATA INFILE("s3a://celerdata-cities/cities.parquet")
    INTO TABLE cities
    (continent, country)
)
WITH BROKER
(
    "azure.blob.storage_account" = "<blob_storage_account_name>",
    "azure.blob.shared_key" = "<blob_storage_account_shared_key>"
)
;

Check the status of the "broker load" by executing the "show" command.

show load;

StarRocks > show load;
+-------+----------+-----------+---------------------+--------+----------+-----------------------------------------------------+------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JobId | Label    | State     | Progress            | Type   | Priority | EtlInfo                                             | TaskInfo                                             | ErrorMsg                                                                                                                                                                                                                                                                                                      | CreateTime          | EtlStartTime        | EtlFinishTime       | LoadStartTime       | LoadFinishTime      | TrackingSQL | JobDetails                                                                                                                                                                                                                                                           |
+-------+----------+-----------+---------------------+--------+----------+-----------------------------------------------------+------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10164 | s3upload | FINISHED  | ETL:100%; LOAD:100% | BROKER | NORMAL   | unselected.rows=0; dpp.abnorm.ALL=0; dpp.norm.ALL=3 | resource:N/A; timeout(s):14400; max_filter_ratio:0.0 | NULL                                                                                                                                                                                                                                                                                                          | 2023-05-01 22:17:24 | 2023-05-01 22:17:26 | 2023-05-01 22:17:26 | 2023-05-01 22:17:26 | 2023-05-01 22:17:27 |             | {"All backends":{"1a0ac4d8-f938-4afe-a2b8-61338e830557":[10006]},"FileNumber":1,"FileSize":866,"InternalTableLoadBytes":451,"InternalTableLoadRows":3,"ScanBytes":866,"ScanRows":3,"TaskNumber":1,"Unfinished backends":{"1a0ac4d8-f938-4afe-a2b8-61338e830557":[]}} |
+-------+----------+-----------+---------------------+--------+----------+-----------------------------------------------------+------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

If you make a mistake, just delete the "load".

CANCEL LOAD from testparquet WHERE LABEL = "s3upload"

Step 3B: Load Table using files()

In the 3.1 release of StarRocks, you can now use the files(). See https://docs.starrocks.io/en-us/latest/sql-reference/sql-functions/table-functions/files for details.

Step 3C: Load Table from Local Disk

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 xxxx1 (data infile("file:///home/disk1/zhaoheng/ssb-poc-0.10.0/ssb-poc/output/data_dir/user_behavior_sample_data.parquet") into table user_behavior format as "parquet"(UserID,ItemID,CategoryID,BehaviorType,Timestamp) ) with broker local_load properties("timeout"="3600");

Step 4: Run Query

select count(*) from cities;

The results should look like below

StarRocks > select count(*) from cities;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.12 sec)

Step 5: Clean Up

drop database testparquet;

[jaogoy]

A small mistake: at the first line, you should change "routine load" to "broker load"