CREATE FUNCTION(SQL, Python, Scala a Java)

Platí pro: Databricks SQL Databricks Runtime

Vytvoří skalární nebo tabulkovou funkci SQL, která přebírá sadu argumentů a vrací skalární hodnotu nebo sadu řádků.

Platí pro: Databricks SQL Databricks Runtime 13.3 LTS a vyšší

Vytvoří skalární funkci Pythonu, která přebírá sadu argumentů a vrací skalární hodnotu.

Uživatelem definované funkce Pythonu vyžadují katalog Unity na bezserverových SQL skladech nebo na profesionálních SQL skladech, nebo na výpočetním prostředku s povoleným katalogem Unity.

Platí pro: Databricks SQL Databricks Runtime 14.1 a vyšší verze

Kromě použití pozičních parametrů můžete také použít SQL a Python UDF pomocí pojmenovaného použití parametrů.

platí pro: Databricks SQL Databricks Runtime 16.2 a novější

ENVIRONMENT Pomocí klauzule zadejte prostředí Pythonu pro funkci deklarovanou pomocí LANGUAGE PYTHON. Slouží k instalaci vlastních závislostí a nastavení verze prostředí.

Platí pro: Databricks SQL Databricks Runtime 18.2 and above

Vytvoří skalární funkci Scala nebo Java, která spouští kompilovanou logiku JVM zabalenou jako JAR. Scala a Java definované uživatelem vyžadují katalog Unity. Odkazujete na zkompilovaný kód s HANDLER klauzulí a poskytnete JAR prostřednictvím ENVIRONMENT klíče klauzule java_dependencies . Úplný pracovní postup, včetně postupu sestavení SOUBORU JAR a registrace funkce, najdete v tématu Scala a Java uživatelem definované funkce (UDF) v katalogu Unity.

Syntaxe

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 } | HANDLER handler_name }

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 | SCALA | JAVA } |
    [NOT] DETERMINISTIC |
    COMMENT function_comment |
    [CONTAINS SQL | READS SQL DATA] |
    DEFAULT COLLATION default_collation_name } |
    environment }

environment
  ENVIRONMENT ( { environment_key = environment_value } [, ...] )

Parametry

• NEBO NAHRAĎTE

  Pokud je zadáno, nahradí se funkce se stejným názvem a podpisem (počet parametrů a typů parametrů). Existující funkci nelze nahradit jiným podpisem nebo postupem. To je užitečné hlavně k aktualizaci těla funkce a návratového typu funkce. Tento parametr nelze zadat pomocí parametru IF NOT EXISTS.

• DOČASNÝ

  Rozsah vytvářené funkce. Při zadání TEMPORARY je vytvořená funkce platná a viditelná v aktuální relaci. Trvalá položka není v katalogu vytvořena.

• POKUD NEEXISTUJE

  Pokud je zadáno, vytvoří funkci pouze v případě, že neexistuje. Vytvoření funkce proběhne úspěšně (nenastane žádná chyba), pokud zadaná funkce již v systému existuje. Tento parametr nelze zadat pomocí parametru OR REPLACE.

• function_name

  Název funkce. U trvalé funkce můžete volitelně kvalifikovat název funkce názvem schématu. Pokud název není kvalifikovaný, vytvoří se v aktuálním schématu trvalá funkce.

  Název funkce musí být jedinečný pro všechny rutiny (procedury a funkce) ve schématu.

• function_parameter

  Určuje parametr funkce.

  • parameter_name

    Název parametru musí být v rámci funkce jedinečný.

  • datový_typ

    Libovolný podporovaný datový typ. V případě Pythonu data_type se přetypuje na datový typ Pythonu podle tohoto mapování jazyka.

    Pro STRINGdata_type, je výchozí kolace funkce default_collation_name.

  • VÝCHOZÍ default_expression

    Platí pro: Databricks SQL Databricks Runtime 10.4 LTS a novější

    Volitelné výchozí nastavení, které se má použít, když vyvolání funkce nepřiřazuje argument parametru. default_expression musí být přetypovatelný nadata_type. Výraz nesmí odkazovat na jiný parametr ani obsahovat poddotaz.

    Pokud zadáte výchozí hodnotu pro jeden parametr, musí mít všechny následující parametry také výchozí hodnotu.

    DEFAULT je podporován pouze pro LANGUAGE SQL .

  • KOMENTÁŘ

    Volitelný popis parametru. comment musí být STRING literál.

