GCP/BigQuery

[BigQuery] 운영 2탄 / Query 사용량 관리하기

안녕하세요. 지난번의 BigQuery 운영 1탄 편이었던 불필요한 Dataset 삭제 이후에 2탄 BigQuery 사용량 관리에 대해서 글을 작성하였습니다. 어찌 보면 이 글이 1탄보다 훨씬 더 유용할 것으로 보입니다. Query 사용량의 경우 비용과 직결되는 부분이며, 데이터셋의 저장비용 보다 훨씬 많은 비용이 발생합니다. ( BigQuery 가격 )

Query를 어떻게 실행 하느냐에 따라서 엄청난 비용을 절약할 수 있고 이러한 모니터링을 만들어두면 여러모로 도움이 많이 됩니다. 사용자들에게 무작정 데이터를 많이 쓰지 못하게 하는 것보다는 효율적으로 만드는 방법을 알려줌으로써 모두가 데이터에 손쉽게 접근하고 손쉽게 사용하게 하는 것을 목적으로 하였기에 누구나 접근이 가능한 구조이므로, 데이터를 잘 모르고 쓰실 경우 비용 폭탄을 맞기 쉽습니다.

일반적으로 데이터를 접근하는 사람은 데이터 관련 종사자만 생각 할 수 있지만 제가 현재 재직 중인 회사의 경우 모든 사용자 (사업팀, 기획팀, 재무팀 등등)가 데이터에 접근 가능합니다. 이렇게 함으로써 단점이 물론 존재 하지만 장점도 많습니다. 여러 사용자가 각기 다른 분야의 시각으로 데이터를 보기 때문에 새로운 인사이트를 찾기 쉽고 데이터 부서에 불필요한 ad-hoc을 덜어줌으로써 서로가 좋은 효과를 불러오고 있습니다. 하지만 데이터 관련 종사가 아닐 경우 효율적으로 데이터를 쓰기 어려우므로 이를 모니터링하여, 비용이 많이 나오고 성능이 떨어질 경우 이를 미리 파악하여 먼저 튜닝을 해줌으로써 비용도 줄이고, 신뢰도 쌓을 수 있습니다.

1. 수집

일단 어떠한 분석 및 모니터링이든 데이터를 수집 해야 합니다. GCP에서는 Query를 실행 한 로그에 대해서 로그를 수집하여 BigQuery 자체에 저장하는 기능을 공식적으로 제공하고 있습니다. 따로 스스로 만들 필요가 없습니다.

데이터를 수집하는 방법은 그렇게 어렵지 않으므로, 로그를 수집하는 방법에 대해서는 따로 정리하지 않았습니다. 다만 처음이신 분들은 전혀 무슨 내용인지 알 수 없기 때문에 해당 부분에 대해서 설명이 있는 링크를 걸어서 이를 대처하였습니다.

공식 블로그 : 링크

참고 블로그 : 링크

위의 공식 블로그에서 나오는 내용을 보면 GCP의 기능 중 하나인 Stackdriver를 통해서 데이터를 수집하고 이를 BigQuery에 Export 하는 방법으로 BigQuery에 Query 로그를 저장한다고 합니다. 이렇게 저장된 로그는 다음과 같이 샤딩 테이블로 만들어져서 일자별로 데이터를 쌓게 됩니다.

데이터를 이제 수집하였으니 가공을 통해서 어떠한 사용자가 많이 사용했는지 보도록 하겠습니다.

2. Query를 이용한 로그 활용

이 단락에서는 여러 가지 상황별로 어떻게 이 로그를 활용 할 수 있는 몇 가지 예시를 통해서 보고자 합니다. 아마도 여러가지 상황이 각 회사마다 다를텐데, 제가 현재의 회사에 적용한 몇가지 사례를 예시로 들면서 하나씩 확인하고자 합니다. 현재 2년 넘게 이 로그를 활용해서 비용을 절약하고 있습니다.

추가적으로 활용을 하기에 앞서 Query 사용 비용의 경우 집계를 할 경우 비용은 누적입니다. 그러므로 BI 지표를 만들 때 누적으로 해야 합니다. 이 부분은 뒷부분에 그래프의 형태를 보면 이해가 빠를 것으로 보입니다.

