구글 빅쿼리(BigQuery) 쿼리 속도 높이기

http://www.yes24.com/Product/Goods/95562895

구글 빅쿼리 완벽 가이드 - YES24

* '구글 빅쿼리 완벽 가이드' 내용 중 일부를 정리한 글입니다.

 

I/O 최소화

간단한 쿼리의 오버헤드 대부분은 연산이 아니라 I/O에 의해서 발생한다.

 

  1. SELECT의 대상을 명확히 하기 ("SELECT *"의 사용을 최대한 피하고, 가능한 최소한의 컬럼만 참조)

  2. 만약 거의 모든 컬럼이 필요하다면, 차라리  "SELECT * EXCEPT" 문법을 통해 불필요한 컬럼을 제거한다.

  3. 비용 높은 연산이 필요할 경우, 해당 연산을 미리 수행하는 테이블을 미리 구성해놓는다.

  4. Materialized View (MV)를 활용한다.

    - MV는 정기적으로 빅쿼리 테이블에 대한 쿼리 결과를 캐시해서 성능과 효율성을 향상시키는 미리 연산된 뷰다.

    - 미리 Join을 해놓거나, 집계 함수를 사용한 요약 정보를 담아놓을 수도 있다. (* 아직 빅쿼리 MV에서는 Join이 불가능)

    - But, MV는 그만큼 유지비용(주기적 refresh를 위한 slot 사용)이 발생하기 때문에, 신중히 사용해야 한다.

    - https://cloud.google.com/bigquery/docs/materialized-views-intro

구체화된 뷰 소개  |  BigQuery  |  Google Cloud

    - https://cloud.google.com/bigquery/docs/materialized-views-faq

구체화된 뷰 FAQ  |  BigQuery  |  Google Cloud

 

이전 쿼리 결과 캐싱

빅쿼리는 자체적으로 쿼리 결과를 Temp Table에 캐싱하여, 약 24시간 내에 같은 쿼리를 제출하면, 연산 없이 해당 Temp Table에서 결과를 제공한다(매우 빠르고 비용이 발생하지 않음). But, 쿼리 캐싱은 정확한 문자열 비교를 기반으로 하기에, 공백 하나만 추가해도 캐시를 활용하지 않는다(+ 비결정적 작동, 테이블 변경, 버퍼 행 부족, DML 포함 등도 마찬가지).

 

중간 결과를 캐싱하기 위하여 Temp Table을 직접 생성할 수 있다.

***빅쿼리의 WITH절은 결과를 캐싱시키지도, Temp Table을 생성하지도 않는다. 서브 쿼리의 가독성 향상을 위해서만 쓰인다.

CREATE OR REPLACE TABLE xxx AS
SELECT
  xxx
FROM
 xxx

그러나 Temp Table은 MV와 다르게 자동으로 갱신되지 않으며, 간단한 Sub Query라면, 경우에 따라서 더 느려질 수도 있다. 따라서 신중히 사용해야 한다.

  * 만약, 메인 쿼리에 필터링 제약이 걸려 있다면, 빅쿼리 옵티마이저가 자체적으로 연산할 행의 개수를 줄일 수 있다. 그러나, 미리 Temp Table을 구성한 후, 메인 쿼리를 한 번만 수행할 경우, 이미 모든 행에 대한 연산이 수행되었기에, 전체 성능은 더 낮아질 수 있다.

 

BI 엔진을 활용하면 쿼리를 가속화시킬 수 있다.

BI 엔진들은 보통 자체적으로 관련 데이터를 메모리에 자동으로 저장하며, 대부분 메모리 상에 저장한 데이터로 작업하도록 튜닝된 특수 쿼리 프로세서를 사용한다.

 

효율적으로 조인하기

두 테이블을 조인하려면 데이터 조정이 필요하며, 슬롯 간 통신 대역폭에 의해 부과되는 제한이 따른다. 따라서, 조인을 되도록 피하거나, 조인되는 데이터의 양을 줄여야 한다.

 

비정규화

두 개의 테이블을 조인하지 않고, 미리 테이블을 비정규화시켜 한 테이블에 모든 정보를 담아둔다. (대신 연산 비용은 높아짐)

 

큰 테이블의 셀프 조인 피하기

집계, 윈도우 함수 등의 SQL 기능을 활용하면 셀프조인을 피할 수 있는 경우가 많다.

 

조인할 데이터 줄이기

그룹화를 통해 미리 데이터의 양을 줄이면, 조인의 부담이 줄어든다.

 

워커의 과도한 작업 피하기

정렬과 같은 일부 작업은 단일 워커에서 수행되어야 한다. 그런데 단일 워커에 너무 많은 데이터를 할당하면 워커의 메모리를 초과할 수 있다. 따라서 워커에 과도한 데이터를 몰아주면 안 된다.

 

큰 정렬 제한하기

"ROW_NUMBER() OVER(ORDER BY xxx) AS yyy"를 사용할 때, PARTITION을 활용해 정렬을 2개의 컬럼으로 나누면 단일 워커의 정렬 워크로드를 줄일 수 있다.

 

데이터 왜도(skew)

ARRAY_AGG 함수나 GROUP BY 절에서 사용하는 키 중 특정 키가 다른 키보다 월등히 많이 나오면 워커의 과부하 문제가 발생할 수 있다. 이 경우, 더 세분화된 키(GROUP BY의 컬럼을 추가)를 사용한다.