What does this skill do?

The ClickHouse Analytics Pro Skill provides specialized patterns for schema design, query optimization, and data engineering in ClickHouse. It addresses performance issues in high-volume analytical (OLAP) workloads using MergeTree engines, partitioning, materialized views, and batch ingestion.

MergeTree Diagram Design
Select the appropriate table engine (MergeTree, ReplacingMergeTree, AggregatingMergeTree) based on your query patterns and deduplication needs.
Query Optimization
Improve performance with temporal partitioning, cardinality-based sorting keys, projections, and materialized views.
Mass Data Ingestion
Insert large volumes in batches using formats such as JSONEachRow, or integrate streaming with Kafka for continuous ingestion.
Migration to ClickHouse
Migrate schemas and data from PostgreSQL or MySQL to ClickHouse, optimizing data types and engines for analytics.

Usage examples

🏗️ Board Design
Design a MergeTree schema for an events table with monthly partitioning and an ORDER BY clause optimized by user_id and date.
⚡ Query Optimization
This analytical query takes 30 seconds to run in ClickHouse. Optimize it using aggregations and window functions.
📊 Materialized view
Create a materialized view to pre-aggregate hourly transaction statistics by market using `sumState` and `countState`.
🔄 Migration from PostgreSQL
Migrate this schema from PostgreSQL to ClickHouse by optimizing data types using LowCardinality and UInt32.

Features

MergeTree Engine Selection A Guide to Choosing Between MergeTree, ReplacingMergeTree, and AggregatingMergeTree Based on Deduplication and Pre-Aggregation.
Partition and Order Key Configure `PARTITION BY` by time and `ORDER BY` with columns of higher cardinality first for efficient filtering.
Data Type Optimization Use the smallest possible data type (UInt32 vs. UInt64) and LowCardinality for repetitive strings.
Materialized Views Pre-aggregate data in real time using sumState, countState, and merge functions to reduce the query load.
Monitoring Using System Tables Check `system.query_log` and `system.parts` to identify slow queries and review the size of the tables.

Frequently asked questions

Yes. The skill generates SQL scripts and integration patterns, but you need access to a ClickHouse instance to run them.
Yes. The skill includes migration patterns from relational databases to ClickHouse, optimizing data types and table engines for analytics.
It includes examples in pure SQL and Node.js using @clickhouse/client for batch inserts in JSONEachRow format.
The patterns are specific to ClickHouse, but many concepts related to partitioning, data types, and materialized views apply to OLAP in general.
ClickHouse Analytics Pro — OLAP Optimization with Claude AI

¿Prefieres escuchar el contenido? Genera la narración de audio con un clic.