ClickHouse Performance: Automating Schema Generation

JSON to ClickHouse: MergeTree Engines, LowCardinality, and JSONEachRow Ingestion

ClickHouse is a columnar OLAP database that processes billions of rows per second by storing each column separately and reading only the columns a query touches. Converting JSON to ClickHouse tables requires choosing the right engine from the MergeTree family (regular, ReplacingMergeTree for deduplication, AggregatingMergeTree for pre-aggregation), mapping JSON strings to LowCardinality(String) for enum-like columns, and understanding that ClickHouse's ORDER BY is both the primary sort key and the primary "index" — queries that filter on the ORDER BY columns avoid most block reads. JSON ingestion uses the JSONEachRow format, loading newline-delimited JSON directly at hundreds of MB/s.

Live Example: Web Analytics Events

-- Input JSON (web analytics event)
{
  "event_id":   "evt_9f2a1b",
  "event_time": "2024-01-15 10:30:00.123",
  "session_id": "sess_abc123",
  "user_id":    "usr_001",
  "page_url":   "/products/widget-pro",
  "event_type": "page_view",
  "browser":    "Chrome",
  "os":         "macOS",
  "country":    "US",
  "duration_ms": 3400,
  "properties": {"utm_source": "google", "utm_campaign": "summer_sale"}
}

-- Generated ClickHouse DDL
CREATE TABLE events
(
    -- Time-series events: use DateTime64 for millisecond precision
    event_time   DateTime64(3, 'UTC')    NOT NULL,    -- 64-bit timestamp, ms precision
    event_id     UUID                    NOT NULL DEFAULT generateUUIDv4(),

    -- String IDs
    session_id   String,
    user_id      String,
    page_url     String,

    -- Low-cardinality strings (dictionary-encoded — use when < 10,000 unique values)
    event_type   LowCardinality(String),
    browser      LowCardinality(String),
    os           LowCardinality(String),
    country      LowCardinality(FixedString(2)),  -- ISO country codes: 'US', 'GB', etc.

    -- Numeric — use smallest type that fits the data
    duration_ms  UInt32,                          -- 0 to 4.3 billion ms

    -- Semi-structured JSON — queryable with JSONExtract functions
    properties   String DEFAULT '{}',

    -- Computed/derived columns
    date         Date MATERIALIZED toDate(event_time),  -- auto-computed from event_time

    INDEX idx_user_id user_id TYPE bloom_filter GRANULARITY 4
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)            -- monthly partitions for data lifecycle
ORDER BY (event_type, toStartOfHour(event_time), user_id)  -- primary sort key
TTL event_time + INTERVAL 90 DAY             -- auto-delete records older than 90 days
SETTINGS index_granularity = 8192;

The ORDER BY is critical — ClickHouse reads data in sorted blocks. Queries that filter on leading ORDER BY columns skip entire data blocks without reading them. Put the highest-cardinality columns you filter by most often first.

MergeTree Engine Family

-- MergeTree: standard append-only time-series (events, logs)
ENGINE = MergeTree()
ORDER BY (event_type, event_time, user_id)

-- ReplacingMergeTree: deduplication by primary key on background merges
-- Use for slowly-changing dimension tables (users, products)
ENGINE = ReplacingMergeTree(updated_at)   -- keeps row with max updated_at
ORDER BY user_id

-- SummingMergeTree: auto-aggregates numeric columns on merge
-- Use for pre-aggregated metrics (saves query time for SUM)
ENGINE = SummingMergeTree()             -- sums all UInt/Int columns by ORDER BY key
ORDER BY (user_id, toStartOfDay(event_time))

-- AggregatingMergeTree: stores partial aggregation states
-- Used with materialized views for complex pre-aggregation
ENGINE = AggregatingMergeTree()
ORDER BY (user_id, date)

-- CollapsingMergeTree: soft deletes via sign column (+1/-1)
ENGINE = CollapsingMergeTree(sign)      -- sign = 1 (insert) or -1 (delete marker)
ORDER BY user_id

LowCardinality: Dictionary Encoding for Strings

-- LowCardinality(String) stores unique values in a dictionary
-- The column stores integer indices instead of repeated string values
-- ~4x compression improvement for string columns with < 10,000 unique values

-- GOOD candidates for LowCardinality:
country     LowCardinality(String),     -- ~250 values
browser     LowCardinality(String),     -- ~50 values
event_type  LowCardinality(String),     -- ~20 values
os          LowCardinality(String),     -- ~20 values
currency    LowCardinality(FixedString(3)),  -- ~150 ISO codes

-- BAD candidates (too many unique values — defeats dictionary):
user_id   String,     -- millions of unique values
page_url  String,     -- millions of unique paths
event_id  UUID,       -- unique per event

-- ClickHouse-specific integer types (use the smallest that fits):
-- UInt8:   0 to 255             (status codes, scores 0-100)
-- UInt16:  0 to 65,535          (port numbers, pixel dimensions)
-- UInt32:  0 to 4.29 billion    (most counters, duration_ms)
-- UInt64:  0 to 1.8×10^19      (byte counts, large IDs)
-- Int8:   -128 to 127          (signed small values)
-- Int32:  -2.1B to 2.1B        (signed medium values)
-- Float32: 7 significant digits (imprecise)
-- Float64: 15 significant digits (more precise)
-- Decimal64(s): exact decimals, use for money

JSONEachRow: Bulk Ingestion

# Load newline-delimited JSON (NDJSON) directly — no preprocessing needed

