Modifica degli schemi delle tabelle

Questo documento descrive come modificare le definizioni dello schema per e tabelle BigQuery.

Puoi apportare la maggior parte delle modifiche allo schema descritte in questo documento utilizzando SQL Istruzioni DDL (Data Definition Language). Questi estratti conto non comportano alcun addebito.

Puoi modificare uno schema di tabella in tutti i modi descritti in questa pagina: esportare i dati delle tabelle in Cloud Storage e poi caricando i dati in una nuova tabella con la definizione dello schema modificata. I job di caricamento ed esportazione di BigQuery sono ma comportano costi per l'archiviazione dei dati esportati di archiviazione ideale in Cloud Storage. Le seguenti sezioni descrivono altri metodi per eseguire i vari tipi di modifiche allo schema.

Nota: quando aggiorni uno schema, le modifiche potrebbero non essere applicate immediatamente alle visualizzazioni INFORMATION_SCHEMA.TABLES e INFORMATION_SCHEMA.COLUMNS. Per visualizzare le modifiche immediate allo schema, chiama il metodo Metodo tables.get.

Aggiungi una colonna

Puoi aggiungere colonne alla definizione dello schema di una tabella esistente utilizzando una delle seguenti opzioni:

• Aggiungi una nuova colonna vuota.
• Sovrascrivi una tabella con un job di caricamento o di query.
• Aggiungere dati a una tabella con un job di caricamento o di query.

Qualsiasi colonna aggiunta deve rispettare le regole di BigQuery per i nomi delle colonne. Per ulteriori informazioni sulla creazione dei componenti dello schema, consulta l'articolo Specificare uno schema.

Aggiungere una colonna vuota

Se aggiungi nuove colonne a uno schema di tabella esistente, le colonne devono essere NULLABLE o REPEATED. Non puoi aggiungere una colonna REQUIRED a uno schema di tabella esistente. Aggiunta di una colonna REQUIRED a una tabella esistente nell'API o nello strumento a riga di comando bq causa un errore. Tuttavia, puoi creare un colonna REQUIRED nidificata come parte di un nuovo campo RECORD. Le colonne REQUIRED possono essere aggiunte solo quando crei una tabella durante il caricamento dei dati o quando crei una tabella vuota con una definizione dello schema.

Per aggiungere colonne vuote alla definizione dello schema di una tabella:

Console

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

   Vai a BigQuery

2. Nel riquadro Explorer, espandi il progetto e il set di dati, quindi seleziona la tabella.

3. Nel riquadro dei dettagli, fai clic sulla scheda Schema.

4. Fai clic su Modifica schema. Per vedere il pulsante potresti dover scorrere.

5. Nella pagina Schema attuale, fai clic su Aggiungi campo in Nuovi campi.

   • In Nome, digita il nome della colonna.
   • In Tipo, scegli il tipo di dati.
   • Per Modalità, scegli NULLABLE o REPEATED.

6. Quando hai finito di aggiungere le colonne, fai clic su Salva.

Nota: non puoi utilizzare la console Google Cloud per aggiungere una colonna a una tabella esterna.

SQL

Utilizza la ALTER TABLE ADD COLUMN Istruzione DDL:

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

   Vai a BigQuery

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

   ALTER TABLE mydataset.mytable
   ADD COLUMN new_column STRING;

3. Fai clic su Esegui.

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

Nota: non puoi utilizzare l'istruzione ALTER TABLE ADD COLUMN per aggiungere una colonna a una tabella esterna.

bq

Esegui il comando bq update e fornisci un file di schema JSON. Se la tabella che stai aggiornando si trova in un progetto diverso da quello predefinito, aggiungi al nome del set di dati nel seguente formato: PROJECT_ID:DATASET.

bq update PROJECT_ID:DATASET.TABLE SCHEMA

Sostituisci quanto segue:

• PROJECT_ID: il tuo ID progetto.
• DATASET: il nome del set di dati che contiene la tabella che stai aggiornando.
• TABLE: il nome della tabella da aggiornare.
• SCHEMA: il percorso del file dello schema JSON sulla tua macchina locale.

Quando specifichi uno schema in linea, non puoi specificare la colonna descrizione, modalità e RECORD (STRUCT) di testo. Tutte le modalità delle colonne sono impostate su NULLABLE per impostazione predefinita. Di conseguenza, se stai aggiungendo una nuova colonna nidificata a un RECORD, devi fornire un file di schema JSON.

Se tenti di aggiungere colonne utilizzando una definizione di schema in linea, devi: fornisce l'intera definizione dello schema, incluse le nuove colonne. Perché non può specificare le modalità a colonne utilizzando una definizione di schema in linea, l'aggiornamento cambia qualsiasi colonna REPEATED esistente in NULLABLE, genera il seguente errore: BigQuery error in update operation: Provided Schema does not match Table PROJECT_ID:dataset.table. Field field has changed mode from REPEATED to NULLABLE.

Il metodo preferito per aggiungere colonne a una tabella esistente utilizzando lo strumento a riga di comando bq è per fornire un file di schema JSON.

Per aggiungere colonne vuote allo schema di una tabella utilizzando un file di schema JSON:

1. Innanzitutto, invia il comando bq show con il flag --schema e scrivi schema di tabella esistente in un file. Se la tabella che stai aggiornando è in una per un progetto diverso da quello predefinito, aggiungi l'ID progetto nome del set di dati nel seguente formato: PROJECT_ID:DATASET.

   bq show \
   --schema \
   --format=prettyjson \
   PROJECT_ID:DATASET.TABLE > SCHEMA

   Sostituisci quanto segue:

   • PROJECT_ID: il tuo ID progetto.
   • DATASET: il nome del set di dati che contiene la tabella che stai aggiornando.
   • TABLE: il nome della tabella da aggiornare.
   • SCHEMA: il file di definizione dello schema scritto sulla tua macchina locale.

   Ad esempio, per scrivere la definizione di schema di mydataset.mytable in un inserisci il seguente comando. mydataset.mytable è in progetto predefinito.

      bq show \
      --schema \
      --format=prettyjson \
      mydataset.mytable > /tmp/myschema.json
   

2. Apri il file di schema in un editor di testo. Lo schema dovrebbe avere il seguente aspetto:

   [
     {
       "mode": "REQUIRED",
       "name": "column1",
       "type": "STRING"
     },
     {
       "mode": "REQUIRED",
       "name": "column2",
       "type": "FLOAT"
     },
     {
       "mode": "REPEATED",
       "name": "column3",
       "type": "STRING"
     }
   ]
   

3. Aggiungi le nuove colonne alla fine della definizione dello schema. Se provi ad aggiungere nuove colonne altrove nell'array, viene restituito il seguente errore: BigQuery error in update operation: Precondition Failed.

   Con un file JSON puoi specificare descrizioni, NULLABLE o modalità REPEATED e RECORD tipi per le nuove colonne. Ad esempio: utilizzando la definizione dello schema del passaggio precedente, il nuovo array JSON sarebbe simile al seguente. In questo esempio, una nuova colonna NULLABLE viene aggiunto con il nome column4. column4 include una descrizione.

     [
       {
         "mode": "REQUIRED",
         "name": "column1",
         "type": "STRING"
       },
       {
         "mode": "REQUIRED",
         "name": "column2",
         "type": "FLOAT"
       },
       {
         "mode": "REPEATED",
         "name": "column3",
         "type": "STRING"
       },
       {
         "description": "my new column",
         "mode": "NULLABLE",
         "name": "column4",
         "type": "STRING"
       }
     ]
     

   Per ulteriori informazioni sull'utilizzo dei file di schema JSON, consulta Specificare un file di schema JSON.

4. Dopo aver aggiornato il file di schema, esegui il comando seguente per aggiornare lo schema della tabella. Se la tabella che stai aggiornando si trova in un progetto diverso da quello predefinito, aggiungi l'ID progetto al nome del set di dati nel seguente formato: PROJECT_ID:DATASET.

   bq update PROJECT_ID:DATASET.TABLE SCHEMA

   Sostituisci quanto segue:

   • PROJECT_ID: il tuo ID progetto.
   • DATASET: il nome del set di dati che contiene la tabella che stai aggiornando.
   • TABLE: il nome della tabella da aggiornare.
   • SCHEMA: il file di definizione dello schema scritto nel tuo dalla macchina locale.

   Ad esempio, inserisci il comando seguente per aggiornare la definizione dello schema di mydataset.mytable nel progetto predefinito. Il percorso del file dello schema sulla tua macchina locale è /tmp/myschema.json.

   bq update mydataset.mytable /tmp/myschema.json
   

