구글 빅쿼리 성능 및 비용 최적화

구글 빅쿼리란?

 많은 회사에서 클라우드 기반의 데이터 웨어하우스, 마트를 구축하고 있다. 그중에서 가장 대표적인 구글 빅쿼리를 소개하고자 한다. 빅쿼리를 어떻게 사용하는지는 BigQuery 문서  |  Google Cloud 에서 자세하게 설명되어 있다. 체험버전으로 데이터를 연결해서 오픈 데이터셋을 이용해 쿼리를 날려볼 수도 있고, BQML이라는 빅쿼리 내 분석툴도 사용할 수 있다.

 

 제목에서도 나와 있듯이 내가 오늘 다뤄볼 이야기는 먹고사는 문제, 비용이다. 빅쿼리에서 쿼리를 날리면 데이터를 읽는데 비용을 지불해야하고, 구독서비스처럼 슬롯을 미리 할당받는데 비용이 들어간다. 그리고 할당받은 슬롯에서 비용을 절감하려면 데이터를 더 적게 읽어야 한다. 데이터를 더 적게 읽으면 쿼리를 읽는 시간도 단축되기 때문에 결국 빅쿼리에서 비용효율적인 쿼리는 '빨리 돈다'. 만약 your_table이 하루 1TB 정도가 쌓이는 테이블이라면, 아래 쿼리는 구글이 가장 좋아하는 쿼리일 것이다. (물론 파티셔닝 된 컬럼이 존재한다면 애초에 아래 쿼리는 조회 전에 파티셔닝 된 컬럼을 where 조건에 넣어야 한다고 오류가 난다!)

select *
from `your_table`

우리는 비용을 어떻게하면 아낄 것인가, 과연 성능을 최적화한다고 해서 비용을 아낄 수는 있을 것인가?

 

“We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%.”
“작은 효과를 위해 할당했던 노력의 약 97%는 잊힌다 : 섣부른 최적화는 모든 악의 근원이다. 하지만 우리는 중요한 3%의 기회를 놓쳐서는 안 된다.”

— Donald Knuth

 보통 '섣부른 최적화는 악의 근원이다'로 잘 알려진 도날드 커누스의 명언의 전체 문장이다. 그만큼 최적화는 꼭 필요하지만 너무 매몰되지 말라는 뜻이다. 우리는 다음 인수자가 읽기 쉽도록 쿼리는 짜는 것이 우선이고, 최적화는 그 뒤에 신경 써야 할 것이다. ('그 뒤'라고 가정하고 최적화에 대한 이야기를 계속하겠다.)

 

  우선 가장 먼저 확인할 수 있는 것은 어떤 테이블이 가장 비싸게 돌아가는 가다. 아래 쿼리를 통해서 확인할 수 있다.

select job_id                       -- 쿼리 실행 JOB ID
    , query                         -- 실행 쿼리
    , user_email                    -- 쿼리 실행 유저ID
    , total_bytes_processed         -- 쿼리 실행 용량
    , total_slot_ms                 -- 쿼리 실행 슬롯 할당
