Skip to main content

Amazon Redshift

Redshift reads Send2Bucket data in three ways. For an Amazon S3 destination, COPY loads the delivered objects into a native table with SUPER columns, and an auto-copy job keeps loading new objects as they land. That is the fastest table to query again and again. Redshift Spectrum can also query the objects in place through an external table. For an Amazon S3 Tables destination, Redshift reads the Iceberg table through the AWS Glue Data Catalog.

note

This page follows the Amazon Redshift, AWS Glue and Amazon S3 documentation. We have not run these statements against Redshift ourselves; if one fails for you, tell us and we will fix the page.

Each section below ends with a table or view that has the same columns, taken from the envelope described in Delivery format: signal, delivery_batch_id, received_at, resource and data, with resource and data as SUPER. The example queries run against any of them.

Load bucket objects with COPY​

1. Create the table​

CREATE SCHEMA IF NOT EXISTS s2b;

CREATE TABLE s2b.records (
v INT,
delivery_batch_id VARCHAR(64),
batch_id VARCHAR(64),
received_at TIMESTAMPTZ,
signal VARCHAR(16),
resource SUPER,
scope SUPER,
data SUPER
)
SORTKEY (received_at);

The column names match the envelope's top-level keys, so COPY can map each key to its column. resource, scope and data keep their nested objects as SUPER values, whatever shape your events have.

2. Load new objects automatically​

An auto-copy job watches an S3 prefix and loads each new object once. It needs an S3 event integration between the bucket and your cluster or Serverless namespace, created in the Redshift console under S3 event integrations or with aws redshift create-integration. The setup also needs a bucket policy and a resource policy on the data warehouse; the console offers Fix it for me for the second. See Create an S3 event integration for the policies.

Then create the job:

COPY s2b.records
FROM 's3://<bucket>/<prefix>/s2b/stream='
IAM_ROLE 'arn:aws:iam::123456789012:role/redshift-s2b'
FORMAT JSON 'auto'
TIMEFORMAT 'auto'
GZIP
JOB CREATE s2b_records_load
AUTO ON;
  • COPY treats the FROM path as a key prefix. …/s2b/stream= matches every stream and leaves out the test objects under s2b/_verify/, which are not gzip and would fail the load. Use …/s2b/stream=<stream_id>/ for one stream.
  • FORMAT JSON 'auto' matches top-level keys to column names. The match is case-sensitive, and all envelope keys are lowercase.
  • TIMEFORMAT 'auto' reads received_at, an ISO 8601 timestamp such as 2026-11-19T03:04:05.123456Z.
  • GZIP decompresses the .ndjson.gz objects. Every object under the prefix must be gzip.
  • The job records which object keys it has loaded. Send2Bucket writes each object key once and never rewrites it: a retried delivery either writes the object for the first time or finds the one already there, so the job never sees a second copy of a delivery batch.
  • The job only loads objects created after the job itself. Load earlier deliveries with a plain COPY, below.
  • The job name cannot contain a hyphen. COPY JOB LIST and the SYS_COPY_JOB_DETAIL view show the jobs and the files they loaded.
  • A bucket can have only one S3 event integration per data warehouse, the bucket name cannot contain a period, and the bucket cannot already have an event notification for object-created events.

Load with a plain COPY​

Without auto-copy, run COPY on a schedule, one finished day or hour at a time. Each run loads every object under the prefix, so loading the same prefix twice loads its records twice. Load into a staging table and insert only the delivery batches you do not have yet:

CREATE TEMP TABLE staging (LIKE s2b.records);

COPY staging
FROM 's3://<bucket>/<prefix>/s2b/stream=<stream_id>/date=2026-11-18/'
IAM_ROLE 'arn:aws:iam::123456789012:role/redshift-s2b'
FORMAT JSON 'auto'
TIMEFORMAT 'auto'
GZIP;

INSERT INTO s2b.records
SELECT * FROM staging s
WHERE NOT EXISTS (
SELECT 1 FROM s2b.records r WHERE r.delivery_batch_id = s.delivery_batch_id
);

This is also the way to backfill objects that predate an auto-copy job.

Keep the whole envelope in one column​

To store each line as one SUPER value instead, use FORMAT JSON 'noshred' with a table that has a single SUPER column:

CREATE TABLE s2b.envelopes (envelope SUPER);

