Tabellendaten mit der Datenbearbeitungssprache aktualisieren

Auf dieser Seite wird erläutert, wie Sie Daten in BigQuery-Tabellen mithilfe der Datenbearbeitungssprache (DML) aktualisieren bzw. löschen. Das Einfügen von Zeilen in vorhandene Tabellen mit DML wird hier nicht behandelt. Informationen zum Einfügen von Zeilen mit DML finden Sie in der DML-Syntaxreferenz im Abschnitt zur INSERT-Anweisung.

Beachten Sie, dass es für DML in BigQuery einige Beschränkungen gibt. Für DML gelten außerdem eigene Preise.

Hinweis: Zur Nutzung der DML-Funktion müssen Sie Standard-SQL verwenden. Wie Sie Standard-SQL aktivieren, erfahren Sie unter SQL-Dialekte wechseln.

Daten aktualisieren

Befolgen Sie die Anleitungen unten, indem Sie diese Beispieldatei verwenden, die eine Tabelle mit einer IP-Adressenspalte darstellt, die sich zur Anonymisierung maskieren lässt:

• userSessionsData.json (Download)

Mit den folgenden Schritten werden die Beispieldaten in eine Tabelle geladen und die Werte in der Spalte ip_address aktualisiert:

Schritt 1: Laden Sie die JSON-Datei in eine Tabelle vom Typ UserSessions.

Schritt 2: Führen Sie die folgende DML-Abfrage aus, um das letzte Oktett in der Spalte ip_address jeder Zeile zu maskieren:

UPDATE sample_db.UserSessions
SET ip_address = REGEXP_REPLACE(ip_address, r"(\.[0-9]+)$", ".0")
WHERE TRUE

Java

Bevor Sie dieses Beispiel ausprobieren, folgen Sie den Schritten zur Einrichtung von Java in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Java API.

Auf GitHub ansehen Feedback

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableDataWriteChannel;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableResult;
import com.google.cloud.bigquery.WriteChannelConfiguration;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.channels.Channels;
import java.nio.file.FileSystems;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.UUID;

// Sample to update data in BigQuery tables using DML query
public class UpdateTableDml {

  public static void main(String[] args) throws IOException, InterruptedException {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    updateTableDml(datasetName, tableName);
  }

  public static void updateTableDml(String datasetName, String tableName)
      throws IOException, InterruptedException {
    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();

      // Load JSON file into UserSessions table
      TableId tableId = TableId.of(datasetName, tableName);

      WriteChannelConfiguration writeChannelConfiguration =
          WriteChannelConfiguration.newBuilder(tableId)
              .setFormatOptions(FormatOptions.json())
              .build();

      // Imports a local JSON file into a table.
      Path jsonPath =
          FileSystems.getDefault().getPath("src/test/resources", "userSessionsData.json");

      // The location and JobName must be specified; other fields can be auto-detected.
      String jobName = "jobId_" + UUID.randomUUID().toString();
      JobId jobId = JobId.newBuilder().setLocation("us").setJob(jobName).build();

      try (TableDataWriteChannel writer = bigquery.writer(jobId, writeChannelConfiguration);
          OutputStream stream = Channels.newOutputStream(writer)) {
        Files.copy(jsonPath, stream);
      }

      // Get the Job created by the TableDataWriteChannel and wait for it to complete.
      Job job = bigquery.getJob(jobId);
      Job completedJob = job.waitFor();
      if (completedJob == null) {
        System.out.println("Job not executed since it no longer exists.");
        return;
      } else if (completedJob.getStatus().getError() != null) {
        System.out.println(
            "BigQuery was unable to load local file to the table due to an error: \n"
                + job.getStatus().getError());
        return;
      }

      System.out.println(
          job.getStatistics().toString() + " userSessionsData json uploaded successfully");

      // Write a DML query to modify UserSessions table
      // To create DML query job to mask the last octet in every row's ip_address column
      String dmlQuery =
          String.format(
              "UPDATE `%s.%s` \n"
                  + "SET ip_address = REGEXP_REPLACE(ip_address, r\"(\\.[0-9]+)$\", \".0\")\n"
                  + "WHERE TRUE",
              datasetName, tableName);

      QueryJobConfiguration dmlQueryConfig = QueryJobConfiguration.newBuilder(dmlQuery).build();

      // Execute the query.
      TableResult result = bigquery.query(dmlQueryConfig);

      // Print the results.
      result.iterateAll().forEach(rows -> rows.forEach(row -> System.out.println(row.getValue())));

      System.out.println("Table updated successfully using DML");
    } catch (BigQueryException e) {
      System.out.println("Table update failed \n" + e.toString());
    }
  }
}

Python

Bevor Sie dieses Beispiel ausprobieren, folgen Sie den Schritten zur Einrichtung von Python in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur  Python API.

Auf GitHub ansehen Feedback

import pathlib
from typing import Dict, Optional

from google.cloud import bigquery
from google.cloud.bigquery import enums

def load_from_newline_delimited_json(
    client: bigquery.Client,
    filepath: pathlib.Path,
    project_id: str,
    dataset_id: str,
    table_id: str,
) -> None:
    full_table_id = f"{project_id}.{dataset_id}.{table_id}"
    job_config = bigquery.LoadJobConfig()
    job_config.source_format = enums.SourceFormat.NEWLINE_DELIMITED_JSON
    job_config.schema = [
        bigquery.SchemaField("id", enums.SqlTypeNames.STRING),
        bigquery.SchemaField("user_id", enums.SqlTypeNames.INTEGER),
        bigquery.SchemaField("login_time", enums.SqlTypeNames.TIMESTAMP),
        bigquery.SchemaField("logout_time", enums.SqlTypeNames.TIMESTAMP),
        bigquery.SchemaField("ip_address", enums.SqlTypeNames.STRING),
    ]

    with open(filepath, "rb") as json_file:
        load_job = client.load_table_from_file(
            json_file, full_table_id, job_config=job_config
        )

    # Wait for load job to finish.
    load_job.result()

