BigQuery 스크립팅

1. 절차적 언어 

• 변수 선언 후 공유 상태로 한 시퀀스에서 여러 문을 실행 (많은 쿼리를 절차에 따라서 실행한다!)
• 테이블 생성/삭제 등의 관리 태스크를 자동화 
• IF 및 WHILE과 같은 프로그래밍 구조를 사용하여 복잡한 로직 구현 
• BEGIN과 END를 사용해 블록을 나타낸다. 블록 내의 모든 문은 세미콜론으로 끝나야 한다. 

 

1.1 DECLARE / SET 

• 변수 선언과 할당 

declare end_dt date; 
declare period int64 default 5; -- 매개변수 

set end_dt = date('2021-01-15');

select
  date(timestamp_micros(event_timestamp)) date,
  count(distinct user_pseudo_id) user_count
from 
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
  unnest(items)
where 
  event_name = 'add_to_cart'
  AND _TABLE_SUFFIX BETWEEN 
    format_date('%Y%m%d', end_dt-period) 
    AND format_date('%Y%m%d', end_dt)
group by 1

 

 

 

1.2 EXECUTE IMMEDIATE 

• 동적 SQL 실행을 위한 문 

declare fill_period int64 default 3; 

begin
  declare max_date date; 

  execute immediate """
    select max(date) from temp.add_to_cart 
  """ --문자열을 그대로 쿼리로 실행 

  into max_date; --쿼리 실행 결과를 변수에 할당 

  create temp table recent_data as 
    select
      date(timestamp_micros(event_timestamp)) date,
      count(distinct user_pseudo_id) user_count
    from 
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
      unnest(items)
    where 
      event_name = 'add_to_cart'
      AND _TABLE_SUFFIX BETWEEN 
        format_date('%Y%m%d', max_date+1) 
        and format_date('%Y%m%d', max_date+fill_period+1) --max date 이후 3일 
    group by 1
  ;

  select * from recent_data; 
end;

 

 

declare fill_period int64 default 3; 

begin
  declare max_date date; 

  execute immediate """
    select max(date) from temp.add_to_cart 
  """ 

  into max_date; --쿼리 실행 결과를 변수에 할당 

  create temp table recent_data as 
    select
      date(timestamp_micros(event_timestamp)) date,
      count(distinct user_pseudo_id) user_count
    from 
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
      unnest(items)
    where 
      event_name = 'add_to_cart'
      AND _TABLE_SUFFIX BETWEEN 
        format_date('%Y%m%d', max_date+1) 
        and format_date('%Y%m%d', max_date+fill_period+1) --max date 이후 3일 
    group by 1
  ;

  merge temp.add_to_cart as a -- 원본 테이블에 insert 또는 update  
  using recent_data as r 
  on a.date = r.date 
  when not matched then 
    insert(date, user_count)
    values(date, user_count)
  when matched then 
    update set user_count = r.user_count;

  drop table recent_data;

  select max(date) from temp.add_to_cart; 

end;

 

 

1.3 IF / THEN / ELSEIF / ELSE / END IF

-- 필요한 변수를 선언한다. 
DECLARE DIE1, DIE2, TOTAL INT64;

-- 결과를 넣을 문자열을 선언한다. 
DECLARE RESULT STRING; 

-- 두 개의 난수를 생성하고 변수를 설정한다. 둘의 합을 구한다. 
SET (DIE1, DIE2) = (CAST(FLOOR(RAND()*6)+1 AS INT64), CAST(FLOOR(RAND()*6)+1 AS INT64));
SET TOTAL = DIE1 + DIE2; 

-- if 블록으로 특별한 결과를 확인하고 결과를 설정한다. 
-- if 블록에서는 BEGIN 또는 END문을 사용하지 않는다. 
IF (DIE1 = 1 AND DIE2 = 1) THEN SET RESULT ='Snake eyes.';
ELSEIF (DIE1 = 6 AND DIE2 = 6) THEN SET RESULT ='Boxcars.';
ELSEIF (DIE1 = 4 AND DIE2 = 4) THEN SET RESULT ='Hard eight.';
ELSE SET RESULT ='Nothing special.'; 
END IF; 

-- 결과를 사용자에게 반환한다. 
SELECT FORMAT('You rolled %d and %d for a total of %d. %s',DIE1, DIE2, TOTAL, RESULT);

 

 

 

 

1.4  LOOP / END LOOP

LOOP는 BREAK (또는 LEAVE) 하지 않는 한 영원히 실행된다. 

DECLARE I INT64 DEFAULT 0; 
DECLARE R ARRAY<INT64> DEFAULT [];

LOOP 
  SET I = I + 1; 
  SET R = ARRAY_CONCAT(R, [I]);
  IF I > 9 THEN LEAVE;
  END IF;
END LOOP; 

SELECT * FROM UNNEST(R);

 

 

1.5 WHILE / DO / END WHILE 

DECLARE I INT64 DEFAULT 0;
DECLARE R ARRAY<INT64> DEFAULT [];

WHILE I < 10 DO 
  SET I = I+1; 
  SET R = ARRAY_CONCAT(R, [I]);
END WHILE;

SELECT * FROM UNNEST(R);

 

 

 

1.6 예외 처리

예외 처리는 BEGIN/END 블록으로 범위를 지정한다.

예외가 발생하면 EXCEPTION문을 바로 실행한다. 

BEGIN 

SELECT 1/0; -- 0으로 나누기 오류 
EXCEPTION WHEN ERROR THEN 
SELECT 'Whare are you even doing there.';

END

 

 

 

2. 저장 프로시져 

• 다른 쿼리 또는 다른 저장 프로시져에서 호출 할 수 있는 문 (스크립트를 함수처럼 사용 할 수 있게 해준다!) 
• 입력 인수를 사용하고 값을 출력으로 반환 

CREATE OR REPLACE PROCEDURE temp.create_customer(name STRING) --매개변수 name 지정

BEGIN
  DECLARE id STRING;
  SET id = GENERATE_UUID();
  INSERT INTO temp.customers (customer_id, name)
    VALUES(id, name);
  SELECT FORMAT('Created customer %s (%s)', id, name);
END

▼

CALL temp.create_customer('foo')

 

 

 

3. UDF (사용자 정의 함수)

• SQL 표현식 또는 자바스크립트 코드를 사용하는 사용자 정의 함수 
• 영구 UDF 또는 임시 UDF로 정의 가능 

-- array 안에 struct가 들어간 경우 
SELECT  
  (select value.string_value from unnest(event_params) where key='page_title') page_title,
  (select value.string_value from unnest(event_params) where key='page_location') page_location
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`

 

 

-- 매개변수로 지정하고 싶은 경우  
create temp function get_value(_key string, _event_params any type) as (
  (select value.string_value from unnest(_event_params) where key=_key) 
);

SELECT 
  get_value('page_title', event_params) page_title,
  get_value('page_location', event_params) page_location
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`

 

 

4. TVF (사용자 정의 테이블 함수) 

• 테이블을 반환하는 사용자 정의 함수 
• 뷰와 비슷하게 작동하되 매개변수 사용 가능한 장점 (뷰테이블인데 매개변수 사용 가능!) 

CREATE OR REPLACE TABLE FUNCTION temp.names_by_year(y INT64)
AS 
  SELECT year, name, SUM(number) as total 
  FROM `bigquery-public-data.usa_names.usa_1910_current` 
  WHERE year = y
  GROUP BY year, name