COPY s2b.envelopes
FROM 's3://<bucket>/<prefix>/s2b/stream=<stream_id>/date=2026-11-18/'
IAM_ROLE 'arn:aws:iam::123456789012:role/redshift-s2b'
FORMAT JSON 'noshred'
GZIP;

Fields are then one level deeper, for example e.envelope.data.event. The shredded table above is easier to filter and sort.

Query bucket objects in place with Spectrum​

Spectrum reads the objects at query time, so it always sees the latest hour, but every query parses the JSON again. Spectrum needs the table definition and its partitions in the AWS Glue Data Catalog. It does not use Athena's partition projection, so a table defined as on the Athena page returns no rows here.

Create an external schema and a table that reads each line as one string:

CREATE EXTERNAL SCHEMA s2b_lake
FROM DATA CATALOG DATABASE 's2b_lake'
IAM_ROLE 'arn:aws:iam::123456789012:role/redshift-s2b'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

CREATE EXTERNAL TABLE s2b_lake.raw (
line VARCHAR(65535)
)
PARTITIONED BY (stream VARCHAR(36), date VARCHAR(10), hour VARCHAR(2))
STORED AS TEXTFILE
LOCATION 's3://<bucket>/<prefix>/s2b/';

With no ROW FORMAT, Spectrum splits lines on byte 0x01, which never occurs in Send2Bucket's output, so the whole line lands in line. Spectrum picks gzip from the .gz suffix.

Register each hour's folder as a partition, on a schedule as new hours arrive:

ALTER TABLE s2b_lake.raw ADD IF NOT EXISTS
PARTITION (stream = '<stream_id>', date = '2026-11-19', hour = '08')
LOCATION 's3://<bucket>/<prefix>/s2b/stream=<stream_id>/date=2026-11-19/hour=08/';

A Glue crawler or Athena's MSCK REPAIR TABLE can register the partitions in the same Glue table instead.

Then parse the line in a view. Views over external tables need WITH NO SCHEMA BINDING:

CREATE VIEW s2b.records_lake AS
SELECT e.env.signal::VARCHAR AS signal,
e.env.delivery_batch_id::VARCHAR AS delivery_batch_id,
CAST(e.env.received_at::VARCHAR AS TIMESTAMPTZ) AS received_at,
e.env.resource AS resource,
e.env.data AS data,
e.stream, e.date, e.hour
FROM (SELECT JSON_PARSE(line) AS env, stream, date, hour FROM s2b_lake.raw) e
WITH NO SCHEMA BINDING;

Filter on date and hour so Spectrum reads only those folders. A VARCHAR holds at most 65,535 bytes, and by default Spectrum returns NULL for a longer line. Set the table properties data_cleansing_enabled to true and surplus_char_handling to FAIL to get an error instead, or load large records with COPY, which accepts JSON lines up to 4 MB.

Connect to Amazon S3 Tables​

Redshift reads S3 Tables through the AWS Glue Data Catalog:

  1. Turn on the S3 Tables integration with AWS analytics services, as described on the Athena page. Keep the namespace and table names lowercase.

  2. Create a Glue resource link to the namespace, in your default catalog:

    aws glue create-database --region us-east-1 --cli-input-json '{
    "CatalogId": "123456789012",
    "DatabaseInput": {
    "Name": "s2b_tables_link",
    "TargetDatabase": {
    "CatalogId": "123456789012:s3tablescatalog/analytics",
    "DatabaseName": "s2b"
    }
    }
    }'
  3. Create an external schema from the resource link:

    CREATE EXTERNAL SCHEMA s2b_tables
    FROM DATA CATALOG DATABASE 's2b_tables_link'
    IAM_ROLE 'arn:aws:iam::123456789012:role/redshift-s2b'
    REGION 'us-east-1'
    CATALOG_ID '123456789012';
  4. Parse payload in a view:

    CREATE VIEW s2b.records_tables AS
    SELECT e.signal, e.delivery_batch_id, e.received_at,
    e.env.resource AS resource,
    e.env.data AS data
    FROM (SELECT signal_type AS signal, delivery_batch_id, ingested_at AS received_at,
    JSON_PARSE(payload) AS env
    FROM s2b_tables.events) e
    WITH NO SCHEMA BINDING;

If your cluster uses federated identities and has the Glue Data Catalog auto-mounted, you can skip the resource link and external schema and name the table directly as "analytics@s3tablescatalog".s2b.events. See Query Amazon S3 Tables from Amazon Redshift.