def update_with_dml(
    client: bigquery.Client, project_id: str, dataset_id: str, table_id: str
) -> int:
    query_text = f"""
    UPDATE `{project_id}.{dataset_id}.{table_id}`
    SET ip_address = REGEXP_REPLACE(ip_address, r"(\\.[0-9]+)$", ".0")
    WHERE TRUE
    """
    query_job = client.query(query_text)

    # Wait for query job to finish.
    query_job.result()

    assert query_job.num_dml_affected_rows is not None

    print(f"DML query modified {query_job.num_dml_affected_rows} rows.")
    return query_job.num_dml_affected_rows

def run_sample(override_values: Optional[Dict[str, str]] = None) -> int:
    if override_values is None:
        override_values = {}

    client = bigquery.Client()
    filepath = pathlib.Path(__file__).parent / "user_sessions_data.json"
    project_id = client.project
    dataset_id = "sample_db"
    table_id = "UserSessions"
    load_from_newline_delimited_json(client, filepath, project_id, dataset_id, table_id)
    return update_with_dml(client, project_id, dataset_id, table_id)

Daten löschen

Führen Sie die Anleitung unten mit den Beispieldateien aus – ein Dataset mit mehreren Tabellen mit Analysen für Nutzersitzungen und eine Tabelle mit Nutzern, die gelöscht werden sollen.

• deletedUsersData.json (Download)
• usersData.json (Download)
• userSessionsData.json (Download)

Mit den folgenden Schritten werden die Daten in drei Tabellen geladen. Danach werden die in der DeletedUsers-Tabelle aufgeführten Nutzer gelöscht.

Schritt 1: Laden Sie die JSON-Dateien in die Tabellen "DeletedUsers", "Users" bzw. "UserSessions".

Console

1. Rufen Sie die Cloud Console auf.

2. Maximieren Sie im Bereich Explorer Ihr Projekt und wählen Sie ein Dataset aus.

3. Maximieren Sie die Option Aktionen und klicken Sie auf Öffnen.

4. Klicken Sie im Detailfeld auf Tabelle erstellen.

5. Wählen Sie unter Tabelle erstellen aus die Option Hochladen aus.

6. Suchen Sie unter Datei auswählen nach der heruntergeladenen Datei und wählen Sie sie aus.

   

7. Wählen Sie für Dateiformat die Option JSON (durch Zeilenumbruch getrennt) aus.

8. Wählen Sie einen geeigneten Table name (Tabellenname) aus.

9. Klicken Sie unter Schema auf Add field (Feld hinzufügen), geben Sie für jede Spalte in der Tabelle einen Namen ein und wählen Sie den entsprechenden Typ aus.

   • Klicken Sie auf Add field (Feld hinzufügen) und wiederholen Sie diesen Schritt, bis Sie alle Spalten in der Tabelle eingegeben haben.

10. Klicken Sie auf Create table (Tabelle erstellen).

Schemas für die Beispieltabellen:

• DeletedUsers
  • Name id, Typ INTEGER
• Nutzer
  • Name id, Typ INTEGER
  • Name date_joined, Typ TIMESTAMP
• UserSessions
  • Name id, Typ STRING
  • Name user_id, Typ INTEGER
  • Name login_time, Typ TIMESTAMP
  • Name logout_time, Typ TIMESTAMP
  • Name ip_address, Typ STRING

bq

Verwenden Sie den Befehl bq load, um die Tabellen mit dem bq-Befehlszeilentool zu erstellen. Geben Sie das Flag --location an und legen Sie als Wert Ihren Standort fest. Das Flag --location ist optional. Wenn Sie BigQuery beispielsweise in der Region asia-northeast1 (Tokio) verwenden, sieht der Ladebefehl so aus:

bq --location=asia-northeast1 load ...

So erstellen Sie die Tabelle DeleteUsers:

bq --location=asia-northeast1 load \
--source_format=NEWLINE_DELIMITED_JSON \
sample_db.DeletedUsers \
deletedUsersData.json \
id:integer

So erstellen Sie die Tabelle Users:

bq --location=asia-northeast1 load \
--source_format=NEWLINE_DELIMITED_JSON \
sample_db.Users \
usersData.json \
id:integer,date_joined:timestamp

So erstellen Sie die Tabelle UserSessions:

bq --location=asia-northeast1 load \
--source_format=NEWLINE_DELIMITED_JSON \
sample_db.UserSessions \
userSessionsData.json \
id:string,user_id:integer,login_time:timestamp,logout_time:timestamp,ip_address:string

Schritt 2: Löschen Sie die Informationen zu den Nutzern in der Tabelle "DeletedUsers". Führen Sie dazu die folgenden DML-Abfragen aus:

• So löschen Sie aus UsersSessions:

  DELETE FROM sample_db.UserSessions
  WHERE user_id in (SELECT id from sample_db.DeletedUsers)
  

• So löschen Sie aus Users:

  DELETE FROM sample_db.Users
  WHERE id in (SELECT id from sample_db.DeletedUsers)
  

Tabellensicherheit

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

Nächste Schritte

• DML-Referenzseite
• DML-Syntax und -Beispiele
• Weitere Informationen zu Transaktionen mit mehreren Anweisungen, mit denen Sie mehrere DML-Anweisungen ausführen und die Ergebnisse atomar festlegen können