Installer avec Codex ou Claude Copiez ce prompt, collez-le dans Codex, Claude ou un autre assistant, puis laissez-le vérifier la page du skill et l'installer pour vous.
Une commande directe contourne le prompt de vérification. Examinez la source avant de l'exécuter.
Ingesting large volumes of data (batch inserts, Kafka integration)
Migrating from PostgreSQL/MySQL to ClickHouse for analytics
Implementing real-time dashboards or time-series analytics
Overview
ClickHouse is a column-oriented database management system (DBMS) for online analytical processing (OLAP). It's optimized for fast analytical queries on large datasets.
-- PASS: GOOD: Use indexed columns firstSELECT*FROM markets_analytics
WHEREdate>='2025-01-01'AND market_id ='market-123'AND volume >1000ORDERBYdateDESC
LIMIT 100;
-- FAIL: BAD: Filter on non-indexed columns firstSELECT*FROM markets_analytics
WHERE volume >1000AND market_name LIKE'%election%'ANDdate>='2025-01-01';
Aggregations
-- PASS: GOOD: Use ClickHouse-specific aggregation functionsSELECT
toStartOfDay(created_at) ASday,
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() -INTERVAL7DAYGROUPBYday, market_id
ORDERBYdayDESC, total_volume DESC;
-- PASS: Use quantile for percentiles (more efficient than 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() -INTERVAL1HOUR;
Window Functions
-- Calculate running totalsSELECTdate,
market_id,
volume,
sum(volume) OVER (
PARTITIONBY market_id
ORDERBYdateROWSBETWEEN UNBOUNDED PRECEDING ANDCURRENTROW
) AS cumulative_volume
FROM markets_analytics
WHEREdate>= today() -INTERVAL30DAYORDERBY market_id, date;
// For continuous data ingestionimport { createWriteStream } from'fs'import { pipeline } from'stream/promises'asyncfunctionstreamInserts() {
const stream = clickhouse.insert('trades').stream()
forawait (const batch of dataSource) {
stream.write(batch)
}
await stream.end()
}
Materialized Views
Real-time Aggregations
-- Create materialized view for hourly statsCREATE MATERIALIZED VIEW market_stats_hourly_mv
TO market_stats_hourly
ASSELECT
toStartOfHour(timestamp) AShour,
market_id,
sumState(amount) AS total_volume,
countState() AS total_trades,
uniqState(user_id) AS unique_users
FROM trades
GROUPBYhour, market_id;
-- Query the materialized viewSELECThour,
market_id,
sumMerge(total_volume) AS volume,
countMerge(total_trades) AS trades,
uniqMerge(unique_users) AS users
FROM market_stats_hourly
WHEREhour>= now() -INTERVAL24HOURGROUPBYhour, market_id;
Performance Monitoring
Query Performance
-- Check slow queriesSELECT
query_id,
user,
query,
query_duration_ms,
read_rows,
read_bytes,
memory_usage
FROM system.query_log
WHERE type ='QueryFinish'AND query_duration_ms >1000AND event_time >= now() -INTERVAL1HOURORDERBY query_duration_ms DESC
LIMIT 10;
Table Statistics
-- Check table sizesSELECT
database,
table,
formatReadableSize(sum(bytes)) AS size,
sum(rows) ASrows,
max(modification_time) AS latest_modification
FROM system.parts
WHERE active
GROUPBY database, tableORDERBYsum(bytes) DESC;
Common Analytics Queries
Time Series Analysis
-- Daily active usersSELECT
toDate(timestamp) ASdate,
uniq(user_id) AS daily_active_users
FROM events
WHEREtimestamp>= today() -INTERVAL30DAYGROUPBYdateORDERBYdate;
-- Retention analysisSELECT
signup_date,
countIf(days_since_signup =0) AS day_0,
countIf(days_since_signup =1) AS day_1,
countIf(days_since_signup =7) AS day_7,
countIf(days_since_signup =30) AS day_30
FROM (
SELECT
user_id,
min(toDate(timestamp)) AS signup_date,
toDate(timestamp) AS activity_date,
dateDiff('day', signup_date, activity_date) AS days_since_signup
FROM events
GROUPBY user_id, activity_date
)
GROUPBY signup_date
ORDERBY signup_date DESC;
Funnel Analysis
-- Conversion funnelSELECT
countIf(step ='viewed_market') AS viewed,
countIf(step ='clicked_trade') AS clicked,
countIf(step ='completed_trade') AS completed,
round(clicked / viewed *100, 2) AS view_to_click_rate,
round(completed / clicked *100, 2) AS click_to_completion_rate
FROM (
SELECT
user_id,
session_id,
event_type AS step
FROM events
WHERE event_date = today()
)
GROUPBY session_id;
Cohort Analysis
-- User cohorts by signup monthSELECT
toStartOfMonth(signup_date) AS cohort,
toStartOfMonth(activity_date) ASmonth,
dateDiff('month', cohort, month) AS months_since_signup,
count(DISTINCT user_id) AS active_users
FROM (
SELECT
user_id,
min(toDate(timestamp)) OVER (PARTITIONBY user_id) AS signup_date,
toDate(timestamp) AS activity_date
FROM events
)
GROUPBY cohort, month, months_since_signup
ORDERBY cohort, months_since_signup;
Remember: ClickHouse excels at analytical workloads. Design tables for your query patterns, batch inserts, and leverage materialized views for real-time aggregations.
