Cloud Intelligence™

Usare ClickHouse per ridurre i costi di BigQuery e Looker - Parte 2

By Sayle MatthewsJun 30, 202416 min read

Questa pagina è disponibile anche in English, Deutsch, Español, Français, 日本語 e Português.

Riprendiamo da dove eravamo

Nella sezione precedente abbiamo visto cosa fa concretamente questo piano e come creare un servizio ClickHouse di base sfruttando l'offerta DBaaS di Aiven o di ClickHouse. In questa sezione passiamo al trasferimento dei dati in ClickHouse e alla configurazione della replica tra ClickHouse e BigQuery.

Repository GitHub

Nel corso dell'articolo farò riferimento a una Cloud Function e a un job di BigQuery. Il codice sorgente di questi due artefatti è disponibile qui, in questo repository GitHub.

Il codice è volutamente molto semplice e pensato a scopo didattico: con ogni probabilità andrà personalizzato per essere utilizzato in uno scenario reale.

Estrarre i dati da BigQuery verso ClickHouse

Al momento non esistono metodi automatizzati per fare CDC o streaming di dati da BigQuery verso una destinazione qualsiasi. Ciò significa che i dati vanno catturati prima dell'inserimento in BigQuery oppure estratti dopo l'inserimento. L'assenza di un metodo ufficiale rende l'argomento piuttosto articolato e per questo, in questo articolo, mi concentrerò solo sui caricamenti batch.

Ne parleremo nella prossima sezione, ma prima dobbiamo estrarre il dataset iniziale da BigQuery e portarlo in ClickHouse per creare la nostra baseline.

BigQuery dispone di una funzione di esportazione che è il modo più semplice per estrarne i dati. Presenta però due svantaggi importanti: può esportare una sola tabella alla volta e può scrivere i dati solo su GCS.

Vista la limitazione di una sola tabella per volta, è il momento giusto per stabilire se replicare in ClickHouse tutte le tabelle oppure quale sottoinsieme rappresenti un buon candidato per la replica e per l'utilizzo da parte di Looker.

Un modo rapido per elencare tutte le tabelle è eseguire questa query sul suo dataset, che ne restituirà l'elenco completo. Inoltre, se non sa con certezza quali tabelle siano usate più di frequente, questa query elencherà il numero di query che hanno colpito ciascuna tabella del dataset. Attenzione: questa query può avere un costo significativo, quindi controlli prima la stima dei costi nell'interfaccia e regoli il numero di giorni da scansionare prima di eseguirla.

Quando si passa all'esportazione vera e propria, il modo migliore è usare il comando CLI bq e prelevare l'intera tabella. Lo consiglio perché il comando SQL "EXPORT DATA…" comporta costi di elaborazione/scansione sul volume di dati esportato, oppure costi di slot se si usano le Editions, mentre il comando CLI o la chiamata API esegue il dump dell'intera tabella senza costi aggiuntivi.

Se invece le serve solo una parte di una tabella — di solito un insieme di partizioni — esiste un trucco: usare il comando bq cp per copiare una partizione in una nuova tabella, caricabile poi direttamente in ClickHouse. Purtroppo questo comando non funziona con i wildcard né con più di una partizione alla volta, quindi va eseguito una partizione per volta. È relativamente facile da automatizzare con uno script, ma riporto comunque il comando:

bq cp –append_table=true `<source_project_id>:<source_dataset>.<source_table>$<source_partition_name>` `<target_project_id>.<target_dataset>.<target_table>`

Se la tabella non è partizionata (e non è enorme), consiglio semplicemente di caricarla per intero in ClickHouse e poi sfoltirla con SQL, così da non incorrere in costi di elaborazione su BigQuery.

Quando è pronto a procedere con l'esportazione iniziale, andiamo avanti.

Esporterò i dati in formato parquet, perché è il tipo di file più vicino al filesystem usato da BigQuery e si carica facilmente in ClickHouse mantenendo intatti i tipi di colonna.

Il comando per esportare una tabella da BigQuery è:

bq extract — destination_format=PARQUET \
<project_id>:<dataset>.<table_name> \
gs://<bucket_name>/<path_and_filename>

