Daten aus partitionierten Tabellen mithilfe von DML aktualisieren

Diese Seite bietet eine Übersicht über die Unterstützung der Datenbearbeitungssprache (Data Manipulation Language, DML) für partitionierte Tabellen.

Weitere Informationen zu DML finden Sie unter:

In Beispielen verwendete Tabellen

Die folgenden JSON-Schemadefinitionen stellen die Tabellen dar, die in den Beispielen auf dieser Seite verwendet werden.

mytable: eine nach Aufnahmezeit partitionierte Tabelle

    [
      {"name": "field1", "type": "INTEGER"},
      {"name": "field2", "type": "STRING"}
    ]

mytable2: eine Standardtabelle (nicht partitioniert)

    [
      {"name": "id", "type": "INTEGER"},
      {"name": "ts", "type": "TIMESTAMP"}
    ]

mycolumntable: eine nach der Spalte ts TIMESTAMP partitionierte Tabelle

    [
      {"name": "field1", "type": "INTEGER"},
      {"name": "field2", "type": "STRING"}
      {"name": "field3", "type": "BOOLEAN"}
      {"name": "ts", "type": "TIMESTAMP"}
    ]

Ersetzen Sie in Beispielen, in denen COLUMN_ID erscheint, die ID durch den Namen der Spalte, die Sie bearbeiten möchten.

Daten einfügen

Mit der DML-Anweisung INSERT können Sie einer partitionierten Tabelle Zeilen hinzufügen.

Daten in nach Aufnahmezeit partitionierte Tabellen einfügen

Wenn Sie einer nach Aufnahmezeit partitionierten Tabelle Zeilen mithilfe einer DML-Anweisung hinzufügen möchten, können Sie die Partition festlegen, der die Zeilen hinzugefügt werden sollen. Mit der Pseudospalte _PARTITIONTIME verweisen Sie auf die Partition.

Durch die folgende INSERT-Anweisung wird beispielsweise der Partition von mytable vom 1. Mai 2017, “2017-05-01”, eine Zeile hinzugefügt.

INSERT INTO
  project_id.dataset.mytable (_PARTITIONTIME,
    field1,
    field2)
SELECT
  TIMESTAMP("2017-05-01"),
  1,
  "one"

Es können nur Zeitstempel verwendet werden, die den genauen Datumsgrenzen entsprechen. Bei der folgenden DML-Anweisung wird beispielsweise ein Fehler zurückgegeben:

INSERT INTO
  project_id.dataset.mytable (_PARTITIONTIME,
    field1,
    field2)
SELECT
  TIMESTAMP("2017-05-01 21:30:00"),
  1,
  "one"

Daten in partitionierte Tabellen einfügen

Das Einfügen von Daten in eine partitionierte Tabelle mithilfe von DML erfolgt auf die gleiche Weise wie das Einfügen von Daten in eine nicht partitionierte Tabelle.

Durch die folgende INSERT-Anweisung werden Zeilen zur partitionierten Tabelle mycolumntable hinzugefügt, indem Daten aus mytable2 (einer nicht partitionierten Tabelle) ausgewählt werden.

INSERT INTO
  project_id.dataset.mycolumntable (ts,
    field1)
SELECT
  ts,
  id
FROM
  project_id.dataset.mytable2

Daten löschen

Mit einer DELETE-DML-Anweisung können Sie Zeilen aus einer partitionierten Tabelle löschen.

Daten in nach Aufnahmezeit partitionierten Tabellen löschen

Mit der folgenden DELETE-Anweisung werden alle Zeilen aus der Partition vom 1. Juni 2017 ("2017-06-01") von mytable gelöscht, wobei field1 gleich 21 ist. Mit der Pseudospalte _PARTITIONTIME verweisen Sie auf die Partition.

DELETE
  project_id.dataset.mytable
WHERE
  field1 = 21
  AND _PARTITIONTIME = "2017-06-01"

Daten in partitionierten Tabellen löschen

Das Löschen von Daten in einer partitionierten Tabelle mithilfe von DML erfolgt auf die gleiche Weise wie das Löschen von Daten aus einer nicht partitionierten Tabelle.

Mit der folgenden DELETE-Anweisung werden beispielsweise alle Zeilen aus der Partition vom 1. Juni 2017 ("2017-06-01") von mycolumntable gelöscht, wobei field1 gleich 21 ist.

DELETE
  project_id.dataset.mycolumntable
WHERE
  field1 = 21
  AND DATE(ts) = "2017-06-01"

Partitionen mit DML DELETE löschen

Wenn eine qualifizierende DELETE-Anweisung alle Zeilen in einer Partition abdeckt, entfernt BigQuery die gesamte Partition. Das Entfernen erfolgt, ohne Byte zu scannen oder Slots zu verbrauchen. Das folgende Beispiel einer DELETE-Anweisung deckt die gesamte Partition eines Filters in der Pseudospalte _PARTITIONDATE ab:

