[Google BigQuery] 1라인 쿼리에서 변수를 사용하고 싶을 때

0. 배경 (문제 상황)

---

데이터 분석 프로젝트를 진행하면서 Google BigQuery로 구성된 DW에 저장된 전체 서비스 로그와 사용자 정보가 저장된 Table로부터 Mart Table을 생성하는 CTAS Query를 작성해야할 일이 있었습니다. 전체 서비스 로그가 하나의 Table로 모여 저장되고 있었기 때문에 내부적으로 이를 저장할 때에는 해당 로그가 어떤 서비스에 대한 로그인지를 식별하기 위한 식별자가 들어갔고, 이 식별자를 알고 있으면 특정 서비스에 대한 로그만을 필터링하여 이를 데이터 분석에 활용할 수 있었습니다.

 

하지만 통계쿼리의 특성 상 쿼리 길이가 굉장히 길고(수백라인...) 각 서비스들이 가지는 자신 만의 히스토리가 데이터에 스며있기 때문에 이 모든 맥락을 이해하며 데이터 분석에 활용할 수 있는 Mart Table을 생성하는 쿼리를 작성하는 일은 상당히 어렵고 복잡한 일이었습니다. 여기서 중요한 점은 하나의 큰 서비스 안에 있는 여러 하위 서비스 각각에 대해서 Mart Table이 따로 구성되어야 하는 상황이라 이 작업을 하위 서비스의 수만큼 반복해야한다는 문제점이 있었습니다.

 

다행히도 해당 작업의 목적이 결국은 서비스 분석이기 때문에 각 서비스의 성격에 따라서 특수하게 포함되는 몇 가지 지표들을 제외하면 DAU, WAU, MAU와 같은 서비스 활성도 평가에 사용되는 주요 지표들을 각 서비스가 공통으로 사용했고, 그렇기 때문에 재사용 가능하도록 쿼리를 잘 작성해놓고 서비스 식별자만 바꿔가면서 쿼리를 한번씩 실행 해주면 작업이 수월해지는 상황이었습니다. 즉, 특정 서비스를 위한 1회성의 쿼리를 작성하는 것이 아니라 다른 여러 서비스에 대해서도 서비스 식별자만 변경하면 해당 쿼리가 그 식별자에 해당하는 서비스에 대한 집계 Table을 생성할 수 있도록 재사용성을 갖춘 쿼리를 작성하는 것이 요구되는 상황이었습니다.

 

바로 감이 오시겠지만, 서비스 식별자로 서비스 로그를 구분하는 쿼리의 중복을 제거하고, 서비스 식별자 자체를 변수로 사용하여 쿼리 상단에서 쉽게 변경할 수 있도록 구성하는 방식으로 쿼리를 작성하기로 결정했습니다.

 

물론 BigQuery에서도 다음과 같이 익숙한 형태로 변수를 선언하고 사용하는 것이 가능합니다.

DECLARE service_identifier STRING DEFAULT 'service_identifier';
SELECT * FROM all_service_logs WHERE service = service_identifier;

하지만 내부적으로 사용하는 시스템에서는 여러문장의 쿼리를 한번에 실행하는 것이 불가능했기 때문에 CTAS문 1라인에 변수선언도, 각 지표들에 대한 복잡한 집계쿼리도 모두 포함되어야 하는 난감한 상황이 되었습니다. 지속적으로 해당 구문을 1라인 안에 녹여서 사용할 수 있는 방법이 없는 지 찾아봤지만, 불가능했습니다.

 

그래서 Google BigQuery 상에서 1라인에 변수선언, 복잡한 각 지표들에 대한 통계쿼리를 모두 녹여낼 수 있는 방법을 고민하게 됩니다.

 

 

1. 솔루션

---

BigQuery에서 1라인 쿼리에 변수, 복잡한 통계값 산출로직을 모두 포함하여 실행할 수 밖에 없는 상황인 경우, 우리는 "WITH 구문"을 사용해서 이러한 문제를 해결할 수 있습니다. 우선 아래와 같이 변수를 선언한다고 생각하고 WITH 구문을 작성합니다.

WITH identifiers AS (
	SELECT '<serivce_identifier>' AS svi
),