(Una nota: se inserisce i file in una sottocartella, verifichi che esista nel suo bucket, altrimenti riceverà un messaggio di errore piuttosto criptico relativo agli argomenti posizionali.)

Una volta esportata la tabella su GCS, è il momento di caricarla in ClickHouse: proseguiamo il nostro percorso.

Caricare i dati iniziali in ClickHouse

Per caricare i dati in ClickHouse, la query SQL ufficiale da eseguire è la seguente (al momento della stesura):

CREATE TABLE <table_name>
ENGINE = MergeTree
ORDER BY tuple() AS
SELECT *
FROM S3Cluster(default,
  'https://storage.googleapis.com/<bucket_name>/<path>/*.parquet');

Nota: in alcune versioni di ClickHouse esiste un bug per cui la query qui sopra fallisce, ma crea comunque la tabella. Per aggirarlo è sufficiente eseguire un INSERT INTO

seguito dalla parte SELECT vista sopra e l'inserimento andrà a buon fine.

Il modo raccomandato e più sicuro è utilizzare una chiave HMAC. Le aggiunga prima al bucket per il service account utilizzato, poiché dimenticarsene è un errore comune. Tenga presente che queste chiavi servono anche per alcuni metodi di replica dei dati descritti più avanti, quindi conviene configurarle subito e averle già generate e caricate nei bucket appropriati.

Quando utilizza queste chiavi, in ClickHouse usi invece questa SQL:

CREATE TABLE <table_name>
ENGINE = MergeTree
ORDER BY tuple() AS
SELECT *
FROM s3Cluster(‘default’,
  ‘https://storage.googleapis.com/<bucket_name>/<path>/*.parquet’,
  <hmac_access_key>,
  <hmac_secret>)

In queste query l'asterisco (*) è applicato al nome del file e cattura tutti i file in quella directory con estensione parquet. Inoltre, non confonda una chiave del service account con una chiave HMAC: sono due cose completamente diverse. Le chiavi HMAC vivono solo all'interno di GCS e non nel servizio IAM di GCP. Infine, se non aggiunge l'estensione ai file, dovrà passare "Parquet" come argomento alla chiamata della funzione s3Cluster, altrimenti potrebbe ricevere un errore (è un problema comune quando si lavora con servizi che eseguono query su GCS utilizzando chiavi HMAC).

Ora arriva la parte un po' folle, dove ClickHouse dà il meglio di sé. Quella riga con ENGINE è una delle chiavi delle prestazioni. Si chiamano Table Engines e da soli sono oggetto di almeno un'intera serie di formazione su ClickHouse, quindi non li tratterò qui. Le rimando invece alla documentazione ufficiale di ClickHouse qui.

Quando carica i dati deve scegliere l'engine corretto in base al suo utilizzo. Gli engine della famiglia MergeTree sono ottimi storage engine generici e includono alcune varianti specifiche, ma le consiglio VIVAMENTE di esaminare anche gli altri disponibili e di abbinare il suo caso d'uso a quello più adatto. È una delle chiavi del successo, se non LA chiave, in termini di prestazioni su ClickHouse! Non lo si ripeterà mai abbastanza.

Detto questo, c'è un ultimo passaggio importante nel caricamento dei dati iniziali in ClickHouse: eliminare i dati temporanei. Troppi se ne dimenticano e finiscono per pagare lo storage GCS finché un audit non lo fa emergere a distanza di tempo: non cada in questa trappola e gestisca subito la cosa per risparmiare. O quantomeno imposti un promemoria per non dimenticarsene.

In genere è una buona idea eliminare i file all'interno del bucket GCS per contenere i costi. Se non è sicuro che le serviranno per un eventuale ricaricamento, può impostare una regola di lifecycle che li elimini dopo 30 giorni (o più), così non se ne dimentica.

Pattern di replica

Il passo successivo è avviare la replica dei dati, che prevede metodi diversi a seconda di come ingerisce attualmente i dati in BigQuery. In questo articolo vedremo come farlo tramite ingestion batch. Lo streaming sarà argomento di un futuro articolo, in quanto si tratta di uno scenario molto più complesso.

