Utilizzando il proxy per analizzare i pacchetti
si nota subito la semplicita'
del protocollo MySQL.
A parte l'handshake nello scambio
della password durante l'autenticazione
tutto l'SQL passa in chiaro.
ClickHouse
e' un Columnar Database SQL, distribuito ed Open Source con ottime prestazioni
sulle attivita' OLAP (On-Line Analytical Processing).
In questa paginetta vediamo come utilizzare ClickHouse
con il protocollo ed i tool client MySQL.
ClickHouse ha infatto implementato anche un'interfaccia
di protocollo standard MySQL e puo' essere utilizzato
quasi come se fosse un DB MySQL/MariaDB
pur mantenendo le sue caratteristiche di potente database colonnare.
ClickHouse e' di semplice installazione, gestione ed utilizzo
poiche' ha un'interfaccia SQL.
Anche se di recente introduzione ha raggiunto
una buona maturita' e completezza funzionale per essere
utilizzato in ambienti di produzione.
Gli argomenti contenuti in questa pagina sono: Introduzione, MySQL wire protocol, Esempi di utilizzo, Integrazioni MySQL/ClickHouse, ...
Il documento si riferisce alla versione 19.8 o successive di ClickHouse perche' il MySQL wire protocol e' stato introdotto in tale versione [NdA 19.8.3.8, 2019-06].
ClickHouse e' un recente database colonnare
adatto alle attivita' OLAP e terribilmente veloce.
ClickHouse utilizza un approccio differente rispetto
alla rappresentazione ISAM
e con indici B-Tree tipica dei DB relazionali
memorizzando i dati per colonna con una forte compressione.
Questo consente di utilizzare algoritmi per l'accesso ai dati
che possono essere eseguiti in parallelo.
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'interfaccia l'SQL rende facilmente utilizzabile ClickHouse a chiunque conosca i database relazionali.
Dal punto di vista tecnico
ClickHouse e' costituito da un solo processo clickhouse-server, avviato con systemd, che gira
come utente clickhouse.
Internamente sono presenti decine di thread che operano in modo indipendente.
La porta socket piu' nota e' la 8123, con protocollo HTTP, che e' utilizzata
da i principali driver (eg. JDBC), importante e' anche la
9000: interfaccia nativa utilizzata dal protocollo interno tra client e server ClickHouse.
Ma per l'interfaccia MySQL...
ClickHouse puo' essere configurato per ascoltare su una ulteriore porta
con il protocollo di rete MySQL,
in questo modo si puo' utilizzare ClickHouse con tutti i programmi
che accedono a MySQL!
Si puo' utilizzare la classica porta 3306 oppure la 9004
suggerita dalla documentazione.
La configurazione e' molto semplice,
basta impostare la porta nel file di configurazione config.xml:
<mysql_port>3306</mysql_port>
Basta riavviare CH ed e' possibile connettersi.
Per l'accesso e' possibile utilizzare utenti senza password...
ma per definire utenze che vengano riconosciute con l'handshake MySQL
e' opportuno definire la password nel file user.xml con:
<password_double_sha1_hex>958ea6f10c0c7eaca08bdfeb3b65c8925c00f9c4</password_double_sha1_hex>
Dove la password si ottiene con:
echo -n "MyPass" | sha1sum | tr -d '-'| xxd -r -p | sha1sum | tr -d '-'
Abbiamo scelto il duoble SHA1 perche' utilizzando lo sha256 per alcuni client si presenta l'errore:
MySQLHandler: DB::Exception: Client doesn't support authentication method sha256_password used by ClickHouse. Specifying user password using 'password_double_sha1_hex' may fix the problem.
E' anche possibile definire, come per tutte le altre utenze ClickHouse e come avviene su MySQL,
una condizione sugli IP di provenienza.
Per default vengono accettati tutti gli indirizzi come se fosse @'%' in MySQL.
E' ora possibile utilizzare ClickHouse con un client MySQL!
Ora per connettersi a ClickHouse basta utilizzare un qualsiasi client MySQL:
Con il piu' classico client MySQL (o MariaDB :)
si accede tranquillamente a ClickHouse ed appartentemente non vi sono differenze.
Naturalmente, anche se trasmessi con il protocollo MySQL,
i comandi SQL debbono essere validi per ClickHouse.
Per ricordare le principali differenze:
i datatype sono differenti anche se alcuni sono simili
[NdA i datatype incompatibili vengono convertiti in String];
la gestione dei NULL e' profondamente diversa... ma l'effetto finale e' molto simile;
gli Engine di ClickHouse sono completamente differenti;
non esistono le Stored Routines;
il Data Dictionary e' differente;
non sono presenti comandi di DML se non l'INSERT (che ha la stessa sintassi di MySQL);
non esistono transazioni, COMMIT, lock, ... o simili anche se e' gestita la logica MVCC;
i join piu' complessi tra tabelle non sono supportati in ClickHouse
[NdA anche se e' cambiato moltissimo da questo punto di vista
un corretto disegno di database per ClickHouse e' quello delle fact tables];
MySQL ha parecchie estensioni rispetto all'SQL standard (eg. SHOW PROCESSLIST, SHOW VARIABLES)
solo alcune di queste estensioni sono presenti in ClickHouse e con sintassi leggermente diverse;
ClickHouse e' case sensitive sui nomi di tabelle e sulle colonne
mentre MySQL e' case insensitive;
...
le differenze sono quindi molte.
Se pero' l'esigenza e' quella di eseguire query, cosa che a ClickHouse riesce benissimo anche con basi dati enormi, le differenze rispetto a MySQL sono poche tranne la velocita' [NdA la query di esempio legge oltre 10 miliardi di righe al secondo].
Per studiare meglio il protocollo di rete MySQL la cosa piu' semplice
e' utilizzare un analizzatore di protocollo o un proxy che tracci il
contenuto di ogni messaggio. Facciamolo!
Nel seguito e' riportato il codice di un semplice proxy in Python
che stampa l'hexdump di ogni pacchetto. Basta metterlo in ascolto
su una porta, farlo puntare alla porta MySQL di ClickHouse e...
mettersi a parlare!
import sys
import socket
import threading
def server_loop(local_host, local_port, remote_host, remote_port):
server = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
try:
server.bind((local_host, local_port))
except:
print(f"[!!] Failed to listen on {local_host}:{local_port}")
sys.exit(0)
print(f"[*] Listening on {local_host}:{local_port}")
server.listen(5)
while True:
client_socket, addr = server.accept()
print(f"[==>] Received incoming connection from {addr[0]}:{addr[1]}")
proxy_thread = threading.Thread(target=proxy_handler,
args=(client_socket, remote_host, remote_port))
proxy_thread.start()
def main():
if len(sys.argv[1:]) != 4:
print("Usage: ./proxy.py [localhost] [localport] [remotehost] [remoteport] "
"[remoteport] ")
print("Example: ./proxy.py 127.0.0.1 3307 10.20.30.40 3306")
sys.exit(0)
local_host = sys.argv[1]
local_port = int(sys.argv[2])
remote_host = sys.argv[3]
remote_port = int(sys.argv[4])
server_loop(local_host, local_port, remote_host, remote_port)
def proxy_handler(client_socket, remote_host, remote_port, receive_first):
remote_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
remote_socket.connect((remote_host, remote_port))
remote_buffer = receive_from(remote_socket)
hexdump(remote_buffer)
remote_buffer = response_handler(remote_buffer)
if len(remote_buffer):
print(f"[<==] Sending {len(remote_buffer)} bytes to localhost.")
client_socket.send(remote_buffer)
while True:
local_buffer = receive_from(client_socket)
if len(local_buffer):
print(f"[==>] Received {len(local_buffer)} bytes from localhost.")
hexdump(local_buffer)
local_buffer = request_handler(local_buffer)
remote_socket.send(local_buffer)
print("[==>] Sent to remote.")
hexdump(local_buffer)
remote_buffer = receive_from(remote_socket)
if len(remote_buffer):
print(f"[<==] Received {len(remote_buffer)} bytes from remote.")
hexdump(remote_buffer)
remote_buffer = response_handler(remote_buffer)
client_socket.send(remote_buffer)
print("[<==] Sent to localhost.")
if not len(local_buffer) or not len(remote_buffer):
client_socket.close()
remote_socket.close()
print("[*] No more data, Closing connections.")
break
def hexdump(src, length=16):
result = []
digits = 4 if isinstance(src, str) else 2
for i in range(0, len(src), length):
s = src[i:i + length]
hexa = " ".join(map("{0:0>2X}".format, s))
text = "".join([chr(x) if 0x20 <= x < 0x7F else "." for x in s])
result.append("%04X %-*s %s" % (i, length * (digits + 1), hexa, text))
print("\n".join(result))
def receive_from(connection):
buffer = b""
connection.settimeout(0.1)
try:
count = 0
while True:
count += 1
data = connection.recv(4096)
if not data:
break
buffer += data
except:
pass
return buffer
def request_handler(buffer):
buffer = buffer.replace(b'SET NAMES utf8', b'SET compile=0 ')
buffer = buffer.replace(b'SET NAMES \'utf8mb4\' COLLATE \'utf8mb4_general_ci\'', b'SET compile = 0 ')
buffer = buffer.replace(b'SET NAMES \'utf8\' COLLATE \'utf8_general_ci\'', b'SET compile = 0 ')
buffer = buffer.replace(b'SET autocommit=1', b'SET compile=0 ')
buffer = buffer.replace(b'SET sql_mode=\'STRICT_TRANS_TABLES\'', b'SET compile=0 ')
buffer = buffer.replace(b'SHOW MASTER LOGS', b'select 1 ')
buffer = buffer.replace(b'SET lc_messages ', b'set compile=0 --')
buffer = buffer.replace(b'SELECT @@version, @@version_comment', b'SELECT \'5.7.0\', \'Fake\' ')
buffer = buffer.replace(b'SELECT @@session.auto_increment_increment AS auto_increment_increment', b'SELECT 1 AS auto_increment_increment -- ')
buffer = buffer.replace(b'SELECT @@GLOBAL.character_set_server,@@GLOBAL.collation_server', b'SELECT \'utf8\', \'utf8_general_ci\' ')
return buffer
def response_handler(buffer):
return buffer
main()
Utilizzando il proxy per analizzare i pacchetti
si nota subito la semplicita'
del protocollo MySQL.
A parte l'handshake nello scambio
della password durante l'autenticazione
tutto l'SQL passa in chiaro.
Come ci si puo' aspettare ClickHouse restituisce un errore quando riceve un comando a lui
sconosciuto ma valido per MySQL.
Il semplice proxy di esempio e' anche in grado di effettuare SQL Injection
e sostituire alcuni comandi non riconosciuti da ClickHouse con comandi validi
[NdA ho utilizzato una semplice replace() di stringhe].
Anche l'accesso al Data Dictionary e' differente perche' ClickHouse utilizza
il database system e non il database mysql.
Non e' pero' complesso creare alcune tabelle/viste che replicano le strutture tipiche di MySQL...
CREATE TABLE information_schema.CHARACTER_SETS (
`CHARACTER_SET_NAME` String,
`DEFAULT_COLLATE_NAME` String,
`DESCRIPTION` String,
`MAXLEN` Int64)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 8192;
insert into information_schema.CHARACTER_SETS values('utf8','utf8_general_ci','UTF-8 Unicode',3);
CREATE TABLE information_schema.COLLATIONS (
`COLLATION_NAME` String,
`CHARACTER_SET_NAME` String,
`ID` Int64, `IS_DEFAULT` String,
`IS_COMPILED` String,
`SORTLEN` Int64)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 8192;
insert into information_schema.COLLATIONS values('utf8_general_ci','utf8',33,'Yes','Yes',1);
...
In questo modo e' possibile vedere ClickHouse anche con tool classici per MySQL come il phpMyAdmin e DBeaver!
ClickHouse ha diverse somiglianze con il dialetto SQL di MySQL e vi sono specifiche integrazioni con MySQL ulteriori al wire protocol descritto in precedenza... in questo capitolo cerchiamo di fornire un riassunto delle diverse possibilita'.
L'SQL di ClickHouse e' simile a quello di MySQL,
almeno per gli aspetti generali.
Sono presenti i classici comandi come
USE DATABASE, SHOW TABLES, SHOW CREATE TABLE, SHOW PROCESSLIST ...
che sono tipici di MySQL.
Vi sono pero' anche significative differenze:
non esistono UPDATE e DELETE (disponibili solo come DDL in ClickHouse),
non esistono variabili e STORED ROUTINES,
gli Engine sono ovviamente completamente differenti
(eg. InnoDB vs MergeTree).
Dal punto di vista delle funzioni ClickHouse ha un insieme molto piu' ampio di funzioni statistiche
e di conversione dei dati anche perche' e' fortemente tipato e non esegue conversioni per default.
ClickHouse e' integrato in modo nativo con
tabelle MySQL
da sempre [NdA funzione presente dalla 1.1.54337 del 2018-01].
Leggere i dati da una tabella MySQL utilizzando l'apposito Engine e' molto semplice:
Con l'Engine MySQL funzionano tutti gli statement SQL e' quindi possibile anche eseguire CREATE TABLE AS... La tabella MySQL non deve essere necessariamente una tabella; puo' anche essere una vista che a sua volta accede ad altri oggetti MySQL (ad esempio esegue i join necessari a reperire tutte le informazioni necessarie).
L'integrazione e' disponibile anche a livello di database MySQL [NdA 19.10.1.5, 2019-07]: e' possibile definire come database remoto un database MySQL da cui leggere i dati:
Anche se con ovvie limitazioni e' cosi' possibile accedere direttamente ad un intero database MySQL.
ClickHouse utilizza da sempre i Dictionary per evitare i join nelle star query.
Tra le sorgenti di dati supportate per i Dictionary e' anche presente MySQL.
L'implementazione del source MySQL e' piu' specializzata rispetto al generico source ODBC
perche' permette di indicare una serie di server in replica
e condizioni per la rilettura dei dati (oltre all'indicazione del Lifetime valida per tutti i Dictionary).
Interessanti sono anche le integrazioni fornite da terze parti tra cui clickhouse-mysql-data-reader, che mantiene i dati allineati da un DB MySQL mediante replica, CHproxy per controllare gli accessi, ProxySQL, che dispone del supporto per ClickHouse e quindi consente di collegarsi a ClickHouse con un client MySQL.
Il wire protocol MySQL completa l'insieme di integrazioni con MySQL
rendendo ClickHouse la scelta piu' semplice, oltre che terribilmente
piu' veloce, per eseguire query OLAP su dati provenienti da MySQL.
Come abbiamo visto negli esempi si puo' accedere ad un database Clickhouse
pensando di essere connessi ad database MySQL
[NdA e con le versioni piu' recenti e' sempre piu' vero].
Il documento Introduzione a ClickHouse contiene una presentazione sulle funzioni di base, il documento Architettura ClickHouse ne descrive le parti piu' complesse e la gestione ed infine DBA scripts contiene alcuni dei comandi piu' utili per il DBA.
Il MySQL Wire Protocol o MySQLWire e' stato aggiornato in modo significativo nella versione Stable 19.8.3.8 [NdA 2019-06], e' consigliabile utilizzare tale versione o una successiva; il dettaglio delle versioni di ClickHouse e' riportato sul documento Your Server Stinks.
Titolo: ClickHouse MySQL Protocol
Livello: Medio
Data:
31 Ottobre 2019 🎃 Halloween
Versione: 1.0.3 - 1 Aprile 2021 🐟
©
Autore: mail [AT] meo.bogliolo.name