Introduzione al caricamento dei dati

Questo documento fornisce una panoramica del caricamento dei dati in BigQuery.

Panoramica

Esistono diversi modi per importare dati in BigQuery:

  • Carica in gruppo un set di record di dati.
  • Trasmetti il flusso di singoli record o batch di record.
  • Utilizza le query per generare nuovi dati e aggiungere o sovrascrivere i risultati in una tabella.
  • Utilizzare un'applicazione o un servizio di terze parti.

Caricamento in batch

Con il caricamento in batch, i dati di origine vengono caricati in una tabella BigQuery in un'unica operazione batch. Ad esempio, l'origine dati potrebbe essere un file CSV, un database esterno o un insieme di file di log. I job ETL (estrazione, trasformazione e caricamento) tradizionali rientrano in questa categoria.

Le opzioni per il caricamento in batch in BigQuery includono:

  • Job di caricamento. Carica i dati da Cloud Storage o da un file locale creando un job di caricamento. I record possono essere in formato Avro, CSV, JSON, ORC o Parquet.
  • SQL. L'istruzione SQL LOAD DATA carica i dati da uno o più file in una tabella nuova o esistente. Puoi utilizzare l'istruzione LOAD DATA per caricare file Avro, CSV, JSON, ORC o Parquet.
  • BigQuery Data Transfer Service. Utilizza BigQuery Data Transfer Service per automatizzare il caricamento dei dati dalle app Google Software as a Service (SaaS) o da applicazioni e servizi di terze parti.
  • API BigQuery Storage Write. L'API Storage Write consente di elaborare in batch un numero arbitrariamente di record e di eseguirne il commit in una singola operazione atomica. Se l'operazione di commit non va a buon fine, puoi riprovare in sicurezza. A differenza dei job di caricamento BigQuery, l'API Storage Write non richiede la gestione temporanea dei dati per l'archiviazione intermedia, ad esempio Cloud Storage.
  • Altri servizi gestiti. Utilizza altri servizi gestiti per esportare i dati da un datastore esterno e importarli in BigQuery. Ad esempio, puoi caricare i dati dalle esportazioni di Firestore.

Quando si sceglie un metodo di caricamento batch, la maggior parte dei pattern basati su file deve utilizzare un job di caricamento o un'istruzione SQL LOAD DATA per caricare i dati in batch. In genere, altri servizi dovrebbero utilizzare BigQuery Data Transfer Service o esportare dati dai servizi Google.

Il caricamento in batch può essere eseguito come operazione una tantum o in base a una pianificazione ricorrente. Puoi ad esempio eseguire le seguenti operazioni:

  • Puoi eseguire trasferimenti di BigQuery Data Transfer Service in base a una pianificazione.
  • Puoi utilizzare un servizio di orchestrazione come Cloud Composer per pianificare i job di caricamento.
  • Puoi utilizzare un cron job per caricare i dati in base a una pianificazione.

Flussi di dati

Con l'invio di flussi di dati, invii continuamente batch di dati più piccoli in tempo reale, in modo che i dati siano disponibili per le query non appena arrivano. Le opzioni per i flussi di dati in BigQuery includono:

  • API Storage Write. L'API Storage Write supporta l'importazione di flussi di dati ad alta velocità effettiva con semantica di distribuzione "exactly-once".
  • Dataflow. Utilizza Dataflow con l'SDK Apache Beam per configurare una pipeline in modalità flusso che scrive in BigQuery. Per ulteriori informazioni, consulta il connettore BigQuery I/O nella documentazione di Apache Beam e il tutorial sul flusso da Pub/Sub a BigQuery.
  • Datastream. Datastream utilizza la funzionalità Change Data Capture di BigQuery e l'API Storage Write per replicare gli aggiornamenti di dati e schema dai database operativi direttamente in BigQuery. Segui questa guida rapida per un esempio di replica da un database Cloud SQL per PostgreSQL in BigQuery.
  • Connettore BigQuery per SAP. BigQuery Connector for SAP consente la replica quasi in tempo reale dei dati SAP direttamente in BigQuery. Per ulteriori informazioni, consulta la guida alla pianificazione del connettore BigQuery per SAP.
  • Pub/Sub. Pub/Sub è un servizio di messaggistica che puoi utilizzare per coordinare le pipeline di analisi dei flussi di dati e di integrazione dei dati. Puoi utilizzare le sottoscrizioni BigQuery per scrivere messaggi direttamente in una tabella BigQuery esistente.

