CREATE FUNCTION(SQL 및 Python)

적용 대상: Databricks SQL Databricks Runtime

set개의 인수를 받아 스칼라 값이나 set개의 행을 반환하는 SQL 스칼라 또는 table 함수를 만듭니다.

적용 대상: Databricks SQL Databricks Runtime 13.3 LTS 이상

인수의 set 사용하고 스칼라 값을 반환하는 Python 스칼라 함수를 만듭니다.

Python UDF에는 서버리스 또는 프로 SQL 웨어하우스 또는 공유 또는 단일 사용자 Unity Catalog 클러스터에서 Unity Catalog 필요합니다.

적용 대상: Databricks SQL Databricks Runtime 14.1 이상

위치 매개 변수 호출 외에도 명명된 매개 변수 호출을 사용하여 SQL 및 Python UDF를 호출할 수도 있습니다.

구문

CREATE [OR REPLACE] [TEMPORARY] FUNCTION [IF NOT EXISTS]
    function_name ( [ function_parameter [, ...] ] )
    { [ RETURNS data_type ] |
      RETURNS TABLE [ ( column_spec [, ...]) ] }
    [ characteristic [...] ]
    { AS dollar_quoted_string | RETURN { expression | query } }

function_parameter
    parameter_name data_type [DEFAULT default_expression] [COMMENT parameter_comment]

column_spec
    column_name data_type [COMMENT column_comment]

characteristic
  { LANGUAGE { SQL | PYTHON } |
    [NOT] DETERMINISTIC |
    COMMENT function_comment |
    [CONTAINS SQL | READS SQL DATA] }

Parameters

• OR REPLACE

  지정되어 있으면, 이름과 시그니처가 같은 함수(parameters 및 매개변수 형식 수)를 대체합니다. 기존 함수를 다른 서명으로 바꿀 수 없습니다. 이는 주로 함수 본문과 반환 유형을 update 하는 데 유용합니다. 이 매개 변수는 IF NOT EXISTS와 함께 지정할 수 없습니다.

• TEMPORARY

  만들어지는 함수의 범위입니다. TEMPORARY를 지정하면 만들어진 함수가 유효하고 현재 세션에서 볼 수 있습니다. catalog에 영구 항목이 만들어지지 않습니다.

• IF NOT EXISTS

  지정하면 존재하지 않는 경우에만 함수를 만듭니다. 지정된 함수가 시스템에 이미 있는 경우 함수 만들기가 성공합니다(오류가 throw되지 않음). 이 매개 변수는 OR REPLACE와 함께 지정할 수 없습니다.

• function_name

  함수의 이름입니다. 영구 함수의 경우 필요에 따라 함수 이름을 schema 이름으로 qualify 수 있습니다. 이름이 정규화되지 않으면 영구 함수는 현재 schema에서 만들어집니다.

• function_parameter

  함수의 매개 변수를 지정합니다.

  • parameter_name

    매개 변수 이름은 함수 내에서 고유해야 합니다.

  • data_type

    지원되는 모든 데이터 형식입니다. Python의 data_type 경우 이 언어 매핑에 따라 Python 데이터 형식으로 캐스팅됩니다.

  • DEFAULT default_expression

    적용 대상: Databricks SQL Databricks Runtime 10.4 LTS 이상

    함수 호출에서 매개 변수에 인수를 할당하지 않을 때 사용할 선택적 기본값입니다. default_expression은 으로 data_type할 수 있어야 합니다. 식은 다른 매개 변수를 참조하거나 하위 쿼리를 포함해서는 안됩니다.

    하나의 매개 변수에 기본값을 지정하면, 뒤에 오는 모든 parameters도 기본값을 가져야 합니다.

    DEFAULT 은 지원됩니다 LANGUAGE SQL .

  • COMMENT 주석

    매개 변수에 대한 선택적 설명입니다. comment는 STRING 리터럴이어야 합니다.

