Topolearn

Learn ClickHouse

Open-source column-oriented database for real-time analytics on huge tables

16 lessons · 3 hours · 4 levels · covers ClickHouse 25.8 LTS and newer

ClickHouse is an open-source columnar OLAP database that scans and aggregates billions of rows per second on a single server and scales out to clusters. Teams use it for product analytics, observability (logs, metrics, traces) and any dashboard that must stay fast on raw event data. Learning it means learning how MergeTree tables, sort keys and batch inserts shape performance.

Sign in free to track progressOfficial docs ↗

Before you start

ClickHouse builds on these; the tree links to the exact lessons you need.

SQLEssentials level

How deep do you want to go?

1. EssentialsRead and write simple code, do the everyday basics5 lessons · 52 min in total
2. PracticalUse it productively at work, with the usual idioms10 lessons · 2 hours in total
3. AdvancedThe less common features, performance and trade-offs13 lessons · 3 hours in total
4. ExpertInternals and the edge cases experts know16 lessons · 3 hours in total

The ClickHouse tree

ClickHouseWhat ClickHouse is and how to run itWhat ClickHouse isand how to run itYour first MergeTree tableYour first MergeTreetableData types for analyticsData types foranalyticsInserting and importing dataInserting andimporting dataQuerying: the ClickHouse dialectQuerying: theClickHouse dialectSort keys and the sparse primary indexSort keys and thesparse primary indexPartitions and TTLPartitions and TTLUpdates, deletes and mutationsUpdates, deletes andmutationsAggregate functions and combinatorsAggregate functionsand combinatorsArrays, maps and arrayJoinArrays, maps andarrayJoinCreating and altering tablesCreating and alteringtablesChoosing data typesChoosing data typesGROUP BY and HAVINGGROUP BY andHAVINGIndex basicsIndex basicsINSERT, UPDATE and DELETEINSERT, UPDATE andDELETEViews and materialized viewsViews andmaterialized viewsInner joinsInner joinsReading query plans with EXPLAINReading query planswith EXPLAIN
Essentials and Practical shown (10 of 16 lessons); the full list is below. Click a lesson to read it.

All 16 lessons, in order

Basics and MergeTree

  1. What ClickHouse is and how to run it · Essentials · 8 min

    A columnar OLAP database built for scanning and aggregating huge tables fast, with server, client and clickhouse-local modes.

  2. Your first MergeTree table · Essentials · 12 min

    Create a MergeTree table with ORDER BY, and understand parts and background merges.

  3. Data types for analytics · Essentials · 10 min

    Pick compact, precise types: sized integers, LowCardinality strings, DateTime64, Decimal, and when Nullable is worth it.

Ingesting and changing data

  1. Inserting and importing data · Essentials · 10 min

    Load data with batched INSERTs, input formats like CSV, JSONEachRow and Parquet, and table functions such as file, url and s3.

  2. Partitions and TTL · Practical · 12 min

    Use PARTITION BY for data lifecycle (drop or move whole months cheaply) and TTL to expire or tier old rows automatically.

  3. Updates, deletes and mutations · Practical · 12 min

    Change existing rows with lightweight DELETE and ALTER TABLE ... UPDATE mutations, and know what they cost.

Querying the ClickHouse way

  1. Querying: the ClickHouse dialect · Essentials · 12 min

    Use ClickHouse's extra SQL: countIf, uniq, date bucketing functions, LIMIT BY, WITH TOTALS and handy aliases.

  2. Aggregate functions and combinators · Practical · 12 min

    Go beyond sum and count: quantiles, argMax, topK, groupArray, and combinators like -If, -Array, -State and -Merge.

  3. Arrays, maps and arrayJoin · Practical · 12 min

    Work with Array and Map columns using arrayJoin, higher-order functions with lambdas, and array aggregates.

  4. Joins and dictionaries · Advanced · 14 min

    Join tables efficiently in ClickHouse, pick join algorithms, and use dictionaries with dictGet for fast in-memory lookups.

Indexes and query performance

  1. Sort keys and the sparse primary index · Practical · 14 min

    Understand granules and the sparse index, and design ORDER BY so that common filters skip most of the table.

  2. Skipping indexes and projections · Expert · 15 min

    Add data-skipping indexes (minmax, set, bloom_filter) and projections to speed up filters the primary key can't serve.

  3. Analyzing and tuning queries · Expert · 15 min

    Use EXPLAIN, system.query_log and system.parts to see what a query read, then fix it with PREWHERE, settings and better keys.

Engines and pre-aggregation

  1. Replacing, Summing and Aggregating engines · Advanced · 15 min

    Use ReplacingMergeTree for upserts by key, SummingMergeTree and AggregatingMergeTree for rollups, and FINAL or GROUP BY to read correctly.

  2. Materialized views for real-time rollups · Advanced · 15 min

    Build incremental materialized views that transform each inserted block into a target table, and refreshable ones for periodic rebuilds.

Clusters and operations

  1. Replication, sharding and clusters · Expert · 16 min

    Scale out with ReplicatedMergeTree and ClickHouse Keeper for copies, Distributed tables for shards, and ON CLUSTER DDL.