Dati generati

Puoi utilizzare SQL per generare dati e archiviare i risultati in BigQuery. Le opzioni per la generazione dei dati includono:

  • Utilizza le istruzioni Data Manipulation Language (DML) per eseguire inserti collettivi in una tabella esistente o archiviare i risultati delle query in una nuova tabella.

  • Utilizza un'istruzione CREATE TABLE ... AS per creare una nuova tabella dal risultato di una query.

  • Esegui una query e salva i risultati in una tabella. Puoi aggiungere i risultati a una tabella esistente o scrivere in una nuova tabella. Per ulteriori informazioni, consulta Scrittura dei risultati delle query.

Applicazioni di terze parti

Alcune applicazioni e servizi di terze parti forniscono connettori in grado di importare dati in BigQuery. I dettagli di come configurare e gestire la pipeline di importazione dipendono dall'applicazione. Ad esempio, per caricare i dati da origini esterne nello spazio di archiviazione di BigQuery, puoi utilizzare Informatica Data Loader o Fivetran Data Pipelines. Per ulteriori informazioni, consulta Caricare i dati utilizzando un'applicazione di terze parti.

Scelta di un metodo di importazione dati

Di seguito sono riportate alcune considerazioni da tenere presenti quando scegli un metodo di importazione dati.

Origine dati. L'origine dei dati o il formato dei dati può determinare se il caricamento batch o il trasferimento di flussi di dati sono più semplici da implementare e gestire. Considera i seguenti punti:

  • Se BigQuery Data Transfer Service supporta l'origine dati, il trasferimento dei dati direttamente in BigQuery potrebbe essere la soluzione più semplice da implementare.

  • Per i dati provenienti da origini di terze parti non supportate da BigQuery Data Transfer Service, trasformali in un formato supportato tramite il caricamento in gruppo e utilizza invece questo metodo.

  • Se i dati provengono da Spark o Hadoop, valuta l'utilizzo dei connettori BigQuery per semplificare l'importazione dati.

  • Per i file locali, considera i job di caricamento batch, soprattutto se BigQuery supporta il formato file senza richiedere un passaggio di trasformazione o pulizia dei dati.

  • Per i dati delle applicazioni, come eventi dell'applicazione o flussi di log, potrebbe essere più semplice trasmettere i dati in tempo reale, anziché implementare il caricamento in batch.

Dati in rapida evoluzione e in rapida evoluzione. Se hai bisogno di importare e analizzare i dati quasi in tempo reale, considera la possibilità di trasmettere i dati in flussi. Con i flussi di dati, i dati sono disponibili per le query non appena arriva ogni record. Evita di utilizzare istruzioni DML per inviare un numero elevato di aggiornamenti o posizionamenti di singole righe. Per i dati aggiornati di frequente, è spesso preferibile trasmettere in streaming un log delle modifiche e utilizzare una visualizzazione per ottenere i risultati più recenti. Un'altra opzione è utilizzare Cloud SQL come database di elaborazione delle transazioni online (OLTP) e utilizzare query federate per unire i dati in BigQuery.

Se i dati di origine cambiano lentamente o non hai bisogno di risultati aggiornati continuamente, valuta la possibilità di utilizzare un job di caricamento. Ad esempio, se utilizzi i dati per eseguire un report giornaliero o orario, i job di caricamento possono essere meno costosi e utilizzare meno risorse di sistema.