• VRÁTÍ data_type

  Návratový datový typ skalární funkce. Pro uživatelem definované funkce v Pythonu musí návratové hodnoty přesně odpovídat datovému typu zadanému v data_type. Jinak, aby bylo zabráněno neočekávaným převodům typů, funkce selže.

  Pro UDF SQL je tato klauzule nepovinná. Datový typ bude odvozen z těla funkce, pokud není zadán.

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

  Tato klauzule označuje funkci jako funkci tabulky. Volitelně také určuje podpis výsledku funkce tabulky. Pokud není zadána žádná specifikace sloupce, bude odvozena z těla uživatelem definované funkce (UDF) v SQL.

  RETURNS TABLEje podporován pro LANGUAGE SQLLANGUAGE PYTHON funkce definované uživatelem (Databricks Runtime 18.1 a novější).

  • column_name

    Název sloupce musí být v rámci podpisu jedinečný.

  • datový_typ

    Libovolný podporovaný datový typ.

  • KOMENTÁŘ sloupec_komentář

    Volitelný popis sloupce. comment musí být STRING literál.

• RETURN { expression | query }

  Tělo funkce. Pro skalární funkci může být dotaz nebo výraz. Pro funkci tabulky může být pouze dotazem. Výraz nemůže obsahovat:

  • Agregační funkce
  • Okenní funkce
  • Funkce řazení
  • Funkce generující řádky jako explode

  V těle funkce můžete odkazovat na parametr podle jeho názvu, nebo kvalifikováním parametru pomocí názvu funkce.

• DOLLAR_QUOTED_DEFINITION AS

  dollar_quoted_definition je funkce body Pythonu uzavřená dvěma shodnými $[tag]$body$[tag]$. tag může být prázdný řetězec.

  Příklady:

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

• handler_name obslužné rutiny

  Platí pro: Databricks SQL Databricks Runtime 18.2 and above

  Plně kvalifikovaná cesta k metodě, která implementuje funkci LANGUAGE SCALA jako LANGUAGE JAVASTRING literál. Použijte formát 'package.Object.method' pro Scala a 'package.ClassName.method' pro Java. Zkompilovaný kód musí být poskytován jako SOUBOR JAR prostřednictvím java_dependencies klíče ENVIRONMENT klauzule.

• charakteristický

  Všechny charakteristické klauzule jsou volitelné. Můžete zadat libovolný počet v libovolném pořadí, ale každou klauzuli můžete zadat pouze jednou.

  • JAZYK SQL, JAZYK PYTHON, JAZYK SCALA nebo JAZYK JAVA

    Jazyk implementace funkce. LANGUAGE SCALA a LANGUAGE JAVA vyžadovat Unity Catalog a HANDLER klauzuli, která odkazuje na zkompilovaný kód zadaný prostřednictvím ENVIRONMENT klauzule. Viz Scala a Java uživatelem definované funkce (UDF) v katalogu Unity.

  • [NE] DETERMINISTICKÝ

    Zda je funkce deterministická. Funkce je deterministická, pokud vrátí pouze jeden výsledek pro danou sadu argumentů. Můžete označit funkci jako DETERMINISTIC, pokud její tělo není, a naopak. Důvodem může být podpora nebo odrazování optimalizace dotazů, jako je konstantní posouvání nebo ukládání dotazů do mezipaměti. Pokud tuto možnost nezadáte, je odvozena z těla funkce.

  • KOMENTÁŘ FUNKCE

    Komentář pro funkci. function_comment musí být řetězcový literál.

  • OBSAHUJE SQL nebo READS SQL DATA

    Ať už funkce čte data přímo nebo nepřímo z tabulky nebo zobrazení. Při čtení dat SQL funkce nelze zadat CONTAINS SQL. Pokud nezadáte ani jednu klauzuli, vlastnost je odvozena z těla funkce.

  • výchozí kolace default_collation_name

    Platí pro: Databricks SQL Databricks Runtime 17.0 a výše

    Definuje výchozí kolaci, která se má použít pro:

    • STRING parametry a RETURNS datový typ a RETURNS TABLE pole funkce.
    • DEFAULT výraz.
    • Tělo funkce SQL.

    Pokud není zadáno, výchozí kolace je odvozena ze schématu, ve kterém je funkce vytvořena.

• životní prostředí

  Určuje prostředí pro funkci deklarovanou pomocí LANGUAGE PYTHON, LANGUAGE SCALAnebo LANGUAGE JAVA. Klauzule ENVIRONMENT není podporována pro funkce SQL.

  • závislosti

    Pole JSON s řetězci určujícími požadované Python balíčky nebo soubory kol pro LANGUAGE PYTHON funkci. Klíč dependencies nerozlišuje malá a velká písmena. Podporované formáty:

    • Balíček PyPI s volitelnou verzí, například: "simplejson==3.19.3"
    • Absolutní cesta k souboru kolečka uloženému ve svazku katalogu Unity, například: "/Volumes/my_catalog/my_schema/my_volume/packages/custom_package-1.0.0.whl"
    • Adresa URL HTTPS k souboru typu wheel ve vnějším úložišti, například: "https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=..."

  • java_dependencies

    Platí pro: Databricks SQL Databricks Runtime 18.2 and above

    Pole JSON řetězců určujících soubory JAR, které obsahují zkompilovaný kód pro danou LANGUAGE SCALA funkci.LANGUAGE JAVA Každá položka je absolutní cesta k souboru JAR uloženému ve svazku katalogu Unity, například: "/Volumes/my_catalog/my_schema/my_volume/packages/my-udf-assembly-0.1.0.jar". Zabalte kód jako fat JAR, který obsahuje všechny závislosti třetích stran používané uživatelem definované uživatelem.

  • environment_version

    Řetězec určující verzi prostředí.

    • Pro LANGUAGE PYTHON, pouze hodnota None je aktuálně podporována, která používá výchozí Python prostředí. Pokud tento parametr vynecháte, použije se výchozí prostředí.
    • Pro LANGUAGE SCALA a LANGUAGE JAVA, hodnota musí být '4' nebo vyšší. Verze prostředí 4 specifikuje Scala 2.13.16 a JDK 17. Viz verze bezserverového prostředí .

