SQL4DBA - SQL per DBA ClickHouse

Il DBA (DataBase Administrator) utilizza statement SQL particolari per estrarre le informazioni piu' interessanti sui DB che amministra.
Questa pagina raccoglie e descrive gli script SQL piu' utili ed interessanti per la gestione di un database ClickHouse.

ClickHouse e' un recente e velocissimo database colonnare Open Source adatto a query analitiche (OLAP: OnLine Analytical Processing).

Il documento e' volutamente breve e pratico con esempi funzionanti di statement SQL.

Introduzione

ClickHouse e' un Columnar Database SQL, distribuito ed Open Source con ottime prestazioni sulle attivita' OLAP (On-Line Analytical Processing). ClickHouse e' di semplice installazione, gestione ed utilizzo (con un SQL basilare).
In ClickHouse i dati non sono memorizzati per righe ma per colonne e vengono organizzati come in un indice e compressi. Oltre all'organizzazione per colonne ed alla compressione ClickHouse utilizza il partizionamento ed algoritmi di calcolo parallelo. Quando si effettua una query ClickHouse suddivide il lavoro in piu' thread ed effettua le operazioni in memoria utilizzando tutte le CPU e tutta la RAM disponibili. Questo rende incredibilmente piu' veloci le ricerche rispetto ad un DB tradizionale sopratutto quando vanno analizzati tutti i dati.
Se la quantita' di dati lo richiede e' possibile configurare ClickHouse in cluster sfruttando la replica e lo sharding. ClickHouse scala prestazionalmente in modo lineare sul numero di shard definiti per tabella.

In questa pagina faremo una panoramica sugli statement SQL piu' utili ed interessanti per il DBA ClickHouse.

Sessioni

Una prima visione sull'utilizzo di una base dati e' quella delle connessioni presenti. Dal punto di vista del sistema operativo le sessioni connesse alla base dati non sono evidenti poiche' ClickHouse utilizza un solo processo ed un thread per ogni connessione.
Ecco come ottenere l'elenco delle sessioni attive sulla base dati:

SHOW PROCESSLIST; SELECT query_id, user, address, elapsed, query FROM system.processes ORDER BY query_id;

Il primo comando e' quello piu' semplice, la seconda query consente di porre condizioni nella ricerca. Entrambe i comandi estraggono solo le query attive, non tutte le connessioni presenti. Come facilmente desumibile dall'esempio ClickHouse mantiene le tabelle di gestione nel database system.

SELECT * FROM system.metrics WHERE metric LIKE '%Connection';

Attenzione che questa query restituisce il totale, non l'elenco come invece la precedente; tuttavia il totale comprente tutte le connessioni presenti e non solo quelle attive.

Lock

Quando vengono effettuati inserimenti ClickHouse li esegue in modo asincrono: l'engine MergeTree prima raccoglie ed inserisce i dati in chunck, poi effettua il merge con i dati precedenti. Solo alla fine del processo i dati risultano visibili.
Questa particolare gestione degli aggiornamenti rende poco frequenti e di breve durata i lock.

E' invece possibile che vi siano query di lunga durata che possono essere controllate con SHOW PROCESSLIST e debbano poi essere interrotte con un KILL:

SHOW PROCESSLIST; KILL QUERY WHERE query_id='trx_id';