Un altro scenario è rappresentato dai dati che arrivano raramente o in risposta a un evento. In questo caso, puoi utilizzare Dataflow per trasmettere i dati o utilizzare Cloud Functions per chiamare l'API di inserimento di flussi in risposta a un trigger.

Affidabilità della soluzione. BigQuery ha un accordo sul livello del servizio (SLA). Tuttavia, devi anche considerare l'affidabilità della soluzione specifica che implementi. Considera i seguenti punti:

  • Con formati a caratteri generici come JSON o CSV, i dati non validi possono impedire l'intero job di caricamento. Valuta se è necessario un passaggio di pulizia dei dati prima del caricamento e valuta come rispondere agli errori. Inoltre, valuta l'utilizzo di un formato di forte tipo, come Avro, ORC o Parquet.
  • I job di caricamento periodici richiedono pianificazione, utilizzando Cloud Composer, cron o un altro strumento. Il componente di pianificazione potrebbe essere un punto di errore nella soluzione.
  • Con i flussi di dati, puoi verificare l'efficacia di ogni record e segnalare rapidamente un errore. Potresti scrivere i messaggi non riusciti in una coda di messaggi non elaborati per consentirne l'analisi e l'elaborazione successive. Per ulteriori informazioni sugli errori di inserimento di flussi di BigQuery, consulta Risoluzione dei problemi relativi all'inserimento di flussi di dati.
  • I job di flusso e caricamento sono soggetti a quote. Per informazioni su come gestire gli errori di quota, consulta Risoluzione degli errori di quota di BigQuery.
  • Le soluzioni di terze parti possono differire in configurabilità, affidabilità, garanzie di ordinamento e altri fattori, quindi tienili in considerazione prima di adottare una soluzione.

Latenza. Considera la quantità di dati che carichi e quando hai bisogno che i dati siano disponibili. I flussi di dati offrono la minore latenza di dati disponibili per l'analisi. I job di caricamento periodici hanno una latenza maggiore, perché i nuovi dati sono disponibili solo al termine di ogni job di caricamento.

Per impostazione predefinita, i job di caricamento utilizzano un pool condiviso di slot. Un job di caricamento potrebbe rimanere in attesa fino a quando gli slot non sono disponibili, soprattutto se carichi una quantità molto grande di dati. Se ciò crea tempi di attesa inaccettabili, puoi acquistare slot dedicati, anziché utilizzare il pool di slot condiviso. Per ulteriori informazioni, consulta l'introduzione alle prenotazioni.

Le prestazioni delle query per le origini dati esterne potrebbero non essere elevate come quelle per i dati archiviati in BigQuery. Se è importante ridurre al minimo la latenza delle query, ti consigliamo di caricare i dati in BigQuery.

Formato di importazione dati. Scegli un formato di importazione dati in base ai seguenti fattori:

  • Supporto degli schemi. Le esportazioni Avro, ORC, Parquet e Firestore sono formati autodescrittivi. BigQuery crea automaticamente lo schema della tabella in base ai dati di origine. Per i dati JSON e CSV, puoi fornire uno schema esplicito o utilizzare il rilevamento automatico dello schema.

  • Dati piatti o campi nidificati e ripetuti. Avro, CSV, JSON, ORC e Parquet supportano tutti i dati fissi. Le esportazioni Avro, JSON, ORC, Parquet e Firestore supportano anche i dati con campi nidificati e ripetuti. I dati nidificati e ripetuti sono utili per esprimere dati gerarchici. Anche i campi nidificati e ripetuti riducono la duplicazione dei dati durante il caricamento dei dati.

  • Nuovi elementi a riga incorporati. Quando carichi i dati da file JSON, le righe devono essere delimitate da una nuova riga. BigQuery prevede che i file JSON delimitati da nuova riga contengano un singolo record per riga.

  • Codifica. BigQuery supporta la codifica UTF-8 per dati nidificati o ripetuti e piatti. BigQuery supporta la codifica ISO-8859-1 per dati piatti solo per i file CSV.

Carica dati nidificati e ripetuti

