ClickHouse

Dictionary

ClickHouse e' un potente Columnar Database SQL Open Source con ottime prestazioni sulle attivita' OLAP (On-Line Analytical Processing).

In questa paginetta vediamo gli oggetti di tipo DICTIONARY che consentono di avere prestazioni superiori nelle Star Query.

Anche i dictionary se erano gia' presenti in ClickHouse fin dalle prime versioni, il documento si riferisce alla versione 19.17 o successive perche' da tale versione e' possibile creare i dictionary con comandi SQL.

Introduzione

ClickHouse e' il recente database colonnare sviluppato da Yandex adatto alle attivita' OLAP. Con il termine OLAP (On-Line Analytical Processing) si indicano tecniche ed architetture adatte ad analizzare grandi basi dati in tempi molto brevi.

Una delle tipologie di query piu' comuni in un OLAP e' una STAR Query. In una STAR Query la tabella principale, detta Fact table, viene posta in join con le Dimension Tables che contengono i dettagli delle descrizioni.
ClickHouse e' un database colonnare e generalmente la Fact Table contiene tutte le colonne di dettaglio perche' tale disegno e' il piu' efficiente e non comporta un incremento di spazio utilizzato. Tuttavia vi sono casi in cui i join possono essere necessari.

Per questi casi ClickHouse ha introdotto da tempo i DICTIONARY che utilizzano una rappresentazione chiave-valore in memoria delle tabelle delle dimensioni.

Il documento si riferisce alla versione 19.17 o successive di ClickHouse perche' da tale versione e' possibile creare un dictionary con l'SQL. In realta' i dictionary sono presenti da sempre in ClickHouse e funzionalmente non sono cambiati. Tuttavia la definizione con l'SQL e' sicuramente piu' comoda della precedente configurazione in un file XML.

Dictionary

Vediamo subito un esempio:

CREATE DICTIONARY componentDetails_dic ( `id` UInt64, `name` String, `description` String, `latitude` Float32, `longitude` Float32 ) PRIMARY KEY id LIFETIME(MIN 86400 MAX 100000) LAYOUT(FLAT()) SOURCE(MYSQL( port 3306 user 'demo' password 'xxx' replica(host 'mysql.demo.com' priority 1) db 'demo' table 'component' )); select dictGet('componentDetails', 'name', coId), count(*) from bigFactTable group by coId;

Un DICTIONARY puo' essere immaginato come una normale tabella il cui contenuto e' preso dall'esterno e mantenuto in memoria. Lo statement SQL di CREATE DICTIONARY contiene tutti i parametri necessari alla definizione.

La prima parte della dichiarazione consente di indicare i campi, quindi viene indicata la chiave e il layout. Come layout scegliamo il piu' semplice ovvero FLAT.
Il LIFETIME e' la frequenza con cui i dati debbono essere rinfrescati. In ogni caso l'aggiornamento del dictionary non interrompe o rallenta nessuna query: i nuovi dati saranno visibili solo dopo che il dictionary e' stato completamente aggiornato.

Una parte molto importante del dictionary e' il SOURCE ovvero da dove provengono i dati. I dictionary esterni possono accedere a database (MySQL, ClickHouse, MongoDB, Redis o via ODBC alla maggioranza dei DB), a file locali, ad un file eseguibile o via HTTP. Il formato del comando di creazione dipende naturalmente dal tipo di sorgente dati.
Nel caso di MySQL vanno indicati gli estremi per la connessione, il database e la tabella da cui raccogliere i dati. E' possibile indicare qualche semplice condizione ma nei casi piu' complessi basta creare una vista su MySQL ed accedere a quella. Nel caso siano presenti piu' database MySQL con dati replicati e' possibile indicare una serie di nodi con una priorita'.

Una volta definito il DICTIONARY e' immediatamente utilizzabile mediante specifiche funzioni. La piu' utilizzata e' la dictGet() che permette di trovare i valori mediante la chiave del dizionario.

I dictionary presentano diversi vantaggi. Innanzi tutto consentono l'accesso a database esterni per tabelle di decodifica che non verrebbero trattate in modo altrettanto flessibile in ClickHouse. Ma l'aspetto piu' importante sono le prestazioni...

Prestazioni

