Loading Order Events

Loading BookNest's Order Events into ClickHouse

BookNest's sample event log (JSON, Columnar and Binary Formats) holds 390,737 JSON lines, one per order state change. ClickHouse 29,491 reads the file directly with file() and a schema, and its windowFunnel() aggregate answers the classic event question, how far each order got, in one pass:

1445-events.sql: loading the event log and an order funnelSQL

DROP TABLE IF EXISTS booknest.order_events;
CREATE TABLE booknest.order_events (
  event_id UInt32, ts DateTime('UTC'), type LowCardinality(String),
  order_id UInt32, customer_id Nullable(UInt32), total Nullable(Decimal(10, 2)))
ENGINE = MergeTree PARTITION BY toYYYYMM(ts) ORDER BY (type, ts);
INSERT INTO booknest.order_events
SELECT * FROM file('booknest/data/order_events.jsonl', JSONEachRow,
  'event_id UInt32, ts DateTime64(0, \'UTC\'), type String, order_id UInt32,
   customer_id Nullable(UInt32), total Nullable(Decimal(10, 2))');
SELECT count() AS events, uniqExact(type) AS types FROM booknest.order_events;
-- Funnel: of the orders placed in June 2026, how far did each get within 30 days?
SELECT level, count() AS orders
FROM (SELECT order_id, windowFunnel(30 * 86400)(ts, type = 'order_placed', type = 'order_paid',
        type = 'order_shipped', type = 'order_delivered') AS level
      FROM booknest.order_events
      WHERE ts >= '2026-06-01' GROUP BY order_id)
WHERE level > 0 GROUP BY level ORDER BY level;

Output

   ┌─events─┬─types─┐
1. │ 390737 │     6 │
   └────────┴───────┘
   ┌─level─┬─orders─┐
1. │     1 │    328 │
2. │     2 │    303 │
3. │     3 │    794 │
4. │     4 │   4073 │
   └───────┴────────┘

The 37 MB file loaded in 0.9 to 1.1 seconds across three runs and occupies 4.9 MiB. The funnel covers the 5,498 orders placed in June 2026 (the count httpfs and Object Storage found): 4,073 were delivered, and 794 were still shipped when the sample data ends. Standard SQL needs a self-join or window functions per step for this. In production the events would arrive from Kafka 129 (Apache Kafka and Managed Cloud Kafka) through ClickHouse's Kafka table engine, in batches, into this table.

MENU