Catégories :: DDL pour les fonctions définies par l’utilisateur, les fonctions externes et les procédures stockées

CREATE FUNCTION¶

Crée une nouvelle UDF (fonction définie par l’utilisateur). Une UDF peut contenir l’un des éléments suivants :

Expression SQL.
Code JavaScript.
Code Java (code source ou code compilé).

Une UDF peut retourner des résultats scalaires ou tabulaires.

Voir aussi :: ALTER FUNCTION, DROP FUNCTION, SHOW USER FUNCTIONS , DESCRIBE FUNCTION

Syntaxe¶

SQL

CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'

JavaScript

CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  LANGUAGE JAVASCRIPT
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'

Java

Utilisez la syntaxe ci-dessous si le code source est en ligne :

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] FUNCTION [ IF NOT EXISTS ] <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
    RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
    [ [ NOT ] NULL ]
    LANGUAGE JAVA
    [ RUNTIME_VERSION = <java_jdk_version> ]
    [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
    [ VOLATILE | IMMUTABLE ]
    [ COMMENT = '<string_literal>' ]
    [ IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name_to_read>' ...] ) ]
    [ PACKAGES = ( '<package_name_and_version>' [, '<package_name_and_version>' ...] ) ]
    HANDLER = '<path_to_method>'
    [ TARGET_PATH = '<stage_path_and_file_name_to_write>' ]
    AS '<function_definition>'

Utilisez la syntaxe ci-dessous si le code source est précompilé (c’est-à-dire si le code est fourni comme un fichier JAR) :

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] FUNCTION [ IF NOT EXISTS ] <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
    RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
    [ [ NOT ] NULL ]
    LANGUAGE JAVA
    [ RUNTIME_VERSION = <java_jdk_version> ]
    [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
    [ VOLATILE | IMMUTABLE ]
    [ COMMENT = '<string_literal>' ]
    IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name_to_read>' ...] )
    HANDLER = '<path_to_method>'

Paramètres requis¶

Tous les langages

nom ( [ nom_argument type_données_argument ] [ , ... ] )

Spécifie l’identificateur (et éventuellement un ou plusieurs arguments/entrées) pour l’UDF. L’identificateur n’a pas besoin d’être unique pour le schéma dans lequel l’UDF est créé parce que les UDFs sont identifiés et résolus par leurs noms et types d’arguments.

Toutefois, l’identificateur doit commencer par un caractère alphabétique et ne peut pas contenir d’espaces ou de caractères spéciaux à moins que toute la chaîne d’identificateur soit délimitée par des guillemets doubles (p. ex. "My object"). Les identificateurs entre guillemets doubles sont également sensibles à la casse.

Pour plus de détails, voir Exigences relatives à l’identificateur.

RETURNS ...

Spécifie les résultats retournés par l’UDF, ce qui détermine le type UDF :

type_données_résultat : crée un UDF scalaire qui retourne une valeur unique avec le type de données spécifié.

TABLE ( nom_colonne type_données_colonne , ... ) : crée une UDF de table qui retourne des résultats tabulaires avec la ou les colonnes et le ou les types de colonnes spécifiés.

Note

Pour des UDFs Java :

Le type_données_résultat doit figurer dans la colonne SQL Data Type de la table SQL-Java Type Mappings.

AS définition_fonction

Définit le code exécuté par l’UDF. Le contenu dépend du type de l’UDF créé :

UDF SQL : toute expression SQL valide. Pour plus de détails sur les UDFs SQL, y compris des exemples, voir SQL UDFs.

UDF JavaScript : tout JavaScript valide. Pour plus de détails sur les UDFs JavaScript, y compris des exemples, voir JavaScript UDFs.

UDF Java : tout Java valide. Pour plus de détails sur les UDFs Java, y compris des exemples, voir UDFs Java.

Pour plus de détails, voir Notes sur l’utilisation (dans ce chapitre).

Note

Une définition_fonction n’est pas nécessaire pour les UDFs précompilées.

JavaScript

LANGUAGE JAVASCRIPT: Spécifie que le code est en langage JavaScript.

Java

LANGUAGE JAVA

Spécifie que le code est en langage Java.

RUNTIME_VERSION = java_jdk_version

Spécifie la version de l’environnement d’exécution JDK Java à utiliser. Les versions de Java prises en charge sont les suivantes :

11.x

Si RUNTIME_VERSION n’est pas défini, le JDK Java 11 est utilisé.

IMPORTS = (chemin_zone_préparation_et_fichier_à_lire)

L’emplacement (zone de préparation), le chemin et le nom du ou des fichiers à importer.

Un fichier peut être un fichier JAR ou un autre type de fichier.

Si le fichier est un fichier JAR, il peut contenir un ou plusieurs fichiers .class et zéro ou plusieurs fichiers de ressources.

JNI (Java Native Interface) n’est pas pris en charge. Snowflake interdit le chargement de bibliothèques qui contiennent du code natif (par opposition au bytecode Java).

Les UDFs Java peuvent également lire des fichiers non JAR. Pour un exemple, voir Lecture d’un fichier à partir d’une UDF Java.

Si vous prévoyez de copier un fichier (fichier JAR ou autre) vers une zone de préparation, Snowflake recommande d’utiliser une zone de préparation interne nommée car la commande PUT prend en charge la copie de fichiers vers des zones de préparation internes nommées, et la commande PUT est généralement le moyen le plus simple de déplacer un fichier JAR vers une zone de préparation.

Les zones de préparation externes sont autorisées, mais ne sont pas prises en charge par PUT.

Chaque fichier de la clause IMPORTS doit avoir un nom unique, même si les fichiers se trouvent dans des sous-répertoires différents ou dans des zones de préparation différentes.

Si les clauses IMPORTS et TARGET_PATH sont toutes deux présentes, le nom du fichier dans la clause TARGET_PATH doit être différent de chaque nom de fichier dans la clause IMPORTS, même si les fichiers se trouvent dans des sous-répertoires différents ou dans des zones de préparation différentes.

Snowflake renvoie une erreur si TARGET_PATH correspond à un fichier existant ; vous ne pouvez pas utiliser TARGET_PATH pour écraser un fichier existant.

Pour une UDF Java précompilée, la clause IMPORTS est nécessaire car elle spécifie l’emplacement du fichier JAR qui contient l’UDF.

Pour une UDF Java en ligne, la clause IMPORTS n’est nécessaire que si l’UDF Java en ligne doit accéder à d’autres fichiers, tels que des bibliothèques ou des fichiers texte.

Pour les packages système Snowflake, comme le package Snowpark, vous pouvez spécifier le package avec la clause PACKAGES plutôt que de spécifier son fichier JAR avec IMPORTS. Dans ce cas, le fichier JAR du package ne doit pas nécessairement être inclus dans une valeur IMPORTS.

HANDLER = chemin_vers_méthode

Le chemin et le nom de la méthode statique dans ce fichier .jar. Ceci se présente généralement sous la forme suivante :

com.my_company.my_package.my_class.my_method

où :

com.my_company.my_package

correspond à la déclaration package en haut du fichier de code source Java. Par exemple :

package com.my_company.my_package;

Si le fichier JAR a été créé en dehors d’un package, alors le chemin_vers_méthode ne doit pas inclure de nom de package.

Java en ligne

définition_fonction: Les UDFs Java en ligne nécessitent une définition de fonction.

Paramètres facultatifs¶

Tous les langages

SECURE

Précise que la fonction est sécurisée. Pour plus d’informations sur les fonctions sécurisées, voir UDFs sécurisés.

[ [ NOT ] NULL ]

Indique si la fonction peut renvoyer des valeurs NULL ou doit uniquement renvoyer des valeurs NON-NULL. La valeur par défaut est NULL (c’est-à-dire que la fonction peut renvoyer NULL).

Note

Actuellement, la clause NOT NULL n’est pas appliquée pour des UDFs SQL. Les UDFs SQL déclarées comme NOT NULL peuvent renvoyer des valeurs NULL. Snowflake recommande d’éviter NOT NULL pour des UDFs SQL à moins que le code de la fonction ne soit écrit pour garantir que les valeurs NULL ne soient jamais renvoyées.

CALLED ON NULL INPUT ou . RETURNS NULL ON NULL INPUT | STRICT

Spécifie le comportement de l’UDF lorsqu’il est appelé avec des entrées « null ». Contrairement aux fonctions définies par le système, qui retournent toujours la valeur « null » lorsqu’une entrée est nulle, les UDFs peuvent gérer les entrées null, retournant des valeurs non nulles même lorsqu’une entrée est null :

CALLED ON NULL INPUT appellera toujours l’UDF avec des entrées null. Il appartient à l’UDF de traiter ces valeurs de manière appropriée.

RETURNS NULL ON NULL INPUT (ou son synonyme STRICT) n’appellera pas l’UDF si une entrée est null. En revanche, une valeur null sera toujours retournée pour cette ligne. Notez que l’UDF peut toujours retourner une valeur null pour les entrées non null.

Par défaut : CALLED ON NULL INPUT

VOLATILE | IMMUTABLE

Spécifie le comportement de l’UDF lors de l’affichage de résultats :

VOLATILE : l’UDF peut afficher des valeurs différentes pour différentes lignes, même pour la même entrée (par exemple en raison du non-déterminisme et du statut).

IMMUTABLE : l’UDF suppose que la fonction, lorsqu’elle est appelée avec les mêmes entrées, renvoie toujours le même résultat. Cette garantie n’est pas vérifiée. Spécifier IMMUTABLE pour un UDF qui retourne des valeurs différentes pour la même entrée aura pour résultat un comportement indéfini.

Par défaut : VOLATILE

COMMENT = 'litéral_chaine'

Spécifie un commentaire pour l’UDF, qui est affiché dans la colonne DESCRIPTION de la sortie SHOW FUNCTIONS et de la sortie SHOW USER FUNCTIONS.

Par défaut : user-defined function

Java

PACKAGES = (nom_et_version_package)

Nom et numéro de version des packages système Snowflake requis comme dépendances. La valeur doit être de la forme nom_package:numéro_version, où nom_package est domaine_snowflake:package. Notez que vous pouvez spécifier latest comme numéro de version afin que Snowflake utilise la dernière version disponible sur le système.

Par exemple :

-- Use version 1.2.0 of the Snowpark package.
packages=('com.snowflake:snowpark:1.2.0')

-- Use the latest version of the Snowpark package.
packages=('com.snowflake:snowpark:latest')

Vous pouvez découvrir la liste des packages système pris en charge en exécutant la commande SQL suivante dans Snowflake :

select * from information_schema.packages where language ='java';

Pour une dépendance que vous spécifiez avec PACKAGES, vous n’avez pas besoin de spécifier également son fichier JAR dans une clause IMPORTS.

Java en ligne

TARGET_PATH = chemin_et_nom_de_fichier_à_écrire_de_zone_préparation

La clause TARGET_PATH spécifie l’emplacement vers lequel Snowflake doit écrire le code compilé (fichierJAR) après avoir compilé le code source spécifié dans la définition_fonction.

Si cette clause est incluse, l’utilisateur doit supprimer manuellement le fichier JAR lorsqu’il n’est plus nécessaire (généralement lorsque l’UDF Java est détruite).

Si cette clause est omise, Snowflake recompile le code source chaque fois que le code est nécessaire. Le fichier JAR n’est pas stocké de façon permanente, et l’utilisateur n’a pas besoin de nettoyer le fichier JAR.

Notes sur l’utilisation¶

Tous les langages

définition_fonction a des restrictions de taille. La taille maximale permise est sujette à changement.
Les délimiteurs autour de la définition_fonction peuvent être des guillemets simples ou une paire de signes dollar.

L’utilisation de $$ comme délimiteur facilite l’écriture des procédures stockées contenant des guillemets simples.

Si le délimiteur du corps de la fonction est le caractère guillemet simple, tous les guillemets simples dans définition_fonction (par exemple, les littéraux de chaînes) doivent être échappés par des guillemets simples.
Si vous utilisez une UDF dans une politique de masquage, assurez-vous que le type de données de la colonne, l’UDF, et la politique de masquage correspondent. Pour plus d’informations, voir Fonctions définies par l’utilisateur dans une politique de masquage.
Concernant les métadonnées :

Attention

Les clients doivent s’assurer qu’aucune donnée personnelle (autre que pour un objet utilisateur), donnée sensible, donnée à exportation contrôlée ou autre donnée réglementée n’est saisie comme métadonnée lors de l’utilisation du service Snowflake. Pour plus d’informations, voir Champs de métadonnées dans Snowflake.

SQL

Actuellement, la clause NOT NULL n’est pas appliquée pour des UDFs SQL.

JavaScript

Snowflake ne valide pas le code JavaScript au moment de la création de l’UDF (autrement dit, la création de l’UDF réussit, que le code soit valide ou non). Si le code n’est pas valide, des erreurs s’afficheront lorsque l’UDF sera appelé au moment de la requête.

Java

En Java, les types de données primitifs n’autorisent pas les valeurs NULL. Ainsi, transmettre une valeur NULL pour un argument d’un tel type entraîne une erreur.
Dans la clause HANDLER, le nom de la méthode est sensible à la casse.
Dans les clauses IMPORTS et TARGET_PATH :
- Les noms de package, de classe et de fichier sont sensibles à la casse.
- Le(s) nom(s) de zone de préparation sont insensibles à la casse.
Vous pouvez utiliser la clause PACKAGES pour spécifier le nom et le numéro de version du package pour les dépendances Snowflake définies par le système, comme celles de Snowpark. Pour les autres dépendances, spécifiez les fichiers JAR de dépendance avec la clause IMPORTS.
Snowflake valide ceci :
- Le fichier JAR spécifié dans le HANDLER de l’instruction CREATE FUNCTION existe et contient la classe et la méthode spécifiées.
- Les types d’entrée et de sortie spécifiés dans la déclaration UDF sont compatibles avec les types d’entrée et de sortie de la méthode Java.
La validation peut être effectuée au moment de la création ou de l’exécution.
- Si l’utilisateur est connecté à un entrepôt Snowflake actif au moment de l’exécution de l’instruction CREATE FUNCTION, l’UDF est validée au moment de la création.
- Sinon, l’UDF est créée, mais n’est pas validée immédiatement, et Snowflake renvoie le message suivant : Function <nom> created successfully, but could not be validated since there is no active warehouse.

Exemples¶

SQL

Créer un UDF scalaire SQL qui renvoie une approximation codée en dur de la constante mathématique pi :

CREATE FUNCTION pi_udf()
  RETURNS FLOAT
  AS '3.141592654::FLOAT'
  ;

Créer un UDF de tableau SQL simple qui renvoie des valeurs codées en dur :

CREATE FUNCTION simple_table_function ()
  RETURNS TABLE (x INTEGER, y INTEGER)
  AS
  $$
    SELECT 1, 2
    UNION ALL
    SELECT 3, 4
  $$
  ;

SELECT * FROM TABLE(simple_table_function());

Sortie :

SELECT * FROM TABLE(simple_table_function());
+---+---+
| X | Y |
|---+---|
| 1 | 2 |
| 3 | 4 |
+---+---+

Créer une UDF qui accepte plusieurs paramètres :

CREATE FUNCTION multiply1 (a number, b number)
  RETURNS number
  COMMENT='multiply two numbers'
  AS 'a * b';

Créer une UDF de table SQL nommée get_countries_for_user qui renvoie les résultats d’une requête :

CREATE OR REPLACE FUNCTION get_countries_for_user ( id number )
  RETURNS TABLE (country_code char, country_name varchar)
  AS 'select distinct c.country_code, c.country_name
      from user_addresses a, countries c
      where a.user_id = id
      and c.country_code = a.country_code';

JavaScript

Créer un UDF JavaScript nommé js_factorial :

CREATE OR REPLACE FUNCTION js_factorial(d double)
  RETURNS double
  LANGUAGE JAVASCRIPT
  STRICT
  AS '
  if (D <= 0) {
    return 1;
  } else {
    var result = 1;
    for (var i = 2; i <= D; i++) {
      result = result * i;
    }
    return result;
  }
  ';

Java

Voici un exemple de base d’une instruction CREATE FUNCTION en ligne :

create or replace function echo_varchar(x varchar)
returns varchar
language java
called on null input
handler='TestFunc.echoVarchar'
target_path='@~/testfunc.jar'
as
'class TestFunc {
  public static String echoVarchar(String x) {
    return x;
  }
}';

Voici un exemple de base d’une instruction CREATEFUNCTION précompilée :

create function my_decrement_udf(i numeric(9, 0))
    returns numeric
    language java
    imports = ('@~/my_decrement_udf_package_dir/my_decrement_udf_jar.jar')
    handler = 'my_decrement_udf_package.my_decrement_udf_class.my_decrement_udf_method'
    ;

Pour d’autres exemples d’UDFs Java , voir exemples.