BigQuery의 Procedural Language로 스크립트 작성하기

1. BigQuery Procedural Language란?

BigQuery는 SQL 기반의 데이터 분석 도구이지만, 일반적인 SQL 쿼리만으로는 복잡한 로직을 처리하기 어려울 때가 있습니다. 이런 경우, BigQuery Procedural Language (SQL 스타일의 스크립트라고 생각하면 됩니다.) 를 활용하면 변수 선언, 조건문, 반복문 등을 사용하여 복잡한 로직을 보다 직관적으로 구현할 수 있습니다.

BigQuery에서 제공하는 Procedural Language를 사용하면 다음과 같은 작업이 가능합니다.
• 변수를 활용한 동적 SQL 실행
• 조건문 (IF, CASE)
• 반복문 (LOOP, WHILE, FOR)
• 예외 처리 (BEGIN ... EXCEPTION ... END)

이번 글에서는 실전에서 사용할 수 있는 BigQuery Procedural Language 스크립트를 소개하고, 코드 예시를 통해 이를 어떻게 활용할 수 있는지 살펴보겠습니다.

2. BigQuery Procedural Language의 기본 문법

BigQuery에서 Procedural Language를 사용하려면  서브쿼리만 사용하던 기존 SQL과는 달리, DECLARE, SET, BEGIN … END 등의 키워드를 사용할 수 있습니다.
기본적인 스크립트 구조는 다음과 같습니다.

DECLARE 변수명 데이터타입;
SET 변수명 = 값;

BEGIN
  -- 실행할 SQL 또는 조건문, 반복문
END;

예제를 통해 다양한 키워드를 하나씩 살펴보겠습니다.

3. 변수 선언 및 할당

BigQuery에서는 DECLARE 문을 사용하여 변수를 선언할 수 있습니다. 변수에는 정수, 문자열, 부동소수점 등 다양한 데이터 타입을 사용할 수 있으며, SET 문으로 값을 할당합니다.

DECLARE total_rows INT64;
SET total_rows = (SELECT COUNT(*) FROM `my_dataset.my_table`);

SELECT total_rows;

위 코드에서는 total_rows라는 변수를 선언하고, my_dataset.my_table의 행 개수를 저장한 후, 해당 값을 출력합니다.

4. 조건문 (IF, CASE)

4.1 IF 문 사용하기

BigQuery Procedural Language에서 IF 문을 활용하면 특정 조건에 따라 다른 SQL을 실행할 수 있습니다.

DECLARE row_count INT64;
SET row_count = (SELECT COUNT(*) FROM `my_dataset.my_table`);

BEGIN
  IF row_count > 1000 THEN
    SELECT '데이터가 너무 많습니다.' AS message;
  ELSE
    SELECT '데이터가 적절합니다.' AS message;
  END IF;
END;

이 코드는 my_dataset.my_table의 행 개수를 확인한 후, 1,000개 이상이면 경고 메시지를, 1,000개 미만이면 정상 메시지를 출력합니다.

4.2 CASE 문 사용하기

CASE 문을 사용하여 다중 조건을 처리할 수도 있습니다.

DECLARE category STRING;
SET category = 'A';

BEGIN
  CASE 
    WHEN category = 'A' THEN 
      SELECT 'Category A 선택' AS result;
    WHEN category = 'B' THEN 
      SELECT 'Category B 선택' AS result;
    ELSE 
      SELECT '알 수 없는 카테고리' AS result;
  END CASE;
END;

5. 반복문 (LOOP, WHILE, FOR)

반복문을 사용하면 특정 조건을 만족할 때까지 SQL을 실행할 수 있습니다.

5.1 LOOP 문 사용하기

반복문을 실행할 때는 LEAVE 문을 활용해 명시적으로 종료할 수 있습니다.

DECLARE counter INT64 DEFAULT 0;

BEGIN
  LOOP
    SET counter = counter + 1;

    IF counter >= 5 THEN
      LEAVE;
    END IF;

    SELECT counter;
  END LOOP;
END;

이 코드는 counter 값을 1씩 증가시키면서 5가 되면 반복을 종료합니다.

5.2 WHILE 문 사용하기

WHILE 문을 사용하면 특정 조건이 참일 동안 반복 실행할 수 있습니다.

DECLARE counter INT64 DEFAULT 1;

BEGIN
  WHILE counter <= 5 DO
    SELECT counter;
    SET counter = counter + 1;
  END WHILE;
END;

6. 예외 처리 (BEGIN ... EXCEPTION ... END)

예외 처리 (EXCEPTION)를 사용하면 오류가 발생했을 때 실행할 대체 로직을 정의할 수 있습니다.

DECLARE result INT64;

BEGIN
  BEGIN
    -- 존재하지 않는 테이블을 조회 (오류 발생)
    SET result = (SELECT COUNT(*) FROM `my_dataset.non_existent_table`);
  EXCEPTION WHEN ERROR THEN
    -- 오류 발생 시 실행할 대체 로직
    SELECT '테이블이 존재하지 않습니다.' AS error_message;
  END;
END;

이 코드는 non_existent_table 테이블이 존재하지 않을 경우 예외를 처리하여 "테이블이 존재하지 않습니다."라는 메시지를 출력합니다.

7. 예제: 테이블 데이터 정리하기

아래 예제는 특정 날짜 이전의 데이터를 삭제하는 스크립트입니다.

DECLARE threshold_date DATE;
SET threshold_date = DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);

BEGIN
  DELETE FROM `my_dataset.my_table`
  WHERE created_at < threshold_date;

  SELECT '30일 이전 데이터가 삭제되었습니다.' AS message;
END;

이 코드는 현재 날짜 기준 30일 이전의 데이터를 삭제하는 기능을 수행합니다.

8. 마무리

BigQuery의 Procedural Language를 활용하면 SQL만으로는 구현하기 어려운 복잡한 로직을 보다 직관적으로 작성할 수 있습니다.

다음과 같은 기능을 사용할 수 있습니다.

✅ 변수 선언 및 할당
✅ 조건문 (IF, CASE)
✅ 반복문 (LOOP, WHILE, FOR)
✅ 예외 처리 (EXCEPTION)


실제로 업무에서 특정 기간 내의 데이터 백필 작업시 반복적으로 타겟 날짜를 지정한 후 쿼리를 실행하는 것이 아닌 Loop 문을 활용해 쿼리를 실행할 수 있었습니다.

다만, Procedural Language 사용 시 빅쿼리는 분산된 웨어하우스이므로 트랜잭션을 완전히 지원하지 않는다는 사실을 기억해야 합니다.
예외 발생시 전체 롤백이 불가능하므로, 코드 작성 시 오류 발생 가능성이 있는 경우 철저하게 대비해야 합니다.

이러한 점을 고려하여 적절한 사용 사례인 경우를 판단한 후, 이러한 기능을 적극적으로 활용하면 쿼리의 가독성을 높이고, 유지보수하기 쉬운 코드를 작성할 수 있습니다. 또한 보다 복잡한 데이터 처리 로직을 빅쿼리 내에서 모두 처리할 수 있다는 장점도 가져갈 수 있습니다.