Verschachtelte und wiederkehrende Spalten in Tabellenschemas angeben

Auf dieser Seite wird beschrieben, wie Sie ein Tabellenschema mit verschachtelten und wiederkehrenden Spalten in BigQuery definieren. Eine Übersicht über Tabellenschemas finden Sie unter Schemas angeben.

Verschachtelte und wiederkehrende Spalten definieren

Zum Erstellen einer Spalte mit verschachtelten Daten setzen Sie den Datentyp der Spalte im Schema auf RECORD. Auf RECORD kann als STRUCT-Typ in Standard-SQL zugegriffen werden. Ein STRUCT ist ein Container mit geordneten Feldern.

Zum Erstellen einer Spalte mit wiederkehrenden Daten legen Sie für den Modus der Spalte im Schema den Wert REPEATED fest. Ein wiederkehrendes Feld kann in Standard-SQL als ARRAY-Typ aufgerufen werden.

Eine RECORD-Spalte kann den Modus REPEATED haben, der als Array von STRUCT-Typen dargestellt wird. Außerdem kann ein Feld innerhalb eines Eintrags wiederholt werden, das als STRUCT dargestellt wird, das einen ARRAY enthält. Ein Array darf kein anderes Array direkt enthalten. Weitere Informationen finden Sie unter ARRAY-Typ angeben.

Beschränkungen

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

Ein Schema darf nicht mehr als 15 Ebenen verschachtelter RECORD-Typen enthalten.
Spalten vom Typ RECORD können verschachtelte RECORD-Typen enthalten, auch untergeordnete Datensätze. Es sind maximal 15 Ebenen möglich. Dieses Limit gilt unabhängig davon, ob die RECORDs skalar oder arraybasiert (wiederholt) sind.

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 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 sieht so aus:

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

Verschachtelte und wiederkehrende Spalten im Beispiel festlegen

Console

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

  1. Öffnen Sie in der Cloud Console die Seite "BigQuery".

    BigQuery aufrufen

  2. Maximieren Sie im Bereich Explorer Ihr Projekt und wählen Sie ein Dataset aus.

  3. Klicken Sie in der Detailansicht auf Tabelle erstellen.

  4. 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 "Verschachteln"

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

bq

Wenn Sie die verschachtelte und wiederkehrende Spalte addresses in einer JSON-Schemadatei angeben möchten, verwenden Sie einen Texteditor, um eine neue Datei zu erstellen. Fügen Sie die obige Beispieldefinition ein.

Nachdem Sie die JSON-Schemadatei erstellt haben, können Sie sie über das bq-Befehlszeilentool bereitstellen. Weitere Informationen finden Sie unter JSON-Schemadatei verwenden.

Go

Bevor Sie dieses Beispiel ausprobieren, folgen Sie den Schritten zur Einrichtung von Go in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Angaben 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 Angaben 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  Python API.

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, '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 wiederholte Spalten enthält, auch manuell ändern. Weitere Informationen finden Sie unter Tabellenschemas manuell ändern.

Wann sollten verschachtelte und wiederkehrende Spalten verwendet werden?

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.

Zum Beispiel würden in einer relationalen Datenbank zur Erfassung der Bücher einer Bibliothek die Autoreninformationen wahrscheinlich in einer separaten Tabelle gespeichert werden. 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.

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.

Tipp