API

Chiama il metodo tables.patch e utilizza la proprietà schema per aggiungere colonne vuote alla definizione dello schema. Poiché il metodo tables.update sostituisce l'intera risorsa tabella, è preferibile il metodo tables.patch.

Vai

Prima di provare questo esempio, segui le istruzioni per la configurazione di Go nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta API Go 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.

import (
	"context"
	"fmt"

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

// updateTableAddColumn demonstrates modifying the schema of a table to append an additional column.
func updateTableAddColumn(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()

	tableRef := client.Dataset(datasetID).Table(tableID)
	meta, err := tableRef.Metadata(ctx)
	if err != nil {
		return err
	}
	newSchema := append(meta.Schema,
		&bigquery.FieldSchema{Name: "phone", Type: bigquery.StringFieldType},
	)
	update := bigquery.TableMetadataToUpdate{
		Schema: newSchema,
	}
	if _, err := tableRef.Update(ctx, update, meta.ETag); err != nil {
		return err
	}
	return nil
}

Java

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 la documentazione di riferimento dell'API BigQuery Java.

Per autenticarti a BigQuery, configura le credenziali predefinite dell'applicazione. Per saperne di più, consulta Configurare 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.FieldList;
import com.google.cloud.bigquery.LegacySQLTypeName;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.Table;
import java.util.ArrayList;
import java.util.List;

public class AddEmptyColumn {

  public static void runAddEmptyColumn() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableId = "MY_TABLE_NAME";
    String newColumnName = "NEW_COLUMN_NAME";
    addEmptyColumn(newColumnName, datasetName, tableId);
  }

  public static void addEmptyColumn(String newColumnName, String datasetName, String tableId) {
    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();

      Table table = bigquery.getTable(datasetName, tableId);
      Schema schema = table.getDefinition().getSchema();
      FieldList fields = schema.getFields();

      // Create the new field/column
      Field newField = Field.of(newColumnName, LegacySQLTypeName.STRING);

      // Create a new schema adding the current fields, plus the new one
      List<Field> fieldList = new ArrayList<Field>();
      fields.forEach(fieldList::add);
      fieldList.add(newField);
      Schema newSchema = Schema.of(fieldList);

      // Update the table with the new schema
      Table updatedTable =
          table.toBuilder().setDefinition(StandardTableDefinition.of(newSchema)).build();
      updatedTable.update();
      System.out.println("Empty column successfully added to table");
    } catch (BigQueryException e) {
      System.out.println("Empty column was not added. \n" + e.toString());
    }
  }
}

Node.js

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

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

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

async function addEmptyColumn() {
  // Adds an empty column to the schema.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';
  const column = {name: 'size', type: 'STRING'};

  // Retrieve current table metadata
  const table = bigquery.dataset(datasetId).table(tableId);
  const [metadata] = await table.getMetadata();

  // Update table schema
  const schema = metadata.schema;
  const new_schema = schema;
  new_schema.fields.push(column);
  metadata.schema = new_schema;

  const [result] = await table.setMetadata(metadata);
  console.log(result.schema.fields);
}

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 eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per saperne di più, consulta Configurare l'autenticazione per le librerie client.

Aggiungere un nuovo oggetto SchemaField a una copia di Table.schema e poi sostituire il valore della proprietà Table.schema con lo schema aggiornato.

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 add an empty column.
# table_id = "your-project.your_dataset.your_table_name"

table = client.get_table(table_id)  # Make an API request.

original_schema = table.schema
new_schema = original_schema[:]  # Creates a copy of the schema.
new_schema.append(bigquery.SchemaField("phone", "STRING"))

table.schema = new_schema
table = client.update_table(table, ["schema"])  # Make an API request.

if len(table.schema) == len(original_schema) + 1 == len(new_schema):
    print("A new column has been added.")
else:
    print("The column has not been added.")

Aggiungere una colonna nidificata a una colonna RECORD

Oltre ad aggiungere nuove colonne allo schema di una tabella, puoi anche aggiungere nuove colonne nidificate a una colonna RECORD. La procedura per aggiungere una nuova colonna nidificata è in modo simile alla procedura per aggiungere una nuova colonna.

Console

L'aggiunta di un nuovo campo nidificato a una colonna RECORD esistente non supportate dalla console Google Cloud.

SQL

L'aggiunta di un nuovo campo nidificato a una colonna RECORD esistente utilizzando un'istruzione DDL SQL non è supportata.

bq

Esegui il comando bq update e fornisci un file di schema JSON che aggiunga il campo nidificato alla definizione dello schema della colonna RECORD esistente. Se che stai aggiornando si trova in un progetto diverso da quello predefinito, aggiungi l'ID progetto al nome del set di dati nel seguente formato: PROJECT_ID:DATASET.

bq update PROJECT_ID:DATASET.TABLE SCHEMA

Sostituisci quanto segue:

• PROJECT_ID: il tuo ID progetto.
• DATASET: il nome del set di dati che contiene la tabella che stai aggiornando.
• TABLE: il nome della tabella che stai aggiornando.
• SCHEMA : il percorso del file di schema JSON sul tuo server in una macchina virtuale.

Quando specifichi uno schema in linea, non puoi specificare la colonna descrizione, modalità e RECORD (STRUCT) di testo. Tutte le modalità delle colonne sono impostate su NULLABLE per impostazione predefinita. Di conseguenza, se aggiungendo una nuova colonna nidificata a un RECORD, devi fornisci un file di schema JSON.

Per aggiungere una colonna nidificata a un RECORD utilizzando un file di schema JSON:

1. Innanzitutto, invia il comando bq show con il flag --schema e scrivi schema di tabella esistente in un file. Se la tabella che stai aggiornando è in una per un progetto diverso da quello predefinito, aggiungi l'ID progetto nome del set di dati nel seguente formato: PROJECT_ID:DATASET.TABLE.

   bq show \
   --schema \
   --format=prettyjson \
   PROJECT_ID:DATASET.TABLE > SCHEMA

   Sostituisci quanto segue:

   • PROJECT_ID: il tuo ID progetto.
   • DATASET: il nome del set di dati che contiene la tabella che stai aggiornando.
   • TABLE: il nome della tabella da aggiornare.
   • SCHEMA: il file di definizione dello schema scritto sulla tua macchina locale.

   Ad esempio, per scrivere la definizione di schema di mydataset.mytable in un inserisci il seguente comando. mydataset.mytable è in progetto predefinito.

   bq show \
   --schema \
   --format=prettyjson \
   mydataset.mytable > /tmp/myschema.json
   

2. Apri il file di schema in un editor di testo. Lo schema dovrebbe avere l'aspetto seguire. In questo esempio, column3 è una colonna ripetuta nidificata. Le colonne nidificate sono nested1 e nested2. L'array fields elenca i campi nidificati all'interno di column3.

   [
     {
       "mode": "REQUIRED",
       "name": "column1",
       "type": "STRING"
     },
     {
       "mode": "REQUIRED",
       "name": "column2",
       "type": "FLOAT"
     },
     {
       "fields": [
         {
           "mode": "NULLABLE",
           "name": "nested1",
           "type": "STRING"
         },
         {
           "mode": "NULLABLE",
           "name": "nested2",
           "type": "STRING"
         }
       ],
       "mode": "REPEATED",
       "name": "column3",
       "type": "RECORD"
     }
   ]
   

3. Aggiungi la nuova colonna nidificata alla fine dell'array fields. In questo Ad esempio, nested3 è la nuova colonna nidificata.

     [
       {
         "mode": "REQUIRED",
         "name": "column1",
         "type": "STRING"
       },
       {
         "mode": "REQUIRED",
         "name": "column2",
         "type": "FLOAT"
       },
       {
         "fields": [
           {
             "mode": "NULLABLE",
             "name": "nested1",
             "type": "STRING"
           },
           {
             "mode": "NULLABLE",
             "name": "nested2",
             "type": "STRING"
           },
           {
             "mode": "NULLABLE",
             "name": "nested3",
             "type": "STRING"
           }
         ],
         "mode": "REPEATED",
         "name": "column3",
         "type": "RECORD"
       }
     ]
     

   Per ulteriori informazioni sull'utilizzo dei file di schema JSON, vedi Specifica di un file di schema JSON.

