Identitätsspalte angeben

In diesem Dokument wird beschrieben, wie Sie Identitätsspalten erstellen und verwenden. Diese werden manchmal als Spalten mit automatischer Inkrementierung bezeichnet und dienen zum Erstellen und Verwalten von Primärschlüsseln in Ihren Tabellen. Wenn Sie eine Zeile in eine Tabelle mit einer Identitätsspalte einfügen, generiert BigQuery einen eindeutigen ganzzahligen Wert für diese Spalte.

Übersicht

Eine Identitätsspalte ist eine INT64-Spalte, die mit eindeutigen, vom System generierten Werten gefüllt wird.

Der Hauptanwendungsfall für Identitätsspalten ist das Generieren von Primärschlüsseln. Sie können auch Primärschlüssel mit der GENERATE_UUID Funktion generieren, um eindeutige Strings zu erstellen. Identitätsspalten werden jedoch im Allgemeinen aus folgenden Gründen bevorzugt:

  • Ganzzahlige Werte benötigen weniger Speicherplatz als Stringwerte.
  • Die Verwendung von Ganzzahlen für Tabellenverknüpfungen ist effizienter als die Verwendung von Strings.

Die Werte für eine Identitätsspalte werden basierend auf einem Startwert generiert, der den ersten Wert definiert, und einem Inkrementwert, der die minimale Differenz zwischen aufeinanderfolgenden generierten Werten definiert.

Generierte Werte für Identitätsspalten haben die folgenden Eigenschaften:

  • Eindeutig : Automatisch generierte Werte sind innerhalb der Tabelle eindeutig.
  • Lose sortiert : Es wird nicht garantiert, dass die generierten Werte in streng aufsteigender oder absteigender Reihenfolge sind.
  • Dünnbesetzt : Es wird nicht garantiert, dass die generierten Werte aufeinanderfolgend sind. Einige Werte werden möglicherweise übersprungen, aber die Werte in einer Identitätsspalte unterscheiden sich immer um ein Vielfaches des von Ihnen angegebenen Inkrements.

Beschränkungen

  • Eine Tabelle kann maximal eine Identitätsspalte haben.
  • Sie können mit Legacy-SQL aus Tabellen mit Identitätsspalten lesen, aber nicht in Tabellen mit Identitätsspalten schreiben, die Legacy-SQL verwenden.
  • Clustering oder Partitionierung kann nicht für eine Identitätsspalte verwendet werden.

  • Die folgenden Tabellenkopien werden nicht unterstützt, wenn eine Quell- oder Zieltabelle eine Identitätsspalte hat:

    • Tabellenkopie mit der Schreibdisposition WRITE_APPEND oder WRITE_TRUNCATE
    • Tabellenkopie aus mehreren Quellen

  • Das Streamen von Daten mit der Storage Write API (gRPC) oder der tabledata.insertAll API-Methode wird für Tabellen mit Identitätsspalten nicht unterstützt.

Identitätsspalten erstellen

Sie können eine Identitätsspalte erstellen, wenn Sie mit der CREATE TABLE DDL-Anweisung eine neue Tabelle erstellen. Verwenden Sie die Klausel GENERATED AS IDENTITY, um eine INT64-Spalte als Identitätsspalte zu kennzeichnen. Eine Tabelle kann maximal eine Identitätsspalte haben. Sie können einen der folgenden Generierungsmodi angeben, der bestimmt, ob Sie Werte manuell in die Identitätsspalte einfügen können:

  • GENERATED ALWAYS AS IDENTITY: Werte werden immer vom System generiert. Sie können beim Einfügen oder Aktualisieren von Daten in dieser Spalte keinen eigenen Wert angeben. Wenn Sie ALWAYS oder BY DEFAULT nicht angeben, wird ALWAYS verwendet.

  • GENERATED BY DEFAULT AS IDENTITY: Sie können Werte in die Identitätsspalte einfügen oder ändern. BigQuery erzwingt keine Eindeutigkeit der von Ihnen eingefügten oder geänderten Werte.

    Wenn Sie die Spalte weglassen oder NULL angeben, wenn Sie Daten einfügen, generiert BigQuery automatisch einen Wert für Sie. Die Identitätsspalte darf keinen NULL-Wert enthalten. Wenn Sie einen generierten Wert in einer INSERT-, MERGE- oder UPDATE-Anweisung verwenden möchten, können Sie das Schlüsselwort DEFAULT oder NULL verwenden.

