ClickHouse

ClickHouse ClickHouse e' un Columnar Database, in SQL, distribuito e con prestazioni ottime sulle attivita' OLAP (On-Line Analytical Processing). ChlickHouse e' pubblicato con la licenza Open Source Apache 2.0.

Dal punto di vista tecnico ClickHouse... insomma volete analizzare miliardi di record in pochi secondi? Oppure avete una query che richiede minuti e volte farla eseguire in millisecondi? Allora continuate a leggere!

Questo documento presenta gli aspetti introduttivi di ClickHouse: Introduzione, Installazione, Primi passi, Utilizzo, Query OLAP, ... utilizzando un approccio pratico con molti esempi di query SQL. Per un approfondimento ulteriore la pagina Architettura ClickHouse contiene argomenti un poco piu' complessi sull'architettura e le integrazioni di ClickHouse con altri ambienti/prodotti (eg. JDBC, ODBC, MySQL, Kafka, ProxySQL, Grafana, ...).

Il documento si riferisce alla versione 19.4 di ClickHouse ma i contenuti valgono anche per altre versioni.

Introduzione

La tipica applicazione dei database relazionali e' per ambienti OLTP (On-Line Transaction Processing) in cui la velocita' di esecuzione e la gestione delle transazioni sono gli elementi caratteristici. Per questo utilizzo i database relazionali, utilizzati con il linguaggio SQL, sono da anni la tecnologia piu' utilizzata per gli OLTP.
La rappresentazione tabellare dei dati e gli indici sono molto efficienti per accedere ai record da selezionare/modificare nelle transazioni. Quando pero' e' necessario analizzare basi dati di grandi dimensioni in modo completo sono necessari i famigerati FULL TABLE SCAN, per queste elaborazioni i tempi si allungano e passano dai millisecondi ai minuti ed alle ore per le elaborazioni piu' complesse.
Con il termine OLAP (On-Line Analytical Processing) si indicano tecniche ed architetture adatte ad analizzare in tempi molto brevi grandi basi dati.

ClickHouse utilizza un approccio differente rispetto alla rappresentazione ISAM con indici B-Tree, tipica dei DB relazionali, memorizzando i dati per colonna.
Questo consente di utilizzare algoritmi differenti nell'accesso ai dati che possono essere eseguiti in parallelo. Innanzi si utilizzano tutte le CPU sul nodo ospite, ma e' possibile farlo anche in rete con piu' nodi. Le query di ClickHouse in cluster scalano in modo pressoche' lineare come prestazioni.

L'installazione si esegue con un paio di comandi e praticamente non c'e' bisogno di configurazione e di tuning. L'interfaccia l'SQL rende facilmente utilizzabile ClickHouse a chiunque. Va posta un poco di attenzione nella definizione delle tabelle, un corretto disegno della base dati e' sempre importante, ma anche scelte piu' semplici sono comunque molto efficienti.

ClickHouse si utilizza con l'SQL ma e' importante sottolineare che un database colonnare non e' adatto ad un utilizzo come OLTP: anche se ClickHouse ora sopporta le UPDATE e le DELETE comunque non e' ottimizzato per tali istruzioni e tantomeno per gestire transazioni. Solo se usato nell'ambito giusto ClickHouse e' molto piu' efficiente di un normale DB relazionale.

Infine le prestazioni: come altri DB column-oriented ClickHouse e' migliaia di volte piu' veloce nelle query OLAP rispetto ad un DB relazionale tradizionale. ClickHouse non presenta la latenza tipica di alcuni DB NoSQL rispondendo in modo quasi real time alle richieste. ClickHouse e' molto, molto veloce.

Installazione

L'installazione di ClickHouse e' semplice... per questo motivo la facciamo in tre modi diversi!

APT

La modalita' di installazione consigliata e' il gestore di pacchetti APT (Advanced Packaging Tool: utilizzato da Debian, Ubuntu, ...).

I passi sono molto semplici [NdA leggermente semplificati]:

Il pacchetto e' installato, per attivare ClickHouse:
 systemctl start clickhouse-server.service

Gia' fatto! Si puo' utilizzare...

RPM

Se si utilizza CentOS/Red Hat/OL/... sono disponibili gli RPM sul sito di Yandex: https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/ oppure i repository sul sito di una terza parte: https://github.com/Altinity/clickhouse-rpm-install [NdA maggiori dettagli sulle alternative si trovano in questa paginetta].
La procedura dettagliata per la configurazione del repository e' descritta nella documentazione ufficiale.

