Specifica di uno schema

BigQuery consente di specificare lo schema di una tabella quando carichi dati in una tabella e quando ne crei una vuota. In alternativa, puoi utilizza il rilevamento automatico dello schema per formati di dati supportati.

Quando carichi file di esportazione Avro, Parquet, ORC, Firestore o Datastore, lo schema viene recuperato automaticamente dai dati di origine autodescrittivi.

Puoi specificare lo schema di una tabella nei seguenti modi:

• Utilizza la console Google Cloud.
• Utilizza l'istruzione SQL CREATE TABLE.
• In linea utilizzando lo strumento a riga di comando bq.
• Crea un file di schema in formato JSON.
• Chiama il metodo jobs.insert e configura la proprietà schema nella configurazione del job load.
• Chiama il metodo tables.insert e configura lo schema nella risorsa tabella utilizzando la proprietà schema.

Dopo aver caricato i dati o creato una tabella vuota, puoi: modificare la definizione dello schema della tabella.

Componenti dello schema

Quando specifichi uno schema di tabella, devi fornire il nome e il tipo di dati di ogni colonna. Puoi anche fornire la descrizione, la modalità e il valore predefinito di una colonna.

Nomi delle colonne

Il nome di una colonna può contenere lettere (a-z, A-Z), numeri (0-9) o trattini bassi (_) e deve iniziare con una lettera o un trattino basso. Se utilizzi la colonna flessibile , BigQuery supporta l'inizio del nome della colonna con un numero. Presta attenzione quando inizi colonne con un numero, poiché utilizzi l'impostazione i nomi delle colonne con l'API BigQuery Storage Read o L'API BigQuery StorageWrite richiede una gestione speciale. Per ulteriori informazioni sul supporto dei nomi delle colonne flessibili, consulta Nomi delle colonne flessibili.

I nomi delle colonne hanno una lunghezza massima di 300 caratteri. I nomi delle colonne non possono contenere nessuno dei seguenti prefissi:

• _TABLE_
• _FILE_
• _PARTITION
• _ROW_TIMESTAMP
• __ROOT__
• _COLIDENTIFIER

Non sono consentiti nomi di colonna duplicati anche se le differenze sono diverse. Ad esempio, un una colonna denominata Column1 è considerata identica a una colonna denominata column1. A per ulteriori informazioni sulle regole di denominazione delle colonne, consulta Colonna nomi utente nel Riferimento GoogleSQL.

Se il nome di una tabella (ad es. test) corrisponde a uno dei nomi delle sue colonne (ad es. test), l'espressione SELECT interpreta la colonna test come un STRUCT contenente tutte le altre colonne della tabella. Per evitare questa collisione, utilizza uno dei seguenti metodi:

• Evita di utilizzare lo stesso nome per una tabella e le sue colonne.

• Assegna alla tabella un alias diverso. Ad esempio, la seguente query assegna un alias di tabella t per la tabella project1.dataset.test:

  SELECT test FROM project1.dataset.test AS t;
  

• Includi il nome della tabella quando fai riferimento a una colonna. Ad esempio:

  SELECT test.test FROM project1.dataset.test;
  

Nomi delle colonne flessibili

Hai maggiore flessibilità nel assegnare un nome alle colonne, incluso l'accesso esteso ai caratteri in lingue diverse dall'inglese nonché a simboli aggiuntivi.

I nomi delle colonne flessibili supportano i seguenti caratteri:

• Qualsiasi lettera in qualsiasi lingua, come rappresentata dall'espressione regolare Unicode \p{L}.
• Qualsiasi carattere numerico in qualsiasi lingua, come rappresentato dall'espressione regolare Unicode \p{N}.
• Qualsiasi carattere di punteggiatura del connettore, inclusi gli underscore, come rappresentato dall'espressione regolare Unicode \p{Pc}.
• Un trattino o un a capo come rappresentato dall'espressione regolare Unicode \p{Pd}.
• Qualsiasi marchio destinato ad accompagnare un altro carattere, come rappresentato dal Espressione regolare Unicode \p{M} Ad esempio, accenti, umlaut o riquadri di delimitazione.
• I seguenti caratteri speciali:
  • Una e commerciale (&) rappresentata dalla regola Unicode dell'espressione \u0026.
  • Un segno di percentuale (%) come rappresentato dall'espressione regolare Unicode \u0025.
  • Un segno di uguale (=) rappresentato dall'espressione regolare Unicode \u003D.
  • Un segno più (+) rappresentato dalla regola Unicode dell'espressione \u002B.
  • I due punti (:) come rappresentati dalla regola Unicode dell'espressione \u003A.
  • Un apostrofo (') come rappresentato dalla regola Unicode dell'espressione \u0027.
  • Un segno di minore (<) rappresentato dall'espressione regolare Unicode \u003C.
  • Un segno di maggiore (>) come rappresentato dall'espressione regolare Unicode \u003E.
  • Un segno di numero (#) come rappresentato dall'espressione regolare Unicode \u0023.
  • Una linea verticale (|) rappresentata dall'espressione regolare Unicode \u007c.
  • Spazio vuoto.

I nomi delle colonne flessibili non supportano i seguenti caratteri speciali:

• Un punto esclamativo (!) come rappresentato dal simbolo Unicode dell'espressione \u0021.
• Una virgola (") rappresentata dall'espressione regolare Unicode \u0022.
• Un simbolo del dollaro ($) rappresentato dalla regola Unicode dell'espressione \u0024.
• Una parentesi aperta (() rappresentata dalla regolare Unicode dell'espressione \u0028.
• Una parentesi chiusa ()) rappresentata dalla regolare Unicode dell'espressione \u0029.
• Un asterisco (*) come rappresentato dalla regola Unicode dell'espressione \u002A.
• Una virgola (,) come rappresentata dal valore regolare Unicode dell'espressione \u002C.
• Un punto (.) come rappresentato dalla regola Unicode dell'espressione \u002E.
• Una barra (/) rappresentata dalla barra Unicode regolare dell'espressione \u002F.
• Un punto e virgola (;) rappresentato dall'espressione regolare Unicode \u003B.
• Un punto interrogativo (?) come rappresentato dall'espressione regolare Unicode \u003F.
• Un segno di at (@) come rappresentato dall'espressione regolare Unicode \u0040.
• Una parentesi quadra aperta ([) rappresentata dalla barra Unicode regolare dell'espressione \u005B.
• Una barra rovesciata (\) rappresentata dalla barra Unicode regolare dell'espressione \u005C.
• Una parentesi quadra chiusa (]) come rappresentata dalla regolare Unicode dell'espressione \u005D.
• Un accento circonflesso (^) come rappresentato dall'espressione regolare Unicode \u005E.
• Un accento grave (`) rappresentato dalla regola Unicode dell'espressione \u0060.
• Una parentesi graffa aperta {{) rappresentata dall'espressione regolare Unicode \u007B.
• Una parentesi graffa chiusa (}) rappresentata dalla regola Unicode dell'espressione \u007D.
• Una tilde (~) rappresentata dall'espressione regolare Unicode \u007E.

Per ulteriori linee guida, consulta Nomi delle colonne.

I caratteri delle colonne espansi sono supportati sia dall'API BigQuery Storage Read e l'API BigQuery StorageWrite. Per utilizzare l'elenco espanso dei caratteri Unicode con l'API BigQuery Storage di lettura, devi impostare un flag. Puoi utilizzare l'attributo displayName per recuperare il nome della colonna. Nell'esempio che segue mostra come impostare un flag con il client Python:

from google.cloud.bigquery_storage import types
requested_session = types.ReadSession()

#set avro serialization options for flexible column.
options = types.AvroSerializationOptions()
options.enable_display_name_attribute = True
requested_session.read_options.avro_serialization_options = options

Per usare l'elenco espanso di caratteri Unicode con l'API BigQuery StorageWrite, devi fornire lo schema con la notazione column_name, a meno che non utilizzi l'oggetto writer JsonStreamWriter. L'esempio seguente mostra come indica lo schema:

syntax = "proto2";
package mypackage;
// Source protos located in github.com/googleapis/googleapis
import "google/cloud/bigquery/storage/v1/annotations.proto";

message FlexibleSchema {
  optional string item_name_column = 1
  [(.google.cloud.bigquery.storage.v1.column_name) = "name-列"];
  optional string item_description_column = 2
  [(.google.cloud.bigquery.storage.v1.column_name) = "description-列"];
}

In questo esempio, item_name_column e item_description_column sono nomi segnaposto che devono essere conformi alla convenzione di denominazione del buffer del protocollo. Tieni presente che le annotazioni column_name hanno sempre la precedenza sulle i nomi dei segnaposto.

Limitazioni

I nomi di colonna flessibili non sono supportati con tabelle esterne.

Descrizioni delle colonne

Ogni colonna può includere una descrizione facoltativa. La descrizione è una stringa con una lunghezza massima di 1024 caratteri.

Valori predefiniti

Il valore predefinito di una colonna deve essere un letterale o uno dei le seguenti funzioni:

• CURRENT_DATE
• CURRENT_DATETIME
• CURRENT_TIME
• CURRENT_TIMESTAMP
• GENERATE_UUID
• RAND
• SESSION_USER
• ST_GEOGPOINT

Tipi di dati di GoogleSQL

GoogleSQL ti consente di specificare i seguenti tipi di dati nel tuo schema. Il tipo di dati è obbligatorio.

Nome	Tipo di dati	Descrizione
Numero intero	INT64	Valori numerici senza componenti frazionari
Punto floating	FLOAT64	Approssimare valori numerici con componenti frazionari
Numerico	NUMERIC	Valori numerici esatti con componenti frazionari
BigNumeric	BIGNUMERIC	Valori numerici esatti con componenti frazionari
Booleano	BOOL	TRUE o FALSE (non è sensibile alle maiuscole)
Stringa	STRING	Dati di caratteri (Unicode) di lunghezza variabile
Byte	BYTES	Dati binari di lunghezza variabile
Data	DATE	Una data di calendario logica
Data/ora	DATETIME	Un anno, mese, giorno, ora, minuto, secondo e sottosecondo
Ora	TIME	Un'ora, indipendente da una data specifica
Timestamp	TIMESTAMP	Un punto di tempo assoluto, con precisione in microsecondi
Struct (Record)	STRUCT	Contenitore di campi ordinati ciascuno con un tipo (obbligatorio) e un nome (facoltativo)
Geografia	GEOGRAPHY	Un punto sulla superficie terrestre (un insieme di punti, linee e poligoni su WGS84 sferoide di riferimento, con bordi geodetici)
JSON	JSON	Rappresenta JSON, un formato di interscambio dati leggero
INTERVALLO	RANGE	Un intervallo di valori DATE, DATETIME o TIMESTAMP

Per saperne di più sui tipi di dati in GoogleSQL, consulta Tipi di dati GoogleSQL.

Puoi anche dichiarare un tipo di array quando esegui query sui dati. Per ulteriori informazioni, consulta Utilizzare gli array.

Modalità

BigQuery supporta le seguenti modalità per le colonne. La modalità è facoltativo. Se la modalità non è specificata, il valore predefinito della colonna è NULLABLE.

Modalità	Descrizione
Ammette valori Null	La colonna consente valori NULL (valore predefinito)
Obbligatorio	I valori NULL non sono consentiti
Ripetuto	La colonna contiene un array di valori del tipo specificato

Per ulteriori informazioni sulle modalità, consulta mode in TableFieldSchema.

Modalità di arrotondamento

Quando una colonna è di tipo NUMERIC o BIGNUMERIC, puoi impostare l'opzione della colonna rounding_mode, che determina il modo in cui i valori in quella colonna vengono arrotondati quando vengono scritti nella tabella. Puoi impostare l'opzione rounding_mode su una colonna di primo livello o in una STRUCT . Sono supportate le seguenti modalità di arrotondamento:

• "ROUND_HALF_AWAY_FROM_ZERO": questa modalità (predefinita) arrotondamento a metà casi lontano da zero.
• "ROUND_HALF_EVEN": questa modalità arrotonda i casi a metà strada verso il numero pari più vicino numero.

Non puoi impostare l'opzione rounding_mode per una colonna di tipo diverso da NUMERIC o BIGNUMERIC. Per scoprire di più su questi tipi, consulta: tipi decimali.

L'esempio seguente crea una tabella e inserisce valori arrotondati in base alla modalità di arrotondamento della colonna:

CREATE TABLE mydataset.mytable (
  x NUMERIC(5,2) OPTIONS (rounding_mode='ROUND_HALF_EVEN'),
  y NUMERIC(5,2) OPTIONS (rounding_mode='ROUND_HALF_AWAY_FROM_ZERO')
);
INSERT mydataset.mytable (x, y)
VALUES (NUMERIC "1.025", NUMERIC "1.025"),
       (NUMERIC "1.0251", NUMERIC "1.0251"),
       (NUMERIC "1.035", NUMERIC "1.035"),
       (NUMERIC "-1.025", NUMERIC "-1.025");

La tabella mytable ha il seguente aspetto:

+-------+-------+
| x     | y     |
+-------+-------+
| 1.02  | 1.03  |
| 1.03  | 1.03  |
| 1.04  | 1.04  |
| -1.02 | -1.03 |
+-------+-------+

Per ulteriori informazioni, consulta roundingMode in TableFieldSchema.

Specifica gli schemi

Quando carichi i dati o crei una tabella vuota, puoi specificare utilizzando la console Google Cloud o lo strumento a riga di comando bq. Specificare un lo schema è supportato quando carichi file CSV e JSON (delimitato da nuova riga) . Quando carichi dati di esportazione Avro, Parquet, ORC, Firestore o Datastore, lo schema viene recuperato automaticamente dai dati di origine autodescrittivi.

Per specificare uno schema di tabella:

Console

Nella console Google Cloud, puoi specificare uno schema utilizzando l'opzione Aggiungi campo o Modifica come testo.

1. Nella console Google Cloud, apri la pagina BigQuery.

   Vai a BigQuery

2. Nel riquadro Spazio di esplorazione, espandi il progetto e seleziona un set di dati.

3. Espandi Azioni e fai clic su Apri.

4. Nel riquadro dei dettagli, fai clic su Crea tabella. .

5. Nella sezione Origine della pagina Crea tabella, seleziona Tabella vuota.

6. Nella sezione Destinazione della pagina Crea tabella:

   • Per Nome set di dati, scegli il set di dati appropriato

     

   • Nel campo Nome tabella, inserisci il nome della tabella che stai è in fase di creazione.

   • Verifica che l'opzione Tipo di tabella sia impostata su Tabella nativa.

7. Nella sezione Schema, inserisci lo schema. definizione di Kubernetes.

   • Opzione 1: utilizza Aggiungi campo e specifica il nome, il tipo e la modalità di ogni campo.
   • Opzione 2: fai clic su Modifica come testo e incolla lo schema sotto forma di un array JSON. Quando utilizzi un array JSON, generi lo schema utilizzando la stessa procedura utilizzata per creare un file di schema JSON.

8. Fai clic su Crea tabella.

SQL

Utilizza la CREATE TABLE. Specifica lo schema utilizzando il comando colonna . L'esempio seguente crea una nuova tabella denominata newtable con le colonne x, y, z di tipi interi, stringa e booleano:

1. Nella console Google Cloud, vai alla pagina BigQuery.

   Vai a BigQuery

2. Nell'editor query, inserisci la seguente istruzione:

   CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRING, z BOOL)
     OPTIONS(
       description = 'My example table');

3. Fai clic su Esegui.

Per ulteriori informazioni su come eseguire query, consulta Eseguire una query interattiva.

bq

Fornisci lo schema in linea nel formato field:data_type,field:data_type usando uno degli seguenti comandi:

• Se carichi dati, utilizza il comando bq load.
• Se stai creando una tabella vuota, utilizza il comando bq mk.

Quando specifichi lo schema nella riga di comando, non puoi includere RECORD (STRUCT) o RANGE esistenti, non puoi includere una descrizione della colonna non è possibile specificare la modalità della colonna. Tutte le modalità predefinite sono: NULLABLE. A includi descrizioni, modalità, tipi di RECORD e tipi di RANGE, fornisci un file di schema JSON.

Per caricare i dati in una tabella utilizzando una definizione di schema in linea, inserisci il comando load e specifica il formato dei dati utilizzando il flag --source_format. Se carichi dati in una tabella di un progetto diverso da quello predefinito includi l'ID progetto nel seguente formato: project_id:dataset.table_name.

(Facoltativo) Fornisci il flag --location e imposta il valore sulla tua posizione.

bq --location=location load \
--source_format=format \
project_id:dataset.table_name \
path_to_source \
schema

Sostituisci quanto segue:

• location: il nome della tua località. Il --location è facultativo. Ad esempio, se utilizzi BigQuery nella regione di Tokyo, puoi impostare il valore del flag su asia-northeast1. Puoi imposta un valore predefinito per la località utilizzando file.bigqueryrc.
• format: NEWLINE_DELIMITED_JSON o CSV.
• project_id: il tuo ID progetto.
• dataset: il set di dati che contiene la tabella in cui stai caricando i dati.
• table_name: il nome della tabella in cui stai caricando i dati.
• path_to_source: la posizione del file di dati CSV o JSON sulla tua macchina locale o in Cloud Storage.
• schema: la definizione dello schema in linea.

Esempio:

Inserisci il seguente comando per caricare i dati da un file CSV locale denominato myfile.csv in mydataset.mytable nel progetto predefinito. Lo schema è specificato in linea.

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

Per ulteriori informazioni sul caricamento dei dati in BigQuery, consulta Introduzione al caricamento dei dati.

Per specificare una definizione di schema in linea quando crei una tabella vuota, inserisci Il comando bq mk con il flag --table o -t. Se crei una tabella in un progetto diverso da quello predefinito, aggiungi l'ID progetto il comando nel seguente formato: project_id:dataset.table.

bq mk --table project_id:dataset.table schema

Sostituisci quanto segue:

• project_id: il tuo ID progetto.
• dataset: un set di dati nel tuo progetto.
• table: il nome della tabella che stai creando.
• schema: una definizione dello schema in linea.

Ad esempio, il seguente comando crea una tabella vuota denominata mytable in il progetto predefinito. Lo schema è specificato in linea.

bq mk --table mydataset.mytable qtr:STRING,sales:FLOAT,year:STRING

Per ulteriori informazioni sulla creazione di una tabella vuota, consulta Creare una tabella vuota con una definizione di schema.

C#

Per specificare lo schema di una tabella quando carichi i dati in una tabella:

Prima di provare questo esempio, segui le istruzioni per la configurazione di C# nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API BigQuery C#.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.

using Google.Apis.Bigquery.v2.Data;
using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryLoadTableGcsJson
{
    public void LoadTableGcsJson(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        var gcsURI = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
        var dataset = client.GetDataset(datasetId);
        var schema = new TableSchemaBuilder {
            { "name", BigQueryDbType.String },
            { "post_abbr", BigQueryDbType.String }
        }.Build();
        TableReference destinationTableRef = dataset.GetTableReference(
            tableId: "us_states");
        // Create job configuration
        var jobOptions = new CreateLoadJobOptions()
        {
            SourceFormat = FileFormat.NewlineDelimitedJson
        };
        // Create and run job
        BigQueryJob loadJob = client.CreateLoadJob(
            sourceUri: gcsURI, destination: destinationTableRef,
            schema: schema, options: jobOptions);
        loadJob = loadJob.PollUntilCompleted().ThrowOnAnyError();  // Waits for the job to complete.
        // Display the number of rows uploaded
        BigQueryTable table = client.GetTable(destinationTableRef);
        Console.WriteLine(
            $"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}");
    }
}

Per specificare uno schema quando crei una tabella vuota:

using Google.Cloud.BigQuery.V2;

public class BigQueryCreateTable
{
    public BigQueryTable CreateTable(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        var dataset = client.GetDataset(datasetId);
        // Create schema for new table.
        var schema = new TableSchemaBuilder
        {
            { "full_name", BigQueryDbType.String },
            { "age", BigQueryDbType.Int64 }
        }.Build();
        // Create the table
        return dataset.CreateTable(tableId: "your_table_id", schema: schema);
    }
}

Vai

Per specificare lo schema di una tabella quando carichi i dati in una tabella:

Prima di provare questo esempio, segui le istruzioni di configurazione Go riportate nella guida rapida all'utilizzo di BigQuery con le librerie client. Per ulteriori informazioni, consulta API Go BigQuery documentazione di riferimento.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.

import (
	"context"
	"fmt"

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

// importJSONExplicitSchema demonstrates loading newline-delimited JSON data from Cloud Storage
// into a BigQuery table and providing an explicit schema for the data.
func importJSONExplicitSchema(projectID, datasetID, tableID 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()

	gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states.json")
	gcsRef.SourceFormat = bigquery.JSON
	gcsRef.Schema = bigquery.Schema{
		{Name: "name", Type: bigquery.StringFieldType},
		{Name: "post_abbr", Type: bigquery.StringFieldType},
	}
	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(gcsRef)
	loader.WriteDisposition = bigquery.WriteEmpty

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

	if status.Err() != nil {
		return fmt.Errorf("job completed with error: %v", status.Err())
	}
	return nil
}

Per specificare uno schema quando crei una tabella vuota:

import (
	"context"
	"fmt"
	"time"

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

// createTableExplicitSchema demonstrates creating a new BigQuery table and specifying a schema.
func createTableExplicitSchema(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType},
		{Name: "age", Type: bigquery.IntegerFieldType},
	}

	metaData := &bigquery.TableMetadata{
		Schema:         sampleSchema,
		ExpirationTime: time.Now().AddDate(1, 0, 0), // Table will be automatically deleted in 1 year.
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, metaData); err != nil {
		return err
	}
	return nil
}

Java

Per specificare lo schema di una tabella quando carichi i dati in una tabella:

Prima di provare questo esempio, segui le istruzioni di configurazione Java riportate nella guida rapida all'utilizzo di BigQuery con le librerie client. Per ulteriori informazioni, consulta API Java BigQuery documentazione di riferimento.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;

// Sample to load JSON data from Cloud Storage into a new BigQuery table
public class LoadJsonFromGCS {

  public static void runLoadJsonFromGCS() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    loadJsonFromGCS(datasetName, tableName, sourceUri, schema);
  }

  public static void loadJsonFromGCS(
      String datasetName, String tableName, String sourceUri, Schema schema) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);
      LoadJobConfiguration loadConfig =
          LoadJobConfiguration.newBuilder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.json())
              .setSchema(schema)
              .build();

      // Load data from a GCS JSON file into the table
      Job job = bigquery.create(JobInfo.of(loadConfig));
      // Blocks until this load table job completes its execution, either failing or succeeding.
      job = job.waitFor();
      if (job.isDone()) {
        System.out.println("Json from GCS successfully loaded in a table");
      } else {
        System.out.println(
            "BigQuery was unable to load into the table due to an error:"
                + job.getStatus().getError());
      }
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Column not added during load append \n" + e.toString());
    }
  }
}

Per specificare uno schema quando crei una tabella vuota:

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

public class CreateTable {

  public static void runCreateTable() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    Schema schema =
        Schema.of(
            Field.of("stringField", StandardSQLTypeName.STRING),
            Field.of("booleanField", StandardSQLTypeName.BOOL));
    createTable(datasetName, tableName, schema);
  }

  public static void createTable(String datasetName, String tableName, Schema schema) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);
      TableDefinition tableDefinition = StandardTableDefinition.of(schema);
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Table was not created. \n" + e.toString());
    }
  }
}

Python

Per specificare lo schema di una tabella quando carichi i dati in una tabella, LoadJobConfig.schema proprietà.

Prima di provare questo esempio, segui le istruzioni per la configurazione di Python nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API BigQuery Python.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per saperne di più, consulta Configurare l'autenticazione per le librerie client.

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(
    schema=[
        bigquery.SchemaField("name", "STRING"),
        bigquery.SchemaField("post_abbr", "STRING"),
    ],
    source_format=bigquery.SourceFormat.NEWLINE_DELIMITED_JSON,
)
uri = "gs://cloud-samples-data/bigquery/us-states/us-states.json"

load_job = client.load_table_from_uri(
    uri,
    table_id,
    location="US",  # Must match the destination dataset location.
    job_config=job_config,
)  # Make an API request.

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

destination_table = client.get_table(table_id)
print("Loaded {} rows.".format(destination_table.num_rows))

Per specificare uno schema quando crei una tabella vuota, configura il Table.schema proprietà.

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"

schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]

table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)  # Make an API request.
print(
    "Created table {}.{}.{}".format(table.project, table.dataset_id, table.table_id)
)

Specifica di un file di schema JSON

Se preferisci, puoi specificare lo schema utilizzando uno schema JSON. anziché utilizzare una definizione di schema in linea. Un file di schema JSON è costituito di un array JSON contenente quanto segue:

• Il nome della colonna
• Il tipo di dati della colonna
• (Facoltativo) La modalità della colonna (se non specificata, il valore predefinito è NULLABLE).
• (Facoltativo) I campi della colonna se è di tipo STRUCT
• Facoltativo: la descrizione della colonna
• (Facoltativo) I tag di criterio della colonna, utilizzata per il controllo dell'accesso a livello di campo
• (Facoltativo) La lunghezza massima dei valori della colonna per i tipi STRING o BYTES
• (Facoltativo) Il valore della precisione della colonna. per tipi NUMERIC o BIGNUMERIC
• (Facoltativo) La scala della colonna per i tipi NUMERIC o BIGNUMERIC
• (Facoltativo) La composizione della colonna per STRING tipi
• (Facoltativo) Il valore predefinito della colonna
• (Facoltativo) La modalità di arrotondamento della colonna, se la colonna è una Tipo NUMERIC o BIGNUMERIC

Nota: puoi anche specificare l'array JSON che crei nel file di schema utilizzando l'opzione Modifica come testo della console Google Cloud. Può anche essere utilizzato come punto di partenza per la configurazione della proprietà schema nell'API.

Creare un file di schema JSON

Per creare un file di schema JSON, inserisci un TableFieldSchema per ogni colonna. I campi name e type sono obbligatori. Tutti gli altri campi sono facoltativi.

[
  {
    "name": string,
    "type": string,
    "mode": string,
    "fields": [
      {
        object (TableFieldSchema)
      }
    ],
    "description": string,
    "policyTags": {
      "names": [
        string
      ]
    },
    "maxLength": string,
    "precision": string,
    "scale": string,
    "collation": string,
    "defaultValueExpression": string,
    "roundingMode": string
  },
  {
    "name": string,
    "type": string,
    ...
  }
]

Se la colonna è di tipo RANGE<T>, utilizza il campo rangeElementType per descrivere T, dove T deve essere uno tra DATE, DATETIME o TIMESTAMP.

[
  {
    "name": "duration",
    "type": "RANGE",
    "mode": "NULLABLE",
    "rangeElementType": {
      "type": "DATE"
    }
  }
]

L'array JSON è indicato dalle parentesi graffe di inizio e di fine []. Ciascuna la voce nella colonna deve essere separata da una virgola: },.

Per scrivere uno schema di tabella esistente in un file locale:

bq

bq show \
--schema \
--format=prettyjson \
project_id:dataset.table > path_to_file

Sostituisci quanto segue:

• project_id: il tuo ID progetto.
• dataset: un set di dati nel tuo progetto.
• table: il nome di uno schema di tabella esistente.
• path_to_file: la posizione del file locale in cui stai scrivendo uno schema di tabella.

Python

Prima di provare questo esempio, segui le istruzioni per la configurazione di Python nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API BigQuery Python.

Per autenticarti a BigQuery, configura le credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.

Per scrivere un file JSON di schema da una tabella utilizzando la libreria client Python, chiama il metodo Client.schema_to_json.

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change the table_id variable to the full name of the
# table you want to get schema from.
table_id = "your-project.your_dataset.your_table_name"

# TODO(dev): Change schema_path variable to the path
# of your schema file.
schema_path = "path/to/schema.json"
table = client.get_table(table_id)  # Make an API request.

# Write a schema file to schema_path with the schema_to_json method.
client.schema_to_json(table.schema, schema_path)

with open(schema_path, "r", encoding="utf-8") as schema_file:
    schema_contents = schema_file.read()

# View table properties
print(f"Got table '{table.project}.{table.dataset_id}.{table.table_id}'.")
print(f"Table schema: {schema_contents}")

Puoi utilizzare il file di output come punto di partenza per il tuo file dello schema JSON. Se utilizzi questo approccio, assicurati che il file contenga solo l'array JSON che rappresenta lo schema della tabella.

Ad esempio, il seguente array JSON rappresenta uno schema di tabella di base. Questo lo schema ha tre colonne: qtr (REQUIRED STRING), rep (NULLABLE STRING), e sales (NULLABLE FLOAT).

[
  {
    "name": "qtr",
    "type": "STRING",
    "mode": "REQUIRED",
    "description": "quarter"
  },
  {
    "name": "rep",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "sales representative"
  },
  {
    "name": "sales",
    "type": "FLOAT",
    "mode": "NULLABLE",
    "defaultValueExpression": "2.55"
  }
]

Utilizzo di un file di schema JSON

Dopo aver creato il file di schema JSON, puoi specificarlo utilizzando lo strumento a riga di comando bq. Non puoi utilizzare un file di schema con la console Google Cloud o l'API.

Fornisci il file dello schema:

• Se carichi dati, utilizza il comando bq load.
• Se stai creando una tabella vuota, utilizza il comando bq mk.

Quando fornisci un file schema JSON, deve essere archiviato in una posizione di lettura locale. Non puoi specificare un file schema JSON archiviato in Cloud Storage o su Google Drive.

Specifica di un file dello schema durante il caricamento dei dati

Per caricare i dati in una tabella utilizzando una definizione dello schema JSON, procedi nel seguente modo:

bq

bq --location=location load \
--source_format=format \
project_id:dataset.table \
path_to_data_file \
path_to_schema_file

Sostituisci quanto segue:

• location: il nome della tua località. Il --location è facultativo. Ad esempio, se utilizzi BigQuery nella zona puoi impostare il valore del flag su asia-northeast1. Puoi impostare un valore predefinito per la località utilizzando file.bigqueryrc.
• format: NEWLINE_DELIMITED_JSON o CSV.
• project_id: il tuo ID progetto.
• dataset: il set di dati che contiene la tabella in cui stai caricando i dati.
• table: il nome della tabella in cui stai caricando i dati.
• path_to_data_file: la posizione del file CSV o JSON sul tuo computer locale o in Cloud Storage.
• path_to_schema_file: il percorso del file di schema su della macchina locale.

Esempio:

Inserisci il seguente comando per caricare i dati da un file CSV locale denominato myfile.csv in mydataset.mytable nel progetto predefinito. Lo schema è specificato in myschema.json nella directory corrente.

bq load --source_format=CSV mydataset.mytable ./myfile.csv ./myschema.json

Python

Prima di provare questo esempio, segui le istruzioni di configurazione Python riportate nella guida rapida all'utilizzo di BigQuery con le librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API BigQuery Python.

Per autenticarti a BigQuery, configura le credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.

Per caricare lo schema di una tabella da un file JSON utilizzando la libreria client Python, chiama il metodo schema_from_json.

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change uri variable to the path of your data file.
uri = "gs://your-bucket/path/to/your-file.csv"
# TODO(dev): Change table_id to the full name of the table you want to create.
table_id = "your-project.your_dataset.your_table"
# TODO(dev): Change schema_path variable to the path of your schema file.
schema_path = "path/to/schema.json"
# To load a schema file use the schema_from_json method.
schema = client.schema_from_json(schema_path)

job_config = bigquery.LoadJobConfig(
    # To use the schema you loaded pass it into the
    # LoadJobConfig constructor.
    schema=schema,
    skip_leading_rows=1,
)

# Pass the job_config object to the load_table_from_file,
# load_table_from_json, or load_table_from_uri method
# to use the schema on a new table.
load_job = client.load_table_from_uri(
    uri, table_id, job_config=job_config
)  # Make an API request.

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

destination_table = client.get_table(table_id)  # Make an API request.
print(f"Loaded {destination_table.num_rows} rows to {table_id}.")

Specifica di un file di schema durante la creazione di una tabella

Per creare una tabella vuota in un set di dati esistente utilizzando un file di schema JSON:

bq

bq mk --table project_id:dataset.table path_to_schema_file

Sostituisci quanto segue:

• project_id: il tuo ID progetto.
• dataset: un set di dati nel tuo progetto.
• table: il nome della tabella che stai creando.
• path_to_schema_file: il percorso del file dello schema sulla tua macchina locale.

Ad esempio, il seguente comando crea una tabella denominata mytable in mydataset nel progetto predefinito. Lo schema è specificato in myschema.json nella directory corrente:

bq mk --table mydataset.mytable ./myschema.json

Python

Prima di provare questo esempio, segui le istruzioni di configurazione Python riportate nella guida rapida all'utilizzo di BigQuery con le librerie client. Per ulteriori informazioni, consulta API Python BigQuery documentazione di riferimento.

Per autenticarti a BigQuery, configura le credenziali predefinite dell'applicazione. Per saperne di più, consulta Configurare l'autenticazione per le librerie client.

Per caricare lo schema di una tabella da un file JSON utilizzando la libreria client Python, chiama il metodo schema_from_json.

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change table_id to the full name of the table you want to create.
table_id = "your-project.your_dataset.your_table_name"
# TODO(dev): Change schema_path variable to the path of your schema file.
schema_path = "path/to/schema.json"
# To load a schema file use the schema_from_json method.
schema = client.schema_from_json(schema_path)

table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)  # API request
print(f"Created table {table_id}.")

Specifica di uno schema nell'API

Specifica uno schema tabella utilizzando l'API:

• Per specificare uno schema quando carichi i dati, chiama il metodo jobs.insert e configura la proprietà schema nella risorsa JobConfigurationLoad.

• Per specificare uno schema quando crei una tabella, chiama il metodo tables.insert e configura la proprietà schema nella risorsa Table.

La specifica di uno schema utilizzando l'API è simile alla procedura per Creazione di un file di schema JSON.

Sicurezza delle tabelle

Per controllare l'accesso alle tabelle in BigQuery, consulta Introduzione ai controlli di accesso alle tabelle.

Passaggi successivi

• Scopri come specificare colonne nidificate e ripetute in una definizione dello schema.
• Scopri di più sul rilevamento automatico dello schema.
• Scopri di più sul caricamento dei dati in BigQuery.
• Scopri di più su come creare e utilizzare le tabelle.