Tabellenschemas ändern

In diesem Dokument wird beschrieben, wie Sie die Schemadefinitionen für vorhandene BigQuery-Tabellen ändern können. BigQuery unterstützt nativ die folgenden Schemaänderungen:

  • Spalten zu einer Schemadefinition hinzufügen
  • Modus einer Spalte von REQUIRED auf NULLABLE lockern

Tabellen können ohne Festlegung eines anfänglichen Schemas erstellt werden. Eine Schemadefinition können Sie der Tabelle problemlos später hinzufügen.

Alle anderen Schemaänderungen werden nicht unterstützt und erfordern manuelle Problemumgehungen, einschließlich:

  • Name einer Spalte ändern
  • Datentyp einer Spalte ändern
  • Modus einer Spalte ändern (ausgenommen das Lockern von REQUIRED auf NULLABLE)
  • Spalte löschen

Weitere Informationen zu nicht unterstützten Schemaänderungen, die Problemumgehungen erfordern, finden Sie unter Tabellenschemas manuell ändern.

Spalten zur Schemadefinition einer Tabelle hinzufügen

So können Sie Spalten zur Schemadefinition einer vorhandenen Tabelle hinzufügen:

  • Manuell (erstellt eine leere Spalte)
  • Beim Überschreiben einer Tabelle mit einem Lade- oder Abfragejob
  • Beim Anhängen von Daten an eine Tabelle mit einem Lade- oder Abfragejob

Beim Hinzufügen von Spalten müssen die BigQuery-Regeln für Spaltennamen beachtet werden. Weitere Informationen zum Erstellen von Schemakomponenten finden Sie unter Schema angeben.

Manuell eine leere Spalte hinzufügen

Sie können einer vorhandenen Tabelle auf folgende Weise eine leere Spalte hinzufügen:

  • Mit der Cloud Console oder der klassischen BigQuery-Web-UI
  • Mit dem Befehl bq update des bq-Befehlszeilentools
  • Durch Aufrufen der API-Methode tables.patch
  • Mit den Clientbibliotheken

Wenn Sie einem vorhandenen Tabellenschema neue Spalten hinzufügen, müssen die Spalten den Modus NULLABLE oder REPEATED haben. Sie können einem vorhandenen Tabellenschema keine REQUIRED-Spalte hinzufügen. Wenn Sie versuchen, einem vorhandenen Tabellenschema in der API oder im bq-Befehlszeilentool die Spalte REQUIRED hinzuzufügen, wird der folgende Fehler zurückgegeben: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Cannot add required columns to an existing schema. REQUIRED-Spalten können nur hinzugefügt werden, wenn Sie eine Tabelle beim Laden von Daten erstellen oder wenn Sie eine leere Tabelle mit einer Schemadefinition erstellen.

Nachdem Sie der Schemadefinition einer Tabelle eine neue Spalte hinzugefügt haben, können Sie Daten in die neue Spalte laden. Dazu verwenden Sie Folgendes:

So fügen Sie der Schemadefinition einer Tabelle leere Spalten hinzu:

Console

  1. Wählen Sie im Bereich Resources (Ressourcen) Ihre Tabelle aus.

  2. Scrollen Sie unter dem Abfrageeditor an das Ende des Bereichs Schema und klicken Sie auf Edit schema (Schema bearbeiten).

Tabellenschema bearbeiten

  1. Scrollen Sie an das Ende des sich öffnenden Felds und klicken Sie auf Add field (Feld hinzufügen).

    • Geben Sie unter Name den Spaltennamen ein.
    • Wählen Sie unter Type (Typ) den Datentyp aus.
    • Wählen Sie unter Mode (Modus) NULLABLE oder REPEATED aus.
  2. Wenn Sie die gewünschten Spalten hinzugefügt haben, klicken Sie auf Save (Speichern).

Klassische UI

  1. Wählen Sie im Navigationsbereich Ihre Tabelle aus.

  2. Klicken Sie auf der Seite Table Details (Tabellendetails) auf Add New Fields (Neue Felder hinzufügen).

  3. Im Bereich New Fields (Neue Felder):

    • Geben Sie unter Name den Spaltennamen ein.
    • Wählen Sie unter Type (Typ) den Datentyp aus.
    • Wählen Sie unter Mode (Modus) NULLABLE oder REPEATED aus.

      Tabellenschema aktualisieren

  4. Wenn Sie keine weiteren Spalten hinzufügen möchten, klicken Sie auf Zu Tabelle hinzufügen.

bq

Führen Sie den Befehl bq update aus und geben Sie eine JSON-Schemadatei an. Wenn sich die zu aktualisierende Tabelle in einem anderen Projekt als Ihrem Standardprojekt befindet, fügen Sie dem Dataset-Namen die Projekt-ID im folgenden Format hinzu: project_id:dataset.

bq update project_id:dataset.table schema

Dabei gilt:

  • project_id ist die Projekt-ID.
  • dataset ist der Name des Datasets, das die zu aktualisierende Tabelle enthält.
  • table ist der Name der Tabelle, die Sie aktualisieren.
  • schema ist der Pfad zur JSON-Schemadatei auf Ihrem lokalen Rechner.

Wenn Sie das Schema in der bq-Befehlszeile angeben, können Sie den Typ RECORD (STRUCT) und die Spaltenbeschreibung nicht einfügen. Außerdem ist es dann nicht möglich, den Modus der Spalte anzugeben. Als Modus ist standardmäßig NULLABLE eingestellt.