• RETURNS data_type

  스칼라 함수의 반환 데이터 형식입니다. Python UDF의 경우 values 반환은 data_type에 지정된 데이터 형식과 정확히 일치해야 합니다. 그렇지 않으면 예기치 않은 형식 변환을 방지하기 위해 함수가 실패합니다.

  SQL UDF의 경우 이 절은 선택 사항입니다. 데이터 형식이 제공되지 않으면 함수 본문에서 파생됩니다.

• RETURNS TABLE [ (column_spec [,...]) ]

  이 절은 함수를 table 함수로 표시합니다. 필요에 따라 table 함수 결과의 서명도 지정합니다. column_spec 지정되지 않으면 SQL UDF의 본문에서 파생됩니다.

  RETURNS TABLE 은 지원됩니다 LANGUAGE SQL .

  • column_name

    column 이름은 서명 내에서 고유해야 합니다.

  • data_type

    지원되는 모든 데이터 형식입니다.

  • COMMENT column_comment

    column에 대한 옵션 설명입니다. comment는 STRING 리터럴이어야 합니다.

• RETURN { expression | query }

  함수의 본문입니다. 스칼라 함수의 경우 쿼리 또는 식이 될 수 있습니다. table 함수의 경우 쿼리일 뿐입니다. 식은 다음을 포함할 수 없습니다.

  • 집계 함수
  • Window 함수
  • 순위 함수
  • explode와 같은 행 생성 함수

  함수의 본문 내에서 정규화되지 않은 이름으로 또는 매개 변수를 함수 이름으로 한정하여 매개 변수를 참조할 수 있습니다.

• AS dollar_quoted_definition

  dollar_quoted_definition 는 두 개의 일치로 묶인 Python 함수 body 입니다 $[tag]$body$[tag]$. tag 은 빈 문자열일 수 있습니다.

  예:

  $$
    return “Hello world”
  $$
  
  $py$
    return "Hello World"
  $py$
  

• characteristic

  모든 특성 조항은 선택 사항입니다. 순서에 관계없이 원하는 수를 지정할 수 있지만 각 절은 한 번만 지정할 수 있습니다.

  • LANGUAGE SQL 또는 LANGUAGE PYTHON

    함수 구현의 언어입니다.

  • [NOT] DETERMINISTIC

    함수가 결정적인지 여부입니다. 함수는 지정된 인수 set 대해 하나의 결과만 반환할 때 결정적입니다. 함수를 본문이 아닌 경우로 DETERMINISTIC 표시하고 그 반대로 표시할 수 있습니다. 그 이유는 상수 접기 또는 쿼리 캐싱과 같은 쿼리 최적화를 권장하거나 권장하지 않을 수 있습니다. ths 옵션을 지정하지 않으면 함수 본문에서 파생됩니다.

  • COMMENT function_comment

    함수에 대한 설명입니다. function_comment는 문자열 리터럴이어야 합니다.

  • CONTAINS SQL 또는 READS SQL DATA

    함수가 table 또는 뷰에서 직접 또는 간접적으로 데이터를 읽는지 여부입니다. 함수가 SQL 데이터를 읽을 때는 지정할 CONTAINS SQL수 없습니다. 두 절 중 하나를 지정하지 않으면 속성이 함수 본문에서 파생됩니다.

Python UDF에서 지원되는 라이브러리

종속성을 사용하려면 함수 본문 내에서 사용합니다 import <package> . 예를 들어 다음을 참조하세요.

CREATE FUNCTION […]
AS $$
   import json
   [... (rest of function definition)]
$$

종속성은 표준 Python 라이브러리 및 다음 라이브러리로 제한됩니다.

예제

• SQL 스칼라 함수 만들기 및 사용
• DEFAULT를 사용하는 함수 만들기 및 사용
• SQL table 함수 만들기
• SQL 함수 바꾸기
• SQL 함수 설명
• Python 함수 만들기

SQL 스칼라 함수 만들기 및 사용

