CREATE FUNCTION(SQL, Python, Scala et Java)

S’applique à : Databricks SQL Databricks Runtime

Crée une fonction SQL scalaire ou de table qui accepte un ensemble d’arguments et renvoie une valeur scalaire ou un ensemble de lignes.

S’applique à : Databricks SQL Databricks Runtime 13.3 LTS et version ultérieure

Crée une fonction scalaire Python qui prend un ensemble d'arguments et renvoie une valeur scalaire.

Les UDF Python nécessitent un catalogue Unity sur des entrepôts SQL sans serveur ou professionnels, ou une ressource de calcul activée par le catalogue Unity.

S’applique à : Databricks SQL Databricks Runtime 14.1 et version ultérieure

Vous pouvez appeler des fonctions SQL et Python définies par l'utilisateur non seulement à l'aide d'un appel de paramètre positionnel, mais aussi d'un appel de paramètre nommé.

S’applique à : Databricks SQL Databricks Runtime 16.2 et versions ultérieures

Utilisez la clause pour spécifier l’environnement ENVIRONMENT Python pour une fonction déclarée avec LANGUAGE PYTHON. Permet d’installer des dépendances personnalisées et de définir la version de l’environnement.

S’applique à : Databricks SQL Databricks Runtime 18.2 et versions ultérieures

Crée une fonction Scala ou Java scalaire qui exécute une logique JVM compilée empaquetée en tant que fichier JAR. Scala et Java fonctions définies par l’utilisateur nécessitent le catalogue Unity. Vous référencez le code compilé avec la HANDLER clause et fournissez le fichier JAR par le biais de la clé de java_dependencies la ENVIRONMENT clause. Pour le flux de travail complet, notamment la création du fichier JAR et l’inscription de la fonction, consultez Scala et Java fonctions définies par l’utilisateur (UDF) dans le catalogue 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 } [, ...] )

Parameters

• OU REMPLACER

  S’il est spécifié, la fonction ayant le même nom et la même signature (nombre de paramètres et types de paramètres) est remplacée. Vous ne pouvez pas remplacer une fonction existante par une autre signature ou une procédure. Cela est principalement utile pour mettre à jour le corps de la fonction et le type de retour de la fonction. Vous ne pouvez pas spécifier ce paramètre avec IF NOT EXISTS.

• TEMPORARY

  Étendue de la fonction en cours de création. Lorsque vous spécifiez TEMPORARY, la fonction créée est valide et visible dans la session active. Aucune entrée persistante n’est effectuée dans le catalogue.

• SI N'EXISTE PAS

  S’il est spécifié, crée la fonction uniquement lorsqu’elle n’existe pas. La création de la fonction réussit (aucune erreur n’est signalée) si la fonction spécifiée existe déjà dans le système. Vous ne pouvez pas spécifier ce paramètre avec OR REPLACE.

• function_name

  Nom de la fonction. Pour une fonction permanente, vous pouvez qualifier le nom de la fonction avec un nom de schéma si vous le souhaitez. Si le nom n’est pas qualifié, la fonction permanente est créée dans le schéma actuel.

  Le nom de la fonction doit être unique pour toutes les routines (procédures et fonctions) dans le schéma.

• function_parameter

  Spécifie un paramètre de la fonction.

  • parameter_name

    Le nom du paramètre doit être unique dans la fonction.

  • type_de_données

    Tout type de données pris en charge. Pour Python, data_type est converti en type de données Python en fonction de ce mappage de langage.

    Pour un STRINGdata_type, le classement par défaut est la fonction default_collation_name.

  • DEFAULT_EXPRESSION DEFAULT

    S’applique à : Databricks SQL Databricks Runtime 10.4 LTS et version ultérieure

    Valeur par défaut facultative à utiliser lorsqu’un appel de fonction n’affecte pas d’argument au paramètre. default_expression doit être convertible en une valeur de type data_type. L’expression ne doit pas faire référence à un autre paramètre ou contenir une sous-requête.

    Lorsque vous spécifiez une valeur par défaut pour un paramètre, tous les paramètres suivants doivent également avoir une valeur par défaut.

    DEFAULT est pris en charge pour LANGUAGE SQL uniquement.

  • COMMENT Commentaire

    Description optionnelle du paramètre. comment doit être un littéral STRING.

