DML을 사용하여 테이블 데이터 업데이트

이 페이지에서는 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

자바

이 샘플을 사용해 보기 전에 BigQuery 빠른 시작: 클라이언트 라이브러리 사용의 자바 설정 안내를 따르세요. 자세한 내용은 BigQuery 자바 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());
    }
  }
}

데이터 삭제

사용자 세션에 대한 여러 분석 테이블과 삭제할 사용자 테이블이 있는 데이터세트를 나타내는 샘플 파일을 사용하여 아래 안내를 따르세요.

다음 단계에서는 데이터를 테이블 세 개에 로드한 후 DeletedUsers 테이블에 나열된 사용자를 삭제합니다.

1단계: JSON 파일을 DeletedUsers, Users, UserSessions 테이블에 각각 로드합니다.

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. 새 테이블 만들기를 클릭합니다.
  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

bq

bq 명령줄 도구를 사용하여 테이블을 만들려면 bq load 명령어를 사용합니다. --location 플래그를 지정하고 값을 사용자 위치로 설정합니다. --location 플래그는 선택사항입니다. 예를 들어 asia-northeast1(도쿄) 리전에서 BigQuery를 사용한다면 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)
    

다음 단계

  • DML 참조 페이지를 참조하세요.
  • DML 구문 페이지에서 DML 구문과 샘플을 참조하세요.