Loading JSON Data into StarRocks from Local Disk, from S3 Bucket or Postman

[alberttwong]

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

Prerequisites

For this tutorial you need to:

• A StarRocks or CelerData database cluster.
• Create the Sample JSON Data File.
• Access to an Object Store and upload the JSON data file.
• Create a Database and Create a Database Table
• Load the json file from HTTP
• Load the json from HTTP
• Load the JSON file from S3
• Load JSON data using Postman

A StarRocks or CelerData database cluster

This is out of scope for the tutorial.

Create the Sample CSV Data File

Create a example2.json file with the following data

{"name": "Beijing", "code":2}

And a example3.json

{"name": "Paris", "code":5}

Access to an Object Store and upload the JSON data file

This is out of scope for the tutorial. Upload the example3.json file. 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 `table2`
(
    `id` int(
11
) NOT NULL COMMENT "city ID",
    `city` varchar(
65533
) NULL COMMENT "city name"
)
ENGINE=OLAP
PRIMARY KEY(`id`)
DISTRIBUTED BY HASH(`id`);

Load the json file from HTTP

So in this example, we're going to do transformation/functions during the loading. You'll notice that the json file keys don't match up with the database. First we're going to do jsonpaths to map json fields to database columns. You'll notice that "name" field will match with column "city" however I mapped "code" field to "tmp_id" column that doesn't exist. I'm actually assigning the "code" field to a variable. At the end of the column when everything has been mapped, I now assign the real "id". See the picture for a visual representation of the assignment.

curl -v --location-trusted -u adminuser:adminpassword -H "strict_mode: true" \
    -H "Expect:100-continue" \
    -H "format: json" -H "jsonpaths: [\"$.name\", \"$.code\"]" \
    -H "columns: city,tmp_id, id = tmp_id * 100" \
    -T example2.json -XPUT \
    https://6dihxzedg.cloud-app.celerdata.com/api/demo/table2/_stream_load

Here is the output.

atwong@Alberts-MacBook-Pro sandbox % curl -v --location-trusted -u adminuser:adminpassword -H "strict_mode: true" \
    -H "Expect:100-continue" \
    -H "format: json" -H "jsonpaths: [\"$.name\", \"$.code\"]" \
    -H "columns: city,tmp_id, id = tmp_id * 100" \
    -T example2.json -XPUT \
    https://6dihxzedg.cloud-app.celerdata.com/api/demo/table2/_stream_load
