ClickHouse

Materialized Views

ClickHouse e' un Columnar Database SQL, distribuito e con ottime prestazioni sulle attivita' OLAP (On-Line Analytical Processing).
ClickHouse e' di semplice installazione, gestione ed utilizzo (mediante il linguaggio SQL). Non da ultimo... ClickHouse e' completamente Open Source con una licenza molto aperta (Apache 2.0).

In questa paginetta vediamo una sua particolare funzionalita': le materialized views.

Views

ClickHouse e' un database colonnare che utilizza un dialetto SQL simile a quello MySQL e che e' molto completo. Gli statement di DELETE ed UPDATE in CH sono differenti dallo standard SQL e le insert vengono effettuate in batch, ma le altre sintassi sono praticamente le stesse.

In particolare ClickHouse fornisce il supporto completo per le viste:

CREATE VIEW space_usage AS SELECT database, sum(rows) rows, formatReadableSize(sum(bytes_on_disk)) size, formatReadableSize(sum(data_uncompressed_bytes)) uncompressed_size FROM system.parts GROUP BY database ORDER BY database; SELECT * FROM space_usage; ┌─database─┬────────rows─┬─size───────┬─uncompressed_size─┐ │ store1 │ 46200235856 │ 501.63 GiB │ 2.27 TiB │ │ my2 │ 1323640 │ 3.21 MiB │ 41.24 MiB │ │ stage │ 16216091 │ 764.60 MiB │ 4.53 GiB │ │ system │ 502606 │ 49.11 MiB │ 491.85 MiB │ │ dwh │ 29425337305 │ 980.24 GiB │ 3.16 TiB │ └──────────┴─────────────┴────────────┴───────────────────┘

Una vista non e' altro che una SELECT salvata e richiamabile successivamente...
Dal punto di vista elaborativo viene eseguita ogni volta la query originale.

Per cancellare una vista? DROP TABLE!

Materialized Views

ClickHouse Materialized View Una funzionalita' molto utile e specifica di ClickHouse sono le Materialized Views.
Le materilized view utilizzano lo storage per mantenere i dati gia' calcolati, come avviene sul altri database relazionali, pero' in ClickHouse la differenza sostanziale e' la modalita' di popolamento delle viste.

Dal punto di vista sintattico si definiscono come normali viste aggiungendo la keyword MATERIALIZED. Ma dal punto di vista elaborativo cambiano rispetto alle normali viste. Infatti il risultato della query viene salvato come su una normale tabella e quindi va specificato il relativo Engine in fase di creazione. Sulla vista materializzata le query possono risultare notevolmente piu' veloci perche' contengono gia' i dati di interesse:

CREATE MATERIALIZED VIEW error_log ENGINE=MergeTree ORDER BY (timestamp, server) AS SELECT timestamp, server, level, message FROM enterprise_log WHERE level IN ('ERROR', 'FATAL'); select * from error_log;

Per i curiosi, o quelli come me che vogliono sempre provare i comandi, riportiamo anche una possibile definizione della tabella su cui si basa la vista: 

CREATE TABLE enterprise_log (
  timestamp DateTime,
  server String,
  level String,
  message String
)
ENGINE=MergeTree
  PARTITION BY toYYYYMM(timestamp)
  ORDER BY (timestamp, server);
insert into enterprise_log values(now(), 'server1', 'ERROR', 'Wrong password');

Quando vengono inseriti nuovi dati la query della vista viene applicata alle nuove Part ed i dati vengono inseriti anche nella vista materializzata.
Quando la MV viene creata risulta vuota e solo i nuovi dati verranno inseriti [NdA a meno di non utilizzare la clasuola POPULATE che pero' e' sconsigliata]. Tutte le clausole della SELECT della vista vengono applicate ai dati inseriti nella Part e non nel loro insieme. Questo rende l'aggiornamento delle materialized view molto efficiente. Quindi per fare in modo che un GROUP BY venga applicato sull'intero insieme di dati va utilizzato un Engine adatto. Poiche' l'Engine effettua i raggruppamenti in fase di merge, e questi non sono immediati, e' necessario utilizzare il raggruppamento anche nelle SELECT:

