ClickHouse
ClickHouse reads the gzip NDJSON objects that Amazon S3, Google Cloud Storage and S3-compatible destinations write, in place, with the s3 table function. It reads Apache Iceberg tables through a REST catalog. For dashboards and repeated queries, load the records into a MergeTree table on a schedule or continuously. Each object is one delivery batch, so the load can skip the batches it already has. The same SQL runs on self-hosted ClickHouse and ClickHouse Cloud.
Use ClickHouse 25.3 or later. Older releases lack the production-ready JSON column type used below.
We ran the statements on this page with ClickHouse 26.10 against S3-compatible storage and an Iceberg REST catalog. The ClickHouse Cloud, Google Cloud Storage and Supabase sections follow the ClickHouse documentation and were not run by us; each is marked.
For the record layout, see Delivery format. For other engines, see the overview.
Connect to bucket objects
Each section ends with a view named s2b.records with the envelope's columns plus stream, date, hour and file from the object key. The example queries run against that view.
Self-hosted ClickHouse
Store the bucket location and a read-only key in a named collection, then define the view:
CREATE NAMED COLLECTION s2b_bucket AS
url = 'https://<bucket>.s3.<region>.amazonaws.com/<prefix>/s2b/',
access_key_id = '<access_key_id>',
secret_access_key = '<secret_access_key>';
CREATE DATABASE IF NOT EXISTS s2b;
CREATE VIEW s2b.records AS
SELECT stream, date, hour, _file AS file, *
FROM s3(s2b_bucket,
filename = 'stream=*/date=*/hour=*/*.ndjson.gz',
format = 'JSONEachRow',
structure = 'v UInt8, delivery_batch_id String, batch_id String,
received_at DateTime64(6, \'UTC\'), signal LowCardinality(String),
resource JSON, scope JSON, data JSON');
stream=*reads the delivered records only. The destination's test objects unders2b/_verify/stay out.- ClickHouse turns the
key=valuepath segments intostream(String),date(Date) andhour(Int64) columns. Filter on them and ClickHouse skips the objects in other folders before it downloads anything:WHERE date = '2026-11-19'reads one day. - The
.gzextension tells ClickHouse to decompress each object. resource,scopeanddatause theJSONtype, sodata.eventanddata.centsread like columns whatever shape your events have. An object that is missing, such asresourceon a JSON event, reads as{}.- To read a single stream, replace
stream=*withstream=<stream_id>.
Leave out access_key_id and secret_access_key on an EC2 instance or in a container with an IAM role: ClickHouse then uses the credentials from its environment. Creating a named collection needs the CREATE NAMED COLLECTION privilege.
ClickHouse Cloud
This section follows the ClickHouse Cloud documentation. We have not run it against ClickHouse Cloud ourselves.
ClickHouse Cloud reads a private S3 bucket by assuming an IAM role that trusts your Cloud service. Set up the role with Accessing S3 data securely, grant it s3:GetObject and s3:ListBucket on <prefix>/s2b/, and pass its ARN to s3:
CREATE DATABASE IF NOT EXISTS s2b;
CREATE VIEW s2b.records AS
SELECT stream, date, hour, _file AS file, *
FROM s3('https://<bucket>.s3.<region>.amazonaws.com/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz',
'JSONEachRow',
'v UInt8, delivery_batch_id String, batch_id String,
received_at DateTime64(6, \'UTC\'), signal LowCardinality(String),
resource JSON, scope JSON, data JSON',
extra_credentials(role_arn = 'arn:aws:iam::<account_id>:role/<role_name>'));
Role-based access needs a Scale or Enterprise service. On other plans, use an access key in a named collection as for self-hosted ClickHouse.
Google Cloud Storage
This section follows ClickHouse's gcs documentation. We have not run it against Cloud Storage ourselves.
ClickHouse reads Cloud Storage through its S3-compatible XML API. Create an HMAC key for a service account with roles/storage.objectViewer on the bucket, and use it as the access key:
CREATE NAMED COLLECTION s2b_bucket AS
url = 'https://storage.googleapis.com/<bucket>/<prefix>/s2b/',
access_key_id = '<hmac_access_id>',
secret_access_key = '<hmac_secret>';
Then create the view as for self-hosted ClickHouse. Use the storage.googleapis.com host; storage.cloud.google.com does not serve the XML API.
S3-compatible storage
Put the endpoint and bucket in the URL, path-style. For Cloudflare R2:
CREATE NAMED COLLECTION s2b_bucket AS
url = 'https://<account_id>.r2.cloudflarestorage.com/<bucket>/<prefix>/s2b/',
access_key_id = '<access_key_id>',
secret_access_key = '<secret_access_key>';
For MinIO and other self-hosted stores, use http(s)://<host>:<port>/<bucket>/<prefix>/s2b/. Then create the view as for self-hosted ClickHouse.
Connect to an Iceberg table
REST catalog
The DataLakeCatalog database engine maps a REST catalog to a ClickHouse database, with every table in the warehouse in it. Each query reads the table's current snapshot, so new Send2Bucket appends show up without a refresh:
SET allow_database_iceberg = 1;
CREATE DATABASE lakehouse
ENGINE = DataLakeCatalog('<catalog_uri>', '<access_key_id>', '<secret_access_key>')
SETTINGS
catalog_type = 'rest',
warehouse = '<warehouse>',
auth_header = 'Authorization: Bearer <catalog_token>',
-- Only for S3-compatible storage outside AWS: the endpoint and bucket of the warehouse.
storage_endpoint = 'https://<storage_host>/<warehouse_bucket>';
SHOW TABLES FROM lakehouse;
- The second and third arguments are the storage key for the table's data files. Use a read-only token and storage key here, not the ones Send2Bucket writes with.
- For a catalog that uses OAuth client credentials instead of a static token, replace
auth_headerwithcatalog_credential = '<client_id>:<client_secret>'andoauth_server_uri. - The engine is beta. Self-hosted ClickHouse needs 25.8 or later and
allow_database_iceberg = 1in the session that creates the database; on 25.3 to 25.7 the setting isallow_experimental_database_iceberg. ClickHouse Cloud supports REST catalogs from 25.12.
Tables are named <namespace>.<table> inside the database, so quote the pair with backticks:
CREATE VIEW s2b.records AS
SELECT signal_type AS signal, ingested_at AS received_at, delivery_batch_id,
payload::JSON AS envelope, envelope.^resource AS resource, envelope.^data AS data
FROM lakehouse.`<namespace>.<table>`;
envelope.^data returns the data object as JSON, so the example queries work unchanged. Unlike the bucket view, this view has no date column; filter on received_at.
Without a catalog
icebergS3 reads a table straight from its location in storage, without asking the catalog:
SELECT signal_type, count()
FROM icebergS3('https://<bucket>.s3.<region>.amazonaws.com/<table_location>/',
'<access_key_id>', '<secret_access_key>')
GROUP BY signal_type;
It reads the newest metadata file it finds under <table_location>/metadata/. Use it for a one-off look; DataLakeCatalog asks the catalog which snapshot is current.
Amazon S3 Tables and Supabase Analytics Buckets
We have not run ClickHouse against these destinations.
- Supabase Analytics Buckets expose a REST catalog. Create the database as above with
<catalog_uri>set tohttps://<project_ref>.supabase.co/storage/v1/iceberg,warehouseset to the bucket name, the catalog token inauth_header, the bucket's S3 access keys as the storage key, andstorage_endpointpointing athttps://<project_ref>.supabase.co/storage/v1/s3. - Amazon S3 Tables: ClickHouse does not support the S3 Tables REST endpoint yet. Query it with DuckDB or Trino, or use a bucket destination for ClickHouse.
Load into a MergeTree table
Reading gzip JSON from the bucket costs a full download and parse on every query. A MergeTree table stores the records in columns on ClickHouse's own disks, where repeated queries are fast. Create it once:
CREATE TABLE s2b.records_local
(
stream LowCardinality(String),
date Date,
delivery_batch_id String,
batch_id String,
received_at DateTime64(6, 'UTC'),
signal LowCardinality(String),
resource JSON,
scope JSON,
data JSON
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (signal, received_at, delivery_batch_id);
Then fill it with one of the two methods below. Use one, not both.
On a schedule
A refreshable materialized view runs an INSERT … SELECT from the bucket view on a timer, inside ClickHouse. Each object holds one delivery batch and is named <delivery_batch_id>.ndjson.gz. The query skips every object whose batch is already in the table:
CREATE MATERIALIZED VIEW s2b.records_loader
REFRESH EVERY 5 MINUTE APPEND TO s2b.records_local
AS SELECT stream, date, delivery_batch_id, batch_id, received_at, signal, resource, scope, data
FROM s2b.records
WHERE date >= toDate(now('UTC')) - 1
AND file NOT IN (
SELECT delivery_batch_id || '.ndjson.gz'
FROM s2b.records_local
WHERE date >= toDate(now('UTC')) - 1);
- The filter on
fileruns before ClickHouse downloads anything, so a refresh reads only new objects. - The two-day window catches objects for the last hour of yesterday that land after midnight UTC. It misses objects older than that, for example the first load of existing data or deliveries from before a ClickHouse outage longer than a day. Load those once by hand with the same
INSERT INTO s2b.records_local SELECT …and a widerdatefilter; thefilefilter keeps it from adding batches twice. - The first refresh runs when the view is created.
SYSTEM REFRESH VIEW s2b.records_loaderruns one now, andSELECT * FROM system.view_refreshesshows the last result and any error. - Rows a refresh adds are visible when it finishes, so the table lags the bucket by up to the refresh interval plus about a minute of delivery.
Continuously, with S3Queue
The S3Queue engine polls the bucket and passes each new object once to a materialized view, which writes it into the table. It needs ClickHouse Keeper or ZooKeeper to remember which objects it processed; ClickHouse Cloud provides it. Run these instead of the refreshable view:
CREATE TABLE s2b.queue
(
delivery_batch_id String,
batch_id String,
received_at DateTime64(6, 'UTC'),
signal LowCardinality(String),
resource JSON,
scope JSON,
data JSON
)
ENGINE = S3Queue(s2b_bucket, filename = 'stream=*/date=*/hour=*/*.ndjson.gz', format = 'JSONEachRow')
SETTINGS mode = 'unordered', tracked_files_limit = 1000000;
CREATE MATERIALIZED VIEW s2b.queue_mv TO s2b.records_local AS
SELECT extract(_path, 'stream=([^/]+)') AS stream,
toDate(extract(_path, 'date=([^/]+)')) AS date,
delivery_batch_id, batch_id, received_at, signal, resource, scope, data
FROM s2b.queue;
- S3Queue tables do not get
streamanddatecolumns from the path, so the view cuts them out of_path. unorderedmode keeps the name of every processed object. Once it holdstracked_files_limitnames, it forgets the oldest and processes them again, which duplicates their records. The default is 1,000. A stream that receives records all day writes about 1,440 objects a day, and more under heavy load. Set the limit above the number of objects you keep unders2b/, or expire old objects with a bucket lifecycle rule.- Do not use
orderedmode. It skips any object whose key sorts before the last one processed. Keys start with the stream ID, so a new object in one stream sorts before older objects in another, and a retried upload can land after a newer object in the same stream. SELECT * FROM system.s3queue_logshows each processed object and its row count. TheS3Queuetable cannot be queried directly.
Example queries
These run against s2b.records. On s2b.records_local, the column names are the same.
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. A path in a JSON column has no fixed type, so cast it when you read it:
SELECT data.event::String AS event,
count() AS events,
sum(data.cents::Nullable(Int64)) / 100 AS revenue_usd
FROM s2b.records
WHERE signal = 'event'
GROUP BY event
ORDER BY events DESC;
┌─event──────┬─events─┬─revenue_usd─┐
│ page.view │ 177 │ ᴺᵁᴸᴸ │
│ order.paid │ 53 │ 4161 │
│ email.sent │ 47 │ ᴺᵁᴸᴸ │
│ signup │ 23 │ ᴺᵁᴸᴸ │
└────────────┴────────┴─────────────┘
Log records by service and severity. resource.attributes[] reads the attribute list as an array of JSON objects:
SELECT arrayFirst(a -> a.key::String = 'service.name', resource.attributes[]).value.stringValue::String AS service,
data.severityText::String AS severity,
count() AS logs
FROM s2b.records
WHERE signal = 'log'
GROUP BY service, severity;
The five slowest spans. Timestamps are nanosecond strings; cast them to Int128 before subtracting:
SELECT data.name::String AS span,
data.traceId::String AS trace_id,
(data.endTimeUnixNano::Int128 - data.startTimeUnixNano::Int128) / 1e6 AS duration_ms
FROM s2b.records
WHERE signal = 'span'
ORDER BY duration_ms DESC
LIMIT 5;
Gauge data points, one row per point:
SELECT data.name::String AS metric,
fromUnixTimestamp64Nano(p.timeUnixNano::Int64) AS at,
coalesce(p.asDouble::Nullable(Float64), p.asInt::Nullable(Int64)) AS value
FROM s2b.records
ARRAY JOIN data.gauge.dataPoints[] AS p
WHERE signal = 'metric';
Notes
- Every query on the bucket view lists the matching objects and downloads them. Filter on
date(andhour) to keep a query to a few folders; an unfiltered query lists and reads the wholes2b/prefix, which costs S3 requests and egress. - The
JSONtype stores each path as its own subcolumn, up to 1,024 paths per data part by default; further paths still work but read more slowly. A key that contains a dot, such as"a.b", becomes the nested patha.b. - If you prefer plain strings, declare
resource,scopeanddataasStringin the structure and read them withJSONExtractString(data, 'event')and the otherJSONExtract*functions. - A load that fails partway can leave some rows of one batch in
records_local. The scheduled load would then skip that batch. Delete its rows withDELETE FROM s2b.records_local WHERE delivery_batch_id = '<delivery_batch_id>'and the next refresh loads it again. See Delivery semantics for duplicates that Send2Bucket itself can produce. - DuckDB reads the same objects and tables from a laptop, with no server to run.