ClickHouse tutorial for analytics beginners

This ClickHouse® tutorial assumes you know SQL and have used a row-oriented database like Postgres. It does not assume you have run ClickHouse before. You will create a table, insert sample events, run aggregation queries, add a materialized view, and apply a TTL rule. Every step maps to patterns you will use in production analytics workloads.

ClickHouse is a column-oriented SQL database for OLAP workloads, per ClickHouse documentation. The examples below use standard ClickHouse SQL syntax.

Step 1: Create your first table

Start with an append-only events table. MergeTree is the default engine for analytical tables:

CREATE TABLE page_views
(
    event_time   DateTime64(3),
    user_id      String,
    page_path    LowCardinality(String),
    country      LowCardinality(String),
    duration_ms  UInt32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (page_path, event_time);

Three decisions matter here:

• PARTITION BY toYYYYMM(event_time) splits data into monthly partitions. Queries with time filters skip irrelevant partitions.
• ORDER BY (page_path, event_time) is the sort key. Queries filtering by page_path and time ranges read contiguous data blocks.
• LowCardinality(String) stores a dictionary for dimensions with few unique values relative to row count, per ClickHouse LowCardinality docs.

For a deeper walkthrough of table definitions, see ClickHouse create table example.

Step 2: Insert sample data

Insert rows individually or in batches:

INSERT INTO page_views VALUES
    ('2026-07-14 10:00:00.000', 'user_1', '/pricing', 'US', 4200),
    ('2026-07-14 10:00:01.000', 'user_2', '/docs',    'DE', 8100),
    ('2026-07-14 10:00:02.000', 'user_1', '/pricing', 'US', 3100),
    ('2026-07-14 10:00:03.000', 'user_3', '/blog',    'US', 1200);

For production ingestion, data arrives via HTTP, Kafka, or batch files rather than manual inserts. The ClickHouse Kafka table engine consumes topics directly without a custom consumer application.

Step 3: Run your first aggregation

Count page views by path:

SELECT
    page_path,
    count() AS views
FROM page_views
WHERE event_time >= '2026-07-14 00:00:00'
GROUP BY page_path
ORDER BY views DESC;

Add a time dimension:

SELECT
    toStartOfHour(event_time) AS hour,
    page_path,
    count()                   AS views,
    uniq(user_id)             AS unique_users
FROM page_views
WHERE event_time >= now() - INTERVAL 7 DAY
GROUP BY hour, page_path
ORDER BY hour, page_path;

uniq() is ClickHouse's approximate distinct count. It is faster than exact COUNT(DISTINCT) on large datasets.

Filter before aggregation with PREWHERE when the filter column is in the sort key:

SELECT
    country,
    avg(duration_ms) AS avg_duration
FROM page_views
PREWHERE page_path = '/pricing'
WHERE event_time >= now() - INTERVAL 1 DAY
GROUP BY country;

For how to make SQL database faster, sort key alignment and PREWHERE are the first optimizations to apply on ClickHouse tables.

Step 4: Add a materialized view

Materialized views pre-aggregate data on insert. Create a target table and a view that populates it:

CREATE TABLE page_views_hourly
(
    hour        DateTime,
    page_path   LowCardinality(String),
    views       UInt64,
    unique_users AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (page_path, hour);

CREATE MATERIALIZED VIEW page_views_hourly_mv
TO page_views_hourly
AS SELECT
    toStartOfHour(event_time) AS hour,
    page_path,
    count()                   AS views,
    uniqState(user_id)        AS unique_users
FROM page_views
GROUP BY hour, page_path;

Query the rollup with -Merge combinators:

SELECT
    hour,
    page_path,
    sum(views)                    AS total_views,
    uniqMerge(unique_users)       AS unique_users
FROM page_views_hourly
WHERE hour >= now() - INTERVAL 24 HOUR
GROUP BY hour, page_path
ORDER BY hour;

Dashboard queries against rollups scan far fewer rows than raw events. For real-time analytics implementation, materialized views are the standard pre-aggregation pattern.

Step 5: Set a TTL rule

TTL deletes old data automatically during background merges:

ALTER TABLE page_views
    MODIFY TTL event_time + INTERVAL 90 DAY;

Partitions older than 90 days expire without manual DELETE statements. For analytics at scale, TTL keeps storage bounded as event volume grows.

5 common mistakes in your first ClickHouse project

1. Wrong sort key

Putting user_id first when every query filters by event_time and page_path forces full scans. The sort key should match your most common WHERE and GROUP BY columns.

2. Tiny inserts

Single-row inserts create many small parts and slow merges. Batch inserts in groups of thousands of rows per request.

3. Using COUNT(DISTINCT) everywhere

uniq() or uniqCombined() delivers approximate distinct counts with lower memory use on billion-row tables.

4. Skipping partitions

A table without PARTITION BY on time-series data cannot drop old months efficiently. Monthly partitions are the default starting point.

5. Querying raw events for dashboards

Production dashboards should query materialized views or rollup tables. Raw event scans work for exploration, not for charts refreshed every few seconds.

Tinybird for running this tutorial

You can run this tutorial on a local ClickHouse instance, ClickHouse Cloud, or Tinybird. Tinybird is managed ClickHouse with developer tooling: define Data Sources as .datasource files, explore and query in the Workspace, write SQL in Pipes, and publish them as sub-second API endpoints.

The tutorial maps directly to Tinybird:

1. Create a .datasource file with the page_views schema.
2. Ingest sample rows via the Events API or tb datasource append.
3. Write the aggregation SQL as a Pipe.
4. Deploy and call the Pipe as an API endpoint.

For build real-time apps, the path from tutorial table to production API is the same: Data Source, Pipe, endpoint.

Resend processes 100TB per month on Tinybird with 62ms p90 query latency without relying on cache, per Tinybird's Resend customer story. Tinybird is SOC 2 Type II certified.

Branches give you zero-copy environments with prod data for iterating on schema and queries. The ClickHouse SQL in this tutorial runs unchanged inside Tinybird Pipes.

Where to go next

After this tutorial, extend the same table with Kafka ingestion, additional rollups, and sampling for exploratory queries. The ClickHouse course covers intermediate topics including deduplication, ReplacingMergeTree for CDC, and query profiling.

The patterns in this tutorial, MergeTree schema design, batch ingestion, aggregation queries, materialized views, and TTL, are the foundation for every ClickHouse analytics project.