Google BigQuery Standard SQL에서 UDF(User Defined Function) 작성하기

 

Standard SQL의 UDF

 

기존 BigQuery에서 제공 가능한 SQL은 BigQuery 고유의 Legacy SQL뿐이었지만, 현재(2017년 10월)는 SQL 2011과 호환되는 형식의 Standard SQL도 제공하고 있다. Legacy SQL에서 제공하는 UDF는 Map 함수 기반의 방식이며, Standard SQL의 UDF에서 제공하는 것은 실제 SQL 함수와 유사한 방식이다.

Standard SQL이 제공하는 UDF가 기존 BigQuery Legacy SQL의 UDF와 가장 큰 차이는, SQL 문에서 실제로 데이터 연산에 필요한 함수를 제공한다는 점이다. 특히 일반 SQL의 sum, avg 등과 같은 aggregation 방식의 통계 함수도 작성할 수 있다.

 

함수 작성 방법

Standard SQL UDF를 작성하기 위해서는 당연히 BigQuery SQL을 Standard로 전환해야 한다. 다음과 같이 Web UI에서 Use Legacy SQL 옵션을 Uncheck 함으로써 가능하다.

 

그리고 나서 UDF 함수의 내용은 UDF Editor가 아닌 Query Editor에서 직접 입력한다. Query Editor에 입력할 수 있는 SQL 문은 원래 하나로 제한이 되지만, UDF를 작성하는 경우에는 예외적으로 추가로 SQL 문을 작성하여 세미콜론(;)으로 구분할 수 있다. 그리고 User Defined Function의 내용은 Java Script 언어로 작성한다. 다음은 작성한 UDF와 그것을 테스트하는 SQL 문의 예이다.

 

create temporary function udftest(a STRING, b STRING)

returns STRING

language js as '''

return a + ":" + b;

''';

 

SELECT udftest(tbl.by, tbl.author) FROM `jerryjg-kr-ce-api.workshop.comments_copy` tbl LIMIT 1000

 

 

여기서는 udftest라는 함수를 정의하고 입력은 문자열 두 개를 받아 결과로 문자열을 반환한다. 작성 언어 문법은 JavaScript(js)를 사용하고, 내용은 간단하게 두 문자열을 더해 반환하는 것 뿐이다. 실제 SQL 문에서는 udftest 함수에 쿼리 결과에서 나오는 필드 두개를 넣어 호출하였다.

 

Aggregation 함수 작성 방법

 

SQL 문에서 group by 등을 통해 레코드들을 그룹으로 묶으면 그것들의 sun, avg 등과 같은 통계값을 구하는 경우가 있다. Standard SQL UDF에서는 이러한 Aggregation 통계 함수도 작성할 수 있는데, 약간의 예외적인 방법이 필요하다. 일반적인 SQL의 통계 함수는 다음과 같이 group by 절과 함께 함수를 사용하면 자동으로 그것을 감지하여 동작한다.

 

Select sum(tbl.ranking), avg(tbl.ranking) from emp_list;

 

 

하지만 여기 UDF에서는 group by 결과로 만들어진 레코드나 그것의 필드들을 ARRAY_AGG라는 함수로 묶어 배열을 만들어 함수에 전달해야 한다. 다음은 UDF로 만들어진 Aggregation 함수의 예로서 주어진 필드의 max 값을 구하는 함수이다. 실제 SQL이 제공하는 max와 비교하도록 같이 사용하여 보았다.

 

create temporary function udfmax(a ARRAY<INT64>)

returns INT64

language js as '''

var max = 0;

a.forEach(function(i) {

   n = Number(i)

   if(n > max) {

     max = n;

   }

});

return max;

''';

 

SELECT udfmax(ARRAY_AGG(tbl.ranking)), max(tbl.ranking) FROM `jerryjg-kr-ce-api.workshop.comments_copy` tbl group by deleted

 

 

여기 UDF 함수에서는 입력 값이 단일 값이 아닌 ARRAY를 받도록 되어 있는 것을 알 수 있다. 그리고 그것을 사용하는 SQL 구문에서도 ARRAY_AGG() 함수를 이용하여 결과 필드값들을 묶어 배열로 만들어 전달하고 있다.

 

이렇게 만들어지는 UDF 함수는 Query Editor 내에서 작성되는 SQL 세션 내에서만 유효하며 다른 SQL 구문에서는 사용할 수 없다. 즉, 다른 SQL에서 동일한 함수를 사용하려면 같은 내용을 매번 넣어 줘야 한다는 의미이다.

 

참고: https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions