Skip to main content

Amazon Athena

Athena runs SQL on data in your AWS account with nothing to provision, and bills per byte it scans. Use it with an Amazon S3 destination, where you define one external table over the delivered objects, or with an Amazon S3 Tables destination, which Athena reads through the AWS Glue Data Catalog with no table definition at all. Athena engine version 3 is built on Trino, so the queries match the Trino page.

note

The table definitions, permissions and AWS setup on this page follow the Athena, AWS Glue and Amazon S3 documentation. We have not run them against Athena ourselves; if one fails for you, tell us and we will fix the page. We ran the example queries on Trino against our sample data.

Connect to bucket objects​

1. Define the table​

Each object line is one envelope. Read the whole line as one string column, declare the three key segments as partition columns, and let Athena compute the partitions with partition projection:

CREATE DATABASE IF NOT EXISTS s2b;

CREATE EXTERNAL TABLE s2b.raw (
line string
)
PARTITIONED BY (stream string, `date` string, hour string)
STORED AS TEXTFILE
LOCATION 's3://<bucket>/<prefix>/s2b/'
TBLPROPERTIES (
'projection.enabled' = 'true',
'projection.stream.type' = 'enum',
'projection.stream.values' = '<stream_id>',
'projection.date.type' = 'date',
'projection.date.format' = 'yyyy-MM-dd',
'projection.date.range' = '2026-11-01,NOW',
'projection.date.interval' = '1',
'projection.date.interval.unit' = 'DAYS',
'projection.hour.type' = 'integer',
'projection.hour.range' = '0,23',
'projection.hour.digits' = '2',
'storage.location.template' = 's3://<bucket>/<prefix>/s2b/stream=${stream}/date=${date}/hour=${hour}/'
);
  • With projection, Athena works out every stream=…/date=…/hour=…/ folder from the table properties. A new hour is queryable as soon as its first object lands. There is no MSCK REPAIR TABLE to run and no crawler to schedule.
  • List your stream IDs in projection.stream.values, separated by commas. When you add a stream, add its ID with ALTER TABLE s2b.raw SET TBLPROPERTIES ('projection.stream.values' = '<stream_id>,<new_stream_id>').
  • Set the start of projection.date.range to the day of your first delivery. NOW moves the end forward each day. Projected dates are UTC, the same as Send2Bucket's date and hour folders.
  • The template only produces stream= folders, so the test objects under s2b/_verify/ are never read.
  • Athena picks gzip from the .gz suffix, so the .ndjson.gz objects need no compression setting.
  • With no ROW FORMAT, Athena splits each line on byte 0x01. Send2Bucket escapes control characters inside JSON strings, so that byte never occurs and the whole line lands in line.
  • date is a reserved word in Athena DDL, so it is in backticks here. SELECT statements can use it unquoted.

Partition projection works only in Athena. Redshift Spectrum, EMR and Glue jobs that read the same table see no partitions. See Amazon Redshift for a table those can use.

2. Add a view with the table columns​

A view gives the raw objects the same columns as a table destination, so the example queries work on both:

CREATE OR REPLACE VIEW s2b.records AS
SELECT
json_extract_scalar(line, '$.signal') AS signal_type,
json_extract_scalar(line, '$.delivery_batch_id') AS delivery_batch_id,
from_iso8601_timestamp(json_extract_scalar(line, '$.received_at')) AS ingested_at,
line AS payload,
stream,
date,
hour
FROM s2b.raw;

Filter on date, and on hour or stream where you can. Athena pushes those filters through the view and reads only the matching folders.

Why not a JSON SerDe or a crawler​

Athena's JSON SerDes map each top-level key to a typed column. That works for signal and received_at, but data holds whatever your events contain, and a struct type has to list every field. AWS also documents that the OpenX JSON SerDe can return non-deterministic results. Reading the line as one string and parsing it at query time avoids both problems.

