ClickHouse Documentation
Welcome to the ClickHouse documentation hub. This page organizes all our ClickHouse-related articles into logical sections for easy navigation.
SQL DDL: Databases, Tables, and Data
The everyday SQL statements you'll run against a ClickHouse cluster, with the engine choices and gotchas that matter in production.
- CREATE DATABASE - Atomic, Replicated, MySQL, PostgreSQL, and other database engines
- CREATE TABLE - Columns, engines, ORDER BY, PARTITION BY, codecs, TTL, ON CLUSTER
- MergeTree Engine Family - MergeTree, Replicated, Replacing, Summing, Aggregating, Collapsing, Versioned, Graphite
- CREATE MATERIALIZED VIEW - Streaming aggregates, refreshable MVs, Kafka pipelines
- ALTER TABLE - Column changes, mutations, partition operations, MODIFY ORDER BY
- INSERT INTO - VALUES, FORMAT, SELECT, async inserts, deduplication, batch sizing
- DROP TABLE - SYNC, UNDROP, Atomic vs Ordinary semantics
Access Control and Security
Roles, users, grants, and row-level security. The right place to start when setting up multi-tenant access on a shared cluster.
- CREATE USER - Authentication methods, host clauses, default roles, settings limits
- CREATE ROLE - RBAC, default roles, layered roles, settings profiles
- GRANT and REVOKE - Privilege hierarchy, column-level grants, WITH GRANT/ADMIN OPTION
- CREATE ROW POLICY - Row-level security, permissive vs restrictive policies
Table Engines
Learn about ClickHouse's various table engines, each designed for specific use cases and data patterns.
- AggregatingMergeTree: Efficient Data Aggregation - Pre-aggregating data for efficient storage and querying
- CollapsingMergeTree - Managing versioned data with automatic collapsing
- GraphiteMergeTree - Optimized for Graphite monitoring data
- Log Engine - Simple write-once storage engine
- Materialized View - Pre-computed views for faster queries
- Null Engine - Discard all data writes
- ReplacingMergeTree - Automatic deduplication of data
- ReplicatedAggregatingMergeTree - Replicated storage with pre-aggregation
- ReplicatedCollapsingMergeTree - Replicated storage with collapsing
- ReplicatedReplacingMergeTree - Replicated storage with deduplication
- ReplicatedSummingMergeTree - Replicated storage with automatic summation
- ReplicatedVersionedCollapsingMergeTree - Replicated storage with versioning and collapsing
- SummingMergeTree - Automatic summation of numeric columns
- StripeLog - Log-structured storage engine
- TinyLog - Lightweight storage engine for small tables
- VersionedCollapsingMergeTree - Managing versioned data with automatic collapsing
- View - Virtual tables for query simplification
External Engines
Connect ClickHouse with external data sources and systems.
- File Engine - Read data from files on the filesystem
- HDFS Engine - Integration with Hadoop Distributed File System
- JDBC Engine - Connect to external SQL databases
- Kafka Engine - Stream processing with Apache Kafka
- MySQL Engine - Integration with MySQL databases
- ODBC Engine - Connect to any ODBC-compatible database
- RabbitMQ Engine - Message queue integration
- S3 Engine - Store and query data in Amazon S3
- URL Engine - Access data from HTTP/HTTPS endpoints
Functions
Explore ClickHouse's powerful built-in functions for data manipulation and analysis.
Aggregation Functions
- anyheavy Function - Select frequently occurring value
- avg Function - Calculate average values
- countif Function - Count rows matching a condition
- groupUniqArray Function - Create arrays of unique values
- if Function - Conditional aggregation
- max Function - Find maximum values
- median Function - Calculate median values
- min Function - Find minimum values
- quantile Function - Calculate quantiles
- replaceone Function - Select first non-null value
- stddevSamp Function - Sample standard deviation
- sum Function - Sum values
- topK Function - Find most frequent values
- uniq Function - Count unique values
- uniqCombined Function - Approximate count of unique values
Date and Time Functions
- formatDateTime Function - Format date and time values
- parseDateTimeBestEffort Function - Flexible date parsing
- toStartOfInterval Function - Round to interval start
- toUnixTimestamp Function - Convert to Unix timestamp
String Functions
- concat Function - Concatenate strings
- lower Function - Convert to lowercase
- replaceAll Function - Replace all occurrences
- substring Function - Extract substring
Window Functions
- denseRank Function - Calculate dense rank
- lagInFrame Function - Access previous rows
- leadInFrame Function - Access following rows
- rank Function - Calculate rank with gaps
- rowNumberInAllBlocks Function - Generate row numbers
- runningDifference Function - Calculate differences between consecutive rows
- windowFunnel Function - Analyze event sequences
Settings and Configuration
Learn about ClickHouse's configuration options and settings.
- allow_experimental_analyzer - Enable experimental query analyzer
- allow_suspicious_low_cardinality_types - Control low cardinality type warnings
- enable_memory_tracker - Memory usage tracking
- insert_quorum - Control write consistency
- join_use_nulls - NULL handling in joins
- log_queries - Query logging
- max_execution_time - Query timeout
- max_memory_usage - Memory limits
- max_threads - Thread pool size
- optimize_skip_unused_shards - Shard optimization
- preferred_block_size_bytes - Block size tuning
Tools and Utilities
Explore ClickHouse's command-line tools and utilities.
- ClickHouse Client - Command-line interface
- ClickHouse Copier - Data copying utility
- ClickHouse Dictionary - External dictionary management
- ClickHouse Dictionary Lazy Load - Optimize dictionary loading
- ClickHouse Executable Dictionary - Dynamic dictionary updates
- ClickHouse Executable Pool - Resource management
- ClickHouse Join Engine - Join operations
- ClickHouse Live View - Real-time data views
- ClickHouse Memory Management - Memory optimization and monitoring
- ClickHouse Settings Profile - User configuration management
- ClickHouse Trace Log - Detailed logging
Error Handling
Learn about common ClickHouse errors and their solutions.
- Cannot Create New ZooKeeper Session
- Cannot Load Table
- Cannot Read from Socket
- Cannot Write to Table
- Directory Already Exists
- Distributed Query Execution Failed
- Memory Limit Exceeded
- No Active Replicas
- No Query for ID
- Table Readonly Mode
- Table Structure Does Not Match
- Too Many Parts
- Too Many Simultaneous Connections
- Unknown Function
- Wrong Password
- ZooKeeper Node Exists