• RENVOIE data_type

  Type de données renvoyé de la fonction scalaire. Pour les fonctions Python définies par l'utilisateur, les valeurs renvoyées doivent correspondre exactement au type de données spécifié dans data_type. Sinon, la fonction ne parvient pas à empêcher les conversions de types inattendues.

  Pour les fonctions SQL définies par l'utilisateur, cette clause est facultative. Le type de données sera dérivé du corps de la fonction s'il n'est pas fourni.

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

  Cette clause marque la fonction en tant que fonction de table. Éventuellement, elle spécifie également la signature du résultat de la fonction de table. Si aucune column_spec n'est spécifiée, elle sera dérivée du corps de la fonction SQL définie par l'utilisateur.

  RETURNS TABLEest pris en charge pour et pour LANGUAGE SQLLANGUAGE PYTHON les UDF polymorphes (Databricks Runtime 18.1 et versions ultérieures).

  • nom_de_colonne

    Le nom de colonne doit être unique dans la signature.

  • type_de_données

    Tout type de données pris en charge.

  • COMMENTAIRE Column_comment

    Description optionnelle de la colonne. comment doit être un littéral STRING.

• RETURN { expression | requête }

  Corps de la fonction. Pour une fonction scalaire, il peut s’agir d’une requête ou d’une expression. Pour une fonction de table, il ne peut s’agir que d’une requête. L’expression ne peut pas contenir :

  • Fonctions d'agrégation
  • Fonctions de fenêtre
  • Fonctions de classement
  • Fonctions de production de lignes telles que explode

  Dans le corps de la fonction, vous pouvez faire référence au paramètre par son nom non qualifié ou en qualifiant le paramètre avec le nom de la fonction.

• AS dollar_quoted_definition

  dollar_quoted_definition est la fonction Python body placée entre deux $[tag]$body$[tag]$ correspondants. tag peut être une chaîne vide.

  Exemples :

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

• Gestionnaire handler_name

  S’applique à : Databricks SQL Databricks Runtime 18.2 et versions ultérieures

  Chemin complet de la méthode qui implémente une ou LANGUAGE JAVA une LANGUAGE SCALA fonction, en tant que STRING littéral. Utilisez le format 'package.Object.method' scalaire et 'package.ClassName.method' pour Java. Le code compilé doit être fourni en tant que fichier JAR par le biais de la java_dependencies clé de la ENVIRONMENT clause.

• caractéristique

  Toutes les clauses relatives aux caractéristiques sont optionnelles. Vous pouvez en spécifier un nombre quelconque dans n’importe quel ordre, mais vous ne pouvez spécifier chaque clause qu’une seule fois.

  • LANGUAGE SQL, LANGUAGE PYTHON, LANGUAGE SCALA ou LANGUAGE JAVA

    Langage de l'implémentation de la fonction. LANGUAGE SCALA et LANGUAGE JAVA exiger unity Catalog et une HANDLER clause qui fait référence au code compilé fourni par le biais de la ENVIRONMENT clause. Consultez les fonctions définies par l’utilisateur en Scala et Java (UDF) dans Unity Catalog.

  • [NOT] DÉTERMINISTE

    Indique si la fonction est déterministe. Une fonction est déterministe lorsqu’elle renvoie un seul résultat pour un ensemble donné d’arguments. Vous pouvez marquer une fonction comme DETERMINISTIC lorsque son corps ne l'est pas et vice versa. Vous pouvez le faire par exemple pour encourager ou décourager les optimisations des requêtes telles que le pliage constant ou la mise en cache des requêtes. Si vous ne spécifiez pas cette option, elle est dérivée du corps de la fonction.

  • COMMENTAIRE function_comment

    Commentaire pour la fonction. function_comment doit être un littéral de chaîne.

  • CONTAINS SQL ou READS SQL DATA

    Indique si une fonction lit les données directement ou indirectement à partir d’une table ou d’une vue. Lorsque la fonction lit des données SQL, vous ne pouvez pas spécifier CONTAINS SQL. Si vous ne spécifiez aucune des deux clauses, la propriété est dérivée du corps de la fonction.

  • COLLATION PAR DÉFAUT default_collation_name

    S’applique à : Databricks SQL Databricks Runtime 17.0 et versions ultérieures

    Définit le classement par défaut à utiliser pour :

    • STRING paramètres et type de données et RETURNSRETURNS TABLE champs de la fonction.
    • Expression DEFAULT.
    • Le corps de la fonction SQL.

    S’il n’est pas spécifié, le classement par défaut est dérivé du schéma dans lequel la fonction est créée.

