Skip to main content

StarRocks

StarRocks reads Send2Bucket data two ways. For a bucket destination, the FILES() table function reads the delivered gzip objects in place, and a Pipe loads new objects into a native table as they land. For an Iceberg destination, an external catalog queries the table through its REST catalog with no copy. Use the native table when the same queries run many times a day; use FILES() or the Iceberg catalog when you want the latest deliveries without a load job.

note

We ran the S3-compatible, Iceberg REST, Pipe and example query sections against StarRocks 4.1.8. The Amazon S3 instance profile settings, Amazon S3 Tables and Supabase Analytics Buckets sections follow the StarRocks documentation and were not run by us.

Connect​

Each section ends with a view named records, with the columns signal, received_at, delivery_batch_id, resource and data. resource and data are JSON columns. The example queries run against that view.

Bucket objects​

FILES() in StarRocks reads Parquet, ORC, CSV and Avro, not JSON files. It does read gzip CSV, so read each line as a one-column CSV row and parse it with parse_json. The column separator \x01 never occurs in Send2Bucket's output, because control characters inside JSON strings are escaped:

CREATE DATABASE IF NOT EXISTS s2b;
USE s2b;

CREATE OR REPLACE VIEW raw_records AS
SELECT parse_json($1) AS line, stream, `date`, `hour`
FROM FILES(
"path" = "s3://<bucket>/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz",
"format" = "csv",
"csv.column_separator" = "\\x01",
"columns_from_path" = "stream, date, hour",
"aws.s3.region" = "<region>",
"aws.s3.use_instance_profile" = "true",
"aws.s3.iam_role_arn" = "arn:aws:iam::<account_id>:role/<read_only_role>"
);

CREATE OR REPLACE VIEW records AS
SELECT stream, `date`, `hour`,
get_json_string(line, '$.signal') AS signal,
CAST(get_json_string(line, '$.received_at') AS DATETIME) AS received_at,
get_json_string(line, '$.delivery_batch_id') AS delivery_batch_id,
json_query(line, '$.resource') AS resource,
json_query(line, '$.data') AS data
FROM raw_records;
  • Write the separator as "\\x01". The SQL parser turns \\ into one backslash, and FILES() reads \x01 as a hex byte. A single backslash ("\x01") becomes the three characters x01.
  • FILES() decompresses the objects from their .gz suffix. "compression" = "gzip" says so explicitly and gives the same result.
  • stream=* keeps the destination's test objects under s2b/_verify/ out of the view. To read one stream, use stream=<stream_id>.
  • columns_from_path adds stream, date and hour as strings from the key.
  • received_at is a DATETIME in UTC. StarRocks drops the trailing Z when it casts.

For an IAM user instead of a role, replace the two instance profile lines with "aws.s3.access_key" and "aws.s3.secret_key". The view stores whatever credentials you put in it, and anyone who can query the view reads the bucket with them, so use read-only ones.

S3-compatible storage​

Add the endpoint and path-style access, and use access keys:

FROM FILES(
"path" = "s3://<bucket>/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz",
"format" = "csv",
"csv.column_separator" = "\\x01",
"columns_from_path" = "stream, date, hour",
"aws.s3.endpoint" = "https://<endpoint>",
"aws.s3.enable_path_style_access" = "true",
"aws.s3.region" = "<region>",
"aws.s3.access_key" = "<access_key_id>",
"aws.s3.secret_key" = "<secret_access_key>"
)

Use this FILES() call in raw_records above. For Cloudflare R2 the endpoint is https://<account_id>.r2.cloudflarestorage.com and the region is auto.

Apache Iceberg (REST catalog)​

CREATE EXTERNAL CATALOG s2b_iceberg
PROPERTIES (
"type" = "iceberg",
"iceberg.catalog.type" = "rest",
"iceberg.catalog.uri" = "<catalog_uri>",
"iceberg.catalog.warehouse" = "<warehouse>",
"iceberg.catalog.security" = "oauth2",
"iceberg.catalog.oauth2.token" = "<catalog_token>",
"aws.s3.endpoint" = "https://<endpoint>",
"aws.s3.enable_path_style_access" = "true",
"aws.s3.region" = "<region>",
"aws.s3.access_key" = "<access_key_id>",
"aws.s3.secret_key" = "<secret_access_key>"
);

CREATE DATABASE IF NOT EXISTS s2b;
CREATE OR REPLACE VIEW s2b.records AS
SELECT signal_type AS signal,
ingested_at AS received_at,
delivery_batch_id,
json_query(parse_json(payload), '$.resource') AS resource,
json_query(parse_json(payload), '$.data') AS data
FROM s2b_iceberg.<namespace>.<table>;
  • Leave out the two oauth2 lines if the catalog takes no token. If it vends storage credentials, set "iceberg.catalog.vended-credentials-enabled" = "true" instead of the aws.s3.* keys.
  • For data files on Amazon S3, drop aws.s3.endpoint and aws.s3.enable_path_style_access.
  • The catalog maps the Iceberg namespace to a database: SHOW DATABASES FROM s2b_iceberg lists them.
  • ingested_at is shown in the session's time_zone. Run SET time_zone = 'UTC' to see UTC.

Use a read-only token and storage key here, not the ones Send2Bucket writes with.

Amazon S3 Tables​

StarRocks reaches S3 Tables through the AWS Glue Iceberg REST endpoint, with SigV4 signing:

