---
title: "ClickHouse tutorial for analytics beginners"
excerpt: "Learn ClickHouse: MergeTree tables, inserts, aggregations, materialized views, and TTL. Run the SQL locally or on Tinybird."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-07-21 00:00:00"
publishedOn: "2026-07-21 00:00:00"
updatedOn: "2026-07-21 00:00:00"
status: "published"
---

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](https://clickhouse.com/docs). 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:

```sql
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](https://clickhouse.com/docs/en/sql-reference/data-types/lowcardinality).

For a deeper walkthrough of table definitions, see [ClickHouse create table example](https://www.tinybird.co/blog/clickhouse-create-table-example).

## Step 2: Insert sample data

Insert rows individually or in batches:

```sql
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](https://clickhouse.com/docs/en/engines/table-engines/integrations/kafka) consumes topics directly without a custom consumer application.

## Step 3: Run your first aggregation

Count page views by path:

```sql
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:

```sql
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:

```sql
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](https://www.tinybird.co/blog/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:

```sql
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:

```sql
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](https://www.tinybird.co/blog/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:

```sql
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](https://www.tinybird.co/blog/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](https://www.tinybird.co/). 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](https://www.tinybird.co/docs/ingest/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](https://www.tinybird.co/blog/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](https://www.tinybird.co/customer-stories/resend). Tinybird is SOC 2 Type II certified.

[Branches](https://www.tinybird.co/) 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](https://www.tinybird.co/blog/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.

{% cta
  title="Run this tutorial without a cluster"
  text="Tinybird is managed ClickHouse with a Workspace, Events API, and sub-second SQL APIs. Start the tutorial in minutes."
  button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"}
/%}