Modifica degli schemi delle tabelle

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

Puoi apportare la maggior parte delle modifiche allo schema descritte in questo documento utilizzando le istruzioni Data Definition Language (DDL). Queste istruzioni non comportano addebiti.

Puoi modificare uno schema di tabella in tutti i modi descritti in questa pagina exporting i dati della tabella in Cloud Storage per poi caricarli in una nuova tabella con la definizione dello schema modificata. I job di caricamento ed esportazione di BigQuery sono gratuiti, ma ti vengono addebitati dei costi per l'archiviazione dei dati esportati in Cloud Storage. Le seguenti sezioni descrivono altri modi per eseguire vari tipi di modifiche allo schema.

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 query.
  • Aggiungi dati a una tabella con un job di caricamento o 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 Specifica di uno schema.

Aggiungi 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. L'aggiunta di una colonna REQUIRED a uno schema di tabella esistente nello strumento a riga di comando API o bq causa un errore. Tuttavia, puoi creare una colonna REQUIRED nidificata come parte di un nuovo campo RECORD. Puoi aggiungere colonne REQUIRED solo quando crei una tabella durante il caricamento dei dati o quando crei una tabella vuota con una definizione di 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, poi seleziona la tabella.

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

  4. Fai clic su Modifica schema. È possibile che sia necessario scorrere per visualizzare questo pulsante.

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

    • In Nome, digita il nome della colonna.
    • In Tipo, scegli il tipo di dati.
    • In corrispondenza di Modalità, scegli NULLABLE o REPEATED.
  6. Quando hai finito di aggiungere colonne, fai clic su Salva.

SQL

Utilizza l'istruzione DDL ALTER TABLE ADD COLUMN:

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

    Vai a BigQuery

  2. Nell'Editor 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.

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 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 contenente la tabella che stai aggiornando.
  • TABLE: il nome della tabella che stai aggiornando.
  • SCHEMA: il percorso del file di schema JSON sulla tua macchina locale.

Quando specifichi uno schema incorporato, non puoi specificare la descrizione, la modalità e il tipo di colonna RECORD (STRUCT). Tutte le modalità colonna sono impostate automaticamente su NULLABLE. Di conseguenza, se aggiungi una nuova colonna nidificata a un elemento RECORD, devi fornire un file di schema JSON.

Se tenti di aggiungere colonne utilizzando una definizione di schema in linea, devi fornire l'intera definizione di schema, comprese le nuove colonne. Poiché non è possibile specificare le modalità colonna utilizzando una definizione di schema in linea, l'aggiornamento cambia tutte le colonne REPEATED esistenti in NULLABLE, il che 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 è 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 lo schema della tabella esistente su un file. 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 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 contenente la tabella che stai aggiornando.
    • TABLE: il nome della tabella che stai aggiornando.
    • SCHEMA: il file di definizione dello schema scritto nella tua macchina locale.

    Ad esempio, per scrivere la definizione dello schema di mydataset.mytable in un file, inserisci il seguente comando. mydataset.mytable è 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. Aggiungi le nuove colonne alla fine della definizione dello schema. Se tenti di aggiungere nuove colonne in un altro punto dell'array, viene restituito il seguente errore: BigQuery error in update operation: Precondition Failed.

    Utilizzando un file JSON, puoi specificare descrizioni, modalità NULLABLE o REPEATED e tipi RECORD per le nuove colonne. Ad esempio, utilizzando la definizione dello schema nel passaggio precedente, il nuovo array JSON sarà simile al seguente. In questo esempio, viene aggiunta una nuova colonna NULLABLE denominata 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 maggiori informazioni sull'utilizzo dei file di schema JSON, consulta Specifica di un file di schema JSON.

  4. Dopo aver aggiornato il file dello 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 contenente la tabella che stai aggiornando.
    • TABLE: il nome della tabella che stai aggiornando.
    • SCHEMA: il file di definizione dello schema scritto nella tua macchina locale.

    Ad esempio, inserisci il comando seguente per aggiornare la definizione dello schema di mydataset.mytable nel progetto predefinito. Il percorso del file di 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 della tabella, è preferibile il metodo tables.patch.

Go

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 di Java disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Java.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, 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 di Node.js disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Node.js.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, 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 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 di Python disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Python.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.

Aggiungi un nuovo oggetto SchemaField a una copia di Table.schema e sostituisci 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.")

Aggiungi 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 è simile a quella per aggiungere una nuova colonna.

Console

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

SQL

