[BigQuery] 비용 모니터링 구축기

개요

회사에서 DW로 BigQuery를 도입한지 대략 1달이 지났고 전월 BigQuery 비용이 생각보다 많이 나와서 쿼리 사용과 스토리지 등에 대한 모니터링이 필요하다고 느껴졌고 구축에 대한 내용과 이를 통한 얻은 효과에 대해 포스팅을 하려고 한다.

BigQuery 쿼리 사용량 모니터링

BigQuery는 쿼리 사용량과 스토리지 사용량으로 비용을 청구하는데 스토리지에 대한 비용은 매우 저렴하기 때문에 신경쓰지 않아도 큰 문제는 없어서 쿼리 사용량이 대부분이다. 그렇기 때문에 이를 모니터링 하기 위해서는 쿼리를 사용했다는 것에 대한 정보가 필요한데 다행히도 GCP에서 BigQuery를 사용한 로그를 남겨주기 때문에 이것을 활용하면 된다.

라우팅 및 스토리지 개요  |  Cloud Logging  |  Google Cloud

GCP에서는 BigQuery의 로그를 남기기 때문에 해당 로그를 BigQuery의 데이터 세트로 라우팅을 시켜주면 BigQuery를 통해 해당 로그를 쿼리를 할 수 있다.

BigQuery 사용 로그 수집

싱크 만들기

싱크 이름 작성

싱크 대상 선정

싱크를 통해 적재할 서비스와 데이터 세트를 선정한다.

• 파티션을 나눈 테이블을 사용하는 것을 추천한다. (로그성 데이터라 로그가 날짜별로 많이 쌓이게 됨.)

로그 포함 필터링

싱크를 통해 로그를 적재할 때, 해당 필드가 포함된 로그를 가져오도록 한다.

• 빈 공간으로 넘어가면 모든 로그가 적재된다.

로그 제외 필터링

로그를 적재할 때, 제외할 로그를 결정하는 부분.

로그 라우터 싱크 확인

BigQuery의 데이터 세트 확인

모니터링 대시보드 구축

자 이제, 로그 수집을 완료했으니 해당 로그를 활용하여 대시보드를 구축하면 된다. 해당 로그는 BigQuery 사용에 대한 로그로 쿼리 시각, 처리한 쿼리량, 쿼리한 유저, 쿼리 내용 등 유용한 정보들을 담고 있어서 이를 활용하면 쓸만한 대시보드를 만들 수 있다. 본인은 Grafana를 이용하여 모니터링 대시보드를 구축할 것이고 시각화 툴로는 어떤 것을 써도 무방하다.

Grafana 설정

Grafana에서 BigQuery를 Data Source 로 사용할 수 있는 Plugin 을 제공한다. 이를 통해 BigQuery에 직접 쿼리를 할 수 있어 따로 데이터 저장소를 구축하지 않아도 된다.

Google BigQuery plugin for Grafana | Grafana Labs

모니터링 대시보드

본인은 로그를 활용하여 아래와 같이 대시보드를 구축했고 각자 운영 환경에 맞게 적절한 대시보드를 구축하면 될 것 같다.

사용한 쿼리

일별 쿼리 사용량

SELECT tmp.time, SUM(tmp.totalBilledGB / 1024) AS totalBilledTB, 
FROM (
  SELECT 
    FORMAT_DATETIME('%Y-%m-%d', DATETIME(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.startTime, 'Asia/Seoul')) AS time, 
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes/(1024*1024*1024) AS totalBilledGB, 
  FROM 
    `bigquery_use_log.cloudaudit_googleapis_com_data_access`
) AS tmp
GROUP BY tmp.time
ORDER BY tmp.time DESC
LIMIT 7

이번 달 BigQuery 사용량 및 예상비용

SELECT 
  SUM(tmp.totalBilledGB / 1024) AS totalQuery, 
CASE
  WHEN SUM(tmp.totalBilledGB / 1024) < 1 THEN 0
  ELSE SUM(tmp.totalBilledGB / 1024) * 6 - 6
END AS totalCost
FROM (
  SELECT 
    FORMAT_DATETIME('%Y-%m', protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.startTime) AS starttime, 
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes/(1024*1024*1024) AS totalBilledGB, 
  FROM 
    `bigquery_use_log.cloudaudit_googleapis_com_data_access`
  WHERE 1=1
    AND EXTRACT(MONTH FROM protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.startTime) = EXTRACT(MONTH FROM CURRENT_DATE('Asia/Seoul'))
) AS tmp
GROUP BY tmp.starttime

