Learn ClickHouse® - A Fast Open-Source OLAP DBMS

Explore ClickHouse SQL, its ANSI-like dialect, and how keywords, identifiers, clauses, and expressions form queries. Learn to run examples with the ClickHouse client and understand dql, ddl, and dml.

Explore data definition language commands in ClickHouse SQL, including create, rename, truncate, and drop for databases, tables, views, and users.

Explore ClickHouse data query language by constructing select statements with from, where, group by, and order by clauses, and practice with limit and distinct examples.

Learn how to manipulate data in Clickhouse using dml commands, including insert, update (mutations), delete, and lightweight delete on merge tree engines; understand syntax with alter table and where clauses.

Explore ClickHouse sql data definition language by using alter table to add, rename, clear, and drop columns on a merge table engine, with string columns and null values.

Understand ClickHouse views, including regular, parameterized, and materialized types, how to create them with ddl statements, and how each stores and queries data differently.

Explore ClickHouse joins and unions, including inner, left, right, full, and cross joins, with on clauses and related columns, plus union all and distinct results.

Explore Clickhouse operators, including mathematical, comparison, and logical operators, plus is null and is not null checks, with examples and their corresponding functions.

Explore the supported data types in Clickhouse, including numeric types (integers, unsigned integers, floating point, decimals), strings, dates and date times, plus arrays, tuples, and nested schemas.

Explore ClickHouse numeric data types including signed and unsigned integers, 8 to 256 bit, floats, decimals with precision and scale, booleans, infinity and NaN, plus division behavior.

Explore clickhouse string data types, including string and fixed string, with fixed strings limited by a predefined byte length. See how insertion handles encoding and padding with null bytes.

Learn how ClickHouse stores calendar dates with date and date32, and timestamps with date_time and date_time64, including time zone handling and precision.

Explore arrays and tuples in ClickHouse, create arrays with the array function or square brackets, support multidimensional arrays and nulls, and declare tuples with the tuple function.

Explore the nested data type in Clickhouse, storing semi-structured data in a structured table with a nested column and subcolumns, and reading with the dot operator.

Explore ClickHouse data types for low cardinal data, including enumerated types and low cardinality with dictionary encoding, enabling faster read and write of columns with few unique values.

Explore ClickHouse geo data types, including point, ring, polygon, and multipolygon, stored as 64-bit coordinates and arrays, then see a table example inserting these values.

Learn how the map data type in Clickhouse stores key value pairs in a table, with keys as string, integer, date, or enum, and how to read values by key.

Design a ClickHouse table for wearable metrics by choosing data types for fields like device id, owner, gender, timestamp, heart rate, oxygen saturation, distance, steps, calories, position, altitude, and unit.

Explore how the merge table engine family in ClickHouse organizes data on disk, enabling replication, sharding, and data skipping for workloads, including collapsing and aggregating merge trees and replicas.

Explore why the merge tree names the ClickHouse table engine, and how data parts are created in a data path and merged over time into a hierarchical structure.

Explore how the merge tree engine merges data parts by sorting on the primary key, creating new data paths and discarding old parts, guided by index granularity and granule concepts.

Explore how ClickHouse organizes merge tree data in the data directory, including primary index, binary and Mach files, and wide versus compact formats determined by min bytes for wide part.

Review how the primary index file stores the first row of each granule in ClickHouse, with data sorted by the primary key ID and fixed versus adaptive index granularity.

Explore how ClickHouse stores column data in bin files with per-column compression, blocks and granules, and how index granularity and block size settings shape storage.

Explore how Mac files mark granules and blocks to locate data efficiently. Understand how mark files map granules to block and granule offsets for retrieval.

Explore how primary key and order by keys shape sorting and the primary index file in merge table engines, including cases where primary keys are prefixes of sorting keys.

Master the syntax to create a merge tree table, including optional if not exists, database, on cluster options, column definitions, ttl rules, and order by and sample by.

Create a partitioned table in clickhouse with id, name, quantity, and date; use lz4 for name, zstd for quantity, partition by date, and rely on primary key id for sorting.

Master partition management in ClickHouse by using alter table operations to freeze and unfreeze, detach and attach, move, replace, update, and drop partitions.

Explore the non replicated plain merge table engines in ClickHouse. Compare aggregating merge tree, collapsing merge tree, and versioned engines with practical examples.

Explore replacing merge tree engine in ClickHouse, learn how deduplication uses sorting keys and the final keyword to keep the latest row for each key, with version and is_deleted options.

Explore the summing merge tree engine in ClickHouse, which consolidates rows sharing a sorting key into a single row by summing numeric columns during background merge.

Understand aggregating merge tree engine, which reduces rows with the same sorting key into a single row storing aggregating states, and uses state and merge combinators for insertions and reads.

Explore how the collapsing merge tree engine uses a sign column with values minus one or plus one to collapse state and cancelled rows, enabling updates and deletes in ClickHouse.

