Mit Sammlungen den Überblick behalten Sie können Inhalte basierend auf Ihren Einstellungen speichern und kategorisieren.

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

Wählen Sie eine der folgenden Optionen aus, um eine neue Tabelle mit den vorherigen verschachtelten und wiederkehrenden Spalten zu erstellen:

Console

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

  1. Öffnen Sie in der Google 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 im Detailbereich auf Tabelle erstellen.

  4. Geben Sie auf der Seite Tabelle erstellen die folgenden Details an:

    • Wählen Sie unter Quelle im Feld Tabelle erstellen aus die Option Leere Tabelle aus.
    • Geben Sie im Bereich Ziel die folgenden Felder an:

      • Wählen Sie bei Dataset das Dataset aus, in dem Sie die Tabelle erstellen möchten.
      • Geben Sie unter Tabelle den Namen der Tabelle ein, die Sie erstellen möchten.
    • Klicken Sie unter Schema auf Feld hinzufügen und geben Sie das folgende Tabellenschema ein:

      • Geben Sie als Feldname addresses ein.
      • Wählen Sie für Typ den Eintrag RECORD aus.
      • Wählen Sie für Modus die Option REPEATED aus.

        Adressschema

      • Geben Sie die folgenden Felder für ein verschachteltes Feld an:

        • Geben Sie im Feld Feldname den Wert status ein.
        • Wählen Sie für Typ den Wert STRING aus.
        • Übernehmen Sie für Modus den Wert NULLABLE.
        • Klicken Sie auf Feld hinzufügen, um die folgenden Felder hinzuzufügen:

          Feldname Typ Modus
          address STRING NULLABLE
          city STRING NULLABLE
          state STRING NULLABLE
          zip STRING NULLABLE
          numberOfYears STRING NULLABLE

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

SQL

Verwenden Sie die Anweisung CREATE TABLE. Geben Sie das Schema mit der Option Spalte an.

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

    BigQuery aufrufen

  2. Geben Sie im Abfrageeditor die folgende Anweisung ein:

    CREATE TABLE IF NOT EXISTS mydataset.mytable (
      id STRING,
      first_name STRING,
      last_name STRING,
      dob DATE,
      addresses
        ARRAY<
          STRUCT<
            status STRING,
            address STRING,
            city STRING,
            state STRING,
            zip STRING,
            numberOfYears STRING>>
    ) OPTIONS (
        description = 'Example name and addresses table');
    

  3. Klicken Sie auf Ausführen.

Informationen zum Ausführen von Abfragen finden Sie unter Interaktive Abfragen ausführen.

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 anwenden, folgen Sie den Schritten zur Einrichtung von Go in der BigQuery-Kurzanleitung zur Verwendung von Clientbibliotheken. 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 zur Verwendung von Clientbibliotheken. Weitere Angaben 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 zur Verwendung von Clientbibliotheken. 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 zur Verwendung von Clientbibliotheken. Weitere Angaben finden Sie in der Referenzdokumentation zur BigQuery 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 abfragen

Verwenden Sie einen Array-Subscript-Operator, um den Wert eines ARRAY an einer bestimmten Position auszuwählen. Verwenden Sie den Punktoperator, um auf Elemente in einem STRUCT zuzugreifen. Im folgenden Beispiel werden der Vorname, Nachname und die erste Adresse ausgewählt, die im Feld addresses aufgeführt sind:

SELECT
  first_name,
  last_name,
  addresses[offset(0)].address
FROM
  mydataset.mytable;

Das Ergebnis lautet:

+------------+-----------+------------------+
| first_name | last_name | address          |
+------------+-----------+------------------+
| John       | Doe       | 123 First Avenue |
| Jane       | Doe       | 789 Any Avenue   |
+------------+-----------+------------------+

Verwenden Sie zum Extrahieren aller Elemente eines ARRAY den UNNEST-Operator mit einem CROSS JOIN. Im folgenden Beispiel werden der Vorname, der Nachname, die Adresse und der Bundesstaat für alle Adressen ausgewählt, die sich nicht in New York befinden:

SELECT
  first_name,
  last_name,
  a.address,
  a.state
FROM
  mydataset.mytable CROSS JOIN UNNEST(addresses) AS a
WHERE
  a.state != 'NY';

Das Ergebnis lautet:

+------------+-----------+------------------+-------+
| first_name | last_name | address          | state |
+------------+-----------+------------------+-------+
| John       | Doe       | 123 First Avenue | WA    |
| John       | Doe       | 456 Main Street  | OR    |
| Jane       | Doe       | 321 Main Street  | NJ    |
+------------+-----------+------------------+-------+

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.

Tabellensicherheit

Informationen zum Steuern des Zugriffs auf Tabellen in BigQuery finden Sie unter Einführung in die Tabellenzugriffssteuerung.

Mehr zur Verwendung der Play Console erfahren