CREATE FUNCTION (SQL ve Python)

Şunlar için geçerlidir: Databricks SQL Databricks Runtime

Bir dizi argüman alan ve skaler bir değer veya satır kümesi döndüren bir SQL skaler fonksiyonu veya tablo fonksiyonu oluşturur.

Şunlar için geçerlidir: Databricks SQL Databricks Runtime 13.3 LTS ve üzeri

Bir dizi bağımsız değişken alarak geriye bir skaler değer döndüren bir Python skaler işlevi oluşturur.

Python UDF'leri sunucusuz veya profesyonel SQL ambarlarında Unity Kataloğu'nu veya Unity Kataloğu özellikli bir işlem kaynağını gerektirir.

Şunlar için geçerlidir: Databricks SQL Databricks Runtime 14.1 ve üzeri

Konumsal parametre çağırmaya ek olarak, adlandırılmış parametre çağırmayı kullanarak SQL ve Python UDF'yi de çağırabilirsiniz.

Şunun için geçerlidir: Databricks SQL Databricks Runtime 16.2 ve üzeri

ENVIRONMENT yan tümcesini, LANGUAGE PYTHON ile bildirilen bir işlev için Python ortamını belirtmek amacıyla kullanın. Özel bağımlılıkları yüklemek ve ortam sürümünü ayarlamak için kullanılır.

Sözdizimi

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 } [, ...] )

Parametreler

• VEYA DEĞİŞTİR

  Belirtilirse, aynı ad ve imzaya (parametre sayısı ve parametre türü) sahip işlev değiştirilir. Var olan bir işlevi farklı bir imza veya yordamla değiştiremezsiniz. Bu temel olarak işlev gövdesini ve işlevin dönüş türünü güncelleştirmek için yararlıdır. Bu parametreyi ile IF NOT EXISTSbelirtemezsiniz.

• GEÇİCİ

  Oluşturulan işlevin kapsamı. belirttiğinizde TEMPORARY, oluşturulan işlev geçerli olur ve geçerli oturumda görünür. Katalogda kalıcı girdi yapılmaz.

• MEVCUT DEĞİLSE

  Belirtilirse, işlevi yalnızca mevcut olmadığında oluşturur. Belirtilen işlev sistemde zaten varsa, işlevin oluşturulması başarılı olur (hata oluşturulmaz). Bu parametreyi ile OR REPLACEbelirtemezsiniz.

• function_name

  İşlev için bir ad. Kalıcı bir işlev için, isteğe bağlı olarak işlev adını bir şema adıyla niteleyebilirsiniz. Ad nitelenmiş değilse, geçerli şemada kalıcı işlev oluşturulur.

  İşlev adı, şemadaki tüm yordamlar (yordamlar ve işlevler) için benzersiz olmalıdır.

• function_parameter

  İşlevin bir parametresini belirtir.

  • parameter_name

    Parametre adı işlevi içinde benzersiz olmalıdır.

  • veri_türü

    Desteklenen herhangi bir veri türü. Python için, data_type bu dil eşlemesine göre bir Python veri türüne yayınlanır.

    STRING data_type için varsayılan harmanlama default_collation_name işlevi olarak belirlenmiştir.

  • DEFAULT default_expression

    Şunlar için geçerlidir: Databricks SQL Databricks Runtime 10.4 LTS ve üzeri

    bir işlev çağrısı parametreye bağımsız değişken atamadığında kullanılacak isteğe bağlı varsayılan değerdir. default_expression, 'ye dönüştürülebilir olmalıdır. İfade başka bir parametreye başvurmamalı veya bir alt sorgu içermemelidir.

    Bir parametre için varsayılan değer belirttiğinizde, aşağıdaki tüm parametrelerin de varsayılanı olmalıdır.

    DEFAULT yalnızca LANGUAGE SQL için desteklenir.

  • YORUM

    Parametresinin isteğe bağlı açıklaması. comment bir STRING sabit olmalıdır.

• GERİ DÖNER data_type

  Skaler işlevin dönüş veri türü. Python UDF'leri için, dönüş değerleri data_type'de belirtildiği gibi veri türüyle tam olarak eşleşmelidir. Aksi takdirde, beklenmeyen tür dönüştürmelerini önlemek için işlev başarısız olur.

  SQL UDF için bu yan tümce isteğe bağlıdır. Veri türü sağlanmadıysa işlev gövdesinden türetilir.

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

  Bu cümle, işlevi tablo işlevi olarak belirtir. İsteğe bağlı olarak tablo işlevinin sonucunun imzasını da belirtir. column_spec belirtilmezse, SQL UDF'nin gövdesinden türetilir.

  RETURNS TABLE yalnızca LANGUAGE SQL için desteklenir.

  • sütun_adı

    Sütun adı imza içinde benzersiz olmalıdır.

  • veri_türü

    Desteklenen herhangi bir veri türü.

  • YORUM sütun_yorumu

    Sütunun isteğe bağlı açıklaması. comment bir STRING sabit olmalıdır.

• RETURN { ifade | sorgusu }

  İşlevin gövdesi. Skaler bir işlev için sorgu veya ifade olabilir. Tablo işlevi için yalnızca bir sorgu olabilir. İfade aşağıdakileri içeremez:

  • Toplama işlevleri
  • Pencere işlevleri
  • Derecelendirme işlevleri
  • explode gibi satır üreten işlevler

  İşlevin gövdesinde parametreye nitelenmemiş adıyla veya parametreyi işlev adıyla niteleyerek başvurabilirsiniz.

• AS dollar_quoted_definition

  dollar_quoted_definition, iki eşleşen bodyile çevrelenmiş Python işlevidir$[tag]$body$[tag]$. tag boş bir dize olabilir.

  Örnekler:

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

