Aperçu des UDFs

Ce chapitre couvre des concepts et des détails d’utilisation qui s’appliquent à toutes les UDFs (fonctions définies par l’utilisateur).

Dans ce chapitre :

UDFs scalaires vs tabulaires

Les UDFs peuvent être scalaires ou tabulaires.

La fonction scalaire renvoie une ligne de sortie pour chaque ligne d’entrée. La ligne renvoyée est constituée d’une seule colonne/valeur.

Pour plus de détails sur la création et l’utilisation d’UDFs scalaires, voir :

Une fonction tabulaire, également appelée fonction de table, renvoie zéro, une ou plusieurs lignes pour chaque ligne d’entrée. Une UDF tabulaire est définie en spécifiant une clause de retour qui contient le mot-clé TABLE et spécifie les noms et types de données de colonnes dans les résultats de table. Les UDFs tabulaires sont souvent appelées UDTFs (fonctions de table définies par l’utilisateur) ou UDFs de tables.

Pour plus de détails sur la création et l’utilisation d’UDTFs, y compris des exemples, voir :

Langages de programmation pris en charge pour la création d’UDFs

Snowflake prend en charge les langages de programmation suivants :

SQL

Une UDF SQL peut être soit scalaire soit tabulaire.

Une UDF SQL évalue une expression SQL arbitraire et retourne les résultats de l’expression.

L’expression peut être soit une expression générale, soit une expression de requête :

  • Cette UDF utilise l’expression générale pi() * radius * radius :

    CREATE FUNCTION area_of_circle(radius FLOAT)
      RETURNS FLOAT
      AS
      $$
        pi() * radius * radius
      $$
      ;
    
  • Cette UDF utilise une expression de requête :

    CREATE FUNCTION profit()
      RETURNS NUMERIC(11, 2)
      AS
      $$
        SELECT SUM((retail_price - wholesale_price) * number_sold)
            FROM purchases
      $$
      ;
    

Pour les UDFs non tabulaires, l’expression de la requête doit garantir le retour d’au plus une ligne, contenant une colonne.

L’expression définissant une UDF peut faire référence aux arguments d’entrée de la fonction, comme le montrent les exemples ci-dessus.

L’expression définissant une UDF peut faire référence à des objets de base de données tels que des tables, des vues et des séquences, comme le montrent les exemples ci-dessus. Toutefois, les restrictions suivantes s’appliquent :

  • L’UDF propriétaire doit avoir des privilèges appropriés sur tous les objets de la base de données auxquels l’UDF accède.

  • Il n’est pas possible de faire référence à des objets de la base de données à l’aide de SQL dynamique. Par exemple, l’instruction suivante échoue, car IDENTIFIER(table_name_parameter) n’est pas autorisé :

    CREATE OR REPLACE FUNCTION profit2(table_name_parameter VARCHAR)
      RETURNS NUMERIC(11, 2)
      AS
      $$
        SELECT SUM((retail_price - wholesale_price) * number_sold)
            FROM IDENTIFIER(table_name_parameter)
      $$
      ;
    

L’expression définissant une UDF ne doit pas contenir de point-virgule comme terminateur d’instruction. Par exemple, notez que l’instruction SELECT de l’UDF d’expression de requête ci-dessus ne se termine pas par un point-virgule.

Une expression de définition d’UDF SQL peut faire référence à d’autres fonctions définies par l’utilisateur. Cependant, elle ne peut faire référence récursivement à elle-même, que ce soit directement ou indirectement (c’est-à-dire par l’intermédiaire d’une autre fonction faisant appel à elle).

Pour plus d’informations, voir Présentation des UDFs SQL.

JavaScript

Les UDFs JavaScript vous permettent de manipuler des données à l’aide du langage de programmation et de l’environnement d’exécution JavaScript. Les UDFs JavaScript sont créés de la même manière que les UDFs SQL, mais avec le paramètre LANGUAGE réglé sur JAVASCRIPT.

Comme les UDFs SQL, les UDFs JavaScript peuvent retourner un résultat scalaire ou tabulaire, selon la façon dont l’UDF est défini.

Une expression de définition d’UDF JavaScript peut se référer de manière récursive à elle-même, mais ne peut pas faire référence à d’autres fonctions définies par l’utilisateur.

Pour plus d’informations, voir Présentation des UDFs JavaScript.

Note

