ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/tips-and-tricks/community-wisdom.md)

*These interactive guides represent collective wisdom from hundreds of production deployments. Each runnable example helps you understand ClickHouse patterns using real GitHub events data - practice these concepts to avoid common mistakes and accelerate your success.*

Combine this collected knowledge with our [Best Practices](/docs/best-practices) guide for optimal ClickHouse Experience.

## Problem-specific quick jumps[​](#problem-specific-quick-jumps "Direct link to Problem-specific quick jumps")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Issue Document Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Production issue** [Debugging insights](/docs/community-wisdom/debugging-insights) Community production debugging tips|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Slow queries** [Performance optimization](/docs/community-wisdom/performance-optimization) Optimize Performance|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Materialized views** [MV double-edged sword](/docs/tips-and-tricks/materialized-views) Avoid 10x storage instances|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Too many parts** [Too many parts](/docs/tips-and-tricks/too-many-parts) Addressing the 'Too Many Parts' error and performance slowdown|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | **High costs** [Cost optimization](/docs/community-wisdom/cost-optimization) Optimize Cost|  |  |  | | --- | --- | --- | | **Success stories** [Success stories](/docs/community-wisdom/creative-use-cases) Examples of ClickHouse in successful use cases | | | | | | | | | | | | | | | | | | | | |

**Last Updated:** Based on community meetup insights through 2024-2025  
**Contributing:** Found a mistake or have a new lesson? Community contributions welcome

- [Problem-specific quick jumps](#problem-specific-quick-jumps)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/tips-and-tricks/materialized-views.md)

*This guide is part of a collection of findings gained from community meetups. For more real world solutions and insights you can [browse by specific problem](/docs/tips-and-tricks/community-wisdom).*
*Too many parts bogging your database down? Check out the [Too Many Parts](/docs/tips-and-tricks/too-many-parts) community insights guide.*
*Learn more about [Materialized Views](/docs/materialized-views).*

## The 10x storage anti-pattern[​](#storage-antipattern "Direct link to The 10x storage anti-pattern")

**Real production problem:** *"We had a materialized view. The raw log table was around 20 gig but the view from that log table exploded to 190 gig, so almost 10x the size of the raw table. This happened because we were creating one row per attribute and each log can have 10 attributes."*

**Rule:** If your `GROUP BY` creates more rows than it eliminates, you're building an expensive index, not a materialized view.

## Production materialized view health validation[​](#mv-health-validation "Direct link to Production materialized view health validation")

This query helps you predict whether a materialized view will compress or explode your data before you create it. Run it against your actual table and columns to avoid the "190GB explosion" scenario.

**What it shows:**

- **Low aggregation ratio** (<10%) = Good MV, significant compression
- **High aggregation ratio** (>70%) = Bad MV, storage explosion risk
- **Storage multiplier** = How much bigger/smaller your MV will be

```
-- Replace with your actual table and columns
SELECT 
    count() as total_rows,
    uniq(your_group_by_columns) as unique_combinations,
    round(uniq(your_group_by_columns) / count() * 100, 2) as aggregation_ratio
FROM your_table
WHERE your_filter_conditions;

-- If aggregation_ratio > 70%, reconsider your MV design
-- If aggregation_ratio < 10%, you'll get good compression
```

## When materialized views become a problem[​](#mv-problems "Direct link to When materialized views become a problem")

**Warning signs to monitor:**

- Insert latency increases (queries that took 10ms now take 100ms+)
- "Too many parts" errors appearing more frequently
- CPU spikes during insert operations
- Insert timeouts that didn't happen before

You can compare insert performance before and after adding MVs using `system.query_log` to track query duration trends.

## Video sources[​](#video-sources "Direct link to Video sources")

- [ClickHouse at CommonRoom - Kirill Sapchuk](https://www.youtube.com/watch?v=liTgGiTuhJE) - Source of the "over enthusiastic about materialized views" and "20GB→190GB explosion" case study

- [The 10x storage anti-pattern](#storage-antipattern)- [Production materialized view health validation](#mv-health-validation)- [When materialized views become a problem](#mv-problems)- [Video sources](#video-sources)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/index.md)

In this section of the docs you can find our use case guides.

|  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [Observability](/docs/use-cases/observability) Use case guide on how to setup and use ClickHouse for Observability|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Time-Series](/docs/use-cases/time-series) Use case guide on how to setup and use ClickHouse for time-series|  |  |  |  | | --- | --- | --- | --- | | [Data Lake](/docs/use-cases/data-lake) Use case guide on Data Lakes in ClickHouse|  |  | | --- | --- | | [Machine Learning and GenAI](/docs/use-cases/AI/ask-ai) Use case guides for Machine Learning and GenAI applications with ClickHouse | | | | | | | | | |

Was this page helpful?
Machine Learning and GenAI

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/AI_ML/index.md)

ClickHouse is ideally suited as a real-time database to power Machine Learning workloads.
With ClickHouse, it's easier than ever to unleash GenAI on your analytics data.
In this section, you'll find some guides around how ClickHouse is used for
Machine Learning and GenAI.

|  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Section Description|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [AI chat](/docs/use-cases/AI_ML/AIChat) This guide explains how to enable and use the AI Chat feature in the ClickHouse Cloud Console.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Semantic Layer](/docs/use-cases/AI_ML/AIChat/semantic-layer) Learn how to customize the ClickHouse Assistant chat agent with AGENTS.md to provide business-specific rules, calculations, and data structure guidance.|  |  |  |  | | --- | --- | --- | --- | | [MCP](/docs/use-cases/AI/MCP) A collection of guides to get you setup using Model Context Protocol (MCP) with ClickHouse|  |  | | --- | --- | | [AI-powered SQL generation](/docs/use-cases/AI/ai-powered-sql-generation) This feature allows you to describe your data requirements in plain text, which the system then translates into corresponding SQL statements. | | | | | | | | | |