L'aggiunta di un nuovo campo nidificato a una colonna RECORD esistente mediante 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 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 contenente la tabella che stai aggiornando.
  • TABLE: il nome della tabella che stai aggiornando.
  • SCHEMA : il percorso del file di schema JSON sulla tua macchina locale.

Quando specifichi uno schema incorporato, non puoi specificare la descrizione, la modalità e il tipo di colonna RECORD (STRUCT). Tutte le modalità colonna sono impostate automaticamente su NULLABLE. Di conseguenza, se aggiungi una nuova colonna nidificata a un elemento RECORD, devi fornire 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 lo schema della tabella esistente su un file. 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.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 contenente la tabella che stai aggiornando.
    • TABLE: il nome della tabella che stai aggiornando.
    • SCHEMA: il file di definizione dello schema scritto nella tua macchina locale.

    Ad esempio, per scrivere la definizione dello schema di mydataset.mytable in un file, inserisci il seguente comando. mydataset.mytable è 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 essere simile al seguente. 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 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 maggiori informazioni sull'utilizzo dei file di schema JSON, consulta Specifica di un file di schema JSON.

  4. Dopo aver aggiornato il file dello 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 contenente la tabella che stai aggiornando.
    • TABLE: il nome della tabella che stai aggiornando.
    • SCHEMA: il percorso del file di schema JSON sulla tua macchina locale.

    Ad esempio, inserisci il comando seguente per aggiornare la definizione dello schema di mydataset.mytable nel progetto predefinito. Il percorso del file di 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 le colonne nidificate alla definizione dello schema. Poiché il metodo tables.update sostituisce l'intera risorsa della tabella, è preferibile il metodo tables.patch.

Aggiungere colonne quando sovrascrivi o aggiungi dati

Puoi aggiungere nuove colonne a una tabella esistente quando carichi 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 sulla sovrascrittura di una tabella utilizzando un job di caricamento, consulta il documento relativo al formato dei dati:

Aggiungere colonne in un job di caricamento di accodamento

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

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

Se specifichi lo schema in un file JSON, le nuove colonne devono essere definite al suo interno. Se mancano le nuove definizioni della colonna, viene restituito un errore quando provi ad aggiungere i 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 oppure fornisci lo schema in un file JSON di schema. Le colonne aggiunte possono essere dedotte 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 in 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 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 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 il 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 aggiungere.
  • 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 JSON di schema locale. Un file di 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 comando seguente 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, non è necessario 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 comando seguente per aggiungere un file di dati JSON delimitato da nuova riga in Cloud Storage a mydataset.mytable utilizzando un job di caricamento. Il flag --autodetect viene utilizzato per rilevare le nuove colonne. mydataset è nel tuo 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 comando seguente per aggiungere un file di dati JSON delimitato da nuova riga in Cloud Storage a mydataset.mytable utilizzando un job di caricamento. Lo schema contenente le nuove colonne viene specificato in un file di schema JSON locale, /tmp/myschema.json. mydataset si trova in myotherproject, non è il tuo 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

Richiama il metodo 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 la disposizione di scrittura della tabella di destinazione su WRITE_APPEND utilizzando la proprietà writeDisposition.

Go

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 di configurazione di Java disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Java.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, 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.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 di Node.js disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Node.js.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, 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 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 di Python disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Python.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare 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 quando aggiungi 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 località.

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

bq

Utilizza il comando bq query per eseguire query sui dati e specifica il flag --destination_table per indicare la tabella da aggiungere.

Per indicare 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_ADDITION per indicare che i risultati della query che aggiungi contengono 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 in 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 stai eseguendo la query e quella di destinazione devono trovarsi nella stessa località.

(Facoltativo) Fornisci il flag --location e imposta il valore sulla tua 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 località utilizzando il file.bigqueryrc. Tieni presente che non puoi aggiungere i risultati della query a una tabella in un'altra posizione.
  • PROJECT_ID: il tuo ID progetto.
  • dataset: il nome del set di dati contenente la tabella che stai aggiungendo.
  • TABLE: il nome della tabella da aggiungere.
  • QUERY: una query con la sintassi GoogleSQL.

Esempi:

Inserisci il seguente comando per eseguire una query su mydataset.mytable nel progetto predefinito e per aggiungere 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 comando seguente per eseguire una query su mydataset.mytable nel progetto predefinito e aggiungere 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

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

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