ClickHouse e' incredibilmente veloce, ha prestazioni realtime su query complesse su una sola tabella ma quando e' necessario il join di piu' tabelle la velocita' diminuisce e' solo 10 o 100 volte piu' veloce di un normale relazionale.
Ottenere prestazioni ottimali anche quando sarebbe necessario un join e' possibile utilizzando i dictionary. Vediamo un esempio:

select f.coId, count(*), d.name from bigFactTable f, componentDetails_tab d where f.coId=d.id group by f.coId, d.name; ┌─coId─┬─count()─┬─name───────────┐ │ 13 │ 2950 │ Benin City │ │ 17 │ 8650 │ Torino │ │ 69 │ 1313 │ Port Elizabeth │ │ 113 │ 6502 │ Berlin │ ... └──────┴─────────┴────────────────┘ 69 rows in set. Elapsed: 0.358 sec. Processed 100.03 thousand rows, 424.86 KB (296.23 thousand rows/s., 1.19 MB/s.) select coId, count(*), dictGet('componentDetails', 'name', coId) as name from bigFactTable group by coId; ┌─coId─┬─count()─┬─name───────────┐ │ 13 │ 2950 │ Benin City │ │ 17 │ 8650 │ Torino │ │ 69 │ 1313 │ Port Elizabeth │ │ 113 │ 6502 │ Berlin │ ... └──────┴─────────┴────────────────┘ 69 rows in set. Elapsed: 0.005 sec. Processed 100.08 thousand rows, 424.32 KB (19.28 million rows/s., 77.10 MB/s.)

La differenza nell'elaborazione e' quasi di due ordini di grandezza!
Su elaborazioni piu' pesanti, nell'ordine dei miliardi di record, la differenza e' ancora piu' importante perche' si passa da ore di elaborazione a minuti.

Variazioni sul tema

Le possibilita' offerte dai Dictionary in ClickHouse sono molto ampie.

Nell'esempio abbiamo utilizzato una chiave di tipo UInt64 che e' il caso piu' semplice. E' anche possibile utilizzare una chiave composta da uno o piu' campi di tipo diverso da Int. In questo caso viene calcolato un hash della chiave: va scelto il formato LAYOUT(complex_key_hashed()) e nella dictGet dovra' essere passata la tuple() con la chiave composta.
L'elenco completo dei layout disponibili e': flat, hashed, complex_key_hashed, sparse_hashed, cache, range_hashed, complex_key_cache, ip_trie.

Il LIFETIME indica ogni quanto tempo, in secondi, debbono essere ricaricati i dati. Si potrebbe indicare un numero fisso ma non e' opportuno. Il rischio e' che, dopo un riavvio di ClickHouse, piu' dizionari vengano aggiornati nello stesso momento. Per questo nell'esempio abbiamo indicato un intervallo.

Come ultimo vediamo un esempo di configurazione con la vecchia modalita' in XML: 

<source>
  <executable>
    <command>cat /home/clickhouse/sample.txt</command>
    <format>TabSeparated</format>
  </executable>
</source>
<lifetime>1800</lifetime>
<layout>
  <complex_key_hashed/>
</layout>
<structure>
      <key>
        <attribute>
          <name>item</name>
          <type>String</type>
        </attribute>
      </key>
      <attribute>
        <name>value</name>
        <type>String</type>
        <null_value></null_value>
      </attribute>
</structure>

Varie ed eventuali

I DICTIONARY sono presenti da sempre in ClickHouse e nel tempo si sono arricchiti di funzionalita'. Inizialmente disponibili solo su dati locali ora coprono praticamente ogni possibile esigenza potendosi collegare a quasi qualsiasi database o sorgente esterna di dati.
Dalla versione 19.17 i Dictionary sono configurabili da SQL anziche' agendo sul file di configurazione XML. Dalla 20.1 e' disponibile il comando SYSTEM RELOAD DICTIONARY anche ON CLUSTER. Dalla versione 20.4 sono disponibili specifici GRANT anche per i Dictionary.

Il documento Introduzione a ClickHouse contiene una presentazione sulle funzioni di base, il documento Architettura di ClickHouse contiene una presentazione dell'architettura, DBA scripts contiene alcuni dei comandi piu' utili per il DBA.
Naturalmente la fonte piu' completa di informazioni e' la documentazione ufficiale.

Titolo: ClickHouse Dictionary
Livello: Medio (2/5)
Data: 14 Febbraio 2020 ❤️ San Valentino
Versione: 1.0.2 - 14 Febbraio 2021
© Autore: mail [AT] meo.bogliolo.name