CREATE FUNCTION (SQL et Python)

S’applique à : coché Databricks SQL coché 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 à : coché Databricks SQL coché 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 à : coché Databricks SQL coché 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 à : case cochée oui 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.

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] |
    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 default_expression
  
  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 comment
  
  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 TABLE est pris en charge pour LANGUAGE SQL uniquement.
- 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$
```
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 ou LANGUAGE PYTHON
  
  Langage de l'implémentation de la fonction.
- [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 Python d’une fonction déclarée avec LANGUAGE PYTHON. La ENVIRONMENT clause n’est pas prise en charge pour les fonctions SQL.
- Dépendances
  
  Tableau JSON de chaînes spécifiant les packages Python ou les fichiers de roue requis pour la 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=… »
- version_d'environnement
  
  Chaîne spécifiant la version de l’environnement Python. Permet d’utiliser None l’environnement Python par défaut. S’il est omis, l’environnement par défaut est utilisé.
  - Seule la valeur None est actuellement prise en charge.

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

Rétroaction

Cette page vous a-t-elle été utile ?

Last updated on 2025-12-05