Wenn Sie versuchen, Spalten mithilfe einer Inline-Schemadefinition hinzuzufügen, müssen Sie die gesamte Schemadefinition einschließlich der neuen Spalten angeben. Da Sie keine Spaltenmodi mithilfe einer Inline-Schemadefinition angeben können, versucht das Update, alle vorhandenen REQUIRED-Spalten in NULLABLE zu ändern. Dies führt zu folgendem Fehler: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Field field has changed mode from REPEATED to NULLABLE.

Die bevorzugte Methode, um Spalten zu einer vorhandenen Tabelle mit dem bq-Befehlszeilentool hinzuzufügen, ist das Bereitstellen einer JSON-Schemadatei.

So fügen Sie dem Schema einer Tabelle mithilfe einer JSON-Schemadatei leere Spalten hinzu:

  1. Führen Sie zuerst den Befehl bq show mit dem Flag --schema aus und schreiben Sie das vorhandene Tabellenschema in eine Datei. Wenn sich die zu aktualisierende Tabelle in einem anderen Projekt als Ihrem Standardprojekt befindet, fügen Sie dem Dataset-Namen die Projekt-ID im folgenden Format hinzu: project_id:dataset.

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

    Dabei gilt:

    • project_id ist die Projekt-ID.
    • dataset ist der Name des Datasets, das die zu aktualisierende Tabelle enthält.
    • table ist der Name der Tabelle, die Sie aktualisieren.
    • schema_file ist die Schemadefinitionsdatei, die auf Ihren lokalen Rechner geschrieben wurde.

    Wenn Sie beispielsweise die Schemadefinition von mydataset.mytable in eine Datei schreiben möchten, geben Sie den folgenden Befehl ein. mydataset.mytable befindet sich in Ihrem Standardprojekt.

       bq show \
       --schema \
       --format=prettyjson \
       mydataset.mytable > /tmp/myschema.json
    
  2. Öffnen Sie die Schemadatei in einem Texteditor. Das Schema sollte so aussehen:

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    
  3. Fügen Sie die neuen Spalten am Ende der Schemadefinition hinzu. Wenn Sie versuchen, neue Spalten an anderer Stelle im Array hinzuzufügen, wird der folgende Fehler zurückgegeben: BigQuery error in update operation: Precondition Failed.

    Mit einer JSON-Datei können Sie für neue Spalten Beschreibungen, die Modi NULLABLE oder REPEATED und RECORD-Typen angeben. Wenn Sie beispielsweise die Schemadefinition aus dem vorherigen Schritt verwenden, sieht Ihr neues JSON-Array so aus. In diesem Beispiel wird die neue column4-Spalte mit dem Namen NULLABLE hinzugefügt. column4 enthält eine Beschreibung.

      [
        {
          "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"
        }
      ]
      

    Weitere Informationen zum Arbeiten mit JSON-Schemadateien finden Sie unter JSON-Schemadatei festlegen.

  4. Nachdem Sie die Schemadatei aktualisiert haben, führen Sie den folgenden Befehl aus, um die Schemadefinition der Tabelle zu aktualisieren. Wenn sich die zu aktualisierende Tabelle in einem anderen Projekt als Ihrem Standardprojekt befindet, fügen Sie dem Dataset-Namen die Projekt-ID im folgenden Format hinzu: project_id:dataset.

    bq update project_id:dataset.table schema
    

    Dabei gilt:

    • project_id ist die Projekt-ID.
    • dataset ist der Name des Datasets, das die zu aktualisierende Tabelle enthält.
    • table ist der Name der Tabelle, die Sie aktualisieren.
    • schema ist der Pfad zur JSON-Schemadatei auf Ihrem lokalen Rechner.

    Geben Sie beispielsweise den folgenden Befehl ein, um die Schemadefinition von mydataset.mytable in Ihrem Standardprojekt zu aktualisieren. Der Pfad zur Schemadatei auf Ihrem lokalen Rechner lautet /tmp/myschema.json.

    bq update mydataset.mytable /tmp/myschema.json
    

API

Rufen Sie die Methode tables.patch auf und verwenden Sie das Attribut schema, um Ihrer Schemadefinition leere Spalten hinzuzufügen. Da die Methode tables.update die gesamte Tabellenressource ersetzt, ist die Methode tables.patch zu bevorzugen.

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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Java in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Java API.

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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Node.js in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Node.js API.


// 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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Python in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Python API.

Fügen Sie an eine Kopie von Table.schema ein neues SchemaField-Objekt an und ersetzen Sie den Wert des Attributs Table.schema durch das aktualisierte Schema.
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.")

Verschachtelte Spalte einem RECORD hinzufügen

Zusätzlich zum Einfügen neuer Spalten in das Schema einer Tabelle können Sie einem RECORD auch neue verschachtelte Spalten hinzufügen. Der Vorgang zum Einfügen einer neuen verschachtelten Spalte ähnelt dem Vorgang zum Hinzufügen einer neuen Spalte.

Console

Das Einfügen eines neuen verschachtelten Felds in eine vorhandene RECORD-Spalte wird derzeit von der Cloud Console nicht unterstützt.

Klassische UI

Das Hinzufügen eines neuen verschachtelten Felds zu einer bestehenden RECORD-Spalte wird derzeit von der BigQuery-Web-UI nicht unterstützt.

bq

Führen Sie den Befehl bq update aus und stellen Sie eine JSON-Schemadatei bereit, die das verschachtelte Feld zur Schemadefinition der vorhandenen Spalte mit dem Modus RECORD hinzufügt. Wenn sich die zu aktualisierende Tabelle in einem anderen Projekt als Ihrem Standardprojekt befindet, fügen Sie dem Dataset-Namen die Projekt-ID im folgenden Format hinzu: project_id:dataset.

