BigQuery 가격 체계를 알아보자 2편 – 비용 최적화하는 법

들어가며

본 <BigQuery 가격 체계를 알아보자> 시리즈는 제가 BigQuery 가격 체계 공부한 것을 정리하는 목적으로  Cost optimization best practices for BigQuery 포스팅을 번역한 것입니다.

공식 블로그를 바탕으로 정리하였으나, 의역이 많고, 틀린 부분이 있을 수 있으므로 정확한 최신 정보는 공식 문서를 확인 부탁드립니다 🙂

Storage에서 비용 최적화하기

필요한 만큼만 데이터 보관하기

데이터 세트의 기본 테이블 만료 설정하기  

데이터세트를 생성시, 보존 할 필요가없는 임시 스테이징 데이터를 가진 데이터 세트는 기본 테이블 만료를 설정합니다.  

콘솔

만료 시간은 콘솔에서는 1일 단위, bq 명령어로는 1초, API로는 밀리초 단위로 설정할 수 있습니다.

참고 : 기본 테이블 만료 시간 업데이트 

bq 

예를 들어, 데이터세트에서 앞으로 생성될 테이블의 기본 수명을 초 단위로 설정하는 방법은 다음과 같습니다.

Cloud Shell을 연 후, 아래와 같이 입력하세요.

bq update --default_table_expiration={초단위 시간} {프로젝트ID}:{데이터세트 이름}

<예시>

참고 : bq command-line tool reference | BigQuery 

테이블 별 테이블 만료 설정하기

데이터세트가 아니라 테이블 단위로 테이블 만료를 설정할 수 있습니다.

쿼리 편집기에서 아래 SQL문을 사용하세요.

ALTER TABLE {데이터세트 이름}.{테이블 이름}
SET OPTIONS(
    expiration_timestamp=TIMESTAMP "2021-06-08 00:00:00 UTC"
    );

<예시>

데이터를 편집하는 방법에 주의하기

테이블 또는 파티션을 90일 동안 편집하지 않으면 테이블에 저장된 데이터의 가격이 자동으로 약 50% 하락합니다. 

테이블 또는 파티션을 Long-term storage으로 저장한다고 해서 성능, 내구성, 가용성이 저하되지 않으므로 장기간 편집하지 않는 데이터는 Long-term storage으로 저장하시는 것을 고려해보세요.

Long-term storage을 최대한 활용하려면, 모든 데이터 편집 작업을 조심하세요.

데이터를 편집하면 데이터가 Active storage으로 돌아가고 90일 타이머가 재설정됩니다.

다음 작업과 같이 Long-term storage의 테이블을 수정하면 Active storage 요금으로 돌아가며, GCP의 타이머가 다시 0일부터 90일까지 세기 시작합니다. 

• 테이블에 데이터 로드
• 테이블에 데이터 복사
• 테이블에 쿼리 결과 쓰기
• 데이터 조작 언어(DML) 사용
• 데이터 정의 언어(DDL) 사용
• 테이블에 데이터 스트리밍

새 데이터 일괄 처리를 기존 테이블이 아니라 새 테이블에서 진행하는 방법도 고려할 수 있습니다.  

외부 데이터 소스 사용하기

아래 외부 데이터 소스에서는 BigQuery에 데이터를 로드하지 않고도 직접 데이터를 쿼리할 수 있습니다.

외부 데이터 소스 종류

• Bigtable
• Cloud Storage
• Google 드라이브
• Cloud SQL

외부 데이터 소스 사용 사례

• 외부 데이터 소스에서 데이터를 쿼리하고 정리된 결과를 BigQuery 스토리지에 작성해 단 한 번의 전달로 데이터를 BigQuery에 로드하고 정리합니다.
• 외부 데이터 소스이기 때문에, 자주 변경되는 소량의 데이터를 매번 다시 빅쿼리에 로드할 필요가 없습니다.
  • 참고: 소량으로 간주되는 데이터 양은 스토리지 백엔드 유형에 따라 다릅니다. 일반적으로 스토리지 백엔드에서 약 10초 내에 데이터를 읽을 수 있다면 소량으로 간주할 수 있습니다.

외부 데이터 쿼리 방법

• External table
  • 표준 BigQuery 테이블처럼 작동하는 테이블. 
  • 테이블 스키마를 포함한 메타 데이터는 BigQuery 저장소에 저장되지만, 데이터 자체는 외부 소스에 있음
  • 사용 가능한 외부 데이터 소스 : Cloud Bigtqble, Cloud Storage, 구글 드라이브

• Federated query
  • Federated query는 EXTERNAL_QUERY함수를 사용하여 외부 데이터베이스로 쿼리를 보내고 임시 테이블로 결과를 가져오는 방식입니다.
  • 쿼리 결과는 BigQuery의 표준 SQL 데이터 유형으로 변환됩니다.
  • Federated query는 BigQuery Connection API를 사용하여 외부 데이터베이스와 연결합니다.
  • 사용 가능한 외부 데이터 소스 : Cloud SQL 