CREATE EXTERNAL CATALOG s2b_iceberg
PROPERTIES (
"type" = "iceberg",
"iceberg.catalog.type" = "rest",
"iceberg.catalog.uri" = "https://glue.<region>.amazonaws.com/iceberg",
"iceberg.catalog.rest.sigv4-enabled" = "true",
"iceberg.catalog.rest.signing-name" = "glue",
"iceberg.catalog.rest.access-key-id" = "<access_key_id>",
"iceberg.catalog.rest.secret-access-key" = "<secret_access_key>",
"iceberg.catalog.warehouse" = "<account_id>:s3tablescatalog/<table_bucket>",
"aws.s3.region" = "<region>"
);

The IAM user needs Glue read permissions on the table bucket's catalog and lakeformation:GetDataAccess; see StarRocks: Iceberg REST catalog for S3 Tables. Then create the view as for Iceberg.

Supabase Analytics Buckets​

An Analytics Bucket is an Iceberg REST catalog with S3-compatible storage. Use the Iceberg catalog above with:

PropertyValue
iceberg.catalog.urihttps://<project_ref>.supabase.co/storage/v1/iceberg
iceberg.catalog.warehouseThe Analytics Bucket's name
iceberg.catalog.oauth2.tokenThe catalog token from the bucket's connection details
aws.s3.endpointhttps://<project_ref>.supabase.co/storage/v1/s3
aws.s3.enable_path_style_accesstrue
aws.s3.access_key, aws.s3.secret_keyThe project's S3 access keys

Load into a native table​

A Pipe loads each new object once, in the background, so you can query a native table and still pick up new deliveries:

USE s2b;

CREATE TABLE IF NOT EXISTS records_native (
received_at DATETIME,
signal VARCHAR(16),
delivery_batch_id VARCHAR(64),
batch_id VARCHAR(64),
resource JSON,
scope JSON,
data JSON
)
DUPLICATE KEY (received_at, signal)
PARTITION BY date_trunc('day', received_at)
DISTRIBUTED BY RANDOM;

CREATE PIPE s2b_records
PROPERTIES ("AUTO_INGEST" = "TRUE", "POLL_INTERVAL" = "300")
AS
INSERT INTO records_native
SELECT CAST(get_json_string($1, '$.received_at') AS DATETIME),
get_json_string($1, '$.signal'),
get_json_string($1, '$.delivery_batch_id'),
get_json_string($1, '$.batch_id'),
json_query(parse_json($1), '$.resource'),
json_query(parse_json($1), '$.scope'),
json_query(parse_json($1), '$.data')
FROM FILES(
"path" = "s3://<bucket>/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz",
"format" = "csv",
"csv.column_separator" = "\\x01",
"aws.s3.region" = "<region>",
"aws.s3.use_instance_profile" = "true"
);
  • A Pipe's INSERT must select from FILES() directly, not from a subquery or view, so each column parses $1 itself.
  • POLL_INTERVAL is in seconds; 300 is the default. SHOW PIPES shows the files loaded so far and the last error.
  • The Pipe remembers which files it loaded. A one-off INSERT INTO records_native SELECT … FROM FILES(…) does not: running it twice over the same objects loads the records twice. Narrow the path to one day, such as date=2026-11-18, for a manual backfill.

The example queries run against records_native unchanged.

Example queries​

Records per signal:

SELECT signal, count(*) AS records
FROM records
GROUP BY signal
ORDER BY records DESC;

JSON events by name, with revenue from order.paid:

SELECT get_json_string(data, '$.event') AS event,
count(*) AS events,
sum(get_json_int(data, '$.cents')) / 100 AS revenue_usd
FROM records
WHERE signal = 'event'
GROUP BY event
ORDER BY events DESC;
+------------+--------+-------------+
| event | events | revenue_usd |
+------------+--------+-------------+
| page.view | 177 | NULL |
| order.paid | 53 | 4161 |
| email.sent | 47 | NULL |
| signup | 23 | NULL |
+------------+--------+-------------+

Log records by service and severity. service.name is one entry in the resource's attribute list, so json_each expands the list and the WHERE clause keeps that entry:

SELECT get_json_string(a.value, '$.value.stringValue') AS service,
get_json_string(r.data, '$.severityText') AS severity,
count(*) AS logs
FROM records AS r, LATERAL json_each(json_query(r.resource, '$.attributes')) AS a
WHERE r.signal = 'log' AND get_json_string(a.value, '$.key') = 'service.name'
GROUP BY service, severity;

The five slowest spans. Timestamps are nanosecond strings, so cast to BIGINT before subtracting:

SELECT get_json_string(data, '$.name') AS span,
get_json_string(data, '$.traceId') AS trace_id,
(CAST(get_json_string(data, '$.endTimeUnixNano') AS BIGINT)
- CAST(get_json_string(data, '$.startTimeUnixNano') AS BIGINT)) / 1e6 AS duration_ms
FROM records
WHERE signal = 'span'
ORDER BY duration_ms DESC
LIMIT 5;

Notes​

  • FILES() lists and reads every matching object on each query. Narrow the path, for example date=2026-11-19/hour=*, to read less. On 4.1.8, a query whose only columns come from columns_from_path (such as SELECT count(*) … WHERE date = … with no line column) returned no rows; reference a file column too, as the example queries do.
  • FILES() types the line as VARCHAR(1048576), so keep each record under 1 MiB.
  • get_json_string returns a nested object as JSON text and get_json_int returns BIGINT. json_query returns JSON, which other JSON functions and json_each take.
  • StarRocks does not read DuckLake. Read a DuckLake destination with DuckDB.
  • Google Cloud Storage objects are not covered here. StarRocks reads them through FILES() with gcp.gcs.* credentials; see the FILES() reference.
  • Apache Doris shares StarRocks' origins but not this syntax. Its S3() table function reads JSON lines directly ("format" = "json", "read_json_by_line" = "true"); see the Doris S3 function reference. We have not run it.

See Delivery format for the envelope and table columns, and the Querying overview for other engines.