Verschachtelte und wiederkehrende Spalten angeben

BigQuery ist mit denormalisierten Daten am leistungsfähigsten. Anstatt relationale Schemas wie Stern- oder Schneeflockenschemas beizubehalten, sollten Sie Ihre Daten denormalisieren und verschachtelte und wiederkehrende Spalten nutzen. Verschachtelte und wiederkehrenden Spalten können Beziehungen bewahren, ohne dass es zu Leistungsbeeinträchtigungen wie bei relationalen bzw. normalisierten Schemas kommt.

Sie können verschachtelte oder verschachtelte und wiederkehrende Daten in der Benutzeroberfläche oder in einer JSON-Schemadatei angeben. Um verschachtelte oder verschachtelte und wiederkehrende Spalten anzugeben, verwenden Sie den Datentyp RECORD (STRUCT).

Verschachtelte und wiederkehrende Spalten angeben

BigQuery unterstützt das Laden verschachtelter und wiederkehrender Daten aus Quellformaten, die objektbasierte Schemas unterstützen. Das sind etwa JSON- und Avro-Dateien sowie Firestore- und Datastore-Exportdateien.

Beispielsweise werden in einer relationalen Datenbank zur Erfassung der Bücher einer Bibliothek die Autoreninformationen vermutlich in einer separaten Tabelle gespeichert. In einem solchen Fall wird mit einem Schlüssel wie author_id das Buch mit den Autoren verknüpft.

In BigQuery können Sie die Beziehung zwischen Buch und Autor beibehalten, ohne eine eigene Autorentabelle erstellen zu müssen. Stattdessen erstellen Sie eine Autorenspalte und verschachteln darin Felder wie den Vornamen, den Nachnamen und das Geburtsdatum des Autors. Wenn ein Buch mehrere Autoren hat, können Sie die verschachtelte Autorenspalte zu einer wiederkehrenden Spalte machen.

Für das Erstellen einer Spalte mit verschachtelten und wiederkehrenden Daten legen Sie als Datentyp für die Spalte den Wert RECORD fest. RECORD-Daten werden als STRUCT gespeichert und können als STRUCT in Standard-SQL verwendet werden. STRUCT ist ein Container mit geordneten Feldern, die jeweils einen Typ (erforderlich) und einen Namen (optional) enthalten. Zum Verschachteln der Spalte fügen Sie dem übergeordneten Element RECORD untergeordnete Felder hinzu. Wenn Sie aus der Spalte eine wiederkehrende Spalte machen möchten, ändern Sie den Modus in REPEATED.

Einschränkungen

Für verschachtelte und wiederholte Schemas gelten folgende Einschränkungen:

Wenn Sie verschachtelte und wiederkehrende Daten laden, darf Ihr Schema nicht mehr als 15 Ebenen verschachtelter STRUCTs bzw. RECORD-Typen enthalten.
BigQuery unterstützt Spalten vom Typ STRUCT oder RECORD. Ein STRUCT ist ein komplexer Typ, mit dem ein Objekt mit mehreren untergeordneten Spalten dargestellt werden kann. In einer Spalte vom Typ STRUCT können Sie auch eine oder mehrere untergeordnete Spalten als STRUCT definieren. Diese werden dann als verschachtelte oder eingebettete STRUCTs bezeichnet. Wenn Sie STRUCTS verschachteln, wird die Verschachtelungstiefe in BigQuery auf 15 Ebenen begrenzt. Das Limit für die Verschachtelungstiefe gilt dabei unabhängig davon, ob die STRUCTs skalar oder arraybasiert sind.

Beispiel

Beispielschema

Es folgen Beispiele für verschachtelte und wiederkehrende Daten. Diese Tabelle enthält Informationen zu Personen. Sie umfasst die folgenden Felder:

  • id
  • first_name
  • last_name
  • dob (Geburtsdatum)
  • addresses (verschachteltes und wiederkehrendes Feld)
    • addresses.status (aktueller oder vorheriger Status)
    • addresses.address
    • addresses.city
    • addresses.state
    • addresses.zip
    • addresses.numberOfYears (Jahre an der Adresse)

Die JSON-Datendatei würde so aussehen. Beachten Sie, dass die Adressspalte ein Array von Werten enthält, die durch [ ] angegeben werden. Die verschiedenen Adressen im Array sind die wiederkehrenden Daten. Die verschiedenen Felder innerhalb der einzelnen Adressen sind die verschachtelten Daten.

{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]}
{"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"},{"status":"previous","address":"321 Main Street","city":"Hoboken","state":"NJ","zip":"44444","numberOfYears":"3"}]}

Das Schema für diese Tabelle würde so aussehen:

[
    {
        "name": "id",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "first_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "last_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "dob",
        "type": "DATE",
        "mode": "NULLABLE"
    },
    {
        "name": "addresses",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
            {
                "name": "status",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "address",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "city",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "state",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "zip",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "numberOfYears",
                "type": "STRING",
                "mode": "NULLABLE"
            }
        ]
    }
]

Die verschachtelten und wiederholten Spalten im Beispiel angeben

Console

So legen Sie die verschachtelte und wiederkehrende Spalte addresses in der Cloud Console fest:

  1. Rufen Sie in der Cloud Console die BigQuery-Web-UI auf.
    Zur Cloud Console

  2. Maximieren Sie im Navigationsbereich im Abschnitt Resources (Ressourcen) Ihr Projekt und wählen Sie ein Dataset aus. Klicken Sie auf Create table (Tabelle erstellen).

    Tabelle erstellen

  3. Gehen Sie auf der Seite Tabelle erstellen so vor:

    • Wählen Sie als Source (Quelle) Empty table (Leere Tabelle) aus.
    • Wählen Sie als Ziel Ihr Dataset aus und geben Sie den Tabellennamen in das Feld Tabellenname ein.
    • Fügen Sie für Schema ein Feld hinzu:

      • Geben Sie im Feld Name den Wert addresses ein.
      • Wählen Sie für Type (Typ) den Wert RECORD aus.
      • Wählen Sie für Mode (Modus) den Wert REPEATED aus.

        Adressschema

      • Klicken Sie rechts neben addresses auf das Plussymbol, um ein verschachteltes Feld hinzuzufügen.

        Schaltfläche

        • Geben Sie im Feld Name den Wert status ein. Beachten Sie, dass das Feld bereits mit addresses. ausgefüllt ist, was darauf hinweist, dass es sich um ein verschachteltes Feld handelt.
        • Wählen Sie für Type (Typ) den Wert STRING aus.
        • Übernehmen Sie für Mode (Modus) den Wert NULLABLE.

          Statusschema

        • Wiederholen Sie diese Schritte, um address (NULLABLE STRING), city (NULLABLE STRING), state (NULLABLE STRING), zip (NULLABLE STRING) und numberOfYears (NULLABLE STRING) hinzuzufügen.

    • Alternativ können Sie auf Als Text bearbeiten klicken und das Schema als JSON-Array angeben.

Klassische UI

So geben Sie die verschachtelte und wiederkehrende Spalte addresses in der klassischen BigQuery-Web-UI an:

  1. Öffnen Sie die BigQuery-Web-UI.

    Zur BigQuery-Web-UI

  2. Klicken Sie im Navigationsbereich neben dem Dataset-Namen auf den Abwärtspfeil Abwärtspfeil und anschließend auf Create new table (Neue Tabelle erstellen).

  3. Gehen Sie auf der Seite Tabelle erstellen so vor:

    • Klicken Sie für Source data (Quelldaten) auf Create from source (Aus Quelle erstellen).
    • Wählen Sie unter Zieltabelle das Dataset aus und geben Sie in das Feld Name der Zieltabelle den Namen der Tabelle ein.
    • Gehen Sie unter Schema so vor:

      • Geben Sie im Feld Name den Wert addresses ein.
      • Wählen Sie für Type (Typ) den Wert RECORD aus.
      • Wählen Sie für Mode (Modus) den Wert REPEATED aus.
      • Klicken Sie rechts neben RECORD auf das Plussymbol Plussymbol, um ein verschachteltes Feld hinzuzufügen.

        • Geben Sie im Feld Name den Wert status ein. Beachten Sie, dass das Feld bereits mit addresses. ausgefüllt ist, was darauf hinweist, dass es sich um ein verschachteltes Feld handelt.
        • Wählen Sie für Type (Typ) den Wert STRING aus.
        • Übernehmen Sie für Mode (Modus) den Wert NULLABLE.
        • Wiederholen Sie diese Schritte, um address (NULLABLE STRING), city (NULLABLE STRING), state (NULLABLE STRING), zip (NULLABLE STRING) und numberOfYears (NULLABLE STRING) hinzuzufügen.

        Verschachteltes Feld in der UI

    • Alternativ können Sie auf Edit as text (Als Text bearbeiten) klicken und das Schema als JSON-Array angeben.

Wenn Sie das Schema in der BigQuery-Web-UI prüfen, sieht das Feld addresses so aus:

Verschachteltes Feld im Tab

bq

Zur Festlegung der verschachtelten und wiederkehrenden Spalte addresses in einer JSON-Schemadatei geben Sie in einem Texteditor Folgendes ein:

[
    {
        "name": "id",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "first_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "last_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "dob",
        "type": "DATE",
        "mode": "NULLABLE"
    },
    {
        "name": "addresses",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
            {
                "name": "status",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "address",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "city",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "state",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "zip",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "numberOfYears",
                "type": "STRING",
                "mode": "NULLABLE"
            }
        ]
    }
]

Nachdem Sie die JSON-Schemadatei erstellt haben, können Sie sie über die Befehlszeile bereitstellen.

Go

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

import (
	"context"
	"fmt"
	"io"

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

// createTableComplexSchema demonstrates creating a BigQuery table and specifying a complex schema that includes
// an array of Struct types.
func createTableComplexSchema(w io.Writer, projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

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

	sampleSchema := bigquery.Schema{
		{Name: "id", Type: bigquery.StringFieldType},
		{Name: "first_name", Type: bigquery.StringFieldType},
		{Name: "last_name", Type: bigquery.StringFieldType},
		{Name: "dob", Type: bigquery.DateFieldType},
		{Name: "addresses",
			Type:     bigquery.RecordFieldType,
			Repeated: true,
			Schema: bigquery.Schema{
				{Name: "status", Type: bigquery.StringFieldType},
				{Name: "address", Type: bigquery.StringFieldType},
				{Name: "city", Type: bigquery.StringFieldType},
				{Name: "state", Type: bigquery.StringFieldType},
				{Name: "zip", Type: bigquery.StringFieldType},
				{Name: "numberOfYears", Type: bigquery.StringFieldType},
			}},
	}

	metaData := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, metaData); err != nil {
		return err
	}
	fmt.Fprintf(w, "created table %s\n", tableRef.FullyQualifiedName())
	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.Field.Mode;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

public class NestedRepeatedSchema {

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

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

      Schema schema =
          Schema.of(
              Field.of("id", StandardSQLTypeName.STRING),
              Field.of("first_name", StandardSQLTypeName.STRING),
              Field.of("last_name", StandardSQLTypeName.STRING),
              Field.of("dob", StandardSQLTypeName.DATE),
              // create the nested and repeated field
              Field.newBuilder(
                      "addresses",
                      StandardSQLTypeName.STRUCT,
                      Field.of("status", StandardSQLTypeName.STRING),
                      Field.of("address", StandardSQLTypeName.STRING),
                      Field.of("city", StandardSQLTypeName.STRING),
                      Field.of("state", StandardSQLTypeName.STRING),
                      Field.of("zip", StandardSQLTypeName.STRING),
                      Field.of("numberOfYears", StandardSQLTypeName.STRING))
                  .setMode(Mode.REPEATED)
                  .build());

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

      bigquery.create(tableInfo);
      System.out.println("Table with nested and repeated schema created successfully");
    } catch (BigQueryException e) {
      System.out.println("Table was not created. \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 nestedRepeatedSchema() {
  // Creates a new table named "my_table" in "my_dataset"
  // with nested and repeated columns in schema.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";
  // const schema = [
  //   {name: 'Name', type: 'STRING', mode: 'REQUIRED'},
  //   {
  //     name: 'Addresses',
  //     type: 'RECORD',
  //     mode: 'REPEATED',
  //     fields: [
  //       {name: 'Address', type: 'STRING'},
  //       {name: 'City', type: 'STRING'},
  //       {name: 'State', type: 'STRING'},
  //       {name: 'Zip', type: 'STRING'},
  //     ],
  //   },
  // ];

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    location: 'US',
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);
}

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

schema = [
    bigquery.SchemaField("id", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("first_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("last_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("dob", "DATE", mode="NULLABLE"),
    bigquery.SchemaField(
        "addresses",
        "RECORD",
        mode="REPEATED",
        fields=[
            bigquery.SchemaField("status", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("address", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("city", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("state", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("zip", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("numberOfYears", "STRING", mode="NULLABLE"),
        ],
    ),
]
table_ref = dataset_ref.table("my_table")
table = bigquery.Table(table_ref, schema=schema)
table = client.create_table(table)  # API request

print("Created table {}".format(table.full_table_id))

Verschachtelte und wiederkehrende Spalten ändern

Nachdem Sie eine verschachtelte Spalte oder eine verschachtelte und wiederkehrende Spalte in die Schemadefinition einer Tabelle aufgenommen haben, können Sie die Spalte wie bei jedem anderen Spaltentyp ändern. BigQuery unterstützt nativ mehrere Schemaänderungen, zum Beispiel das Hinzufügen eines neuen verschachtelten Felds zu einem Datensatz oder das Lockern des Modus eines verschachtelten Felds. Weitere Informationen finden Sie unter Tabellenschemas ändern.

Außerdem können Sie eine Schemadefinition, die geschachtelte und wiederkehrende Spalten enthält, auch manuell ändern. Weitere Informationen finden Sie unter Tabellenschemas manuell ändern.