참고 문서

• BigQuery | External Data Source Limits 
• Introduction to external data sources | BigQuery 
• BigQuery External Table 
• Cloud SQL federated queries 

Streaming insert가 꼭 필요한지 검토하기

BigQuery에는 두가지 데이터 수집 모드가 있습니다.

• Batch load(일괄 로드) : 단일 일괄 작업으로 소스 데이터를 하나 이상의 BigQuery 테이블에 로드
• Streeaming : 한 번에 한 레코드씩 또는 소규모 batch로 데이터를 스트리밍

스트리밍은 비용이 서울 리전 기준으로  200MB 당 $0.012 의 금액이 발생합니다.

반면, 일괄 로드는 (일반적으로 공유 슬롯 풀을 사용할 경우)무료입니다.

BigQuery에 데이터를 즉시 로드해야 하는 경우, BigQuery에 데이터 로드 후 실시간으로 데이터를 사용해야 하는 경우가 아니라면 스트리밍 보다는 일괄 로드가 현명한 비용 절감 방법입니다.

7일 내의 스냅샷은 쿼리로 가져오기

BigQuery는 7일 내 테이블 변경 내역을 유지하므로 데이터의 특정 시점 스냅샷을 쿼리할 수 ​​있습니다. 즉, 복구 백업 에서 복원하지 않고도 데이터를 되돌릴 수 있습니다. 테이블이 삭제되면 이틀 후에 기록이 플러시됩니다.

1시간 전의 테이블 스냅샷을 얻는 방법은 다음과 같습니다.

상댓값으로 구하는 법

#standardSQL
SELECT *
FROM DATASET.TABLE
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

절댓값으로 구하는 법

#standardSQL
SELECT *
FROM DATASET.TABLE
  FOR SYSTEM_TIME AS OF '2021-08-01 10:00:00-07:00';

참고 링크

• Migrating to standard SQL
• Query syntax in Standard SQL

Query processing에서 비용 최적화하기

‘SELECT * ’ 지양하기

BigQuery는 데이터를 열 데이터 구조로 저장합니다.

동시에, 이는 ‘SELECT *’가 데이터를 쿼리하는 가장 비용이 많이 드는 방법임을 의미합니다. 

미리보기 탭 사용하기

‘SELECT * ’ 을 사용하는 이유가 단순히 데이터 테이블을 확인하는 목적이라면 미리보기 탭을 사용합니다.

미리보기 기능을 사용하면 할당량에 영향을 주지 않으면서 데이터를 무료로 볼 수 있습니다.

필요한 열만 쿼리하기

BigQuery가 데이터를 열 데이터 구조로 저장하기 때문에, 쿼리문을 어떻게 쓰냐에 따라 처리되는 데이터 양이 달라집니다.

전체 데이터를 ‘SELECT *’를 사용해서 쿼리할 때

SELECT * FROM [데이터세트 이름].[테이블 이름];

<예시>

전체 3개의 열을 쿼리하므로 246B가 처리되었습니다.

필요한 열 1개만 쿼리할 때

SELECT [쿼리할 열 이름] FROM [데이터세트 이름].[테이블 이름];

<예시>

1개의 열만 쿼리하므로 100B가 처리되었습니다.

특정 열을 제외하는 ‘EXCEPT’를 사용할 때

SELECT * EXCEPT([제외할 열 이름]) FROM [데이터세트 이름].[테이블 이름];

<예시>

2개의 열만 쿼리하므로 166B가 처리되었습니다.

반환할 행의 개수를 제한하는 ‘LIMIT’를 사용할 때

SELECT * FROM [데이터세트 이름].[테이블 이름] LIMIT [반환할 행의 개수];

<예시>

2행밖에 없지만, 3개의 열을 쿼리하므로 ‘SELECT *’를 한 것과 동일하게 246B가 처리되었습니다.

쿼리되는 데이터 양을 제한하기

쿼리되는 데이터 양을 제한하기

BigQuery 프로젝트와 사용자의 수가 많은 경우에는 커스텀 할당량을 요청하여 하루에 처리되는 쿼리 데이터 양의 한도를 지정하여 비용을 관리할 수 있습니다. 

https://cloud.google.com/bigquery/docs/custom-quotas

청구 가능한 바이트 수를 제한하기

청구 가능한 최대 바이트 설정을 사용하여 쿼리에 대해 청구되는 바이트 수를 제한할 수 있습니다. 청구 가능한 최대 바이트를 설정하면 쿼리가 실행되기 전에 쿼리가 읽는 바이트 수가 추정됩니다. 추정 바이트 수가 한도를 초과하면 비용이 발생하지 않고 쿼리는 실패합니다.