L'altro grande tema è che non esiste un approccio valido per tutti: dipende molto dal funzionamento attuale della sua data pipeline. Quello che farò è proporle quelli più comuni che vediamo qui in DoiT, lasciando poi a Lei, in quanto realizzatore concreto, decidere quale sia la strategia migliore per la sua organizzazione.

Prerequisiti per la replica

Esistono alcuni prerequisiti comuni a tutti questi metodi di replica dei dati tra le due sorgenti. Per fortuna la maggior parte dei dataset li avrà già integrati, quindi in genere non è qualcosa di cui preoccuparsi, ma per completezza è giusto menzionarli.

Prerequisito 1: tempo di ingestion o "marker incrementali"

Riuscire a determinare dove la replica è iniziata e/o si è interrotta è fondamentale per evitare di inserire dati duplicati e generare scenari di dati errati. Può trattarsi di un tempo di ingestion o di una "primary key" univoca di natura incrementale. In BigQuery spesso coincide con un confine di partizione.

Prerequisito 2: identificare la sorgente dei dati

In sostanza, si tratta di scoprire da dove provengono i dati prima di essere caricati in BigQuery. Spesso si trovano in GCS o S3 per dati di tipo più batch, oppure possono essere trasmessi in streaming direttamente in BigQuery tramite una subscription Pub/Sub, o ancora arrivare da Dataflow/Beam che li trasforma. Sapere da dove provengono i dati è cruciale per scegliere il metodo di replica più adatto.

Prerequisito 3: stabilire con quale rapidità servono i dati

La domanda è se le servono dati "realtime" o "eventual" da interrogare in ClickHouse tramite il suo strumento di BI. Se le servono entro pochi minuti, parliamo di "realtime"; se invece va bene un aggiornamento ogni 30 minuti o più, parliamo di "eventual": questo orienterà la scelta della strategia.

Configurare la replica batch

La replica batch dei dati su ClickHouse avviene a intervalli, portando i dati dalla sorgente o da BigQuery verso ClickHouse. Sono le strategie più semplici da implementare e si utilizzano quando i dati sono già disponibili in file o tramite un altro meccanismo batch prima di essere caricati in BigQuery.

L'alternativa è estrarre i dati da BigQuery in GCS e poi caricarli da lì in ClickHouse.

Il primo metodo consiste nel caricare i dati archiviati in GCS direttamente in ClickHouse tramite una Cloud Function. Funziona solo se ha già i dati in GCS e in un formato compatibile tra quelli supportati da ClickHouse in caricamento.

Questo metodo è molto semplice e si traduce in un load job lineare, simile a quello che la maggior parte dei clienti esegue verso BigQuery.

Il secondo metodo si applica quando i dati sono già archiviati in BigQuery e devono essere replicati in ClickHouse per essere utilizzati. Non è altrettanto semplice e ha più componenti in gioco e più costi: per questo motivo non è il metodo preferito.

Si noti che, se ha dati in un altro datastore — ad esempio un database MySQL o PostgreSQL — che funge da passaggio intermedio verso BigQuery o che viene replicato verso BigQuery, allora la strada da seguire è il secondo metodo. Più avanti illustrerò il processo per gli scenari con datastore non-BigQuery.

Configurare la replica batch con dati in GCS prima del caricamento in BigQuery

Questo metodo è di gran lunga il più semplice: bastano pochi passaggi e una configurazione minima.

In sintesi, prende i suoi file di dati da GCS ed esegue un LOAD verso ClickHouse. Naturalmente non è sempre così immediato (come al solito), ma ci si avvicina molto e fornisco già la maggior parte del codice necessario.

Il metodo più semplice che ho trovato finora è impostare un trigger su GCS sui file finalizzati nel bucket e richiamare una Cloud Function che carichi il file in ClickHouse. Il procedimento è descritto qui: nel deployment usi pure il codice sorgente fornito, ma legga prima la documentazione per soddisfare i prerequisiti (in particolare la creazione del secret che conterrà tutte le informazioni di connessione).