4. Dopo aver aggiornato il file dello schema, esegui il seguente comando per aggiornarne lo schema. Se la tabella che stai aggiornando si trova in un progetto diverso da quello predefinito, aggiungi l'ID progetto al nome del set di dati nel seguente formato: PROJECT_ID:DATASET.

   bq update PROJECT_ID:DATASET.TABLE SCHEMA

   Sostituisci quanto segue:

   • PROJECT_ID: il tuo ID progetto.
   • DATASET: il nome del set di dati che contiene la tabella che stai aggiornando.
   • TABLE: il nome della tabella da aggiornare.
   • SCHEMA: il percorso del file dello schema JSON sulla tua macchina locale.

   Ad esempio, inserisci il seguente comando per aggiornare la definizione dello schema di mydataset.mytable nel progetto predefinito. Il percorso dello schema sul computer locale è /tmp/myschema.json.

   bq update mydataset.mytable /tmp/myschema.json
   

API

Chiama il metodo tables.patch e utilizza la proprietà schema per aggiungere le colonne nidificate alla definizione dello schema. Poiché il metodo tables.update sostituisce l'intera risorsa tabella, è preferibile il metodo tables.patch.

Aggiungere colonne quando sovrascrivi o accondi i dati

Puoi aggiungere nuove colonne a una tabella esistente quando carichi i dati al suo interno e scegli di sovrascrivere la tabella esistente. Quando sovrascrivi una tabella esistente, lo schema dei dati che stai caricando viene utilizzato per sovrascrivere lo schema della tabella esistente. Per informazioni sull'overwriting di una tabella utilizzando un job di caricamento, consulta il documento relativo al formato dei dati:

• Avro
• Pacchino
• ORC
• CSV
• JSON

Aggiungere colonne in un job di accodamento del caricamento

Puoi aggiungere colonne a una tabella quando le accondi i dati in un job di caricamento. Il nuovo schema è determinato da uno dei seguenti elementi:

• Rilevamento automatico (per file CSV e JSON)
• Uno schema specificato in un file di schema JSON (per i file CSV e JSON)
• I dati di origine autodescrittivi per Avro, ORC, Parquet e File di esportazione Datastore

Se specifichi lo schema in un file JSON, le nuove colonne devono essere definite al suo interno. Se mancano le nuove definizioni di colonna, viene restituito un errore quando provi ad aggiungere dati.

Quando aggiungi nuove colonne durante un'operazione di accodamento, i valori nelle nuove colonne vengono impostati su NULL per le righe esistenti.

Per aggiungere una nuova colonna quando aggiungi dati a una tabella durante un job di caricamento, utilizza una delle seguenti opzioni:

bq

Utilizza il comando bq load per caricare i dati e specifica il flag --noreplace per indicare che stai aggiungendo i dati a una tabella esistente.

Se i dati che stai aggiungendo sono in formato CSV o JSON delimitato da nuova riga, specifica il flag --autodetect per utilizzare il rilevamento automatico dello schema o fornisci lo schema in un file di schema JSON. Le colonne aggiunte possono essere ricavate automaticamente dai file di esportazione Avro o Datastore.

Imposta il flag --schema_update_option su ALLOW_FIELD_ADDITION per indicare che i dati che stai aggiungendo contengono nuove colonne.

Se la tabella che stai aggiungendo si trova in un set di dati di un progetto diverso da quello predefinito, aggiungi l'ID progetto al nome del set di dati nel seguente formato: PROJECT_ID:DATASET.

(Facoltativo) Fornisci il flag --location e imposta il valore su località.

Inserisci il comando load come segue:

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

Sostituisci quanto segue:

• LOCATION: il nome del luogo in cui ti trovi. Il flag --location è facoltativo. Ad esempio, se utilizzi BigQuery Regione di Tokyo, imposta il valore del flag su asia-northeast1. Puoi impostare un valore predefinito per la località utilizzando file.bigqueryrc.
• FORMAT: il formato dello schema. NEWLINE_DELIMITED_JSON, CSV, AVRO, PARQUET, ORC o DATASTORE_BACKUP.
• PROJECT_ID: il tuo ID progetto.
• DATASET: il nome del set di dati che contiene la tabella.
• TABLE: il nome della tabella da accodare.
• PATH_TO_SOURCE: un URI Cloud Storage completo, un elenco di URI separati da virgole o il percorso di un file di dati sul tuo dalla macchina locale.
• SCHEMA: il percorso di un file di schema JSON locale. Un file schema è obbligatorio solo per i file CSV e JSON quando --autodetect non è specificato. Gli schemi Avro e Datastore vengono dedotti dai dati di origine.

Esempi:

Inserisci il seguente comando per aggiungere un file di dati Avro locale: /tmp/mydata.avro, a mydataset.mytable utilizzando un job di caricamento. Poiché gli schemi possono essere dedotti automaticamente dai dati Avro che non hai bisogno di utilizzare il flag --autodetect. mydataset è nel tuo progetto predefinito.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=AVRO \
mydataset.mytable \
/tmp/mydata.avro

Inserisci il seguente comando per aggiungere un file di dati JSON delimitato da riga nuova in Cloud Storage a mydataset.mytable utilizzando un job di caricamento. --autodetect viene usato per rilevare le nuove colonne. mydataset si trova nel progetto predefinito.

bq load \
--noreplace \
--autodetect \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json

Inserisci il seguente comando per aggiungere un file di dati JSON delimitato da riga nuova in Cloud Storage a mydataset.mytable utilizzando un job di caricamento. Lo schema contenente le nuove colonne è specificato in un file di schema JSON locale,/tmp/myschema.json. mydataset si trova in myotherproject, non nel progetto predefinito.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=NEWLINE_DELIMITED_JSON \
myotherproject:mydataset.mytable \
gs://mybucket/mydata.json \
/tmp/myschema.json

API

Chiama il jobs.insert . Configura un job load e imposta le seguenti proprietà:

• Fai riferimento ai tuoi dati in Cloud Storage utilizzando la proprietà sourceUris.
• Specifica il formato dei dati impostando la proprietà sourceFormat.
• Specifica lo schema nella proprietà schema.
• Specifica l'opzione di aggiornamento dello schema utilizzando la proprietà schemaUpdateOptions.
• Imposta l'istruzione di scrittura della tabella di destinazione su WRITE_APPEND usando la proprietà writeDisposition.

Vai

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 saperne di più, consulta Configurare l'autenticazione per le librerie client.

import (
	"context"
	"fmt"
	"os"

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

// createTableAndWidenLoad demonstrates augmenting a table's schema to add a new column via a load job.
func createTableAndWidenLoad(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()

	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType},
	}
	meta := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	// Now, import data from a local file, but specify field additions are allowed.
	// Because the data has a second column (age), the schema is amended as part of
	// the load.
	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)
	loader.SchemaUpdateOptions = []string{"ALLOW_FIELD_ADDITION"}
	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
}

Java

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

Per autenticarti a BigQuery, configura le 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.JobId;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobInfo.SchemaUpdateOption;
import com.google.cloud.bigquery.JobInfo.WriteDisposition;
import com.google.cloud.bigquery.LegacySQLTypeName;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.TableId;
import com.google.common.collect.ImmutableList;
import java.util.UUID;

public class AddColumnLoadAppend {

  public static void runAddColumnLoadAppend() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "/path/to/file.csv";
    addColumnLoadAppend(datasetName, tableName, sourceUri);
  }

  public static void addColumnLoadAppend(String datasetName, String tableName, String sourceUri)
      throws Exception {
    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);

      // Add a new column to a BigQuery table while appending rows via a load job.
      // 'REQUIRED' fields cannot  be added to an existing schema, so the additional column must be
      // 'NULLABLE'.
      Schema newSchema =
          Schema.of(
              Field.newBuilder("name", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.REQUIRED)
                  .build(),
              // Adding below additional column during the load job
              Field.newBuilder("post_abbr", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build());

      LoadJobConfiguration loadJobConfig =
          LoadJobConfiguration.builder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.csv())
              .setWriteDisposition(WriteDisposition.WRITE_APPEND)
              .setSchema(newSchema)
              .setSchemaUpdateOptions(ImmutableList.of(SchemaUpdateOption.ALLOW_FIELD_ADDITION))
              .build();

      // Create a job ID so that we can safely retry.
      JobId jobId = JobId.of(UUID.randomUUID().toString());
      Job loadJob = bigquery.create(JobInfo.newBuilder(loadJobConfig).setJobId(jobId).build());

      // Load data from a GCS parquet file into the table
      // Blocks until this load table job completes its execution, either failing or succeeding.
      Job completedJob = loadJob.waitFor();

      // Check for errors
      if (completedJob == null) {
        throw new Exception("Job not executed since it no longer exists.");
      } else if (completedJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(
            "BigQuery was unable to load into the table due to an error: \n"
                + loadJob.getStatus().getError());
      }
      System.out.println("Column successfully added during load append job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Column not added during load append \n" + e.toString());
    }
  }
}

