Come installare e usare ClickHouse su Ubuntu 18.04 LTS

Introduzione

ClickHouse è un database di analisi open source orientato alle colonne creato da Yandex per OLAP e casi di utilizzo di big data. Il supporto di ClickHouse per l'elaborazione delle query in tempo reale lo rende adatto per le applicazioni che richiedono risultati analitici in un secondo. Il linguaggio di query di ClickHouse è un dialetto di SQL che consente potenti funzionalità di query dichiarative offrendo al contempo familiarità e una curva di apprendimento più piccola per l'utente finale.

I database orientati alle colonne memorizzano i record in blocchi raggruppati per colonne anziché per righe. Non caricando i dati per le colonne assenti nella query, i database orientati alla colonna impiegano meno tempo a leggere i dati durante il completamento delle query. Di conseguenza, questi database possono calcolare e restituire risultati molto più velocemente rispetto ai tradizionali sistemi basati su righe per determinati carichi di lavoro, come OLAP.

I sistemi di elaborazione di analisi online (OLAP) consentono di organizzare grandi quantità di dati e di eseguire query complesse. Sono in grado di gestire petabyte di dati e restituire rapidamente i risultati delle query. In questo modo, OLAP è utile per lavorare in aree come la scienza dei dati e l'analisi aziendale.

In questo tutorial, installerai il server di database e il client ClickHouse sul tuo computer. Utilizzerai il DBMS per attività tipiche e facoltativamente abiliterai l'accesso remoto da un altro server in modo da poterti connettere al database da un'altra macchina. Quindi testerai ClickHouse modellando e interrogando i dati di visita del sito Web di esempio.

Prerequisiti

• Un server Ubuntu 18.04 con un utente sudo non root abilitato.

Se desideri installare ClickHouse su un server in remoto continua a leggere, altrimenti salta il primo paragrafo "Connessione al Server" e leggi il successivo.

Connessione al Server

Per accedere al server, è necessario conoscere l'indirizzo IP. Avrai anche bisogno dell'username e della password per l'autenticazione. Per connettersi al server come utente root digitare il seguente comando:

ssh root@IP_DEL_SERVER

Successivamente sarà necessario inserire la password dell'utente root.

Se non utilizzate l'utente root potete connettervi con un'altro nome utente utilizzando lo stesso comando, quindi modificare root con il vostro nome_utente:

ssh nome_utente@IP_DEL_SERVER

Successivamente vi verrà chiesto di inserire la password del vostro utente.

La porta standard per connettersi tramite ssh è la 22, se il vostro server utilizza una porta diversa, sarà necessario specificarla utilizzando il parametro -p, quindi digitare il seguente comando:

ssh nome_utente@IP_DEL_SERVER -p PORTA

Passaggio 1: Installare ClickHouse

In questa sezione, installerai il server ClickHouse e i programmi client utilizzando apt-get.

Yandex mantiene un repository APT con l'ultima versione di ClickHouse. Aggiungi la chiave GPG del repository in modo da poter scaricare in modo sicuro pacchetti ClickHouse convalidati:

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4

Vedrai un output simile al seguente:

Executing: /tmp/apt-key-gpghome.JkkcKnBAFY/gpg.1.sh --keyserver keyserver.ubuntu.com --recv E0C56BD4

gpg: key C8F1E19FE0C56BD4: public key "ClickHouse Repository Key <[email protected]>" imported
gpg: Total number processed: 1
gpg:               imported: 1

L'output conferma di aver verificato e aggiunto correttamente la chiave.

Aggiungi il repository all'elenco dei repository APT eseguendo:

echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" | sudo tee /etc/apt/sources.list.d/clickhouse.list

Ora dai il comando apt-get update per aggiornare i tuoi pacchetti:

sudo apt-get update

I pacchetti clickhouse-server e clickhouse-client saranno ora disponibili per l'installazione. Installali con:

sudo apt-get install -y clickhouse-server clickhouse-client