캐싱을 전략적으로 사용합니다.

캐싱은 실제로 쿼리 성능을 향상시킬 수 있으며, 캐시된 테이블에서 검색된 결과에 대해 비용이 청구되지 않습니다. 

캐시 설정은 기본값으로 활성화되어 있습니다. 쿼리 편집기에서 [더보기] > [쿼리 설정]을 클릭하면 오른쪽에서 열리는 패널에서 캐시 환경설정 > [캐시 처리된 결과 사용]에 체크가 되어있는 것을 확인할 수 있습니다.

다음 예외 경우는 쿼리 결과가 캐싱 처리되지 않습니다.

• 대상 테이블이 작업 구성(job configuration), 콘솔, bq 명령줄 도구 또는 API에 지정된 경우
• 이전에 결과가 캐시 처리된 후, 참조된 테이블 또는 논리 view가 둘 중 하나라도 변경된 경우
• 새 행이 도착하지 않았더라도, 쿼리에서 참조하는 테이블 중 하나에서 스트리밍 삽입을 수신한 경우
• 쿼리 실행 시기에 따라 다른 값을 반환하는 함수를 사용하는 경우
  • 예: CURRENT_TIMESTAMP, NOW(), SESSION_USER()
• 와일드 카드를 사용해 여러 테이블을 쿼리하는 경우
  • 예: FROM `bigquery-public-data.noaa_gsod.gsod*`
• 캐시된 결과가 만료된 경우
  • 참고 : 일반적인 캐시 수명은 24시간입니다.
• 쿼리가 Cloud Storage 이외의 외부 데이터 소스를 대상으로 실행되는 경우 
  • 참고 : ‘캐시 처리된 결과’는 Cloud Storage의 BigQuery 표준 SQL 쿼리를 지원합니다

파티셔닝하기

테이블을 파티셔닝하면 쿼리 처리 비용을 줄이고 성능을 향상시키는 데 도움이 될 수 있습니다. 예를 들면 ingestion time, date, 또는 timestamp 열을 기반으로 테이블을 분할할 수 있습니다. 

파티셔닝 이점1 : 사용한 파티션에 대해서만 비용을 지불합니다.

지난 12개월 동안의 데이터가 포함된 판매 테이블을 date별로 파티셔닝한다고 가정해 보겠습니다. 그러면 각 날짜에 대한 데이터가 포함된 더 작은 파티션들이 365개 생성됩니다. 그럼 8월의 판매 데이터를 분석하기 위해 쿼리할 때 전체 테이블이 아니라 8월에 해당하는 31개 파티션에서 처리된 데이터에 대해서만 비용을 지불합니다.

파티셔닝 이점2 : 사용되지 않은 파티션 스토리지 비용을 절약합니다.

또다른 이점은 각 파티션이 장기 저장을 위해 별도로 고려될 수 있다는 것입니다.

위의 예시에서, 판매 데이터는 종종 지난 몇 개월 동안 로드 및 수정됩니다. 따라서 지난 90일 동안 수정되지 않은 모든 파티션(예: 1월)은 이미 일부 스토리지 비용을 절약하고 있습니다. 파티션된 테이블 쿼리의 이점을 실제로 얻으려면 파티션 열을 사용하여 테이블을 필터링해야 합니다.

팁: 파티션 테이블을 생성하거나 업데이트하는 동안 “Require partition filter(파티션 필터 필요)”를 활성화하면 사용자가 파티션 열을 지정하는 WHERE 절을 포함하도록 강제할 수 있습니다. WHERE절을 포함하지 않으면 쿼리에서 오류가 발생합니다.

참고 : Qwiklabs : BigQuery에서 날짜별로 파티션된 테이블 생성하기 

클러스터링을 사용하기

파티션을 나눈 후, 최대 4개의 열에 대한 콘텐츠를 기반으로 데이터를 구성하는 테이블을 클러스터링할 수 있습니다. BigQuery는 지정된 열의 순서에 따라 데이터를 정렬하고 블록으로 구성합니다. 이러한 열을 사용하여 쿼리 필터를 사용하면 BigQuery는 블록 정리라고 하는 프로세스를 사용하여 관련 블록만 지능적으로 스캔합니다.

참고 링크

• BigQuery 제품 페이지 
• GCP 문서 > BigQuery 가격 정책 
• GCP 공식 블로그 > BigQuery의 비용 최적화 권장 사항 
• GCP 문서 > BigQuery 비용 관리 
• BigQuery 가격 정책 
• CodeLabs > Partitioning and Clustering in BigQuery 
• 클러스터링된 테이블 소개 | BigQuery 
• GCP 유튜브 > Partitioning and Clustering with BigQuery