Les UDFs JavaScript ont des exigences, des détails d’utilisation et des limitations qui ne s’appliquent pas aux UDFs SQL. Pour plus de détails, y compris des exemples, voir JavaScript UDFs.

Java

Les UDFs Java vous permettent de manipuler des données à l’aide du langage de programmation Java et de l’environnement d’exécution.

Comme les UDFs SQL et JavaScript, les UDFs peuvent retourner un résultat scalaire ou tabulaire, selon la façon dont l’UDF est définie.

Vous pouvez créer des UDFs Java de deux façons :

  • En ligne : vous tapez le code Java dans le cadre de la commande CREATE FUNCTION.

  • Fichier JAR : vous spécifiez l’emplacement d’un fichier JAR déjà compilé dans le cadre de la commande CREATE FUNCTION.

Pour plus d’informations, voir Introduction aux UDFs Java.

Note

Les UDFs Java ont des exigences, des détails d’utilisation et des limitations qui ne s’appliquent pas aux UDFs SQL. Pour plus de détails, voir :

Python

Les UDFs Python vous permettent de manipuler des données à l’aide du langage de programmation et de l’environnement d’exécution Python.

Comme les UDFs Java, les UDFs Python peuvent retourner un résultat scalaire ou tabulaire, selon la façon dont l’UDF est défini.

Vous pouvez créer des UDFs Python de deux façons :

  • En ligne : vous tapez le code Python dans le cadre de la commande CREATE FUNCTION.

  • Comme un module copié sur une zone de préparation : vous spécifiez l’emplacement du fichier du module dans le cadre de la commande CREATE FUNCTION.

Pour plus d’informations, voir Introduction aux UDFs Python.

Note

Les UDFs Python ont des exigences, des détails d’utilisation et des limitations qui ne s’appliquent pas aux UDFs SQL. Pour plus de détails, voir :

Choisir son langage de programmation

De nombreux facteurs peuvent influer sur le choix du langage de programmation que vous utilisez pour écrire une UDF. Les facteurs peuvent inclure :

  • Si vous avez déjà du code dans un langage particulier. Par exemple, si vous disposez déjà d’un fichier .jar contenant une méthode qui effectue le travail dont vous avez besoin, vous pouvez préférer Java comme langage de programmation.

  • Les capacités du langage.

  • Si un langage possède des bibliothèques qui peuvent vous aider à effectuer le traitement dont vous avez besoin.

Le tableau suivant résume les principales capacités de chaque langage de programmation d’UDF. La liste ci-dessous décrit les colonnes de la table :

  • Tabulaire : indique si Snowflake prend en charge l’écriture de fonctions tabulaires (en plus des fonctions scalaires).

  • Bouclage et branchement : indique si le langage de programmation prend en charge le bouclage et le branchement.

  • Pré-compilé : indique si vous pouvez fournir le code de votre UDF sous forme de fichier compilé (par exemple, un fichier .JAR).

  • En ligne : indique si vous pouvez fournir le code sous forme de texte dans l’instruction CREATE FUNCTION.

  • Partageable : indique si les UDFs écrits dans ce langage peuvent être utilisés avec la fonction Snowflake Secure Data Sharing.

Langage

Tabulaire

Bouclage et branchement

Source précompilée ou zone de préparation

En ligne

Partageable

SQL

Oui

Non

Non

Oui

Oui

JavaScript

Oui

Oui

Non

Oui

Oui

Java

Oui

Oui

Oui (précompilée)

Oui

Non 1

Python

Oui

Oui

Oui (zone de préparation)

Oui

Non 2

1

Pour plus d’informations sur les limites du partage d’UDFs Java , voir Limitations sur les UDFs Java.

2

Pour plus d’informations sur les limites du partage d’UDFs Python, voir Limites des UDFs Python.

Conventions d’appellation pour les UDFs

Les UDFs sont des objets de base de données, c’est-à-dire qu’ils sont créés dans une base de données et un schéma spécifiés. À ce titre, ils ont un nom complet défini par leur espace de noms, sous la forme db.schema.function_name , par exemple :

SELECT temporary_db_qualified_names_test.temporary_schema_1.udf_pi();

Lorsqu’ils sont appelés sans leur nom complet, les UDFs sont résolus en fonction de la base de données et du schéma utilisés pour la session.

