Skip to main content

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.

note

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 under s2b/_verify/ stay out.
  • ClickHouse turns the key=value path segments into stream (String), date (Date) and hour (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 .gz extension tells ClickHouse to decompress each object.
  • resource, scope and data use the JSON type, so data.event and data.cents read like columns whatever shape your events have. An object that is missing, such as resource on a JSON event, reads as {}.
  • To read a single stream, replace stream=* with stream=<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​

note

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​

note

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_header with catalog_credential = '<client_id>:<client_secret>' and oauth_server_uri.
  • The engine is beta. Self-hosted ClickHouse needs 25.8 or later and allow_database_iceberg = 1 in the session that creates the database; on 25.3 to 25.7 the setting is allow_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​

note

We have not run ClickHouse against these destinations.

  • Supabase Analytics Buckets expose a REST catalog. Create the database as above with <catalog_uri> set to https://<project_ref>.supabase.co/storage/v1/iceberg, warehouse set to the bucket name, the catalog token in auth_header, the bucket's S3 access keys as the storage key, and storage_endpoint pointing at https://<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 file runs 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 wider date filter; the file filter keeps it from adding batches twice.
  • The first refresh runs when the view is created. SYSTEM REFRESH VIEW s2b.records_loader runs one now, and SELECT * FROM system.view_refreshes shows 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 stream and date columns from the path, so the view cuts them out of _path.
  • unordered mode keeps the name of every processed object. Once it holds tracked_files_limit names, 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 under s2b/, or expire old objects with a bucket lifecycle rule.
  • Do not use ordered mode. 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_log shows each processed object and its row count. The S3Queue table 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 (and hour) to keep a query to a few folders; an unfiltered query lists and reads the whole s2b/ prefix, which costs S3 requests and egress.
  • The JSON type 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 path a.b.
  • If you prefer plain strings, declare resource, scope and data as String in the structure and read them with JSONExtractString(data, 'event') and the other JSONExtract* 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 with DELETE 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.