총 사용 스토리지 및 예상비용

SELECT SUM(total_logical_bytes) / pow(1024, 4) AS totalStorage, SUM((active_logical_bytes / pow(1024, 3)) * 0.023 + (long_term_logical_bytes / pow(1024, 3)) * 0.016) AS totalCost
FROM <project-id>.region-asia-northeast3.INFORMATION_SCHEMA.TABLE_STORAGE

많이 접근한 테이블

SELECT 
        datasetId,
        tableId,
        count(*) as table_read_cnt,
FROM (     
    SELECT 
        protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.startTime,
        table_info.projectId AS projectId,
        table_info.datasetId AS datasetId,
        table_info.tableId AS tableId,
        protopayload_auditlog.authenticationInfo.principalEmail AS userid, 
        protopayload_auditlog.requestMetadata.callerSuppliedUserAgent as Useragent,
        protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes/(1024*1024*1024) AS totalBilledGB, 
    FROM `bigquery_use_log.cloudaudit_googleapis_com_data_access`
        , UNNEST(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.referencedTables) as table_info
)AS T
WHERE 1=1
        AND $__timeFilter(startTime)
        AND datasetId != 'bigquery_use_log'
        AND tableId != 'INFORMATION_SCHEMA.TABLE_STORAGE'
GROUP BY 
        projectId,
        datasetId,
        tableId
ORDER BY table_read_cnt DESC

많이 쿼리된 테이블

SELECT 
        datasetId,
        tableId,
        sum(totalBilledGB)as totalBilledGB
FROM (     
    SELECT 
        protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.startTime,
        table_info.projectId AS projectId,
        table_info.datasetId AS datasetId,
        table_info.tableId AS tableId,
        protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes/(1024*1024*1024) AS totalBilledGB, 
    FROM `bigquery_use_log.cloudaudit_googleapis_com_data_access`
        , UNNEST(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.referencedTables) as table_info
)AS T
WHERE 1=1
        AND $__timeFilter(startTime)
GROUP BY 
        projectId,
        datasetId,
        tableId
ORDER BY totalBilledGB DESC

유저별 쿼리 사용량

SELECT
  protopayload_auditlog.authenticationInfo.principalEmail AS email,
  SUM(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes) / (1024 * 1024 * 1024) AS totalBilledGB
FROM
  `bigquery_use_log.cloudaudit_googleapis_com_data_access`
where $__timeFilter(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.startTime)
GROUP BY
  protopayload_auditlog.authenticationInfo.principalEmail

BigQuery 쿼리 내역

SELECT 
  resource.labels.project_id, 
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.startTime, 
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.endTime, 
  protopayload_auditlog.authenticationInfo.principalEmail, 
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes/(1024*1024*1024) as totalBilledGB, 
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query, 
  protopayload_auditlog.requestMetadata.callerSuppliedUserAgent,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.error.message error_message
FROM 
  `bigquery_use_log.cloudaudit_googleapis_com_data_access`
WHERE protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query IS NOT NULL
  AND $__timeFilter(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.startTime)
  AND protopayload_auditlog.authenticationInfo.principalEmail != '<Service-Account>'
ORDER BY startTime DESC

기대 효과

쿼리 사용량 모니터링을 통해 우리가 얼마나 쿼리를 사용했고 또 누가 많은 쿼리를 사용하고 있는지 확인할 수 있다. 이를 통해 비용이 어디서 많이 사용되고 있고 뭐가 비효율적인 쿼리인지를 한 눈에 파악할 수 있다. 실제로 모니터링을 구축하고 바로 어떤 쿼리가 사용량이 많은 지 바로 파악하여 해당 쿼리에 대해 튜닝을 거쳐서 10GB를 사용하고 있던 쿼리가 70MB 정도로 줄이는 효과를 봤다.

 

이처럼 운영을 위해서 리소스 모니터링도 중요하지만 엔지니어의 입장에서 효율적으로 비용을 줄이는 부분도 중요하다고 생각한다. BigQuery를 사용하는 사용자들에게 비용 모니터링에 도움이 되었으면 한다.