[데이터 분석] 빅쿼리(Bigquery)의 쿼리를 최적화 해보자!

현재 회사는 데이터와 관련 클라우드로 GCP를 사용하고 있다. Bigquery는 현 회사에서 처음 접해보았는데 이 전에 썼던 서비스들보다 확실히 더 편하게 쓰고 있다. 빅쿼리의 장점을 그냥 잠깐 설명해 보자면, 1) 루커 스튜디오와 연결되어서 바로 시각화가 가능하다. 빅쿼리에서 추출한 데이터를 가지고 그~대로 Looker Studio로 가지고 가서 시각화를 할 수 있기 때문에 이 추출한 데이터를 다시 다운로드하여서 다른 시각화툴에 insert를 할 필요가 없다. 그렇기 때문에 해당 데이터에 대해 좀 더 읽고 이해하기가 편해진다는 장점이 있다. 2) 잘~ 쓴다면 실시간 분석이 가능하다. 이 부분은 데이터 엔지니어링적인 부분도 필요하겠지만, 실시간 스트리밍을 통해서 사용자의 로그를 볼 수 있는 앞단의 작업이 선행된다면 실시간으로 유저 행동을 트랙킹 할 수 있다는 장점이 있다. 그리고 마지막으로 3) 쉽고, 빠르다. 진짜. 다만, 쿼리를 얼마나 최적화해서 짜냐에 따라. 만약, 쿼리 최적화가 제대로 되어있지 않다면 어마어마한 비용이 나올 수도 있다. 

 

현 회사의 경우에는 모든 구성원들이 데이터에 대해 관심이 많기도 하고, 쉽게 접근할 수 있는 권한도 가지고 있기 때문에 누구나 빅쿼리를 이용하여 데이터 추출이 가능하다. 데이터를 보기 위해서 쿼리를 많이 사용하는 건 좋지만, 회사의 입장에서는 (사측은 아닙니다...) 이 비용을 적게 사용하면서 데이터를 잘 보는 게 가장 좋은 방향이라고 생각할 수 있다. 즉, 빅쿼리를 잘 사용하기 위해서는 '최적화된 쿼리'를 잘 짜야한다. 해당 글에서는 '내'가 빅쿼리를 작성할 때 최적화를 하기 위해서 항상 염두에 두는 부분에 대해서 소개하고자 한다. 

 

1. 칼럼이 3개 이상인데, 내가 필요한 칼럼이 2개라면 최대한 SELECT * FROM A는 피하자. 

- SQL을 처음 배울 때는 주로 SELECT * FROM A에 대해서 먼저 배운다. 하지만 여기서 이야기하는 *는 A에 존재하는 모든 칼럼을 이야기한다. 유저 행동 데이터의 경우에는 하나의 테이블 안에 유저의 아이디, 유저가 행동을 한 시간, 유저가 한 행동 등 적어도 3개 이상의 칼럼이 존재하는 경우가 많다. 그렇기 때문에 행동 데이터를 보고 싶다고 무작정 *를 사용하게 되면 내가 보고 싶은 칼럼 이외에도, 불필요한 칼럼을 다 불러오게 된다. 따라서, 최대한 *의 사용은 피하는 게 좋다. 

 

2. 존재하는 데이터의 기간이 길지만, 내가 원하는 데이터의 기간은 짧을 때는 꼭 날짜의 파티셔닝을 진행하자.

- 서비스가 오래되거나 혹은 데이터를 쌓은 기간이 많은 경우에는 해당 데이터 테이블의 크기가 어마어마할 수 있다. 이때, 한꺼번에 많은 데이터를 끌어온다면 해당 데이터를 불러오는 시간도 오래 걸리면서 굳이 보지 않아도 되는 데이터까지 다 긁어올 수 있다. (박박. 복복) 만약 필요한 기간의 데이터가 따로 있다면 해당 기간만을 기준으로 꼭 파티셔닝을 진행하여 가지고 오는 게 좋다. 만약 긴~ 데이터의 시간이 꼭필요한거라면 미리 연산된 중간테이블을 만들어 둔 후 해당 중간 테이블을 쿼리 하는 게 가장 좋은 방법이다. 

 

3. 만약, 동일한 테이블을 가지고 하나의 쿼리에서 여러번 참조하는 경우, CTE(with 절)을 이용하자. 

- 여러 데이터를 한 쿼리를 통해서 보고싶을 때, 동일한 테이블을 하나의 쿼리 내에 참조하는 경우가 있다. 만약 이 동일 테이블을 한 쿼리 내에서 계속해서 참조하도록 작업을 진행하게 된다면, 쿼리 수행이 될 때마다 해당 테이블 내에서 데이터를 가지고 오도록 구조가 되어있다. 

예시 테이블) 


WITH A as (
  SELECT 
  	DATE_TRUNC(event_timestamp, MONTH) as month, 
    type, 
    media_source, 
    campaign, 
    COUNT(DISTINCT user_id) as cnt_user
  FROM `campaign_table` ## 동일 테이블 복수 참조
  LEFT JOIN b ON b.id = campagin_table.id
), 