Ils se distinguent des fonctions intégrées et définies par le système fournies par Snowflake, qui n’ont pas d’espace de noms, et qui peuvent donc être appelées de n’importe où.

Conflits avec les fonctions définies par le système

Pour éviter les conflits lors de l’appel de fonctions, Snowflake ne permet pas de créer des UDFs avec le même nom que les fonctions définies par le système.

Surcharge de noms d’UDF

Snowflake prend en charge la surcharge de noms UDF SQL. Plusieurs UDFs SQL dans le même schéma peuvent porter le même nom, tant que leurs signatures d’arguments diffèrent, soit par le nombre d’arguments soit par leur type. Lorsqu’un UDF surchargé est appelé, Snowflake vérifie les arguments et appelle la fonction appropriée.

Prenons les exemples suivants, qui créent deux UDFs SQL nommés add5 :

CREATE OR REPLACE FUNCTION add5 (n number)
  RETURNS number
  AS 'n + 5';

CREATE OR REPLACE FUNCTION add5 (s string)
  RETURNS string
  AS 's || ''5''';

Important

Dans la deuxième fonction ADD5, les guillemets simples sont utilisés pour effectuer un échappement du littéral de chaîne '5'. Les guillemets simples utilisés dans une définition UDF doivent être échappés par des guillemets simples.

Si add5 est appelé avec un argument numérique, alors la première implémentation est choisie, tandis qu’un argument de type chaîne utilise la seconde implémentation. Si l’argument n’est ni un nombre ni une chaîne, alors l’implémentation dépend des règles implicites de conversion de types de Snowflake. Par exemple, un argument de type date serait converti en une chaîne, et l’implémentation de la chaîne serait sélectionnée, puisque la conversion de DATE à NUMBER n’est pas prise en charge.

Par exemple :

select add5(1);

+---------+
| ADD5(1) |
|---------|
|       6 |
+---------+

select add5('1');

+-----------+
| ADD5('1') |
|-----------|
| 15        |
+-----------+

select add5('hello');

+---------------+
| ADD5('HELLO') |
|---------------|
| hello5        |
+---------------+

select add5(to_date('2014-01-01'));

+-----------------------------+
| ADD5(TO_DATE('2014-01-01')) |
|-----------------------------|
| 2014-01-015                 |
+-----------------------------+

Soyez prudent lorsque vous utilisez la surcharge. La combinaison de la conversion de type automatique et de la surcharge peut favoriser la situation dans laquelle une erreur utilisateur mineure provoquerait des résultats inattendus. Pour un exemple, voir Surcharge des noms de fonction.

Prudence

Pour les UDFs écrites dans des langages autres que SQL, les règles peuvent différer.

Exemples

Surcharge des noms de fonction

Comme décrit dans Surcharge de noms d’UDF (dans ce chapitre), vous pouvez surcharger les noms de fonction.

L’exemple suivant de surcharge montre comment la combinaison de la surcharge et de la conversion de type automatique peut favoriser l’obtention de résultats inattendus :

Créez une fonction qui prend un paramètre FLOAT :

CREATE FUNCTION add_pi(PARAM_1 FLOAT)
    RETURNS FLOAT
    LANGUAGE SQL
    AS $$
        PARAM_1 + 3.1415926::FLOAT
    $$;

Appelez la fonction deux fois. La première fois, transmettez un FLOAT. La deuxième fois, transmettez un VARCHAR. Le VARCHAR est converti en un FLOAT et la sortie de chaque appel est identique :

SELECT add_pi(1.0), add_pi('1.0');
+-------------+---------------+
| ADD_PI(1.0) | ADD_PI('1.0') |
|-------------+---------------|
|   4.1415926 |     4.1415926 |
+-------------+---------------+

Maintenant, créez une fonction surchargée qui prend un paramètre VARCHAR :

CREATE FUNCTION add_pi(PARAM_1 VARCHAR)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS $$
        PARAM_1 || ' + 3.1415926'
    $$;

Utilisez maintenant exactement les mêmes CALLs qu’avant. Notez la différence de sortie entre ces deux CALLs et les deux CALLs précédents.

SELECT add_pi(1.0), add_pi('1.0');
+-------------+-----------------+
| ADD_PI(1.0) | ADD_PI('1.0')   |
|-------------+-----------------|
|   4.1415926 | 1.0 + 3.1415926 |
+-------------+-----------------+
Revenir au début