Daten mit Datenbearbeitungssprache (DML) transformieren

Mit der BigQuery-Datenbearbeitungssprache (DML) können Sie Daten in BigQuery-Tabellen aktualisieren, einfügen und löschen.

Sie können DML-Anweisungen genau wie eine SELECT-Anweisung ausführen. Allerdings müssen folgende Bedingungen erfüllt sein:

• Sie müssen GoogleSQL verwenden. Wie Sie GoogleSQL aktivieren, erfahren Sie unter SQL-Dialekte wechseln.
• Sie können keine Zieltabelle für die Abfrage angeben.

Weitere Informationen zur Berechnung der Anzahl der von einer DML-Anweisung verarbeiteten Byte finden Sie unter Berechnung der Abfragegröße bei On-Demand-Abfragen.

Beschränkungen

• Jede DML-Anweisung initiiert eine implizite Transaktion, was bedeutet, dass von der Anweisung vorgenommene Änderungen automatisch am Ende jeder erfolgreichen DML-Anweisung per Commit übernommen werden.

• Zeilen, die kürzlich mit der tabledata.insertall Streaming-Methode geschrieben wurden, können nicht mit Datenbearbeitungssprache (Data Manipulation Language, DML) geändert werden, darunter: UPDATE, DELETE, MERGE oder TRUNCATE-Anweisungen. Die kürzlichen Schreibvorgänge sind jene, die innerhalb der letzten 30 Minuten ausgeführt wurden. Alle anderen Zeilen in der Tabelle können weiterhin durch die Anweisungen UPDATE, DELETE, MERGE oder TRUNCATE verändert werden. Es kann bis zu 90 Minuten dauern, bis die gestreamten Daten für Kopiervorgänge verfügbar sind.

  Alternativ können Zeilen, die kürzlich mit der Storage Write API geschrieben wurden, mit den Anweisungen UPDATE, DELETE oder MERGE geändert werden. Weitere Informationen finden Sie unter Datenbearbeitungssprache (DML) mit kürzlich gestreamten Daten verwenden.

• Korrelierte Unterabfragen innerhalb von when_clause, search_condition, merge_update_clause oder merge_insert_clause werden für MERGE-Anweisungen nicht unterstützt.

• Abfragen, die DML-Anweisungen enthalten, können keine Platzhaltertabelle als Ziel der Abfrage verwenden. Eine Platzhaltertabelle kann beispielsweise in der FROM-Klausel einer UPDATE-Abfrage verwendet werden, aber nicht als Ziel des UPDATE-Vorgangs.

DML-Anweisungen

In den folgenden Abschnitten werden die verschiedenen Arten von DML-Anweisungen und ihre Verwendung beschrieben.

INSERT-Anweisung

Mit der Anweisung INSERT können Sie einer vorhandenen Tabelle neue Zeilen hinzufügen. Im folgenden Beispiel werden neue Zeilen mit explizit angegebenen Werten in die Tabelle dataset.Inventory eingefügt.

INSERT dataset.Inventory (product, quantity)
VALUES('whole milk', 10),
      ('almond milk', 20),
      ('coffee beans', 30),
      ('sugar', 0),
      ('matcha', 20),
      ('oat milk', 30),
      ('chai', 5)

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | sugar             |        0 |
 | whole milk        |       10 |
 +-------------------+----------+/

Weitere Informationen zu INSERT-Anweisungen finden Sie unter INSERT-Anweisung.

DELETE-Anweisung

Mit der Anweisung DELETE werden Zeilen in einer Tabelle gelöscht. Im folgenden Beispiel werden alle Zeilen in der Tabelle dataset.Inventory gelöscht, die den quantity-Wert 0 haben.

DELETE dataset.Inventory
WHERE quantity = 0

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | whole milk        |       10 |
 +-------------------+----------+/

Wenn Sie alle Zeilen in einer Tabelle löschen möchten, verwenden Sie stattdessen die Anweisung TRUNCATE TABLE. Weitere Informationen zu DELETE-Anweisungen finden Sie unter DELETE-Anweisung.

TRUNCATE-Anweisung

Mit der TRUNCATE-Anweisung werden alle Zeilen aus einer Tabelle entfernt, die Metadaten der Tabelle, einschließlich Tabellenschema, Beschreibung und Labels, bleiben jedoch erhalten. Im folgenden Beispiel werden alle Zeilen aus der Tabelle dataset.Inventory entfernt.

TRUNCATE dataset.Inventory

