$ loading_
帮助用户掌握 ClickHouse 查询优化、分析建模与数据工程最佳实践。
复制安装指令，让 AI 自动完成配置 · 推荐新手
请帮我安装 askskill 上的 "clickhouse-io" 技能： 1. 下载 https://raw.githubusercontent.com/affaan-m/ECC/main/docs/ko-KR/skills/clickhouse-io/SKILL.md 2. 保存为 ~/.claude/skills/clickhouse-io/SKILL.md 3. 装好后重载技能，告诉我可以用了
我有一条 ClickHouse 查询很慢，请根据表结构、分区键、主键和 SQL 语句，分析瓶颈并给出可执行的优化建议，包括索引设计、数据跳过、预聚合和查询改写方案。
一份针对慢查询的性能诊断与分步骤优化方案。
请为用户行为日志设计一个适合 ClickHouse 的表结构，包含分区策略、排序键、字段类型、TTL、物化视图和常见查询场景，并说明这样设计的原因。
一套面向分析场景的 ClickHouse 建模方案与设计说明。
我准备把 Kafka 中的实时数据写入 ClickHouse，请给出一套数据工程最佳实践，包括写入方式、去重策略、延迟控制、表引擎选择、监控指标和常见故障排查建议。
一份可落地的 ClickHouse 实时数据管道实施建议清单。
고성능 분석 및 데이터 엔지니어링을 위한 ClickHouse 전용 패턴.
ClickHouse는 온라인 분석 처리(OLAP)를 위한 컬럼 지향 데이터베이스 관리 시스템(DBMS)입니다. 대규모 데이터셋에 대한 빠른 분석 쿼리에 최적화되어 있습니다.
주요 특징:
CREATE TABLE markets_analytics (
date Date,
market_id String,
market_name String,
volume UInt64,
trades UInt32,
unique_traders UInt32,
avg_trade_size Float64,
created_at DateTime
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, market_id)
SETTINGS index_granularity = 8192;
-- 중복이 있을 수 있는 데이터용 (예: 여러 소스에서 수집된 경우)
CREATE TABLE user_events (
event_id String,
user_id String,
event_type String,
timestamp DateTime,
properties String
) ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (user_id, event_id, timestamp)
PRIMARY KEY (user_id, event_id);
-- 집계 메트릭을 유지하기 위한 용도
CREATE TABLE market_stats_hourly (
hour DateTime,
market_id String,
total_volume AggregateFunction(sum, UInt64),
total_trades AggregateFunction(count, UInt32),
unique_users AggregateFunction(uniq, String)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, market_id);
-- 집계된 데이터 조회
SELECT
hour,
market_id,
sumMerge(total_volume) AS volume,
countMerge(total_trades) AS trades,
uniqMerge(unique_users) AS users
FROM market_stats_hourly
WHERE hour >= toStartOfHour(now() - INTERVAL 24 HOUR)
GROUP BY hour, market_id
ORDER BY hour DESC;
-- PASS: 좋음: 인덱스된 컬럼을 먼저 사용
SELECT *
FROM markets_analytics
WHERE date >= '2025-01-01'
AND market_id = 'market-123'
AND volume > 1000
ORDER BY date DESC
LIMIT 100;
-- FAIL: 나쁨: 비인덱스 컬럼을 먼저 필터링
SELECT *
FROM markets_analytics
WHERE volume > 1000
AND market_name LIKE '%election%'
AND date >= '2025-01-01';
-- PASS: 좋음: ClickHouse 전용 집계 함수를 사용
SELECT
toStartOfDay(created_at) AS day,
market_id,
sum(volume) AS total_volume,
count() AS total_trades,
uniq(trader_id) AS unique_traders,
avg(trade_size) AS avg_size
FROM trades
WHERE created_at >= today() - INTERVAL 7 DAY
GROUP BY day, market_id
ORDER BY day DESC, total_volume DESC;
-- PASS: 백분위수에는 quantile 사용 (percentile보다 효율적)
SELECT
quantile(0.50)(trade_size) AS median,
quantile(0.95)(trade_size) AS p95,
quantile(0.99)(trade_size) AS p99
FROM trades
WHERE created_at >= now() - INTERVAL 1 HOUR;
-- 누적 합계 계산
SELECT
date,
market_id,
volume,
sum(volume) OVER (
PARTITION BY market_id
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_volume
FROM markets_analytics
WHERE date >= today() - INTERVAL 30 DAY
ORDER BY market_id, date;
import { ClickHouse } from 'clickhouse'
const clickhouse = new ClickHouse({
url: process.env.CLICKHOUSE_URL,
port: 8123,
basicAuth: {
username: process.env.CLICKHOUSE_USER,
password: process.env.CLICKHOUSE_PASSWORD
}
})
// PASS: 배치 삽입 (효율적)
async function bulkInsertTrades(trades: Trade[]) {
const rows = trades.map(trade => ({
id: trade.id,
market_id: trade.market_id,
user_id: trade.user_id,
amount: trade.amount,
timestamp: trade.timestamp.toISOString()
}))
await clickhouse.insert('trades', rows)
}
// FAIL: 개별 삽입 (느림)
async function insertTrade(trade: Trade) {
// 루프 안에서 이렇게 하지 마세요!
await clickhouse.query(`
INSERT INTO trades VALUES ('${trade.id}', ...)
`).toPromise()
}
// 연속적인 데이터 수집용
…
通过双评审智能体对结果进行对抗式校验，提升输出发布前的可靠性
为 ClickHouse 提供只读查询优化、延迟分析与数据质量监控支持