Podporované knihovny v Pythonu UDF (uživatelsky definovaných funkcích)

Pokud chcete použít jakékoli závislosti, použijte import <package> v těle funkce. Podívejte se například na následující:

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

Ve výchozím nastavení jsou závislosti omezené na standardní knihovnu Pythonu a následující knihovny:

Balíček	Verze
bělidlo	4.0.0
chardet	4.0.0
nástroj pro normalizaci znakové sady	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 (knihovna pro numerické výpočty v Pythonu)	1.20.3
balení	21.3
Pandy	1.3.4
bábovka	0.5.2
protobuf	4.21.5
pyarrow	7.0.0
pyparsing	3.0.9
python-dateutil	2.8.2
knihovna pytz pro zpracování časových zón v Pythonu	2021.3
scikit-learn	0.24.2”
SciPy	1.7.1”
setuptools (nástroj pro vytváření a distribuci Python projektů)	65.2.0
Šest	1.16.0
threadpoolctl	3.1.0
webová kódování	0.5.1
uživatelské agenty	2.2.0
kryptografie	38.0.4

Vlastní závislosti v uživatelsky definovaných funkcích v Pythonu

Pokud chcete použít další závislosti nad rámec standardní knihovny a podporovaných integrovaných balíčků, zadejte je v ENVIRONMENT klauzuli.

Příklady

• Vytvoření a použití skalární funkce SQL
• Vytvoření a použití funkce, která používá defaulty
• Vytvoření funkce tabulky SQL
• Nahrazení funkce SQL
• Popis funkce SQL
• Vytváření funkcí Pythonu
• Definování vlastních závislostí ve funkcích Pythonu
• Vytvoření funkcí Scala a Java

Vytvoření a použití skalární funkce 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

Vytvoření a použití funkce, která používá defaulty

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

Vytvoření funkce tabulky 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

Nahrazení funkce 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

Poznámka:

Existující funkci nelze nahradit jiným podpisem.

Popis funkce 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)

Vytváření funkcí Pythonu

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

Definování vlastních závislostí ve funkcích Pythonu

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

Vytvoření funkcí Scala a Java

Následující příklady registrují uživatelem definovanou uživatelem zkompilovaného kódu JVM zabaleného jako SOUBOR JAR ve svazku katalogu Unity. Klauzule HANDLER odkazuje na metodu v souboru JAR a java_dependencies klíč odkazuje na soubor JAR. Úplný pracovní postup, včetně postupu sestavení SOUBORU JAR, najdete v tématu Scala a Java uživatelem definované funkce (UDF) v katalogu Unity.

-- Register a Scala UDF from a JAR in a Unity Catalog volume.
> CREATE OR REPLACE FUNCTION my_catalog.my_schema.add_one_scala(x INT)
    RETURNS INT
    LANGUAGE SCALA
    DETERMINISTIC
    ENVIRONMENT (
      java_dependencies = '["/Volumes/my_catalog/my_schema/udf_jars/my-udf-assembly-0.1.0.jar"]',
      environment_version = '4'
    )
    HANDLER 'com.example.ScalaUDF.addOne';

-- Register a Java UDF from a JAR in a Unity Catalog volume.
> CREATE OR REPLACE FUNCTION my_catalog.my_schema.add_one_java(x INT)
    RETURNS INT
    LANGUAGE JAVA
    DETERMINISTIC
    ENVIRONMENT (
      java_dependencies = '["/Volumes/my_catalog/my_schema/udf_jars/my-udf-1.0.jar"]',
      environment_version = '4'
    )
    HANDLER 'com.example.JavaUDF.addOne';

-- Use the functions in a query.
> SELECT my_catalog.my_schema.add_one_scala(5);
 6
> SELECT my_catalog.my_schema.add_one_java(5);
 6

Související články

• Scala a Java uživatelem definované funkce (UDF) v katalogu Unity
• DROP FUNCTION
• SHOW FUNCTIONS
• DESCRIBE FUNCTION
• GRANT
• REVOKE