Puoi caricare i dati in campi nidificati e ripetuti nei seguenti formati di dati:

  • Avro
  • JSON (delimitato da nuova riga)
  • ORC
  • Parquet
  • Esportazioni Datastore
  • Esportazioni di Firestore

Per informazioni sulla specifica di campi nidificati e ripetuti nello schema quando carichi i dati, consulta Specificare campi nidificati e ripetuti.

Carica dati da altri servizi Google

Alcuni servizi Google esportano dati in BigQuery utilizzando query, esportazioni o trasferimenti pianificati. Per ulteriori informazioni sui servizi che supportano le esportazioni in BigQuery, consulta Caricare dati dai servizi Google.

Altri servizi Google supportano le esportazioni di dati avviate da BigQuery Data Transfer Service. Per ulteriori informazioni sui servizi che supportano le esportazioni avviate da BigQuery Data Transfer Service, consulta BigQuery Data Transfer Service.

Quota

Per informazioni sulle quote, consulta le seguenti sezioni:

Alternative al caricamento dei dati

Non è necessario caricare i dati prima di eseguire query nelle seguenti situazioni:

Set di dati pubblici
I set di dati pubblici sono set di dati archiviati in BigQuery e condivisi con il pubblico. Per ulteriori informazioni, consulta Set di dati pubblici di BigQuery.
Set di dati condivisi
Puoi condividere i set di dati archiviati in BigQuery. Se qualcuno ha condiviso un set di dati con te, puoi eseguire query su quel set di dati senza caricare i dati.
Origini dati esterne
BigQuery può eseguire query su determinate forme di dati esterni, senza caricarli nello spazio di archiviazione di BigQuery. Questo approccio consente di sfruttare le capacità di analisi di BigQuery senza spostare i dati archiviati altrove. Per informazioni sui vantaggi e sulle limitazioni di questo approccio, consulta le origini dati esterne.
File di logging
Cloud Logging offre un'opzione per esportare i file di log in BigQuery. Per ulteriori informazioni, consulta Configurare e gestire i sink.

Monitoraggio dell'utilizzo dei job di caricamento

Puoi monitorare l'utilizzo dei job di caricamento nei due modi seguenti:

  • Utilizza Cloud Monitoring. Per ulteriori informazioni, consulta la pagina relativa alle metriche di BigQuery. In particolare, puoi monitorare la quantità di dati e il numero di righe caricate in una tabella specifica. Se i job di caricamento caricano dati in una tabella specifica, questo può essere un proxy per il monitoraggio dell'utilizzo dei dati di caricamento del job di caricamento.

  • Usa INFORMATION_SCHEMA.JOBS_BY_PROJECT. Puoi utilizzare la vista INFORMATION_SCHEMA.JOBS_BY_PROJECT per ottenere il numero di job di caricamento per tabella al giorno.

Esempio di caso d'uso

I seguenti esempi illustrano i metodi da utilizzare in base al tuo caso d'uso e come utilizzarli con altre soluzioni di analisi dei dati.

Trasmetti flussi di dati con l'API Storage Write

Supponiamo che esista una pipeline che elabora dati sugli eventi dai log degli endpoint. Gli eventi vengono generati continuamente e devono essere disponibili per le query in BigQuery il prima possibile. Poiché l'aggiornamento dei dati è fondamentale per questo caso d'uso, l'API Storage Write è la scelta migliore per importare i dati in BigQuery. Un'architettura consigliata per mantenere sottili questi endpoint consiste nell'inviare eventi a Pub/Sub, da dove vengono utilizzati da una pipeline Dataflow in modalità flusso che trasmette direttamente i flussi verso BigQuery.

