tech
Defaultdeesfrzh

ClickHouse

Base de donnees OLAP orientee colonnes - analyses ultra-rapides sur milliards de lignes, agregations temps reel

TL;DR

Quoi: Une base de données orientée colonnes pour le traitement analytique en ligne (OLAP).

Pourquoi: Requêtes ultra-rapides sur des milliards de lignes, analyses en temps réel, compression des données.

Quick Start

Installer avec Docker:

docker run -d --name clickhouse \
  -p 8123:8123 -p 9000:9000 \
  clickhouse/clickhouse-server

Se connecter:

docker exec -it clickhouse clickhouse-client

Créer une table et insérer des données:

CREATE TABLE events (
  event_date Date,
  event_time DateTime,
  user_id UInt32,
  event_type String,
  value Float64
) ENGINE = MergeTree()
ORDER BY (event_date, user_id);

INSERT INTO events VALUES
  ('2024-01-01', '2024-01-01 10:00:00', 1, 'click', 1.5),
  ('2024-01-01', '2024-01-01 10:05:00', 2, 'view', 0.5);

Cheatsheet

CommandeDescription
SHOW DATABASESLister les bases de données
SHOW TABLESLister les tables
DESCRIBE tableAfficher la structure de la table
SELECT * FROM tableInterroger les données
INSERT INTO table VALUES (...)Insérer des données
DROP TABLE tableSupprimer une table

Gotchas

Table engines

-- MergeTree (le plus courant)
CREATE TABLE logs (
  timestamp DateTime,
  level String,
  message String
) ENGINE = MergeTree()
ORDER BY timestamp;

-- ReplacingMergeTree (déduplication)
CREATE TABLE users (
  id UInt32,
  name String,
  updated_at DateTime
) ENGINE = ReplacingMergeTree(updated_at)
ORDER BY id;

-- SummingMergeTree (agrégation)
CREATE TABLE metrics (
  date Date,
  name String,
  value Int64
) ENGINE = SummingMergeTree()
ORDER BY (date, name);

Analytics queries

-- Agrégations
SELECT
  toDate(event_time) as date,
  count() as events,
  uniq(user_id) as unique_users
FROM events
GROUP BY date
ORDER BY date;

-- Séries temporelles
SELECT
  toStartOfHour(event_time) as hour,
  count() as count
FROM events
WHERE event_date = today()
GROUP BY hour;

-- Top N
SELECT user_id, count() as cnt
FROM events
GROUP BY user_id
ORDER BY cnt DESC
LIMIT 10;

Data types

-- Numérique
UInt8, UInt16, UInt32, UInt64
Int8, Int16, Int32, Int64
Float32, Float64

-- Chaîne
String, FixedString(N)

-- Date/Heure
Date, DateTime, DateTime64

-- Tableaux
Array(T)

-- Nullable
Nullable(T)

Materialized views

CREATE MATERIALIZED VIEW daily_stats
ENGINE = SummingMergeTree()
ORDER BY date
AS SELECT
  toDate(event_time) as date,
  count() as events
FROM events
GROUP BY date;

Next Steps