In ClickHouse le operazioni DML di modifica UPDATE o DELETE sono eseguite in modo asincrono e trattate come DDL e vengono chiamate mutation. Anche se non e' possibile effettuare un rollback delle mutation e' possibile interromperle (anche perche' potrebbero trovarsi in situazione di blocco). In questo caso i comandi sono:

SELECT * FROM system.mutations; KILL MUTATION mutation_id = 'trx_id';

Nota etica: prima di ammazzare qualcuno... e' importante essere certi di quello che si sta facendo!
Questo naturalmente vale sia per le query, che per le mutation, che per gli utenti: terminate solo quello giusto...

Utilizzo di spazio

In ClickHouse la gestione dello spazio e' di fondamentale importanza. La forte compressione dei dati e' una delle ragioni per cui ClickHouse riesce ad avere ottime prestazioni nelle query OLAP.
Iniziamo con lo spazio utilizzato:

SELECT database, table, partition, name part_name, active, bytes_on_disk FROM system.parts ORDER BY database, table, partition, name;

La query precedente riporta il dettaglio finale fino alle parti delle partizioni... ma basta cambiare la GROUP BY per ottenere i valori riassuntivi per tabella o per database.
Dopo un inserimento dati le parts non vengono immediatamente consolidate nelle partitions con un merge. Per forzare il merge e' utilizzabile il comando:
OPTIMIZE TABLE table [PARTITION partition] [FINAL]

E' molto importante il fattore di compressione e questo e' differente per ogni campo. Ecco la query per controllare l'utilizzo di spazio per ogni colonna:

SELECT database, table, column, any(type), sum(column_data_compressed_bytes) compressed, sum(column_data_uncompressed_bytes) uncompressed, sum(rows) FROM system.parts_columns WHERE active AND database <> 'system' GROUP BY database, table, column ORDER BY database, table, column;

Una funzionalita' recente di CH e' il TTL (Time To Live) [NdA 19.11 da 2019-05] che consente di definire la ritezione dei dati per colonna o per tabella. E' particolarmente utile per i dati in Time Serie e ne semplifica la gestione.
Ecco la sintassi: alter table mytable MODIFY TTL time_column + interval 3 MONTH;

Il TTL e' utile anche per svecchiare le tabelle di log di sistema [NdA database system], in particolare: metric_log, query_log, query_thread_log, trace_log, part_log, crash_log, text_log.

Prestazioni

L'ottimizzazione delle performance e' il leitmotiv di ogni DBA.
Per default CH non registra le query eseguite ma e' possibile farlo impostando il parametro log_queries=1 a livello di sessione o nel file di configurazione users.xml [NdA E' assolutamente consigliabile impostarlo].

SELECT user, client_hostname AS host, client_name AS client, formatDateTime(query_start_time, '%T') AS started, query_duration_ms/1000 AS sec, round(memory_usage/1048576) AS MEM_MB, result_rows AS RES_CNT, toDecimal32(result_bytes/1048576, 6) AS RES_MB, read_rows AS R_CNT, round(read_bytes/1048576) AS R_MB, written_rows AS W_CNT, round(written_bytes/1048576) AS W_MB, query FROM system.query_log WHERE type=2 ORDER BY query_duration_ms DESC LIMIT 10;

In ClickHouse non esistono indici, se non la chiave primaria definita al momento di creazione delle tabelle: e' quindi molto importante un corretto disegno della base dati. Le tabelle debbono essere denormalizzate e vanno evitati i join, ClickHouse fornisce i dictionary che sono una potente e veloce alternativa ai join. Il disegno di una base dati per un DWH e' molto differente rispetto a quello adatto ad un database di tipo OLTP.
Una funzionalita' particolarmente utile da questo punto di vista sono le materialized view (MV) che consentono di definire viste alternative ai dati. Le MV possono consolidare i dati o utilizzare chiavi differenti. Analizzare le query piu' pesanti e ricorrenti consente di indirizzare il disegno delle materialized views.

Replica

SELECT database, table, is_leader, total_replicas, active_replicas FROM system.replicas WHERE is_readonly OR is_session_expired OR future_parts > 20 OR parts_to_check > 10 OR queue_size > 20 OR inserts_in_queue > 10 OR log_max_index - log_pointer > 10 OR total_replicas < 2 OR active_replicas < total_replicas;

Ulteriori informazioni

Un'introduzione ad ClickHouse si trova in ClickHouse, mentre maggiori dettagli si trovano su Architettura ClickHouse. Un documento simile a questo, ma in inglese, si trova su questo link. Maggiori dettagli tecnici sulle diverse versioni di ClickHouse e le date di rilascio di ogni versione sono riportate in questo documento.
Il sito ClickHouse ufficiale di Yandex contiene tutta la documentazione ufficiale.

DBA SQL Scripts

SQL per DBA ClickHouse