Node.js

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

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

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

// Instantiate client
const bigquery = new BigQuery();

async function addColumnLoadAppend() {
  // Adds a new column to a BigQuery table while appending rows via a load job.

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

  // In this example, the existing table contains only the 'Name', 'Age',
  // & 'Weight' columns. 'REQUIRED' fields cannot  be added to an existing
  // schema, so the additional column must be 'NULLABLE'.
  const schema = 'Name:STRING, Age:INTEGER, Weight:FLOAT, IsMagic:BOOLEAN';

  // Retrieve destination table reference
  const [table] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .get();
  const destinationTableRef = table.metadata.tableReference;

  // Set load job options
  const options = {
    schema: schema,
    schemaUpdateOptions: ['ALLOW_FIELD_ADDITION'],
    writeDisposition: 'WRITE_APPEND',
    destinationTable: destinationTableRef,
  };

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

  console.log(`Job ${job.id} completed.`);
  console.log(`New Schema:`);
  console.log(job.configuration.load.schema.fields);

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

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 eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, 'my_dataset')
# filepath = 'path/to/your_file.csv'

# Retrieves the destination table and checks the length of the schema
table_id = "my_table"
table_ref = dataset_ref.table(table_id)
table = client.get_table(table_ref)
print("Table {} contains {} columns.".format(table_id, len(table.schema)))

# Configures the load job to append the data to the destination table,
# allowing field addition
job_config = bigquery.LoadJobConfig()
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION
]
# In this example, the existing table contains only the 'full_name' column.
# 'REQUIRED' fields cannot be added to an existing schema, so the
# additional column must be 'NULLABLE'.
job_config.schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="NULLABLE"),
]
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1

with open(filepath, "rb") as source_file:
    job = client.load_table_from_file(
        source_file,
        table_ref,
        location="US",  # Must match the destination dataset location.
        job_config=job_config,
    )  # API request

job.result()  # Waits for table load to complete.
print(
    "Loaded {} rows into {}:{}.".format(
        job.output_rows, dataset_id, table_ref.table_id
    )
)

# Checks the updated length of the schema
table = client.get_table(table)
print("Table {} now contains {} columns.".format(table_id, len(table.schema)))

Aggiungi colonne in un job di aggiunta query

Puoi aggiungere colonne a una tabella aggiungendo i risultati della query.

Quando aggiungi colonne utilizzando un'operazione di accodamento in un job di query, lo schema dei risultati della query viene utilizzato per aggiornare lo schema della tabella di destinazione. Tieni presente che non puoi eseguire query su una tabella in una posizione e scrivere i risultati in una tabella in un'altra posizione.

Per aggiungere una nuova colonna quando aggiungi dati a una tabella durante un job di query, seleziona una delle seguenti opzioni:

bq

Usa il comando bq query per eseguire query sui dati e specificare --destination_table per indicare la tabella che stai aggiungendo.

Per specificare l'aggiunta dei risultati della query a una destinazione esistente. specifica il flag --append_table.

Imposta il flag --schema_update_option su ALLOW_FIELD_ADDITION per indicare che i risultati della query che stai aggiungendo contengano nuove colonne.

Specifica il flag use_legacy_sql=false per utilizzare la sintassi GoogleSQL per la query.

Se la tabella che stai aggiungendo si trova in un set di dati di un progetto diverso da quello predefinito, aggiungi l'ID progetto al nome del set di dati nel seguente formato: PROJECT_ID:DATASET. Tieni presente che la tabella su cui esegui una query e la tabella di destinazione deve trovarsi nella stessa località.

(Facoltativo) Fornisci il flag --location e imposta il valore su località.

bq --location=LOCATION query \
--destination_table PROJECT_ID:DATASET.TABLE \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'QUERY'

Sostituisci quanto segue:

• LOCATION: il nome della tua località. Il flag --location è facoltativo. Ad esempio, se utilizzi BigQuery nella regione di Tokyo, imposta il valore del flag su asia-northeast1. Puoi impostare un valore predefinito per la posizione utilizzando il file .bigqueryrc. Tieni presente che non puoi accodare i risultati della query a una tabella in un'altra posizione.
• PROJECT_ID: il tuo ID progetto.
• dataset: il nome del set di dati che contiene la tabella che stai aggiungendo.
• TABLE: il nome della tabella da accodare.
• QUERY: una query in sintassi GoogleSQL.

Esempi:

Inserisci il seguente comando per eseguire una query su mydataset.mytable nel progetto predefinito e per accodare i risultati della query a mydataset.mytable2 (anche nel progetto predefinito).

bq query \
--destination_table mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

Inserisci il seguente comando per eseguire una query su mydataset.mytable nel progetto predefinito e per accodare i risultati della query a mydataset.mytable2 in myotherproject.

bq query \
--destination_table myotherproject:mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

API

Chiama il jobs.insert . Configura un job query e imposta le seguenti proprietà:

• Specifica la tabella di destinazione utilizzando la proprietà destinationTable.
• Imposta l'istruzione di scrittura della tabella di destinazione su WRITE_APPEND usando la proprietà writeDisposition.
• Specifica l'opzione di aggiornamento dello schema utilizzando schemaUpdateOptions proprietà.
• Specifica la query GoogleSQL utilizzando la proprietà query.

Vai

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

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

import (
	"context"
	"fmt"

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

// createTableAndWidenQuery demonstrates how the schema of a table can be modified to add columns by appending
// query results that include the new columns.
func createTableAndWidenQuery(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()

	// First, we create a sample table.
	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	original := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, original); err != nil {
		return err
	}
	// Our table has two columns.  We'll introduce a new favorite_color column via
	// a subsequent query that appends to the table.
	q := client.Query("SELECT \"Timmy\" as full_name, 85 as age, \"Blue\" as favorite_color")
	q.SchemaUpdateOptions = []string{"ALLOW_FIELD_ADDITION"}
	q.QueryConfig.Dst = client.Dataset(datasetID).Table(tableID)
	q.WriteDisposition = bigquery.WriteAppend
	q.Location = "US"
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	_, err = job.Wait(ctx)
	if err != nil {
		return err
	}
	return nil
}

Java

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

Per autenticarti a BigQuery, configura le 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.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobInfo.SchemaUpdateOption;
import com.google.cloud.bigquery.JobInfo.WriteDisposition;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableList;

public class RelaxTableQuery {

  public static void runRelaxTableQuery() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    relaxTableQuery(projectId, datasetName, tableName);
  }

  // To relax all columns in a destination table when you append data to it during a query job
  public static void relaxTableQuery(String projectId, String datasetName, String tableName)
      throws Exception {
    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);

      String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`";
      String query = "SELECT word FROM " + sourceTable + " WHERE word like '%is%'";

      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              // Use standard SQL syntax for queries.
              // See: https://cloud.google.com/bigquery/sql-reference/
              .setUseLegacySql(false)
              .setSchemaUpdateOptions(ImmutableList.of(SchemaUpdateOption.ALLOW_FIELD_RELAXATION))
              .setWriteDisposition(WriteDisposition.WRITE_APPEND)
              .setDestinationTable(tableId)
              .build();

      Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).build());

      queryJob = queryJob.waitFor();

      // Check for errors
      if (queryJob == null) {
        throw new Exception("Job no longer exists");
      } else if (queryJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(queryJob.getStatus().getError().toString());
      }

      // Get the results.
      TableResult results = queryJob.getQueryResults();

      // Print all pages of the results.
      results
          .iterateAll()
          .forEach(
              rows -> {
                rows.forEach(row -> System.out.println("row: " + row.toString()));
              });

      System.out.println("Successfully relaxed all columns in destination table during query job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Columns not relaxed during query job \n" + e.toString());
    }
  }
}

Node.js

Prima di provare questo esempio, segui le istruzioni di configurazione Node.js riportate nella guida rapida all'utilizzo di BigQuery con le librerie client. Per ulteriori informazioni, consulta API Node.js 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 the Google Cloud client libraries
const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate client
const bigquery = new BigQuery();

async function addColumnQueryAppend() {
  // Adds a new column to a BigQuery table while appending rows via a query job.

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

  // Retrieve destination table reference
  const [table] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .get();
  const destinationTableRef = table.metadata.tableReference;

  // In this example, the existing table contains only the 'name' column.
  // 'REQUIRED' fields cannot  be added to an existing schema,
  // so the additional column must be 'NULLABLE'.
  const query = `SELECT name, year
    FROM \`bigquery-public-data.usa_names.usa_1910_2013\`
    WHERE state = 'TX'
    LIMIT 10`;

  // Set load job options
  const options = {
    query: query,
    schemaUpdateOptions: ['ALLOW_FIELD_ADDITION'],
    writeDisposition: 'WRITE_APPEND',
    destinationTable: destinationTableRef,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
  };

  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();
  console.log(`Job ${job.id} completed.`);

  // Print the results
  console.log('Rows:');
  rows.forEach(row => console.log(row));
}

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 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 destination table.
# table_id = "your-project.your_dataset.your_table_name"

