UDF란?
BigQuery > 사용자 정의 함수 (공식문서)
UDF(User Define Function)이란 사용자가 정의한 함수를 의미한다.
UDF를 사용하면 SQL 표현식 또는 JavaScript 코드를 사용하여 함수를 만들 수 있다. UDF는 입력으로 구성된 열을 받아 입력을 기반으로 작업을 수행하고 작업의 결과를 값으로 반환한다.
UDF는 영구 UDF(persistent UDF) 또는 임시 UDF(Temporary UDF) 로 정의할 수 있다. 영구 UDF는 여러 쿼리에서 재사용 가능하고, 임시 UDF는 단일 쿼리 범위에만 사용 가능하다.
UDF 사용 언어
- SQL
- JavaScript
UDF 종류
- Temporary UDF : 쿼리문 위에 정의해서 사용하는 방식이다.
- Persistent UDF : BigQuery의 데이터셋에 저장해서 사용하는 방식으로 여러 사람과 공유할 경우 유용하다.
UDF 사용
UDF를 만들기 위해서는 CREATE (TEMP) FUNCTION 문을 사용한다.
- Temporary UDF
- 쿼리가 완료되는 즉시 만료된다.
- Persistent UDF
DROP FUNCTION문을 사용하여 삭제한다.
UDF 제한사항
- Temporary UDF
- 임시 UDF를 만들 때
function_name에는 마침표를 포함할 수 없다. - VIEW와 영구 UDF는 임시 UDF를 참조할 수 없다.
- 임시 UDF를 만들 때
- Persistent UDF
- 각 데이터 셋에는 이름이 같은 영구 UDF를 한 개만 포함할 수 있다.
- 이름이 동일한 데이터 셋의 테이블 이름과 동일한 UDF를 만들 수 있다.
- 다른 영구 UDF 또는 논리 VIEW에서 영구 UDF를 참조하는 경우 데이터 셋으로 이름을 검증해야 한다.
CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFuntion());
SQL UDF
# Temporary UDF : 쿼리문 작성 시 생성
CREATE [TEMPORARY | TEMP] FUNCTION <function_name> (<function_parameters>)
[RETURNS data_type]
AS <function_definition>;
# Persistent UDF : BigQeury UDF 데이터 셋 생성
CREATE OR REPLACE FUNCTION <function_name>(<function_parameters>)
[RETURNS data_type]
AS <function_definition>;- 'RETURNS + 리턴 될 데이터 타입' 은 생략 가능하다.
- AS 뒤엔 함수를 정의한다.
- 함수 정의 후
;를 반드시 붙여야 한다.- 보통 SQL 쿼리문에서
;가 나오면 하나의 쿼리문이 종료됨을 의미하지만 UDF에서 함수를 정의할 땐 예외사항 으로 동작한다. - function을 생성하기 위해
;를 붙여야 하고, function이 유효하게 사용되기 위해 쿼리문과 같이 실행해야 한다.
- 보통 SQL 쿼리문에서
SQL TEMP UDF 예시
-- URL decode function example
#standardSQL
CREATE TEMP FUNCTION URLDECODE(url STRING) AS ((
SELECT SAFE_CONVERT_BYTES_TO_STRING(
ARRAY_TO_STRING(ARRAY_AGG(
IF(STARTS_WITH(y, '%'), FROM_HEX(SUBSTR(y, 2)), CAST(y AS BYTES)) ORDER BY i
), b''))
FROM UNNEST(REGEXP_EXTRACT_ALL(url, r"%[0-9a-fA-F]{2}|[^%]+")) AS y WITH OFFSET AS i
));
SELECT
column_name,
URLDECODE(REGEXP_EXTRACT(column_name, "url=([^&]+)")) AS url
FROM `project.dataset.table`JavaScript UDF
- 자바스크립트 UDF는 외부 자바스크립트 파일을 참고하거나 직접 자바스크립트 문법을 이용하여 사용할 수 있다.
- 외부 자바스크립트 파일을 참고할 경우는 다음과 같이 사용한다.
Google Storage에 라이브러리 파일을 첨부한 뒤, 해당 라이브러리에 있는 함수를 AS 뒤에 첨부한다.
- 외부 자바스크립트 파일을 참고할 경우는 다음과 같이 사용한다.
- 원시 문자열 내에서 자바스크립트 코드는 따옴표로 둘러싸여 있다. 짧은 문법은
"을 하나만 사용하고, 스니펫(snippet)이거나 여러 줄이면 triple-quoted"""을 사용한다. - 지원되는 자바스크립트 UDF 데이터 유형 은 공식 문서를 참고한다.
# Temporary UDF : 쿼리문 작성 시 생성
CREATE [TEMPORARY | TEMP] FUNCTION <function_name> (<function_parameters>)
RETURNS data_type
LANGUAGE js
AS <"""body""">];JavaScript UDF 권장사항
- 입력 사전 필터링
- 입력을 자바스크립트 UDF로 전달하기 전에 쉽게 필터링할 수 있으면, 쿼리 속도가 빨라지고 비용이 절감된다.
- 영구적인 변경 가능 상태 금지
- 자바스크립트 UDF 호출에서는 변경 가능 상태를 저장하거나 액세스하지 않는 것이 좋다.
- 효율적인 메모리 사용
- 자바스크립트 처리 환경은 쿼리 당 사용 가능한 메모리가 제한되어 있다.
- 로컬 상태를 너무 많이 누적하는 자바스크립트 UDF 쿼리는 메모리 소진으로 인해 실패할 수 있다.
정리
- BigQuery UDF는 SQL과 JavaScript 언어를 지원하며 각각 임시, 영구 타입으로 생성 가능하다.
CREATE TEMP FUNCTION을 통해 Temporary UDF를 생성한다.CREATE FUNCTION을 통해 Persistent UDF를 생성한다.- 영구 UDF는 Data Set 아래에 저장한다.
CREATE OR REPLACE FUNCTION은 기존 함수가 저장할 경우 수정한다.
