Bigquery Audit Log를 구성하고 사용자/시간대 별 비용 확인하기

by

안녕하세요. 오늘은 베스핀글로벌 DevOps실 이주웅님이 작성해 주신 Bigquery Audit Log를 구성하고 사용자/시간대 별 비용 확인하기에 대해 알아보겠습니다. 궁금하신 부분이 있으시면 댓글을 달아주세요 🙂

1. 감사로그 설정 방법

Stackdriver의 로그 기록을 이용하여 Bigquery 감사로그를 BigQuery에 내보낼 수 있습니다.

감사로그는 데이터에 대한 읽기 및 변경과 관련된 DML성 data_access 로그와 Table 및 Dataset 생성과 관련된 DDL성 activity 로그로 Dataset 내 Table로 구분되어 저장됩니다.

[note]
작업 했던 시점차이로 현재는 테이블이 만료되어 삭제될때에 대한 로그가 system_event에 추가로 기록되는 것 같네요.
Bigquery는 Table 및 Partition에 만료기한을 설정하여 데이터에 대한 Retention Policy 관리가 가능합니다. https://cloud.google.com/bigquery/docs/managing-partitioned-tables?hl=ko


StackDriver > 로그기록 > 내보내기 > 상단에 내보내기 만들기

  • 싱크이름 : 싱크 명
  • 싱크서비스 : BigQuery, Colud Storage, Colud pub/Sub 중에 선택가능 
  • 싱크 대상 위치 : BigQuery를 선택했으면 Dataset 이름을 넣어준다.

싱크 생성이 완료되면 BigQuery에 감사로그 테이블을 만들어야 하는데 싱크 서비스 계정이 BigQuery에 테이블 생성 권한이 없어 수동으로 구성이 필요합니다.

작성자 ID를 복사하여 IAM 및 관리자에서 위 계정을 IAM에 BigQuery 쓰기 권한을 줘서 등록 해야합니다.

이제 BiGquery에 DDL, DML 작업이 실행되면 Dataset에 아래 이름으로 테이블이 생성됩니다.

  • cloudaudit_googleapis_com_activity_[date]
  • cloudaudit_googleapis_com_data_access_[date]

2. 시간대/사용자 별 비용 확인하기

A. 시간대 별 비용

#standardSQL
SELECT
  TIMESTAMP_TRUNC(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.endTime, HOUR) as time_window,
  FORMAT(‘%9.2f’,5.0 * (SUM(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes)/POWER(2, 40))) as Estimated_USD_Cost
FROM
  `AuditLogsDataSet.cloudaudit_googleapis_com_data_access_YYYYMMDD`
WHERE
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.eventName = ‘query_job_completed’
GROUP BY time_window
ORDER BY time_window DESC

B. 사용자 별 비용

 
#standardSQL
WITH data as
(
  SELECT
    protopayload_auditlog.authenticationInfo.principalEmail as principalEmail,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent as jobCompletedEvent
  FROM
    `AuditLogsDataSet.cloudaudit_googleapis_com_data_access_YYYYMMDD`
)
SELECT
  principalEmail,
  FORMAT(‘%9.2f’,5.0 * (SUM(jobCompletedEvent.job.jobStatistics.totalBilledBytes)/POWER(2, 40))) as Estimated_USD_Cost
FROM
  data
WHERE
  jobCompletedEvent.eventName = ‘query_job_completed’
GROUP BY principalEmail
ORDER BY Estimated_USD_Cost DESC

3. 주요 컬럼 정보

4. 참고 자료

감사합니다~ 🙂

문의: info@bespinglobal.com | 대표번호: 02-1668-1280

Leave a Comment