# Retrieves the destination table and checks the length of the schema.
table = client.get_table(table_id)  # Make an API request.
print("Table {} contains {} columns".format(table_id, len(table.schema)))

# Configures the query to append the results to a destination table,
# allowing field addition.
job_config = bigquery.QueryJobConfig(
    destination=table_id,
    schema_update_options=[bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION],
    write_disposition=bigquery.WriteDisposition.WRITE_APPEND,
)

# Start the query, passing in the extra configuration.
query_job = client.query(
    # In this example, the existing table contains only the 'full_name' and
    # 'age' columns, while the results of this query will contain an
    # additional 'favorite_color' column.
    'SELECT "Timmy" as full_name, 85 as age, "Blue" as favorite_color;',
    job_config=job_config,
)  # Make an API request.
query_job.result()  # Wait for the job to complete.

# Checks the updated length of the schema.
table = client.get_table(table_id)  # Make an API request.
print("Table {} now contains {} columns".format(table_id, len(table.schema)))

Modificare il nome di una colonna

Per rinominare una colonna di una tabella, utilizza l'istruzione DDL ALTER TABLE RENAME COLUMN. L'esempio seguente rinomina la colonna old_name in new_name in mytable:

ALTER TABLE mydataset.mytable
  RENAME COLUMN old_name TO new_name;

Per maggiori informazioni informazioni sugli estratti conto ALTER TABLE RENAME COLUMN, consulta Dettagli DDL.

Modificare il tipo di dati di una colonna

La modifica del tipo di dati di una colonna non è supportata dalla console Google Cloud, dallo strumento a riga di comando bq o dall'API BigQuery. Se provi ad aggiornare una tabella applicando uno schema che specifica un nuovo tipo di dati per una colonna, viene restituito un errore.

Modificare il tipo di dati di una colonna con un'istruzione DDL

Puoi utilizzare GoogleSQL per apportare determinate modifiche al tipo di dati di una colonna. Per ulteriori informazioni e un elenco completo delle conversioni dei tipi di dati supportati, consulta la istruzione DDL ALTER COLUMN SET DATA TYPE.

L'esempio seguente crea una tabella con una colonna di tipo INT64, quindi aggiorna il tipo su NUMERIC:

CREATE TABLE mydataset.mytable(c1 INT64);

ALTER TABLE mydataset.mytable
ALTER COLUMN c1 SET DATA TYPE NUMERIC;

L'esempio seguente crea una tabella con una colonna nidificata con due campi e poi aggiorna il tipo di una delle colonne da INT a NUMERIC:

CREATE TABLE mydataset.mytable(s1 STRUCT<a INT64, b STRING>);

ALTER TABLE mydataset.mytable ALTER COLUMN s1
SET DATA TYPE STRUCT<a NUMERIC, b STRING>;

Eseguire il casting del tipo di dati di una colonna

Per modificare il tipo di dati di una colonna in un tipo trasformabile, utilizza una query SQL per selezionare i dati della tabella, trasforma la colonna pertinente e sovrascrive la tabella. Il trasferimento e la sovrascrittura non sono consigliati per tabelle di grandi dimensioni perché richiedono una scansione completa della tabella.

L'esempio seguente mostra una query SQL che seleziona tutti i dati da column_two e column_three in mydataset.mytable e esegue il casting di column_one da DATE a STRING. Il risultato della query viene utilizzato per sovrascrivere la tabella esistente. La tabella sovrascritta memorizza column_one come tipo di dati STRING.

Quando utilizzi CAST, una query può non riuscire se BigQuery non è in grado di eseguire il trasferimento. Per maggiori dettagli sulle regole di trasmissione in GoogleSQL, consulta Trasmissione.

Console

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

   Vai a BigQuery

2. In Editor di query, inserisci la seguente query per selezionare tutte le query dati da column_two e column_three nel mese di mydataset.mytable e a trasmetti column_one da DATE a STRING. La query utilizza un alias per trasmetti column_one con lo stesso nome. mydataset.mytable partecipa il progetto predefinito.

   SELECT
    column_two,
    column_three,
    CAST(column_one AS STRING) AS column_one
   FROM
    mydataset.mytable;

3. Fai clic su Altro e seleziona Impostazioni query.

4. Nella sezione Destinazione:

   1. Seleziona Imposta una tabella di destinazione per i risultati della query.

   2. In Nome progetto, lascia il valore impostato sul progetto predefinito. Questo è il progetto che contiene mydataset.mytable.

   3. In Set di dati, scegli mydataset.

   4. Nel campo Table Id (ID tabella), inserisci mytable.

   5. Per Preferenza di scrittura per la tabella di destinazione, seleziona Sovrascrivi dalla tabella. Questa opzione sovrascrive mytable utilizzando i risultati della query.

5. Se vuoi, scegli la località dei dati.

6. Per aggiornare le impostazioni, fai clic su Salva.

7. Fai clic su Esegui.

   Al termine del job di query, il tipo di dati di column_one è STRING.

bq

Inserisci il seguente comando bq query per selezionare tutti i dati da column_two e column_three in mydataset.mytable e per eseguire il trasferimento di column_one da DATE a STRING. La query utilizza un alias per trasmettere column_one con lo stesso nome. mydataset.mytable è nella tua predefinita progetto.

I risultati della query vengono scritti in mydataset.mytable utilizzando il flag --destination_table e il flag --replace viene utilizzato per sovrascrivere mytable. Specifica il flag use_legacy_sql=false da utilizzare Sintassi GoogleSQL.

Se vuoi, fornisci il flag --location e imposta il valore sulla tua posizione.

bq query \
    --destination_table mydataset.mytable \
    --replace \
    --use_legacy_sql=false \
'SELECT
  column_two,
  column_three,
  CAST(column_one AS STRING) AS column_one
FROM
  mydataset.mytable'

API

Per selezionare tutti i dati di column_two e column_three in mydataset.mytable e per trasmettere column_one da DATE a STRING, chiama jobs.insert e configurare un job query. Facoltativamente, specifica la tua posizione nel location nella sezione jobReference.

La query SQL utilizzata nel job di query sarà SELECT column_two, column_three, CAST(column_one AS STRING) AS column_one FROM mydataset.mytable. La query utilizza un alias per trasmettere column_one con con lo stesso nome.

Per sovrascrivere mytable con i risultati della query, includi mydataset.mytable nella proprietà configuration.query.destinationTable e specifica WRITE_TRUNCATE nella proprietà configuration.query.writeDisposition.

Modificare la modalità di una colonna

L'unica modifica supportata che puoi apportare alla modalità di una colonna è cambiarla da REQUIRED a NULLABLE. La modifica della modalità di una colonna da REQUIRED a NULLABLE è chiamata anche rilassamento della colonna. Puoi anche ignorare una colonna quando carichi i dati per sovrascrivere una tabella esistente o quando aggiungi dati a una tabella esistente. Non puoi modificare la modalità di una colonna da NULLABLE a REQUIRED.

Creare una colonna NULLABLE in una tabella esistente

Per modificare la modalità di una colonna da REQUIRED a NULLABLE, seleziona una delle le seguenti opzioni:

Console

1. Vai alla pagina BigQuery.

   Vai a BigQuery

2. Nel riquadro Explorer, espandi il progetto e il set di dati, quindi seleziona la tabella.

3. Nel riquadro dei dettagli, fai clic sulla scheda Schema.

4. Fai clic su Modifica schema. Potresti dover scorrere per visualizzare questo pulsante.

5. Nella pagina Schema attuale, individua il campo da modificare.

6. Nell'elenco a discesa Modalità per il campo in questione, seleziona NULLABLE.

7. Per aggiornare le impostazioni, fai clic su Salva.

Nota: non puoi utilizzare la console Google Cloud per modificare una colonna in una tabella esterna.

SQL