Go

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 di configurazione di Java disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Java.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, 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.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 di Node.js disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Node.js.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, 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 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 di configurazione di Python disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Python.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, 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. Nell'esempio seguente la colonna old_name viene rinominata in new_name il giorno mytable:

ALTER TABLE mydataset.mytable
  RENAME COLUMN old_name TO new_name;

Per ulteriori informazioni sulle istruzioni ALTER TABLE RENAME COLUMN, consulta i 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 tenti di aggiornare una tabella applicando uno schema che specifica un nuovo tipo di dati per una colonna, viene restituito un errore.

Forzare il tipo di dati di una colonna

Per modificare il tipo di dati di una colonna in un tipo coercibile, utilizza l'istruzione DDL ALTER COLUMN SET DATA TYPE. Per le conversioni dei tipi di dati supportati, consulta Dettagli.

L'esempio seguente crea una tabella con una colonna di tipo INT64 e poi aggiorna il tipo in NUMERIC:

CREATE TABLE mydataset.mytable(c1 INT64);

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

Trasmettere il tipo di dati di una colonna

Per cambiare il tipo di dati di una colonna in un tipo castable, utilizza una query SQL per selezionare i dati della tabella, trasmettere la colonna pertinente e sovrascrivere la tabella. La trasmissione e la sovrascrittura non sono consigliate per tabelle molto grandi, perché richiedono una scansione completa della tabella.

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

Quando utilizzi CAST, una query può avere esito negativo se BigQuery non è in grado di eseguire la trasmissione. 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. Nell'Editor query, inserisci la query seguente per selezionare tutti i dati di column_two e column_three in mydataset.mytable e per trasmettere column_one da DATE a STRING. La query utilizza un alias per trasmettere column_one con lo stesso nome. mydataset.mytable è nel tuo 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, procedi nel seguente modo:

    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. Per Set di dati, scegli mydataset.

    4. Nel campo ID tabella, inserisci mytable.

    5. In Preferenza di scrittura della tabella di destinazione, seleziona Sovrascrivi tabella. Questa opzione sovrascrive mytable utilizzando i risultati della query.

  5. Se vuoi, scegli la posizione dei tuoi 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 di column_two e column_three in mydataset.mytable e trasmettere column_one da DATE a STRING. La query utilizza un alias per trasmettere column_one con lo stesso nome. mydataset.mytable è nel tuo progetto predefinito.

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 per utilizzare la sintassi GoogleSQL.

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

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 trasmettere column_one da DATE a STRING, chiama il metodo jobs.insert e configura un job query. (Facoltativo) Specifica la tua località nella proprietà 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 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 è quella da REQUIRED a NULLABLE. La modifica della modalità di una colonna da REQUIRED a NULLABLE è anche chiamata rilassamento della colonna. Puoi anche ridurre una colonna quando carichi dati per sovrascrivere una tabella esistente o quando aggiungi dati a una tabella esistente. Non puoi cambiare la modalità di una colonna da NULLABLE a REQUIRED.

Crea una colonna NULLABLE in una tabella esistente

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

Console

  1. Vai alla pagina BigQuery.

    Vai a BigQuery

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

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

  4. Fai clic su Modifica schema. È possibile che sia necessario scorrere per visualizzare questo pulsante.

  5. Nella pagina Schema corrente, individua il campo che vuoi modificare.

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

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

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 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 lo schema della tabella esistente su un file. 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 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 contenente la tabella che stai aggiornando.
    • TABLE: il nome della tabella che stai aggiornando.
    • SCHEMA_FILE: il file di definizione dello schema scritto sulla tua macchina locale.

    Ad esempio, per scrivere la definizione dello schema di mydataset.mytable in un file, inserisci il seguente comando. mydataset.mytable è 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. Cambia la modalità di una colonna esistente da REQUIRED a NULLABLE. In questo esempio, la modalità per column1 è confortevole.

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

    Per saperne di più sull'utilizzo dei file di schema JSON, consulta Specifica di un file di schema JSON.

  4. Dopo aver aggiornato il file dello 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 contenente la tabella che stai aggiornando.
    • TABLE: il nome della tabella che stai aggiornando.
    • SCHEMA: il percorso del file di schema JSON sulla tua macchina locale.

    Ad esempio, inserisci il comando seguente per aggiornare la definizione dello schema di mydataset.mytable nel progetto predefinito. Il percorso del file di schema sulla tua macchina 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 nella definizione dello schema. Poiché il metodo tables.update sostituisce l'intera risorsa della tabella, è preferibile il metodo tables.patch.