Una volta configurato il repository si lancia il comando:
 yum install clickhouse-server clickhouse-client

Gia' fatto! Si puo' utilizzare...

Docker

La documentazione si trova su https://hub.docker.com/r/yandex/clickhouse-server/. In pratica si tratta di lanciare i comandi:

$ docker run -d --name my-clickhouse --ulimit nofile=262144:262144 -p8123:8123 \ --volume=/Users/me/ch/data:/var/lib/clickhouse yandex/clickhouse-server $ docker run -it --rm --link my-clickhouse:clickhouse-server yandex/clickhouse-client --host clickhouse-server ClickHouse client version 19.4.2.7. Connecting to clickhouse-server:9000 as user default. Connected to ClickHouse server version 19.1.14 revision 54413.

Gia' fatto! Lo stiamo gia' usando!
I principali comandi di Docker sono riportati in questa paginetta. A tutti gli effetti il primo comando ha lanciato un container con clickhouse server ed il secondo comando ha attivato un client connesso al DB. E' possibile inserire eventuali script di inizializzazione nella directory /docker-entrypoint-initdb.d. Per default viene scaricata l'ultima versione dell'immagine (latest) ma e' possibile caricare una versione specifica con :TAG.

Ulteriori informazioni sull'installazione si trovano sulla documentazione ufficiale.

Primi passi

Per utilizzare ClickHouse bastano... semplici comandi SQL!

Per chi e' abituato ai comandi in linea di MySQL vi sono parecchie similitudini: USE database; SHOW PROCESSLIST, SHOW CREATE TABLE table, ...
Ci sono pero' delle differenze: non c'e' bisogno del ";" al termine del comando ed il prompt e' un simpatico smiley!

Per fare un parallelo vediamo usiamo una tabella MySQL e lanciamo una query:

mysql>  select count(*), min(timest), max(timest) from my2.status;
+----------+---------------------+---------------------+
| count(*) | min(timest)         | max(timest)         |
+----------+---------------------+---------------------+
|  2196272 | 2018-08-01 23:55:00 | 2019-04-01 15:38:69 |
+----------+---------------------+---------------------+
1 row in set (1.22 sec)

Carichiamo il DB MySQL su ClickHouse:

$ clickhouse-client :) show databases SHOW DATABASES ┌─name────┐ │ default │ │ system │ └─────────┘ 2 rows in set. Elapsed: 0.003 sec. :) create database my2 :) use database my2 :) create table ch_status :-] engine=MergeTree order by TIMEST :-] as select * from mysql('host.docker.internal:3306', 'my2', 'status', 'user', 'xxx') 0 rows in set. Elapsed: 2.533 sec. Processed 2.20 million rows, 95.81 MB (867.24 thousand rows/s., 37.82 MB/s.) :) SELECT :-] count(*), :-] min(TIMEST), :-] max(TIMEST) :-] FROM ch_status ┌─count()─┬─────────min(TIMEST)─┬─────────max(TIMEST)─┐ │ 2196720 │ 2018-08-01 23:55:00 │ 2019-04-01 15:48:69 │ └─────────┴─────────────────────┴─────────────────────┘ 1 rows in set. Elapsed: 0.051 sec. Processed 2.20 million rows, 8.79 MB (43.08 million rows/s., 172.30 MB/s.)

Il DB era molto piccolo e l'ambiente ospite virtualizzato (Docker), in ogni caso i tempi si riducono di ordini di grandezza passando da MySQL a ClickHouse. Naturalmente questo e' vero per le query OLAP, una select con chiave e' piu' veloce in MySQL, le transazioni non esistono in ClickHouse e praticamente neanche le DELETE e le UPDATE: sono database con funzionalita' completamente differenti.

Con query piu' complesse la differenza di prestazioni si fa ancora piu' evidente. Sul relazionale i tempi superavano i 10 minuti, con ClickHouse si resta sotto il secondo:

┌─tot_viaggi─┬──tot_distanza─┐ │ 3041355 │ 6500444787.35 │ └────────────┴───────────────┘ 1 rows in set. Elapsed: 0.553 sec. Processed 3.04 million rows, 388.87 MB (5.50 million rows/s., 702.63 MB/s.)

