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.
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, andFILES()reads\x01as a hex byte. A single backslash ("\x01") becomes the three charactersx01. FILES()decompresses the objects from their.gzsuffix."compression" = "gzip"says so explicitly and gives the same result.stream=*keeps the destination's test objects unders2b/_verify/out of the view. To read one stream, usestream=<stream_id>.columns_from_pathaddsstream,dateandhouras strings from the key.received_atis aDATETIMEin UTC. StarRocks drops the trailingZwhen 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
oauth2lines if the catalog takes no token. If it vends storage credentials, set"iceberg.catalog.vended-credentials-enabled" = "true"instead of theaws.s3.*keys. - For data files on Amazon S3, drop
aws.s3.endpointandaws.s3.enable_path_style_access. - The catalog maps the Iceberg namespace to a database:
SHOW DATABASES FROM s2b_iceberglists them. ingested_atis shown in the session'stime_zone. RunSET 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:
| Property | Value |
|---|---|
iceberg.catalog.uri | https://<project_ref>.supabase.co/storage/v1/iceberg |
iceberg.catalog.warehouse | The Analytics Bucket's name |
iceberg.catalog.oauth2.token | The catalog token from the bucket's connection details |
aws.s3.endpoint | https://<project_ref>.supabase.co/storage/v1/s3 |
aws.s3.enable_path_style_access | true |
aws.s3.access_key, aws.s3.secret_key | The 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
INSERTmust select fromFILES()directly, not from a subquery or view, so each column parses$1itself. POLL_INTERVALis in seconds; 300 is the default.SHOW PIPESshows 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 asdate=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 exampledate=2026-11-19/hour=*, to read less. On 4.1.8, a query whose only columns come fromcolumns_from_path(such asSELECT count(*) … WHERE date = …with nolinecolumn) returned no rows; reference a file column too, as the example queries do.FILES()types the line asVARCHAR(1048576), so keep each record under 1 MiB.get_json_stringreturns a nested object as JSON text andget_json_intreturnsBIGINT.json_queryreturnsJSON, which other JSON functions andjson_eachtake.- StarRocks does not read DuckLake. Read a DuckLake destination with DuckDB.
- Google Cloud Storage objects are not covered here. StarRocks reads them through
FILES()withgcp.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.