BigQuery에서 가장 많은 비용을 발생시킨 쿼리 찾기 (INFORMATION_SCHEMA)
들어가며
본 포스팅에서는 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 이다.
- total_bytes_billed를 1099511627776로 나눈 이유 : BigQuery 쿼리 비용은 TiB 기준으로 작성된다. total_bytes_billed의 단위가 bytes이므로,
쿼리를 실행하면 다음과 같은 결과를 확인할 수 있다.
활용 예시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
쿼리를 실행하면 다음과 같은 결과를 확인할 수 있다.
참고
- BigQuery 가격 책정 > 주문형 컴퓨팅 가격 책정 : https://cloud.google.com/bigquery/pricing#on_demand_pricing
- INFORMATION_SCHEMA.JOBS 뷰 > 프로젝트별로 비용이 가장 많이 드는 쿼리 : https://cloud.google.com/bigquery/docs/information-schema-jobs#most_expensive_queries_by_project
- INFORMATION_SCHEMA.JOBS 뷰 > 멀티 문 쿼리 작업 > 하위 작업 : https://cloud.google.com/bigquery/docs/information-schema-jobs?hl=ko#child_jobs