Utilizzo

Si accede a ClickHouse utilizzando l'interfaccia nativa sulla porta 9000, come fa il client clickhouse-client che abbiamo gia' utilizzato negli esempi, o l'interfaccia HTTP sulla porta 8123.

Sono disponibili i driver per l'accesso via JDBC o ODBC e le interfacce per i piu' comuni linguaggi di programmazione (C++, C#, Java, Perl, PHP, Python, Node.js, Go, ...).

E' possibile accedere a ClickHouse con:

echo 'SELECT * from system.one' | curl 'http://localhost:8123/?' --data-binary @-

Evidentemente non e' molto comodo... per lanciare le query si utilizza tipicamente il client clickhouse-client:

$ clickhouse-client :) create database test :) show databases SHOW DATABASES ┌─name────┐ │ default │ │ system │ │ test │ └─────────┘ 3 rows in set. Elapsed: 0.002 sec. :) use database test :) create table cpu ( :-] host String, :-] region String, :-] type UInt8, :-] value Float32, :-] timeref DateTime) :-] engine=MergeTree :-] PARTITION BY toYYYYMM(timeref) :-] ORDER BY (host, timeref) $ cat BigData01.csv | \ clickhouse-client --format_csv_delimiter="|" --query="INSERT INTO test.cpu FORMAT CSV"

Da notare nella create table il partizionamento per mese e l'indicizzazione piu' granulare nell'ORDER BY. L'engine MergeTree e' molto efficiente nell'effettuare i caricamenti batch. Con il successivo comando dell'esempio i dati vengono caricati direttamente da un file CSV.

Ancora piu' semplice e' utilizzare una GUI come TABiX (integrata nativamente su Chrome) o le molte altre che utilizzano i driver JDBC/ODBC...

TABiX GUI per ClickHouse

Query OLAP

Le query in cui ClickHouse fornisce le migliori prestazioni sono query su grandi quantita' di dati con condizioni complesse e clausole GROUP BY ed ORDER BY.

Utilizzando clickhouse-client sono molto evidenti il parallelismo sfruttato nell'elaborazione delle query e la velocita' ottenuta:

select pt_class, formatReadableSize(sum(RxBytes)), formatReadableSize(sum(TxBytes)) from router_info where timestamp > yesterday() group by pt_class; ... ┌─pt_class─┬─formatReadableSize(sum(RxBytes))─┬─formatReadableSize(sum(TxBytes))─┐ │ RED │ 18.97 GiB │ 34.69 GiB │ └──────────┴──────────────────────────────────┴──────────────────────────────────┘ ┌─pt_class─┬─formatReadableSize(sum(RxBytes))─┬─formatReadableSize(sum(TxBytes))─┐ │ SIP │ 53.58 TiB │ 9.80 TiB │ └──────────┴──────────────────────────────────┴──────────────────────────────────┘ ┌─pt_class─┬─formatReadableSize(sum(RxBytes))─┬─formatReadableSize(sum(TxBytes))─┐ │ VIP │ 18.75 TiB │ 1.81 TiB │ │ B2B │ 14.07 TiB │ 4.83 TiB │ └──────────┴──────────────────────────────────┴──────────────────────────────────┘ 69 rows in set. Elapsed: 5.464 sec. Processed 1.42 billion rows, 33.98 GB (259.45 million rows/s., 6.22 GB/s.)

Il risultato e' una tabella ma gli header sono ripetuti perche' elaborati in parallelo per ogni Part; nell'ultima riga sono riportati i tempi ed il numero di record trattati.

Anche se l'uso di ClickHouse da cli ricorda quello di MySQL (eg. use database, show tables, show create table, ...) in realta' le differenze sono molte. In particolare CH e' molto piu' rigido sui tipi di dati: una stringa e' una stringa e non una data o un numero! Anche una data e' una solo un Date e non un DateTime... Basta pensare in C ed e' tutto chiaro. Sono comunque presenti decine di semplici funzioni da richiamare per effettuare le conversioni: toUInt8, toFloat64, toString, toDate, toDateTime, ...

