[SQL] Bigquery : 사용자 정의 함수(UDF)

 

 

1. UDF

 

SQL에서도 자주 사용하는 처리 방식을 함수로 만들 수 있다. 예를 들어, CASE WHEN 문을 매 쿼리마다 적용해야 한다. 

CASE WHEN x>= 1 and x < 3 ....... WHEN x >= 3 and x < 5 ..... THEN 1 ELSE NULL 등 이 구문을 모든 쿼리 문마다 적용해야 한다고 생각해보자.

 

반복 작업으로 업무 효율성 및 가독성이 떨어진다. 이를 해결하기 위해 빅쿼리에는 사용자 정의 함수가 존재한다. 말 그대로 내가 함수를 직접 만드는것 이다.

CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64) # 함수 선언 : (변수명과 타입 지정)
RETURNS FLOAT64 # 리턴에는 타입을 지정, 아래부터는 사용하고 싶은 함수명
AS ( 
  (x + 4) / y
); # 반드시 함수가 끝나고 나서는 ; 이 붙어야한다. 


## 쿼리문 : 해당 쿼리문에 UDF를 적용한 모습
SELECT
  val, AddFourAndDivide(val, 2)
FROM
  UNNEST([2,3,5,8]) AS val;

 

 

위와 같이 숫자를 적용하는 것도 좋지만 개인적으론 위에서 언급한 CASE WHEN 문 같은 반복적인 작업을 함수로 만들어주는 게 좋다. Python, Java 등의 if 문과 else if 문을 구성할 수 있다.

 

아래가 그 예시이다.

 

CREATE OR REPLACE FUNCTION `your_project.your_dataset.bin_function`(x FLOAT64)
RETURNS STRING
LANGUAGE js AS """
  if (x >= 2 && x < 4) {
    return '2bins';
  } else if (x >= 4 && x < 6) {
    return '4bins';
  } else if (x >= 6 && x < 8) {
    return '6bins';
  } else if (x >= 8 && x < 10) {
    return '8bins';
  } else if (x >= 10 && x < 12) {
    return '10bins';
  } else {
    return 'out_of_range';  // You can change this to any default category
  }
""";

 

전처리 방식이야 각자 다양하게 사용할 수 있지만, 위와 같은 Custom Function 을 적용할 경우 SQL에서도 가독성과 효율성을 높일 수 있기 때문에 장점이라 할 수 있다. 외쳐 갓 Bigquery