bq update project_id:dataset.table schema

Dabei gilt:

  • project_id ist die Projekt-ID.
  • dataset ist der Name des Datasets, das die zu aktualisierende Tabelle enthält.
  • table ist der Name der Tabelle, die Sie aktualisieren.
  • schema ist der Pfad zur JSON-Schemadatei auf Ihrem lokalen Rechner.

Wenn Sie das Schema in der bq-Befehlszeile angeben, können Sie den Typ RECORD (STRUCT) und die Spaltenbeschreibung nicht einfügen. Außerdem ist es dann nicht möglich, den Modus der Spalte anzugeben. Als Modus ist standardmäßig NULLABLE eingestellt. Wenn Sie also einem RECORD eine neue verschachtelte Spalte hinzufügen, müssen Sie eine JSON-Schemadatei bereitstellen.

So fügen Sie einem RECORD mit einer JSON-Schemadatei eine verschachtelte Spalte hinzu:

  1. Führen Sie zuerst den Befehl bq show mit dem Flag --schema aus und schreiben Sie das vorhandene Tabellenschema in eine Datei. Wenn sich die zu aktualisierende Tabelle in einem anderen Projekt als Ihrem Standardprojekt befindet, fügen Sie dem Dataset-Namen die Projekt-ID im folgenden Format hinzu: project_id:dataset.table.

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

    Dabei gilt:

    • project_id ist die Projekt-ID.
    • dataset ist der Name des Datasets, das die zu aktualisierende Tabelle enthält.
    • table ist der Name der Tabelle, die Sie aktualisieren.
    • schema_file ist die Schemadefinitionsdatei, die auf Ihren lokalen Rechner geschrieben wurde.

    Wenn Sie beispielsweise die Schemadefinition von mydataset.mytable in eine Datei schreiben möchten, geben Sie den folgenden Befehl ein. mydataset.mytable befindet sich in Ihrem Standardprojekt.

    bq show \
    --schema \
    --format=prettyjson \
    mydataset.mytable > /tmp/myschema.json
    
  2. Öffnen Sie die Schemadatei in einem Texteditor. Das Schema sollte wie unten dargestellt aussehen. In diesem Beispiel ist column3 eine verschachtelte wiederholte Spalte. Die verschachtelten Spalten sind nested1 und nested2. Das Array fields listet die in column3 verschachtelten Felder auf.

    [
      {
        "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. Fügen Sie die neue verschachtelte Spalte am Ende des Arrays fields hinzu. In diesem Beispiel ist nested3 die neue verschachtelte Spalte.

      [
        {
          "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"
        }
      ]
      

    Weitere Informationen zum Arbeiten mit JSON-Schemadateien finden Sie unter JSON-Schemadatei festlegen.

  4. Nachdem Sie die Schemadatei aktualisiert haben, führen Sie den folgenden Befehl aus, um die Schemadefinition der Tabelle zu aktualisieren. Wenn sich die zu aktualisierende Tabelle in einem anderen Projekt als Ihrem Standardprojekt befindet, fügen Sie dem Dataset-Namen die Projekt-ID im folgenden Format hinzu: project_id:dataset.

    bq update project_id:dataset.table schema
    

    Dabei gilt:

    • project_id ist die Projekt-ID.
    • dataset ist der Name des Datasets, das die zu aktualisierende Tabelle enthält.
    • table ist der Name der Tabelle, die Sie aktualisieren.
    • schema ist der Pfad zur JSON-Schemadatei auf Ihrem lokalen Rechner.

    Geben Sie beispielsweise den folgenden Befehl ein, um die Schemadefinition von mydataset.mytable in Ihrem Standardprojekt zu aktualisieren. Der Pfad zur Schemadatei auf Ihrem lokalen Rechner lautet /tmp/myschema.json.

    bq update mydataset.mytable /tmp/myschema.json
    

API

Rufen Sie die Methode tables.patch auf und verwenden Sie das Attribut schema, um der Schemadefinition die verschachtelten Spalten hinzuzufügen. Da die Methode tables.update die gesamte Tabellenressource ersetzt, ist die Methode tables.patch zu bevorzugen.

Spalte beim Überschreiben oder Anfügen von Daten hinzufügen

Sie können einer vorhandenen Tabelle neue Spalten hinzufügen, wenn Sie Daten in sie laden und die vorhandene Tabelle überschreiben. Wenn Sie eine vorhandene Tabelle überschreiben, wird das dazugehörige Schema mit dem Schema der Daten überschrieben, die Sie laden. Informationen zum Überschreiben einer Tabelle mit einem Ladejob finden Sie hier:

Sie können einer vorhandenen Tabelle auch neue Spalten hinzufügen, wenn Sie Daten mit einem Lade- oder Abfragejob an sie anfügen.

Spalte in einem Lade-/Anfügejob hinzufügen

Sie können, während Sie in einem Ladejob Daten anfügen, einer Tabelle auf folgende Weise Spalten hinzufügen:

  • Mit dem Befehl bq load des bq-Befehlszeilentools
  • Durch Aufrufen der API-Methode jobs.insert und Konfigurieren eines Ladejobs (load)
  • Mit den Clientbibliotheken

Das Einfügen einer Spalte in eine vorhandene Tabelle während eines Anfügevorgangs wird derzeit weder von der Cloud Console noch von der klassischen BigQuery-Web-UI unterstützt.

Wenn Sie einem Ladejob mithilfe eines Anfügevorgangs Spalten hinzufügen, kann das aktualisierte Schema:

  • automatisch erkannt werden (bei CSV- und JSON-Dateien)
  • in einer JSON-Schemadatei angegeben werden (bei CSV- und JSON-Dateien)
  • aus den selbstbeschreibenden Quelldaten für Avro-, ORC-, Parquet- und Cloud Datastore-Exportdateien abgerufen werden

Wenn Sie das Schema in einer JSON-Datei angeben, müssen die neuen Spalten darin definiert sein. Wenn die neuen Spaltendefinitionen fehlen und Sie versuchen, die Daten anzufügen, wird der folgende Fehler zurückgegeben: Error while reading data, error message: parsing error in row starting at position int: No such field: field.

Wenn Sie während eines Anfügevorgangs neue Spalten hinzufügen, werden die Werte in den neuen Spalten für vorhandene Zeilen auf NULL gesetzt.

So erstellen Sie beim Anfügen von Daten während eines Ladejobs eine neue Spalte in einer Tabelle:

Console

Beim Laden von Daten mit der Cloud Console können Sie einer vorhandenen Tabelle keine neuen Spalten hinzufügen.

Klassische UI

Sie können in einer vorhandenen Tabelle keine neuen Spalten erstellen, wenn Sie Daten über die BigQuery-Web-UI laden.

bq

Laden Sie Ihre Daten mit dem Befehl bq load und fügen Sie das Flag --noreplace hinzu, um anzugeben, dass Sie die Daten an eine vorhandene Tabelle anfügen.

Wenn die von Ihnen angefügten Daten im CSV-Format oder im JSON-Format mit Zeilenumbrüchen vorliegen, geben Sie das Flag --autodetect für die automatische Schemaerkennung an oder stellen das Schema in einer JSON-Schemadatei bereit. Die hinzugefügten Spalten können automatisch aus Avro- oder Datastore-Exportdateien übernommen werden.

Setzen Sie das Flag --schema_update_option auf ALLOW_FIELD_ADDITION, um anzugeben, dass die von Ihnen angefügten Daten neue Spalten enthalten.

Wenn sich die anzufügende Tabelle in einem Dataset befindet, das in einem anderen Projekt als Ihrem Standardprojekt enthalten ist, geben Sie im Dataset-Namen die Projekt-ID im folgenden Format ein: project_id:dataset.

Optional: Geben Sie das Flag --location an und legen Sie als Wert Ihren Standort fest.

Geben Sie den Befehl load so ein:

bq --location=location load \
--noreplace \
--autodetect \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=format \
project_id:dataset.table \
path_to_source \
schema

Dabei gilt:

  • location ist der Name Ihres Standorts. Das Flag --location ist optional. Wenn Sie beispielsweise BigQuery in der Region Tokio verwenden, legen Sie den Wert des Flags auf asia-northeast1 fest. Mit der Datei .bigqueryrc können Sie einen Standardwert für den Standort festlegen.
  • format ist NEWLINE_DELIMITED_JSON, CSV, AVRO, PARQUET, ORC oder DATASTORE_BACKUP.
  • project_id ist die Projekt-ID.
  • dataset ist der Name des Datasets, das die Tabelle enthält.
  • table ist der Name der Tabelle, die Sie anfügen.
  • path_to_source ist ein vollständig qualifizierter Cloud Storage-URI, eine durch Kommas getrennte Liste von URIs oder der Pfad zu einer Datendatei auf dem lokalen Rechner.
  • schema ist der Pfad zu einer lokalen JSON-Schemadatei. Eine Schemadatei ist nur für CSV- und JSON-Dateien erforderlich, wenn --autodetect nicht angegeben wurde. Avro- und Datastore-Schemas werden aus den Quelldaten abgeleitet.

Beispiele:

Geben Sie den folgenden Befehl ein, um eine lokale Avro-Datendatei (/tmp/mydata.avro) mithilfe eines Ladejobs an mydataset.mytable anzufügen. Da Schemas automatisch aus Avro-Daten abgeleitet werden können, müssen Sie das Flag --autodetect nicht verwenden. mydataset befindet sich in Ihrem Standardprojekt.

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

Geben Sie den folgenden Befehl ein, um mithilfe eines Ladejobs eine durch Zeilenumbruch getrennte JSON-Datei in Cloud Storage an mydataset.mytable anzufügen. Das Flag --autodetect wird verwendet, um die neuen Spalten zu erkennen. mydataset befindet sich in Ihrem Standardprojekt.

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

Geben Sie den folgenden Befehl ein, um mithilfe eines Ladejobs eine durch Zeilenumbruch getrennte JSON-Datei in Cloud Storage an mydataset.mytable anzufügen. Das Schema mit den neuen Spalten wird in der lokalen JSON-Schemadatei /tmp/myschema.json angegeben. mydataset befindet sich in myotherproject, nicht in Ihrem Standardprojekt.

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

Rufen Sie die Methode jobs.insert auf. Konfigurieren Sie einen Ladejob (load) und legen Sie die folgenden Attribute fest:

  • Verweisen Sie mit dem Attribut sourceUris auf Ihre Daten in Cloud Storage.
  • Geben Sie das Datenformat an. Legen Sie dazu das Attribut sourceFormat fest.
  • Geben Sie das Schema im Attribut schema an.
  • Geben Sie die Aktualisierungsoption des Schemas mit dem Attribut schemaUpdateOptions an.
  • Legen Sie die Schreibanordnung der Zieltabelle mit dem Attribut writeDisposition als WRITE_APPEND fest.

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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Java in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Java API.

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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Node.js in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Node.js API.

// 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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Python in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Python API.

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('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)))

Spalte in einem Abfrage-/Anfügejob hinzufügen

Sie können einer Tabelle beim Anhängen von Abfrageergebnissen auf folgende Weise Spalten hinzufügen:

  • Mit dem Befehl bq query des bq-Befehlszeilentools
  • Durch Aufrufen der API-Methode jobs.insert und Konfigurieren eines Ladejobs (query)
  • Mit den Clientbibliotheken

Das Einfügen einer Spalte während eines Anfügevorgangs wird derzeit weder von der Cloud Console noch von der klassischen BigQuery-Web-UI unterstützt.

Wenn Sie Spalten über einen Anfügevorgang in einem Abfragejob hinzufügen, wird das Schema der Zieltabelle mit dem Schema der Abfrageergebnisse aktualisiert. Beachten Sie, dass Sie nicht eine Tabelle an einem Standort abfragen und die Ergebnisse in eine Tabelle an einem anderen Standort schreiben können.

So erstellen Sie beim Anfügen von Daten an eine Tabelle während eines Abfragejobs eine neue Spalte:

Console

Beim Anfügen von Abfrageergebnissen mit der Cloud Console können Sie einer vorhandenen Tabelle keine neuen Spalten hinzufügen.

Klassische UI

Sie können in einer vorhandenen Tabelle keine neuen Spalten erstellen, wenn Sie Abfrageergebnisse über die klassische BigQuery-Web-UI anfügen.

bq

Verwenden Sie den Befehl bq query, um Ihre Daten abzufragen. Mit dem Flag --destination_table geben Sie an, welche Tabelle Sie anfügen.

Mit dem Flag --append_table können Sie angeben, dass Sie Abfrageergebnisse an eine vorhandene Zieltabelle anfügen.

Setzen Sie das Flag --schema_update_option auf ALLOW_FIELD_ADDITION, um anzugeben, dass die angehängten Abfrageergebnisse neue Spalten enthalten.

Geben Sie das Flag use_legacy_sql=false an, um die Standard-SQL-Syntax für die Abfrage zu verwenden.

Wenn sich die anzufügende Tabelle in einem Dataset befindet, das in einem anderen Projekt als Ihrem Standardprojekt enthalten ist, geben Sie im Dataset-Namen die Projekt-ID im folgenden Format ein: project_id:dataset. Beachten Sie, dass sich die Tabelle, die Sie abfragen, und die Zieltabelle am selben Standort befinden müssen.

Optional: Geben Sie das Flag --location an und legen Sie als Wert Ihren Standort fest.

bq --location=location query \
--destination_table project_id:dataset.table \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'query'

Dabei gilt:

  • location ist der Name Ihres Standorts. Das Flag --location ist optional. Wenn Sie beispielsweise BigQuery in der Region Tokio verwenden, legen Sie den Wert des Flags auf asia-northeast1 fest. Mit der Datei ".bigqueryrc" können Sie einen Standardwert für den Standort festlegen. Beachten Sie, dass Sie Abfrageergebnisse nicht an eine Tabelle an einem anderen Standort anfügen können.
  • project_id ist die Projekt-ID.
  • dataset ist der Name des Datasets, das die von Ihnen angefügte Tabelle enthält.
  • table ist der Name der Tabelle, die Sie anfügen.
  • query ist eine Abfrage in der Standard-SQL-Syntax.

Beispiele:

Geben Sie den folgenden Befehl ein, um mydataset.mytable in Ihrem Standardprojekt abzufragen und die Abfrageergebnisse an mydataset.mytable2 anzufügen. Dies ist auch in Ihrem Standardprojekt möglich.

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

Geben Sie den folgenden Befehl ein, um mydataset.mytable in Ihrem Standardprojekt abzufragen und die Abfrageergebnisse an mydataset.mytable2 in myotherproject anzufügen.

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

Rufen Sie die Methode jobs.insert auf. Konfigurieren Sie einen Ladejob (query) und legen Sie die folgenden Attribute fest:

  • Geben Sie die Zieltabelle mit dem Attribut destinationTable an.
  • Legen Sie die Schreibanordnung der Zieltabelle mit dem Attribut writeDisposition als WRITE_APPEND fest.
  • Geben Sie die Aktualisierungsoption des Schemas mit dem Attribut schemaUpdateOptions an.
  • Geben Sie die Standard-SQL-Abfrage mit dem Attribut query an.

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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Java in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Java API.

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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Node.js in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Node.js API.

// 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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Python in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Python API.

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)))