Was this page helpful?
Customize ClickHouse Assistant chat with a semantic layer

## Example: domain terminology[​](#example-terminology "Direct link to Example: domain terminology")

Map business terms to technical implementation:

```
<terminology>
When users refer to "conversions", they mean:
- For e-commerce: transactions WHERE transaction_type = 'purchase'
- For SaaS: subscriptions WHERE subscription_status = 'active' AND first_payment_date IS NOT NULL

"Churn" is calculated as:
COUNT(DISTINCT user_id) WHERE last_active_date < today() - INTERVAL 90 DAY
AND previous_subscription_status = 'active'

"DAU" (Daily Active Users) means:
COUNT(DISTINCT user_id) WHERE activity_date = today()

"Qualified leads" must meet ALL criteria:
- lead_score >= 70
- company_size >= 50
- budget_confirmed = true
- contact_role IN ('Director', 'VP', 'C-Level')
</terminology>
```

- [How it works](#how-it-works)- [Creating AGENTS.md](#creating-agents-md)
    - [Create the saved query](#create-query)- [Add your instructions](#add-instructions)- [Best practices](#best-practices)
      - [Treat context as a finite resource](#finite-resource)- [Find the right altitude](#right-altitude)- [Organize with structured sections](#structured-sections)- [Provide diverse, canonical examples](#canonical-examples)- [Keep it minimal yet complete](#minimal-complete)- [Example: Calculated Metrics from raw data](#example-calculated-metrics)- [Example: Business logic rules](#example-business-logic)- [Example: Data structure quirks](#example-data-quirks)- [Example: domain terminology](#example-terminology)

Was this page helpful?
Getting Started with Data Lakes

Write aggregated results:

```
SET allow_experimental_insert_into_iceberg = 1;

INSERT INTO output_iceberg
SELECT
    url,
    count() AS cnt
FROM hits_clickhouse
GROUP BY url
ORDER BY cnt DESC
```

The resulting Iceberg table is readable by any Iceberg-compatible engine.

**Learn more:** [Writing data to open table formats](/docs/use-cases/data-lake/getting-started/writing-data) covers writing raw data and aggregated results using the UK Price Paid dataset, including schema considerations when mapping ClickHouse types to Iceberg.

## Next steps[​](#next-steps "Direct link to Next steps")

Now that you've seen the full workflow, dive deeper into each area:

- [Querying directly](/docs/use-cases/data-lake/getting-started/querying-directly) — All four formats, cluster variants, table engines, caching
- [Connecting to catalogs](/docs/use-cases/data-lake/getting-started/connecting-catalogs) — Full Unity Catalog walkthrough with Delta and Iceberg
- [Accelerating analytics](/docs/use-cases/data-lake/getting-started/accelerating-analytics) — Schema optimization, indexing, ~40x speedup demo
- [Writing to data lakes](/docs/use-cases/data-lake/getting-started/writing-data) — Raw writes, aggregated writes, type mapping
- [Support matrix](/docs/use-cases/data-lake/support-matrix) — Feature comparison across formats and storage backends

- [Query Iceberg data directly](#query-directly)- [Create a persistent table engine](#table-engine)- [Connect to a catalog](#connect-catalog)- [Issue a query](#issue-query)- [Load a subset into ClickHouse](#load-data)- [Write back to Iceberg](#write-back)- [Next steps](#next-steps)

Was this page helpful?
Accelerating analytics with MergeTree

### Insert data from the catalog[​](#insert-data "Direct link to Insert data from the catalog")

Use `INSERT INTO SELECT` to load the ~300m from the data lake table into our ClickHouse table:

```
INSERT INTO single_day_log SELECT * FROM icebench.`icebench.single_day_log`

282634391 rows in set. Elapsed: 237.680 sec. Processed 282.63 million rows, 5.42 GB (1.19 million rows/s., 22.79 MB/s.)
Peak memory usage: 18.62 GiB.
```

## Re-execute the query[​](#reexecute-query "Direct link to Re-execute the query")

If we now run the same query against the MergeTree table we see performance improves dramatically:

```
SELECT
    logger_name,
    count() AS c
FROM single_day_log
WHERE (thread_name = 'TCPHandler')
    AND (instance_type = 'm6i.4xlarge')
    AND hasToken(message, 'error')
GROUP BY logger_name
ORDER BY c DESC
LIMIT 5

┌─logger_name──────────────┬────c─┐
│ executeQuery             │ 6907 │
│ TCPHandler               │ 4145 │
│ TCP-Session              │  790 │
│ PostgreSQLConnectionPool │  530 │
│ ContextAccess (default)  │  392 │
└──────────────────────────┴──────┘

5 rows in set. Elapsed: 0.220 sec. Processed 13.84 million rows, 2.85 GB (62.97 million rows/s., 12.94 GB/s.)
Peak memory usage: 1.12 GiB.
```

The same query now completes in **0.22 seconds** - a **~40x speedup**. Two key optimizations drive this improvement:

- **Sparse primary index** - The `ORDER BY (instance_type, thread_name, ...)` key means ClickHouse can skip directly to granules matching `instance_type = 'm6i.4xlarge'` and `thread_name = 'TCPHandler'`, reducing the rows processed from 283 million to just 14 million.
- **Full-text index** - The `text_idx` index on the `message` column allows `hasToken(message, 'error')` to resolve via the index rather than scanning every message string, further reducing the data ClickHouse needs to read.

The result is a query that can comfortably power a real-time dashboard - at a scale and latency that querying Parquet files in object storage cannot match.

- [Connect to the catalog](#connect-catalog)
  - [List tables](#list-tables)- [Explore the schema](#explore-schema)- [Query over the data lake table](#query-lakehouse)- [Load data into MergeTree](#load-data)
      - [Create an optimized table](#create-table)- [Insert data from the catalog](#insert-data)- [Re-execute the query](#reexecute-query)

Was this page helpful?
Querying open table formats directly

The [`paimonS3Cluster`](/docs/sql-reference/table-functions/paimonCluster) function distributes reads across multiple nodes in a ClickHouse cluster. The initiator node dispatches data files dynamically to worker nodes for parallel processing. `paimonCluster` is an alias for `paimonS3Cluster`. Variants also exist for Azure ([`paimonAzureCluster`](/docs/sql-reference/table-functions/paimonCluster)) and HDFS ([`paimonHDFSCluster`](/docs/sql-reference/table-functions/paimonCluster)).

```
paimonS3Cluster(cluster_name, url [,access_key_id, secret_access_key] [,format] [,structure] [,compression])
-- paimonCluster is an alias for paimonS3Cluster

paimonAzureCluster(cluster_name, connection_string|storage_account_url, container_name, blobpath, [,account_name], [,account_key] [,format] [,compression_method])

paimonHDFSCluster(cluster_name, path_to_table, [,format] [,compression_method])
```

### Table engine[​](#paimon-table-engine "Direct link to Table engine")

Paimon does not currently have a dedicated table engine in ClickHouse. Use the table functions above for querying Paimon tables.

For supported features including storage backends and more, see the [support matrix](/docs/use-cases/data-lake/support-matrix#format-support). For full reference, see the [`paimon` table function](/docs/sql-reference/table-functions/paimon) documentation.

Was this page helpful?
Writing data to open table formats

Other tools - and other ClickHouse instances - can now read this pre-computed dataset:

```
SELECT
    town,
    price
FROM uk.uk_avg_town
ORDER BY price DESC
LIMIT 10

┌─town───────────────┬──────────────price─┐
│ GATWICK            │ 28232811.583333332 │
│ THORNHILL          │             985000 │
│ VIRGINIA WATER     │  984633.2938574939 │
│ CHALFONT ST GILES  │  863347.7280187573 │
│ COBHAM             │    775251.47313278 │
│ PURFLEET-ON-THAMES │           772651.8 │
│ BEACONSFIELD       │  746052.9327405858 │
│ ESHER              │  686708.4969745865 │
│ KESTON             │  654541.1774842045 │
│ GERRARDS CROSS     │  639109.4084023251 │
└────────────────────┴────────────────────┘

10 rows in set. Elapsed: 0.210 sec.
```

- [Prepare a source dataset](#prepare-source)
  - [Create and populate a MergeTree table](#create-source-table)- [Write data to an Iceberg table](#write-iceberg)
    - [Create the Iceberg table](#create-iceberg-table)- [Insert a subset of data](#insert-subset)- [Query the Iceberg table](#query-iceberg)- [Write aggregated results](#write-aggregates)
      - [Create an Iceberg table for aggregates](#create-aggregate-table)- [Insert aggregated data](#insert-aggregates)- [Query the aggregated table](#query-aggregates)

Was this page helpful?
Catalog guides

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/data_lake/reference/index.md)

ClickHouse integrates with a range of data lake catalogs through the [`DataLakeCatalog`](/docs/engines/database-engines/datalakecatalog) database engine. The following guides walk through connecting ClickHouse to each supported catalog, including configuration, authentication, and querying examples.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Catalog Description|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [AWS Glue](/docs/use-cases/data-lake/glue-catalog) Query Iceberg tables registered in the AWS Glue Data Catalog from data stored in S3.|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Databricks Unity Catalog](/docs/use-cases/data-lake/unity-catalog) Connect to Databricks Unity Catalog for Delta Lake and Iceberg tables.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [Iceberg REST Catalog](/docs/use-cases/data-lake/rest-catalog) Use any catalog implementing the Iceberg REST specification, such as Tabular.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Lakekeeper](/docs/use-cases/data-lake/lakekeeper-catalog) Connect to the Lakekeeper Catalog for Iceberg tables.|  |  |  |  | | --- | --- | --- | --- | | [Project Nessie](/docs/use-cases/data-lake/nessie-catalog) Query Iceberg tables using the Nessie Catalog with Git-like data version control.|  |  | | --- | --- | | [Microsoft OneLake](/docs/use-cases/data-lake/onelake-catalog) Query Iceberg tables in Microsoft Fabric OneLake. | | | | | | | | | | | | | |

Was this page helpful?
REST catalog

## Loading data from your Data Lake into ClickHouse[​](#loading-data-from-your-data-lake-into-clickhouse "Direct link to Loading data from your Data Lake into ClickHouse")

If you need to load data from the REST catalog into ClickHouse, start by creating a local ClickHouse table:

```
CREATE TABLE taxis
(
    `VendorID` Int64,
    `tpep_pickup_datetime` DateTime64(6),
    `tpep_dropoff_datetime` DateTime64(6),
    `passenger_count` Float64,
    `trip_distance` Float64,
    `RatecodeID` Float64,
    `store_and_fwd_flag` String,
    `PULocationID` Int64,
    `DOLocationID` Int64,
    `payment_type` Int64,
    `fare_amount` Float64,
    `extra` Float64,
    `mta_tax` Float64,
    `tip_amount` Float64,
    `tolls_amount` Float64,
    `improvement_surcharge` Float64,
    `total_amount` Float64,
    `congestion_surcharge` Float64,
    `airport_fee` Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(tpep_pickup_datetime)
ORDER BY (VendorID, tpep_pickup_datetime, PULocationID, DOLocationID);
```

Then load the data from your REST catalog table via an `INSERT INTO SELECT`:

```
INSERT INTO taxis 
SELECT * FROM demo.`default.taxis`;
```

- [Local Development Setup](#local-development-setup)
  - [Prerequisites](#local-prerequisites)- [Setting up Local REST Catalog](#setting-up-local-rest-catalog)- [Connecting to Local REST Catalog](#connecting-to-local-rest-catalog)- [Querying REST catalog tables using ClickHouse](#querying-rest-catalog-tables-using-clickhouse)- [Loading data from your Data Lake into ClickHouse](#loading-data-from-your-data-lake-into-clickhouse)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/observability/build-your-own/index.md)

This guide helps you build a custom observability stack using ClickHouse as the foundation. Learn how to design, implement, and optimize your observability solution for logs, metrics, and traces, with practical examples and best practices.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Introduction](/docs/use-cases/observability/introduction) This guide is designed for if you're looking to build your own observability solution using ClickHouse, focusing on logs and traces.|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Schema design](/docs/use-cases/observability/schema-design) Learn why users are recommended to create their own schema for logs and traces, along with some best practices for doing so.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [Managing data](/docs/observability/managing-data) Deployments of ClickHouse for observability invariably involve large datasets, which need to be managed. ClickHouse offers features to assist with data management.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Integrating OpenTelemetry](/docs/observability/integrating-opentelemetry) Collecting and exporting logs and traces using OpenTelemetry with ClickHouse.|  |  |  |  | | --- | --- | --- | --- | | [Using Visualization Tools](/docs/observability/grafana) Learn how to use observability visualization tools for ClickHouse, including HyperDX and Grafana.|  |  | | --- | --- | | [Demo Application](/docs/observability/demo-application) Explore the OpenTelemetry demo application forked to work with ClickHouse for logs and traces. | | | | | | | | | | | | | |

Was this page helpful?
ClickStack - Adminstration

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/observability/clickstack/managing/admin.md)

Most administrative tasks in ClickStack are performed directly on the underlying ClickHouse database. Users deploying ClickStack should be familiar with ClickHouse concepts and administration basics.

Administrative operations typically involve executing DDL statements. The available options depend on whether you're using Managed ClickStack or ClickStack Open Source.

## ClickStack Open Source[​](#clickstack-oss "Direct link to ClickStack Open Source")

For ClickStack Open Source deployments, users perform administrative tasks using the [ClickHouse client](/docs/interfaces/cli). The client connects to the database over the native ClickHouse protocol and supports full DDL and administrative operations, as well as providing interactive feedback on queries.

## Managed ClickStack[​](#clickstack-managed "Direct link to Managed ClickStack")

In Managed ClickStack, users can also use both the ClickHouse client and [SQL Console](/docs/cloud/get-started/sql-console). To connect via the client, users will need to obtain the [credentials for the service](/docs/cloud/guides/sql-console/gather-connection-details).

The [SQL Console](/docs/cloud/get-started/sql-console) is a web-based interface that offers additional convenience, including SQL autocomplete, query history, and built-in charting for result visualization.

- [ClickStack Open Source](#clickstack-oss)- [Managed ClickStack](#clickstack-managed)

Was this page helpful?