Google Cloud

BigQuery에서 가장 많은 비용을 발생시킨 쿼리 찾기 (INFORMATION_SCHEMA)

라온클 2025. 6. 4. 01:02

들어가며

본 포스팅에서는 BigQuery의 INFORMATION SCHEMA에 대한 소개와 INFORMATION SCHEMA를 활용하여 BigQuery 비용을 디테일하게 확인하는 방법에 대해 정리한다.

개인적으로 BigQuery는 INFORMATION SCHEMA의 존재를 알기 전후로 나뉘기 때문에 한번쯤 꼭 블로그에 남기고 싶었다.

 

 

BigQuery 비용 체계

BigQuery 비용 체계는 크게 두가지 Compute, Storage 비용 두가지로 나뉜다.

Compute 가격은 또 On-demand, Capacity 가격 체계로 나뉘고, 그외에 BigQuery Omni, BigQuery ML, BI Engine, 스트리밍 읽기 및 쓰기, FreeTier, Trial 등등 다양한 가격 체계가 있다. 가격 정책은 자주 업데이트 되기 때문에 본 포스팅에서 자세한 내용은 생략한다.

https://cloud.google.com/bigquery/pricing

 

 

 

 

BigQuery의 INFORMATION_SCHEMA

INFORMATION_SCHEMA 뷰는 BigQuery에 기본적으로 내장되어 있는 읽기 전용 뷰다.

세션, 예약, 작업, 액세스 제어 등등 BigQuery에서 이루어지는 객체들에 대해 상세한 메타데이터를 볼 수 있다.

이는 곧 ‘INFORMATION_SCHEMA’ 뷰를 활용하면 조건(프로젝트별, 사용자별, 날짜별 등) 별로 디테일한 로깅 확인을 할 수 있게 해준다.

예시)

  • 비용을 많이 발생시키는 쿼리가 무엇인지?
  • 지난 7일간 특정 프로젝트의 모든 쿼리에 대한 평균 슬롯 활용률은 얼마인지?
  • 어떤 사용자가 특정 프로젝트에 대한 일괄 로드 작업을 제출했는지?


INFORMATION_SCHEMA 뷰가 제공하는 뷰들은 공식 문서에서 확인해보자.

https://cloud.google.com/bigquery/docs/information-schema-intro

 

 

INFORMATION_SCHEMA 뷰에 대한 특징을 몇가지 정리해보면 다음과 같다.

  • 범위 : INFORMATION_SCHEMA 뷰에 쿼리를 할 땐 위치(Location)을 지정해줘야 한다.
  • 일부 뷰에서는 데이터 세트 한정자 또는 리전 한정자를 지정해줘야 한다.
    • 데이터 세트 한정자 예시 : TABLES , VIEWS , PARTITIONS , COLUMNS
    • 리전 한정자 예시 : region-us , region-asia-northeast3
  • 가격
    • INFORMATION_SCHEMA 쿼리는 캐시되지 않으므로 쿼리 텍스트가 동일해도 INFORMATION_SCHEMA 쿼리를 실행할 때마다 요금이 청구된다.
    • INFORMATION_SCHEMA 뷰에 대한 스토리지 수수료는 청구되지 않는다.

 

 

 

INFORMATION SCHEMA의 활용

예시1 : 특정 날짜에 서울 리전에서 가장 많은 분석 비용을 발생시킨 쿼리 찾기

“Billing Report를 보니 n월 n일 쿼리 비용이 갑자기 치솟았는데, 구체적으로 어떤 쿼리가(또는 어느 사용자가) 비용을 많이 발생시켰는지 알고싶다” 라는 상황에서 쓸 수 있다.

 

아래 쿼리는 2024년 11월 1일 서울 리전에서 많은 분석 비용을 발생시킨 쿼리를 비용 내림차순으로 정렬한 쿼리다.

SELECT
  DATETIME(creation_time, 'Asia/Seoul') as kst_creation_time,
  user_email, 
  query,
  REGEXP_EXTRACT(query, r'FROM\s+`?([^\s`]+)`?') AS table_name, 
  job_id,
  total_slot_ms / 1000 AS total_slot_seconds,
  total_bytes_processed,  
  total_bytes_billed,
  (total_bytes_billed / 1099511627776) * 7.5 AS estimated_analysis_cost_usd
FROM
  `region-asia-northeast3`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  DATE(creation_time) = '2024-11-01' 
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND total_bytes_processed > 0
ORDER BY
  estimated_analysis_cost_usd DESC
  • kst_creation_time : 작업의 생성 시간대인 creation_time 를 한국 시간대로 변경
  • user_email : 작업을 실행한 사용자의 이메일 주소 또는 서비스 계정
  • query : SQL 쿼리 텍스트
  • table_name : 테이블 또는 뷰의 이름
  • job_id : 작업의 ID
  • total_slot_seconds
    • total_slot_ms : 재시도 포함하여 RUNNING 상태에서 작업이 지속되는 동안의 슬롯 밀리초
    • total_slot_ms 을 1000 으로 나눈 이유 : BigQuery의 Slot은 초당 청구되므로, 밀리초 단위인 total_slot_ms 를 1000 단위로 나눈다.(1 밀리초 = 0.001초)
  • total_bytes_processed : 작업에서 처리한 총 바이트
  • total_bytes_billed : 온디맨드에서 작업에 대해 청구되는 총 바이트
  • estimated_analysis_cost_usd
    • total_bytes_billed를 1099511627776로 나눈 이유 : BigQuery 쿼리 비용은 TiB 기준으로 작성된다. total_bytes_billed의 단위가 bytes이므로, 1099511627776 를 곱하여 1TiB 단위로 만들어준다.
    • 7.5를 곱한 이유 : 현재 (2025년 6월 3일) 기준으로 서울 리전에서 On-demand 쿼리 비용은 1TiB 당 $7.5 이다.

US 에서는 1TiB 당 6.25달러

 

 

 

쿼리를 실행하면 다음과 같은 결과를 확인할 수 있다.

 

 

 

 

 

 

 

활용 예시2 : US 리전에서 가장 많은 분석 비용을 발생시킨 쿼리 찾기(멀티 문 쿼리 제외)

본 포스팅을 쓰면서 INFORMATION_SCHEMA.JOBS 뷰의 특징 몇가지를 알게 되어 예시1 쿼리에서 다음 세가지를 제외하였다.

  • creation_time WHERE 절 : INFORMATION_SCHEMA.JOBS 뷰는 최근 180일만 포함되어 제외함
  • 프로젝트 한정자 _BY_PROJECT : 선택사항이기에 제외
  • 멀티문 쿼리 : statement_type != 'SCRIPT' 추가
SELECT
  DATETIME(creation_time, 'Asia/Seoul') as kst_creation_time,
  user_email,
  statement_type, 
  query,
  REGEXP_EXTRACT(query, r'FROM\s+`?([^\s`]+)`?') AS table_name,
  job_id,
  total_slot_ms / 1000 AS total_slot_seconds,
  total_bytes_processed,
  (total_bytes_billed / 1099511627776) AS estimated_analysis_cost_usd
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  job_type = 'QUERY'
  AND state = 'DONE'
  AND total_bytes_processed > 0
  AND statement_type != 'SCRIPT'
ORDER BY
  estimated_analysis_cost_usd DESC

 

 

쿼리를 실행하면 다음과 같은 결과를 확인할 수 있다.

 

 

 

참고

반응형
저작자표시 비영리 (새창열림)