So löschen Sie bestimmte Zeilen in einer Tabelle: Verwenden Sie stattdessen die DELETE-Anweisung. Weitere Informationen zur TRUNCATE-Anweisung finden Sie unter TRUNCATE-Anweisung.

UPDATE-Anweisung

Mit der Anweisung UPDATE werden vorhandene Zeilen in einer Tabelle aktualisiert. Die UPDATE-Anweisung muss außerdem das Schlüsselwort WHERE enthalten, um eine Bedingung anzugeben. Im folgenden Beispiel wird der Wert quantity von Zeilen für Produkte, die den String milk enthalten, um 10 verringert.

UPDATE dataset.Inventory
SET quantity = quantity - 10,
WHERE product LIKE '%milk%'

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       10 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       20 |
 | whole milk        |        0 |
 +-------------------+----------+/

UPDATE-Anweisungen können auch FROM-Klauseln enthalten, um verknüpfte Tabellen einzubeziehen. Weitere Informationen zu UPDATE-Anweisungen finden Sie unter UPDATE-Anweisung.

MERGE-Anweisung

Die MERGE-Anweisung kombiniert die Vorgänge INSERT, UPDATE und DELETE in einer einzigen Anweisung und führt die Vorgänge atomar aus, um Daten aus einer Tabelle in eine andere zusammenzuführen. Weitere Informationen und Beispiele zur MERGE-Anweisung finden Sie unter MERGE-Anweisung.

Gleichzeitige Jobs

BigQuery verwaltet die Gleichzeitigkeit von DML-Anweisungen, mit denen Zeilen in einer Tabelle hinzugefügt, geändert oder gelöscht werden.

Hinweis: DML-Anweisungen unterliegen Ratenbegrenzungen wie etwa der maximalen Rate von Tabellenschreibvorgängen. Eine Ratenbegrenzung kann erreicht werden, wenn Sie eine große Anzahl an Jobs für eine Tabelle gleichzeitig senden. Diese Raten begrenzen nicht die Gesamtzahl der DML-Anweisungen, die ausgeführt werden können. Wenn Sie eine Fehlermeldung erhalten, die angibt, dass Sie eine Ratenbegrenzung überschritten haben, wiederholen Sie den Vorgang mit exponentiellem Backoff zwischen den Wiederholungen.

Gleichzeitigkeit von INSERT-DML-Anweisungen

Während eines Zeitraums von 24 Stunden werden die ersten 1.500 INSERT-Anweisungen unmittelbar nach dem Senden ausgeführt. Nachdem dieses Limit erreicht wurde, ist die Gleichzeitigkeit von INSERT-Anweisungen zum Schreiben in eine Tabelle auf 10 beschränkt. Zusätzliche INSERT-Anweisungen werden einer PENDING-Warteschlange hinzugefügt. Es können jeweils bis zu 100 INSERT-Anweisungen für eine Tabelle in die Warteschlange gestellt werden. Wenn eine INSERT-Anweisung abgeschlossen ist, wird die nächste INSERT-Anweisung aus der Warteschlange entfernt und ausgeführt.

Wenn Sie DML-INSERT-Anweisungen häufiger ausführen müssen, können Sie Daten mit der Storage Write API in Ihre Tabelle streamen.

Gleichzeitigkeit von UPDATE-, DELETE- und MERGE-DML-Anweisungen

Die DML-Anweisungen UPDATE, DELETE und MERGE werden als sich ändernde DML-Anweisungen bezeichnet. Wenn Sie eine oder mehrere sich ändernde DML-Anweisungen für eine Tabelle senden, während noch andere sich ändernde DML-Jobs für diese ausgeführt werden (oder ausstehend sind), führt BigQuery bis zu zwei von ihnen gleichzeitig aus, danach werden bis zu 20 als PENDING in der Warteschlange platziert. Wenn ein zuvor ausgeführter Job abgeschlossen ist, wird der nächste ausstehende Job aus der Warteschlange entfernt und ausgeführt. Derzeit teilen sich mutierende DML-Anweisungen in der Warteschlange eine tabellenspezifische Warteschlange mit einer maximalen Länge von 20. Zusätzliche Anweisungen nach der maximalen Warteschlangenlänge für jede einzelne Tabelle schlagen mit der Fehlermeldung fehl: Resources exceeded during query execution: Too many DML statements outstanding against table PROJECT_ID:TABLE, limit is 20.

Interaktive Prioritäts-DML-Jobs, die länger als sechs Stunden in der Warteschlange stehen, schlagen mit der folgenden Fehlermeldung fehl:

DML statement has been queued for too long

Konflikte mit DML-Anweisungen

