Di Ajay Lakshminarayanarao e Allen Fung

Google Bigquery è uno strumento potente. Un ingegnere del software con un'idea minimalista di SQL può usarlo con facilità. È possibile hackerare grandi quantità di dati per ottenere intuizioni in pochi secondi. Ogni giorno ShareThis esaminiamo terabyte di dati sociali utilizzabili ed è diventato parte integrante del nostro lavoro di analisi, controllo ed elaborazione.

Uno dei problemi che abbiamo affrontato di recente è stato quello di segmentare le righe in una tabella Bigquery (BQ) basata sul paese. La BQ si basa sulla memorizzazione a colonne. Invece di memorizzare l'intero record su un singolo volume, BQ divide la riga in base ai valori delle colonne e memorizza ogni valore nel proprio volume di archiviazione. Per sfruttare questo schema abbiamo progettato un'efficiente soluzione di interrogazione a basso costo per recuperare i valori per ogni paese come descritto di seguito.

Problema: Separare una tabella BQ per paese
La via diritta

Per separare una tabella BQ per paese dobbiamo interrogare la tabella, tante volte quanto il numero di paesi.

Supponiamo di dover interrogare le colonne A, B, C, D ed E. La dimensione di ogni colonna è di 20GB. Il numero di byte fatturabili sarà di 100GB per la nostra tabella.

Se dobbiamo eseguire la nostra richiesta tante volte quanto il numero di paesi, ovvero 240, il nostro numero di byte fatturabili sarà di 24.000 GB o quasi 24 TB. Diciamo che il costo per 1 TB è di 5$, il nostro costo totale sarà di 120$ per una tabella BQ.

Il modo ottimizzato

Il trucco è quello di raggruppare il numero di campi richiesto in un'unica colonna. Questo da solo non salverà il numero di byte fatturati. Il secondo trucco consiste nel fare perno sulla colonna della segregazione; in questo caso sarà per paese.

Dopo i passaggi precedenti, la nostra tabella dei risultati conterrà tante colonne quanti sono i paesi. Ogni colonna rappresenta i dati di un paese e avrà i dati precedentemente raggruppati solo per quel paese.

Nella nostra tabella BQ di 100GB, possiamo ipotizzare un risultato probabile, in quanto la colonna che rappresenta gli Stati Uniti è di 20GB, i 10 paesi principali in media intorno a 3GB e il resto dei 230 paesi in media verso 0,5 GB.

Ora, i nostri byte fatturabili si basano sulla dimensione dei dati per ogni paese. Il costo fatturabile per l'interrogazione degli Stati Uniti sarà di soli 0,1$, il costo per i principali paesi sarà di 0,015$ ciascuno e il resto dei paesi molto più piccolo.

Il totale dei byte fatturabili sarà di 100GB per la tabella raggruppata e 165GB per le richieste di tutti i paesi. Il costo totale è di circa 1,5$!

Generazione della tabella raggruppata

Possiamo raggruppare le colonne richieste in un'unica colonna utilizzando CONCAT. Possiamo creare una riga in un formato che rappresenta una linea in un file CSV o un oggetto JSON valido.

Per la seconda fase, la query consisterà in 240 dichiarazioni CASE, con una dichiarazione CASE per ogni paese da far ruotare in una particolare colonna. La query sarà grande, ma BQ supporta dimensioni di query fino a 256KB. Possiamo combinare entrambi i passaggi in un'unica query in cui concentriamo in ogni affermazione di caso.

Esempio di richiesta:

SELEZIONARE
( CASO QUANDO geo.ISO = 'US' QUANDO CONCAT( A,B,C,D,E ) ) COME PAESESTATI UNITI,
( CASO QUANDO geo.ISO = 'CA' QUANDO CONCAT( A,B,C,D,E ) ) COME PAESECA ...
DA
mergelog_20160101;