[BigQuery] 동적 SQL문 작성하는 법

개요

BigQuery의 예약된 쿼리 기능을 사용해서 테이블 별로 postfix를 붙여서 일자 별로 데이터 마트를 생성하는 쿼리를 매일 실행하도록 하려고 하는데 쿼리를 작성할 때, postfix를 동적으로 매일 날짜로 붙이기 위해서 리서치하는 중 알게 된 내용을 정리하려고 한다.

동적 SQL

절차적 언어  |  BigQuery  |  Google Cloud

먼저, BigQuery 에서는 동적 SQL을 실행할 수 있도록 쿼리를 지원을 한다. EXECUTE IMMEDIATE 라는 구문을 지원하며 해당 구문 뒤에 작성한 쿼리를 프로시저로 만들어서 실행하는 방식이다. 다음의 예시를 보자.

-- y = 1 * (3 + 2) = 5
DECLARE y INT64;
EXECUTE IMMEDIATE "SELECT ? * (? + 2)" INTO y USING 1, 3;
SELECT FORMAT('%d', y) AS y;

y 라는 INT64 type의 변수를 선언하고 첫 번째 쿼리는 EXECUTE IMMEDIATE를 활용해서 한 쿼리이고 두 번째는 FORMAT을 활용하여 y라는 값을 볼 수 있는 쿼리이다. 해당 쿼리 이후 다음과 같은 결과를 얻을 수 있다.

첫 번째 쿼리는 프로시져로 실행됐다는 것을 알 수 있다. SELECT 쿼리의 ?로 지정된 곳에 1과 3이 formatting 되어 1 * (3 + 2) 의 결과인 5를 볼 수 있다.

그리고 두 번째 쿼리의 결과를 확인하면 FORMAT 구문을 통해 y라는 값을 formatting 하였는데 결과 역시 5인 것을 확인할 수 있다. 이를 통해 첫 번째 쿼리에서 INTO y 구문에서 결과가 선언한 y 변수에 들어간 것을 알 수 있다. (INTO 구문은 사용하지 않아도 문제가 없다.)

여러 줄의 쿼리를 동적으로 실행하기

그렇다면 우리가 지금 원하는 것은 엄청나게 긴 SELECT 문이 있을 때, 그것을 동적으로 실행하고 싶은 것인데 EXECUTE IMMEDIATE의 구문 자리에 그대로 넣으면 될까? 결론만 말하자면 안된다. 기본적으로 다중 행의 구문을 인식하지 못하기 때문에 그대로 사용하면 안되고 우리가 두 번째 쿼리에서 사용한 FORMAT 구문을 활용해야 한다.

String functions  |  BigQuery  |  Google Cloud

FORMAT 구문은 안의 String 에 대해서 formatting을 지원한다. 우리는 EXECUTE IMMEDIATE 구문 자리에 해당 FORMAT을 이용해 여러 줄의 SQL문을 넣을 것인데 여러 줄의 문자열을 표시하는 방법이 공식 문서에서도 찾을 수가 없고 딱히 사례도 찾지 못해서 (내가 못 찾는 것일 수도...) 직접 트라이 해보다가 알게 된 사실이다. 아래의 예시 쿼리를 보자.

WITH items AS
  (SELECT 'apple' as item
  UNION ALL
  SELECT 'banana' as item
  UNION ALL
  SELECT 'orange' as item)

SELECT *, %s FROM items

items 라는 임시 테이블을 생성하고 %s 라는 값에 동적으로 변수 값을 넣을려고 한다. 그래서 다음과 같이 작성했다.

DECLARE today_date STRING;
SET today_date = (SELECT FORMAT_DATE('%Y%m%d', CURRENT_DATE('Asia/Seoul')));

EXECUTE IMMEDIATE FORMAT(
  """
    WITH items AS
      (SELECT 'apple' as item
      UNION ALL
      SELECT 'banana' as item
      UNION ALL
      SELECT 'orange' as item)

    SELECT *, %s FROM items
  """
  , today_date
)

today_date 라는 변수에 오늘 날짜를 넣고 해당 날짜를 쿼리에 동적으로 넣는 쿼리이다. 여러 줄의 SQL문을 문자열로 감싸는 방법은 Python과 똑같이 """으로 감싸주는 방법이다. 해당 구문을 실행하면 다음과 같은 결과를 얻을 수 있다.

이제 해당 쿼리 부분을 우리가 실행시키고자 하는 쿼리로 바꾸어서 문자열 formatting을 통해 동적으로 실행할 수 있다. 본인은 동적으로 쿼리를 작성하여 예약된 쿼리에 등록하여 사용하고 있다.

마치며

BigQuery를 사용하면서 깨달은 점이 왜 이렇게 많은 기업이 BigQuery를 사용하는 지 알 것 같다. SQL에 대한 이해만 있다면 사용하기 쉽고 편리한 기능(ex. 예약된 쿼리)과 매우 빠른 성능을 보여주고 있어 DW로써 매우 유용하게 사용할 수 있을 것 같다. 쿼리에 대한 비용도 나름 저렴한 편이라 파티션과 SELECT 문에서 컬럼 선정을 잘 하면 충분히 적은 비용으로 쿼리를 잘 사용할 수 있기 때문에 비용 부담도 많이 크지 않다. 해당 글을 통해 나와 같은 문제가 있다면 도움이 됐으면 좋겠다.