CREATE MATERIALIZED VIEW error_count ENGINE=AggregatingMergeTree ORDER BY (server, level) AS SELECT server, level, countState(*) as tot FROM enterprise_log GROUP BY server, level; SELECT * FROM error_count; -- ERRATA! ; ┌─server──┬─level─┬─tot─┐ │ server1 │ WARN │ │ └─────────┴───────┴─────┘ SELECT server, level, countMerge(tot) FROM error_count GROUP BY server, level; ┌─server──┬─level─┬─countMerge(tot)─┐ │ server1 │ WARN │ 69 │ └─────────┴───────┴─────────────────┘

Per nascondere la difficolta' delle funzioni di gruppo -Merge e' ovviamente possibile utilizzare una normale vista... ma questo e' banale!

Storage table

E' possibile prima creare la tabella per la memorizzazione dei dati (Storage Table) e poi assegnarla alla vista materializzata con la clausola TO.
CREATE TABLE error_store ( `server` String, `level` String, `tot` AggregateFunction(count, DateTime, String, String, String)) ENGINE = AggregatingMergeTree ORDER BY (server, level) SETTINGS index_granularity = 8192; CREATE MATERIALIZED VIEW error_count TO error_store AS SELECT server, level, countState(*) as tot FROM enterprise_log GROUP BY server, level;

Questa sintassi e' piu' flessibile perche' consente di modificare la struttura dati senza ricreare la vista ovvero ricreare la MV senza perdere i dati.
L'altro vantaggio e' nell'evoluzione del database: l'aggiunta di colonne o la loro variazione diventa cosi' possibile nel tempo.

Esempi

Le MV possono essere utilizzate per molti scopi... un esempio classico e' quello di reperire l'ultimo inserimento eseguito, a fronte di certe condizioni, su una tabella di grandi dimensioni [NdA non e' stata utilizzata la storage table per brevita']:
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_st group by tag_id;

E' stata utilizzata una MV raggruppando per un campo chiave. E' importante notare l'Engine utilizzato (AggregatingMergeTree) e la clausola di ORDER BY.

Altro esempio classico e' quello di aggregare i dati per intervallo di tempo:

CREATE MATERIALIZED VIEW cpu_1h_mv TO ... ( `tag_id` String, `created_at` DateTime('Etc/UTC'), `byte_in` AggregateFunction(sum, UInt64), `byte_out` AggregateFunction(sum, UInt64), `temp` AggregateFunction(avg, Float16), ... ) AS SELECT tag_id, toStartOfHour(created_at) AS created_at, sumState(byte_in) AS byte_in, sumState(byte_out) AS byte_out, avgState(temp) AS temp, ... from cpu group by tag_id, toStartOfHour(created_at);

Alle MV possono essere assegnate retention differenti dalle tabelle da cui hanno origine, ma questo lo vediamo in un altro articolo!

Varie ed eventuali

Il documento Introduzione a ClickHouse contiene una presentazione sulle funzioni di base, DBA scripts contiene alcuni dei comandi piu' utili per il DBA ed il documento Architettura di ClickHouse descrive gli aspetti piu' tecnici.

Le materialized view sono disponibili nei principali database relazionali ma presentano differenze rispetto all'implementazione di ClickHouse. Infatti su altri Database relazionali (eg. Oracle, PostgreSQL) le Materialized View sono tipicamente mantenute ad intervalli di tempo regolari rieseguendo ogni volta la query. Un comportamento simile a quello di ClickHouse con un database relazionale si puo' ottenere utilizzando i trigger... che pero' operano record per record e non per Part come in ClickHouse.
Passando ai Times-Series DB anche le Continuous Queries (CQ) di InfluxQL vengono eseguite a tempo mentre TimescaleDB ha di recente introdotto le viste continuous-aggregates che sono molto simili a quelle di ClickHouse.

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