Utilizza l'istruzione DDL ALTER COLUMN DROP NOT NULL. L'esempio seguente cambia la modalità della colonna mycolumn da REQUIRED a NULLABLE:

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

   Vai a BigQuery

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

   ALTER TABLE mydataset.mytable
   ALTER COLUMN mycolumn
   DROP NOT NULL;

3. Fai clic su Esegui.

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

bq

1. Innanzitutto, invia il comando bq show con il flag --schema e scrivi schema di tabella esistente in un file. Se la tabella che stai aggiornando è in una un progetto diverso da quello predefinito, aggiungi l'ID progetto al set di dati nome nel seguente formato: PROJECT_ID:DATASET.

   bq show \
   --schema \
   --format=prettyjson \
   PROJECT_ID:DATASET.TABLE > SCHEMA_FILE

   Sostituisci quanto segue:

   • PROJECT_ID: il tuo ID progetto.
   • DATASET: il nome del set di dati che contiene la tabella che stai aggiornando.
   • TABLE: il nome della tabella da aggiornare.
   • SCHEMA_FILE: il file di definizione dello schema scritto sul computer locale.

   Ad esempio, per scrivere la definizione di schema di mydataset.mytable in un inserisci il seguente comando. mydataset.mytable si trova nel tuo progetto predefinito.

     bq show \
     --schema \
     --format=prettyjson \
     mydataset.mytable > /tmp/myschema.json
   

2. Apri il file dello schema in un editor di testo. Lo schema dovrebbe avere il seguente aspetto:

   [
     {
       "mode": "REQUIRED",
       "name": "column1",
       "type": "STRING"
     },
     {
       "mode": "REQUIRED",
       "name": "column2",
       "type": "FLOAT"
     },
     {
       "mode": "REPEATED",
       "name": "column3",
       "type": "STRING"
     }
   ]
   

3. Modifica la modalità di una colonna esistente da REQUIRED a NULLABLE. In questo ad esempio, la modalità column1 è informale.

   [
     {
       "mode": "NULLABLE",
       "name": "column1",
       "type": "STRING"
     },
     {
       "mode": "REQUIRED",
       "name": "column2",
       "type": "FLOAT"
     },
     {
       "mode": "REPEATED",
       "name": "column3",
       "type": "STRING"
     }
   ]
   

   Per ulteriori informazioni sull'utilizzo dei file di schema JSON, consulta Specificare un file di schema JSON.

4. Dopo aver aggiornato il file di schema, esegui il comando seguente per aggiornare lo schema della tabella. Se la tabella che stai aggiornando si trova in un progetto diverso da quello predefinito, aggiungi l'ID progetto al nome del set di dati nel seguente formato: PROJECT_ID:DATASET.

   bq update PROJECT_ID:DATASET.TABLE SCHEMA

   Sostituisci quanto segue:

   • PROJECT_ID: il tuo ID progetto.
   • DATASET: il nome del set di dati che contiene la tabella che stai aggiornando.
   • TABLE: il nome della tabella da aggiornare.
   • SCHEMA: il percorso del file dello schema JSON sulla tua macchina locale.

   Ad esempio, inserisci il seguente comando per aggiornare la definizione dello schema di mydataset.mytable nel progetto predefinito. Il percorso dello schema sul computer locale è /tmp/myschema.json.

     bq update mydataset.mytable /tmp/myschema.json
   

API

Chiama tables.patch e utilizza la proprietà schema per modificare una colonna REQUIRED in NULLABLE in la definizione dello schema. Poiché il metodo tables.update sostituisce l'intera risorsa di tabella, è preferibile il metodo tables.patch.

Vai

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 autenticarti a BigQuery, configura le credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.

