# 사용자 정의 함수(UDF)

사용자 정의 함수는 UDF(User Defined Function)로 사용자가 정의한 함수를 의미합니다.

파이썬 같은 프로그래밍 언어에서 함수를 만드는 것처럼, SQL에서도 함수를 만들 수 있습니다.

다음과 같은 경우에 UDF를 사용하면 좋습니다

BigQuery엔 없지만 자바스크립트에 존재하는 함수를 사용하고 싶은 경우
조직에서 공통적으로 정의해서 반복 사용해야 하는 경우

UDF를 만들 수 있는 언어

SQL
JavaScript

UDF의 종류

1. 임시(Temp) UDF : 쿼리문에서만 사용할 수 있는 UDF
1. 영구(Persistent) UDF : 데이터셋에 저장되는 UDF

예전엔 Temp UDF만 존재했다가, Persistent UDF가 등장했습니다.

# 임시 UDF

쿼리문 최상단에 위치해야 하며, CREATE TEMP FUNCTION을 사용합니다.

# SQL

CREATE  { TEMPORARY | TEMP }  FUNCTION function_name ([named_parameter[, ...]])
[RETURNS data_type]
AS (function_definition);

CREATE TEMP FUNCTION add_three_and_divide(x INT64, y INT64) 
  RETURNS FLOAT64
  AS ((x + 3) / y);
	
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

# JavaScript

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

# 영구 UDF

CREATE FUNCTION을 사용하며, TEMP란 단어가 빠지면 됩니다. 영구 UDF는 특정 데이터셋 아래에 테이블처럼 저장됩니다.

# SQL

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

위 쿼리를 실행하면 udf 데이터셋 아래에 다음과 같은 영구 함수가 저장됩니다.

함수를 사용하고 싶다면 Datset.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

# JavaScript

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 + '!';
  }
  """;

이제 아래와 같이 저장된 것을 확인할 수 있습니다.

# 외부 자바스크립트 파일 활용하는 경우

외부 자바스크립트 파일에 있는 함수를 사용하고 싶은 경우, 해당 파일을 Google Cloud Storage에 저장해서 사용해야 합니다.

저는 geultto-udf라는 Google Cloud Storage에 파일을 업로드했습니다. 여러분들의 Google Cloud Storage를 만들어서 자바스크립트 파일을 업로드해주세요!

그 후 OPTIONS에 library 인자에 자바스크립트 파일을 명시하면 됩니다.

이제 다음과 같이 정의해서 사용할 수 있습니다.

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 모음

구글 클라우드 플랫폼의 bigquery-utils (opens new window)에 커뮤니티에서 만든 UDF를 확인할 수 있습니다.

UDF 관련해서 더 자세한 내용이 궁금하시면 BigQuery UDF 사용하기 (opens new window)를 참고해주세요

← VIEW 파티션 →