Im folgenden Beispiel wird die Tabelle mydataset.id_table mit einer Identitäts spalte id erstellt, die bei 0 beginnt und um 5 erhöht wird:

CREATE TABLE mydataset.id_table (
  id INT64 GENERATED ALWAYS AS IDENTITY(START WITH 0 INCREMENT BY 5),
  data STRING
);

Einer Spalte die Eigenschaft „Identitätsspalte“ hinzufügen

Wenn Sie eine vorhandene Spalte so ändern möchten, dass Identitätswerte generiert werden, verwenden Sie die ALTER TABLE ALTER COLUMN SET GENERATED DDL-Anweisung. Mit dieser Anweisung wird eine vorhandene INT64-Spalte in eine Identitätsspalte geändert. Es werden keine Werte für vorhandene Zeilen in der Identitätsspalte nachgefüllt.

DML-Anweisungen mit Identitätsspalten verwenden

Sie können DML-Anweisungen wie INSERT, MERGE und UPDATE mit Identitätsspalten verwenden. In den folgenden Abschnitten wird die Tabelle mydataset.mytable verwendet, die eine Identitätsspalte namens id und eine Stringspalte namens data enthält:

CREATE OR REPLACE TABLE mydataset.mytable (
  id INT64 GENERATED BY DEFAULT AS IDENTITY(START WITH 100 INCREMENT BY 10),
  data STRING
);

Daten einfügen

Wenn Sie Daten in eine Tabelle mit einer Identitätsspalte einfügen, können Sie die Identitätsspalte aus der Spaltenliste weglassen, um einen Wert dafür zu generieren. In der folgenden INSERT-Anweisung wird die Spalte id weggelassen. BigQuery generiert Werte dafür:

INSERT mydataset.mytable (data) VALUES ('A'), ('B'), ('C');

Das Ergebnis sieht etwa so aus, wobei die Reihenfolge der Zuweisung generierter Werte zu Zeilen variieren kann:

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
+-----+------+

Wenn eine Identitätsspalte mit GENERATED BY DEFAULT AS IDENTITY definiert ist, können Sie einen eigenen Wert für die Spalte angeben. Sie können auch das Schlüsselwort DEFAULT oder NULL verwenden, damit BigQuery einen Wert generiert.

Die folgende INSERT-Anweisung gibt einen Wert für eine Zeile an und verwendet DEFAULT oder NULL, um Werte für die anderen beiden Zeilen zu generieren:

INSERT mydataset.mytable (id, data)
VALUES (155, 'D'), (DEFAULT, 'E'), (NULL, 'F');

Das Ergebnis sieht etwa so aus:

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
+-----+------+

Wenn eine Identitätsspalte mit GENERATED ALWAYS AS IDENTITY definiert ist, können Sie nur das Schlüsselwort DEFAULT verwenden, damit BigQuery einen Wert generiert. Sie können keinen eigenen Wert angeben oder NULL verwenden.

Daten zusammenführen

Sie können die MERGE Anweisung verwenden, um Daten in eine Tabelle mit einer Identitätsspalte zusammenzuführen. Wenn für Ihre Identitätsspalte der GENERATED BY DEFAULT AS IDENTITY Generierungsmodus verwendet wird, können Sie die Schlüsselwörter DEFAULT oder NULL verwenden, um einen Wert zu generieren, wenn Sie im Rahmen einer MERGE-Anweisung Daten einfügen oder aktualisieren.

Im folgenden Beispiel wird mydataset.source_table in mydataset.mytable zusammengeführt. Wenn es keine Übereinstimmung in der Spalte data gibt, wird eine neue Zeile eingefügt. Wenn es eine Übereinstimmung gibt, wird die Spalte id auf einen neuen generierten Wert aktualisiert:

CREATE OR REPLACE TABLE mydataset.source_table(data STRING)
AS SELECT * FROM UNNEST(['A', 'C', 'G']);

MERGE mydataset.mytable T
USING mydataset.source_table S
ON T.data = S.data
WHEN MATCHED THEN
  UPDATE SET id = DEFAULT
WHEN NOT MATCHED THEN
  INSERT(data)
  VALUES(S.data);

Das Ergebnis sieht etwa so aus:

+-----+------+
| id  | data |
+-----+------+
| 160 | A    |
| 120 | B    |
| 150 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
| 170 | G    |
+-----+------+

Wenn für Ihre Identitätsspalte der Generierungsmodus GENERATED ALWAYS AS IDENTITY verwendet wird, können Sie die Identitätsspalte nicht in eine Merge-Update-Klausel aufnehmen. Wenn Sie eine Merge-Insert-Klausel verwenden möchten, können Sie die Identitätsspalte aus der Spaltenliste weglassen oder das Schlüsselwort DEFAULT verwenden.

