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

Der Typ RECORD ist nicht mit UNION, INTERSECT, EXCEPT DISTINCT und SELECT DISTINCT kompatibel.

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

Geben Sie die verschachtelte und wiederkehrende Spalte addresses an:

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

Richten Sie zur Authentifizierung bei BigQuery die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für Clientbibliotheken einrichten. 

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.

Richten Sie zur Authentifizierung bei BigQuery die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für Clientbibliotheken einrichten. 

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.

Richten Sie zur Authentifizierung bei BigQuery die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für Clientbibliotheken einrichten. 

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

Richten Sie zur Authentifizierung bei BigQuery die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für Clientbibliotheken einrichten. 

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change table_id to the full name of the table you want to create.
table_id = "your-project.your_dataset.your_table_name"

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 = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)  # API request

print(f"Created table {table.project}.{table.dataset_id}.{table.table_id}.")

Daten in verschachtelten Spalten im Beispiel einfügen

Mit den folgenden Abfragen können Sie verschachtelte Datensätze in Tabellen mit RECORD-Datentypspalten einfügen.

Beispiel 1

INSERT INTO mydataset.mytable (id,
first_name,
last_name,
dob,
addresses) values ("1","Johnny","Dawn","1969-01-22",
    ARRAY<
      STRUCT<
        status STRING,
        address STRING,
        city STRING,
        state STRING,
        zip STRING,
        numberOfYears STRING>>
      [("current","123 First Avenue","Seattle","WA","11111","1")])

Beispiel 2

INSERT INTO mydataset.mytable (id,
first_name,
last_name,
dob,
addresses) values ("1","Johnny","Dawn","1969-01-22",[("current","123 First Avenue","Seattle","WA","11111","1")])

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.

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.

Angenommen, Sie haben die folgende Tabelle mydataset.books:

+------------------+------------+-----------+
| title            | author_ids | num_pages |
+------------------+------------+-----------+
| Example Book One | [123, 789] | 487       |
| Example Book Two | [456]      | 89        |
+------------------+------------+-----------+

Sie haben auch die folgende Tabelle mydataset.authors mit vollständigen Informationen zu jeder Autoren-ID:

+-----------+-------------+---------------+
| author_id | author_name | date_of_birth |
+-----------+-------------+---------------+
| 123       | Alex        | 01-01-1960    |
| 456       | Rosario     | 01-01-1970    |
| 789       | Kim         | 01-01-1980    |
+-----------+-------------+---------------+

Wenn die Tabellen groß sind, kann es ressourcenintensiv sein, sie regelmäßig zusammenzuführen. Je nach Situation kann es sinnvoll sein, eine einzelne Tabelle mit allen Informationen zu erstellen:

CREATE TABLE mydataset.denormalized_books(
  title STRING,
  authors ARRAY<STRUCT<id INT64, name STRING, date_of_birth STRING>>,
  num_pages INT64)
AS (
  SELECT
    title,
    ARRAY_AGG(STRUCT(author_id, author_name, date_of_birth)) AS authors,
    ANY_VALUE(num_pages)
  FROM
    mydataset.books,
    UNNEST(author_ids) id
  JOIN
    mydataset.authors
    ON
      id = author_id
  GROUP BY
    title
);

Die Tabelle sieht so aus:

+------------------+-------------------------------+-----------+
| title            | authors                       | num_pages |
+------------------+-------------------------------+-----------+
| Example Book One | [{123, Alex, 01-01-1960},     | 487       |
|                  |  {789, Kim, 01-01-1980}]      |           |
| Example Book Two | [{456, Rosario, 01-01-1970}]  | 89        |
+------------------+-------------------------------+-----------+

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.

Doppelte Datensätze in einer Tabelle deduplizieren

Die folgende Abfrage verwendet die row_number()-Funktion, um doppelte Datensätze mit denselben Werten für last_name und first_name in den verwendeten Beispielen zu identifizieren und sie nach dob zu sortieren:

CREATE OR REPLACE TABLE mydataset.mytable AS (
  SELECT * except(row_num) FROM (
    SELECT *,
    row_number() over (partition by last_name, first_name order by dob) row_num
    FROM
    mydataset.mytable) temp_table
  WHERE row_num=1
)

Tabellensicherheit

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

Nächste Schritte