DELETE mydataset.mytable
WHERE _PARTITIONDATE IN ('2076-10-07', '2076-03-06');

Häufige Ablehnungen

Abfragen mit den folgenden Merkmalen profitieren möglicherweise nicht von der Optimierung:

Die Berechtigung für die Optimierung kann je nach Art der Partitionierung, den zugrunde liegenden Speichermetadaten und den Filterprädikaten variieren. Führen Sie als Best Practice einen Probelauf durch, um zu prüfen, ob die Abfrage zu 0 Byte verarbeitet wird.

Transaktion mit mehreren Anweisungen

Diese Optimierung funktioniert in einer Transaktion mit mehreren Anweisungen. Im folgenden Abfragebeispiel wird eine Partition in einer einzelnen Transaktion durch Daten aus einer anderen Tabelle ersetzt, ohne die Partition nach der Anweisung DELETE zu scannen.

DECLARE REPLACE_DAY DATE;
BEGIN TRANSACTION;

-- find the partition which we want to replace
SET REPLACE_DAY = (SELECT MAX(d) FROM mydataset.mytable_staging);

-- delete the entire partition from mytable
DELETE FROM mydataset.mytable WHERE part_col = REPLACE_DAY;

-- insert the new data into the same partition in mytable
INSERT INTO mydataset.mytable
SELECT * FROM mydataset.mytable_staging WHERE part_col = REPLACE_DAY;

COMMIT TRANSACTION;

Daten aktualisieren

Mit einer UPDATE-Anweisung können Sie Zeilen in einer partitionierten Tabelle aktualisieren.

Daten in nach Aufnahmezeit partitionierten Tabellen aktualisieren

Durch die folgende UPDATE-Anweisung werden Zeilen zwischen Partitionen verschoben. Zeilen in der Partition vom 1. Mai 2017 (“2017-05-01”) von mytable, bei denen field1 gleich 21 ist, werden in die Partition vom 1. Juni 2017 (“2017-06-01”) verschoben.

UPDATE
  project_id.dataset.mytable
SET
  _PARTITIONTIME = "2017-06-01"
WHERE
  _PARTITIONTIME = "2017-05-01"
  AND field1 = 21

Daten in partitionierten Tabellen aktualisieren

Das Aktualisieren von Daten in einer partitionierten Tabelle mithilfe von DML erfolgt auf die gleiche Weise wie das Aktualisieren von Daten in einer nicht partitionierten Tabelle. Mit der folgenden UPDATE-Anweisung werden beispielsweise Zeilen zwischen Partitionen verschoben. Zeilen in der Partition vom 1. Mai 2017 (“2017-05-01”) von mytable, bei denen field1 gleich 21 ist, werden in die Partition vom 1. Juni 2017 (“2017-06-01”) verschoben.

UPDATE
  project_id.dataset.mycolumntable
SET
  ts = "2017-06-01"
WHERE
  DATE(ts) = "2017-05-01"
  AND field1 = 21

DML in stündlich, monatlich und jährlich partitionierten Tabellen

Mit DML-Anweisungen können Sie eine stündlich, monatlich oder jährlich partitionierte Tabelle ändern. Geben Sie die Stunde, den Monat oder das Jahr des entsprechenden Datums/Zeitstempels/Datums-/Uhrzeitformats an, wie im folgenden Beispiel für monatlich partitionierte Tabellen gezeigt:

    bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE
    TIMESTAMP_TRUNC(ts_column, MONTH) = "2020-01-01 00:00:00";'

Oder ein anderes Beispiel für partitionierte Tabellen mit der Spalte DATETIME:

    bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE
    dt_column BETWEEN DATETIME("2020-01-01")
    AND DATETIME("2020-05-01");'

MERGE-Anweisung verwenden

Mit einer MERGE-DML-Anweisung können Sie die Vorgänge INSERT, UPDATE und DELETE für eine partitionierte Tabelle in einer Anweisung zusammenfassen und so gemeinsam ausführen.

Partitionen bei Verwendung einer MERGE-Anweisung bereinigen

Wenn Sie eine MERGE-Anweisung für eine partitionierte Tabelle ausführen, können Sie begrenzen, welche Partitionen gescannt werden. Fügen Sie dazu die Partitionierungsspalte in einen Unterabfragefilter, einen search_condition-Filter oder einen merge_condition-Filter ein. Das Bereinigen kann beim Scannen der Quelltabelle oder der Zieltabelle oder von beidem auftreten.

In jedem der folgenden Beispiele wird eine nach Aufnahmezeit partitionierte Tabelle mithilfe der Pseudospalte _PARTITIONTIME als Filter abgefragt.

Quelldaten mithilfe einer Unterabfrage filtern

In der folgenden MERGE-Anweisung filtert die Unterabfrage in der USING-Klausel nach der Pseudospalte _PARTITIONTIME in der Quelltabelle.

MERGE dataset.target T
USING (SELECT * FROM dataset.source WHERE _PARTITIONTIME = '2018-01-01') S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED THEN
  DELETE