B as ( 
  SELECT 
  	DATE_TRUNC(event_timestamp, MONTH) as month, 
    type, 
    media_source, 
    campaign, 
    COUNT(DISTINCT user_id) as cnt_user
  FROM campagin_table
	LEFT JOIN c ON c.id = campagin_table.id
),

...

이처럼 여러 행동을 보거나, 혹은 행동 자체가 여러 개일 때는 이렇게 동일한 테이블을 참조해야 하는 경우도 있는데, 이렇게 동일 구조 테이블을 참조하는 경우에는 한 번만 테이블을 읽어오도록 처음부터 아예 CTE(with 절)로 정의해서 해당 테이블을 미리 만들어 두는 게 좋다. 

예시 테이블) 

WITH campaign_data AS (
	SELECT 
  		event_timestamp, 
    	type, 
    	media_source, 
    	campaign, 
    	user_id
	FROM `campaign_data`
), ## 반복 사용 테이블 미리 CTE

SELECT 
	event_timestamp, 
    type, 
    media_source, 
    campaign, 
    COUNT(user_id) AS cnt_user
FROM campaign_data
LEFT JOIN A ON a.id = campaign_data.user_id
LEFT JOIN B ON b.id = campaign_data.user_id
...

이런 식으로 미리 CTE로 만들어 놓은 후, 계산된 결과를 가지고 오게 된다면 동일한 테이블을 두 번 스캔하지 않아도 되기 때문에 비용적인 측면에서 좀 더 효율적일 수 있다. 

 

4. Join을 조심하자. 

- 하나의 테이블 내에 내가 보고싶어하는 모든 데이터가 존재한다는 건 정말 어려운 일이다. 아주 정확하게! 내가 원하는 모습처럼 중간 테이블이 존재하지 않는 이상 빅쿼리를 사용하게 될 때, (혹은 그냥 데이터를 추출할 때라도) Join을 사용하는 건 너무나도 당연한 일이다. 그리고 이 join을 할 때, 또 한 번의 쿼리 최적화가 필요하다. Join은 말 그대로 특정 키를 기준으로 A 테이블과 B 테이블이 함께 하도록 하는 쿼리인데, 만약 A 테이블 내에서 필요한 데이터가 작고, B 테이블은 그 A 테이블의 결과를 따를 때, A 테이블의 크기를 최대한 줄인 후, (즉 필요한 데이터만을 미리 불러온 후) B 테이블을 Join 시키는 게 가장 좋은 방법이다. 

예시 쿼리)
WITH
  comments AS (
  SELECT
    user_id,
    COUNT(text) AS comments_count
  FROM
    comments
  WHERE user_id IS NOT NULL
	AND event_date BETWEEN '2023-01-01' AND '2023-02-01' 
    AND created_at IS NOT NULL 
  ) #조건이 많고 조인이 되는 테이블을 미리 정리.
  
  
SELECT
  user_id,
  reputation,
  comments_count
FROM comments
JOIN users ON users.user_id = comment.user_id

또한, Left join을 이용할 때는 가장 많은 데이터, 즉 가장 많은 row 수를 가진 데이터 테이블을 먼저 선행되도록 한 후, 그 후에 적은 양의 데이터를 가진 테이블을 Join 해 준다면 좀 더 최적화된 쿼리라고 할 수 있다. 마지막으로 cross join은 최대한 최대한! 피하는 게 좋다. cross join은 교차 조인으로 조인되는 두 개의 테이블의 모든 경우의 수를 다 조합한 결과가 보이는 쿼리이다. 즉, 두 개 테이블의 모든 정보를 그냥 하나의 테이블 내에서 다 확인할 수 있도록 하는 쿼리라고 볼 수 있다. 그렇기 때문에 정말 정말 필요한 게 아닌 이상, cross join을 사용하게 되면 불필요한 모든 정보를 가지고 오게 된다. 

 

위에서 소개한 4개의 주제처럼 업무를 할 때 주로 이 네가지 부분을 항상 염두에 두고 쿼리 최적화를 진행하려고 한다. 물론 데이터의 크기에 따라서 최적화를 진행했음에도 많은 비용이 나올 수 있지만, 해당 방법을 사용하게 되면 그래도 크으게~ 비용적인 부분에서 문제가 있었던 적은 없었던 것 같다. 또한, 이 방법을 통해서 쿼리를 작성했을 때에는 비용적인 부분도 많이 개선되었지만 내 쿼리를 보는 다른 구성원들이 '쿼리를 좀 더 쉽게 이해할 수 있었다'.라는 이야기를 많이 들었던 것 같다. 하루에 바로 이 4가지 방법을 이용해서 최적화하기는 어렵겠지만, 그래도 항상 염두에 두면서 쿼리를 작성한다면 좀 더 가독성이 좋고 효율적인 쿼리를 짤 수 있을 것이다.