Mutierende DML-Anweisungen, die gleichzeitig in einer Tabelle ausgeführt werden, verursachen Konflikte mit DML-Anweisungen, wenn durch die Anweisungen versucht wird, dieselbe Partition zu mutieren. Die Anweisungen sind erfolgreich, solange sie nicht dieselbe Partition ändern. BigQuery versucht bis zu dreimal, fehlgeschlagene Anweisungen noch einmal auszuführen.

• Eine INSERT-DML-Anweisung, die Zeilen in eine Tabelle einfügt, steht nicht im Konflikt mit einer anderen gleichzeitig ausgeführten DML-Anweisung.

• Eine MERGE-DML-Anweisung steht nicht in Konflikt mit anderen gleichzeitig ausgeführten DML-Anweisungen, solange mit der Anweisung nur Zeilen eingefügt werden und vorhandene Zeilen nicht gelöscht oder aktualisiert werden. Dazu können MERGE-Anweisungen mit UPDATE- oder DELETE-Klauseln gehören, sofern diese Klauseln nicht bei der Ausführung der Abfrage aufgerufen werden.

Detaillierte DML

Vorschau

Diese Funktion unterliegt den „Nutzungsbedingungen für Pre-GA-Angebote“ im Abschnitt „Allgemeine Nutzungsbedingungen“ der dienstspezifischen Nutzungsbedingungen. Sie können personenbezogene Daten für dieses Feature gemäß der Zusatz zur Verarbeitung von Cloud-Daten verarbeiten, vorbehaltlich der in der Vereinbarung beschriebenen Verpflichtungen und Einschränkungen, die für den Zugriff auf Google Cloud gelten. Pre-GA-Funktionen sind „wie besehen“ verfügbar und werden möglicherweise nur eingeschränkt unterstützt. Weitere Informationen finden Sie in den Beschreibungen der Startphase.

Hinweis: Wenn Sie Feedback geben oder Support für diese Funktion anfordern möchten, senden Sie eine E-Mail an [e-mail].

Die detaillierte DML ist eine Leistungssteigerung, die die Ausführung von UPDATE-, DELETE- und MERGE-Anweisungen (auch als sich ändernde DML-Anweisungen bezeichnet) optimieren soll. Wenn die detaillierte DML nicht aktiviert ist, werden Mutationen auf Dateigruppenebene ausgeführt, was zu ineffizienten Datenüberschreibungen führen kann. Die detaillierte DML führt einen detaillierteren Ansatz ein, mit dem die Menge der Daten, die neu geschrieben werden müssen, und der Gesamtverbrauch von Slots reduziert werden soll.

Wenn Sie ein Projekt für die Vorabversion der detaillierten DML registrieren möchten, füllen Sie das BigQuery-Registrierungsformular für die detaillierte DML aus. Projekte werden basierend auf einer Bewertung Ihrer Arbeitslasten selektiv registriert.

Detaillierte DML aktivieren

Wenn Sie die detaillierte DML aktivieren möchten, legen Sie die Tabellenoption enable_fine_grained_mutations auf TRUE fest, wenn Sie eine DDL-Anweisung vom Typ CREATE TABLE oder ALTER TABLE ausführen.

Verwenden Sie die Anweisung CREATE TABLE, um eine neue Tabelle mit detaillierter DML zu erstellen:

CREATE TABLE mydataset.mytable (
  product STRING,
  inventory INT64)
OPTIONS(enable_fine_grained_mutations = TRUE);

Verwenden Sie die Anweisung ALTER TABLE, um eine vorhandene Tabelle mit detaillierter DML zu ändern:

ALTER TABLE mydataset.mytable
SET OPTIONS(enable_fine_grained_mutations = TRUE);

Nachdem die Option enable_fine_grained_mutations auf TRUE gesetzt wurde, werden sich ändernde DML-Anweisungen mit aktivierten detaillierten DML-Funktionen ausgeführt und verwenden die vorhandene Syntax für DML-Anweisungen.

Wenn Sie die detaillierte DML für eine Tabelle deaktivieren möchten, setzen Sie enable_fine_grained_mutations mithilfe der DDL-Anweisung ALTER TABLE auf FALSE.

Preise

Wenn Sie die detaillierte DML für eine Tabelle aktivieren, können zusätzliche BigQuery-Speicherkosten anfallen, um die zusätzlichen Metadaten für Mutationen zu speichern, die mit detaillierten DML-Vorgängen verknüpft sind. Die tatsächlichen Kosten hängen von der Menge der geänderten Daten ab, sind aber in den meisten Fällen im Vergleich zur Größe der Tabelle vernachlässigbar.

