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

이 페이지에서는 데이터 조작 언어(DML)를 사용한 BigQuery 테이블의 데이터를 업데이트 및 삭제하는 방법을 설명합니다. 이 페이지에서는 DML을 사용하여 기존 테이블에 행을 추가하는 방법을 다루지 않습니다. DML을 사용하여 행을 추가하는 방법에 대한 자세한 내용은 DML 구문 참조의 INSERT 문을 참조하세요.

BigQuery의 DML에는 몇 가지 제한사항이 있습니다. 또한 DML에는 자체 가격이 적용됩니다.

참고: DML 기능에는 표준 SQL을 사용해야 합니다 표준 SQL을 사용 설정하려면 SQL 언어 전환을 참조하세요.

데이터 업데이트

익명처리를 위해 마스킹하려는 IP 주소 열이 포함된 테이블이 표시된 이 샘플 파일을 사용하여 다음 안내를 따르세요.

• userSessionsData.json(다운로드)

다음 단계에서는 샘플 데이터를 테이블에 로드하고 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 참조 문서를 확인하세요.

GitHub에서 보기 의견 보내기

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

이 샘플을 사용해 보기 전에 BigQuery 빠른 시작: 클라이언트 라이브러리 사용의 Python 설정 안내를 따르세요. 자세한 내용은 BigQuery Python API 참조 문서를 확인하세요.

GitHub에서 보기 의견 보내기

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)

데이터 삭제

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

• deletedUsersData.json(다운로드)
• usersData.json(다운로드)
• userSessionsData.json(다운로드)

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

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

Console

1. Cloud Console을 엽니다.

2. 탐색기 패널에서 프로젝트를 확장하고 데이터 세트를 선택합니다.

3. 작업 옵션을 펼치고 열기를 클릭합니다.

4. 세부정보 패널에서 테이블 만들기를 클릭합니다.

5. 다음 항목으로 테이블 만들기에서 업로드를 선택합니다.

6. 파일 선택에서 다운로드한 파일을 찾아서 선택합니다.

   

7. 파일 형식에서 JSON(줄바꿈으로 구분)을 선택합니다.

8. 적합한 테이블 이름을 선택합니다.

9. 스키마 아래에서 필드 추가를 클릭하고 테이블 각 열의 이름을 입력한 후 적절한 유형을 선택합니다.

   • 필드 추가를 클릭하고 테이블에 모든 열을 입력할 때까지 반복합니다.

10. 테이블 만들기를 클릭합니다.

샘플 테이블의 스키마는 다음과 같습니다.

• 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)

테이블 보안

BigQuery에서 테이블에 대한 액세스를 제어하려면 테이블 액세스 제어 소개를 참조하세요.

다음 단계

• DML 참조 페이지를 참조하세요.
• DML 구문 페이지에서 DML 구문과 샘플을 참조하세요.
• 여러 DML 문을 실행하고 결과를 원자적으로 커밋할 수 있게 해주는 멀티 문 트랜잭션에 대해 알아보세요.