Explore the version collapsing merge Streetable engine in Clickhouse, a versioned variant for continuously changing data using a version and sign column with background merges.

Discover how data skipping indexes in ClickHouse boost performance by skipping granules. Learn types like min max, set, and bloom filter indexes, and how to configure granularity and index expressions.

Choose the right ClickHouse merge tree engine by data: append-only uses plain merge tree, updates use replacing or collapsing trees, and real-time analytics use aggregating merge tree.

Explore clickhouse's four engine categories, focusing on log engines and special table engines that solve specific problems, enabling RAM storage and web-server file access with various formats.

Explore the file table engine in ClickHouse, store data in formats like csv, json, avro, and more, and walk through creating a database and table, inserting rows, and viewing data.

Learn how the merge table engine in ClickHouse enables parallel reading from multiple similar tables, with no inserts, and view data sources via the virtual _table column.

Explore the set table engine in ClickHouse, storing data in RAM with optional disk persistence and enabling reads via the in clause rather than select.

Explore the memory table engine in ClickHouse, where data lives in RAM and is lost on restart, ideal for testing but limited for large datasets.

Learn how the buffer table engine buffers inserts in RAM and flushes to the storage table based on time, rows, or bytes, with volatile data that is lost on shutdown.

Learn how the generate random table engine in ClickHouse creates on-the-fly random data for a simple schema, supports select queries, and enables inserting random rows into other tables for testing.

Explore the url table engine in ClickHouse, which reads from a remote http(s) server and translates select to get and insert to post, with url, file type, compression, and auto-detect.

Explore clickhouse dictionaries as in-memory key-value structures that replace joins for faster lookups. Load them from external sources, refresh automatically, and define them via SQL for efficient querying.

Learn how the log family in ClickHouse enables lightweight, append-only tables with three engines—tiny log, log, and stripe log—each lacking replication and mutations, emphasizing multi-thread reading through mark files.

Explore how ClickHouse clusters enable high availability, fault tolerance, and scalable load balancing across multiple nodes. Learn data replication and sharding for distributed query processing in a shared-nothing architecture.

Describe data replication in ClickHouse using Zookeeper-based replicated tables, with leader-follower dynamics, macros for replica identity, and how inserts propagate to replicas to ensure consistency.

Explore how to shard a Clickhouse table with the distributed table engine, splitting data across multiple nodes for parallel queries and scalable storage, using shard keys, weights, and hashing.

Learn to replicate a sharded two-shard, two-replica ClickHouse cluster using zookeeper, replicated merge tree, and distributed tables; configure, deploy, and verify data distribution.

Explore ClickHouse external integrations to connect and ingest data from relational databases, non-relational databases, data lakes, streaming sources, and business intelligence tools, leveraging built-in and community offerings.

Explore how to set up a Docker-based MySQL instance and connect it with ClickHouse using the MySQL table engine to read and write data seamlessly.

Explore how the PostgreSQL table engine in Clickhouse enables seamless read and write operations to a Postgres database, using docker compose to set up and access data.

Explore how the MongoDB table engine in ClickHouse queries and ingests documents from a MongoDB collection, translating queries into MongoDB language with limited support for where, limit, and order by.

Explore how ClickHouse uses the Apache Kafka table engine to publish and pull messages from Kafka topics via materialized views in a docker-based demonstration.

ClickHouse integrates with cloud storage like AWS S3 to store, read, and analyze data, enabling data archival, ingestion, and cloud-based queries via S3 table and queue engines.

Explore role based access control in Clickhouse, defining users, roles, permissions, quotas, and settings profiles, and applying a SQL driven RBAC workflow to assign access.

Create and manage Clickhouse users and roles using create, alter, drop, grant, and revoke statements, with a default user, role-based access control, and the ability to list users.

Explore how ClickHouse roles act as containers for privileges and assign permissions to users, including creating, granting, revoking, altering, and dropping roles with practical examples.

Create and assign a settings profile to roles and users in ClickHouse, customize settings such as max threads, view existing profiles, and drop profiles.

Learn how quotas control resource usage in ClickHouse, limiting queries, results size, bytes read, and execution time for users and roles. Create, alter, show, and drop quotas with time intervals.

Explore a real-world clickhouse rbac scenario with admin, data owner, and dashboard roles, including permissions, quotas, and async insert settings on a fresh server.

Configure a ClickHouse server by managing global and query level settings in config.xml and users.xml, using XML or YAML formats, and test custom SQL underscore settings with get setting function.

Explore how the system database in ClickHouse hosts read-only system tables stored in RAM to monitor, troubleshoot, and configure the server.

Discover how to back up and restore clickhouse tables with full and incremental backups, stored locally or on AWS S3 or Azure Blob, with optional compression and encryption.

Explore Clickhouse local, a lightweight tool that runs without a full server to query local files using the file engine across multiple formats for development and testing.

Learn how the Clickhouse benchmark tool connects to a server, repeatedly runs queries, and reports metrics like queries per second, rows per second, and execution time percentiles.