データ操作言語を使用したテーブルデータの更新

このページでは、DML を使用して BigQuery テーブルのデータを更新および削除する方法について説明します。DML を使用して既存のテーブルに行を追加する方法については、このページでは取り上げません。DML を使用した行の追加については、DML 構文リファレンスの INSERT ステートメントをご覧ください。

BigQuery の DML には、いくつかの制限事項既知の問題があります。また、DML 固有の割り当て料金もあります。

をご覧ください。

データの更新

次のサンプル ファイルを使用して以下の手順を行います。このファイルは、匿名化のためにマスクする IP アドレス列を含むテーブルです。

次の手順に沿ってサンプルデータをテーブルに読み込み、ip_address 列の値を更新します。

ステップ 1. UserSessions テーブルに JSON ファイルを読み込みます。

ステップ 2. 各行の ip_address 列の最後のオクテットをマスクするには、次の DML クエリを実行します。

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

Java

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Java の設定手順を実施してください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。

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 runUpdateTableDML() 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());
    }
  }
}

データの削除

次のサンプル ファイルを使用して以下の手順を行います。これらのファイルは、ユーザー セッション解析用の複数のテーブルと削除対象ユーザーのテーブルを含むデータセットです。

次の手順では、データを 3 つのテーブルに読み込んでから、DeletedUsers テーブルのユーザーを削除します。

ステップ 1. DeletedUsers、Users、UserSessions の各テーブルに JSON ファイルを読み込みます

Console

  1. Cloud Console を開きます
  2. [リソース] リストからデータセットを選択します。
  3. [テーブルを作成] をクリックします。

    テーブルの作成

  4. [テーブルの作成元] で [アップロード] を選択します。

  5. [ファイルを選択] で、ダウンロードしたファイルを参照して選択します。

    ファイルを参照

  6. [ファイル形式] で [JSON(改行区切り)] を選択します。

  7. [テーブル名] に、適切なテーブル名を入力します。

  8. [スキーマ] セクションの [フィールドを追加] をクリックし、[名前] にテーブル内の列の名前を入力し、[] で適切なデータ型を選択します。

    • テーブル内のすべての列を入力するまで、[フィールドを追加] をクリックして、この操作を繰り返します。
  9. [テーブルを作成] をクリックします。

サンプル テーブルのスキーマは次のとおりです。

  • DeletedUsers
    • 名前 id、型 INTEGER
  • Users
    • 名前 id、型 INTEGER
    • 名前 date_joined、型 TIMESTAMP
  • UserSessions
    • 名前 id、型 STRING
    • 名前 user_id、型 INTEGER
    • 名前 login_time、型 TIMESTAMP
    • 名前 logout_time、型 TIMESTAMP
    • 名前 ip_address、型 STRING

従来の UI

  1. BigQuery ウェブ UI を開きます
  2. データセット ID にカーソルを合わせます。
  3. データセット ID の横にある矢印 データセット名の横にある矢印 をクリックします。
  4. [Create new table] をクリックします。
  5. [Location] で [File upload] を選択します。
  6. [ファイル形式] で [JSON(改行区切り)] を選択します。
  7. [Table name] でテーブル名を選択します。
  8. [Schema] で、[Name] にテーブルの各列の名前を入力し、[Type] で適切なデータ型を選択します。

    • テーブル内のすべての列を入力するまで、[フィールドを追加] をクリックして、この操作を繰り返します。
  9. [テーブルを作成] をクリックします。

サンプル テーブルのスキーマは次のとおりです。

  • DeletedUsers
    • 名前 id、型 INTEGER
  • Users
    • 名前 id、型 INTEGER
    • 名前 date_joined、型 TIMESTAMP
  • UserSessions
    • 名前 id、型 STRING
    • 名前 user_id、型 INTEGER
    • 名前 login_time、型 TIMESTAMP
    • 名前 logout_time、型 TIMESTAMP
    • 名前 ip_address、型 STRING

bq

bq コマンドライン ツールを使用してテーブルを作成するには、bq load コマンドを使用します。--location フラグを指定して、その値をロケーションに設定します。--location フラグは省略可能です。たとえば、BigQuery を asia-northeast1(東京)リージョンで使用している場合、load コマンドは以下のようになります。

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

DeleteUsers テーブルの作成

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

Users テーブルの作成

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

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

ステップ 2. DeletedUsers テーブルでユーザーに関する情報を削除するには、次の DML クエリを実行します。

  • UsersSessions からの削除

    DELETE FROM sample_db.UserSessions
    WHERE user_id in (SELECT id from sample_db.DeletedUsers)
    
  • Users からの削除

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

次のステップ