import (
	"context"
	"fmt"

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

// relaxTableAPI demonstrates modifying the schema of a table to remove the requirement that columns allow
// no NULL values.
func relaxTableAPI(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()

	// Setup: We first create a table with a schema that's restricts NULL values.
	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	original := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	if err := client.Dataset(datasetID).Table(tableID).Create(ctx, original); err != nil {
		return err
	}

	tableRef := client.Dataset(datasetID).Table(tableID)
	meta, err := tableRef.Metadata(ctx)
	if err != nil {
		return err
	}
	// Iterate through the schema to set all Required fields to false (nullable).
	var relaxed bigquery.Schema
	for _, v := range meta.Schema {
		v.Required = false
		relaxed = append(relaxed, v)
	}
	newMeta := bigquery.TableMetadataToUpdate{
		Schema: relaxed,
	}
	if _, err := tableRef.Update(ctx, newMeta, meta.ETag); err != nil {
		return err
	}
	return nil
}

Java

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.LegacySQLTypeName;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.Table;

public class RelaxColumnMode {

  public static void runRelaxColumnMode() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableId = "MY_TABLE_NAME";
    relaxColumnMode(datasetName, tableId);
  }

  public static void relaxColumnMode(String datasetName, String tableId) {
    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();

      Table table = bigquery.getTable(datasetName, tableId);

      // Create new relaxed schema based on the existing table schema
      Schema relaxedSchema =
          Schema.of(
              // The only supported modification you can make to a column's mode is changing it from
              // REQUIRED to NULLABLE
              // Changing a column's mode from REQUIRED to NULLABLE is also called column relaxation
              // INFO: LegacySQLTypeName will be updated to StandardSQLTypeName in release 1.103.0
              Field.newBuilder("word", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build(),
              Field.newBuilder("word_count", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build(),
              Field.newBuilder("corpus", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build(),
              Field.newBuilder("corpus_date", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build());

      // Update the table with the new schema
      Table updatedTable =
          table.toBuilder().setDefinition(StandardTableDefinition.of(relaxedSchema)).build();
      updatedTable.update();
      System.out.println("Table schema successfully relaxed.");
    } catch (BigQueryException e) {
      System.out.println("Table schema not relaxed \n" + e.toString());
    }
  }
}

Node.js

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

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

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

async function relaxColumn() {
  /**
   * Changes columns from required to nullable.
   * Assumes existing table with the following schema:
   * [{name: 'Name', type: 'STRING', mode: 'REQUIRED'},
   * {name: 'Age', type: 'INTEGER'},
   * {name: 'Weight', type: 'FLOAT'},
   * {name: 'IsMagic', type: 'BOOLEAN'}];
   */

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

  const newSchema = [
    {name: 'Name', type: 'STRING', mode: 'NULLABLE'},
    {name: 'Age', type: 'INTEGER'},
    {name: 'Weight', type: 'FLOAT'},
    {name: 'IsMagic', type: 'BOOLEAN'},
  ];

  // Retrieve current table metadata
  const table = bigquery.dataset(datasetId).table(tableId);
  const [metadata] = await table.getMetadata();

  // Update schema
  metadata.schema = newSchema;
  const [apiResponse] = await table.setMetadata(metadata);

  console.log(apiResponse.schema.fields);
}

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 eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per saperne di più, consulta Configurare l'autenticazione per le librerie client.

Sovrascrivi Table.schema con un elenco di SchemaField di oggetti con la modalità proprietà impostata su 'NULLABLE'

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change table_id to full name of the table you want to create.
table_id = "your-project.your_dataset.your_table"

table = client.get_table(table_id)
new_schema = []
for field in table.schema:
    if field.mode != "REQUIRED":
        new_schema.append(field)
    else:
        # SchemaField properties cannot be edited after initialization.
        # To make changes, construct new SchemaField objects.
        new_field = field.to_api_repr()
        new_field["mode"] = "NULLABLE"
        relaxed_field = bigquery.SchemaField.from_api_repr(new_field)
        new_schema.append(relaxed_field)

table.schema = new_schema
table = client.update_table(table, ["schema"])

print(f"Updated {table_id} schema: {table.schema}.")

Crea una colonna NULLABLE con un job di caricamento con accodamento

Puoi allentare la modalità di una colonna quando aggiungi dati a una tabella in un job di caricamento. Seleziona una delle seguenti opzioni in base al tipo di file:

• Quando aggiungi dati da file CSV e JSON, allenta la modalità per specificando un file di schema JSON.
• Quando aggiungi dati da file Avro, ORC o Parquet, allenta le colonne in NULL nello schema e consentire all'inferenza dello schema di rilevare le colonne rilassate.

Nota: l'allentamento della colonna non si applica alle aggiunte di esportazione di Datastore. Le colonne nelle tabelle create caricando i file di esportazione di Datastore sono sempre NULLABLE.

Allentare una colonna da REQUIRED a NULLABLE quando aggiungi dati a una tabella durante un job di caricamento, seleziona una delle seguenti opzioni:

Console

Non puoi riattivare la modalità di una colonna utilizzando la console Google Cloud.

bq

Utilizza il comando bq load per caricare i dati e specifica il flag --noreplace per indicare che stai aggiungendo i dati a una tabella esistente.

Se i dati che stai aggiungendo sono in formato CSV o JSON delimitato da nuova riga, specifica le colonne con regole meno rigide in un file dello schema JSON locale o utilizza il flag --autodetect per utilizzare il rilevamento dello schema per rilevare le colonne con regole meno rigide nei dati di origine.

Le colonne confortevoli possono essere dedotte automaticamente da Avro, ORC e Parquet . Il rilassamento della colonna non si applica all'esportazione di Datastore . Le colonne nelle tabelle create caricando i file di esportazione di Datastore sono sempre NULLABLE.

Imposta il flag --schema_update_option su ALLOW_FIELD_RELAXATION per indicare che i dati che stai aggiungendo contengono colonne con regole meno rigide.

Se la tabella che stai aggiungendo si trova in un set di dati di un progetto diverso da quello predefinito, aggiungi l'ID progetto al nome del set di dati nel seguente formato: PROJECT_ID:DATASET.

(Facoltativo) Fornisci il flag --location e imposta il valore su località.

Inserisci il comando load come segue:

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

Sostituisci quanto segue:

• LOCATION: il nome della tua località. Il flag --location è facoltativo. Ad esempio, se utilizzi BigQuery nella regione di Tokyo, imposta il valore del flag su asia-northeast1. Puoi impostare un valore predefinito per la località utilizzando file.bigqueryrc.
• FORMAT: NEWLINE_DELIMITED_JSON, CSV, PARQUET, ORC o AVRO, I file DATASTORE_BACKUP non richiedono un rilassamento della colonna. La le colonne nelle tabelle create dai file di esportazione Datastore sono sempre NULLABLE.
• PROJECT_ID: il tuo ID progetto.
• dataset è il nome del set di dati che contiene la tabella.
• TABLE: il nome della tabella da aggregare.
• PATH_TO_SOURCE: un URI Cloud Storage completo, un elenco di URI separati da virgole o il percorso di un file di dati sulla tua macchina locale.
• SCHEMA: il percorso di un file di schema JSON locale. Questa opzione viene utilizzata solo per i file CSV e JSON. Le colonne con restrizioni vengono ricavate automaticamente dai file Avro.

Esempi:

Inserisci il seguente comando per accodare un file di dati Avro locale, /tmp/mydata.avro, a mydataset.mytable utilizzando un job di caricamento. Poiché le colonne con regole meno rigide possono essere dedotte automaticamente dai dati Avro, non è necessario specificare un file dello schema. mydataset è nel tuo progetto predefinito.

bq load \
    --noreplace \
    --schema_update_option=ALLOW_FIELD_RELAXATION \
    --source_format=AVRO \
    mydataset.mytable \
    /tmp/mydata.avro

Inserisci il seguente comando per accodare i dati da un file JSON delimitato da riga in Cloud Storage a mydataset.mytable utilizzando un job di caricamento. La Lo schema contenente le colonne relax è inserito in un file di schema JSON locale: /tmp/myschema.json. mydataset si trova nel tuo progetto predefinito.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json \
/tmp/myschema.json

Inserisci il seguente comando per aggiungere dati in un file CSV sulla tua rete locale dalla macchina virtuale a mydataset.mytable utilizzando un job di caricamento. Il comando utilizza il rilevamento automatico dello schema per rilevare le colonne con regole meno stringenti nei dati di origine. mydataset si trova in myotherproject, non nel tuo progetto predefinito.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=CSV \
--autodetect \
myotherproject:mydataset.mytable \
mydata.csv

API

Chiama il jobs.insert . Configura un job load e imposta le seguenti proprietà:

• Fai riferimento ai tuoi dati in Cloud Storage utilizzando la proprietà sourceUris.
• Specifica il formato dei dati impostando la proprietà sourceFormat.
• Specifica lo schema nella proprietà schema.
• Specifica l'opzione di aggiornamento dello schema utilizzando la proprietà schemaUpdateOptions.
• Imposta l'istruzione di scrittura della tabella di destinazione su WRITE_APPEND usando la proprietà writeDisposition.

Vai

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 autenticarti a BigQuery, configura le credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.

import (
	"context"
	"fmt"
	"os"

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

// relaxTableImport demonstrates amending the schema of a table to relax columns from
// not allowing NULL values to allowing them.
func relaxTableImport(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()

	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	meta := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	// Now, import data from a local file, but specify relaxation of required
	// fields as a side effect while the data is appended.
	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)
	loader.SchemaUpdateOptions = []string{"ALLOW_FIELD_RELAXATION"}
	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
}

Java

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 la documentazione di riferimento dell'API BigQuery Java.

Per autenticarti a BigQuery, configura le 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.CsvOptions;
import com.google.cloud.bigquery.Field;
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.Table;
import com.google.cloud.bigquery.TableId;
import com.google.common.collect.ImmutableList;

// Sample to append relax column in a table.
public class RelaxColumnLoadAppend {

  public static void main(String[] args) {
    // 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.csv";
    relaxColumnLoadAppend(datasetName, tableName, sourceUri);
  }

  public static void relaxColumnLoadAppend(String datasetName, String tableName, String sourceUri) {
    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();

      // Retrieve destination table reference
      Table table = bigquery.getTable(TableId.of(datasetName, tableName));

      // column as a 'REQUIRED' field.
      Field name =
          Field.newBuilder("name", StandardSQLTypeName.STRING).setMode(Field.Mode.REQUIRED).build();
      Field postAbbr =
          Field.newBuilder("post_abbr", StandardSQLTypeName.STRING)
              .setMode(Field.Mode.REQUIRED)
              .build();
      Schema schema = Schema.of(name, postAbbr);

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      // Set job options
      LoadJobConfiguration loadConfig =
          LoadJobConfiguration.newBuilder(table.getTableId(), sourceUri)
              .setSchema(schema)
              .setFormatOptions(csvOptions)
              .setSchemaUpdateOptions(
                  ImmutableList.of(JobInfo.SchemaUpdateOption.ALLOW_FIELD_RELAXATION))
              .setWriteDisposition(JobInfo.WriteDisposition.WRITE_APPEND)
              .build();

      // Create a load job and wait for it to complete.
      Job job = bigquery.create(JobInfo.of(loadConfig));
      job = job.waitFor();
      // Check the job's status for errors
      if (job.isDone() && job.getStatus().getError() == null) {
        System.out.println("Relax column append 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());
    }
  }
}

Node.js

Prima di provare questo esempio, segui le istruzioni per la configurazione di Node.js nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta API Node.js 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.

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

// Instantiate client
const bigquery = new BigQuery();

async function relaxColumnLoadAppend() {
  // Changes required column to nullable in load append job.

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

  // In this example, the existing table contains the 'Name'
  // column as a 'REQUIRED' field.
  const schema = 'Age:INTEGER, Weight:FLOAT, IsMagic:BOOLEAN';

  // Retrieve destination table reference
  const [table] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .get();
  const destinationTableRef = table.metadata.tableReference;

  // Set load job options
  const options = {
    schema: schema,
    schemaUpdateOptions: ['ALLOW_FIELD_RELAXATION'],
    writeDisposition: 'WRITE_APPEND',
    destinationTable: destinationTableRef,
  };

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

  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;
  }
}

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.

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, 'my_dataset')
# filepath = 'path/to/your_file.csv'

# Retrieves the destination table and checks the number of required fields
table_id = "my_table"
table_ref = dataset_ref.table(table_id)
table = client.get_table(table_ref)
original_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
# In this example, the existing table has 3 required fields.
print("{} fields in the schema are required.".format(original_required_fields))

# Configures the load job to append the data to a destination table,
# allowing field relaxation
job_config = bigquery.LoadJobConfig()
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION
]
# In this example, the existing table contains three required fields
# ('full_name', 'age', and 'favorite_color'), while the data to load
# contains only the first two fields.
job_config.schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1

with open(filepath, "rb") as source_file:
    job = client.load_table_from_file(
        source_file,
        table_ref,
        location="US",  # Must match the destination dataset location.
        job_config=job_config,
    )  # API request

job.result()  # Waits for table load to complete.
print(
    "Loaded {} rows into {}:{}.".format(
        job.output_rows, dataset_id, table_ref.table_id
    )
)

# Checks the updated number of required fields
table = client.get_table(table)
current_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
print("{} fields in the schema are now required.".format(current_required_fields))

Crea tutte le colonne NULLABLE con un job di accodamento

Puoi ignorare tutte le colonne di una tabella quando le accondi ai risultati della query. Tu puoi allentare tutti i campi obbligatori nella tabella di destinazione impostando il valore --schema_update_option flag per ALLOW_FIELD_RELAXATION. Non puoi rilassare le singole colonne di una tabella di destinazione utilizzando un'unione di query. Per allentare le singole colonne con un job di accodamento del caricamento, consulta Creare una colonna NULLABLE con un job di accodamento.

Per rilassare tutte le colonne quando aggiungi i risultati della query a una tabella di destinazione, seleziona una delle seguenti opzioni:

Console

Non puoi riattivare la modalità di una colonna utilizzando la console Google Cloud.

bq

Usa il comando bq query per eseguire query sui dati e specificare --destination_table per indicare la tabella che stai aggiungendo.

Per specificare che stai aggiungendo i risultati della query a una tabella di destinazione esistente, specifica il flag --append_table.

Imposta il flag --schema_update_option su ALLOW_FIELD_RELAXATION su indica che tutte le REQUIRED colonne nella tabella che stai aggiungendo devono essere modificato in NULLABLE.

Specifica il flag use_legacy_sql=false per utilizzare la sintassi GoogleSQL per la query.

Se la tabella che stai aggiungendo si trova in un set di dati di un progetto diverso da quello predefinito, aggiungi l'ID progetto al nome del set di dati nel seguente formato: PROJECT_ID:DATASET.

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

bq --location=LOCATION query \
--destination_table PROJECT_ID:DATASET.TABLE \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'QUERY'

Sostituisci quanto segue:

• LOCATION: il nome della tua località. Il flag --location è facoltativo. Ad esempio, se utilizzi BigQuery nella regione di Tokyo, imposta il valore del flag su asia-northeast1. Puoi impostare un valore predefinito per la posizione utilizzando il file .bigqueryrc.
• PROJECT_ID: il tuo ID progetto.
• DATASET: il nome del set di dati che contiene la tabella che stai aggiungendo.
• TABLE: il nome della tabella da accodare.
• QUERY: una query in sintassi GoogleSQL.

Esempi:

Inserisci la seguente query di comando mydataset.mytable nel progetto predefinito per accodare i risultati della query a mydataset.mytable2 (anche nel progetto predefinito). Il comando modifica tutte le REQUIRED colonne in tabella di destinazione su NULLABLE.

bq query \
    --destination_table mydataset.mytable2 \
    --append_table \
    --schema_update_option=ALLOW_FIELD_RELAXATION \
    --use_legacy_sql=false \
    'SELECT
       column1,column2
     FROM
       mydataset.mytable'

Inserisci la seguente query mydataset.mytable nel progetto predefinito per accodare i risultati della query a mydataset.mytable2 in myotherproject. Il comando modifica tutte le colonne REQUIRED nella tabella di destinazione in NULLABLE.

bq query \
--destination_table myotherproject:mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

API

Chiama il metodo jobs.insert. Configura un job query e imposta le seguenti proprietà:

• Specifica la tabella di destinazione utilizzando la proprietà destinationTable.
• Imposta l'istruzione di scrittura della tabella di destinazione su WRITE_APPEND usando la proprietà writeDisposition.
• Specifica l'opzione di aggiornamento dello schema utilizzando la proprietà schemaUpdateOptions.
• Specifica la query GoogleSQL utilizzando la proprietà query.

Vai

Prima di provare questo esempio, segui le istruzioni per la configurazione di Go nel Guida rapida di BigQuery con 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"
)

