Trino
Trino reads Send2Bucket data through two connectors:
- The Iceberg connector reads Apache Iceberg and Amazon S3 Tables destinations directly. There is nothing to define: the table and its four columns are already in the catalog.
- The Hive connector reads the gzip NDJSON objects that Amazon S3, Google Cloud Storage and S3-compatible destinations write. You define an external table over the
s2b/prefix once.
Starburst ships the same connectors, and Amazon Athena (engine version 3) is built on Trino, so the SQL on this page carries over. See Starburst and Athena for the differences.
We ran the Iceberg REST and S3-compatible sections, and every query, with Trino 484. The S3 Tables, Glue, Hive metastore and Google Cloud Storage settings follow Trino's documentation.
The configuration and queries below were tested with Trino 484. For the record layout, see Delivery format. For other engines, see the overview.
When to use Trino
Use Trino when you already run it, or Starburst or Athena, and want Send2Bucket data next to your other sources in one SQL engine. Table destinations need only a catalog file. Bucket destinations need a catalog file, a table definition and a way to register new hourly partitions.
Connect to an Iceberg table
Add a catalog file, for example etc/catalog/iceberg.properties, with the same catalog URI, warehouse and storage key you gave Send2Bucket:
connector.name=iceberg
iceberg.catalog.type=rest
iceberg.rest-catalog.uri=https://catalog.example.com/api/catalog
iceberg.rest-catalog.warehouse=analytics
iceberg.rest-catalog.security=OAUTH2
iceberg.rest-catalog.oauth2.token=<catalog-token>
fs.s3.enabled=true
s3.region=us-east-1
s3.aws-access-key=<access-key-id>
s3.aws-secret-key=<secret-access-key>
- A read-only token and a read-only storage key are enough for querying.
- Leave out
s3.aws-access-keyands3.aws-secret-keyto use the default AWS credential chain, such as an instance profile. - Recent Trino releases call the S3 switch
fs.s3.enabledand still accept the olderfs.native-s3.enabledwith a deprecation warning. Older releases only knowfs.native-s3.enabled.
The table is then iceberg.<namespace>.<table>:
SELECT signal_type, count(*) AS records
FROM iceberg.s2b.events
GROUP BY signal_type;
The columns are the ones in Table columns. ingested_at is timestamp(6) with time zone, and payload is the envelope as JSON text.
Amazon S3 Tables
S3 Tables uses the same connector with SigV4 signing against the regional S3 Tables endpoint:
connector.name=iceberg
iceberg.catalog.type=rest
iceberg.rest-catalog.uri=https://s3tables.us-east-1.amazonaws.com/iceberg
iceberg.rest-catalog.warehouse=arn:aws:s3tables:us-east-1:123456789012:bucket/analytics
iceberg.rest-catalog.security=SIGV4
iceberg.rest-catalog.signing-name=s3tables
iceberg.rest-catalog.view-endpoints-enabled=false
fs.s3.enabled=true
s3.region=us-east-1
s3.aws-access-key=<access-key-id>
s3.aws-secret-key=<secret-access-key>
The IAM identity needs read access to the table bucket, for example s3tables:GetTableBucket, s3tables:GetNamespace, s3tables:GetTable, s3tables:GetTableMetadataLocation and s3tables:GetTableData. These settings follow the Trino documentation. We have checked that Trino accepts them but have not run them against AWS.
S3-compatible storage
If the table's data files are in S3-compatible storage outside AWS, add the endpoint, and path-style access if your provider needs it:
s3.endpoint=https://s3.example.com
s3.path-style-access=true
Connect to bucket objects
1. Add a Hive catalog
The Hive connector needs a metastore to hold the table definition. On AWS, use the AWS Glue Data Catalog:
connector.name=hive
hive.metastore=glue
hive.metastore.glue.region=us-east-1
fs.s3.enabled=true
s3.region=us-east-1
s3.aws-access-key=<access-key-id>
s3.aws-secret-key=<secret-access-key>
With your own Hive metastore, replace the two hive.metastore lines with hive.metastore.uri=thrift://metastore.example.com:9083. For S3-compatible storage, add s3.endpoint and s3.path-style-access as above.
For Google Cloud Storage, replace the fs.s3.enabled and s3.* lines with:
fs.gcs.enabled=true
gcs.project-id=<project-id>
gcs.json-key-file-path=/etc/trino/gcs-key.json
and use gs://<bucket>/<prefix>/s2b/ as the location below. The Glue and GCS settings follow the Trino documentation; we tested this section against S3-compatible storage.
2. Define the table
Each object line is one envelope. Read the whole line as one VARCHAR column, and declare the three key segments as partition columns:
CREATE SCHEMA hive.s2b;
CREATE TABLE hive.s2b.raw (
payload varchar,
stream varchar,
date varchar,
hour varchar
)
WITH (
external_location = 's3://<bucket>/<prefix>/s2b/',
format = 'TEXTFILE',
partitioned_by = ARRAY['stream', 'date', 'hour']
);
- Point
external_locationat thes2b/prefix. The table reads only thestream=…/date=…/hour=…/directories, so the test objects Send2Bucket writes tos2b/_verify/are never read. - Trino picks gzip from the
.gzsuffix, so the.ndjson.gzobjects need no compression setting. format = 'JSON'does not work here: Trino's JSON reader cannot load a nested object such asdatainto aVARCHARcolumn (varchar value must be a scalar json value), and the Hive connector has nojsoncolumn type. AROWtype works only when you list every field, which differs per record type.DROP TABLE hive.s2b.rawremoves the definition only. The objects stay in the bucket.
3. Register partitions
Register the partitions that exist now:
CALL hive.system.sync_partition_metadata(
schema_name => 's2b', table_name => 'raw', mode => 'FULL');
Send2Bucket starts a new hour= directory every hour, and a new date= directory every day. Run the call on a schedule, or let Trino compute the partitions with partition projection instead. With projection there is nothing to sync, but you list your stream IDs in the table:
CREATE TABLE hive.s2b.raw (
payload varchar,
stream varchar WITH (
partition_projection_type = 'enum',
partition_projection_values = ARRAY['<stream_id>']),
date varchar WITH (
partition_projection_type = 'date',
partition_projection_format = 'yyyy-MM-dd',
partition_projection_range = ARRAY['2026-11-01', 'NOW'],
partition_projection_interval = 1,
partition_projection_interval_unit = 'DAYS'),
hour varchar WITH (
partition_projection_type = 'integer',
partition_projection_range = ARRAY['0', '23'],
partition_projection_digits = 2)
)
WITH (
external_location = 's3://<bucket>/<prefix>/s2b/',
format = 'TEXTFILE',
partitioned_by = ARRAY['stream', 'date', 'hour'],
partition_projection_enabled = true
);
Set the start of the date range to the day of your first delivery.
4. Add a view with the table columns
A view gives the raw objects the same columns as a table destination, so the queries below work on both:
CREATE VIEW hive.s2b.records AS
SELECT
json_extract_scalar(payload, '$.signal') AS signal_type,
json_extract_scalar(payload, '$.delivery_batch_id') AS delivery_batch_id,
from_iso8601_timestamp_nanos(json_extract_scalar(payload, '$.received_at')) AS ingested_at,
payload,
stream,
date,
hour
FROM hive.s2b.raw;
Filter on date and hour to limit which objects Trino reads, for example WHERE date = '2026-11-19'.
Example queries
The queries use iceberg.s2b.events. For bucket objects, replace it with hive.s2b.records. Fields inside payload are under $.data, as described in The envelope.
Records per signal:
SELECT signal_type, count(*) AS records
FROM iceberg.s2b.events
GROUP BY signal_type
ORDER BY records DESC;
JSON events by name, with revenue from order.paid:
SELECT
json_extract_scalar(payload, '$.data.event') AS event,
count(*) AS events,
sum(CAST(json_extract_scalar(payload, '$.data.cents') AS bigint)) / 100.0 AS revenue_usd
FROM iceberg.s2b.events
WHERE signal_type = 'event'
GROUP BY 1
ORDER BY events DESC;
event | events | revenue_usd
------------+--------+-------------
page.view | 177 | NULL
order.paid | 53 | 4161.000000
email.sent | 47 | NULL
signup | 23 | NULL
Logs per service and severity. service.name is one entry in the resource's attribute array, so the query uses json_value with a filter in the JSON path:
SELECT
json_value(payload,
'lax $.resource.attributes[*]?(@.key == "service.name").value.stringValue') AS service,
json_extract_scalar(payload, '$.data.severityText') AS severity,
count(*) AS logs
FROM iceberg.s2b.events
WHERE signal_type = 'log'
GROUP BY 1, 2
ORDER BY logs DESC;
The five slowest spans. OTLP timestamps are nanoseconds stored as strings, so cast them before subtracting:
SELECT
json_extract_scalar(payload, '$.data.name') AS span,
json_extract_scalar(payload, '$.data.traceId') AS trace_id,
(CAST(json_extract_scalar(payload, '$.data.endTimeUnixNano') AS bigint)
- CAST(json_extract_scalar(payload, '$.data.startTimeUnixNano') AS bigint)) / 1e6 AS duration_ms
FROM iceberg.s2b.events
WHERE signal_type = 'span'
ORDER BY duration_ms DESC
LIMIT 5;
To limit an Iceberg query to a time range, filter on ingested_at, for example WHERE ingested_at >= TIMESTAMP '2026-11-19 00:00:00 UTC'.
Notes and limits
-
json_extract_scalarreturns text, orNULLfor objects and arrays. Cast numbers before doing arithmetic. Usejson_extractorjson_queryto get an object or array as JSON. -
If your engine lacks
json_value, unnest the attribute array instead. Records without the attribute drop out of the result:SELECTjson_extract_scalar(attr, '$.value.stringValue') AS service,json_extract_scalar(payload, '$.data.severityText') AS severity,count(*) AS logsFROM iceberg.s2b.eventsCROSS JOIN UNNEST(CAST(json_extract(payload, '$.resource.attributes') AS array(json))) AS r(attr)WHERE signal_type = 'log'AND json_extract_scalar(attr, '$.key') = 'service.name'GROUP BY 1, 2ORDER BY logs DESC; -
Every query parses
payload. For large tables, filter oningested_at(Iceberg) ordateandhour(bucket objects) first, or materialize the fields you query often into your own table. -
Send2Bucket appends to Iceberg tables and never rewrites them, so reading while it delivers is safe. Each query sees the latest committed snapshot.
Starburst and Athena
- Starburst Enterprise and Galaxy use the same Iceberg and Hive connectors. Property names can differ by release, so check them against your version's documentation.
- Amazon Athena (engine version 3) queries Iceberg tables registered in the AWS Glue Data Catalog, and S3 Tables through the AWS Glue integration, with the SQL on this page. For bucket objects, Athena uses Hive DDL instead of Trino's
WITH (...)properties:CREATE EXTERNAL TABLEwith a singlestringcolumn,PARTITIONED BY (stream string, date string, hour string)andLOCATION 's3://<bucket>/<prefix>/s2b/', thenMSCK REPAIR TABLEor partition projection. We have not tested Starburst or Athena.