Go

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 di Java disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Java.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, 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.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 di configurazione di Node.js disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Node.js.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, 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 di Python disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Python.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.

Sovrascrivi la proprietà Table.schema con un elenco di oggetti SchemaField con la proprietà mode 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 aggiuntivo

Puoi rendere più flessibile 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, riduci la modalità per le singole colonne specificando un file di schema JSON.
  • Quando aggiungi dati da file Avro, ORC o Parquet, rilassa le colonne in NULL nello schema e lascia che l'inferenza dello schema rilevi le colonne ridotte.

Per ridurre 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 ridurre 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 confortevoli in un file di schema JSON locale oppure utilizza il flag --autodetect per utilizzare il rilevamento di schema per rilevare colonne confortevoli nei dati di origine.

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

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

Se la tabella che stai aggiungendo si trova in un set di dati in 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 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 il file.bigqueryrc.
  • FORMAT: NEWLINE_DELIMITED_JSON, CSV, PARQUET, ORC o AVRO. I file DATASTORE_BACKUP non richiedono l'allentamento della colonna. Le colonne nelle tabelle create dai file di esportazione di 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 che stai aggiungendo.
  • 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 JSON di schema locale. Questa opzione viene utilizzata solo per i file CSV e JSON. Le colonne confortevoli vengono dedotte automaticamente dai file Avro.

Esempi:

Inserisci il comando seguente per aggiungere un file di dati Avro locale, /tmp/mydata.avro, a mydataset.mytable utilizzando un job di caricamento. Poiché le colonne rilassate possono essere dedotte automaticamente dai dati Avro, non è necessario specificare un file di 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 comando seguente per aggiungere i dati di un file JSON delimitato da una nuova riga in Cloud Storage a mydataset.mytable utilizzando un job di caricamento. Lo schema contenente le colonne confortevoli è in un file di schema JSON locale: /tmp/myschema.json. mydataset è 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 macchina locale a mydataset.mytable utilizzando un job di caricamento. Il comando utilizza il rilevamento automatico dello schema per rilevare colonne semplici nei dati di origine. mydataset si trova in myotherproject, non nel progetto predefinito.

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

API

Richiama il metodo 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 la disposizione di scrittura della tabella di destinazione su WRITE_APPEND utilizzando la proprietà writeDisposition.

Go

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 di Java disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Java.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, 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.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 di configurazione di Node.js disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Node.js.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, 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 di Python disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Python.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare 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))

Imposta tutte le colonne come NULLABLE con un job di aggiunta

Puoi rendere più flessibili tutte le colonne di una tabella quando aggiungi i risultati della query. Puoi rilassare tutti i campi obbligatori nella tabella di destinazione impostando il flag --schema_update_option su ALLOW_FIELD_RELAXATION. Non puoi allentare singole colonne in una tabella di destinazione utilizzando un'aggiunta di query. Per ridurre le singole colonne con un job di caricamento di aggiunta, consulta Creare una colonna NULLABLE con un job di aggiunta.

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

Console

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

bq

Utilizza il comando bq query per eseguire query sui dati e specifica il flag --destination_table per indicare la tabella da aggiungere.

Per indicare 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 per indicare che tutte le REQUIRED colonne della tabella che stai aggiungendo devono essere modificate 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 in 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 località.

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 località utilizzando il file.bigqueryrc.
  • PROJECT_ID: il tuo ID progetto.
  • DATASET: il nome del set di dati contenente la tabella che stai aggiungendo.
  • TABLE: il nome della tabella da aggiungere.
  • QUERY: una query con la sintassi GoogleSQL.

Esempi:

Inserisci la seguente query di comando mydataset.mytable nel progetto predefinito per aggiungere i risultati della query a mydataset.mytable2 (anche nel progetto predefinito). Il comando modifica tutte le colonne REQUIRED nella tabella di destinazione in 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 di comando mydataset.mytable nel progetto predefinito e per aggiungere 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

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

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

Go

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 di configurazione di Java disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Java.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, 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.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 di Python disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Python.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, 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, poi seleziona la tabella.

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

  4. Fai clic su Modifica schema. È possibile che sia necessario scorrere per visualizzare questo pulsante.

  5. Nella pagina Schema corrente, individua il campo di primo livello che vuoi modificare.

  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 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 le 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.

L'istruzione non libera immediatamente lo spazio di archiviazione associato alla colonna eliminata. Per saperne di più sull'impatto sull'archiviazione di una colonna, 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 poi caricali in una nuova tabella con lo schema corretto.