Everything we've written about ClickHouse: connections, SQL patterns, imports, clients, and comparisons.
Connect to ClickHouse from Ruby over the HTTP interface with click_house, the clickhouse gem, or clickhouse-activerecord for Rails. Covers the 8123-vs-9000 port trap, parameterized queries, batch inserts, and async_insert durability.
Connect to ClickHouse from PHP over the HTTP interface: the smi2/phpClickHouse client, raw cURL, the 8123-not-9000 port trap, parameter binding, batch inserts, async_insert, TLS on 8443, and the errors you actually hit.
Connect to ClickHouse from C# with the ADO.NET drivers: ClickHouse.Driver, ClickHouse.Client, and Octonica. Ports, parameters, bulk inserts, Dapper, the too-many-parts trap, and the errors you actually hit.
Connect to ClickHouse from Java with the official clickhouse-jdbc driver: the HTTP-port trap, DriverManager and DataSource setup, batch inserts, ClickHouse Cloud TLS, and the errors you actually hit.
Connect to ClickHouse from Go with the official clickhouse-go driver. Working code for the native vs database/sql interface, the 9000 vs 9440 vs 8123 port question, connection pooling, batch inserts, and TLS.
Connect to ClickHouse from Node.js with the official @clickhouse/client. Working code for queries, async inserts, streaming, the HTTP-vs-native port trap, and JSONEachRow result handling.
Connect to ClickHouse from Python with clickhouse-connect or clickhouse-driver. Working code for HTTP and native protocol, async clients, inserts, pandas, and the port mistakes everyone makes.
How data type choice drives compression and query speed in ClickHouse. Covers right-sizing integers, LowCardinality dictionary encoding and when it helps, Enum vs LowCardinality, the real cost of Nullable, FixedString, Date vs DateTime sizing, and avoiding String for everything.
How ClickHouse data skipping indexes work, the five index types (minmax, set, bloom_filter, ngrambf_v1, tokenbf_v1), how GRANULARITY controls the index block size, when a skip index actually helps, and the correlation rule that decides whether it skips anything at all.
How to model key-value and repeated-group data in ClickHouse with Map(K, V), Nested, and Tuple. Covers m[k] access and its linear cost, mapKeys/mapValues subcolumns, mapContains, the Nested-is-parallel-arrays model, the flatten_nested setting, and when to reach for a Map versus a Nested versus parallel arrays.
A practical guide to the MergeTree family in ClickHouse: plain MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree, CollapsingMergeTree and VersionedCollapsingMergeTree. How merges work, what each engine deduplicates or pre-aggregates, the eventual-consistency trap, and how to pick one.
How PARTITION BY works in ClickHouse: choosing a partition key, partition pruning, partition-level operations (DROP, DETACH, ATTACH, MOVE, FREEZE), the too-many-parts problem, and why most teams over-partition.
How projections work in ClickHouse: normal (reordering) projections and aggregating projections, how the optimizer picks them, the storage and write-amplification trade-offs, backfilling existing data, and when a materialized view is the better choice.
How ClickHouse table functions let you SELECT from and INSERT into S3, files, URLs, remote ClickHouse, PostgreSQL, and MySQL without loading data first. Covers s3, file, url, remote, postgresql, mysql, schema inference, glob patterns, and when to promote a table function into a permanent table engine.
How to update and delete rows in ClickHouse: heavyweight mutations (ALTER TABLE UPDATE/DELETE), lightweight deletes, lightweight updates, why they rewrite parts, how to monitor mutation progress in system.mutations, and engine-based alternatives that avoid mutations entirely.
Working with arrays in ClickHouse: building and indexing them, arrayJoin to unfold rows, the higher-order functions arrayMap/arrayFilter/arrayReduce with lambdas, the ARRAY JOIN clause, and the GROUP BY arrays produced by groupArray.
How the WITH clause works in ClickHouse: scalar expressions, named subquery CTEs, the inlining-by-default behavior that catches people out, materialized CTEs, and recursive CTEs for hierarchical data.
How to work with dates and times in ClickHouse: truncation with toStartOf*, dateDiff and date arithmetic, formatting and parsing, time zones, and the rounding gotchas that catch newcomers.
How dictionaries work in ClickHouse: creating them with CREATE DICTIONARY, the dictGet family of lookup functions, choosing a layout (flat, hashed, complex_key_hashed, range_hashed, cache, direct), and using a dictionary as a fast alternative to JOIN.
How incremental materialized views work in ClickHouse: the trigger-on-insert model, the target table and TO syntax, rolling up with SummingMergeTree and AggregatingMergeTree, the -State and -Merge combinators, and the gotchas around backfilling and source-table coupling.
How sampling works in ClickHouse: declaring a SAMPLE BY key, the SAMPLE k / SAMPLE n / SAMPLE k OFFSET m forms, scaling aggregates with _sample_factor, determinism, and when approximate results are worth the speedup.
Working with strings in ClickHouse: length and case, substrings with 1-based indexing, splitting and joining, search and replace including regular expressions, and the UTF-8 versus byte distinction that trips people up.
How TTL works in ClickHouse: row-level deletes, column-level expiry, moving data between storage tiers, rollups with TTL GROUP BY, recompression, when TTL actually fires during merges, and partitioning so whole partitions drop instead of rewriting parts.
How to use ClickHouse aggregate functions, including the -If/-Array/-State/-Merge combinators, approximate counting with uniq, quantiles, and groupArray for analytical queries.
A practical guide to JOINs in ClickHouse: the supported join types and strictness modifiers, the join algorithms you can configure, GLOBAL JOIN for distributed tables, and when a dictionary beats a join.
How to query JSON in ClickHouse, comparing the String + JSONExtract approach with the native JSON data type introduced in v24.8, plus parsing, paths, and performance tradeoffs.
How to use window functions in ClickHouse with OVER, PARTITION BY, and frame clauses. Covers ROW_NUMBER, RANK, LAG/LEAD, moving averages, and the RANGE-default gotcha.
Three ways to import Excel data into ClickHouse: convert to CSV and use clickhouse-client, Python with pandas and clickhouse-connect, and Mako. Covers format conversion, type mapping, and batch loading.
Three ways to import JSON data into ClickHouse: JSONEachRow format with clickhouse-client, the HTTP interface, and Mako. Covers JSON format variants, nested data, and performance.
Four ways to import CSV files into ClickHouse: clickhouse-client piping, INSERT FROM INFILE, the HTTP interface, and the file() table function. Includes performance tips for large datasets.