CREATE FUNCTION(SQL 및 Python)
적용 대상:
Databricks SQL Databricks 런타임
인수 집합을 사용하고 스칼라 값 또는 행 집합을 반환하는 SQL 스칼라 또는 테이블 함수를 만듭니다.
적용 대상: Databricks SQL Databricks Runtime 13.3 LTS 이상
인수 집합을 사용하고 스칼라 값을 반환하는 Python 스칼라 함수를 만듭니다.
Python UDF에는 서버리스 또는 프로 SQL 웨어하우스 또는 공유 또는 단일 사용자 Unity 카탈로그 클러스터에 Unity 카탈로그가 필요합니다.
적용 대상: 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] }
매개 변수
OR REPLACE
지정된 경우 이름과 서명이 같은 함수(매개 변수 및 매개 변수 형식의 수)가 대체됩니다. 기존 함수를 다른 서명으로 바꿀 수 없습니다. 이는 주로 함수 본문과 함수의 반환 형식을 업데이트하는 데 유용합니다. 이 매개 변수는
IF NOT EXISTS와 함께 지정할 수 없습니다.TEMPORARY
만들어지는 함수의 범위입니다.
TEMPORARY를 지정하면 만들어진 함수가 유효하고 현재 세션에서 볼 수 있습니다. 카탈로그에 영구적인 항목이 작성되지 않습니다.IF NOT EXISTS
지정하면 존재하지 않는 경우에만 함수를 만듭니다. 지정된 함수가 시스템에 이미 있는 경우 함수 만들기가 성공합니다(오류가 throw되지 않음). 이 매개 변수는
OR REPLACE와 함께 지정할 수 없습니다.-
함수의 이름입니다. 영구 함수의 경우 선택적으로 스키마 이름으로 함수 이름을 한정할 수 있습니다. 이름이 규정되지 않으면 현재 스키마에서 permanent 함수가 작성됩니다.
function_parameter
함수의 매개 변수를 지정합니다.
-
매개 변수 이름은 함수 내에서 고유해야 합니다.
-
지원되는 모든 데이터 형식입니다. Python의
data_type경우 이 언어 매핑에 따라 Python 데이터 형식으로 캐스팅됩니다. DEFAULT default_expression
적용 대상:
Databricks SQL Databricks Runtime 10.4 LTS 이상함수 호출에서 매개 변수에 인수를 할당하지 않을 때 사용할 선택적 기본값입니다.
default_expression은data_type으로 캐스트할 수 있어야 합니다. 식은 다른 매개 변수를 참조하거나 하위 쿼리를 포함해서는 안됩니다.하나의 매개 변수에 대해 기본값을 지정하는 경우 다음 매개 변수 모두에도 기본값이 있어야 합니다.
DEFAULT은 지원됩니다LANGUAGE SQL.COMMENT 주석
매개 변수에 대한 선택적 설명입니다.
comment은 리터럴이어야STRING합니다.
-
RETURNS data_type
스칼라 함수의 반환 데이터 형식입니다. Python UDF의 경우 반환 값은 에 지정된
data_type데이터 형식과 정확히 일치해야 합니다. 그렇지 않으면 예기치 않은 형식 변환을 방지하기 위해 함수가 실패합니다.SQL UDF의 경우 이 절은 선택 사항입니다. 데이터 형식이 제공되지 않으면 함수 본문에서 파생됩니다.
RETURNS TABLE [ (column_spec [,...] ) ]
이 절은 함수를 테이블 함수로 표시합니다. 필요에 따라 테이블 함수 결과의 서명도 지정합니다. column_spec 지정되지 않으면 SQL UDF의 본문에서 파생됩니다.
RETURNS TABLE은 지원됩니다LANGUAGE SQL.-
열 이름은 서명 내에서 고유해야 합니다.
-
지원되는 모든 데이터 형식입니다.
COMMENT column_comment
열에 대한 선택적 설명입니다.
comment은 리터럴이어야STRING합니다.
-
RETURN { expression | query }
함수의 본문입니다. 스칼라 함수의 경우 쿼리 또는 식이 될 수 있습니다. 테이블 함수의 경우 쿼리만 될 수 있습니다. 식은 다음을 포함할 수 없습니다.
함수의 본문 내에서 정규화되지 않은 이름으로 또는 매개 변수를 함수 이름으로 한정하여 매개 변수를 참조할 수 있습니다.
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
함수가 결정적인지 여부입니다. 함수는 주어진 인수 세트에 대해 하나의 결과만 반환할 때 결정적입니다. 함수를 본문이 아닌 경우로
DETERMINISTIC표시하고 그 반대로 표시할 수 있습니다. 그 이유는 상수 접기 또는 쿼리 캐싱과 같은 쿼리 최적화를 권장하거나 권장하지 않을 수 있습니다. ths 옵션을 지정하지 않으면 함수 본문에서 파생됩니다.COMMENT function_comment
함수에 대한 설명입니다.
function_comment는 문자열 리터럴이어야 합니다.CONTAINS SQL 또는 READS SQL DATA
함수가 테이블이나 뷰에서 직접 또는 간접적으로 데이터를 읽는지 여부입니다. 함수가 SQL 데이터를 읽을 때는 지정할
CONTAINS SQL수 없습니다. 두 절 중 하나를 지정하지 않으면 속성이 함수 본문에서 파생됩니다.
Python UDF에서 지원되는 라이브러리
종속성을 사용하려면 함수 본문 내에서 사용합니다 import <package> . 예를 들어 다음을 참조하세요.
CREATE FUNCTION […]
AS $$
import json
[... (rest of function definition)]
$$
종속성은 표준 Python 라이브러리 및 다음 라이브러리로 제한됩니다.
| Package(패키지) | 버전 |
|---|---|
| bleach | 4.0.0 |
| chardet | 4.0.0 |
| charset-normalizer | 2.0.4 |
| defusedxml | 0.7.1 |
| googleapis-common-protos | 1.56.4 |
| grpcio | 1.47.0 |
| grpcio-상태 | 1.47.0 |
| jmespath | 0.10.0 |
| joblib | 1.1.0 |
| numpy | 1.20.3 |
| 패키징 | 21.3 |
| pandas | 1.3.4 |
| patsy | 0.5.2 |
| protobuf | 4.21.5 |
| pyarrow | 7.0.0 |
| pyparsing | 3.0.9 |
| python-dateutil | 2.8.2 |
| pytz | 2021.3 |
| scikit-learn | 0.24.2" |
| scipy | 1.7.1" |
| setuptools | 65.2.0 |
| six | 1.16.0 |
| threadpoolctl | 3.1.0 |
| webencodings | 0.5.1 |
| user-agents | 2.2.0 |
| 암호화 | 38.0.4 |
예제
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 테이블 함수 만들기
-- 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
$$