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

다음 단계