또한 저 같은 경우 이러한 로그를 가공하는 Query를 이용하여 결괏값을 RDB (GCP의 Cloud SQL 사용)에 내용을 저장하여 해당 내용을 Datastudio를 통해서지표 화하여 보여주었습니다.

2.1. Query 비용을 너무 많이 사용한 사용자 찾기

이 부분이 가장 처음에 만들었던 부분입니다. Query을 특정 사용자가 엄청나게 많이 사용한다면 이는 효율적인 Query를 실행하고 있다고 보기 어렵습니다. 그래서 이 부분을 가장 먼저 도입하였습니다.

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.code error_code,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.error.message error_message
FROM 
  `bigquery_logs.cloudaudit_googleapis_com_data_access_*`
where _table_suffix between format_date('%Y%m%d', date_sub(current_date(), interval 1 day)) and format_date('%Y%m%d', current_date())
    and   protopayload_auditlog.authenticationInfo.principalEmail = 'gcp account email''
Colored by Color Scripter

cs

여기서 _table_suffix 구문은 필수입니다. 이 구문을 사용하지 않을 경우 자칫 모든 테이블이 조회되어 내가 가장 비용을 많이 사용한 사용자가 될 수 있음을 꼭 유의하시기 바랍니다. 해당 WHERE 조건의 경우는 지금 현재 시간으로부터 24시간 이내의 누적된 사용자의 Query량입니다.

위와 같이 이 경우 Query를 계속해서 날려서 사용자를 조회해야 하므로 이 Query만으로는 여러 사용자를 보면서 운영을 하기 어렵습니다. 그래서 위의 구성도와 같이 구성하여 RDB에 데이터를 저장하고 이를 Datastudio에서 불러오도록 통일해놨습니다. 데이터를 주기적으로 스냅숏을 찍어서 집계하여 보여준다면, 저렴한 비용으로 여러 가지 형태로 데이터를 볼 수 있습니다.

해당 지표의 경우 위에서 언급한 RDB에서 데이터를 가공하여 1시간에 한 번씩 스냅숏을 찍어서 저장 한 뒤에서 해당 데이터를 Datastudio를 통해서 지표로 보여주는 방식입니다. 위의 내용에서 전부 빈칸으로 보여주는 이유는 실제 사용하고 있는 대시보드 화면이므로, 많은 부분을 빈칸처럼 보이도록 처리하였습니다. 이렇게 지표를 통해서 한 번에 어떠한 사용자가 비용을 많이 사용했는지 보여줄 수 있고 추가적으로 Slack을 통해서 특정 Query 비용을 초과한 사용자의 경우 알람을 받도록 하고 있습니다.

Slack을 통해서 팀 내부적으로 정한 상한선을 넘을 경우 알람을 받아서 해당 사용자는 긴급하게 튜닝을 해줄 수 있습니다. 아무래도 지표를 매일 지켜보고 있기는 어렵기 때문에 너무 많은 비용을 사용할 경우 찾아가서(?) 튜닝을 해줄 수 있습니다.

2.2. 테이블의 조회량, 횟수 등 수집

테이블을 얼마나 사용하는지 또한 얼마나 많이 조회하는지 파악하여, 해당 테이블이 얼마나 중요한지 파악할 수 있고 해당 테이블의 파티션이나 구조등을 변경하여 더욱 효율적으로 만들 수도 있습니다. 그래서 사용자가 단순히 Query를 많이 실행하고 용량이 큰 테이블을 조회하여 발생한다고 판단할 수 있지만, 해당 테이블 자체가 비효율적으로 만들어졌을 수 있기 때문에 이를 파악하기 위해서 테이블과 관련된 항목도 만들 수 있습니다.

SELECT TDDate,projectId,datasetId,tableId,userid,Useragent
       ,count(*)as table_read_cnt,sum(totalBilledGB)as totalBilledGB
FROM (     
    SELECT SUBSTRING(CAST(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.startTime AS String),0,10)AS TDDate,
            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_logs.cloudaudit_googleapis_com_data_access_*`
        , UNNEST(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.referencedTables) as table_info
    WHERE _table_suffix = '20220114'
)AS T
GROUP BY TDDate,projectId,datasetId,tableId,userid,Useragent
Colored by Color Scripter

cs

위의 Query를 통해서 누가 어떤 프로젝트에서 어떤 데이터셋의 테이블을 조회하였는지 알 수 있습니다. 위의 Query를 통해서 이 역시 RDB에 스냅숏 형태로 저장해 두고 해당 데이터를 Datastudio에 지표 화하여 사용하고 있습니다. 여기서도 마찬가지로 _table_suffix가 있습니다. 이 부분을 빼먹으면 모든 데이터를 조회해서 Query 비용을 많이 사용한 사용자로 즉시 알람이 올 수 있으니 꼭 확인하길 바랍니다.

이 부분에서는 딱히 그래프를 만들진 않았고, 조회 용도로 쓰는 것이 보통이라서 표 형태를 제공하는 것에 초점을 두었습니다. 앞의 지표에 비하면 정말... 디자인이 많이... 네... 알고 있습니다. 이 부분은 솔직히 팀 내부적으로 보는 지표라서 정말 성의 없이 만들었습니다.

2.3. 종합 지표

해당 로그만 이용해서 만든 것은 아니지만 다음과 같은 종합 지표를 만들어서 제공할 수 있습니다. 아마도 위의 로그만 이용해도 아래의 해당하는 지표를 80% 이상은 구현할 수 있다고 봅니다. BigQuery에서 사용되는 대부분의 로그를 수집하기 때문에 여러 가지 형태의 지표를 만들고 이를 구현 가능합니다.

종합지표를 통해서 BigQuery 운영을 조금 더 손쉽게 할 수 있을 듯합니다. 세부적으로 더욱 필요한 지표는 각자 회사의 상황에 맞게 팀 상황에 맞게 선택하여 만들 수 있을 것 같습니다. 위의 로그의 경우 정말 많은 정보를 수집하기 때문에 활용도가 정말 많습니다. 저는 비록 이 정도밖에 만들지 못하였지만 아마도 다른 분들은 더욱 좋은 아이디어가 있을 것 같습니다.

3. 결론

BigQuery 운영을 하면서 가장 처음 만들었던 부분이 Query의 사용량을 수집하는 것이었습니다. 처음에 Query를 얼마나 누가 사용하는지 몰랐을 때는 그냥 많이 나오면 많이 나오니 줄여야 한다. 조금 써야 한다. 이렇게만 이야기할 수 있었지만 이제는 누가 얼마나 어떻게 쓰는지 알 수 있기 때문에 근거를 통해서 줄이 수 있는 건 줄이고 효율적으로 만들 수 있는 부분은 효율적으로 사용하여 비용을 관리할 수 있게 되었습니다.

처음에는 비용적인 측면에서 매우 불안정하고 누수가 어디에서 발생하는지 몰랐다면, 이제는 즉시즉시 어디서 누수가 발생하는지 알 수 있고 이를 즉각적으로 튜닝하여 모두에게 만족스러운 결과를 낼 수 있습니다. 그렇기 때문에 항상 이야기 하지만 운영은 선택이 아닌 필수입니다. 누군가는 개발을 한다면 누군가는 그것을 잘 운영할 수 있도록 해야 합니다.

아직 운영에 관련된 이야기가 많이 남아 있는데, 더욱 빠르게 정리하여 글을 더 많이 쓰도록 하겠습니다. 끝까지 부족한 글 읽어 주셔서 감사합니다. 다음에 더욱 좋은 글로 찾아뵙겠습니다.

저작자표시 비영리 변경금지

'GCP > BigQuery' 카테고리의 다른 글

[BigQuery] Json 파일 Import 오류 (0)	2022.07.12
Unable to proceed: Could not connect with provided parameters: No suitable driver found for "jdbc:redshift" (0)	2022.04.28
BigQuery - clustering 이야기 (7)	2022.01.04
BigQuery - 운영 1탄 / 불필요한 dataset 삭제 (0)	2021.11.10
BigQuery - 테이블에서 스키마 추출 (4)	2021.09.28

Contents

새소식

인기 검색어