Che cos’è Google BigQuery?
A mio avviso, ci possono essere alcune risposte a questa domanda:
BigQuery è un database
Nel senso più ampio, BigQuery è un database. Come sai, i database sono raccolte di dati correlati e BigQuery ti consente di archiviare terabyte di record.
BigQuery è un data warehouse nel cloud
I data warehouse sono sistemi che consentono non solo di raccogliere dati strutturati da più fonti, ma anche di analizzarli. Quindi, puoi chiamarlo un database di analisi per interrogare e ottenere approfondimenti dai tuoi dati.
BigQuery è un database a colonne
Questo titolo si basa sul sistema di archiviazione a colonne di BigQuery che supporta dati semi-strutturati: colonne nidificate e ripetute. Questa è principalmente una definizione tecnica, che abbiamo introdotto per ampliare i tuoi orizzonti.
BigQuery è un database di fogli di calcolo
Questa è una definizione realistica di BigQuery se quelle precedenti non sono sufficienti. BigQuery combina le funzionalità di un software per fogli di calcolo, come Fogli Google, e di un sistema di gestione di database, come MySQL.
Perché dovresti usare BigQuery
Il motivo principale per optare per BigQuery è l’esecuzione di query analitiche. BigQuery ti consente di eseguire query analitiche complesse su grandi set di dati. Le query sono richieste di dati che possono includere calcoli, modifiche, unioni e altre manipolazioni con i dati.
Diciamo che, in Fogli Google, puoi anche eseguire query sui set di dati utilizzando la funzione QUERY. Questo può funzionare per diversi tipi di report e grafici basati su set di dati medio-piccoli. Tuttavia, qualsiasi app per fogli di calcolo (anche Excel) non sarà in grado di gestire query complesse di set di dati di grandi dimensioni che includono milioni di righe in una tabella.
BigQuery ha lo scopo di eseguire query analitiche oltre le semplici operazioni CRUD e può vantare un throughput davvero buono.
Guida alla configurazione di BigQuery
Un altro motivo per cui potresti prendere in considerazione BigQuery è che si tratta di un servizio cloud. Non devi installare alcun software. Google gestisce l’infrastruttura e devi solo configurare BigQuery, il che è abbastanza semplice.
Il primo step è accedere a Google Cloud Platform . Al primo accesso dovrai selezionare il tuo paese e accettare i Termini di servizio.
Successivamente, vai su BigQuery: puoi utilizzare la barra di ricerca o trovarla manualmente nel menu a sinistra.
Crea un progetto BigQuery
Ecco come appare BigQuery alla tua prima visita.
Fai clic sul pulsante Crea progetto, assegna un nome al progetto, scegli l’organizzazione se necessario e fai clic su Crea.
Ora sei ufficialmente il benvenuto in BigQuery.
Che cos’è una sandbox BigQuery
SANDBOX significa che stai utilizzando un account sandbox, che non richiede l’inserimento di dati di pagamento. Questa opzione di livello gratuito ti garantisce 10 GB di spazio di archiviazione attivo e 1 TB di dati di query elaborati al mese. Utilizzando questo account, le tue tabelle scadranno tra 60 giorni. Se opzioni Ulteriori informazioni puoi scoprire altri limiti.
Il secondo banner ti propone di attivare una prova gratuita. La differenza rispetto all’account sandbox è che se attivi la versione di prova, dovrai inserire i tuoi dati di fatturazione. Se lo fai, riceverai $ 300 di crediti cloud gratuiti.
Per il tutorial su Google BigQuery, utilizzeremo l’opzione sandbox. Quindi, sentiti libero di fare clic su Ignora per entrambe le opzioni.
Come usare Google BigQuery: Crea un set di dati in BigQuery
Aggiungiamo alcuni dati in BigQuery per verificare come funziona. Fare clic sul progetto desiderato e quindi su Crea set di dati.
Assegna un ID set di dati: puoi inserire lettere e numeri. Se necessario, è possibile selezionare la posizione dei dati, la scadenza della tabella (fino a 60 giorni) e la crittografia. Successivamente, fai clic su Crea set di dati.
Viene ora creato un nuovo set di dati. Puoi trovarlo facendo clic sul pulsante Espandi nodo accanto al nome del tuo progetto:
Il passaggio successivo consiste nel creare una tabella nel set di dati. Ecco il pulsante da cliccare:
Hai alcune opzioni qui:
· Crea una tabella vuota e riempila manualmente
· Carica una tabella dal tuo dispositivo in uno dei formati supportati (spiegato nella sezione successiva)
· Importa una tabella da Google Cloud Storage o Google Drive (questa opzione ti consente di importare Fogli Google)
· Importa una tabella da Google Cloud Bigtable tramite l’interfaccia a riga di comando
Formati di file che puoi importare in BigQuery
Puoi caricare facilmente i tuoi dati tabulari in BigQuery nei seguenti formati:
· CSV
· JSONL (righe JSON)
· Avro
· Parquet
· ORCO
· Fogli Google (solo per Google Drive)
· Backup di Cloud Datastore (solo per Google Cloud Storage)
· Nota: non puoi importare file Excel direttamente in BigQuery. Per fare ciò, dovrai convertire il tuo file Excel in CSV o convertire Excel in Fogli Google e quindi caricarlo in BigQuery. In questo tutorial su BigQuery, non ci concentreremo sui casi di Excel.
Carica i dati CSV in BigQuery
Dopo aver fatto clic sul pulsante Crea tabella , è necessario completare i seguenti passaggi:
1. Scegli fonte – Carica
2. Seleziona file: fai clic su Sfoglia e scegli il file CSV dal tuo dispositivo
3. Formato file: scegli CSV, ma di solito il sistema rileva automaticamente il formato del file
4. Nome tabella: inserisci il nome della tabella
5. Seleziona la casella di controllo Rilevamento automatico
6. Fare clic su Crea tabella
Questo è l’aspetto del flusso di base. Inoltre, puoi definire le impostazioni della partizione (per dividere la tua tabella in segmenti più piccoli), le impostazioni del cluster (per organizzare i dati in base al contenuto delle colonne specificate), nonché configurare le Opzioni avanzate . Ecco come appare la tua tabella caricata su BigQuery:
Nota: la funzionalità di anteprima della tabella mostra le anteprime delle tabelle archiviate in BigQuery. Ad esempio, quando carichi CSV, viene salvato in BigQuery: vedrai l’anteprima. Tuttavia, quando estrai i dati da Fogli Google, si tratta di una connessione in tempo reale poiché BigQuery esegue la scansione di Fogli Google ogni volta che esegui una query. In questo caso, non avrai l’anteprima disponibile.
Importa manualmente i dati da Fogli Google a BigQuery
Il flusso di lavoro è molto simile, ma con alcune modifiche. Fare clic sul pulsante Crea tabella e:
1. Scegli la fonte – Drive
2. Seleziona Drive URI: inserisci l’URL del foglio di lavoro di Fogli Google
3. Formato file: scegli Fogli Google
4. Intervallo di fogli: specifica il foglio e l’intervallo di dati da importare. Se lasci vuoto questo campo, BigQuery recupererà i dati dal primo foglio del tuo foglio di lavoro.
5. Nome tabella: inserisci il nome della tabella
6. Contrassegnare la casella di controllo Rilevamento automatico
7. Fare clic su Crea tabella
Potresti essere interessato a configurare le opzioni avanzate poiché ti consentono di:
· Salta le righe con i valori della colonna che non corrispondono allo schema.
· Salta un numero specifico di righe dall’alto.
· Abilita l’inclusione delle nuove righe contenute nelle sezioni di dati tra virgolette.
· Abilita l’accettazione di righe che mancano di colonne facoltative finali.
· Seleziona una soluzione di gestione delle chiavi di crittografia.
Dopo aver fatto clic su Crea tabella, il foglio specificato dal foglio di lavoro verrà importato in BigQuery. Ecco i dettagli (l’anteprima della tabella non è disponibile per l’importazione di Fogli Google):
Importa i dati in BigQuery da Fogli Google e altri software in base a una pianificazione
Supponiamo che tu abbia un set di dati in Fogli Google che devi importare in BigQuery ogni giorno. Puoi farlo manualmente come descritto sopra o automatizzare l’importazione dei dati in BigQuery utilizzando una delle integrazioni fornite da Coupler.io.
Coupler.io è uno strumento di integrazione dei dati per automatizzare il flusso di dati da diverse fonti a BigQuery, Fogli Google e Microsoft Excel.
Devi accedere a Coupler.io. Puoi utilizzare il tuo account Google o Microsoft per questo o creare un account con un indirizzo email personalizzato. Quindi fai clic su Aggiungi importatore e seleziona l’applicazione di origine, Fogli Google e l’app di destinazione che è BigQuery. Se desideri connettere BigQuery a Excel, seleziona le rispettive app di conseguenza.
Fare clic su Procedi per configurare queste connessioni all’app.
· Imposta Sorgente
Collegati all’account Google, quindi seleziona un file e un foglio da cui esportare i dati.
Nota: se selezioni più fogli, i relativi dati verranno combinati in una vista principale in una tabella BigQuery.
· Imposta destinazione
Collega il tuo account BigQuery. Per questo, dovrai creare un account di servizio con due ruoli: BigQuery Data Editor e BigQuery Job User. Quindi scarica e aggiungi il tuo file chiave .json. Ecco un’istruzione dettagliata.
Quindi inserisci i nomi del set di dati e della tabella BigQuery in cui verranno caricati i dati di Fogli Google.
· Imposta programma
Dovrai personalizzare la pianificazione delle esportazioni automatiche dei dati da Fogli Google a BigQuery.
Quando sei pronto, fai clic su Salva ed esegui per inserire i tuoi dati nella tabella BigQuery specificata.
In modo simile, puoi caricare dati da Airtable, HubSpot, Excel e altre fonti su BigQuery.
INIZIA ORA
Esplora la piattaforma Coupler.io con una prova gratuita di 14 giorni
Tabelle di query in BigQuery
Il vero potere di BigQuery risiede nelle query. Puoi interrogare le tabelle nel tuo database usando il dialetto SQL standard. È supportato anche il dialetto SQL non standard o legacy, ma BigQuery consiglia di utilizzare il dialetto SQL standard.
Dai un’occhiata al nostro tutorial su Google BigQuery SQL per approfondire questo aspetto.
Se conosci la funzione QUERY di Fogli Google , dovresti capire come funzionano le query. Ad esempio, ecco un esempio di formula QUERY:
= query ( Deals!A:EU, “select E, N, T order by T Desc” )
“select E, N, T order by T Desc”– questa è la query per recuperare tre colonne dell’intero set di dati e ordinare i risultati in ordine decrescente.
In BigQuery, la stessa query sul set di dati importato da Pipedrive in BigQuery avrà il seguente aspetto:
SELEZIONARE
stringa_campo_4,
stringa_campo_13,
campo_stringa_19
DA `test-project-310714.test.pipedrive-deals`
ORDINA PER string_field_19 DESC
Ora spiegherò come funziona.
Come eseguire query sui dati in BigQuery + esempio di sintassi
Fare clic sul pulsante Interroga tabella per avviare l’interrogazione.
Vedrai il boilerplate della query, in questo modo:
SELEZIONA DA `test-project-310714.test.pipedrive-deals` LIMITE 1000
Questo è l’esempio di base che puoi utilizzare per iniziare la tua introduzione alle query. Aggiungere *dopo il metodo SELECT, in modo che la query assomigli a questa:
SELEZIONA * DA `test-project-310714.test.pipedrive-deals` LIMITE 1000
Questa query restituirà tutte le colonne disponibili dalla tabella specificata, ma non più di 1.000 righe. Fai clic su Esegui e il gioco è fatto!
Ora, interroghiamo campi specifici (colonne) e li ordiniamo. Quindi, invece di usare *, dobbiamo specificare i nomi dei campi di cui abbiamo bisogno. Puoi trovare i nomi dei campi nella scheda Schema o dalla tua ultima query.
Sostituiamo il metodo LIMIT dalla query predefinita con ORDER BY: questo ti consentirà di ordinare i dati in base a una colonna specificata. Per ordinare i dati in ordine decrescente, aggiungi DESC alla fine della query. Ecco come appare:
SELEZIONARE
stringa_campo_4,
stringa_campo_13,
campo_stringa_19
DA `test-project-310714.test.pipedrive-deals`
ORDINA PER string_field_19 DESC
Ecco un ottimo tutorial su Google BigQuery SQL . Puoi anche dare un’occhiata a questo tutorial video SQL per principianti, realizzato da Railsware.
Impostazioni della query
Se fai clic su Altro e selezioni Impostazioni query , potrai configurare la destinazione per i risultati della query e altre impostazioni.
Qui puoi anche configurare l’esecuzione di query in batch. Le query batch vengono messe in coda e avviate non appena le risorse inattive sono disponibili nel pool di risorse condivise di BigQuery.
Come salvare le query in BigQuery
Puoi salvare le tue query per un uso successivo. Per fare ciò, fare clic su Salva => Salva query .
Nella finestra successiva, dai un nome alla tua query e scegli la sua visibilità:
· personale : solo tu sarai in grado di modificare la query
· progetto : solo i membri del progetto potranno modificare la query
· public : la query sarà disponibile pubblicamente per la modifica
· Fare clic su Salva.
Puoi trovare le query salvate nella rispettiva scheda popup.
Come pianificare le query in BigQuery
Accanto al pulsante Salva , è presente un pulsante Pianifica , che consente di abilitare le query pianificate. Il tuo primo pensiero: ” Perché dovrei eseguire query in base a una pianificazione? Bene, ci sono almeno due motivi per farlo:
· Le query possono essere enormi e richiedere molto tempo per essere eseguite, quindi è meglio preparare i dati in anticipo.
· Google addebita denaro per le query di dati, quindi se è OK per te avere dati aggiornati quotidianamente, è meglio farlo e utilizzare le visualizzazioni già preparate per interrogarli ad hoc.
Nota: la pianificazione delle query è disponibile solo per i progetti con fatturazione abilitata. Non funzionerà per i progetti di account SANDBOX.
Dopo aver fatto clic sul pulsante Pianifica , riceverai una notifica che ti informa che devi prima abilitare l’API BigQuery Data Transfer.
Fai clic su Abilita API e attendi qualche istante. Successivamente, sarai in grado di creare query pianificate facendo clic sul pulsante Pianifica.
Fare clic su Crea nuova query pianificata e definire i seguenti parametri:
· Nome per la query pianificata
· Opzioni di pianificazione
o Ripete
o Data di inizio e ora di esecuzione
o Data di fine
· Destinazione
o Nome della tabella
o Preferenza di scrittura (sovrascrivi o aggiungi)
§ Sovrascrivi: i risultati della query sovrascriveranno i dati nella tabella
§ Aggiungi: i risultati della query verranno aggiunti ai dati nella tabella
Facoltativamente, puoi impostare opzioni avanzate e opzioni di notifica. Fare clic su Pianifica quando la configurazione è completa.
Successivamente, dovrai selezionare il tuo account Google per continuare con BigQuery Data Transfer Service.
Cronologia delle query
Diciamo che hai dimenticato di salvare la tua query avanzata, ma vuoi ripristinarla ora. Nessun problema, BigQuery ti fornisce i log delle query e dei lavori che hai eseguito. Li troverai nelle schede popup Cronologia lavori e Cronologia query.
Nota: BigQuery visualizza tutti i job di caricamento, esportazione, copia e query degli ultimi 6 mesi. Limita le cronologie dei lavori e delle query a 1.000 voci.
Esporta le query da BigQuery manualmente e automaticamente
Nella maggior parte dei casi, gli utenti devono esportare i risultati delle loro query all’esterno di BigQuery. Le destinazioni comuni sono app per fogli di calcolo, come Fogli Google ed Excel, strumenti di visualizzazione e dashboard, come Google Data Studio (abbiamo un meraviglioso tutorial di Google Data Studio sul nostro blog) e Tableau e altri software. Puoi anche connettere Power BI a BigQuery.
Limiti di esportazione BigQuery
Per aprire le opzioni di esportazione dei dati nativi di BigQuery , devi fare clic sul pulsante Salva risultati e selezionare una delle seguenti opzioni:
· File CSV
o Scarica sul tuo dispositivo (fino a 16.000 righe)
o Scarica su Google Drive (fino a 1 GB)
· file JSON
o Scarica sul tuo dispositivo (fino a 16.000 righe)
o Scarica su Google Drive (fino a 1 GB)
· Tabella BigQuery
· Fogli Google (fino a 16.000 righe)
· Copia negli appunti (fino a 16.000 righe)
Ulteriori informazioni sull’esportazione dei dati BigQuery.
Esempio di esportazione di query da BigQuery
Ad esempio, scegliamo l’opzione della tabella BigQuery. Dovrai scegliere il progetto e il set di dati, nonché assegnare un nome alla tabella.
Fai clic su Salva e il gioco è fatto!
Esporta automaticamente le query da BigQuery a Fogli Google
Il salvataggio manuale dei risultati della query su Fogli Google non è particolarmente efficiente, soprattutto se è necessario farlo periodicamente. Dovresti automatizzare l’esportazione delle query da BigQuery a Fogli Google : bastano pochi minuti e non sono necessarie competenze di programmazione.
Devi accedere a Coupler.io , una soluzione ETL per automatizzare l’integrazione dei dati. Fornisce un’integrazione BigQuery che ti consente di copiare la tabella BigQuery in un’altra tabella , nonché di esportare query da BigQuery, ad esempio in Fogli Google. Quindi, una volta effettuato l’accesso a Coupler.io, fai clic su Aggiungi importatore , seleziona BigQuery come app di origine e Fogli Google come app di destinazione e procedi alla loro configurazione:
· Imposta Sorgente
Ottieni le credenziali per connettere il tuo account BigQuery, quindi inserisci la query.
· Imposta destinazione
Collega il tuo account Google, quindi scegli un foglio di calcolo e un foglio in cui esportare i risultati della query.
· Imposta programma
Dovrai personalizzare la pianificazione delle esportazioni automatiche delle query da BigQuery a Fogli Google.
Quando sei pronto, fai clic su Salva ed esegui per inserire i risultati della query nel foglio di calcolo.
Bonus: come BigQuery memorizza i dati
A differenza dei tradizionali database relazionali che memorizzano i dati riga per riga, l’archiviazione di BigQuery è colonna per colonna. Ciò significa che viene utilizzato un blocco di file separato per memorizzare ciascuna colonna. Questo formato colonnare, chiamato Capacitor, consente a BigQuery di raggiungere un throughput molto elevato, fondamentale per l’elaborazione analitica online.
Architettura BigQuery
Nell’architettura serverless di BigQuery, le risorse per l’archiviazione e il calcolo sono disaccoppiate. Ciò ti consente di portare i tuoi dati di qualsiasi dimensione nel magazzino e di procedere immediatamente con l’analisi dei dati. Ecco le tecnologie di infrastruttura che lo rendono possibile:
· Colossus – responsabile dello stoccaggio. Si tratta di un sistema di archiviazione globale ottimizzato per la lettura di grandi quantità di dati strutturati, nonché per la gestione della replica, del ripristino e della gestione distribuita.
· Dremel – responsabile del calcolo. Si tratta di un cluster multi-tenant che trasforma le query SQL in alberi di esecuzione. Questi alberi hanno foglie chiamate slot e un singolo utente può ottenere migliaia di slot per eseguire le proprie query.
· Giove : responsabile dello spostamento dei dati tra l’archiviazione (Colosso) e il calcolo (Dremel). Questa è una rete petabit, che sposta i dati da un luogo all’altro e lo fa molto rapidamente.
· Borg – responsabile dell’allocazione delle risorse hardware. Questo è un sistema di gestione dei cluster per eseguire centinaia di migliaia di job in BigQuery.
Ulteriori informazioni su BigQuery
Siamo onesti: il nostro obiettivo non era scrivere un perfetto tutorial su BigQuery, ma fornirti le risposte che ogni principiante ha quando scopre un nuovo strumento o una nuova tecnologia. Siamo sicuri che probabilmente avrai altre domande dopo averlo letto, ad esempio sul costo di BigQuery . Dai un’occhiata al nostro blog: abbiamo già risposto ad alcune domande. Tuttavia, per gli altri, dovrai trovare tu stesso le risposte.
La principale fonte di informazioni è la documentazione ufficiale di BigQuery , in cui troverai un’ampia base di conoscenze sull’utilizzo di BigQuery. Lo svantaggio di questa fonte è che è assolutamente enorme e talvolta troppo strutturata (come la maggior parte della documentazione di Google).
Da parte nostra, cercheremo di trattare altri argomenti relativi a BigQuery per chiarire alcuni punti avanzati come la sintassi SQL di BigQuery o l’utilizzo delle librerie client per iniziare con l’API di BigQuery nel tuo linguaggio di programmazione preferito. Buona fortuna!