표준 SQL 사용자 정의 함수

표준 SQL의 맞춤 설정 함수

BigQuery에서는 UDF(맞춤 설정 함수)가 지원됩니다. UDF를 사용하면 다른 SQL 표현식 또는 자바스크립트와 같은 다른 프로그래밍 언어를 사용하여 함수를 만들 수 있습니다. 이러한 함수는 입력 열을 받아들여서 작업을 수행하고, 이러한 작업의 결과를 값으로 반환합니다. 이전 SQL의 맞춤 설정 함수에 대한 자세한 내용은 이전 SQL의 맞춤 설정 함수를 참조하세요.

UDF는 일시적인 함수입니다. 즉, 현재 쿼리 또는 명령줄 세션에 대해서만 이를 사용할 수 있습니다. 표준 SQL 쿼리에 사용하기 위해 맞춤 설정 함수를 만들 때는 웹 UI에 있는 UDF 편집기 탭을 사용하지 않습니다. UDF 편집기 탭은 이전 SQL용으로 사용됩니다.

일반 UDF 구문

BigQuery의 맞춤 설정 함수에는 다음과 같은 일반 구문이 사용됩니다.

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

named_parameter:
  param_name param_type

이 구문은 다음 구성요소로 구성됩니다.

  • CREATE { TEMPORARY | TEMP } FUNCTION. 새 함수를 만듭니다. 함수에는 0개 이상의 named_parameter가 포함될 수 있으며, 각 매개변수는 param_nameparam_type 쌍으로 구성됩니다. UDF를 만들 때는 TEMPORARY 또는 TEMP를 포함해야 합니다.
  • [RETURNS data_type]. 함수가 반환하는 데이터 유형을 지정합니다. 함수가 SQL에 정의된 경우, RETURNS 절은 선택사항이며, BigQuery는 SQL 함수 본문으로부터 함수의 결과 유형을 유추합니다. 함수가 외부 언어로 정의된 경우에는 RETURNS 절이 필수입니다. data_type에 허용되는 값에 대한 자세한 내용은 지원되는 UDF 데이터 유형을 참조하세요.
  • [LANGUAGE language AS '''body''']. 함수 및 함수를 정의하는 코드의 외부 언어를 지정합니다.
  • AS (function_definition). 함수를 정의하는 SQL 코드를 지정합니다. function_definition은 SQL 표현식입니다.

외부 UDF 구조

다음 구조를 사용하여 외부 UDF를 만듭니다.

CREATE  { TEMPORARY | TEMP }  FUNCTION function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  [LANGUAGE language]
  AS external_code

외부 UDF 예

CREATE TEMPORARY FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
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, multiplyInputs(x, y) as product
FROM numbers;

+-----+-----+--------------+
| x   | y   | product      |
+-----+-----+--------------+
| 1   | 5   | 5            |
| 2   | 10  | 20           |
| 3   | 15  | 45           |
+-----+-----+--------------+

쿼리 전에 UDF를 여러 개 만들 수 있습니다. 예:

CREATE TEMPORARY FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
CREATE TEMPORARY FUNCTION divideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x / 2;
""";
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,
  multiplyInputs(x, y) as product,
  divideByTwo(x) as half_x,
  divideByTwo(y) as half_y
FROM numbers;

+-----+-----+--------------+--------+--------+
| x   | y   | product      | half_x | half_y |
+-----+-----+--------------+--------+--------+
| 1   | 5   | 5            | 0.5    | 2.5    |
| 2   | 10  | 20           | 1      | 5      |
| 3   | 15  | 45           | 1.5    | 7.5    |
+-----+-----+--------------+--------+--------+

UDF의 결과를 다른 UDF의 입력으로 전달할 수 있습니다. 예:

CREATE TEMPORARY FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
CREATE TEMPORARY FUNCTION divideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x/2;
""";
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,
  multiplyInputs(divideByTwo(x), divideByTwo(y)) as half_product
FROM numbers;

+-----+-----+--------------+
| x   | y   | half_product |
+-----+-----+--------------+
| 1   | 5   | 1.25         |
| 2   | 10  | 5            |
| 3   | 15  | 11.25        |
+-----+-----+--------------+

다음 예는 지정된 JSON 문자열로 이름이 'foo'인 모든 필드의 값을 합산합니다.

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
  RETURNS FLOAT64
  LANGUAGE js AS """
function SumFoo(obj) {
  var sum = 0;
  for (var field in obj) {
    if (obj.hasOwnProperty(field) && obj[field] != null) {
      if (typeof obj[field] == "object") {
        sum += SumFoo(obj[field]);
      } else if (field == "foo") {
        sum += obj[field];
      }
    }
  }
  return sum;
}
var row = JSON.parse(json_row);
return SumFoo(row);
""";