Nel calcolo delle funzioni di gruppo CH e' particolarmente efficiente. Sono presenti tutte le classiche funzioni: count(), sum(), avg(), ... e ve ne sono di ulteriori che possono essere utilizzate per calcolare risultati stimati o esatti (eg. uniqExact).
Tutte le funzioni di gruppo possono essere alterate da modificatori. Ad esempio sumIf(comm, comm>0)
I suffissi State e Merge hanno un comportamento particolare e vengono utilizzati per aggregare i dati nelle Materialized View.

Sono presenti in CH tutte le comuni funzioni di analisi statistica: stddevSamp(), varSamp(), ... Ma anche quelle un po' meno comuni quali stddevPop(), varPop(), kurt(), corr(), quantile(), groupArray(), ... Ed infine quelle piu' specialistiche tipiche del ML (Machine Learning) quali stochasticLinearRegression(), stochasticLogisticRegression()!

Vi sono decine di utili funzioni di trattamento delle date come la toStartOfInterval(), toYYYYMM(), toDayOfWeek(), ... Vediamo ora qualche esempio di query utilizzando sintassi e funzioni tipiche di ClickHouse:

-- Date and DateTime 
select host, toStartOfInterval(timeref, INTERVAL 1 hour) as time, avg(value) as Average
  from test.cpu
 where toDate(timeref) > toDate('2018-06-30')
 group by host, toStartOfInterval(timeref, INTERVAL 1 hour)
 order by toStartOfInterval(timeref, INTERVAL 1 hour), host;

Anche se l'SQL di ClickHouse non e' quello previsto dagli standard e vi sono alcune limitazioni sono pero' presenti molte estensioni.
Tra le moltissime funzioni disponibili sono sicuramente utili: runningDifference(), basename(), toIPv4(), formatReadableSize(), bitAnd(), multiIf(), sumIf(), , ...
Mentre tra le clausole della SELECT sono sicuramente da riportare: LIMIT BY che consente di riportare facilmente il Top-N per gruppo, SAMPLE per campionare i dati ed INTO OUTFILE per scaricare su client il risultato della query.

Un'altra funzione d'effetto e' la bar() [NdA il cui risultato si vede nella figura di seguito]:

Query OLAP ClickHouse

Nel disegno del database e' importante utilizzare la tecnica degli star schema denormalizzando i dati e/o utilizzando i dictionary. Tra le estensioni utili riportiamo le virtual columns e gli Engine di aggregazione dati (eg. SummingMergeTree) che consentono di ottenere i risultati di calcoli anche complessi, su grandi quantita' di dati con tempi real time.

Ulteriori esempi sono riportati nella documentazione ufficiale.

CRUD

Questo paragrafo e' provocatorio... ClickHouse non ha funzioni CRUD!

Iniziando con la C: la clausola di INSERT di ClickHouse ha sintassi standard SQL. Tuttavia e' fortemente consigliato effettuare gli inserimenti in batch. Il massimo di record trattati sulle operazioni bulk in modo atomico per default e' 1M, e' consigliato inserire indicativamente almeno 100.000 record alla volta. Come frequenza e' consigliato di non superare le 10 operazioni di INSERT al secondo. L'inserimento dati e' piu' efficiente se e' ordinato per primary key; i dati inseriti vengono tipicamente partizionati per mese [NdA una volta era fisso ora si puo' scegliere]. L'Engine MergeTree inserisce i nuovi dati in una part e successivamente esegue il merge (da qui il nome) nella partition corrispondente.

La R (Read) e' SELECT ed ha la normale sintassi SQL [NdA con le subquery perche' i JOIN ed i MULTIJOIN sono supportati solo di recente] pero' le ricerche per chiave sono piu' lente che su un OLTP perche' vanno effettuate su tutte le colonne... Le query OLAP sono invece terribilmente piu' veloci.

UPDATE e DELETE non erano supportate! Di recente sono state aggiunte ma con la forma di ALTER TABLE. La richiesta viene raccolta subito ma la sua esecuzione e' eseguita da un thread asincrono che effettua una copia delle strutture, le modifica ed al termine le sostituisce agli originali. Tali esecuzioni sono chiamate MUTATION e non e' possibile alcun rollback.

Sono invece supportate da sempre e molto efficienti le modifiche sulle partizioni quali ALTER TABLE ... DROP PARTITION e le TRUNCATE.

JOIN

Il supporto dei join in ClickHouse e' limitato ad un solo join ma utilizzando le subquery si riesce ad ottenere quasi ogni risultato. Ecco un EMP5:

SELECT count(*)
FROM emp7 AS emp1 
INNER JOIN 
(
    SELECT *
    FROM emp7 AS emp2 
    INNER JOIN 
    (
        SELECT *
        FROM emp7 AS emp3 
        INNER JOIN 
        (
            SELECT *
            FROM emp7 AS emp4 
            INNER JOIN 
            (
                SELECT *
                FROM emp7 AS emp5 
            ) ON 1 = 1
        ) ON 1 = 1
    ) ON 1 = 1
) ON 1 = 1

[NdA in realta' questo limite e' stato superato, ma ClickHouse non supporta ancora tutte le tipologie di join].

Nelle star-query anziche' utilizzare il join e' molto piu' efficiente utilizzare un Dictionary. I dictionary possono essere anche su file o database esterni, vengono caricati in memoria ed si utilizzano con una sintassi particolare:

select dictGetString('dept', 'deptname', deptno) DepName, sum(sal)
  from emp
 group by DepName;

Un corretto disegno della base dati resta comunque la condizione principale per eseguire in modo efficiente query OLAP con ClickHouse e con qualsiasi altro database per DWH.

E l'ultimo...

... chiude la porta!

Ma non e' cosi' facile in ClickHouse determinare qual'e' l'ultimo dato inserito su una serie temporale perche' non ci sono UPDATE o trigger. Selezionare qual'e' l'ultimo record inserito richiede un full scan e non e' immediato.

Con l'SQL vi sono diverse soluzioni, alcune classiche e standard, altre specifiche di ClickHouse; alcune piu' efficienti, altre piu' eleganti. Vediamone alcune:

select *
from cpu
where (tag_id, created_at) in
 (select tag_id, max(created_at)
    from cpu
   group by tab_id);

select argMax(usage_user, created_at),
       argMax(usage_system, created_at),
        ...
  from cpu;

select now() as created_at, cpu.*
  from (select distinct tags_id from cpu) base
  asof left join cpu using (tag_id, created_at);

select *
  from cpu
 order by created_at desc
 LIMIT 1 BY tag_id;

Last but not least...
C'e' anche una soluzione con le Materialized View che e' la piu' efficiente per le tabelle di maggiori dimensioni. In effetti le MV in Clickhouse si comportano come i trigger su altri database:

CREATE MATERIALIZED VIEW cpu_last_mv
ENGINE = AggregatingMergeTree() PARTITION BY tuple() ORDER BY tag_id AS
SELECT tag_id,
       maxState(created_at) AS c 
       argMaxState(field_XXX, created_at) AS f_X,
       ...
from cpu
group by tag_id;

CREATE VIEW cpu_last AS
SELECT tag_id,
       maxMerge(c) AS created_at,
       argMaxMerge(f_X, c) AS field_XXX,
       ...
from cpu_last_mv
group by tag_id;

Selezionando i dati dalla vista cpu_last si ottengono immediatamente gli ultimi valori inseriti.
Nota: e' consigliato definire con la clausola TO una tabella esplicita nella creazione della MV, ma lo abbiamo omesso per semplicita'.

Varie ed eventuali

ClickHouse e' un software Open Source distribuito da Yandex su Github dal 2016 con licenza molto libera: Apache License 2.0.

ClickHouse e' un database giovane: ha ottime prestazioni, effettua un compressione dei dati notevole, scala linearmente in cluster, supporta la replica dei dati, e' di facile installazione/gestione ma ha anche qualche difetto di gioventu'. Le intergrazioni, il supporto SQL, le interfacce grafiche, ... sono molto migliorate in questi mesi ma possono ancora crescere come funzionalita'.

Questa paginetta e' una semplice introduzione al database colonnare ClickHouse, nel documento Architettura ClickHouse vengono trattati argomenti un poco piu' complessi quali: Architettura, Amministrazione, Benchmark, Integrazioni, Versioni ed aggiornamenti, ...

Per i piu' curiosi e' consigliata la lettura di uno dei primi articoli su ClickHouse che descrive l'evoluzione delle strutture dati [NdA tranquilli: non e' in inglese :]


Titolo: ClickHouse
Livello: Medio (2/5)
Data: 1 Aprile 2019 🐟
Versione: 1.0.1 - 1 Aprile 2019
Autore: mail [AT] meo.bogliolo.name