// relaxTableQuery demonstrates relaxing the schema of a table by appending query results to
// enable the table to allow NULL values.
func relaxTableQuery(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()

	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	meta := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	// Now, append a query result that includes nulls, but allow the job to relax
	// all required columns.
	q := client.Query("SELECT \"Beyonce\" as full_name")
	q.QueryConfig.Dst = client.Dataset(datasetID).Table(tableID)
	q.SchemaUpdateOptions = []string{"ALLOW_FIELD_RELAXATION"}
	q.WriteDisposition = bigquery.WriteAppend
	q.Location = "US"
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	_, err = job.Wait(ctx)
	if err != nil {
		return err
	}
	return nil
}

Java

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

Per autenticarti a BigQuery, configura le 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.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobInfo.SchemaUpdateOption;
import com.google.cloud.bigquery.JobInfo.WriteDisposition;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableList;

public class RelaxTableQuery {

  public static void runRelaxTableQuery() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    relaxTableQuery(projectId, datasetName, tableName);
  }

  // To relax all columns in a destination table when you append data to it during a query job
  public static void relaxTableQuery(String projectId, String datasetName, String tableName)
      throws Exception {
    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);

      String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`";
      String query = "SELECT word FROM " + sourceTable + " WHERE word like '%is%'";

      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              // Use standard SQL syntax for queries.
              // See: https://cloud.google.com/bigquery/sql-reference/
              .setUseLegacySql(false)
              .setSchemaUpdateOptions(ImmutableList.of(SchemaUpdateOption.ALLOW_FIELD_RELAXATION))
              .setWriteDisposition(WriteDisposition.WRITE_APPEND)
              .setDestinationTable(tableId)
              .build();

      Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).build());

      queryJob = queryJob.waitFor();

      // Check for errors
      if (queryJob == null) {
        throw new Exception("Job no longer exists");
      } else if (queryJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(queryJob.getStatus().getError().toString());
      }

      // Get the results.
      TableResult results = queryJob.getQueryResults();

      // Print all pages of the results.
      results
          .iterateAll()
          .forEach(
              rows -> {
                rows.forEach(row -> System.out.println("row: " + row.toString()));
              });

      System.out.println("Successfully relaxed all columns in destination table during query job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Columns not relaxed during query job \n" + e.toString());
    }
  }
}

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 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 destination table.
# table_id = "your-project.your_dataset.your_table_name"

# Retrieves the destination table and checks the number of required fields.
table = client.get_table(table_id)  # Make an API request.
original_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)

# In this example, the existing table has 2 required fields.
print("{} fields in the schema are required.".format(original_required_fields))

# Configures the query to append the results to a destination table,
# allowing field relaxation.
job_config = bigquery.QueryJobConfig(
    destination=table_id,
    schema_update_options=[bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION],
    write_disposition=bigquery.WriteDisposition.WRITE_APPEND,
)

# Start the query, passing in the extra configuration.
query_job = client.query(
    # In this example, the existing table contains 'full_name' and 'age' as
    # required columns, but the query results will omit the second column.
    'SELECT "Beyonce" as full_name;',
    job_config=job_config,
)  # Make an API request.
query_job.result()  # Wait for the job to complete.

# Checks the updated number of required fields.
table = client.get_table(table_id)  # Make an API request.
current_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
print("{} fields in the schema are now required.".format(current_required_fields))

Modificare il valore predefinito di una colonna

Per modificare il valore predefinito di una colonna, seleziona una delle seguenti opzioni:

Console

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

   Vai a BigQuery

2. Nel riquadro Explorer, espandi il progetto e il set di dati, quindi seleziona la tabella.

3. Nel riquadro dei dettagli, fai clic sulla scheda Schema.

4. Fai clic su Modifica schema. Potresti dover scorrere per visualizzare questo pulsante.

5. Nella pagina Schema attuale, individua il campo di primo livello che ti interessa. per cambiare.

6. Inserisci il valore predefinito per il campo in questione.

7. Fai clic su Salva.

SQL

Utilizza l'istruzione DDL ALTER COLUMN SET DEFAULT.

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

   Vai a BigQuery

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

   ALTER TABLE mydataset.mytable
   ALTER COLUMN column_name SET DEFAULT default_expression;

3. Fai clic su Esegui.

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

Elimina una colonna

Puoi eliminare una colonna da una tabella esistente utilizzando l'istruzione DDL ALTER TABLE DROP COLUMN.

La dichiarazione non libera immediatamente lo spazio di archiviazione associato nella colonna eliminata. Per saperne di più sull'impatto sullo spazio di archiviazione quando inserisci una colonna nello spazio di archiviazione, consulta i dettagli dell'istruzione ALTER TABLE DROP COLUMN. Esistono due opzioni per recuperare immediatamente lo spazio di archiviazione:

• Sovrascrivi una tabella con una query SELECT * EXCEPT.
• Esporta i dati in Cloud Storage, elimina le colonne indesiderate e caricali in una nuova tabella con lo schema corretto.