WITH Input AS (
  SELECT STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s, 10 AS foo UNION ALL
  SELECT NULL, 4 AS foo UNION ALL
  SELECT STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s, NULL AS foo
)
SELECT
  TO_JSON_STRING(t) AS json_row,
  SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;
+---------------------------------------------------------------------+---------+
| json_row                                                            | foo_sum |
+---------------------------------------------------------------------+---------+
| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10}       | 14.14   |
| {"s":null,"foo":4}                                                  | 4       |
| {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59    |
+---------------------------------------------------------------------+---------+

지원되는 외부 UDF 언어

외부 UDF는 자바스크립트로 작성된 코드를 지원합니다. 이러한 코드는 사용자가 LANGUAGEjs를 사용하여 지정합니다. 예:

CREATE TEMPORARY FUNCTION greeting(a STRING)
RETURNS STRING
LANGUAGE js AS """
  return "Hello, " + a + "!";
  """;
SELECT greeting(name) as everyone
FROM UNNEST(["Hannah", "Max", "Jakob"]) AS name;

+----------------+
| everyone       |
+----------------+
| Hello, Hannah! |
| Hello, Max!    |
| Hello, Jakob!  |
+----------------+

BigQuery 데이터 유형이 자바스크립트 유형에 매핑되는 방식에 대한 자세한 내용은 자바스크립트의 SQL 유형 인코딩을 참조하세요.

지원되는 외부 UDF 데이터 유형

외부 UDF에 대해 BigQuery는 다음과 같은 데이터 유형을 지원합니다.

  • ARRAY
  • BOOL
  • BYTES
  • DATE
  • FLOAT64
  • NUMERIC
  • STRING
  • STRUCT
  • TIMESTAMP

자바스크립트의 SQL 유형 인코딩

일부 SQL 유형에는 자바스크립트 유형에 대한 직접 매핑이 포함되지만, 다른 유형은 그렇지 않습니다.

자바스크립트가 64비트 정수 유형을 지원하지 않기 때문에, 자바스크립트 UDF의 입력 또는 출력 유형에서 INT64가 지원되지 않습니다. 대신 FLOAT64를 사용하여 정수 값을 숫자로 표현하거나 STRING을 사용하여 정수 값을 문자열로 표현해야 합니다.

BigQuery는 유형을 다음 방법으로 표현합니다.

BigQuery 데이터 유형 자바스크립트 데이터 유형
ARRAY ARRAY
BOOL BOOLEAN
BYTES base64로 인코딩된 STRING
FLOAT64 NUMBER
NUMERIC NUMERIC 값을 정확히 IEEE 754 부동 소수점 값으로 표현할 수 있고 여기에 소수점 부분이 없으며, NUMBER로 인코딩됩니다. 이러한 값의 범위는 [-253, 253]입니다. 그렇지 않으면 문자열로 인코딩됩니다.
STRING STRING
STRUCT 각 STRUCT 필드가 이름이 지정된 필드인 OBJECT입니다.
TIMESTAMP 타임스탬프의 microsecond 부분을 포함하는 마이크로초 필드가 포함된 DATE입니다.
DATE DATE

따옴표 규칙

외부 코드는 따옴표로 묶어야 합니다. 간단한 1줄 코드 스니펫의 경우 표준 따옴표 문자열을 사용할 수 있습니다.

CREATE TEMPORARY FUNCTION plusOne(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS "return x+1;";
SELECT val, plusOne(val) AS result
FROM UNNEST([1, 2, 3, 4, 5]) AS val;

+-----------+-----------+
| val       | result    |
+-----------+-----------+
| 1         | 2         |
| 2         | 3         |
| 3         | 4         |
| 4         | 5         |
| 5         | 6         |
+-----------+-----------+

스니펫에 따옴표가 포함되었거나 스니펫이 여러 줄로 구성된 경우에는 삼중 따옴표 블록을 사용합니다.

CREATE TEMPORARY 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;
+-----------------------+
| everyone              |
+-----------------------+
| Good Morning, Hannah! |
| Good Morning, Max!    |
| Good Morning, Jakob!  |
+-----------------------+

SQL UDF 구조

다음 구문을 사용하여 SQL UDF를 만듭니다.

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

named_parameter:
  param_name param_type

SQL UDF 예

다음 예에서는 SQL 함수가 사용된 UDF를 보여줍니다.

CREATE TEMPORARY FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);
WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, addFourAndDivide(val, 2) AS result
FROM numbers;

+-----+--------+
| val | result |
+-----+--------+
| 1   | 2.5    |
| 3   | 3.5    |
| 4   | 4      |
| 5   | 4.5    |
+-----+--------+

외부 라이브러리 포함

OPTIONS 섹션을 사용하여 외부 UDF를 확장할 수 있습니다. 이 섹션에서는 UDF에 대해 외부 코드 라이브러리를 지정할 수 있습니다.

CREATE TEMP FUNCTION myFunc(a FLOAT64, b STRING)
  RETURNS STRING
  LANGUAGE js AS
"""
    // Assumes 'doInterestingStuff' is defined in one of the library files.
    return doInterestingStuff(a, b);
"""
OPTIONS (
  library="gs://my-bucket/path/to/lib1.js",
  library=["gs://my-bucket/path/to/lib2.js", "gs://my-bucket/path/to/lib3.js"]
);

SELECT myFunc(3.14, 'foo');

이전 예에서 lib1.js, lib2.js, lib3.js의 코드는 UDF의 [external_code] 섹션에 있는 모든 코드에 사용할 수 있습니다. 단일 요소 또는 배열 구문을 사용하여 라이브러리 파일을 지정할 수 있습니다.

UDF 및 웹 UI

BigQuery 웹 UI에서는 하나 이상의 UDF를 사용하여 쿼리를 실행할 수 있습니다.

UDF를 사용하여 쿼리 실행

  1. 쿼리 작성 버튼을 클릭합니다.
  2. 쿼리 편집기 탭을 클릭합니다.
  3. 옵션 표시 버튼을 클릭합니다.
  4. 이전 SQL 사용 체크박스를 선택 해제합니다.
  5. 쿼리 편집기 텍스트 영역에 UDF 문을 입력합니다. 예:

    CREATE TEMPORARY FUNCTION timesTwo(x FLOAT64)
    RETURNS FLOAT64
      LANGUAGE js AS """
      return x*2;
    """;
  6. UDF 문 아래에 쿼리를 입력합니다. 예:

    SELECT timesTwo(numbers) as doubles
    FROM UNNEST([1, 2, 3, 4, 5]) AS numbers;
  7. 쿼리 실행 버튼을 클릭합니다. 쿼리 결과가 버튼 아래에 표시됩니다.

UDF 및 bq 명령줄 도구

Google Cloud SDK에서 bq 명령줄 도구를 사용하면 하나 이상의 UDF가 포함된 쿼리를 실행할 수 있습니다.

UDF가 포함된 쿼리를 실행하려면 다음 구문을 사용하세요.

bq query <statement_with_udf_and_query>

자바스크립트 UDF 권장사항

입력 사전 필터링

입력을 자바스크립트 UDF로 전달하기 전에 쉽게 필터링할 수 있으면, 쿼리 속도가 빨라지고 비용이 낮아질 것입니다.

영구적인 변경 가능 상태 금지

자바스크립트 UDF 호출에서는 변경 가능 상태를 저장하거나 액세스하지 않는 것이 좋습니다.

효율적인 메모리 사용

자바스크립트 처리 환경은 쿼리당 사용 가능한 메모리가 제한되어 있습니다. 로컬 상태를 너무 많이 누적하는 자바스크립트 UDF 쿼리는 메모리 소진으로 인해 실패할 수 있습니다.

한도

  • 단일 행을 처리할 때 자바스크립트 UDF가 출력하는 데이터 양은 약 5Mb 이하여야 합니다.
  • 각 사용자가 특정 프로젝트에서 동시에 실행할 수 있는 자바스크립트 UDF 쿼리 수는 약 6개로 제한됩니다. 동시 쿼리 제한을 초과했다는 오류가 발생하면 몇 분 정도 기다린 후 다시 시도하세요.
  • 자바스크립트 UDF가 시간 초과되어 쿼리를 완료하지 못할 수 있습니다. 시간 제한은 5분 정도로 짧을 수 있지만, 해당 함수가 소비하는 사용자 CPU 시간과 JS 함수에 대한 입력 및 출력 크기를 포함한 여러 요소에 따라 달라질 수 있습니다.
  • 쿼리 작업이 포함할 수 있는 자바스크립트 UDF 리소스 수는 최대 50개입니다(인라인 코드 BLOB 또는 외부 파일).
  • 각 인라인 코드 BLOB 크기는 최대 32KB로 제한됩니다.
  • 각 외부 코드 리소스 크기는 최대 1MB로 제한됩니다.

제한사항

  • DOM 객체 Window, Document, Node와 이를 필요로 하는 함수는 지원되지 않습니다.
  • 네이티브 코드를 사용하는 자바스크립트 함수는 지원되지 않습니다.
  • 자바스크립트의 비트 연산은 32비트만 처리합니다.
  • 비확정성으로 인해 맞춤 설정 함수를 호출하는 쿼리는 캐시 처리된 결과를 사용할 수 없습니다.
  • UDF의 테이블은 참조할 수 없습니다.
이 페이지가 도움이 되었나요? 평가를 부탁드립니다.

다음에 대한 의견 보내기...

도움이 필요하시나요? 지원 페이지를 방문하세요.