from `my-project.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
where 1=1
and date(creation_time) between '2019-01-01' and '2019-12-31'
qualify rank() over(order by total_processed desc) <= 5  -- 쿼리 실행 용량을 상위 5개 뽑아냄

 위 쿼리를 통해서 실행 용량이 가장 높은 상위 5개 JOB을 뽑아낸다. 이제 개인의 JOB 쿼리를 살펴보면서 어떤 식으로 줄이면 좋을지 몇 가지 방법을 공유하고자 한다.

 

1. 앞서 언급했던 select *  보다는 더 구체적으로 조회하고자 하는 컬럼을 명시해라.

 굉장히 간단한 이야기이기 때문에 예시 쿼리는 생략하지만, 생각보다 꽤 중요한 이야기이다. 명심하자. (참고로 LIMIT는 쿼리를 읽고 나서 상위행만 뽑는 행위이기 때문에 쿼리비용을 줄이는데 도움이 되지 않는다.)

 

2. 마찬가지로 Where 절에도 꼭 필요한 컬럼으로 조회하도록 하자.

아래 두 가지 쿼리 예시를 비교해 보자.

  SELECT min(start_station_name) as start_station_name -- 조회하는 컬럼은 start_station_name
  , max(end_station_name) as end_station_name          -- 조회하는 컬럼은 end_station_name
  , approx_quantiles(tripduration, 10)[offset(5)] as typical_tripduration
  , count(tripduration) as trip_cnt
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  where 1=1
  and start_station_id != end_station_id               -- where 조건은 start_station_id, end_station_id
  group by start_station_id, end_station_id

-- start_station_name, end_station_name 두 가지 컬럼만 기준으로 집계
SELECT start_station_name
  , end_station_name
  , approx_quantiles(tripduration, 10)[offset(5)] as typical_tripduration
  , count(tripduration) as trip_cnt
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
where 1=1
and start_station_name != end_station_name
group by start_station_name, end_station_name

 

 위 예시를 통해서 조건 컬럼의 차이로 성능의 40%가 증가했다는 것을 알 수 있다!

 

3. 미리 집계하는 materialized view 나 table 생성을 이용해 보자.

create or replace materialized view mv as -- 구체환된 뷰 생성
--create or replace table tb as             -- 테이블 생성
with a as (
SELECT start_station_name
  , end_station_name
  , tripduration
  , date(starttime) as start_date
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
where 1=1
and start_station_name != end_station_name
group by start_station_name, end_station_name
)

SELECT start_station_name
  , end_station_name
  , start_date
  , approx_quantiles(tripduration, 10)[offset(5)] as typical_tripduration
  , count(tripduration) as trip_cnt
FROM a
where 1=1
and start_station_name != end_station_name
group by start_date, start_station_name, end_station_name
;
---------------
select start_station_name
  , end_station_name
  , start_date
  , typical_tripduration
  , trip_cnt
from mv
where 1=1
and start_date between '2023-01-01'
;

 materialized view는 기존 view와 차이(링크참조)가 있지만 간단하게 말하자면, materialized view는 미리 집계하고 파티셔닝 컬럼(보통 날짜) 이용해서 조회 용량을 줄이는데 효과적이다. 또한 집계된 table을 미리 생성해 놓는다면 비용을 줄이는데 도움이 될 수 있다.

 

 

 

4. 효율적인 Join을 통해서 비용을 줄여보자.

 join을 실행하기 전에 with 절을 이용하여 미리 집계를 하거나 파티셔닝 된 컬럼을 이용하여 데이터의 수를 줄이는 방법도 굉장히 중요하다. 뿐만 아니라 window 함수를 사용한다면 join을 사용하지 않을 수도 있다는 것도 참고하자.

-- citibike의 출발지와 종착지의 시간차이를 계산하기 위해서는 
-- starttime을 위한 테이블과 stoptime을 위한 테이블 두개를 조인해서 풀어야한다고 생각할 수 있지만,
-- lag함수를 사용하면 조인하지않고 해결할 수 있다.

SELECT bikeid
  , date(starttime) as start_date
  , date(stoptime) as end_date
  , timestamp_diff(
      date(starttime)
    , lag(stoptime) over (partition by bikeid order by starttime)
    , second) as start_stop_term
FROM `bigquery-public-data.new_york_citibike.citibike_trips`

 

이외에도 과도한 row_number(), array_agg() 사용을 줄여야 하는 등의 방법이 있다.

---

 

 

 이번 글에서는 비용을 최적화하는 방법에 대해서 간략히 알아봤다. 평소 업무를 진행하면서 많이 고려할 시간이 없는 부분이라 머릿속에 넣어두려고 방법론적으로 풀어서 글을 써봤다. 사실 너무 기본적인 이야기밖에 없을 수 있지만, 간혹 업무에 치여 정신없이 쿼리를 짜다 보면 기본적인 것을 지키지 못하는 경우가 있다. 그런 상황을 미연에 방지하고자 하는 목적도 있고, 도날드 크누스가 말했던 그 3%는 사실 그리 대단한 것이 아닐 수도 있다는 생각도 들었다.

 

 이후에 글은 빅쿼리의 파티셔닝과 클러스터링의 개념과 사용하는 방법을 적어서 쿼리 작업을 더 입체적으로 파악하고자 한다.

 

이 글은 '구글 빅쿼리 완벽가이드'를 참조하여 작성하였습니다.