Modus einer Spalte lockern

Derzeit können Sie den Modus einer Spalte nur von REQUIRED in NULLABLE ändern. Das Ändern des Spaltenmodus von REQUIRED zu NULLABLE wird auch als Spaltenlockerung bezeichnet. So können Sie den Modus REQUIRED für Spalten lockern:

  • Manuell
  • Wenn Sie eine Tabelle mit einem Lade- oder Abfragejob überschreiben
  • Wenn Sie Daten mit einem Abfragejob an eine Tabelle anfügen

Spalten manuell von REQUIRED zu NULLABLE ändern

Sie können manuell den Modus einer Spalte auf folgende Weise von REQUIRED zu NULLABLE ändern:

  • Mit der Cloud Console oder der klassischen BigQuery-Web-UI
  • Mit dem Befehl bq update des bq-Befehlszeilentools
  • Durch Aufruf der API-Methode tables.patch
  • Mit den Clientbibliotheken

So ändern Sie manuell den Modus einer Spalte von REQUIRED zu NULLABLE:

Console

Sie können den Modus einer Spalte derzeit nicht mit der Cloud Console lockern.

Klassische UI

  1. Erweitern Sie Ihr Dataset und wählen Sie Ihre Tabelle aus.

  2. Klicken Sie auf der Seite Table Details (Tabellendetails) auf den Tab Schema.

  3. Klicken Sie rechts neben der gewünschten Spalte auf den Abwärtspfeil und wählen Sie eine der folgenden Optionen aus:

    • Make NULLABLE (Auf NULLABLE setzen) – lockert den Modus einer einzelnen Spalte
    • All REQUIRED to NULLABLE (Alle REQUIRED in NULLABLE) – ändert alle REQUIRED-Spalten in der Schemadefinition in NULLABLE

      Modus einer Spalte lockern

  4. Klicken Sie im Dialogfeld Confirm Mode Change (Modusänderung bestätigen) auf OK, um den Modus in NULLABLE zu ändern. Diese Änderung kann nicht rückgängig gemacht werden.

