적용 대상:
Databricks SQL Databricks Runtime
인수 집합을 사용하고 스칼라 값 또는 행 집합을 반환하는 SQL 스칼라 또는 테이블 함수를 만듭니다.
적용 대상: Databricks SQL Databricks Runtime 13.3 LTS 이상
인수 집합을 사용하고 스칼라 값을 반환하는 Python 스칼라 함수를 만듭니다.
Python UDF에는 서버리스 또는 프로 SQL 웨어하우스 또는 Unity 카탈로그 사용 컴퓨팅 리소스에 Unity 카탈로그가 필요합니다.
적용 대상: Databricks SQL Databricks Runtime 14.1 이상
위치 매개 변수 호출 외에도 명명된 매개 변수 호출을 사용하여 SQL 및 Python UDF를 호출할 수도 있습니다.
적용 대상: Databricks SQL Databricks Runtime 16.2 이상
절을 ENVIRONMENT 사용하여 .로 선언된 LANGUAGE PYTHON함수에 대한 Python 환경을 지정합니다. 사용자 지정 종속성을 설치하고 환경 버전을 설정하는 데 사용됩니다.
구문
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] |
DEFAULT COLLATION default_collation_name } |
environment }
environment
ENVIRONMENT ( { environment_key = environment_value } [, ...] )
매개 변수
또는 교체
지정한 경우 이름과 서명이 같은 함수(매개 변수 및 매개 변수 형식의 수)가 바뀝니다. 기존 함수를 다른 서명이나 프로시저로 바꿀 수 없습니다. 함수 본문과 함수의 반환 형식을 업데이트하는 데 주로 유용합니다. 이 매개 변수는
IF NOT EXISTS와 함께 지정할 수 없습니다.임시
만들어지는 함수의 범위입니다.
TEMPORARY를 지정하면 만들어진 함수가 유효하고 현재 세션에서 볼 수 있습니다. 카탈로그에 영구 항목이 만들어지지 않습니다.존재하지 않으면
지정하면 존재하지 않는 경우에만 함수를 만듭니다. 지정된 함수가 시스템에 이미 있는 경우 함수 만들기가 성공합니다(오류가 throw되지 않음). 이 매개 변수는
OR REPLACE와 함께 지정할 수 없습니다.-
함수의 이름입니다. 영구 함수의 경우 필요에 따라 스키마 이름으로 함수 이름을 한정할 수 있습니다. 이름이 정규화되지 않은 경우 영구 함수는 현재 스키마에 만들어집니다.
함수 이름은 스키마의 모든 루틴(프로시저 및 함수)에 대해 고유해야 합니다.
function_parameter
함수의 매개 변수를 지정합니다.
-
매개 변수 이름은 함수 내에서 고유해야 합니다.
-
지원되는 모든 데이터 형식입니다. Python의
data_type경우 이 언어 매핑에 따라 Python 데이터 형식으로 캐스팅됩니다.의
STRINGdata_type경우 기본 데이터 정렬은 함수default_collation_name입니다. -
적용 대상:
Databricks SQL Databricks Runtime 10.4 LTS 이상함수 호출에서 매개 변수에 인수를 할당하지 않을 때 사용할 선택적 기본값입니다.
default_expression은 형으로 변환 가능해야 합니다data_type. 식은 다른 매개 변수를 참조하거나 하위 쿼리를 포함해서는 안됩니다.하나의 매개 변수에 대해 기본값을 지정하면 다음 매개 변수 모두 기본값도 있어야 합니다.
DEFAULT는LANGUAGE SQL에서만 지원됩니다. COMMENT COMMENT
매개 변수에 대한 선택적 설명입니다.
comment는STRING리터럴이어야 합니다.
-
반환 data_type
스칼라 함수의 반환 데이터 형식입니다. Python UDF의 경우 반환 값은
data_type지정된 데이터 형식과 정확히 일치해야 합니다. 그렇지 않으면 예기치 않은 형식 변환을 방지하기 위해 함수가 실패합니다.SQL UDF의 경우 이 절은 선택 사항입니다. 데이터 형식이 제공되지 않으면 함수 본문에서 파생됩니다.
RETURNS TABLE [ (column_spec [,...]) ]
이 절은 함수를 테이블 함수로 표시합니다. 필요에 따라 테이블 함수 결과의 서명도 지정합니다. column_spec 지정되지 않으면 SQL UDF의 본문에서 파생됩니다.
RETURNS TABLE는LANGUAGE SQL에서만 지원됩니다.-
열 이름은 서명 내에서 고유해야 합니다.
-
지원되는 모든 데이터 형식입니다.
주석 column_comment
열에 대한 선택적 설명입니다.
comment는STRING리터럴이어야 합니다.
-
-
함수의 본문입니다. 스칼라 함수의 경우 쿼리 또는 식이 될 수 있습니다. 테이블 함수의 경우 쿼리일 수 있습니다. 식은 다음을 포함할 수 없습니다.
함수의 본문 내에서 정규화되지 않은 이름으로 또는 매개 변수를 함수 이름으로 한정하여 매개 변수를 참조할 수 있습니다.
AS 달러_인용_정의
dollar_quoted_definition는 두 개의 일치하는body로 둘러싸인 Python 함수$[tag]$body$[tag]$입니다.tag은 빈 문자열일 수 있습니다.예:
$$ return “Hello world” $$ $py$ return "Hello World" $py$특징
모든 특성 조항은 선택 사항입니다. 순서에 관계없이 원하는 수를 지정할 수 있지만 각 절은 한 번만 지정할 수 있습니다.
LANGUAGE SQL 또는 LANGUAGE PYTHON
함수 구현의 언어입니다.
비결정론적
함수가 결정적인지 여부입니다. 함수는 지정된 인수 집합에 대해 하나의 결과만 반환할 때 결정적입니다. 당신은 함수의 본문이 아닌 경우에
DETERMINISTIC로 표시하거나 그 반대로 표시할 수 있습니다. 그 이유는 아마도 상수 접기나 쿼리 캐싱과 같은 쿼리 최적화를 장려하거나 억제하기 위해서일 수 있습니다. 이 옵션을 지정하지 않으면 함수 본문에서 파생됩니다.주석 function_comment
함수에 대한 설명입니다.
function_comment는 문자열 리터럴이어야 합니다.SQL 포함 또는 SQL 데이터 읽기
함수가 테이블 또는 뷰에서 직접 또는 간접적으로 데이터를 읽는지 여부입니다. 함수가 SQL 데이터를 읽을 때는 지정할
CONTAINS SQL수 없습니다. 절 중 하나를 지정하지 않으면 속성이 함수 본문에서 파생됩니다.기본 데이터 정렬 default_collation_name
적용 대상:
Databricks SQL Databricks Runtime 17.0 이상다음 용도로 사용할 기본 데이터 정렬을 정의합니다.
-
STRING매개 변수 및RETURNS데이터 형식 및RETURNS TABLE함수의 필드입니다. -
DEFAULT표현. - SQL 함수의 본문입니다.
지정하지 않으면 기본 데이터 정렬은 함수가 만들어지는 스키마에서 파생됩니다.
-
환경
로 선언된
LANGUAGE PYTHON함수에 대한 Python 환경을 지정합니다. 이ENVIRONMENT절은 SQL 함수에 대해 지원되지 않습니다.종속성
함수에 필요한 Python 패키지 또는 휠 파일을 지정하는 문자열의 JSON 배열입니다.
dependencies키는 대소문자를 구분하지 않습니다. 지원되는 형식:- 선택적 버전이 있는 PyPI 패키지(예: "simplejson==3.19.3"
- Unity 카탈로그 볼륨에 저장된 휠 파일의 절대 경로(예: "/Volumes/my_catalog/my_schema/my_volume/packages/custom_package-1.0.0.whl")
- 외부 스토리지의 휠 파일에 대한 HTTPS URL(예: "https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=..."
환경_버전
Python 환경 버전을 지정하는 문자열입니다. 기본 Python 환경을 사용하는 데 사용합니다
None. 생략하면 기본 환경이 사용됩니다.- 현재는 값
None만 지원됩니다.
- 현재는 값
Python UDF에서 지원되는 라이브러리
종속성을 사용하려면 함수 본문 내에서 사용합니다 import <package> . 예를 들어 다음을 참조하세요.
CREATE FUNCTION […]
AS $$
import json
[... (rest of function definition)]
$$
기본적으로 종속성은 표준 Python 라이브러리 및 다음 라이브러리로 제한됩니다.
| 패키지 | 버전 |
|---|---|
| 표백제 | 4.0.0 |
| 챠데트 | 4.0.0 |
| 문자셋 정규화기 | 2.0.4 |
| defusedxml (디퓨즈드 XML) | 0.7.1 |
| googleapis-common-protos | 1.56.4 |
| grpcio (Python용 gRPC 패키지) | 1.47.0 |
| grpcio-status | 1.47.0 |
| jmespath | 0.10.0 |
| joblib (잡리브) | 1.1.0 |
| numpy (파이썬의 수치 계산용 라이브러리) | 1.20.3 |
| 패키징 | 21.3 |
| 팬더 | 1.3.4 |
| 희생양 | 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 |
| 6 | 1.16.0 |
| threadpoolctl | 3.1.0 |
| 웹인코딩스 | 0.5.1 |
| 유저 에이전트 | 2.2.0 |
| 암호화 | 38.0.4 |
Python UDF의 사용자 지정 종속성
표준 라이브러리 및 지원되는 기본 제공 패키지 이외의 추가 종속성을 사용하려면 절에 ENVIRONMENT 해당 종속성을 지정합니다.
예제
- SQL 스칼라 함수 만들기 및 사용
- DEFAULT를 사용하는 함수 만들기 및 사용
- SQL 테이블 함수 만들기
- SQL 함수 바꾸기
- SQL 함수 설명
- Python 함수 만들기
- 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 테이블 함수 만들기
-- 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
$$
Python 함수에서 사용자 지정 종속성 정의
-- Create a Python function with additional dependencies using the ENVIRONMENT clause.
> CREATE FUNCTION main.default.dump_json(data STRING)
RETURNS STRING
LANGUAGE PYTHON
ENVIRONMENT (
dependencies = '["simplejson==3.19.3", "/Volumes/my_catalog/my_schema/my_volume/packages/custom_package-1.0.0.whl", "https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=2043167927&Signature=abcd"]',
environment_version = 'None'
)
AS $$
import simplejson as json
import custom_package
return json.dumps(custom_package.process(data))
$$;
-- Use the Python function in a query.
> SELECT dump_json('{"foo": "bar"}');