> CREATE VIEW t(c1, c2) AS VALUES (0, 1), (1, 2);

-- Create a temporary function with no parameter.
> CREATE TEMPORARY FUNCTION hello() RETURNS STRING
    RETURN 'Hello World!';

> SELECT hello();
  Hello World!

-- Create a permanent function with parameters.
> CREATE FUNCTION area(x DOUBLE, y DOUBLE) RETURNS DOUBLE RETURN x * y;

-- Use a SQL function in the SELECT clause of a query.
> SELECT area(c1, c2) AS area FROM t;
 0.0
 2.0

-- Use a SQL function in the WHERE clause of a query.
> SELECT * FROM t WHERE area(c1, c2) > 0;
 1  2

-- Compose SQL functions.
> CREATE FUNCTION square(x DOUBLE) RETURNS DOUBLE RETURN area(x, x);

> SELECT c1, square(c1) AS square FROM t;
  0  0.0
  1  1.0

-- Create a non-deterministic function
> CREATE FUNCTION roll_dice()
    RETURNS INT
    NOT DETERMINISTIC
    CONTAINS SQL
    COMMENT 'Roll a single 6 sided die'
    RETURN (rand() * 6)::INT + 1;
-- Roll a single 6-sided die
> SELECT roll_dice();
 3

DEFAULT를 사용하는 함수 만들기 및 사용

-- Extend the function to support variable number of sides and dice.
-- Use defaults to support a variable number of arguments
> DROP FUNCTION roll_dice;
> CREATE FUNCTION roll_dice(num_dice  INT DEFAULT 1 COMMENT 'number of dice to roll (Default: 1)',
                            num_sides INT DEFAULT 6 COMMENT 'number of sides per die (Default: 6)')
    RETURNS INT
    NOT DETERMINISTIC
    CONTAINS SQL
    COMMENT 'Roll a number of n-sided dice'
    RETURN aggregate(sequence(1, roll_dice.num_dice, 1),
                     0,
                     (acc, x) -> (rand() * roll_dice.num_sides)::int,
                     acc -> acc + roll_dice.num_dice);

-- Roll a single 6-sided die still works
> SELECT roll_dice();
 3

-- Roll 3 6-sided dice
> SELECT roll_dice(3);
 15

-- Roll 3 10-sided dice
> SELECT roll_dice(3, 10)
 21

-- Roll 3 10-sided dice using named parameter invocation
> SELECT roll_dice(10 => num_sides, num_dice => 3)
 17

-- Create a SQL function with a scalar subquery.
> CREATE VIEW scores(player, score) AS VALUES (0, 1), (0, 2), (1, 2), (1, 5);

> CREATE FUNCTION avg_score(p INT) RETURNS FLOAT
    COMMENT 'get an average score of the player'
    RETURN SELECT AVG(score) FROM scores WHERE player = p;

> SELECT c1, avg_score(c1) FROM t;
 0  1.5
 1  3.5

SQL table 함수 만들기

-- Produce all weekdays between two dates
> CREATE FUNCTION weekdays(start DATE, end DATE)
    RETURNS TABLE(day_of_week STRING, day DATE)
    RETURN SELECT extract(DAYOFWEEK_ISO FROM day), day
             FROM (SELECT sequence(weekdays.start, weekdays.end)) AS T(days)
                  LATERAL VIEW explode(days) AS day
             WHERE extract(DAYOFWEEK_ISO FROM day) BETWEEN 1 AND 5;

-- Return all weekdays
> SELECT weekdays.day_of_week, day
    FROM weekdays(DATE'2022-01-01', DATE'2022-01-14');
  1     2022-01-03
  2     2022-01-04
  3     2022-01-05
  4     2022-01-06
  5     2022-01-07
  1     2022-01-10
  2     2022-01-11
  3     2022-01-12
  4     2022-01-13
  5     2022-01-14