bq

  1. Führen Sie zuerst den Befehl bq show mit dem Flag --schema aus und schreiben Sie das vorhandene Tabellenschema in eine Datei. Wenn sich die zu aktualisierende Tabelle in einem anderen Projekt als Ihrem Standardprojekt befindet, fügen Sie dem Dataset-Namen die Projekt-ID im folgenden Format hinzu: project_id:dataset.

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

    Dabei gilt:

    • project_id ist die Projekt-ID.
    • dataset ist der Name des Datasets, das die zu aktualisierende Tabelle enthält.
    • table ist der Name der Tabelle, die Sie aktualisieren.
    • schema_file ist die Schemadefinitionsdatei, die auf Ihren lokalen Computer geschrieben wurde.

    Wenn Sie beispielsweise die Schemadefinition von mydataset.mytable in eine Datei schreiben möchten, geben Sie den folgenden Befehl ein. mydataset.mytable befindet sich in Ihrem Standardprojekt.

      bq show \
      --schema \
      --format=prettyjson \
      mydataset.mytable > /tmp/myschema.json
    
  2. Öffnen Sie die Schemadatei in einem Texteditor. Das Schema sollte so aussehen:

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    
  3. Ändern Sie den Modus einer vorhandenen Spalte von REQUIRED zu NULLABLE. In diesem Beispiel wird der Modus für column1 gelockert.

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

    Weitere Informationen zum Arbeiten mit JSON-Schemadateien finden Sie unter JSON-Schemadatei angeben.

  4. Nachdem Sie die Schemadatei aktualisiert haben, führen Sie den folgenden Befehl aus, um die Schemadefinition der Tabelle zu aktualisieren. Wenn sich die zu aktualisierende Tabelle in einem anderen Projekt als Ihrem Standardprojekt befindet, fügen Sie dem Dataset-Namen die Projekt-ID im folgenden Format hinzu: project_id:dataset.

    bq update project_id:dataset.table schema
    

    Dabei gilt:

    • project_id ist die Projekt-ID.
    • dataset ist der Name des Datasets, das die zu aktualisierende Tabelle enthält.
    • table ist der Name der Tabelle, die Sie aktualisieren.
    • schema ist der Pfad zur JSON-Schemadatei auf Ihrem lokalen Rechner.

    Geben Sie beispielsweise den folgenden Befehl ein, um die Schemadefinition von mydataset.mytable in Ihrem Standardprojekt zu aktualisieren. Der Pfad zur Schemadatei auf Ihrem lokalen Rechner lautet /tmp/myschema.json.

      bq update mydataset.mytable /tmp/myschema.json
    