Il server e il client ClickHouse sono stati installati correttamente. Ora sei pronto per avviare il servizio di database e assicurarti che funzioni correttamente.

Passaggio 2: Avviare il servizio

Il pacchetto clickhouse-server installato nella sezione precedente crea un servizio systemd che esegue azioni come l'avvio, l'arresto e il riavvio del server di database. systemd è un sistema init per Linux per inizializzare e gestire servizi. In questa sezione avvierai il servizio e verificherai che funzioni correttamente.

Avviare il servizio clickhouse-server eseguendo questo comando:

sudo service clickhouse-server start

Il comando precedente non visualizzerà alcun output. Per verificare che il servizio sia in esecuzione correttamente, eseguire:

sudo service clickhouse-server status

Vedrai un output simile al seguente:

● clickhouse-server.service - ClickHouse Server (analytic DBMS for big data)
   Loaded: loaded (/etc/systemd/system/clickhouse-server.service; enabled; vendor preset: enabled)
   Active: active (running) since Mon 2020-03-09 20:19:14 CET; 2s ago
 Main PID: 2549 (clickhouse-serv)
    Tasks: 40 (limit: 2298)
   CGroup: /system.slice/clickhouse-server.service
           └─2549 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pi

L'output indica che il server è in esecuzione.

Il server ClickHouse è stato avviato correttamente e ora sarà possibile utilizzare il programma CLI clickhouse-client per connettersi al server.

Passaggio 3: Creare database e tabelle

In ClickHouse, è possibile creare ed eliminare database eseguendo istruzioni SQL direttamente nel prompt del database interattivo. Le istruzioni sono costituite da comandi che seguono una particolare sintassi che indicano al server di database di eseguire un'operazione richiesta insieme a tutti i dati richiesti. È possibile creare database utilizzando la sintassi CREATE DATABASE table_name. Per creare un database, avviare prima una sessione client eseguendo il comando seguente, se hai inserito un password durante il processo di installazione:

clickhouse-client --password

Se non hai inserito una password, digitare semplicemente questo comando:

clickhouse-client

Questo comando accederà al prompt del client in cui è possibile eseguire istruzioni SQL ClickHouse per eseguire azioni come:

• Creazione, aggiornamento ed eliminazione di database, tabelle, indici, partizioni e viste.
• Esecuzione di query per recuperare dati che vengono facoltativamente filtrati e raggruppati utilizzando varie condizioni.

In questo passaggio, con il client ClickHouse pronto per l'inserimento di dati, creerai un database e una tabella. Ai fini di questa esercitazione, creerai un database denominato test e al suo interno creerai una tabella denominata visits che tiene traccia della durata delle visite al sito Web.

Ora che ti trovi nel prompt dei comandi di ClickHouse, crea il tuo database test eseguendo:

CREATE DATABASE test;

Vedrai il seguente output che mostra che hai creato il database:

CREATE DATABASE test

Ok.

0 rows in set. Elapsed: 0.003 sec.

Una tabella ClickHouse è simile alle tabelle in altri database relazionali; contiene una raccolta di dati correlati in un formato strutturato. È possibile specificare le colonne con i loro tipi, aggiungere righe di dati ed eseguire diversi tipi di query sulle tabelle.

La sintassi per la creazione di tabelle in ClickHouse segue questa struttura di esempio:

CREATE TABLE table_name
(
    column_name1 column_type [options],
    column_name2 column_type [options],
    ...
) ENGINE = engine

I valori table_name e column_name possono essere qualsiasi identificatore ASCII valido. ClickHouse supporta una vasta gamma di tipi di colonne; alcuni dei più popolari sono:

• UInt64: utilizzato per memorizzare valori interi compresi tra 0 e 18446744073709551615.
• Float64: usato per memorizzare numeri in virgola mobile come 2039.23, 10.5, ecc.
• String: utilizzato per la memorizzazione di caratteri di lunghezza variabile. Non richiede un attributo di lunghezza massima poiché può memorizzare lunghezze arbitrarie.
• Date: utilizzato per memorizzare le date che seguono il formato YYYY-MM-DD.
• DateTime: utilizzato per la memorizzazione delle date associate all'ora e segue il formato YYYY-MM-DD HH:MM:SS.

