CREATE FUNCTION (SQL et Python)

  • Article

S’applique à :case marquée oui Databricks SQL coche marquée oui 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 à :coche marquée oui Databricks SQL case marquée oui Databricks Runtime 13.3 LTS et versions ultérieures

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

Les fonctions définies par l’utilisateur Python nécessitent Unity Catalog sur des entrepôts SQL serverless ou pro, ou un cluster Unity Catalog partagé ou mono-utilisateur.

S’applique à :coche marquée oui Databricks SQL case marquée oui Databricks Runtime 14.1 et versions ultérieures

Pour appeler des fonctions définies par l’utilisateur SQL et Python, vous pouvez utiliser non seulement un appel de paramètre positionnel, mais aussi un appel de paramètre nommé.

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

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

Paramètres

  • OR REPLACE

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

  • IF NOT EXISTS

    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.

  • function_parameter

    Spécifie un paramètre de la fonction.

    • parameter_name

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

    • data_type

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

    • DEFAULT default_expression

      S’applique à :coche marquée oui Databricks SQL oui coché Databricks Runtime 10.4 LTS et versions ultérieures

      Valeur par défaut facultative à utiliser lorsqu’un appel de fonction n’affecte pas d’argument au paramètre. default_expression doit être castable vers 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.

  • RETURNS data_type

    Type de données renvoyé de la fonction scalaire. Pour les fonctions définies par l’utilisateur Python, 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 SQL UDF, 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, il 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 l’UDF SQL.

    RETURNS TABLE est pris en charge pour LANGUAGE SQL uniquement.

    • column_name

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

    • data_type

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

    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 :

    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 entre deux correspondances $[tag]$body$[tag]$. tag peut être une chaîne vide.

    Exemples :

    $$
  return “Hello world”
$$

$py$
  return "Hello World"
$py$

  • characteristic

    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.

    • LANGAGE SQL ou LANGAGE PYTHON

      Langue de l’implémentation de la fonction.

    • [NOT] DETERMINISTIC

      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. La raison peut en être d’encourager ou de 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.

    • COMMENT function_comment

      Commentaire pour la fonction. function_comment doit être un littéral de type STRING.

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

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

Pour utiliser des dépendances, utilisez import <package> dans le corps de la fonction. Par exemple, consultez ce qui suit :

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

Les dépendances sont limitées à la bibliothèque Python standard et aux bibliothèques suivantes :

Package 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
user-agents 2.2.0
chiffrement 38.0.4

Exemples

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

Notes

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