• özellik

  Tüm karakteristik cümleler isteğe bağlı olabilir. Herhangi bir sırada istediğiniz sayıda belirtebilirsiniz, ancak her yan tümceyi yalnızca bir kez belirtebilirsiniz.

  • LANGUAGE SQL veya LANGUAGE PYTHON

    İşlev uygulamasının dili.

  • [DEĞİL] DETERMİNİSTİK

    İşlevin belirleyici olup olmadığı. İşlev, belirli bir bağımsız değişken kümesi için yalnızca bir sonuç döndürdüğünde belirleyicidir. Bir işlevin gövdesi mevcut değilken onu DETERMINISTIC olarak işaretleyebilir veya tam tersi durumda da aynı işlemi yapabilirsiniz. Bunun bir nedeni, sürekli katlama veya sorgu önbelleğe alma gibi sorgu iyileştirmelerini teşvik etmek veya caydırmak olabilir. Bu seçeneği belirtmezseniz, işlevin gövdesinden türetilir.

  • YORUM fonksiyon_yorumu

    İşlev için bir açıklama. function_comment dize sabiti olmalıdır.

  • SQL İÇERİR veya SQL VERİSİ OKUR

    İşlevin verileri tablo veya görünümden doğrudan veya dolaylı olarak okuması. İşlev SQL verilerini okuduğunda belirtemezsiniz CONTAINS SQL. İki yan tümceyi belirtmezseniz, özellik işlev gövdesinden türetilir.

  • VARSAYıLAN HARMANLAMA default_collation_name

    Şunun için geçerlidir: Databricks Runtime 17.0 ve üzeri

    Aşağıdakiler için kullanılacak varsayılan harmanlamayı tanımlar:

    • STRING parametresi, RETURNS veri türü ve RETURNS TABLE işlevin alanları.
    • DEFAULT ifade.
    • SQL işlevinin gövdesi.

    Belirtilmezse, varsayılan sıralama düzeni UTF8_BINARYolarak ayarlanır.

• çevre

  ile LANGUAGE PYTHONbildirilen bir işlev için Python ortamını belirtir. ENVIRONMENT yan tümcesi SQL işlevleri için desteklenmez.

  • Bağımlılık

    İşlev için gerekli Python paketlerini veya tekerlek dosyalarını belirten bir JSON dize dizisi. Anahtar dependencies büyük/küçük harfe duyarlı değildir. Desteklenen biçimler:

    • İsteğe bağlı sürüme sahip PyPI paketi, örneğin: "simplejson==3.19.3"
    • Unity Kataloğu biriminde depolanan tekerlek dosyasının mutlak yolu, örneğin: "/Volumes/my_catalog/my_schema/my_volume/packages/custom_package-1.0.0.whl"
    • Dış depolamadaki bir tekerlek dosyasının HTTPS URL'si, örneğin: "https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=..."

  • environment_version

    Python ortam sürümünü belirten bir Dize. Varsayılan Python ortamını kullanmak için kullanın None . Atlanırsa, varsayılan ortam kullanılır.

    • Şu anda yalnızca değer None desteklenmektedir.

Python UDF'lerinde Desteklenen Kitaplıklar

Bağımlılıkları kullanmak için işlev gövdesi içinde kullanın import <package> . Örneğin, aşağıdakilere bakın:

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

Varsayılan olarak, bağımlılıklar standart Python kitaplığı ve aşağıdaki kitaplıklarla sınırlıdır:

Paket	Sürüm
çamaşır suyu	4.0.0
chardet	4.0.0
karakter seti normalleştirici	2.0.4
defusedxml modülü	0.7.1
googleapis-common-protos kütüphaneleri	1.56.4
grpcio	1.47.0
grpcio-status	1.47.0
jmespath	0.10.0
joblib kütüphanesi	1.1.0
numpy kütüphanesi	1.20.3
Ambalaj	21.3
Pandalar	1.3.4
Patsy	0.5.2
Protobuf	4.21.5
serçe	7.0.0
pyparsing (bir Python kütüphanesi)	3.0.9
python-dateutil (Python tarih ve saat işleme kütüphanesi)	2.8.2
pytz kütüphanesi (Python zaman dilimi hesaplamaları için kullanılan)	2021.3
scikit-learn	0.24.2”
scipy (Python için bir bilimsel hesaplama kütüphanesi)	1.7.1”
kurulum araçları	65.2.0
Altı	1.16.0
threadpoolctl	3.1.0
web kodlamaları	0.5.1
kullanıcı aracıları	2.2.0
şifreleme	38.0.4

Python UDF'lerinde özel bağımlılıklar

Standart kitaplığın ve desteklenen yerleşik paketlerin ötesinde ek bağımlılıklar kullanmak için bunları yan tümcesinde ENVIRONMENT belirtin.

Örnekler

• SQL skaler işlevi oluşturma ve kullanma
• Varsayılanları kullanan bir işlev oluşturma ve kullanma
• SQL tablo işlevi oluşturma
• SQL işlevini değiştirme
• SQL işlevini açıklama
• Python işlevleri oluşturma
• Python işlevlerinde özel bağımlılıkları tanımlama

SQL skaler işlevi oluşturma ve kullanma

> 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;
 1.0
 1.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

Varsayılan değerler kullanan bir işlev oluşturma ve kullanma

-- 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 tablo işlevi oluşturma

-- 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 işlevini değiştirme

-- 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

Not

Mevcut bir işlevi farklı bir imzayla değiştiremezsiniz.

SQL işlevini açıklama

> 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 işlevleri oluşturma

—- 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 işlevlerinde özel bağımlılıkları tanımlama

-- 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"}');

İlgili makaleler

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