Caricamento di dati in batch

Puoi caricare i dati in BigQuery da Cloud Storage o da un file locale come operazione batch. I dati di origine possono essere in uno dei seguenti formati:

• Avro
• Valori separati da virgola (CSV)
• JSON (delimitato da nuova riga)
• ORC
• Parquet
• Esportazioni di Datastore archiviate in Cloud Storage
• Esportazioni di Firestore archiviate in Cloud Storage

Puoi anche utilizzare BigQuery Data Transfer Service per configurare i caricamenti ricorrenti da Cloud Storage in BigQuery.

Prima di iniziare

Concedi i ruoli IAM (Identity and Access Management) che forniscono agli utenti le autorizzazioni necessarie per eseguire ogni attività in questo documento e crea un set di dati per archiviare i dati.

Autorizzazioni obbligatorie

Per caricare i dati in BigQuery, devi disporre delle autorizzazioni IAM per eseguire un job di caricamento e caricare i dati nelle tabelle e nelle partizioni BigQuery. Se carichi i dati da Cloud Storage, devi disporre anche delle autorizzazioni IAM per accedere al bucket contenente i dati.

Autorizzazioni per caricare dati in BigQuery

Per caricare dati in una nuova tabella o partizione BigQuery o per accodare o sovrascrivere una tabella o partizione esistente, sono necessarie le seguenti autorizzazioni IAM:

• bigquery.tables.create
• bigquery.tables.updateData
• bigquery.tables.update
• bigquery.jobs.create

Ciascuno dei seguenti ruoli IAM predefiniti include le autorizzazioni necessarie per caricare i dati in una tabella o una partizione BigQuery:

• roles/bigquery.dataEditor
• roles/bigquery.dataOwner
• roles/bigquery.admin (include l'autorizzazione bigquery.jobs.create)
• bigquery.user (include l'autorizzazione bigquery.jobs.create)
• bigquery.jobUser (include l'autorizzazione bigquery.jobs.create)

Inoltre, se disponi dell'autorizzazione bigquery.datasets.create, puoi creare e aggiornare le tabelle utilizzando un job di caricamento nei set di dati che crei.

Per ulteriori informazioni sui ruoli e sulle autorizzazioni IAM in BigQuery, consulta Ruoli e autorizzazioni predefiniti.

Autorizzazioni per caricare i dati da Cloud Storage

Per ottenere le autorizzazioni necessarie per caricare i dati da un bucket Cloud Storage, chiedi all'amministratore di concederti il ruolo IAM Amministratore archiviazione (roles/storage.admin) nel bucket. Per saperne di più sulla concessione dei ruoli, consulta Gestire l'accesso a progetti, cartelle e organizzazioni.

Questo ruolo predefinito contiene le autorizzazioni necessarie per caricare i dati da un bucket Cloud Storage. Per visualizzare le autorizzazioni esatte richieste, espandi la sezione Autorizzazioni richieste:

Potresti anche ottenere queste autorizzazioni con ruoli personalizzati o altri ruoli predefiniti.

Crea un set di dati

Crea un set di dati BigQuery per archiviare i tuoi dati.

Caricamento dei dati da Cloud Storage

BigQuery supporta il caricamento dei dati da una delle seguenti classi di archiviazione Cloud Storage:

• Standard
• Nearline
• Coldline
• Archivia

Per scoprire come caricare i dati in BigQuery, consulta la pagina relativa al formato dei dati:

• CSV
• JSON
• Avro
• Parquet
• ORC
• Esportazioni di Datastore
• Esportazioni di Firestore

Per scoprire come configurare un caricamento ricorrente da Cloud Storage in BigQuery, consulta Trasferimenti di Cloud Storage.

Considerazioni sulla località

Quando carichi i dati da Cloud Storage, questi devono essere colocalizzati con il set di dati BigQuery.

• Puoi caricare i dati da un bucket Cloud Storage in qualsiasi località se il set di dati BigQuery si trova nella regione multipla US.

• Bucket multiregione: se il bucket Cloud Storage da cui vuoi eseguire il caricamento si trova in un bucket multiregione, il tuo set di dati BigQuery può trovarsi nello stesso bucket multiregione o in qualsiasi singola regione inclusa nello stesso bucket multiregione. Ad esempio, se il bucket Cloud Storage si trova nella regione EU, il set di dati BigQuery può trovarsi nella regione EU multiregionale o in qualsiasi singola regione della EU.

• Bucket a due regioni: se il bucket Cloud Storage da cui vuoi eseguire il caricamento si trova in un bucket a due regioni, il tuo set di dati BigQuery può trovarsi nelle regioni incluse nel bucket a due regioni o in un set di dati a più regioni che include la doppia regione. Ad esempio, se il bucket Cloud Storage si trova nella regione EUR4, il set di dati BigQuery può trovarsi nella regione singola della Finlandia (europe-north1), nella regione singola dei Paesi Bassi (europe-west4) o nella regione multipla EU.

• Bucket a singola regione: se il bucket Cloud Storage da cui vuoi eseguire il caricamento si trova in una singola regione, il set di dati BigQuery può trovarsi nella stessa regione o nella regione multipla che include la regione singola. Ad esempio, se il bucket Cloud Storage si trova nella regione Finlandia (europe-north1), il set di dati BigQuery può trovarsi nella regione Finlandia o nella regione multipla EU.

• Un'eccezione è che se il set di dati BigQuery si trova nella regione asia-northeast1, il bucket Cloud Storage può trovarsi nella regione multipla EU.

Per ulteriori informazioni sulle località di Cloud Storage, consulta Località dei bucket nella documentazione di Cloud Storage.

Non puoi modificare la posizione di un set di dati dopo averlo creato, ma puoi creare una copia del set di dati o spostarlo manualmente. Per ulteriori informazioni, vedi:

• Copiare i set di dati
• Spostare un set di dati

Recupero dell'URI Cloud Storage

Per caricare i dati da un'origine dati Cloud Storage, devi fornire l'URI Cloud Storage.

Il percorso della risorsa Cloud Storage contiene il nome del bucket e dell'oggetto (nome file). Ad esempio, se il bucket Cloud Storage si chiama mybucket e il file di dati si chiama myfile.csv, il percorso della risorsa sarà gs://mybucket/myfile.csv.

BigQuery non supporta i percorsi delle risorse Cloud Storage che includono più barre consecutive dopo la barra doppia iniziale. I nomi degli oggetti Cloud Storage possono contenere più barre consecutive ("/"). Tuttavia, BigQuery converte più barre consecutive in una singola barra. Ad esempio, il seguente percorso della risorsa, sebbene valido in Cloud Storage, non funziona in BigQuery: gs://bucket/my//object//name.

Per recuperare il percorso della risorsa Cloud Storage:

1. Apri la console Cloud Storage.

   Console Cloud Storage

2. Vai alla posizione dell'oggetto (file) che contiene i dati di origine.

3. Fai clic sul nome dell'oggetto.

   Viene visualizzata la pagina Dettagli oggetto.

4. Copia il valore fornito nel campo URI gsutil, che inizia congs://.

Per le esportazioni di Google Datastore, è possibile specificare un solo URI, che deve terminare con .backup_info o .export_metadata.

Supporto dei caratteri jolly per gli URI Cloud Storage

Se i dati sono suddivisi in più file, puoi utilizzare un carattere jolly asterisco (*) per selezionarne più di uno. L'utilizzo del carattere jolly asterisco deve rispettare queste regole:

• L'asterisco può essere visualizzato all'interno del nome dell'oggetto o alla fine del nome.
• L'utilizzo di più asterischi non è supportato. Ad esempio, il percorsogs://mybucket/fed-*/temp/*.csv non è valido.
• L'utilizzo di un asterisco con il nome del bucket non è supportato.

Esempi:

• L'esempio seguente mostra come selezionare tutti i file di tutte le cartelle che iniziano con il prefisso gs://mybucket/fed-samples/fed-sample:

  gs://mybucket/fed-samples/fed-sample*
  

• L'esempio seguente mostra come selezionare solo i file con estensione .csv nella cartella denominata fed-samples e in eventuali sottocartelle di fed-samples:

  gs://mybucket/fed-samples/*.csv
  

• L'esempio seguente mostra come selezionare i file con un pattern di denominazione difed-sample*.csv nella cartella denominata fed-samples. Questo esempio non seleziona i file nelle sottocartelle di fed-samples.

  gs://mybucket/fed-samples/fed-sample*.csv
  

Quando utilizzi lo strumento a riga di comando bq, potresti dover eseguire la fuga dall'asterisco su alcune piattaforme.

Non puoi utilizzare un asterisco jolly quando carichi i dati di esportazione di Datastore o Firestore da Cloud Storage.

Limitazioni

Quando carichi i dati in BigQuery da un bucket Cloud Storage, devi rispettare le seguenti limitazioni:

• Se la posizione del set di dati è impostata su un valore diverso dall'area geografica multipla US, il bucket Cloud Storage deve trovarsi nella stessa regione o essere contenuto nella stessa area geografica multipla del set di dati.
• BigQuery non garantisce la coerenza dei dati per le origini dati esterne. Le modifiche ai dati sottostanti durante l'esecuzione di una query possono comportare un comportamento imprevisto.
• BigQuery non supporta il controllo delle versioni degli oggetti Cloud Storage. Se includi un numero di generazione nell'URI Cloud Storage, il job di caricamento non va a buon fine.

A seconda del formato dei dati di origine Cloud Storage, potrebbero esserci limitazioni aggiuntive. Per ulteriori informazioni, vedi:

• Limitazioni dei file CSV
• Limitazioni di JSON
• Limitazioni dell'esportazione del datastore
• Limitazioni dell'esportazione di Firestore
• Limitazioni relative ai dati nidificati e ripetuti

Caricamento dei dati da file locali

Puoi caricare i dati da un'origine dati leggibile (ad esempio il tuo computer locale) utilizzando una delle seguenti opzioni:

• Nella console Google Cloud
• Il comando bq load dello strumento a riga di comando bq
• L'API
• Le librerie client

Quando carichi i dati utilizzando la console Google Cloud o lo strumento a riga di comando bq, viene creato automaticamente un job di caricamento.

Per caricare i dati da un'origine dati locale:

bq --location=LOCATION load \
--source_format=FORMAT \
PROJECT_ID:DATASET.TABLE \
PATH_TO_SOURCE \
SCHEMA

    bq load \
    --source_format=NEWLINE_DELIMITED_JSON \
    mydataset.mytable \
    ./mydata.json \
    ./myschema.json

    bq load \
    --source_format=CSV \
    myotherproject:mydataset.mytable \
    ./mydata.csv \
    qtr:STRING,sales:FLOAT,year:STRING

    bq load \
    --autodetect \
    --source_format=CSV \
    mydataset.mytable \
    ./mydata.csv

using Google.Cloud.BigQuery.V2;
using System;
using System.IO;

public class BigQueryLoadFromFile
{
    public void LoadFromFile(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id",
        string tableId = "your_table_id",
        string filePath = "path/to/file.csv"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        // Create job configuration
        var uploadCsvOptions = new UploadCsvOptions()
        {
            SkipLeadingRows = 1,  // Skips the file headers
            Autodetect = true
        };
        using (FileStream stream = File.Open(filePath, FileMode.Open))
        {
            // Create and run job
            // Note that there are methods available for formats other than CSV
            BigQueryJob job = client.UploadCsv(
                datasetId, tableId, null, stream, uploadCsvOptions);
            job = job.PollUntilCompleted().ThrowOnAnyError();  // Waits for the job to complete.

            // Display the number of rows uploaded
            BigQueryTable table = client.GetTable(datasetId, tableId);
            Console.WriteLine(
                $"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}");
        }
    }
}

import (
	"context"
	"fmt"
	"os"

	"cloud.google.com/go/bigquery"
)

// importCSVFromFile demonstrates loading data into a BigQuery table using a file on the local filesystem.
func importCSVFromFile(projectID, datasetID, tableID, filename string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	f, err := os.Open(filename)
	if err != nil {
		return err
	}
	source := bigquery.NewReaderSource(f)
	source.AutoDetect = true   // Allow BigQuery to determine schema.
	source.SkipLeadingRows = 1 // CSV has a single header line.

	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(source)

	job, err := loader.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	return nil
}

TableId tableId = TableId.of(datasetName, tableName);
WriteChannelConfiguration writeChannelConfiguration =
    WriteChannelConfiguration.newBuilder(tableId).setFormatOptions(FormatOptions.csv()).build();
// The location must be specified; other fields can be auto-detected.
JobId jobId = JobId.newBuilder().setLocation(location).build();
TableDataWriteChannel writer = bigquery.writer(jobId, writeChannelConfiguration);
// Write data to writer
try (OutputStream stream = Channels.newOutputStream(writer)) {
  Files.copy(csvPath, stream);
}
// Get load job
Job job = writer.getJob();
job = job.waitFor();
LoadStatistics stats = job.getStatistics();
return stats.getOutputRows();

// Imports the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function loadLocalFile() {
  // Imports a local file into a table.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const filename = '/path/to/file.csv';
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  // Load data from a local file into the table
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .load(filename);

  console.log(`Job ${job.id} completed.`);

  // Check the job's status for errors
  const errors = job.status.errors;
  if (errors && errors.length > 0) {
    throw errors;
  }
}

use Google\Cloud\BigQuery\BigQueryClient;
use Google\Cloud\Core\ExponentialBackoff;

/** Uncomment and populate these variables in your code */
// $projectId  = 'The Google project ID';
// $datasetId  = 'The BigQuery dataset ID';
// $tableId    = 'The BigQuery table ID';
// $source     = 'The path to the CSV source file to import';

// instantiate the bigquery table service
$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$table = $dataset->table($tableId);
// create the import job
$loadConfig = $table->load(fopen($source, 'r'))->sourceFormat('CSV');

$job = $table->runJob($loadConfig);
// poll the job until it is complete
$backoff = new ExponentialBackoff(10);
$backoff->execute(function () use ($job) {
    printf('Waiting for job to complete' . PHP_EOL);
    $job->reload();
    if (!$job->isComplete()) {
        throw new Exception('Job has not yet completed', 500);
    }
});
// check if the job has errors
if (isset($job->info()['status']['errorResult'])) {
    $error = $job->info()['status']['errorResult']['message'];
    printf('Error running job: %s' . PHP_EOL, $error);
} else {
    print('Data imported successfully' . PHP_EOL);
}

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
# table_id = "your-project.your_dataset.your_table_name"

job_config = bigquery.LoadJobConfig(
    source_format=bigquery.SourceFormat.CSV, skip_leading_rows=1, autodetect=True,
)

with open(file_path, "rb") as source_file:
    job = client.load_table_from_file(source_file, table_id, job_config=job_config)

job.result()  # Waits for the job to complete.

table = client.get_table(table_id)  # Make an API request.
print(
    "Loaded {} rows and {} columns to {}".format(
        table.num_rows, len(table.schema), table_id
    )
)

require "google/cloud/bigquery"

def load_from_file dataset_id = "your_dataset_id",
                   file_path  = "path/to/file.csv"
  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id
  table_id = "new_table_id"

  # Infer the config.location based on the location of the referenced dataset.
  load_job = dataset.load_job table_id, file_path do |config|
    config.skip_leading = 1
    config.autodetect   = true
  end
  load_job.wait_until_done! # Waits for table load to complete.

  table = dataset.table table_id
  puts "Loaded #{table.rows_count} rows into #{table.id}"
end

Limitazioni

Il caricamento dei dati da un'origine dati locale è soggetto alle seguenti limitazioni:

• I caratteri jolly e gli elenchi separati da virgole non sono supportati quando carichi i file da un'origine dati locale. I file devono essere caricati singolarmente.
• Quando utilizzi la console Google Cloud, i file caricati da un'origine dati locale non possono superare i 100 MB. Per i file di dimensioni maggiori, carica il file da Cloud Storage.
• Per impostazione predefinita, i job di caricamento utilizzano un pool di slot condiviso. BigQuery non garantisce la capacità disponibile di questo pool condiviso o la velocità effettiva visualizzata. In alternativa, puoi acquistare slot dedicati per l'esecuzione dei job di caricamento. Per ulteriori informazioni, consulta Prezzi dell'importazione dati.

Caricamento di dati compressi e non compressi

Per i formati Avro, Parquet e ORC, BigQuery supporta il caricamento di file in cui i dati del file sono stati compressi utilizzando un codec supportato. Tuttavia, BigQuery non supporta il caricamento di file in questi formati che sono stati compressi, ad esempio utilizzando l'utilità gzip.

Il formato binario Avro è il formato preferito per il caricamento sia dei dati compressi sia di quelli non compressi. I dati Avro sono più rapidi da caricare perché possono essere letti in parallelo, anche quando i blocchi di dati sono compressi. Per un elenco dei codec di compressione supportati, consulta Compressione Avro.

Anche il formato binario Parquet è una buona scelta perché la codifica per colonna efficiente di Parquet in genere genera un rapporto di compressione migliore e file più piccoli. I file Parquet sfruttano anche tecniche di compressione che consentono di caricare i file in parallelo. Per un elenco dei codec di compressione supportati, consulta Compressione Parquet.

Il formato binario ORC offre vantaggi simili a quelli del formato Parquet. I dati nei file ORC vengono caricati rapidamente perché le strisce di dati possono essere lette in parallelo. Le righe di ogni banda di dati vengono caricate in sequenza. Per ottimizzare il tempo di caricamento, utilizza una dimensione della striscia di dati pari o inferiore a circa 256 MB. Per un elenco dei codec di compressione supportati, consulta Compressione ORC.

Per altri formati di dati come CSV e JSON, BigQuery può caricare i file non compressi molto più velocemente dei file compressi perché possono essere letti in parallelo. Poiché i file non compressi sono più grandi, il loro utilizzo può comportare limitazioni della larghezza di banda e costi più elevati di Cloud Storage per i dati sottoposti a gestione temporanea in Cloud Storage prima di essere caricati in BigQuery. Tieni presente che l'ordine delle righe non è garantito per i file compressi o non compressi. È importante valutare questi compromessi in base al caso d'uso.

In generale, se la larghezza di banda è limitata, comprimere i file CSV e JSON utilizzandogzip prima di caricarli su Cloud Storage. gzip è l'unico tipo di compressione dei file supportato per i file CSV e JSON durante il caricamento dei dati in BigQuery. Se la velocità di caricamento è importante per la tua app e hai molta larghezza di banda per caricare i dati, lascia i file non compressi.

Aggiunta o sovrascrittura di una tabella

Puoi caricare dati aggiuntivi in una tabella dai file di origine o aggiungendo i risultati delle query. Se lo schema dei dati non corrisponde a quello della tabella o della partizione di destinazione, puoi aggiornarlo quando lo aggiungi o lo sovrascrivi.

Se aggiorni lo schema quando aggiungi i dati, BigQuery ti consente di:

• Aggiungi nuovi campi
• Rilassa i campi REQUIRED in NULLABLE

Se sovrascrivi una tabella, lo schema viene sempre sovrascritto. Gli aggiornamenti dello schema non sono limitati quando sovrascrivi una tabella.

Nella console Google Cloud, utilizza l'opzione Preferenza di scrittura per specificare l'azione da eseguire quando carichi i dati da un file di origine o da un risultato di query. Lo strumento a riga di comando bq e l'API includono le seguenti opzioni:

Criteri per le quote

Per informazioni sul criterio di quota per il caricamento batch dei dati, consulta Carica job nella pagina Quote e limiti.

Visualizzare l'utilizzo attuale delle quote

Puoi visualizzare l'utilizzo corrente dei job di query, caricamento, estrazione o copia eseguendo una query INFORMATION_SCHEMA per visualizzare i metadati dei job eseguiti in un periodo di tempo specificato. Puoi confrontare l'utilizzo corrente con il limite di quota per determinare l'utilizzo della quota per un determinato tipo di job. La seguente query di esempio utilizza la vista INFORMATION_SCHEMA.JOBS per elencare il numero di job di query, caricamento, estrazione e copia per progetto:

SELECT
  sum(case  when job_type="QUERY" then 1 else 0 end) as QRY_CNT,
  sum(case  when job_type="LOAD" then 1 else 0 end) as LOAD_CNT,
  sum(case  when job_type="EXTRACT" then 1 else 0 end) as EXT_CNT,
  sum(case  when job_type="COPY" then 1 else 0 end) as CPY_CNT
FROM `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE date(creation_time)= CURRENT_DATE()

Prezzi

Il caricamento batch dei dati in BigQuery non comporta alcun costo. Per ulteriori informazioni, consulta la pagina relativa ai prezzi dell'importazione dati di BigQuery.

Caso d'uso di esempio

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

Il caricamento collettivo dei dati con i job di caricamento è l'approccio giusto per questo caso d'uso perché la latenza non è un problema, a condizione che la scadenza possa essere rispettata. Assicurati che i bucket Cloud Storage soddisfino i requisiti di posizione per il caricamento dei dati nel set di dati BigQuery.

Il risultato di un job di caricamento BigQuery è atomico: 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 ritenta un job di caricamento non riuscito, in quanto il client potrebbe non essere in grado di distinguere tra i job non riusciti e l'errore causato, ad esempio, dalla comunicazione dello stato di successo al client.

Se i dati da importare sono già stati copiati correttamente in Cloud Storage, riprovare con il backoff esponenziale è sufficiente per risolvere i problemi di importazione.

È consigliabile che un job batch notturno non raggiunga la quota predefinita di 1500 caricamenti per tabella al giorno anche con i tentativi di nuovo caricamento. Quando carichi i dati in modo incrementale, la quota predefinita è sufficiente per eseguire un job di caricamento ogni 5 minuti e avere una quota non utilizzata per almeno 1 nuovo tentativo per job in media.