# From a file:
clickhouse-client \
  --query="INSERT INTO events FORMAT JSONEachRow" \
  < events.ndjson

# From HTTP API:
curl -X POST 'http://localhost:8123/?query=INSERT+INTO+events+FORMAT+JSONEachRow' \
  --data-binary @events.ndjson

# Streaming from Kafka (ClickHouse Kafka Engine)
CREATE TABLE events_kafka_queue (
    event_time   DateTime64(3, 'UTC'),
    user_id      String,
    event_type   LowCardinality(String)
) ENGINE = Kafka
SETTINGS
    kafka_broker_list     = 'kafka:9092',
    kafka_topic_list      = 'events',
    kafka_group_name      = 'clickhouse_consumer',
    kafka_format          = 'JSONEachRow',
    kafka_max_block_size  = 65536;

-- Materialized view moves data from queue table to real table
CREATE MATERIALIZED VIEW events_mv TO events AS
SELECT * FROM events_kafka_queue;

# Python bulk insert with clickhouse-driver
from clickhouse_driver import Client
client = Client('localhost')

rows = [
  {'event_time': '2024-01-15 10:30:00', 'user_id': 'u1', 'event_type': 'page_view', 'duration_ms': 1200},
  {'event_time': '2024-01-15 10:30:01', 'user_id': 'u2', 'event_type': 'click',     'duration_ms': 50},
]
client.execute('INSERT INTO events VALUES', rows)

Querying JSON Properties with JSONExtract

-- properties column stores JSON string: '{"utm_source":"google","campaign":"summer"}'
-- JSONExtract functions query inside without a separate column

SELECT
    user_id,
    JSONExtractString(properties, 'utm_source')  AS utm_source,
    JSONExtractString(properties, 'campaign')    AS campaign,
    COUNT(*)                                     AS visits
FROM events
WHERE event_type = 'page_view'
  AND JSONExtractString(properties, 'utm_source') = 'google'
  AND event_time >= '2024-01-01'
GROUP BY user_id, utm_source, campaign
ORDER BY visits DESC
LIMIT 100;

-- For high-frequency JSON key access, add a MATERIALIZED column:
ALTER TABLE events
    ADD COLUMN utm_source LowCardinality(String)
    MATERIALIZED JSONExtractString(properties, 'utm_source');

-- Now queries on utm_source use the materialized column (no JSON parsing at query time)
SELECT COUNT(*) FROM events WHERE utm_source = 'google';

Materialized Views for Pre-Aggregation

-- Pre-aggregate hourly counts to avoid full-table scans for dashboards
CREATE MATERIALIZED VIEW events_hourly_mv
ENGINE = SummingMergeTree()
ORDER BY (event_type, hour, country)
AS SELECT
    toStartOfHour(event_time)    AS hour,
    event_type,
    country,
    COUNT()                      AS event_count,
    uniqExact(user_id)           AS unique_users,
    SUM(duration_ms)             AS total_duration_ms
FROM events
GROUP BY hour, event_type, country;

-- Dashboard query now reads pre-aggregated rows (sub-second)
SELECT event_type, SUM(event_count) AS total
FROM events_hourly_mv
WHERE hour >= '2024-01-01'
GROUP BY event_type
ORDER BY total DESC;

Best Practices for Production

• Avoid Nullable types — use defaults instead: Nullable(UInt32) requires an extra bitset file per column on disk, adds memory overhead, and disables some optimizations. Use UInt32 DEFAULT 0 or String DEFAULT '' for missing values instead.
• Insert in large batches, not row by row: ClickHouse is optimized for bulk ingestion. Each INSERT creates a new data part; too many small inserts cause "Too many parts" errors and slow merging. Insert at minimum 100K rows per batch, ideally 1M+. Use a buffer or queue (Kafka, file batching) for streaming data.
• Choose ORDER BY based on the most common WHERE filters: The ORDER BY is the sparse index. Columns at the start of ORDER BY benefit most from query pruning. A query WHERE event_type = 'purchase' AND event_time > '2024-01-01' benefits from ORDER BY (event_type, event_time) but not from ORDER BY (user_id, event_time).
• Use PARTITION BY toYYYYMM(event_time) for time-series data: Monthly partitions allow you to drop old data with ALTER TABLE events DROP PARTITION '202312' — instant, no scan. This is the ClickHouse equivalent of time-based table archival.

FAQ

Q: How does ClickHouse compare to BigQuery for analytics?
A: ClickHouse is self-hosted (also available as ClickHouse Cloud) with predictable latency and cost — good for high-query-rate dashboards. BigQuery is serverless (pay per byte scanned) with automatic scaling — good for infrequent, large ad-hoc queries. ClickHouse is faster for known query patterns on structured data; BigQuery is more flexible for schema-on-read semi-structured data.

Q: Can I update or delete rows in ClickHouse?
A: Yes, but not efficiently for individual rows. ALTER TABLE UPDATE/DELETE triggers asynchronous rewrites of data parts. For CDC (change data capture), use ReplacingMergeTree (keeps latest version on merge) or CollapsingMergeTree (sign-based deletes). ClickHouse is designed for append-mostly workloads.

Q: What's the best way to load historical JSON data?
A: Use clickhouse-client --query="INSERT INTO table FORMAT JSONEachRow" < file.ndjson for large files, or the HTTP API for programmatic loading. For very large datasets (>100GB), use clickhouse-admin and parallel uploads split across multiple files. Always compress input with gzip — ClickHouse decompresses on the fly and network is usually the bottleneck.