-- Return weekdays for date ranges originating from a LATERAL correlation
> SELECT weekdays.*
    FROM VALUES (DATE'2020-01-01'),
                (DATE'2021-01-01'),
                (DATE'2022-01-01') AS starts(start),
         LATERAL weekdays(start, start + INTERVAL '7' DAYS);
  3     2020-01-01
  4     2020-01-02
  5     2020-01-03
  1     2020-01-06
  2     2020-01-07
  3     2020-01-08
  5     2021-01-01
  1     2021-01-04
  2     2021-01-05
  3     2021-01-06
  4     2021-01-07
  5     2021-01-08
  1     2022-01-03
  2     2022-01-04
  3     2022-01-05
  4     2022-01-06
  5     2022-01-07

SQL 함수 바꾸기

-- Replace a SQL scalar function.
> CREATE OR REPLACE FUNCTION square(x DOUBLE) RETURNS DOUBLE RETURN x * x;

-- Replace a SQL table function.
> CREATE OR REPLACE FUNCTION getemps(deptno INT)
    RETURNS TABLE (name STRING)
    RETURN SELECT name FROM employee e WHERE e.deptno = getemps.deptno;

-- Describe a SQL table function.
> DESCRIBE FUNCTION getemps;
 Function: default.getemps
 Type:     TABLE
 Input:    deptno INT
 Returns:  id   INT
           name STRING

SQL 함수 설명

> DESCRIBE FUNCTION hello;
 Function: hello
 Type:     SCALAR
 Input:    ()
 Returns:  STRING

> DESCRIBE FUNCTION area;
 Function: default.area
 Type:     SCALAR
 Input:    x DOUBLE
           y DOUBLE
 Returns:  DOUBLE

> DESCRIBE FUNCTION roll_dice;
 Function: default.roll_dice
 Type:     SCALAR
 Input:    num_dice  INT
           num_sides INT
 Returns:  INT

> DESCRIBE FUNCTION EXTENDED roll_dice;
 Function:      default.roll_dice
 Type:          SCALAR
 Input:         num_dice  INT DEFAULT 1 'number of dice to roll (Default: 1)'
                num_sides INT DEFAULT 6 'number of sides per dice (Default: 6)'
 Returns:       INT
 Comment:       Roll a number of m-sided dice
 Deterministic: false
 Data Access:   CONTAINS SQL
 Configs:       ...
 Owner:         the.house@always.wins
 Create Time:   Sat Feb 12 09:29:02 PST 2022
 Body:          aggregate(sequence(1, roll_dice.num_dice, 1),
                      0,
                      (acc, x) -> (rand() * roll_dice.num_sides)::int,
                      acc -> acc + roll_dice.num_dice)

Python 함수 만들기

—- Hello World-like functionality using Python UDFs
> CREATE FUNCTION main.default.greet(s STRING)
  RETURNS STRING
  LANGUAGE PYTHON
  AS $$
    def greet(name):
      return "Hello " + name + "!"

    return greet(s) if s else None
  $$

—- Can import functions from std library and environment
> CREATE FUNCTION main.default.isleapyear(year INT)
  RETURNS BOOLEAN
  LANGUAGE PYTHON
  AS $$
    import calendar
    return calendar.isleap(year) if year else None
  $$

—- Must return the correct type. Otherwise will fail at runtime.
> CREATE FUNCTION main.default.a_number()
  RETURNS INTEGER
  LANGUAGE PYTHON
  AS $$
    # does not work: return "10"
    # does not work: return 3.14
    return 10
  $$

—- Deal with exceptions.
> CREATE FUNCTION main.default.custom_divide(n1 INT, n2 INT)
  RETURNS FLOAT
  LANGUAGE PYTHON
  AS $$
    try:
      return n1/n2
    except ZeroDivisionException:
    # in case of 0, we can return NULL.
    return None
  $$

관련된 문서

• DROP FUNCTION
• SHOW FUNCTIONS
• DESCRIBE FUNCTION
• GRANT
• REVOKE