An AWS Glue crawler can create the table and register partitions for you, but it infers one schema from a sample of objects. Your event fields, OpenTelemetry records and the _verify test objects all end up merged into one guessed data type that changes as the crawler sees new shapes. If you use a crawler anyway, point it at <prefix>/s2b/ with an exclude pattern for _verify/**, and check the table it creates.

Connect to Amazon S3 Tables​

Athena reads S3 Tables through the AWS Glue Data Catalog. Turn on the integration once per Region: select Enable integration when you create the table bucket in the Amazon S3 console, or follow Integrating Amazon S3 Tables with AWS analytics services. The integration creates a catalog named s3tablescatalog, with one child catalog per table bucket and one database per namespace.

The table is then "s3tablescatalog/<table_bucket>"."<namespace>"."<table>":

SELECT signal_type, count(*) AS records
FROM "s3tablescatalog/analytics"."s2b"."events"
GROUP BY signal_type;

In the Athena console you can instead pick AwsDataCatalog, then the s3tablescatalog/<table_bucket> catalog and the namespace as the database, and use the bare table name.

  • Glue and Athena only see S3 Tables whose namespace, table and column names are all lowercase. Send2Bucket's columns are lowercase; give the destination a lowercase namespace and table name. Otherwise queries fail with Unsupported Federation Resource - Invalid table or column names.
  • If your account integrated S3 Tables with Lake Formation access control, also grant the querying principal Lake Formation permissions on the table bucket's catalog.
  • Athena cannot create views inside an S3 Tables catalog. Run the example queries against the full table name.

Apache Iceberg tables​

Athena queries Iceberg tables through the AWS Glue Data Catalog, which includes S3 Tables. It does not read tables in other Iceberg REST catalogs. If you deliver to an Apache Iceberg destination on another catalog, query it with Trino or DuckDB, or use an S3 Tables destination for Athena.

Permissions​

The principal that runs queries needs Athena, the Glue Data Catalog and read access to the data. For bucket objects:

{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "RunQueries",
"Effect": "Allow",
"Action": [
"athena:StartQueryExecution",
"athena:StopQueryExecution",
"athena:GetQueryExecution",
"athena:GetQueryResults",
"athena:GetWorkGroup"
],
"Resource": "arn:aws:athena:us-east-1:123456789012:workgroup/primary"
},
{
"Sid": "ReadTableDefinitions",
"Effect": "Allow",
"Action": ["glue:GetDatabase", "glue:GetDatabases", "glue:GetTable", "glue:GetTables", "glue:GetPartitions"],
"Resource": [
"arn:aws:glue:us-east-1:123456789012:catalog",
"arn:aws:glue:us-east-1:123456789012:database/s2b",
"arn:aws:glue:us-east-1:123456789012:table/s2b/*"
]
},
{
"Sid": "ListDeliveredObjects",
"Effect": "Allow",
"Action": ["s3:ListBucket", "s3:GetBucketLocation"],
"Resource": "arn:aws:s3:::<bucket>"
},
{
"Sid": "ReadDeliveredObjects",
"Effect": "Allow",
"Action": "s3:GetObject",
"Resource": "arn:aws:s3:::<bucket>/<prefix>/s2b/*"
},
{
"Sid": "WriteQueryResults",
"Effect": "Allow",
"Action": ["s3:GetObject", "s3:PutObject", "s3:ListBucket", "s3:GetBucketLocation", "s3:AbortMultipartUpload"],
"Resource": ["arn:aws:s3:::<results_bucket>", "arn:aws:s3:::<results_bucket>/*"]
}
]
}
  • The last statement covers the S3 location where your workgroup writes query results. Leave it out if the workgroup does not write results to your own bucket.
  • Creating the database, table and view once also needs glue:CreateDatabase, glue:CreateTable and glue:UpdateTable. Give those to whoever runs the setup, not to every analyst.
  • If the bucket uses SSE-KMS with a customer managed key, add kms:Decrypt on that key.
  • This role only reads. It is separate from the role Send2Bucket assumes to write.

For S3 Tables, replace the Glue and data statements with these two statements, which give read access to the s3tablescatalog catalog and the table bucket:

{
"Effect": "Allow",
"Action": ["glue:GetCatalog", "glue:GetDatabase", "glue:GetTable"],
"Resource": [
"arn:aws:glue:us-east-1:123456789012:catalog/s3tablescatalog",
"arn:aws:glue:us-east-1:123456789012:catalog/s3tablescatalog/*",
"arn:aws:glue:us-east-1:123456789012:database/s3tablescatalog/*/*",
"arn:aws:glue:us-east-1:123456789012:table/s3tablescatalog/*/*/*"
]
},
{
"Effect": "Allow",
"Action": [
"s3tables:GetTableBucket",
"s3tables:GetNamespace",
"s3tables:GetTable",
"s3tables:GetTableMetadataLocation",
"s3tables:GetTableData"
],
"Resource": [
"arn:aws:s3tables:us-east-1:123456789012:bucket/analytics",
"arn:aws:s3tables:us-east-1:123456789012:bucket/analytics/table/*"
]
}

Example queries​

The queries use the s2b.records view. For S3 Tables, replace it with "s3tablescatalog/<table_bucket>"."<namespace>"."<table>" and drop the date filter (filter on ingested_at instead). Fields inside payload are under $.data, as described in The envelope.

Records per signal:

SELECT signal_type, count(*) AS records
FROM s2b.records
WHERE date = '2026-11-19'
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 s2b.records
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 unnests the array and keeps the matching entry:

SELECT
json_extract_scalar(attr, '$.value.stringValue') AS service,
json_extract_scalar(payload, '$.data.severityText') AS severity,
count(*) AS logs
FROM s2b.records
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;

Records without a service.name attribute drop out of this result.

The five slowest spans. OTLP timestamps are nanoseconds stored as strings, so cast them to bigint 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 s2b.records
WHERE signal_type = 'span'
ORDER BY duration_ms DESC
LIMIT 5;

Notes​

  • Cost. Athena bills per terabyte scanned ($5 per TB in most Regions; see Athena pricing), rounded up to the nearest megabyte with a 10 MB minimum per query. For bucket objects, scanned bytes are the compressed size of every object in the folders the query reads, whatever columns it selects. Filter on date and hour to read fewer folders. S3 request charges for reading the objects are billed separately.
  • S3 Tables. S3 Tables store Parquet, so Athena reads only the columns a query references, and a filter on ingested_at lets it skip data files. Most of a row's bytes are in payload, so a query that parses it still reads most of the data in range.
  • Empty partitions. Athena projects a partition for every stream, day and hour in range, even where no object exists. AWS recommends classic Glue partitions when more than half of the projected partitions are empty, which can happen with a stream that receives data only a few hours a day.
  • Timestamps. from_iso8601_timestamp keeps milliseconds. The full microsecond received_at string is still in payload.
  • JSON functions. json_extract_scalar returns text, or NULL for objects and arrays. Cast numbers before doing arithmetic, and use json_extract to get an object or array as JSON.
  • Duplicates. A record is in your bucket about a minute after it was accepted. See Delivery semantics for when the same record can arrive twice and how to drop it with delivery_batch_id.

See the querying overview for other engines, and Trino for the same queries on a Trino cluster.