Die Unterabfrage wird dann gemäß dem Abfrageausführungsplan zuerst ausgeführt. Nur die Zeilen in der Partition '2018-01-01' in der Quelltabelle werden gescannt. Hier ist die relevante Phase im Abfrageplan:

READ $10:name, $11:_PARTITIONTIME
FROM temp.source
WHERE equal($11, 1514764800.000000000)

Filter in der search_condition einer when_clause verwenden

Wenn eine search_condition einen Filter enthält, versucht die Abfrageoptimierung, Partitionen zu bereinigen. In der folgenden MERGE-Anweisung enthält beispielsweise jede WHEN MATCHED- und WHEN NOT MATCHED-Klausel einen Filter für die Pseudospalte _PARTITIONTIME.

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-02' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID + 10
WHEN NOT MATCHED BY SOURCE AND T._PARTITIONTIME = '2018-01-03' THEN
  DELETE

Während der Join-Phase werden nur die folgenden Partitionen in der Zieltabelle gescannt: '2018-01-01', '2018-01-02' und '2018-01-03', also die Union aller search_condition-Filter.

Gehen Sie im Abfrageausführungsplan so vor:

READ
$1:COLUMN_ID, $2:_PARTITIONTIME, $3:$file_temp_id, $4:$row_temp_id
FROM temp.target
WHERE or(equal($2, 1514764800.000000000), equal($2, 1514851200.000000000), equal($2, 1514937600.000000000))

Im folgenden Beispiel hat die WHEN NOT MATCHED BY SOURCE-Klausel jedoch keinen Filterausdruck:

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET COLUMN_ID = COLUMN_ID + 1

Diese Abfrage muss die gesamte Zieltabelle scannen, um die WHEN NOT MATCHED BY SOURCE-Klausel zu berechnen. Daher werden keine Partitionen bereinigt.

Konstantes falsches Prädikat in einer merge_condition verwenden

Wenn Sie die Klauseln WHEN NOT MATCHED und WHEN NOT MATCHED BY SOURCE zusammen verwenden, führt BigQuery normalerweise einen vollständigen äußeren Join aus, der nicht bereinigt werden kann. Wenn die Zusammenführungsbedingung jedoch ein konstantes falsches Prädikat verwendet, kann BigQuery die Filterbedingung für die Partitionsbereinigung nutzen. Weitere Informationen zur Verwendung konstanter falscher Prädikate finden Sie in der Beschreibung der merge_condition-Klausel in der Dokumentation zur MERGE-Anweisung.

Im folgenden Beispiel wird nur die Partition '2018-01-01' in der Ziel- und der Quelltabelle gescannt.

MERGE dataset.target T
USING dataset.source S
ON FALSE
WHEN NOT MATCHED AND _PARTITIONTIME = '2018-01-01' THEN
  INSERT(COLUMN_ID) VALUES(COLUMN_ID)
WHEN NOT MATCHED BY SOURCE AND _PARTITIONTIME = '2018-01-01' THEN
  DELETE

Filter in einer merge_condition verwenden

Die Abfrageoptimierung versucht, Partitionen mithilfe eines Filters in einer merge_condition zu bereinigen. Die Abfrageoptimierung ist vielleicht in der Lage, das Prädikat je nach Join-Typ nach unten in die Tabellenscan-Phase zu verschieben.

Im folgenden Beispiel wird die merge_condition als Prädikat verwendet, um die Quell- und die Zieltabelle zu verbinden. Die Abfrageoptimierung kann dieses Prädikat nach unten verschieben, wenn beide Tabellen gescannt werden. Daher scannt die Abfrage nur die Partition '2018-01-01' in der Ziel- und der Quelltabelle.

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID AND
  T._PARTITIONTIME = '2018-01-01' AND
  S._PARTITIONTIME = '2018-01-01'
WHEN MATCHED THEN
  UPDATE SET COLUMN_ID = NEW_VALUE

Im nächsten Beispiel enthält die merge_condition kein Prädikat für die Quelltabelle. Daher kann keine Partitionsbereinigung für die Quelltabelle durchgeführt werden. Die Anweisung enthält ein Prädikat für die Zieltabelle, aber die Anweisung verwendet eine WHEN NOT MATCHED BY SOURCE-Klausel statt einer WHEN MATCHED-Klausel. Das bedeutet, dass die Abfrage die gesamte Zieltabelle nach den nicht übereinstimmenden Zeilen scannen muss.

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID AND T._PARTITIONTIME = '2018-01-01'
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET COLUMN_ID = NEW_VALUE

Beschränkungen

Informationen zu DML-Beschränkungen finden Sie unter Beschränkungen in der DML-Referenz.

Kontingente

Informationen zu DML-Kontingenten erhalten Sie auf der Seite Kontingente und Limits unter DML-Anweisungen.

Preise

Informationen zu DML-Preisen finden Sie unter DML-Preise für partitionierte Tabellen.

Tabellensicherheit

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

Nächste Schritte