Tutorials
Tutorials
Step-by-step walkthroughs you can run against your own ClickHouse.
- TutorialThe ClickHouse Production Incident PlaybookEight incident classes, each as symptom, first checks in SQL, root-cause mechanism, fix and what to watch: memory kills, too many parts, replication backlog, stuck mutations, hanging metadata queries, one saturated node, detached parts and rising error rates.
- TutorialMigrating from ZooKeeper to ClickHouse KeeperHow the keeper-converter really works, why a snapshot taken while the cluster is live produces a quiet time bomb in the replication log, a cut-over plan with hard gates, how to verify the copy znode by znode, how to clean up what is left behind, and what to watch on Keeper afterwards.
- TutorialUpgrading ClickHouse safely: from changelog to rollbackA field guide to taking a replicated cluster from one release line to the next: choosing LTS or stable, turning the changelog into probes against your own estate, the compatibility setting and what it cannot cover, rehearsing on staging, rolling a mixed-version cluster, and the point after which you cannot go back.
- TutorialBuild a game with materialized viewsBuild a guess-the-number game in pure ClickHouse SQL: a Null table for input, materialized views for logic, a one-row EmbeddedRocksDB table for state, and a SELECT for the screen.
- Inserting data well · 4ATTACH PARTITION FROM: the cheapest way to move dataLoad into a staging table, then move whole partitions into the production table with hard links instead of copying rows. Includes REPLACE PARTITION for atomic swaps.
- TutorialMoving to the new analyzerWhat the new query analyzer changes, the queries it rejects, how to migrate them, and how to replace the deprecated neighbor() and runningDifference() functions with window functions.
- TutorialWorking with JSON in ClickHouseLoad JSON with JSONEachRow, store semi-structured documents in the JSON type, query paths with their real types, and export column-oriented JSON formats.
- TutorialThe ClickHouse HTTP interfaceRun queries and insert data with plain curl: query parameters, authentication, formats, safe parameter binding, compression, async inserts and error handling.
- TutorialThe query cache in ClickHouseHow to cache the result of a SELECT for a while, what the cache will refuse to store, and the settings that decide who shares an entry and for how long.
- TutorialReading EXPLAIN in ClickHouseThe EXPLAIN variants (AST, SYNTAX, QUERY TREE, PLAN, PIPELINE, ESTIMATE) and which one answers which question, with real output from a small demo table.
- TutorialClickHouse and S3: table functions, S3Queue and S3 disksThe ways to combine ClickHouse with S3-compatible storage: ad-hoc queries, loading and exporting, continuous ingestion with S3Queue, refreshable views, and S3 as a storage disk.
- TutorialProjections: hidden tables that speed up the base tableA projection stores the same data re-sorted or pre-aggregated inside the table's own parts, and ClickHouse picks it automatically. How to add, backfill, verify and when to prefer a materialized view.
- Window functions · 2Window functions: frames, ranking and lag/leadROWS vs RANGE frames, running and moving averages, row_number/rank/dense_rank, lagInFrame/leadInFrame and top-N per group.
- TutorialHow replication works: parts, replicas, Keeper and mutationsThe mental model behind ReplicatedMergeTree: what lives in Keeper, how an insert, a merge and a mutation travel between replicas, and which system tables show each stage.
- Window functions · 1Window functions: OVER, PARTITION BY and ORDER BYHow window functions differ from scalar and aggregate functions, and how OVER, PARTITION BY, ORDER BY and named windows shape the result.
- TutorialData-skipping indexes: minmax, set and bloom filtersSecondary indexes in ClickHouse do not find rows, they rule out granules. The index types, how to size them, how to prove with EXPLAIN that they help, and why they often do not.
- TutorialCommon table expressions (WITH) in ClickHouseWITH in ClickHouse names constants, expressions, scalar subqueries and whole queries. How to refactor a nested query into CTEs, and what CTEs do and do not do for performance.
- TutorialVector similarity search in ClickHouseVectors, norms and distances from scratch, the built-in distance functions, and how to add an approximate-nearest-neighbour index to a MergeTree table.
- TutorialChoosing column codecs: Delta, DoubleDelta, Gorilla and friendsWhich specialised compression codec fits which column. Measured on a million-row table: timestamps, counters and slowly changing floats, plus the check that stops you using Gorilla on integers.
- Inserting data well · 2Asynchronous inserts explainedHow async_insert batches many small INSERTs on the server, what wait_for_async_insert really trades, and which settings to leave alone.
- Inserting data well · 1Insert batching and the too many parts problemEvery INSERT creates a part. Why small frequent inserts hurt, how big a batch should be, and how to see the damage in system.parts.
- Inserting data well · 3Inserting into Distributed tablesWhat happens when you INSERT into a Distributed table: the initiator node, background forwarding, synchronous mode, shard selection and what to watch when a shard is down.
- TutorialMaterialized views in productionHow ClickHouse materialized views really work: the insert-block rule, explicit targets, Summing vs Aggregating engines with -State/-Merge, chained views, JOIN traps, zero-downtime backfill without POPULATE, refreshable views, and when not to use any of it.
- TutorialRunning SQL scripts in batch with clickhouse-clientGenerate a script of statements with SQL itself, then run it unattended with --queries-file, --echo and long timeouts.
- TutorialORDER BY, PARTITION BY and how queries skip dataWhat the MergeTree sorting key and partition key each do, how to read EXPLAIN to see partition pruning and primary-key pruning, and which filter expressions the pruner understands.
- TutorialLesser-known ClickHouse functions and featuresA tour of small things that save time: format(), dollar-quoted strings, short-circuit evaluation, throwIf, the functions that change row counts, the .null subcolumn and the running* family.
- TutorialTracking query and resource trends from system tablesRoll system.query_log up into a small per-minute table, then answer trend questions with SQL: this week against last week, new and unused query shapes, and disk growth from system.parts.
- TutorialUsers, roles and settings profilesCreate users with SQL, give them permissions through roles, and cap their resources with settings profiles and quotas; includes the XML alternative.
- TutorialJOINs in ClickHouse: kinds, table order and memoryThe join kinds ClickHouse offers (ANY, SEMI, ANTI included), why the right table matters, and what to do when a JOIN runs out of memory: join algorithms, spilling and rewriting.
- TutorialTiered storage with disks, volumes and storage policiesSpread a MergeTree table over several disks: how disks, volumes and policies relate, how to move data with TTL or by hand, and how to add a policy to an existing table.
- TutorialArrays, ARRAY JOIN and lambda functionsBuild arrays with groupArray, take them apart with ARRAY JOIN, and process them with arrayMap, arrayFilter, arrayExists and friends. Includes the row-wise 'any of these columns' idiom.
- TutorialDictionaries in ClickHouseUse a dictionary instead of a JOIN for small lookup tables: create one from a table or a query, read it with dictGet, choose a layout and control how it refreshes.
- TutorialGenerating test data in ClickHouseBuild realistic fake tables with numbers(), the rand family, generateRandom and cross joins, including the gotcha that two rand() calls in one query return the same value.
- TutorialHow MergeTree stores dataParts on disk, partitions, merges (horizontal and vertical), OPTIMIZE and FINAL, mutations versus lightweight DELETE and UPDATE, and TTL: what each does to the files, measured with clickhouse local on 26.4.
- TutorialKafka and ClickHouseThe Kafka concepts a ClickHouse user needs, the Kafka engine plus materialized view pipeline, consumer and partition arithmetic, poison messages and dead letters, lag, rebalances, retention and an operations checklist.
- TutorialUser-defined functions and executable scriptsSQL UDFs, executable UDFs written in Python, pooled processes, and the Executable table engine and table function for generating or transforming data with scripts.