Dopo le definizioni delle colonne, si specifica il motore utilizzato per la tabella. In ClickHouse, i motori determinano la struttura fisica dei dati sottostanti, le capacità di query della tabella, le sue modalità di accesso simultaneo e il supporto per gli indici. Diversi tipi di motore sono adatti a requisiti di applicazione diversi. Il tipo di motore più comunemente usato e ampiamente applicabile è MergeTree.

Ora che hai una panoramica della creazione della tabella, creerai una tabella. Inizia confermando il database che modificherai:

USE test;

Vedrai il seguente output che mostra che sei passato al database test dal database default:

USE test

Ok.

0 rows in set. Elapsed: 0.001 sec.

Il resto di questa guida supporrà che si stiano eseguendo istruzioni nel contesto di questo database.

Crea la tua tabella visits eseguendo questo comando:

CREATE TABLE visits (
 id UInt64,
 duration Float64,
 url String,
 created DateTime
) ENGINE = MergeTree() 
PRIMARY KEY id 
ORDER BY id;

Ecco una ripartizione di ciò che fa il comando. Si crea una tabella denominata visits che ha quattro colonne:

• id: La colonna chiave primaria. Analogamente ad altri sistemi RDBMS, una colonna chiave primaria in ClickHouse identifica in modo univoco una riga; ogni riga dovrebbe avere un valore univoco per questa colonna.
• duration: Una colonna float utilizzata per memorizzare la durata di ciascuna visita in secondi. Le colonne float possono memorizzare valori decimali come 12.50.
• url: Una colonna stringa che memorizza l'URL visitato, ad esempio http://example.com.
• created: Una colonna di data e ora che traccia quando si è verificata la visita.

Dopo le definizioni delle colonne, si specifica MergeTree come motore di archiviazione per la tabella. La famiglia di motori MergeTree è consigliata per i database di produzione grazie al supporto ottimizzato per inserimenti in tempo reale di grandi dimensioni, robustezza complessiva e supporto delle query. Inoltre, i motori MergeTree supportano l'ordinamento delle righe in base alla chiave primaria, il partizionamento delle righe e la replica e il campionamento dei dati.

Se si intende utilizzare ClickHouse per l'archiviazione di dati non richiesti frequentemente o per l'archiviazione di dati temporanei, è possibile utilizzare la famiglia di motori Log per l'ottimizzazione per quel caso d'uso.

Dopo le definizioni delle colonne, definirai altre opzioni a livello di tabella. La clausola PRIMARY KEY imposta come colonna chiave primaria id e la clausola ORDER BY memorizzerà i valori ordinati per colonna id. Una chiave primaria identifica in modo univoco una riga e viene utilizzata per accedere in modo efficiente a una singola riga e un'efficiente collocazione delle righe.

Eseguendo l'istruzione create, vedrai il seguente output:

CREATE TABLE visits
(
    id UInt64,
    duration Float64,
    url String,
    created DateTime
)
ENGINE = MergeTree()
PRIMARY KEY id
ORDER BY id

Ok.

0 rows in set. Elapsed: 0.010 sec.

In questa sezione, hai creato un database e una tabella per tracciare i dati delle visite al sito web. Nel passaggio successivo, inserirai i dati nella tabella, aggiornerai i dati esistenti ed eliminerai tali dati.

Passaggio 4: Inserire, aggiornare ed eliminare dati e colonne

In questo passaggio, utilizzerai la tabella visits per inserire, aggiornare ed eliminare i dati. Il seguente comando è un esempio della sintassi per l'inserimento di righe in una tabella ClickHouse:

INSERT INTO table_name VALUES (column_1_value, column_2_value, ....);

