BigQuery UDF 사용하기

Google Cloud Platform의 BigQuery에서 udf 사용하는 방법에 대해 작성한 글입니다
- 이 글은 2019년 9월에 작성되었습니다
- BigQuery temp udf, BigQuery persistent udf에 대해 다룹니다
- 아직 persistent UDF는 pre-release이므로 추후에 기능이나 방식이 변경될 가능성이 있습니다

BigQuery UDF

UDF
- UDF는 User Define Function의 약자로 사용자가 정의한 함수
- Python 같은 프로그래밍 언어에서 함수를 만드는 것처럼, SQL에서 함수를 만드는 것을 UDF라고 표현하기도 함
UDF의 종류
- Temp UDF : 쿼리문 위에 정의해서 사용하는 방식
- Persistent UDF : BigQuery의 Dataset에 저장해서 사용하는 방식으로 여러 사람과 사용할 경우 유용
UDF로 사용할 수 있는 언어
- SQL 표현식
- 자바스크립트
Useful UDF 모음집
- bigquery-utils
- bigquery-jslibs

Temp UDF(SQL)

일반 UDF는 Temp udf로 쿼리문을 작성할 때 함께 작성해야 함
```
  CREATE  { TEMPORARY | TEMP }  FUNCTION function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  AS (function_definition);
```
- RETURNS 뒤엔 리턴될 데이터의 타입(생략 가능하지만 명시적으로 적는 것을 추천)
- AS 뒤엔 함수 정의
- 정의 후 ;를 꼭 붙여야 함

SQL 표현식을 사용한 예시

  CREATE TEMP FUNCTION add_three_and_divide(x INT64, y INT64) 
  RETURNS FLOAT64
  AS ((x + 3) / y);
	
  # 밑에는 Sample Data
  WITH numbers AS
    (SELECT 1 as val
    UNION ALL
    SELECT 4 as val)
  SELECT val, add_three_and_divide(val, 2) AS result
  FROM numbers;

Temp UDF(Javascript)

Javascript UDF 외부 javascript 파일을 참고하거나 직접 javascript 문법을 사용할 수 있음
심플한 문법을 사용하면 “ 하나만 사용해도 되고, snippet이거나 여러 줄이면 triple-quoted(“””) 사용
Best 사례나 한계점은 링크 참고

자바스크립트 문법을 사용할 경우

  CREATE  { TEMPORARY | TEMP }  FUNCTION function_name ([named_parameter[, ...]])
  RETURNS data_type
  LANGUAGE js 
  AS """body"""];

예시

  CREATE TEMP FUNCTION customGreeting(a STRING)
  RETURNS STRING
  LANGUAGE js AS """
    var d = new Date();
    if (d.getHours() < 12) {
      return 'Good Morning, ' + a + '!';
    } else {
      return 'Good Evening, ' + a + '!';
    }
    """;
	  
  SELECT customGreeting(names) as everyone
  FROM UNNEST(["Hannah", "Max", "Jakob"]) AS names;

외부 자바스크립트 파일을 참고할 경우

Google Storage에 라이브러리 파일을 넣은 후 사용
해당 라이브러리에 있는 함수를 AS 뒤에 넣어서 사용

  CREATE  { TEMPORARY | TEMP }  FUNCTION function_name ([named_parameter[, ...]])
  RETURNS data_type
  LANGUAGE js 
  OPTIONS (
      library=['gs://bucket/library.js']
  )
  AS """librarys_method()"""];

Persistent UDF

Temp UDF는 쿼리를 작성할 때마다 위에서 정의해야 하는 불편함이 있음
- 따라서 이런 정의를 저장해두고, 활용하고 싶을 경우 Persistent UDF 사용
Dataset 아래에 UDF로 저장해서 활용함
CREATE 문 뒤에 TEMP 없이 바로 FUNCTION을 작성

Persistent UDF 생성하기(SQL)

BigQuery udf 데이터셋 생성

multiply_inputs 함수 생성

  CREATE OR REPLACE FUNCTION udf.multiply_inputs(x FLOAT64, y FLOAT64)
  RETURNS FLOAT64
  AS (x * y);

udf 데이터셋 확인
- 언어, 반환 유형, 정의, 인수

쿼리에서 UDF 사용하기

Dataset.function_name으로 사용 => 여기선 udf.multiply_inputs

  WITH numbers AS
    (SELECT 1 AS x, 5 as y
    UNION ALL
    SELECT 2 AS x, 10 as y
    UNION ALL
    SELECT 3 as x, 15 as y)
  SELECT x, y, udf.multiply_inputs(x, y) as product
  FROM numbers

Persistent UDF 생성하기(Javascript)

SQL에서 하는 방식과 동일
BigQuery udf 데이터셋 생성(이미 존재하면 생략)

custom_greeting 함수 생성

  CREATE OR REPLACE FUNCTION udf.custom_greeting(a STRING)
  RETURNS STRING
  LANGUAGE js AS """
    var d = new Date();
    if (d.getHours() < 12) {
      return 'Good Morning, ' + a + '!';
    } else {
      return 'Good Evening, ' + a + '!';
    }
    """;

udf 데이터셋 확인

쿼리에서 UDF 사용하기

  SELECT udf.custom_greeting("BYEON") AS result

외부 자바스크립트 파일을 활용할 경우
- temp UDF처럼 Google Storage에 저장하고 사용
- Uber의 H3 함수를 사용해보는 예시
- Github에서 자바스크립트 파일 다운로드
  - Raw 클릭 후 다른 이름으로 저장
- Google Storage에 해당 파일 업로드
  - 저는 geultto-udf라는 곳에 저장
- BigQuery에서 함수 정의
```
  CREATE OR REPLACE FUNCTION udf.geo_to_h3(lat FLOAT64, lng FLOAT64, resolution INT64)
  RETURNS STRING 
  LANGUAGE js 
  AS
  '''return h3.geoToH3(lat, lng, resolution)''' 
  OPTIONS (library=["gs://geultto-udf/h3-js.umd.3.5.0.js"]);
```
- udf 데이터셋 확인
- 쿼리 예시
```
  SELECT udf.geo_to_h3(36.123123, 128.123123, 8) as h3
```

정리

CREATE TEMP FUNCTION을 사용하면 TEMP UDF 생성
CREATE FUNCTION을 사용하면 Persistent UDF 생성
- Dataset 아래에 저장
CREATE OR REPLACE FUNCTION는 기존에 함수가 저장할 경우 수정함
UDF는 SQL, Javascript로 생성 가능
- Javascript 활용시 단순 문법으로 생성 가능하고, 자바스크립트 라이브러리 파일을 사용해 생성 가능
- 공식 문서 : 권장 사항, 한도, 제한사항이 있으니 꼭 확인!

Reference

카일스쿨 유튜브 채널을 만들었습니다. 데이터 사이언스, 성장, 리더십, BigQuery 등을 이야기할 예정이니, 관심 있으시면 구독 부탁드립니다 :)

PM을 위한 데이터 리터러시 강의를 만들었습니다. 문제 정의, 지표, 실험 설계, 문화 만들기, 로그 설계, 회고 등을 담은 강의입니다

이 글이 도움이 되셨거나 다양한 의견이 있다면 댓글 부탁드립니다 :)

Buy me a coffee