Skip to content
Tutorials Center
My learning

Topics

ClickHouse Tutorial

Learn ClickHouse step by step: 35 lessons in 9 chapters with examples, quizzes, diagrams and projects.

Start learning →

of lessons completed

Getting Started

  1. 1 What Is ClickHouse? Learn what ClickHouse is, why it answers analytical queries over billions of rows in milliseconds, where it fits, and where it is the wrong tool.
  2. 2 Row vs Column Storage Compare row-oriented and column-oriented storage, see why column stores read less and compress better, and learn the trade-offs for inserts and lookups.
  3. 3 Your First Table and Queries Create your first ClickHouse table with the MergeTree engine, insert rows, and run SELECT queries, with SQLite stand-ins you can run in the browser.
  4. 4 ClickHouse Data Types Learn the ClickHouse data types: sized integers, floats and Decimal, String, Date and DateTime, Enum, LowCardinality, Nullable, Array, Tuple, Map and JSON.

Architecture and Setup

  1. 5 Architecture: Parts, Granules and Merges Understand how ClickHouse stores data: immutable parts, column files, granules and marks, background merges, and how a query is executed in parallel.
  2. 6 Install and Run ClickHouse Install and start ClickHouse with the official quick-install script, Linux packages, Docker or ClickHouse Cloud, check it works, and know the default ports and folders.
  3. 7 Clients, HTTP and Data Formats Talk to ClickHouse with clickhouse-client, the HTTP interface and clickhouse-local, and learn the input and output formats: CSV, TSV, JSONEachRow, Parquet and Pretty.
  4. 8 Settings, Users and Configuration Learn how ClickHouse settings work at server, user and query level, how to create users and roles with GRANT, use profiles and quotas, and organise config files.

Tables and MergeTree Engines

  1. 9 MergeTree: ORDER BY and the Primary Key Master the MergeTree engine: how ORDER BY becomes the sorting key and sparse primary index, how to choose key columns, and how to check that a query uses it.
  2. 10 Partitioning and Parts Learn what PARTITION BY does in ClickHouse, why it is for data management and not speed, how to choose a partition key, and how to drop or move partitions.
  3. 11 Replacing and Collapsing Engines Handle updates and duplicates in ClickHouse with ReplacingMergeTree, CollapsingMergeTree and VersionedCollapsingMergeTree, including FINAL, version columns and pitfalls.
  4. 12 Summing and Aggregating Engines Pre-aggregate data in ClickHouse with SummingMergeTree and AggregatingMergeTree: sum columns on merge, store aggregate states, and read them with -State and -Merge.
  5. 13 TTL, Codecs and Mutations Expire data automatically with TTL, shrink columns with compression codecs like Delta and ZSTD, and change tables with ALTER, mutations and lightweight deletes.

Querying and Functions

  1. 14 SELECT in ClickHouse: Extras Go beyond standard SELECT with ClickHouse features: LIMIT BY, WITH FILL, PREWHERE, SAMPLE, WITH TOTALS, ARRAY JOIN, aliases, CTEs, and the FORMAT clause.
  2. 15 Date, Time and String Functions Learn the ClickHouse functions you use daily: toStartOfHour, toDate, dateDiff, formatDateTime, time zones, plus string, regex, URL and JSON extraction functions.
  3. 16 Arrays and Lambda Functions Work with ClickHouse arrays: create and index them, use arrayMap, arrayFilter and arrayExists lambdas, ARRAY JOIN, groupArray and arrayReduce on real data.
  4. 17 Aggregate Functions and Combinators Learn ClickHouse aggregates: count, sum, uniq and uniqExact, quantile, argMax, topK, any, and the combinators -If, -Array, -OrDefault, -State and -Merge.
  5. 18 Joins, Subqueries and Dictionaries Learn ClickHouse join types and algorithms, ASOF and ANY joins, IN subqueries, and why dictionaries with dictGet are often faster than joining small lookup tables.

Aggregation and Materialized Views

  1. 19 GROUP BY Modifiers: ROLLUP, CUBE and TOTALS Add subtotals and grand totals in ClickHouse with WITH ROLLUP, WITH CUBE and WITH TOTALS, plus GROUPING SETS, and learn the same logic with portable SQL.
  2. 20 Materialized Views Understand ClickHouse materialized views as insert-time triggers: how TO tables work, what happens on backfill, common pitfalls and a runnable trigger stand-in.
  3. 21 AggregatingMergeTree and SummingMergeTree Learn how SummingMergeTree and AggregatingMergeTree roll up rows during background merges, how -State and -Merge store aggregates, and why you must re-aggregate.

Performance and Indexing

  1. 22 The Sparse Primary Index Learn how the ClickHouse sparse primary index works: granules, marks, ORDER BY key choice, why column order matters and how to check pruning with EXPLAIN.
  2. 23 Data Skipping Indexes Learn ClickHouse data skipping indexes: minmax, set, bloom_filter, ngrambf and tokenbf, how GRANULARITY works, how to add and materialize them and when they do not help.
  3. 24 Projections, Codecs and LowCardinality Speed up and shrink ClickHouse tables: projections with another sort order, per-column codecs such as ZSTD, Delta and DoubleDelta, and LowCardinality strings.
  4. 25 Query Profiling and Optimization Find and fix slow ClickHouse queries with EXPLAIN, system.query_log, read_rows, PREWHERE, FINAL costs, join order and settings, plus a SQL stand-in to compare plans.

Replication and Clusters

  1. 26 Replication and ClickHouse Keeper Learn how ClickHouse replicates data with ReplicatedMergeTree and ClickHouse Keeper: replication log, macros, adding a replica, quorum inserts and read-after-write settings.
  2. 27 Sharding and Distributed Tables Scale ClickHouse horizontally with shards: the Distributed engine, sharding keys, cluster configuration, distributed vs local queries, joins and resharding trade-offs.
  3. 28 Cluster Failures and Consistency What happens when a ClickHouse replica, shard or Keeper node fails: insert deduplication, retries, read-only replicas, quorum, recovery steps and idempotent loading patterns.

Integrations and Operations

  1. 29 Loading Data and Insert Patterns Load data into ClickHouse the right way: batch sizes, INSERT formats, clickhouse-client and HTTP loading, asynchronous inserts, schema inference and common loading mistakes.
  2. 30 Table Functions and Integration Engines Query and ingest from outside sources in ClickHouse: s3, url, file, remote and mysql table functions, Kafka and PostgreSQL engines, dictionaries and database engines.
  3. 31 Users, Roles, Quotas and Security Secure ClickHouse with SQL-driven access control: users, roles, GRANT, row policies, settings profiles, quotas, network rules, TLS and safe defaults for production.
  4. 32 Monitoring, Backups and Upgrades Run ClickHouse in production: system tables for monitoring, alerts that matter, BACKUP and RESTORE, TTL and disk tiering, mutations, and rolling upgrades safely.

Projects and Practice

  1. 33 Project: A Web Analytics Pipeline Design a web analytics backend in ClickHouse: raw events table, sorting key, materialized-view rollups, funnel and retention queries, with SQL stand-ins you can run.
  2. 34 Project: IoT Metrics and Rollups Model sensor time series in ClickHouse: compact schema with codecs, tiered rollups by minute and hour, anomaly and gap detection, retention, with SQL stand-ins to run.
  3. 35 Practice Questions and Interview Prep Test your ClickHouse knowledge with hands-on exercises, design scenarios and common interview questions on MergeTree, indexing, replication, rollups and operations.