• environnement

  Spécifie l’environnement d’une fonction déclarée avec LANGUAGE PYTHON, LANGUAGE SCALAou LANGUAGE JAVA. La ENVIRONMENT clause n’est pas prise en charge pour les fonctions SQL.

  • Dépendances

    Tableau JSON de chaînes spécifiant les Python packages ou fichiers roue requis pour une LANGUAGE PYTHON fonction. La clé dependencies ne respecte pas la casse. Formats pris en charge :

    • Package PyPI avec version facultative, par exemple : « simplejson==3.19.3 »
    • Chemin d'accès absolu à un fichier wheel stocké dans un volume Unity Catalog, par exemple : « /Volumes/my_catalog/my_schema/my_volume/packages/custom_package-1.0.0.whl »
    • URL HTTPS vers un fichier wheel dans le stockage externe, par exemple : « https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=… »

  • java_dependencies

    S’applique à : Databricks SQL Databricks Runtime 18.2 et versions ultérieures

    Tableau JSON de chaînes spécifiant les fichiers JAR qui contiennent le code compilé pour une LANGUAGE SCALA ou LANGUAGE JAVA une fonction. Chaque entrée est un chemin absolu vers un fichier JAR stocké dans un volume catalogue Unity, par exemple : « /Volumes/my_catalog/my_schema/my_volume/packages/my-udf-assembly-0.1.0.jar ». Empaqueter le code en tant que fichier JAR gras qui inclut toutes les dépendances tierces utilisées par la fonction UDF.

  • version_d'environnement

    Chaîne spécifiant la version de l’environnement.

    • Pour LANGUAGE PYTHON, seule la valeur None est actuellement prise en charge, qui utilise l’environnement de Python par défaut. S’il est omis, l’environnement par défaut est utilisé.
    • Pour LANGUAGE SCALA et LANGUAGE JAVA, la valeur doit être '4' ou supérieure. L’environnement version 4 spécifie Scala 2.13.16 et JDK 17. Consultez les Versions de l’environnement serverless.

Bibliothèques prises en charge dans les fonctions Python définies par l'utilisateur

Vous pouvez utiliser des dépendances à l'aide de import <package> dans le corps de la fonction. Par exemple, consultez ce qui suit :

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

Par défaut, les dépendances sont limitées à la bibliothèque Python standard et aux bibliothèques suivantes :

Paquet	Version
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-status	1.47.0
jmespath	0.10.0
joblib	1.1.0
numpy	1.20.3
empaquetage	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
agents-utilisateurs	2.2.0
chiffrement	38.0.4

Dépendances personnalisées dans les fonctions Python définies par l'utilisateur

Pour utiliser des dépendances supplémentaires au-delà de la bibliothèque standard et des packages intégrés pris en charge, spécifiez-les dans la ENVIRONMENT clause.

Exemples

• Créer et utiliser une fonction scalaire SQL
• Créer et utiliser une fonction qui utilise les valeurs par défaut
• Créer une fonction de table SQL
• Remplacer une fonction SQL
• Décrire une fonction SQL
• Créer des fonctions Python
• Définir des dépendances personnalisées dans les fonctions Python
• Créer des fonctions Scala et Java

Créer et utiliser une fonction scalaire 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

Créer et utiliser une fonction qui utilise les valeurs par défaut

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

Créer une fonction de table 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

Remplacer une fonction 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

Note

Vous ne pouvez pas remplacer une fonction existante par une autre signature.

Décrire une fonction 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)

Créer des fonctions 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
  $$

Définir des dépendances personnalisées dans les fonctions 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"}');

Créer des fonctions Scala et Java

Les exemples suivants inscrivent une fonction UDF à partir du code JVM compilé empaqueté en tant que fichier JAR dans un volume de catalogue Unity. La HANDLER clause fait référence à la méthode dans le fichier JAR, et la java_dependencies clé pointe vers le fichier JAR. Pour le flux de travail complet, notamment comment générer le fichier JAR, consultez Scala et Java fonctions définies par l’utilisateur (UDF) dans le catalogue 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

Articles connexes

• Scala et Java fonctions définies par l’utilisateur (UDF) dans le catalogue Unity
• DROP FUNCTION
• SHOW FUNCTIONS
• DESCRIBE FUNCTION
• GRANT
• REVOKE