API

Rufen Sie die Methode tables.patch auf und verwenden Sie das Attribut schema, um eine REQUIRED-Spalte in Ihrer Schemadefinition in NULLABLE zu ändern. Da die Methode tables.update die gesamte Tabellenressource ersetzt, ist die Methode tables.patch zu bevorzugen.

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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Java in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Java API.

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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Node.js in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Node.js API.

// 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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Python in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Python API.

Überschreiben Sie das Attribut Table.schema mit einer Liste von SchemaField-Objekten. Dabei muss für das Attribut "mode" der Wert 'NULLABLE' festgelegt sein.

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'
# table_id = 'my_table'

original_schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]
table_ref = client.dataset(dataset_id).table(table_id)
table = bigquery.Table(table_ref, schema=original_schema)
table = client.create_table(table)
assert all(field.mode == "REQUIRED" for field in table.schema)

# SchemaField properties cannot be edited after initialization.
# To make changes, construct new SchemaField objects.
relaxed_schema = [
    bigquery.SchemaField("full_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("age", "INTEGER", mode="NULLABLE"),
]
table.schema = relaxed_schema
table = client.update_table(table, ["schema"])

assert all(field.mode == "NULLABLE" for field in table.schema)

In einem Lade- oder Abfragejob REQUIRED zu NULLABLE ändern

Sie können im Schema einer vorhandenen Tabelle Spalten mit dem Modus REQUIRED zu NULLABLE lockern, wenn Sie Daten in das Schema laden und die vorhandene Tabelle überschreiben. Wenn Sie eine vorhandene Tabelle überschreiben, wird das dazugehörige Schema mit dem Schema der Daten überschrieben, die Sie laden. Informationen zum Überschreiben einer Tabelle mit einem Ladejob finden Sie hier:

Sie können im Schema einer vorhandenen Tabelle auch Spalten mit dem Modus REQUIRED zu NULLABLE lockern, indem Sie Daten mithilfe eines Abfragejobs anfügen.

In einem Anfüge-/Ladejob REQUIRED zu NULLABLE ändern

Sie können den Modus einer Spalte beim Anhängen von Daten an eine Tabelle in einem Ladejob auf folgende Weise lockern:

  • Mit dem Befehl bq load des bq-Befehlszeilentools
  • Durch Aufrufen der Methode jobs.insert und Konfigurieren eines Ladejobs (load)
  • Mit den Clientbibliotheken

Das Ändern des Modus einer Spalte während eines Anfügevorgangs wird derzeit weder von der Cloud Console noch von der klassischen BigQuery-Web-UI unterstützt.

Wenn Sie den Modus einer Spalte in einem Ladejob mithilfe eines Anfügevorgangs lockern, können Sie

  • den Modus für einzelne Spalten lockern, indem Sie eine JSON-Schemadatei angeben (beim Anfügen von Daten aus CSV- und JSON-Dateien) und
  • die Spalten in Ihrem Avro-, ORC- oder Parquet-Schema zu null lockern und Schemainferenz erlauben, um gelockerte Spalten zu erkennen.

So können Sie eine Spalte von REQUIRED zu NULLABLE lockern, wenn Sie während eines Ladejobs Daten an eine Tabelle anfügen:

Console

Sie können den Modus einer Spalte derzeit nicht mit der Cloud Console lockern.

Klassische UI

Sie können den Modus einer vorhandenen Spalte nicht lockern, wenn Sie über die BigQuery-Web-UI in einem Ladejob Daten an eine Tabelle anfügen.

bq

Laden Sie Ihre Daten mit dem Befehl bq load und fügen Sie das Flag --noreplace hinzu, um anzugeben, dass Sie die Daten an eine vorhandene Tabelle anfügen.

Wenn die von Ihnen angefügten Daten im CSV-Format oder im JSON-Format mit Zeilenumbrüchen vorliegen, geben Sie die gelockerten Spalten in einer lokalen JSON-Schemadatei an oder verwenden das Flag --autodetect, um gelockerte Spalten in den Quelldaten mit der Schemaerkennung zu ermitteln. Informationen dazu, wie Sie die Modi von Spalten mit einer JSON-Schemadatei lockern, finden Sie unter Spalten manuell von REQUIRED in NULLABLE ändern.

Gelockerte Spalten können automatisch aus Avro-, ORC- und Parquet-Dateien abgeleitet werden. Das Lockern von Spalten gilt nicht für das Anfügen von Exportdaten aus Datastore. Die Spalten in Tabellen, die beim Laden aus Datastore-Exportdateien erstellt wurden, sind immer NULLABLE.

Setzen Sie das Flag --schema_update_option auf ALLOW_FIELD_RELAXATION, um anzugeben, dass die von Ihnen angefügten Daten neue Spalten enthalten.

Wenn sich die anzufügende Tabelle in einem Dataset befindet, das in einem anderen Projekt als Ihrem Standardprojekt enthalten ist, geben Sie im Dataset-Namen die Projekt-ID im folgenden Format ein: project_id:dataset.

Optional: Geben Sie das Flag --location an und legen Sie als Wert Ihren Standort fest.

Geben Sie den Befehl load so ein:

bq --location=location load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=format \
project_id:dataset.table \
path_to_source \
schema

Dabei gilt:

  • location ist der Name Ihres Standorts. Das Flag --location ist optional. Wenn Sie beispielsweise BigQuery in der Region Tokio verwenden, legen Sie den Wert des Flags auf asia-northeast1 fest. Mit der Datei .bigqueryrc können Sie einen Standardwert für den Standort festlegen.
  • format ist NEWLINE_DELIMITED_JSON, CSV, PARQUET, ORC oder AVRO. DATASTORE_BACKUP-Dateien erfordern keine Spaltenlockerung. Die Spalten in Tabellen, die aus Datastore-Exportdateien erstellt wurden, sind immer NULLABLE.
  • project_id ist die Projekt-ID.
  • dataset ist der Name des Datasets, das die Tabelle enthält.
  • table ist der Name der Tabelle, die Sie anfügen.
  • path_to_source ist ein vollständig qualifizierter Cloud Storage-URI, eine durch Kommas getrennte Liste von URIs oder der Pfad zu einer Datendatei auf dem lokalen Rechner.
  • schema ist der Pfad zu einer lokalen JSON-Schemadatei. Diese Option wird nur für CSV- und JSON-Dateien verwendet. Aus Avro-Dateien werden gelockerte Spalten automatisch abgeleitet.

Beispiele:

Geben Sie den folgenden Befehl ein, um eine lokale Avro-Datendatei (/tmp/mydata.avro) mithilfe eines Ladejobs an mydataset.mytable anzufügen. Da gelockerte Spalten automatisch aus Avro-Daten abgeleitet werden können, müssen Sie keine Schemadatei angeben. mydataset befindet sich in Ihrem Standardprojekt.

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

Geben Sie den folgenden Befehl ein, um Daten aus einer durch Zeilenumbrüche getrennten JSON-Datei in Cloud Storage mithilfe eines Ladejobs an mydataset.mytable anzufügen. Das Schema mit den gelockerten Spalten befindet sich in einer lokalen JSON-Schemadatei – /tmp/myschema.json. mydataset befindet sich in Ihrem Standardprojekt.

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

Geben Sie den folgenden Befehl ein, um Daten aus einer CSV-Datei auf Ihrem lokalen Rechner mithilfe eines Ladejobs an mydataset.mytable anzufügen. Der Befehl verwendet die automatische Schemaerkennung, um gelockerte Spalten in den Quelldaten zu ermitteln. mydataset befindet sich in myotherproject, nicht in Ihrem Standardprojekt.

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

API

Rufen Sie die Methode jobs.insert auf. Konfigurieren Sie einen Ladejob (load) und legen Sie die folgenden Attribute fest:

  • Verweisen Sie mit dem Attribut sourceUris auf Ihre Daten in Cloud Storage.
  • Geben Sie das Datenformat an. Legen Sie dazu das Attribut sourceFormat fest.
  • Geben Sie das Schema im Attribut schema an.
  • Geben Sie die Aktualisierungsoption des Schemas mit dem Attribut schemaUpdateOptions an.
  • Legen Sie die Schreibanordnung der Zieltabelle mit dem Attribut writeDisposition als WRITE_APPEND fest.

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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Java in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Java API.

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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Node.js in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Node.js API.

// 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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Python in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Python API.

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('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))

In einem Abfrage-/Anfügejob REQUIRED zu NULLABLE ändern

Sie können auf folgende Weise alle Spalten in einer Tabelle lockern, während Sie Abfrageergebnisse an diese anhängen:

  • Mit dem Befehl bq query des bq-Befehlszeilentools
  • Durch Aufrufen der API-Methode jobs.insert und Konfigurieren eines Abfragejobs (query)
  • Mit den Clientbibliotheken

Das Lockern von Spalten während eines Anfügevorgangs wird derzeit weder von der Cloud Console noch von der klassischen BigQuery-Web-UI unterstützt.

Wenn Sie Spalten über einen Anfügevorgang in einem Abfragejob lockern, können Sie alle Pflichtfelder in der Zieltabelle lockern. Setzen Sie dazu das Flag --schema_update_option auf ALLOW_FIELD_RELAXATION. Einzelne Spalten in einer Zieltabelle können nicht mit einem Abfrage-/Anfügejob gelockert werden.

So lockern Sie alle Spalten in einer Zieltabelle, wenn Sie während eines Abfragejobs Daten an sie anfügen:

Console

Sie können den Modus einer Spalte derzeit nicht mit der Cloud Console lockern.

Klassische UI

Sie können Spalten in einer Zieltabelle nicht lockern, wenn Sie Abfrageergebnisse über die BigQuery-Web-UI anfügen.

bq

Verwenden Sie den Befehl bq query, um Ihre Daten abzufragen. Mit dem Flag --destination_table geben Sie an, welche Tabelle Sie anfügen.

Mit dem Flag --append_table können Sie angeben, dass Sie Abfrageergebnisse an eine vorhandene Zieltabelle anfügen.

Setzen Sie das Flag --schema_update_option auf ALLOW_FIELD_RELAXATION, um anzugeben, dass alle REQUIRED-Spalten in der Tabelle, die Sie anfügen, in NULLABLE geändert werden müssen.

Geben Sie das Flag use_legacy_sql=false an, um die Standard-SQL-Syntax für die Abfrage zu verwenden.

Wenn sich die anzufügende Tabelle in einem Dataset befindet, das in einem anderen Projekt als Ihrem Standardprojekt enthalten ist, geben Sie im Dataset-Namen die Projekt-ID im folgenden Format ein: project_id:dataset.

Optional: Geben Sie das Flag --location an und legen Sie als Wert Ihren Standort fest.

bq --location=location query \
--destination_table project_id:dataset.table \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'query'

Dabei gilt:

  • location ist der Name Ihres Standorts. Das Flag --location ist optional. Wenn Sie beispielsweise BigQuery in der Region Tokio verwenden, legen Sie den Wert des Flags auf asia-northeast1 fest. Mit der Datei .bigqueryrc können Sie einen Standardwert für den Standort festlegen.
  • project_id ist die Projekt-ID.
  • dataset ist der Name des Datasets, das die von Ihnen angefügte Tabelle enthält.
  • table ist der Name der Tabelle, die Sie anfügen.
  • query ist eine Abfrage in der Standard-SQL-Syntax.

Beispiele:

Geben Sie den folgenden Befehl ein, um mydataset.mytable in Ihrem Standardprojekt abzufragen und die Abfrageergebnisse an mydataset.mytable2 anzufügen. Dies ist auch in Ihrem Standardprojekt möglich. Der Befehl ändert alle REQUIRED-Spalten in der Zieltabelle zu 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'

Geben Sie den folgenden Befehl ein, um mydataset.mytable in Ihrem Standardprojekt abzufragen und die Abfrageergebnisse an mydataset.mytable2 in myotherproject anzufügen. Der Befehl ändert alle REQUIRED-Spalten in der Zieltabelle zu 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

Rufen Sie die Methode jobs.insert auf. Konfigurieren Sie einen Ladejob (query) und legen Sie die folgenden Attribute fest:

  • Geben Sie die Zieltabelle mit dem Attribut destinationTable an.
  • Legen Sie die Schreibanordnung der Zieltabelle mit dem Attribut writeDisposition als WRITE_APPEND fest.
  • Geben Sie die Aktualisierungsoption des Schemas mit dem Attribut schemaUpdateOptions an.
  • Geben Sie die Standard-SQL-Abfrage mit dem Attribut query an.

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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Java in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Java API.

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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Python in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Python API.

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))