For an Apache Iceberg destination, Redshift reads Iceberg tables registered in the AWS Glue Data Catalog through an external schema, as in step 3. It does not read tables in other Iceberg REST catalogs; query those with Trino or DuckDB, or use an S3 Tables destination.

The IAM role​

Attach one role to your cluster or Serverless namespace for COPY, Spectrum and S3 Tables. Its trust policy allows redshift.amazonaws.com to assume it. Its permissions policy:

{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "ReadDeliveredObjects",
"Effect": "Allow",
"Action": ["s3:GetObject", "s3:ListBucket"],
"Resource": ["arn:aws:s3:::<bucket>", "arn:aws:s3:::<bucket>/<prefix>/s2b/*"]
},
{
"Sid": "SpectrumCatalog",
"Effect": "Allow",
"Action": [
"glue:CreateDatabase", "glue:GetDatabase", "glue:GetDatabases",
"glue:CreateTable", "glue:GetTable", "glue:GetTables", "glue:UpdateTable",
"glue:BatchCreatePartition", "glue:CreatePartition",
"glue:GetPartition", "glue:GetPartitions", "glue:BatchGetPartition"
],
"Resource": [
"arn:aws:glue:us-east-1:123456789012:catalog",
"arn:aws:glue:us-east-1:123456789012:database/s2b_lake",
"arn:aws:glue:us-east-1:123456789012:table/s2b_lake/*"
]
},
{
"Sid": "S3TablesCatalog",
"Effect": "Allow",
"Action": ["glue:GetCatalog", "glue:GetDatabase", "glue:GetTable", "glue:GetTables"],
"Resource": [
"arn:aws:glue:us-east-1:123456789012:catalog",
"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/s2b_tables_link",
"arn:aws:glue:us-east-1:123456789012:database/s3tablescatalog/*/*",
"arn:aws:glue:us-east-1:123456789012:table/s3tablescatalog/*/*/*"
]
},
{
"Sid": "S3TablesData",
"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/*"
]
}
]
}

Keep only the statements for the paths you use. If the bucket uses SSE-KMS with a customer managed key, add kms:Decrypt on that key. This role only reads delivered data; it is separate from the role Send2Bucket assumes to write.

Example queries​

resource and data hold OTLP field names such as severityText and startTimeUnixNano. Redshift lowercases identifiers by default, so turn on case-sensitive SUPER navigation in each session first:

SET enable_case_sensitive_super_attribute TO true;

Without it, r.data.severityText looks up severitytext and returns NULL.

The queries use s2b.records. Replace it with s2b.records_lake for Spectrum or s2b.records_tables for S3 Tables. Navigating a SUPER value returns SUPER; cast it to VARCHAR or a number for clean output and arithmetic.

Records per signal:

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

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

SELECT r.data.event::VARCHAR AS event,
count(*) AS events,
sum(r.data.cents::INT) / 100.0 AS revenue_usd
FROM s2b.records r
WHERE r.signal = 'event'
GROUP BY 1
ORDER BY events DESC;

Logs per service and severity. service.name is one entry in the resource's attribute array. Listing the array in the FROM clause unnests it, one row per attribute:

SELECT a.value.stringValue::VARCHAR AS service,
r.data.severityText::VARCHAR AS severity,
count(*) AS logs
FROM s2b.records r, r.resource.attributes a
WHERE r.signal = 'log'
AND a.key::VARCHAR = '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 nanosecond strings, so cast them to VARCHAR and then to BIGINT before subtracting:

SELECT r.data.name::VARCHAR AS span,
r.data.traceId::VARCHAR AS trace_id,
(r.data.endTimeUnixNano::VARCHAR::BIGINT
- r.data.startTimeUnixNano::VARCHAR::BIGINT) / 1000000.0 AS duration_ms
FROM s2b.records r
WHERE r.signal = 'span'
ORDER BY duration_ms DESC
LIMIT 5;

Notes​

  • Lax navigation. A path that does not exist, such as r.data.cents on a page.view event, returns NULL instead of an error, and so does a cast of the wrong type.
  • Size limits. COPY rejects a JSON line over 4 MB. A string value inside SUPER is limited to 65,535 bytes unless the enable_large_strings_opt_in setting is on.
  • Cost. Native tables cost only cluster or Serverless time. Spectrum queries on a provisioned cluster are also billed per terabyte scanned, and for bucket objects that is the compressed size of every object in the folders a query reads. Filter on date and hour.
  • 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 Amazon Athena for serverless queries over the same bucket.