BigQuery에서 Bigtable 변경 로그 쿼리

이 페이지에서는 BigQuery에서 Bigtable 변경 로그를 처리하는 데 도움이 되는 안내와 쿼리 예시를 제공합니다.

이 페이지는 다음 단계를 완료한 사용자를 대상으로 합니다.

이 가이드에서는 BigQuery에 대한 기본적인 지식을 갖추고 있다고 가정합니다. 자세한 내용은 데이터 로드 및 쿼리 방법을 보여주는 빠른 시작을 참조하세요.

변경 로그 테이블 열기

  1. Google Cloud 콘솔에서 BigQuery 페이지로 이동합니다.

    BigQuery로 이동

  2. 탐색기 창에서 내 프로젝트 ID를 펼칩니다.

  3. 데이터 세트를 확장합니다.

  4. 서픽스가 _changelog인 테이블을 클릭합니다.

테이블 형식

전체 출력 스키마에는 여러 열이 포함됩니다. 이 가이드에서는 행을 열 및 값에 연결하고 값을 분석 가능한 형식으로 파싱하는 방법을 중점적으로 설명합니다.

기본 쿼리

이 섹션의 예시에서는 Bigtable 테이블을 사용하여 신용카드 판매를 추적합니다. 이 테이블에는 column family가 한 개(cf)와 다음 열이 있습니다.

  • credit card number#transaction timestamp 형식의 row key
  • 판매자
  • 수량
  • 카테고리
  • 거래 날짜

한 개의 열 쿼리

WHERE 절을 사용하여 결과를 하나의 column family와 하나의 열로 필터링합니다.

SELECT row_key, column_family, column, value, timestamp,
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="merchant"
LIMIT 1000

값 파싱

모든 값은 문자열 또는 바이트 문자열로 저장됩니다. 변환 함수를 사용하여 값을 의도한 유형으로 변환할 수 있습니다.

SELECT row_key, column_family, column, value, CAST(value AS NUMERIC) AS amount
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="amount"
LIMIT 1000

집계 수행

숫자 값에 대한 집계와 같은 추가 작업을 수행할 수 있습니다.

SELECT SUM(CAST(value AS NUMERIC)) as total_amount
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="amount"

데이터 피벗

여러 Bigtable 열이 포함된 쿼리를 수행하려면 테이블을 피벗해야 합니다. 각각의 새 BigQuery 행에는 Bigtable 테이블의 해당 행에서 변경 내역이 반환한 데이터 변경 레코드 1개가 포함됩니다. 스키마에 따라 row key와 타임스탬프 조합을 사용하여 데이터를 그룹화할 수 있습니다.

SELECT * FROM (
  SELECT row_key, timestamp, column, value
  FROM your_dataset.your_table
)
PIVOT (
  MAX(value)
  FOR column in ("merchant", "amount", "category", "transaction_date")
)

동적 열 세트로 피벗

동적 열 세트가 있는 경우 모든 열을 가져와서 프로그래매틱 방식으로 쿼리에 넣을 수 있도록 추가 처리를 수행할 수 있습니다.

DECLARE cols STRING;
SET cols = (
  SELECT CONCAT('("', STRING_AGG(DISTINCT column, '", "'), '")'),
  FROM your_dataset.your_table
);

EXECUTE IMMEDIATE format("""
SELECT * FROM (
  SELECT row_key, timestamp, column, value
  FROM your_dataset.your_table
)
PIVOT (
  MAX(value)
  FOR column in %s
)""", cols);

JSON 데이터

JSON으로 모든 값을 설정하려면 값을 파싱하고 키를 기반으로 값을 추출해야 합니다. JSON 객체에서 값을 도출한 후에는 파싱 함수를 사용할 수 있습니다. 이 예시에서는 앞에서 소개한 신용카드 판매 데이터를 사용하지만 여러 열에 데이터를 쓰는 대신 데이터가 단일 열에 JSON 객체로 기록됩니다.

SELECT
  row_key,
  JSON_VALUE(value, "$.category") as category,
  CAST(JSON_VALUE(value, "$.amount") AS NUMERIC) as amount
FROM your_dataset.your_table
LIMIT 1000

JSON을 사용한 집계 쿼리

JSON 값으로 집계 쿼리를 실행할 수 있습니다.

SELECT
  JSON_VALUE(value, "$.category") as category,
  SUM(CAST(JSON_VALUE(value, "$.amount") AS NUMERIC)) as total_amount
FROM your_dataset.your_table
GROUP BY category

다음 단계