Una delle principali preoccupazioni di affidabilità per questa architettura è come affrontare il mancato inserimento di un record in BigQuery. Se ogni record è importante e non può essere perso, i dati dovranno essere inseriti nel buffer prima di tentare l'inserimento. Nell'architettura consigliata in alto, Pub/Sub può svolgere il ruolo di buffer con le sue funzionalità di conservazione dei messaggi. La pipeline Dataflow deve essere configurata per riprovare gli inserimento di flussi di dati BigQuery con backoff esponenziale troncato. Una volta esaurita la capacità di Pub/Sub come buffer, ad esempio in caso di prolungata indisponibilità di BigQuery o di un errore di rete, i dati devono essere resi persistenti sul client e il client ha bisogno di un meccanismo per riprendere l'inserimento dei record persistenti una volta ripristinata la disponibilità. Per ulteriori informazioni su come gestire questa situazione, consulta il post del blog della guida all'affidabilità di Google Pub/Sub.

Un altro caso di insuccesso da gestire è quello di fedeltà. Un record poison è un record rifiutato da BigQuery perché non riesce a essere inserito con un errore non ripetibile o un record che non è stato inserito correttamente dopo il numero massimo di nuovi tentativi. Entrambi i tipi di record devono essere archiviati in una "coda messaggi non recapitabili" dalla pipeline Dataflow per ulteriori indagini.

Se è richiesta la semantica "exactly-once", crea un flusso di scrittura nel tipo di commit, con gli offset del record forniti dal client. Ciò consente di evitare duplicati, poiché l'operazione di scrittura viene eseguita solo se il valore di offset corrisponde all'offset di accodamento successivo. Se non viene fornito un offset, i record vengono aggiunti alla fine corrente del flusso e se si ripete un'aggiunta non riuscita, il record potrebbe essere visualizzato più di una volta nel flusso.

Se le garanzie "exactly-once" non sono richieste, la scrittura nel flusso predefinito consente una velocità effettiva superiore e non viene conteggiata ai fini del limite di quota per la creazione di flussi di scrittura.

Stima la velocità effettiva della rete e assicurati in anticipo di disporre di una quota adeguata per gestire la velocità effettiva.

Se il tuo carico di lavoro genera o elabora dati a una frequenza molto irregolare, prova a eliminare eventuali picchi di carico sul client e a trasmettere in BigQuery con una velocità effettiva costante. Questo può semplificare la pianificazione delle capacità. Se non è possibile, assicurati di avere tutto pronto per gestire gli errori 429 (risorsa esaurita) se e quando la velocità effettiva supera la quota durante brevi picchi.

Elaborazione dei dati in batch

Supponiamo che esista una pipeline di elaborazione batch notturna che deve essere completata entro una scadenza fissa. I dati devono essere disponibili entro questa scadenza per un'ulteriore elaborazione da parte di un altro processo batch al fine di generare report da inviare a un regolatore. Questo caso d'uso è comune in settori regolamentati come quello finanziario.

Il caricamento in batch dei dati con job di caricamento è l'approccio giusto per questo caso d'uso perché la latenza non è un problema se è possibile rispettare la scadenza. Assicurati che i bucket Cloud Storage soddisfino i requisiti di località per il caricamento dei dati nel set di dati BigQuery.

Il risultato di un job di caricamento BigQuery è atomico, oppure vengono inseriti tutti i record o nessuno. Come best practice, quando inserisci tutti i dati in un singolo job di caricamento, crea una nuova tabella utilizzando la disposizione WRITE_TRUNCATE della risorsa JobConfigurationLoad. Questo è importante quando si tenta di riprovare un job di caricamento non riuscito, perché il client potrebbe non essere in grado di distinguere tra i job non riusciti e l'errore causato, ad esempio, comunicando al client lo stato riuscito.

Supponendo che i dati da importare siano già stati copiati in Cloud Storage, un nuovo tentativo con backoff esponenziale è sufficiente per risolvere gli errori di importazione.

È consigliabile che un job batch notturno non raggiunga la quota predefinita di 1500 caricamenti per tabella al giorno anche se sono presenti nuovi tentativi. Quando i dati vengono caricati in modo incrementale, la quota predefinita è sufficiente per eseguire un job di caricamento ogni 5 minuti e ha una quota non consumata in media per almeno 1 tentativo per job.

Passaggi successivi