Daten aktualisieren

Sie können die UPDATE Anweisung verwenden, um Werte in einer Identitätsspalte zu aktualisieren, für die der GENERATED BY DEFAULT AS IDENTITY Generierungsmodus verwendet wird. Sie können die Schlüsselwörter DEFAULT oder NULL verwenden, um einen neuen Wert zu generieren.

Im folgenden Beispiel werden alle Werte in der Spalte id auf neu generierte Werte aktualisiert:

UPDATE mydataset.mytable
SET id = NULL
WHERE TRUE;

Das Ergebnis sieht etwa so aus:

+-----+------+
| id  | data |
+-----+------+
| 190 | A    |
| 210 | B    |
| 240 | C    |
| 230 | D    |
| 180 | E    |
| 200 | F    |
| 220 | G    |
+-----+------+

Wenn für Ihre Identitätsspalte der Generierungsmodus GENERATED ALWAYS AS IDENTITY verwendet wird, können Sie die Identitätsspalte nicht aktualisieren.

An eine Tabelle anhängen

Sie können den Befehl bq query mit dem Flag --append_table verwenden, um die Ergebnisse einer Abfrage an eine Zieltabelle mit einer Identitätsspalte anzuhängen. Wenn die Identitätsspalte in der Abfrage weggelassen wird, wird ein Wert dafür generiert.

Im folgenden Beispiel werden nur Daten für die Spalte data an mydataset.mytable angehängt:

bq query \
    --nouse_legacy_sql \
    --append_table \
    --destination_table=mydataset.mytable \
    'SELECT "H" AS data'

Der Tabelle mydataset.mytable wird eine neue Zeile mit einem generierten id-Wert hinzugefügt.

Daten laden

Sie können Daten laden in eine Tabelle mit einer Identitätsspalte, indem Sie den bq load Befehl oder die LOAD DATA Anweisung verwenden. Wenn die Identitätsspalte in den Quelldaten oder im Schema weggelassen wird, werden Werte dafür generiert. Wenn die Identitätsspalte GENERATED ALWAYS AS IDENTITY ist, muss sie weggelassen werden.

Im folgenden Beispiel werden Daten aus einer CSV-Datei (data.csv) in mydataset.mytable geladen. Die Datei enthält nur Daten für die Spalte data:

"X"
"Y"

Mit dem folgenden Befommen bq load wird data.csv in mydataset.mytable geladen, wobei die Kopfzeile weggelassen und im Schema nur die Spalte data angegeben wird:

bq load --source_format=CSV --skip_leading_rows=0 \
mydataset.mytable data.csv data:STRING

Der Ladejob generiert id-Werte für die neuen Zeilen.

Eigenschaft „Identitätsspalte“ entfernen

Sie können die Identitätseigenschaft aus einer Spalte entfernen, indem Sie die ALTER TABLE ALTER COLUMN DROP GENERATED DDL-Anweisung verwenden.

Im folgenden Beispiel werden die Eigenschaften der Identitätsspalte aus der Spalte id in mydataset.mytable entfernt:

ALTER TABLE mydataset.mytable
ALTER COLUMN id DROP GENERATED;

Informationen zu Identitätsspalten ansehen

Wenn Sie die Konfiguration der Identitätsspalte für eine Spalte sehen möchten, fragen Sie die INFORMATION_SCHEMA.COLUMNS Ansicht ab.

Im folgenden Beispiel werden Informationen zur Identitätsspalte für Spalten in mydataset.mytable angezeigt:

SELECT
  column_name,
  is_identity,
  identity_generation,
  identity_start,
  identity_increment
FROM
  mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'mytable';

Das Ergebnis sieht etwa so aus:

+-------------+-------------+---------------------+----------------+--------------------+
| column_name | is_identity | identity_generation | identity_start | identity_increment |
+-------------+-------------+---------------------+----------------+--------------------+
| id          | YES         | BY DEFAULT          | 100            | 10                 |
| data        | NO          | NULL                | NULL           | NULL               |
+-------------+-------------+---------------------+----------------+--------------------+

Alternativ können Sie die ddl Spalte der INFORMATION_SCHEMA.TABLES Ansicht abfragen, um die Definition der Identitätsspalte in der CREATE TABLE DDL-Anweisung für eine Tabelle zu sehen.

Nächste Schritte