Ora, inserisci alcune righe di esempi di dati di visite al sito Web nella visits tabella eseguendo ciascuna delle seguenti istruzioni:

INSERT INTO visits VALUES (1, 10.5, 'http://example.com', '2019-01-01 00:01:01');

INSERT INTO visits VALUES (2, 40.2, 'http://example1.com', '2019-01-03 10:01:01');

INSERT INTO visits VALUES (3, 13, 'http://example2.com', '2019-01-03 12:01:01');

INSERT INTO visits VALUES (4, 2, 'http://example3.com', '2019-01-04 02:01:01');

Vedrai il seguente output ripetuto per ogni istruzione insert.

INSERT INTO visits VALUES

Ok.

1 rows in set. Elapsed: 0.004 sec.

L'output per ogni riga mostra che l'hai inserito correttamente nella tabella visits.

Ora aggiungerai una colonna aggiuntiva alla tabella visits. Quando si aggiungono o si eliminano colonne da tabelle esistenti, ClickHouse supporta la sintassi ALTER.

Ad esempio, la sintassi di base per l'aggiunta di una colonna a una tabella è la seguente:

ALTER TABLE table_name ADD COLUMN column_name column_type;

Aggiungi una colonna denominata location che memorizzerà la posizione delle visite a un sito Web eseguendo la seguente dichiarazione:

ALTER TABLE visits ADD COLUMN location String;

Vedrai un output simile al seguente:

ALTER TABLE visits
    ADD COLUMN
    location String


Ok.

0 rows in set. Elapsed: 0.014 sec.

L'output mostra che hai aggiunto location correttamente la colonna.

A partire dalla versione 19.3.6, ClickHouse non supporta l'aggiornamento e l'eliminazione di singole righe di dati a causa di vincoli di implementazione. ClickHouse supporta tuttavia gli aggiornamenti e le eliminazioni di massa e ha una sintassi SQL distinta per queste operazioni per evidenziarne l'utilizzo non standard.

La sintassi seguente è un esempio di aggiornamento in blocco delle righe:

ALTER TABLE table_name UPDATE  column_1 = value_1, column_2 = value_2 ...  WHERE  filter_conditions;

Eseguirai la seguente istruzione per aggiornare la colonna url di tutte le righe che hanno un valore duration inferiore a 15. Immettila nel prompt del database per eseguire:

ALTER TABLE visits UPDATE url = 'http://example2.com' WHERE duration < 15;

L'output dell'istruzione di aggiornamento in blocco sarà il seguente:

ALTER TABLE visits
    UPDATE url = 'http://example2.com' WHERE duration < 15


Ok.

0 rows in set. Elapsed: 0.003 sec.

L'output mostra che la query di aggiornamento è stata completata correttamente. L'output 0 rows in set indica che la query non ha restituito alcuna riga; questo sarà il caso di qualsiasi aggiornamento ed eliminazione di query.

La sintassi di esempio per l'eliminazione di massa di righe è simile all'aggiornamento delle righe e ha la seguente struttura:

ALTER TABLE table_name DELETE WHERE filter_conditions;

Per testare l'eliminazione dei dati, eseguire la seguente istruzione per rimuovere tutte le righe con un valore duration inferiore a 5:

ALTER TABLE visits DELETE WHERE duration < 5;

L'output dell'istruzione di eliminazione in blocco sarà simile a:

ALTER TABLE visits
    DELETE WHERE duration < 5


Ok.

0 rows in set. Elapsed: 0.003 sec.

L'output conferma che hai eliminato le righe con una durata inferiore a cinque secondi.

Per eliminare le colonne dalla tabella, la sintassi dovrebbe seguire questa struttura di esempio:

ALTER TABLE table_name DROP COLUMN column_name;

Elimina la colonna location che hai aggiunto in precedenza eseguendo quanto segue:

ALTER TABLE visits DROP COLUMN location;

L'output DROP COLUMN che conferma che hai eliminato la colonna sarà il seguente:

ALTER TABLE visits
    DROP COLUMN
    location String


Ok.

0 rows in set. Elapsed: 0.010 sec.

Ora che hai inserito, aggiornato ed eliminato correttamente righe e colonne nella tabella visits, passerai alla query dei dati nel passaggio successivo.

Passaggio 5: Query dei dati

Il linguaggio di query di ClickHouse è un dialetto personalizzato di SQL con estensioni e funzioni adatte ai carichi di lavoro di analisi. In questo passaggio, eseguirai query di selezione e aggregazione per recuperare dati e risultati dalla tabella visits.

Le query di selezione consentono di recuperare righe e colonne di dati filtrate in base alle condizioni specificate, insieme a opzioni come il numero di righe da restituire. È possibile selezionare righe e colonne di dati utilizzando la sintassi SELECT. La sintassi di base per le query SELECT è:

SELECT func_1(column_1), func_2(column_2) FROM table_name WHERE filter_conditions row_options;

Eseguire la seguente istruzione per recuperare i valori url e duration per le righe dove  url è http://example.com.

SELECT url, duration FROM visits WHERE url = 'http://example2.com' LIMIT 2;

Vedrai il seguente output:

SELECT
    url,
    duration
FROM visits
WHERE url = 'http://example2.com'
LIMIT 2

┌─url─────────────────┬─duration─┐
│ http://example2.com │     10.5 │
│ http://example2.com │       13 │
└─────────────────────┴──────────┘

2 rows in set. Elapsed: 0.003 sec.

L'output ha restituito due righe che corrispondono alle condizioni specificate. Ora che hai selezionato i valori, puoi passare all'esecuzione di query di aggregazione.

Le query di aggregazione sono query che operano su un set di valori e restituiscono valori di output singoli. Nei database di analisi, queste query vengono eseguite frequentemente e sono ottimizzate dal database. Alcune funzioni aggregate supportate da ClickHouse sono:

• count: restituisce il conteggio delle righe corrispondenti alle condizioni specificate.
• sum: restituisce la somma dei valori di colonna selezionati.
• avg: restituisce la media dei valori di colonna selezionati.

Alcune funzioni aggregate specifiche di ClickHouse includono:

• uniq: restituisce un numero approssimativo di righe distinte corrispondenti.
• topK: restituisce una matrice dei valori più frequenti di una colonna specifica usando un algoritmo di approssimazione.

Per dimostrare l'esecuzione delle query di aggregazione, calcolerai la durata totale delle visite eseguendo la query sum:

SELECT SUM(duration) FROM visits;

Vedrai un output simile al seguente:

SELECT SUM(duration)
FROM visits

┌─SUM(duration)─┐
│          76.7 │
└───────────────┘

1 rows in set. Elapsed: 0.005 sec.

Ora, calcola i primi due URL eseguendo:

SELECT topK(2)(url) FROM visits;

Vedrai un output simile al seguente:

SELECT topK(2)(url)
FROM visits

┌─topK(2)(url)──────────────────────────────────┐
│ ['http://example2.com','http://example1.com'] │
└───────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.010 sec.

Ora che hai interrogato correttamente la tua tabella visits, eliminerai tabelle e database nel passaggio successivo.

Passaggio 6: Eliminare tabelle e database

In questa sezione, eliminerai la tabella visits e il database test.

La sintassi per l'eliminazione delle tabelle segue questo esempio:

DROP TABLE table_name;

Per eliminare la tabella visits, eseguire la seguente istruzione:

DROP TABLE visits;

Vedrai il seguente output che dichiara che hai eliminato correttamente la tabella:

DROP TABLE visits

Ok.

0 rows in set. Elapsed: 0.005 sec.

È possibile eliminare i database utilizzando la sintassi DROP database table_nametest. Per eliminare il database, eseguire la seguente istruzione:

DROP DATABASE test;

L'output risultante mostra che hai eliminato correttamente il database.

DROP DATABASE test