C'è una precisazione: probabilmente vorrà caricare solo file di un certo tipo e magari indirizzare il file a una tabella diversa in base al percorso o al nome. Questa logica andrà aggiunta da Lei in base al caso d'uso specifico; nel file ho documentato i punti in cui apportare queste modifiche.

Configurare la replica batch con dati già presenti in BigQuery

La strategia di replica successiva è quella batch da BigQuery a ClickHouse a intervalli. Questi metodi sono ideali quando carica i dati direttamente in BigQuery da un'altra fonte e non riesce a intercettarli prima del caricamento. Capita spesso con streaming insert, caricamenti tramite BigQuery Storage API o dati caricati da fonti esterne come Stitch o Fivetran direttamente in BigQuery.

Nota sui costi: questo metodo dovrà probabilmente interrogare i dati a intervalli regolari, con i relativi costi. Ne tenga conto e si assicuri di partizionare e/o clusterizzare adeguatamente i dati nella tabella sottostante per minimizzarli. Può anche optare per la fatturazione on-demand anziché le Editions, a seconda del volume di dati ingerito. Spesso la soluzione preferita è creare un progetto separato dedicato a queste esportazioni dei dati archiviati nel progetto principale, in modo da mantenere una separazione dei workloads. Questo consente anche di usare la fatturazione on-demand o una reservation separata se utilizza le Editions, sfruttando ad esempio la Standard Edition per ridurre i costi.

Lo schema generale del metodo è il seguente: una query schedulata in BigQuery viene eseguita a intervalli ed esporta i nuovi dati in un bucket GCS. Una volta nel bucket, una Cloud Function si attiva e carica i dati in ClickHouse al termine della scrittura del file.

Questo metodo può essere oneroso in termini di query, storage e volume di chiamate alla Cloud Function, ma garantisce il caricamento dei dati a intervalli molto prevedibili. Tuttavia, se non riesce a caricare i dati prima di BigQuery, potrebbe essere la strada migliore e probabilmente le farà comunque risparmiare sui costi di interrogazione.

Per ridurre i costi, in genere conviene allineare le esportazioni dei dati con una partizione, così da poter esportare la partizione direttamente in GCS (vedi il comando sopra). Ad esempio, se partiziona su base oraria, schedulare il processo in modo che possa prelevare un'intera ora di dati alla volta.

Vediamo dunque in dettaglio come funziona!

L'esempio pratico

Per questo esempio ho creato una tabella molto basilare con il seguente schema all'interno di BigQuery:

L'ho partizionata su transaction_time per ottenere query più rapide in questo caso.

I dati vengono caricati in questa tabella ogni ora e contengono tutti i dati dell'ultima ora; conterranno l'intero contenuto di quell'ora, vale a dire che nessun record verrà successivamente incluso in un caricamento posteriore. È una scelta dettata dalla semplicità e che difficilmente si verificherebbe nel mondo reale.

Sempre per semplicità, e come buona prassi, al termine del caricamento viene pubblicato un messaggio in un topic Pub/Sub. A quel topic è collegata una subscription che attiva una Cloud Function che esegue il lavoro al posto suo, evitando di dover allestire una piccola pipeline. In alternativa, una Cloud Function può essere attivata al termine della scrittura del file per caricarlo automaticamente, avvicinando l'esperienza al realtime rispetto al caricamento a intervalli.

Qui il link alla Cloud Function che esegue questo caricamento, insieme a un file readme che ne illustra l'utilizzo.

Si noti che al suo interno ho inserito le credenziali di ClickHouse in un secret di Secret Manager ed espongo quei valori come variabili d'ambiente alla Cloud Function. È il modo più semplice e tra i più sicuri per accedere alle credenziali da una Cloud Function. Ho documentato tutto nel file README.md associato al codice python.

Il primo passo è creare una Cloud Function. Tenga presente che a innescarla è l'evento finalized su un bucket GCS. La configurazione della Cloud Function dovrebbe essere simile a questa (osservi anche l'avviso sul service account: si assicuri di concedere quei permessi):

