[Bigquery] Shard 테이블 날짜별 마이그레이션 (feat. 빅쿼리 절차적 언어_동적 SQL 만들기)

빅쿼리 샤드 테이블 마이그레이션 방법 정리 (feat. 빅쿼리 절차적 언어_동적 SQL 만들기.)

 

먼저 빅쿼리에서는 서브쿼리를 많이 사용하는 쿼리는 실행할때마다 모든 쿼리에대해 요금이 발생한다. 가능하면 서브쿼리의 필요한 부분을 select 한뒤 별도 테이블로 만들어주고 해당 테이블에 대해 메인 쿼리를 실행하는 방법으로 실행할 때 마다 대용량의 요금이 발생하는 것을 지양할 수 있다. 

 

단, 빅쿼리는 테이블을 날짜별로 나누는 샤드 테이블 형태를 사용하기 때문에 이 형태를 그대로 사용하고 싶다면 별도 테이블 또한 날짜별로 쌓아야 한다. 미래 날짜의 경우 빅쿼리 스튜디오에서 제공하는 스케쥴러를 통해서 어렵지 않게 가능하지만 과거의 날짜로는 스케쥴링이 불가하다.

 

예를 들어 1년치의 데이터가 필요한 경우라면, 1년 동안의 날짜별로 나누어서 테이블을 365개 생성해야하는데 이런 경우 빅쿼리의 절차적 언어를 활용하여 동적 SQL 을 만들어 데이터 마이그레이션이 가능하다. 

 

빅쿼리의 절차적 언어 (Procedural Language) 

https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language

Procedural language  |  BigQuery  |  Google Cloud

googleSQL의 절차적 언어는 여러 문을 하나의 쿼리로 실행할 수 있게 해주는 다중 문 쿼리로 여러 목적에 활용될 수 있다.

1. 공유된 상태를 가지며 순차적으로 여러 문을 실행
2. 테이블 생성 또는 삭제와 같은 관리 작업을 자동화
3. 조건문 IF 및 반복문 WHILE 과 같은 프로그래밍 구조를 사용하여 복잡한 논리를 구현

쉽게 말해 한번의 요청으로 여러개의 쿼리 작업을 수행하도록 하기 위해 SQL 임에도 불구하고 프로그래밍 언어처럼 사용할 수 있게 지원되는 기능인 것 같다. 

예를 들어 DECLARE 으로 변수를 선언하고, SET으로 값 할당할 수 있고,  IF / WHILE / LOOP 문 등을 통해 반복문을,

EXECUTE IMMEDIATE 을 통해 동적 SQL 을 실행할 수 있다.  개발자만 이해할 수 있겠지만..

상세 문법 및 사용법은 위의 공식문서를 참고하도록 하자. 

 

 

 

 

샤드 테이블 마이그레이션 _ 동적 SQL 쿼리 (주석에 코드설명) 

-- Shard 하고자 하는 시작일과 종료일을 설정해주기 위한 변수 선언
DECLARE start_date DATE default '2024-01-01';
DECLARE end_date DATE default '2024-01-03';

--데이터 타입을 STRING 이 아닌 DATE로 해야 날짜계산 함수를 쓸수 있음. 

DECLARE table_date STRING; -- DATE 를 STRING 으로 변환하기 위한 변수 선언 

SET table_date = FORMAT_DATE('%Y%m%d', start_date); -- 날짜를 YYYYMMDD 형태로 변환  

LOOP -- 반복문 시작 

  IF start_date > end_date THEN -- 조건: 시작일이 마지막일보다 전이면 계속 반복 
  
  	BREAK; -- 그렇지 않으면 반복문 종료 
  END IF;

-- 동적 SQL 실행 
-- 이때 바로 쿼리 사용이 불가하여 FORMAT() 함수 """ 내에 쿼리를 사용해야 함
 
 EXECUTE IMMEDIATE FORMAT(
    
  -- 동적으로 활용하고자 하는 쿼리 추가   
    """ 
         CREATE OR REPLACE TABLE  -- 없으면 생성하고(CREATE) 존재하면 덮어씀(REPLACE)
              
              dataset.new_table_name_%s ( -- 빅쿼리의 dataset명과 새로만들 table 명
              				-- %s는 변수임! 날짜를 넣을 예정 

 		-- 테이블 스키마 정의. ( 컬럼명 + 데이터 타입) 
                            column1 STRING
                            ,column2 INTEGER
                            ,column3 STRING
                            ,column4 STRING
                            ,column5 STRING

              )
              AS (     -- 빅쿼리 메인테이블에서 읽어들여 별도테이블에 넣고자 하는 데이터
              		   -- 이때 컬럼 명은 위에서 정의한 스키마와 일치해야한다. 

		     SELECT column1
                          ,column2
                          ,column3
                          ,column4
                          ,column5
                        
                      FROM 
                        `project.dataset.main_table_name_*` -- 빅쿼리의 dataset명과 main table 명
                      WHERE
                       _TABLE_SUFFIX = %s -- 여기서도 날짜 넣을 예정 
              )     

              
    
    """
    ,table_date -- 앞서 사용한 첫번째 %s  
    , FORMAT('\"%s\"',table_date) -- 두번째 %s 여기서는 ""가 필요하기 때문에 FORMAT 사용. 
  );

  SET start_date = DATE_ADD(start_date , INTERVAL 1 DAY); -- 시작일 하루씩 증가한 값 할당.  
  SET table_date = FORMAT_DATE('%Y%m%d', start_date); 	  -- YYYYMMDD 형태로 변환  

END LOOP;  -- 반복문 종료

 

 

결과는 아래와 같이 쿼리 하나가 돌때마다 Procedure 단위로 성공시 success / 실패시 fail 과 error를 같이 뱉어낼 것이다. 

 

단, 일반 SQL 쿼리에서 오류 시 실행 전 알려주는 것과 달리 FORMAT 내 텍스트 처리 되었기 때문에 쿼리 내 오류를 미리 알 수 없다. AS( SELECT ~~ ) 절 내의 쿼리만 따로 뽑아서 쿼리문에 에러가 있는지 검증 후 그대로 붙여넣기 하여 사용하는게 좋다 -!! 한방에 돌아가면 갱장히 짜릿