Ok.

0 rows in set. Elapsed: 0.003 sec.

Hai eliminato tabelle e database in questo passaggio. Ora che hai creato, aggiornato ed eliminato database, tabelle e dati nell'istanza di ClickHouse, nella sezione successiva abiliterai l'accesso remoto al tuo server di database.

Passaggio 7: Impostare le regole del firewall (facoltativo)

Se si intende utilizzare ClickHouse solo localmente con applicazioni in esecuzione sullo stesso server o non è abilitato un firewall sul server Ubuntu, non è necessario completare questa sezione. Se invece ti connetti al server di database ClickHouse da remoto, dovresti seguire questo passaggio.

Attualmente il tuo server ha un firewall abilitato che disabilita il tuo indirizzo IP pubblico accedendo a tutte le porte. Completerai i due passaggi seguenti per consentire l'accesso remoto:

• Modifica la configurazione di ClickHouse e consentigli di ascoltare su tutte le interfacce.
• Aggiungi una regola firewall che consenta le connessioni in entrata alla porta 8123, che è la porta HTTP su cui è in esecuzione il server ClickHouse.

Se ci si trova all'interno del prompt del database, uscire digitando CTRL+D.

Modifica il file di configurazione eseguendo:

sudo nano /etc/clickhouse-server/config.xml

Quindi decommenta la riga contenente <!-- <listen_host>0.0.0.0</listen_host> -->, come il seguente file:

...
 <interserver_http_host>example.yandex.ru</interserver_http_host>
    -->

    <!-- Listen specified host. use :: (wildcard IPv6 address), if you want to accept connections both with IPv4 and IPv6 from everywhere. -->
    <!-- <listen_host>::</listen_host> -->
    <!-- Same for hosts with disabled ipv6: -->
    <listen_host>0.0.0.0</listen_host>

    <!-- Default values - try listen localhost on ipv4 and ipv6: -->
    <!--
    <listen_host>::1</listen_host>
    <listen_host>127.0.0.1</listen_host>
    -->
...

Salva il file ed esci nano. Per applicare la nuova configurazione, riavviare il servizio eseguendo:

sudo service clickhouse-server restart

Non vedrai alcun output da questo comando. Il server ClickHouse è in ascolto sulla porta 8123 per le connessioni HTTP e sulla porta 9000 per le connessioni da clickhouse-client. Consenti l'accesso a entrambe le porte per l'indirizzo IP del tuo secondo server con il seguente comando:

sudo ufw allow from second_server_ip/32 to any port 8123  

sudo ufw allow from second_server_ip/32 to any port 9000  

Vedrai il seguente output per entrambi i comandi che mostra che hai abilitato l'accesso ad entrambe le porte:

Rule added

ClickHouse sarà ora accessibile dall'IP che hai aggiunto. Sentiti libero di aggiungere ulteriori IP come l'indirizzo del tuo computer locale, se necessario.

Per verificare che sia possibile connettersi al server ClickHouse dal computer remoto, seguire innanzitutto i passaggi del passaggio 1 di questa esercitazione sul secondo server e assicurarsi di disporre dell'installazione clickhouse-client  su di esso.

Ora che hai effettuato l'accesso al secondo server, avvia una sessione client eseguendo:

clickhouse-client --host your_server_ip

Vedrai il seguente output che mostra che ti sei connesso correttamente al server:

ClickHouse client version 19.3.6.
Connecting to your_server_ip:9000 as user default.
Connected to ClickHouse server version 19.3.6 revision 54415.

hostname

In questo passaggio, hai abilitato l'accesso remoto al server di database ClickHouse modificando le regole del firewall.

Conclusione

Hai configurato correttamente un'istanza del database ClickHouse sul tuo server e creato un database e una tabella, aggiunto dati, eseguito query ed eliminato il database. Nella documentazione di ClickHouse puoi leggere i loro parametri di riferimento rispetto ad altri database di analisi commerciali e open source e documenti di riferimento generali.