CREATE FUNCTION (SQL dan Python)
Berlaku untuk: Databricks SQL Databricks Runtime
Membuat fungsi skalar atau tabel SQL yang mengambil satu set argumen dan mengembalikan nilai skalar atau sekumpulan baris.
Berlaku untuk: Databricks SQL Databricks Runtime 13.3 LTS ke atas
Membuat fungsi skalar Python yang mengambil sekumpulan argumen dan mengembalikan nilai skalar.
UDF Python memerlukan Katalog Unity pada gudang SQL tanpa server atau pro, atau kluster Unity Catalog pengguna bersama atau tunggal.
Berlaku untuk: Databricks SQL Databricks Runtime 14.1 ke atas
Selain pemanggilan parameter posisional, Anda juga dapat memanggil SQL dan Python UDF menggunakan pemanggilan parameter bernama.
Sintaks
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] }
Parameter
OR REPLACE
Jika ditentukan, fungsi dengan nama dan tanda tangan yang sama (jumlah parameter dan jenis parameter) diganti. Anda tidak dapat mengganti fungsi yang ada dengan tanda tangan yang berbeda. Ini terutama berguna untuk memperbarui fungsi isi dan jenis fungsi kembali. Anda tidak dapat menentukan parameter ini dengan
IF NOT EXISTS
.SEMENTARA
Cakupan fungsi yang sedang dibuat. Saat Anda menentukan
TEMPORARY
, fungsi yang dibuat valid dan terlihat di sesi saat ini. Tidak ada entri persisten yang dibuat dalam katalog.IF NOT EXISTS
Jika ditentukan, buat fungsi hanya jika tidak ada. Pembuatan fungsi berhasil (tidak ada kesalahan yang dilemparkan) jika fungsi yang ditentukan sudah ada dalam sistem. Anda tidak dapat menentukan parameter ini dengan
OR REPLACE
.-
Nama untuk fungsi. Untuk fungsi permanen, Anda dapat secara opsional memenuhi syarat nama fungsi dengan nama skema. Jika nama tidak memenuhi syarat, fungsi permanen dibuat dalam skema saat ini.
function_parameter
Menentukan parameter fungsi.
-
Nama parameter harus unik dalam fungsi.
-
Jenis data apa pun yang didukung. Untuk Python,
data_type
dilemparkan ke jenis data Python sesuai dengan pemetaan bahasa ini. DEFAULT default_expression
Berlaku untuk: Databricks SQL Databricks Runtime 10.4 LTS ke atas
Default opsional yang akan digunakan ketika pemanggilan fungsi tidak menetapkan argumen ke parameter.
default_expression
harus dapat ditransmisikan kedata_type
. Ekspresi tidak boleh mereferensikan parameter lain atau berisi kueri bertumpuk.Saat Anda menentukan default untuk satu parameter, semua parameter berikut juga harus memiliki default.
DEFAULT
hanya didukungLANGUAGE SQL
.COMMENT komentar
Deskripsi opsional parameter.
comment
harus harfiahSTRING
.
-
RETURNS data_type
Jenis data pengembalian fungsi skalar. Untuk UDF Python, nilai pengembalian harus sama persis dengan jenis data seperti yang ditentukan dalam
data_type
. Jika tidak, untuk mencegah konversi jenis yang tidak tertandingi, fungsi akan gagal.Untuk SQL UDF, klausa ini bersifat opsional. Jenis data akan berasal dari isi fungsi jika tidak disediakan.
MENGEMBALIKAN TABEL [ (column_spec [,...] ) ]
Klausa ini menandai fungsi sebagai fungsi tabel. Secara opsional juga menentukan tanda tangan hasil fungsi tabel. Jika tidak ada column_spec yang ditentukan, itu akan berasal dari isi UDF SQL.
RETURNS TABLE
hanya didukungLANGUAGE SQL
.-
Nama kolom harus unik dalam tanda tangan.
-
Jenis data apa pun yang didukung.
KOMENTAR column_comment
Deskripsi opsional kolom.
comment
harus harfiahSTRING
.
-
-
Isi fungsi. Untuk fungsi skalar, itu bisa berupa kueri atau ekspresi. Untuk fungsi tabel, itu hanya bisa menjadi kueri. Ekspresi tidak dapat berisi:
- Fungsi agregat
- Fungsi jendela
- Fungsi pemberian peringkat
- Fungsi pembuatan baris seperti meledak
Di dalam isi fungsi Anda dapat merujuk ke parameter dengan namanya yang tidak memenuhi syarat atau dengan memenuhi syarat parameter dengan nama fungsi.
AS dollar_quoted_definition
dollar_quoted_definition
adalah fungsibody
Python yang diapit oleh dua pencocokan$[tag]$body$[tag]$
.tag
dapat berupa string kosong.Contoh:
$$ return “Hello world” $$ $py$ return "Hello World" $py$
karakteristik
Semua klausul karakteristik bersifat opsional. Anda dapat menentukan sejumlah dari mereka dalam urutan apa pun, tetapi Anda dapat menentukan setiap klausul hanya sekali.
LANGUAGE SQL atau LANGUAGE PYTHON
Bahasa implementasi fungsi.
[NOT] DETERMINISTIC
Baik itu fungsinya deterministik. Fungsi bersifat deterministik ketika hanya mengembalikan satu hasil untuk serangkaian argumen tertentu. Anda dapat menandai fungsi seperti
DETERMINISTIC
ketika tubuhnya tidak dan sebaliknya. Alasan untuk ini mungkin untuk mendorong atau mencegah pengoptimalan kueri seperti pelipatan konstan atau penembolokan kueri. Jika Anda tidak menentukan opsi ths, opsi tersebut berasal dari isi fungsi.COMMENT function_comment
Komentar untuk fungsi tersebut.
function_comment
harus berupa String harfiah.CONTAINS SQL atau READS SQL DATA
Apakah suatu fungsi membaca data secara langsung atau tidak langsung dari tabel atau tampilan. Saat fungsi membaca data SQL, Anda tidak dapat menentukan
CONTAINS SQL
. Jika Anda tidak menentukan salah satu klausa, properti berasal dari badan fungsi.
Pustaka yang Didukung di UDF Python
Untuk menggunakan dependensi apa pun, gunakan import <package>
dalam isi fungsi. Misalnya, lihat yang berikut ini:
CREATE FUNCTION […]
AS $$
import json
[... (rest of function definition)]
$$
Dependensi terbatas pada pustaka Python standar dan pustaka berikut:
Paket | Versi |
---|---|
pemutih | 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-status | 1.47.0 |
jmespath | 0.10.0 |
joblib | 1.1.0 |
numpy | 1.20.3 |
packaging | 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 | 0.5.1 |
agen pengguna | 2.2.0 |
cryptography | 38.0.4 |
Contoh
- Membuat dan menggunakan fungsi skalar SQL
- Membuat dan menggunakan fungsi yang menggunakan DEFAULT
- Membuat fungsi Tabel SQL
- Mengganti fungsi SQL
- Menjelaskan fungsi SQL
- Membuat fungsi Python
Membuat dan menggunakan fungsi skalar 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
Membuat dan menggunakan fungsi yang menggunakan 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
Membuat fungsi Tabel 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
Mengganti fungsi 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
Catatan
Anda tidak dapat mengganti fungsi yang ada dengan tanda tangan yang berbeda.
Menjelaskan fungsi 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)
Membuat fungsi 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
$$
Artikel terkait
Saran dan Komentar
https://aka.ms/ContentUserFeedback.
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat:Kirim dan lihat umpan balik untuk