Primär- und Fremdschlüssel verwenden

Primär- und Fremdschlüssel sind Tabelleneinschränkungen, die zur Abfrageoptimierung beitragen können. In diesem Dokument wird erläutert, wie Sie Einschränkungen erstellen, ansehen und verwalten und wie Sie sie zur Optimierung Ihrer Abfragen verwenden.

BigQuery unterstützt die folgenden Schlüsseleinschränkungen:

  • Primärschlüssel: Ein Primärschlüssel für eine Tabelle ist eine Kombination aus einer oder mehreren Spalten, die für jede Zeile eindeutig ist und nicht NULL sein darf.
  • Fremdschlüssel: Ein Fremdschlüssel für eine Tabelle ist eine Kombination aus einer oder mehreren Spalten, die in der Primärschlüsselspalte einer referenzierten Tabelle vorhanden ist oder ist NULL.

Primär- und Fremdschlüssel werden in der Regel verwendet, um die Datenintegrität sicherzustellen und die Abfrage zu optimieren. In BigQuery werden keine Primär- und Fremdschlüsseleinschränkungen erzwungen. Wenn Sie Einschränkungen für Ihre Tabellen deklarieren, müssen Sie darauf achten, dass Ihre Daten diesen Einschränkungen entsprechen. Um die Eindeutigkeit von Werten in einer Spalte sicherzustellen, können Sie eine Identitätsspalte verwenden. BigQuery kann Tabelleneinschränkungen verwenden, um Ihre Abfragen zu optimieren.

Einschränkungen verwalten

Primär- und Fremdschlüsselbeziehungen können über die folgenden DDL-Anweisungen erstellt und verwaltet werden:

Sie können Tabelleneinschränkungen auch über die BigQuery API verwalten, indem Sie das TableConstraints Objekt aktualisieren.

Einschränkungen ansehen

Die folgenden Ansichten enthalten Informationen zu Ihren Tabelleneinschränkungen:

Abfragen optimieren

Wenn Sie Primär- und Fremdschlüssel für Ihre Tabellen erstellen und erzwingen, kann BigQuery diese Informationen verwenden, um bestimmte Abfrageverknüpfungen zu entfernen oder zu optimieren. Es ist zwar möglich, diese Optimierungen durch das Umschreiben Ihrer Abfragen zu simulieren, aber solche Umschreibungen sind nicht immer praktikabel.

In einer Produktionsumgebung können Sie Ansichten erstellen, die viele Faktentabellen und Dimensionstabellen verknüpfen. Entwickler können die Ansichten abfragen, anstatt die zugrunde liegenden Tabellen abzufragen und die Verknüpfungen jedes Mal manuell umzuschreiben. Wenn Sie die richtigen Einschränkungen definieren, werden Verknüpfungsoptimierungen automatisch für alle Abfragen ausgeführt, auf die sie angewendet werden.

In den Beispielen in den folgenden Abschnitten wird auf die Tabellen store_sales und customer mit Einschränkungen verwiesen:

CREATE TABLE mydataset.customer (customer_name STRING PRIMARY KEY NOT ENFORCED);

CREATE TABLE mydataset.store_sales (
    item STRING PRIMARY KEY NOT ENFORCED,
    sales_customer STRING REFERENCES mydataset.customer(customer_name) NOT ENFORCED,
    category STRING);

Innere Verknüpfungen entfernen

Betrachten Sie die folgende Abfrage, die eine INNER JOIN-Anweisung enthält:

SELECT ss.*
FROM mydataset.store_sales AS ss
    INNER JOIN mydataset.customer AS c
    ON ss.sales_customer = c.customer_name;

Die Spalte customer_name ist ein Primärschlüssel in der Tabelle customer. Daher hat jede Zeile aus der Tabelle store_sales entweder eine einzelne Übereinstimmung oder keine Übereinstimmung, wenn sales_customer NULL ist. Da in der Abfrage nur Spalten aus der Tabelle store_sales ausgewählt werden, kann der Abfrageoptimierer die Verknüpfung entfernen und die Abfrage wie folgt umschreiben:

SELECT *
FROM mydataset.store_sales
WHERE sales_customer IS NOT NULL;

Äußere Verknüpfungen entfernen

Um eine LEFT OUTER JOIN-Anweisung zu entfernen, müssen die Verknüpfungsschlüssel auf der rechten Seite eindeutig sein und es dürfen nur Spalten von der linken Seite ausgewählt werden. Betrachten Sie die folgende Abfrage:

SELECT ss.*
FROM mydataset.store_sales ss
    LEFT OUTER JOIN mydataset.customer c
    ON ss.category = c.customer_name;

In diesem Beispiel gibt es keine Beziehung zwischen category und customer_name. Die ausgewählten Spalten stammen nur aus der Tabelle store_sales und der Verknüpfungsschlüssel customer_name ist ein Primärschlüssel in der Tabelle customer. Daher ist jeder Wert eindeutig. Das bedeutet, dass es in der Tabelle customer für jede Zeile in der Tabelle store_sales genau eine Übereinstimmung (möglicherweise NULL) gibt und die LEFT OUTER JOIN-Anweisung entfernt werden kann:

SELECT ss.*
FROM mydataset.store_sales;

Verknüpfungen neu anordnen

Wenn BigQuery eine Verknüpfung nicht entfernen kann, kann es Tabelleneinschränkungen verwenden, um Informationen zu Verknüpfungskardinalitäten zu erhalten und die Reihenfolge zu optimieren, in der Verknüpfungen ausgeführt werden.

Beschränkungen

Für Primär- und Fremdschlüssel gelten die folgenden Einschränkungen:

  • Schlüsseleinschränkungen werden in BigQuery nicht erzwungen. Sie sind jederzeit für die Einhaltung der Einschränkungen verantwortlich. Abfragen für Tabellen mit verletzten Einschränkungen können falsche Ergebnisse zurückgeben.
  • Primärschlüssel dürfen nicht mehr als 16 Spalten enthalten.
  • Fremdschlüssel müssen Werte haben, die in der referenzierten Tabellenspalte vorhanden sind. Diese Werte können NULL sein.
  • Primär- und Fremdschlüssel müssen einen der folgenden Typen haben: BIGNUMERIC, BOOLEAN, BYTES, DATE, DATETIME, INT64, NUMERIC, STRING, oder TIMESTAMP.
  • Primär- und Fremdschlüssel können nur für Spalten auf oberster Ebene festgelegt werden.
  • Primärschlüssel können nicht benannt werden.
  • Tabellen mit Primärschlüsseleinschränkungen können nicht umbenannt werden.
  • Eine Tabelle kann bis zu 64 Fremdschlüssel haben.
  • Ein Fremdschlüssel kann nicht auf eine Spalte in derselben Tabelle verweisen.
  • Felder, die Teil von Primär- oder Fremdschlüsseleinschränkungen sind, können nicht umbenannt werden und ihr Typ kann nicht geändert werden.
  • Wenn Sie eine Tabelle kopieren, klonen, wiederherstellen, oder einen Snapshot machen , ohne die Option -a oder --append_table auszuwählen, werden die Einschränkungen der Quelltabelle kopiert und in die Zieltabelle überschrieben. Wenn Sie die -a oder --append_table Option verwenden, werden nur die Quelltabelleneinträge zur Zieltabelle ohne Tabelleneinschränkungen hinzugefügt.

Nächste Schritte