Dal punto di vista tecnico ClickHouse e' un database colonnare... in pratica 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!
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.
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.
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.
L'installazione di ClickHouse e' semplice... per questo motivo la facciamo in tre modi diversi!
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]:
deb http://repo.yandex.ru/clickhouse/deb/stable/ main/
[NdE Aggiornamento:
deb https://repo.clickhouse.com/deb/stable/ main/ ]
[NdA Il repository indicato funziona ancora...]
Il pacchetto e' installato, per attivare ClickHouse:
systemctl start clickhouse-server.service
Gia' fatto! Si puo' utilizzare...
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...
La documentazione si trova su https://hub.docker.com/r/yandex/clickhouse-server/. In pratica si tratta di lanciare i comandi:
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.
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:
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:
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:
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...
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:
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(), ...
Ed anche clausole SQL in piu' (eg. PREWHERE).
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]:
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.
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.
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.
... 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'.
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 :]
Breaking news: 2021-09-20 e' stata creata la Clickhouse Inc. in cui sono confluite tutte le risorse di sviluppo del database. E' un'ottima notizia perche' potra' dare un ulteriore impulso all'evoluzione ed alla diffusione di Clickhouse.
Titolo: ClickHouse
Livello: Medio
Data:
1 Aprile 2019 🐟
Versione: 1.0.1 - 20 Settembre 2021
Autore: mail [AT] meo.bogliolo.name