ClickHouse Tutorial: Installation with Docker<\/strong><\/h2>\n\n\n\nRun ClickHouse locally with one command:<\/p>\n\n\n\n
docker run -d –name clickhouse-server –ulimit nofile=262144:262144 -p 8123:8123 -p 9000:9000 clickhouse\/clickhouse-server<\/strong><\/p>\n\n\n\nClickHouse listens on port 9000 for the native protocol and port 8123 for HTTP queries. The –ulimit<\/strong> flag sets the maximum number of open files, which ClickHouse needs to perform well under load.<\/p>\n\n\n\nVerify it is running:<\/p>\n\n\n\n
docker ps<\/strong><\/p>\n\n\n\nYou should see the clickhouse-server container listed with status Up.<\/p>\n\n\n\n
Connecting with clickhouse-client<\/strong><\/h2>\n\n\n\nThe interactive SQL shell for ClickHouse is clickhouse-client. Open it directly inside the running container:<\/p>\n\n\n\n
docker exec -it clickhouse-server clickhouse-client<\/strong><\/p>\n\n\n\nYou will see a clickhouse-server \ud83d\ude42<\/strong> prompt. You are now connected and ready to run SQL queries directly against ClickHouse.<\/p>\n\n\n\nCreating a Database and Table<\/strong><\/h2>\n\n\n\nCreate a database first:<\/p>\n\n\n\n
Sql<\/strong><\/p>\n\n\n\nCREATE DATABASE IF NOT EXISTS demo;\nUSE demo;\n<\/code><\/pre>\n\n\n\nNow create a table using the MergeTree engine, the workhorse of ClickHouse:<\/p>\n\n\n\n
sql<\/strong><\/p>\n\n\n\nCREATE TABLE IF NOT EXISTS events\n(\n event_date Date,\n event_time DateTime,\n user_id UInt32,\n event_type LowCardinality(String),\n page_url String,\n duration_ms UInt32\n)\nENGINE = MergeTree()\nPARTITION BY toYYYYMM(event_date)\nORDER BY (event_type, event_time)\nTTL event_date + INTERVAL 90 DAY;\n<\/code><\/pre>\n\n\n\nA few things worth understanding here. PARTITION BY toYYYYMM<\/strong> splits data by month so you can drop old months instantly without scanning the whole table. ORDER BY<\/strong> defines the sparse index and is the most important clause for query performance. LowCardinality(String)<\/strong> is a special type for columns with few unique values like event types or country codes. <\/p>\n\n\n\nIt dramatically reduces storage and speeds up filtering. TTL<\/strong> automatically deletes rows older than 90 days, useful for log data where old events are irrelevant.<\/p>\n\n\n\nInserting and Querying Data<\/strong><\/h2>\n\n\n\nInsert some sample rows:<\/p>\n\n\n\n
Sql<\/strong><\/p>\n\n\n\nINSERT INTO demo.events VALUES\n (today(), now(), 1001, 'page_view', '\/home', 320),\n (today(), now(), 1002, 'click', '\/pricing', 150),\n (today(), now(), 1001, 'page_view', '\/docs', 540);\n<\/code><\/pre>\n\n\n\nRun an analytical query to see ClickHouse in action:<\/p>\n\n\n\n
sql<\/strong><\/p>\n\n\n\nSELECT\n event_type,\n count() AS total_events,\n avg(duration_ms) AS avg_duration\nFROM demo.events\nWHERE event_date >= today() - 7\nGROUP BY event_type\nORDER BY total_events DESC;\n<\/code><\/pre>\n\n\n\nClickHouse returns this aggregation in milliseconds even when the table contains billions of rows, because it only reads the event_type<\/strong>, event_date<\/strong>, and duration_ms<\/strong> columns from disk.<\/p>\n\n\n\nConnecting with Python<\/strong><\/h2>\n\n\n\nInstall the official ClickHouse Python client:<\/p>\n\n\n\n
pip install clickhouse-connect<\/strong><\/p>\n\n\n\nConnect and query:<\/p>\n\n\n\n
Python<\/strong><\/p>\n\n\n\nimport clickhouse_connect\n\nclient = clickhouse_connect.get_client(\n host='localhost',\n port=8123,\n username='default',\n password=''\n)\n\nresult = client.query(\"\"\"\n SELECT event_type, count() as cnt, avg(duration_ms) as avg_ms\n FROM demo.events\n GROUP BY event_type\n ORDER BY cnt DESC\n\"\"\")\n\nfor row in result.result_rows:\n print(row)\n\nclient.close()\n<\/code><\/pre>\n\n\n\nThe clickhouse-connect<\/strong> package is the officially supported Python driver for ClickHouse in 2026, replacing older alternatives.<\/p>\n\n\n\nWhen to Use ClickHouse<\/strong><\/h2>\n\n\n\nUse Case<\/strong><\/td>Why ClickHouse<\/strong><\/td><\/tr>Real-time dashboards<\/td> Sub-second aggregations on billions of events<\/td><\/tr> Log and event analytics<\/td> Handles high ingestion rates with columnar compression<\/td><\/tr> Time-series workloads<\/td> Partitioning by time makes range queries extremely fast<\/td><\/tr> User behaviour funnels<\/td> GROUP BY and window functions perform at scale<\/td><\/tr> Application performance monitoring<\/td> Replaces Elasticsearch for metrics at a fraction of the cost<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n\n
\ud83d\udca1 Did You Know?<\/h3>\n \n ClickHouse processes hundreds of millions to over a billion rows and tens of gigabytes of data per server per second. At Cloudflare, ClickHouse handles over 6 million queries per second across its global network monitoring infrastructure. The same query that takes minutes on PostgreSQL returns in milliseconds on ClickHouse at that scale.<\/li>\n <\/ul>\n<\/div>\n\n\n\nCommon Mistakes to Avoid<\/strong><\/h2>\n\n\n\n\nUsing ClickHouse as a transactional database.<\/strong> ClickHouse lacks full ACID compliance and updates are expensive. It is purpose-built for fast reads on large datasets, not frequent single-row writes and updates. Use PostgreSQL for transactional data and ClickHouse for analytics on top of it.<\/li>\n\n\n\nChoosing a poor ORDER BY clause.<\/strong> The ORDER BY columns in MergeTree are your sparse index. If you filter most queries by event_type and event_date, those should be first in the ORDER BY. A mismatched ORDER BY means full table scans on every query and eliminates ClickHouse’s primary performance advantage.<\/li>\n\n\n\nNot using LowCardinality for string columns with few unique values.<\/strong> Columns like country, event_type, status, or device_type have hundreds of distinct values at most. Wrapping them in LowCardinality cuts storage by up to 10x and significantly speeds up GROUP BY and WHERE filters on those columns.<\/li>\n<\/ul>\n\n\n\nConclusion<\/strong><\/h2>\n\n\n\nThis ClickHouse tutorial covered the full beginner workflow: running ClickHouse with Docker, connecting with clickhouse-client, creating a database and MergeTree table with partitioning and TTL, inserting and querying data, and connecting from Python. ClickHouse’s columnar architecture, SQL compatibility, and free open-source license make it one of the most powerful choices for real-time analytics in 2026.<\/p>\n\n\n\n
FAQs<\/strong><\/h2>\n\n\n\n
\n
\n
1. What is ClickHouse?<\/strong>\u00a0<\/h3>\n\n\n
ClickHouse is a free, open-source columnar database designed for real-time OLAP queries. It stores data by column rather than by row, enabling aggregation queries on billions of rows to return in milliseconds.<\/p>\n\n<\/div>\n<\/div>\n
\n
2. Is ClickHouse free?<\/strong>\u00a0<\/h3>\n\n\n
Yes. ClickHouse is open-source under the Apache 2.0 license. ClickHouse Cloud offers a managed service with a free trial for teams that prefer not to self-host.<\/p>\n\n<\/div>\n<\/div>\n
\n
3. What is the MergeTree engine in ClickHouse?<\/strong><\/h3>\n\n\n
MergeTree is the primary table engine in ClickHouse for analytical workloads. It stores data sorted by the ORDER BY key, creates a sparse index for fast lookups, and supports partitioning, TTL, and data replication.<\/p>\n\n<\/div>\n<\/div>\n
\n
4. Can I use SQL with ClickHouse?<\/strong><\/h3>\n\n\n
Yes. ClickHouse supports standard SQL for SELECT, INSERT, CREATE, and most common operations. Some advanced SQL features differ slightly from PostgreSQL, but most analytical queries work without modification.<\/p>\n\n<\/div>\n<\/div>\n
\n
5. Is ClickHouse suitable for transactional workloads?<\/strong><\/h3>\n\n\n
No. ClickHouse is designed for analytical reads, not frequent single-row updates and deletes. Use a row-based database like PostgreSQL for transactional data and feed ClickHouse for analytics.<\/p>\n\n<\/div>\n<\/div>\n<\/div>\n<\/div>","protected":false},"excerpt":{"rendered":"
Traditional databases slow to a crawl when you ask them to aggregate billions of rows. ClickHouse was built specifically so that question takes milliseconds instead of minutes. This ClickHouse tutorial shows you how to get it running with Docker, create your first table with the MergeTree engine, insert data, run analytical queries, and connect from […]<\/p>\n","protected":false},"author":65,"featured_media":121193,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[325,959],"tags":[],"views":"37","authorinfo":{"name":"Jebasta","url":"https:\/\/www.guvi.in\/blog\/author\/jebasta\/"},"thumbnailURL":"https:\/\/www.guvi.in\/blog\/wp-content\/uploads\/2026\/07\/ClickHouse-300x116.webp","_links":{"self":[{"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/posts\/119550"}],"collection":[{"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/users\/65"}],"replies":[{"embeddable":true,"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/comments?post=119550"}],"version-history":[{"count":3,"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/posts\/119550\/revisions"}],"predecessor-version":[{"id":121196,"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/posts\/119550\/revisions\/121196"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/media\/121193"}],"wp:attachment":[{"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/media?parent=119550"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/categories?post=119550"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/tags?post=119550"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}