즉, 위와 같이 WITH 구문을 작성하면, 마치 "'<service_identifier>'라는 값을 가지는 변수 svi"를 선언한 것과 같은 효과를 내게 됩니다. 만약 여러 개의 변수를 정의하여 사용해야하는 상황이라면, 다음과 같이 작성할 수 있습니다.

WITH identifiers AS ( 
	SELECT '<serivce_identifier>' AS svi,
	SELECT '<service_property>' AS svp
),

이렇게 작성하면 변수 svi, svp를 선언하고 값을 할당한 것과 유사한 효과를 내게 됩니다.

 

물론, WITH 구문을 사용하여 이렇게 작성하는 것이 변수를 선언하여 사용하는 방법이다! 라고 볼 수는 없습니다. 이 방법은 약간의 트릭에 가깝습니다. 그래서 위와 같이 마치 변수처럼 선언한? 값들을 사용하고 싶다면 다음과 같이 쿼리를 작성하시면 됩니다.

SELECT * 
FROM all_service_logs
WHERE service = (SELECT svi FROM identifiers)
	AND property = (SELECT svp FROM identifiers);

즉, 종합해서 이를 하나로 합쳐보면, 다음과 같은 쿼리를 완성할 수 있습니다.

WITH identifiers AS ( 
	SELECT '<serivce_identifier>' AS svi,
	SELECT '<service_property>' AS svp
),
SELECT * 
FROM all_service_logs
WHERE service = (SELECT svi FROM identifiers)
	AND property = (SELECT svp FROM identifiers);

이렇게 작성하면 위에서 원했던 내용처럼 WITH 구문으로 정의한 identifiers 부분에서 하나의 값을 수정하는 것만으로도 해당 식별자를 사용하는 모든 쿼리에 영향을 줄 수 있기 때문에 굉장히 편리하고, 재사용성을 가지며, 신뢰할 수 있는 쿼리를 작성할 수 있었습니다 (각 지표 계산을 수행하는 로직들을 잘 작성했다는 가정하에!).

 

설명이 길었지만, 간편하게 정리하면 Google BigQuery 상에서 1라인 쿼리에서도 마치 변수를 사용하는 것과 같은 효과를 낼 수 있는 방법이 있다! 라는 것을 공유하고 싶었습니다.

 

 

2. 결론

---

다시한 번 강조드리지만 이 방법이 BigQuery에서 변수를 선언하고 사용하는 방법이 아닙니다! 엄연히 BigQuery에는 DECLARE ~ DEFAULT 문을 활용하여 리터럴 변수를 선언하고 이를 사용할 수 있는 방법이 존재합니다. 하지만 위의 문제 상황처럼 DECLARE 문은 어떤 값을 산출하는 쿼리와 1라인으로 결합해서 사용하는 것이 불가능하기 때문에 내부적으로 사용하는 시스템의 사정 상 한번에 1라인 밖에 실행할 수 없는 시스템에서 업무 또는 프로젝트를 진행하셔야 하는 상황이라면 "약간의 트릭이지만 이런 방법도 있다" 라는 느낌으로 이 방법을 공유드리는 것입니다.

 

아래 참고자료에서도 언급되고 있지만 이 솔루션은 WITH 구문을 원래 목적과 조금 다르게 사용하기 때문에 이상적인 문제해결의 사례라고 볼 수는 없습니다. 하지만 반드시 1라인 쿼리만을 실행해야하는 제약조건이 존재하는 상황에서 변수의 개념을 도입하여 작성한 쿼리의 재사용성을 높이고 싶은 경우라면 이 방법은 문제해결에 효과적으로 사용될 수 있다고 생각합니다.

 

혹시 이것이 잘못된 접근이거나 더 좋은 해결방법이 있다거나 있다면 댓글을 통해 피드백 주시면 더 많은 분들에게 도움이 될 것입니다. 피드백은 언제나 감사하는 마음으로 경청하겠습니다! 감사합니다.

 

 

참고자료

---

해당 글은 다음 다음 자료를 참고하여 작성 되었습니다.

https://stackoverflow.com/questions/29759628/setting-big-query-variables-like-mysql

Setting Big Query variables like mysql