Skip to main content

Trino

Trino reads Send2Bucket data through two connectors:

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.

note

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-key and s3.aws-secret-key to use the default AWS credential chain, such as an instance profile.
  • Recent Trino releases call the S3 switch fs.s3.enabled and still accept the older fs.native-s3.enabled with a deprecation warning. Older releases only know fs.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_location at the s2b/ prefix. The table reads only the stream=…/date=…/hour=…/ directories, so the test objects Send2Bucket writes to s2b/_verify/ are never read.
  • Trino picks gzip from the .gz suffix, so the .ndjson.gz objects need no compression setting.
  • format = 'JSON' does not work here: Trino's JSON reader cannot load a nested object such as data into a VARCHAR column (varchar value must be a scalar json value), and the Hive connector has no json column type. A ROW type works only when you list every field, which differs per record type.
  • DROP TABLE hive.s2b.raw removes 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_scalar returns text, or NULL for objects and arrays. Cast numbers before doing arithmetic. Use json_extract or json_query to 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:

    SELECT
    json_extract_scalar(attr, '$.value.stringValue') AS service,
    json_extract_scalar(payload, '$.data.severityText') AS severity,
    count(*) AS logs
    FROM iceberg.s2b.events
    CROSS 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, 2
    ORDER BY logs DESC;
  • Every query parses payload. For large tables, filter on ingested_at (Iceberg) or date and hour (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 TABLE with a single string column, PARTITIONED BY (stream string, date string, hour string) and LOCATION 's3://<bucket>/<prefix>/s2b/', then MSCK REPAIR TABLE or partition projection. We have not tested Starburst or Athena.