Note: Unnecessary use of -X or --request, PUT is already inferred.
*   Trying 35.161.69.10:443...
* Connected to 6dihxzedg.cloud-app.celerdata.com (35.161.69.10) port 443 (#0)
* ALPN: offers h2,http/1.1
* (304) (OUT), TLS handshake, Client hello (1):
*  CAfile: /etc/ssl/cert.pem
*  CApath: none
* (304) (IN), TLS handshake, Server hello (2):
* TLSv1.2 (IN), TLS handshake, Certificate (11):
* TLSv1.2 (IN), TLS handshake, Server key exchange (12):
* TLSv1.2 (IN), TLS handshake, Server finished (14):
* TLSv1.2 (OUT), TLS handshake, Client key exchange (16):
* TLSv1.2 (OUT), TLS change cipher, Change cipher spec (1):
* TLSv1.2 (OUT), TLS handshake, Finished (20):
* TLSv1.2 (IN), TLS change cipher, Change cipher spec (1):
* TLSv1.2 (IN), TLS handshake, Finished (20):
* SSL connection using TLSv1.2 / ECDHE-RSA-AES128-GCM-SHA256
* ALPN: server accepted http/1.1
* Server certificate:
*  subject: CN=6dihxzedg.cloud-app.celerdata.com
*  start date: Apr  3 22:57:46 2023 GMT
*  expire date: Jul  2 22:57:45 2023 GMT
*  subjectAltName: host "6dihxzedg.cloud-app.celerdata.com" matched cert's "6dihxzedg.cloud-app.celerdata.com"
*  issuer: C=US; O=Let's Encrypt; CN=R3
*  SSL certificate verify ok.
* using HTTP/1.1
* Server auth using Basic with user 'admin'
> PUT /api/demo/table2/_stream_load HTTP/1.1
> Host: 6dihxzedg.cloud-app.celerdata.com
> Authorization: Basic YWRtaW46YWRtaW4=
> User-Agent: curl/7.88.1
> Accept: */*
> strict_mode: true
> Expect:100-continue
> format: json
> jsonpaths: ["$.name", "$.code"]
> columns: city,tmp_id, id = tmp_id * 100
> Content-Length: 30
>
< HTTP/1.1 100 Continue
* We are completely uploaded and fine
< HTTP/1.1 200 OK
< Server: nginx/1.14.1
< Date: Fri, 16 Jun 2023 17:29:18 GMT
< Content-Type: text/html; charset=ISO-8859-1
< Transfer-Encoding: chunked
< Connection: keep-alive
<
{
    "TxnId": 20021,
    "Label": "bcee20ef-7fe3-4bf9-9170-d00e92257083",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 1,
    "NumberLoadedRows": 1,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 30,
    "LoadTimeMs": 151,
    "BeginTxnTimeMs": 15,
    "StreamLoadPlanTimeMs": 61,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 34,
    "CommitAndPublishTimeMs": 41
* Connection #0 to host 6dihxzedg.cloud-app.celerdata.com left intact
}%

Load the json from HTTP

curl -v --location-trusted -u adminuser:adminpassword -H "strict_mode: true" \
    -H "Expect:100-continue" \
    -H "format: json" -H "jsonpaths: [\"$.name\", \"$.code\"]" \
    -H "columns: city,tmp_id, id = tmp_id * 100" \
    --data '{"name": "Los Angeles", "code": 3}' -XPUT \
    https://6dihxzedg.cloud-app.celerdata.com/api/demo/table2/_stream_load

atwong@Alberts-MacBook-Pro sandbox %     curl -v --location-trusted -u admin:admin -H "strict_mode: true" \
    -H "Expect:100-continue" \
    -H "format: json" -H "jsonpaths: [\"$.name\", \"$.code\"]" \
    -H "columns: city,tmp_id, id = tmp_id * 100" \
    --data '{"name": "Los Angeles", "code": 3}' -XPUT \
    https://6dihxzedg.cloud-app.celerdata.com/api/demo/table2/_stream_load
*   Trying 35.161.69.10:443...
* Connected to 6dihxzedg.cloud-app.celerdata.com (35.161.69.10) port 443 (#0)
* ALPN: offers h2,http/1.1
* (304) (OUT), TLS handshake, Client hello (1):
*  CAfile: /etc/ssl/cert.pem
*  CApath: none
* (304) (IN), TLS handshake, Server hello (2):
* TLSv1.2 (IN), TLS handshake, Certificate (11):
* TLSv1.2 (IN), TLS handshake, Server key exchange (12):
* TLSv1.2 (IN), TLS handshake, Server finished (14):
* TLSv1.2 (OUT), TLS handshake, Client key exchange (16):
* TLSv1.2 (OUT), TLS change cipher, Change cipher spec (1):
* TLSv1.2 (OUT), TLS handshake, Finished (20):
* TLSv1.2 (IN), TLS change cipher, Change cipher spec (1):
* TLSv1.2 (IN), TLS handshake, Finished (20):
* SSL connection using TLSv1.2 / ECDHE-RSA-AES128-GCM-SHA256
* ALPN: server accepted http/1.1
* Server certificate:
*  subject: CN=6dihxzedg.cloud-app.celerdata.com
*  start date: Apr  3 22:57:46 2023 GMT
*  expire date: Jul  2 22:57:45 2023 GMT
*  subjectAltName: host "6dihxzedg.cloud-app.celerdata.com" matched cert's "6dihxzedg.cloud-app.celerdata.com"
*  issuer: C=US; O=Let's Encrypt; CN=R3
*  SSL certificate verify ok.
* using HTTP/1.1
* Server auth using Basic with user 'admin'
> PUT /api/demo/table2/_stream_load HTTP/1.1
> Host: 6dihxzedg.cloud-app.celerdata.com
> Authorization: Basic YWRtaW46YWRtaW4=
> User-Agent: curl/7.88.1
> Accept: */*
> strict_mode: true
> Expect:100-continue
> format: json
> jsonpaths: ["$.name", "$.code"]
> columns: city,tmp_id, id = tmp_id * 100
> Content-Length: 34
> Content-Type: application/x-www-form-urlencoded
>
< HTTP/1.1 100 Continue
* We are completely uploaded and fine
< HTTP/1.1 200 OK
< Server: nginx/1.14.1
< Date: Fri, 16 Jun 2023 17:42:22 GMT
< Content-Type: text/html; charset=ISO-8859-1
< Transfer-Encoding: chunked
< Connection: keep-alive
<
{
    "TxnId": 20030,
    "Label": "774425d2-b52f-45ae-9f0c-72705f556947",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 1,
    "NumberLoadedRows": 1,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 34,
    "LoadTimeMs": 49,
    "BeginTxnTimeMs": 1,
    "StreamLoadPlanTimeMs": 2,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 29,
    "CommitAndPublishTimeMs": 16
* Connection #0 to host 6dihxzedg.cloud-app.celerdata.com left intact
}%

Here's another more complex example can be used in the Loading JSON and AVRO data from Confluent Cloud Kafka into StarRocks tutorial at #22791

curl -v --location-trusted -u adminuser:adminpasswd -H "strict_mode: true" \
    -H "Expect:100-continue" \
    -H "format: json" \
    -H "jsonpaths: [\"$.userid\",\"$.time\",\"$._time\",\"$.remote_user\",\"$.ip\",\"$.request\",\"$.status\",\"$.bytes\",\"$.referrer\",\"$.agent\"]" \
    -H "columns: userid, time, tmp_time, remote_user, ip, request, status, bytes, referrer, agent, _time = FROM_UNIXTIME(tmp_time)" \
    --data '{ "ip": "122.152.45.245", "userid": 9, "remote_user": "-", "time": "5631", "_time": 5631, "request": "GET /site/user_status.html HTTP/1.1", "status": "407", "bytes": "278", "referrer": "-", "agent": "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)" }' -XPUT \
    https://6dihxzedg.cloud-app.celerdata.com/api/demo/visits/_stream_load

Load the JSON file from S3

There is an RFE for this feature. Please vote for this feature at #25505

Load JSON data using Postman