Bei Projekten, die für die Verwendung von reservations konfiguriert sind, werden Slots verwendet, um detaillierte DML-Anweisungen zu verarbeiten, einschließlich der Hintergrundverarbeitung von Tabellen- oder Mutationsmetadaten.

Überlegungen zu gelöschten Daten

Bei detaillierten DML-Vorgängen werden gelöschte Daten offline verarbeitet.

Bei Projekten, bei denen detaillierte DML-Vorgänge ohne BACKGROUND-Zuweisung ausgeführt werden, werden Daten mit On-Demand-Preisen gelöscht. In diesem Fall werden gelöschte Daten regelmäßig mit internen BigQuery-Ressourcen verarbeitet.

Bei Projekten, bei denen mit einer BACKGROUND-Zuweisung detaillierte DML-Vorgänge ausgeführt werden, werden gelöschte Daten mithilfe von Slots verarbeitet. Sie unterliegen der Ressourcenverfügbarkeit der konfigurierten Reservierung. Wenn in der konfigurierten Reservierung nicht genügend Ressourcen verfügbar sind, kann die Verarbeitung gelöschter Daten länger als erwartet dauern.

Beschränkungen

Für Tabellen mit aktivierter detaillierter DML gelten die folgenden Einschränkungen:

• Sie können die Methode tabledata.list nicht verwenden, um Inhalte aus einer Tabelle mit aktivierter detaillierter DML zu lesen. Verwenden Sie stattdessen die Storage Read API, um Tabelleneinträge über eine API zu lesen.
• Sie können keinen Tabellen-Snapshot oder Tabellenklon einer Tabelle erstellen, für die die detaillierte DML aktiviert ist.
• Sie können die detaillierte DML nicht für eine Tabelle in einem replizierten Dataset aktivieren und auch kein Dataset replizieren, das eine Tabelle mit aktivierter detaillierter DML enthält.
• DML-Anweisungen, die in einer Transaktion mit mehreren Anweisungen ausgeführt werden, werden nicht mithilfe von detaillierter DML optimiert.

Best Practices

Für eine optimale Leistung empfiehlt Google folgende Muster:

• Vermeiden Sie es, zu viele einzelne Zeilenaktualisierungen oder -einfügungen zu senden. Gruppieren Sie stattdessen DML-Vorgänge wo möglich. Weitere Informationen finden Sie unter DML-Anweisungen, die einzelne Zeilen aktualisieren oder einfügen.

• Wenn Aktualisierungen oder Löschvorgänge im Allgemeinen für ältere Daten oder innerhalb eines bestimmten Zeitraums durchgeführt werden, empfiehlt sich eine Partitionierung Ihrer Tabellen. Durch die Partitionierung wird dafür gesorgt, dass die Änderungen auf bestimmte Partitionen innerhalb der Tabelle beschränkt sind.

• Partitionieren Sie Tabellen nicht, wenn die Datenmenge in jeder Partition klein ist und jede Aktualisierung einen großen Teil der Partitionen ändert.

• Wenn Sie häufig Zeilen aktualisieren, in denen eine oder mehrere Spalten in einen engen Wertebereich fallen, können Sie geclusterte Tabellen verwenden. Clustering sorgt dafür, dass Änderungen auf bestimmte Gruppen von Blöcken beschränkt sind, und reduziert die zu lesende und schreibende Datenmenge. Das folgende Beispiel zeigt eine UPDATE-Anweisung, die nach einem Bereich von Spaltenwerten filtert:

  UPDATE mydataset.mytable
  SET string_col = 'some string'
  WHERE id BETWEEN 54 AND 75;

  Hier ist ein ähnliches Beispiel, das nach einer kleinen Liste von Spaltenwerten filtert:

  UPDATE mydataset.mytable
  SET string_col = 'some string'
  WHERE id IN (54, 57, 60);

  In diesen Fällen sollten Sie das Clustering auf der Spalte id vornehmen.

• Wenn Sie OLTP-Funktionen benötigen, können Sie föderierte Cloud SQL-Abfragen verwenden, mit denen BigQuery Daten abfragen kann, die in Cloud SQL gespeichert sind.

Best Practices zur Optimierung der Abfrageleistung finden Sie unter Einführung in die Optimierung der Abfrageleistung.

Nächste Schritte

• Informationen zur DML-Syntax und Beispiele finden Sie unter DML-Syntax.
• Informationen zum Verwenden von DML-Anweisungen in geplanten Abfragen finden Sie unter Abfragen planen.