Ora, prima di salvare, apra il menu a tendina "Runtime, build, connections and security settings" e scorra fino a Security and Image Repo. In alto trova una sezione "Secrets" in cui dovrà aggiungere un secret per ciascuno dei valori richiesti, come nell'esempio sotto. Si assicuri di selezionare "Exposed as environment variable" nel menu a tendina di ognuno, affinché la Cloud Function funzioni.

Dovrà ripetere l'operazione per ciascuno dei secret necessari alla Cloud Function (host, port, secure, username e password). Port e secure hanno valori predefiniti rispettivamente di 9019 e True, quindi se intende utilizzarli i secret non sono obbligatori. Osservi anche l'avviso sul service account che ho lasciato: prima di procedere, verifichi che il service account abbia accesso in lettura a Secret Manager.

Il passaggio successivo è utilizzare questa query come Scheduled Query (qui la documentazione ufficiale per chi non l'avesse mai fatto) in BigQuery, schedulata per essere eseguita 5 minuti dopo l'ora, ogni ora.

Una volta fatto, attenda l'orario stabilito e poi controlli il bucket GCS: dovrebbe essere stata creata una nuova directory con i file. Se è così, la sua Cloud Function ha funzionato.

Utilizzare questo esempio nel mondo reale

Questo esempio è MOLTO semplice e idealizzato per facilitare la comprensione. Nel mondo reale sappiamo bene che i dati non vengono mai caricati perfettamente entro i confini dell'ora e nei tempi previsti, senza arrivi in ritardo. Ho omesso volutamente queste condizioni perché ClickHouse offre una buona risposta: ReplacingMergeTree e CollapsingMergeTree.

Il team di ClickHouse ha scritto un ottimo articolo a riguardo qui, in cui spiega come utilizzarli per update e delete. A seconda dei pattern di utilizzo dei dati, si può scegliere l'uno o l'altro.

È inoltre molto probabile che vorrà aggiungere funzionalità ulteriori — notifiche, attivazione di job ETL/ELT, ecc. — quindi le consiglio di aggiungere altro codice al termine dell'esecuzione della Cloud Function per pubblicare un messaggio su Pub/Sub e attivare eventuali funzionalità a valle.

Collegare Looker a ClickHouse

L'ultima cosa da fare è convertire le sue connessioni Looker affinché utilizzino ClickHouse al posto di BigQuery.

A volte non è facilissimo trovarla, ma deve entrare nella modalità Admin di Looker. La voce si trova in Database->Connections, nel pannello di sinistra della modalità Admin.

Admin->Database->Connections

A questo punto basta cliccare su add e compilare le informazioni dell'host nella pagina delle connessioni per la sua istanza ClickHouse.

Cliccando su connections e selezionando un Dialect ClickHouse apparirà la seguente schermata:

Vista della nuova connessione ClickHouse in Looker

Una volta caricata, copi e incolli le informazioni della sua istanza, come quelle qui sotto prese dalla console Aiven:

Informazioni di connessione dell'istanza dalla console ClickHouse di Aiven

Le informazioni che le serviranno sono in genere quelle relative al connettore JDBC o all'accesso HTTPS su ClickHouse.

Una volta completato il tutto, usi il pulsante test in Looker per verificare che funzioni.

Infine, ed è molto importante per la sicurezza, c'è la funzionalità di whitelisting degli IP nella sua istanza ClickHouse. Questa lista definisce gli unici IP autorizzati a connettersi all'istanza. Seguendo le istruzioni di Google qui, troverà l'elenco di indirizzi IP da inserire in whitelist per l'accesso. In questo modo solo le connessioni provenienti dagli IP di Looker potranno raggiungere la sua istanza ClickHouse.

Vista degli indirizzi IP autorizzati dalla console Aiven

Questo pattern dovrebbe permetterle di mettere in piedi un'istanza ClickHouse a cui far riferimento da Looker al posto di BigQuery, con un potenziale risparmio significativo sui costi di interrogazione.

Trattandosi di un esempio elementare, difficilmente sarà una soluzione pronta all'uso per tutte le sue esigenze di caricamento dati, ma va vista come la prima pietra del guado nel suo percorso verso il risparmio: "cachare" i dati in ClickHouse anziché pagare per ogni query su BigQuery.