Aperçu des UDFs

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

Dans ce chapitre :

Types de fonctions définies par l’utilisateur

SQL

Un UDF SQL évalue une expression arbitraire SQL et retourne les résultats de l’expression. La définition de la fonction peut être une expression SQL qui retourne soit une valeur scalaire (c’est-à-dire unique), soit, si elle est définie comme fonction de table, un ensemble de lignes.

L’expression peut être une expression de requête, bien que pour les fonctions non tabulaires, l’expression de requête doit être garantie pour retourner une ligne maximum, contenant une seule colonne.

L’expression définissant un UDF peut se référer aux arguments d’entrée de la fonction, et aux objets de la base de données tels que les tables, les vues et les séquences. 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. L’expression de définition d’un UDF SQL peut se référer à d’autres fonctions définies par l’utilisateur, bien qu’elle ne puisse pas se référer récursivement à elle-même, soit directement, soit par le biais d’une autre fonction qui lui fait appel.

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.

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.

UDFs scalaires et de tables

Par défaut, un UDF est scalaire, retournant au maximum une ligne, constituée d’une seule colonne/valeur.

Cependant, un UDF peut aussi être défini pour retourner un ensemble de lignes avec une ou plusieurs colonnes, qui sont alors accessibles dans la clause FROM d’une requête. Une fonction de table définie par l’utilisateur (UDTF) est définie en spécifiant un type de retour qui contient le mot clé TABLE et spécifie les noms et types de colonnes dans les résultats de table.

Pour plus de détails sur la création et l’utilisation d’UDTFs, y compris des exemples, voir Fonctions de table définies par l’utilisateur (UDTFs SQL) et Fonctions de table définies par l’utilisateur (UDTFs JavaScript). Pour plus d’informations générales sur les fonctions de table définies par le système et fournies dans Snowflake, voir Fonctions de table.

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 bd.schéma.nom_fonction, 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. Plusieurs UDFs 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.

Exigences en matière de sécurité et de privilège pour les UDFs

Si une définition de fonction fait référence à une table non qualifiée, cette table est résolue dans le schéma contenant la fonction. Une référence à un autre objet de schéma (par exemple, une table, une vue ou une autre fonction) nécessite que le propriétaire de la fonction ait les privilèges nécessaires pour accéder à cet objet de schéma. L’appelant de la fonction n’a pas besoin d’avoir accès aux objets référencés dans la définition de la fonction, mais seulement d’avoir le privilège d’utiliser la fonction.

Par exemple, un administrateur possède une table nommée users, qui contient des données sensibles qui ne sont pas généralement accessibles, mais l’administrateur peut afficher le nombre total d’utilisateurs grâce à une fonction sur laquelle les autres utilisateurs ont des privilèges d’accès :

use role dataadmin;

desc table users;

+-----------+--------------+--------+-------+---------+-------------+------------+--------+------------+---------+
| name      | type         | kind   | null? | default | primary key | unique key | check  | expression | comment |
|-----------+--------------+--------+-------+---------+-------------+------------+--------+------------+---------|
| USER_ID   | NUMBER(38,0) | COLUMN | Y     | [NULL]  | N           | N          | [NULL] | [NULL]     | [NULL]  |
| USER_NAME | VARCHAR(100) | COLUMN | Y     | [NULL]  | N           | N          | [NULL] | [NULL]     | [NULL]  |
  ...
  ...
  ...
+-----------+--------------+--------+-------+---------+-------------+------------+--------+------------+---------+

create function total_user_count() returns number as 'select count(*) from users';

grant usage on function total_user_count() to role analyst;

use role analyst;

-- This will fail because the role named "analyst" does not have the
-- privileges required in order to access the table named "users".
select * from users;

FAILURE: SQL compilation error:
Object 'USERS' does not exist.

-- However, this will succeed.
select total_user_count();

+--------------------+
| TOTAL_USER_COUNT() |
|--------------------+
| 123                |
+--------------------+

Pour plus d’informations sur les rôles et les privilèges de gestion du contrôle d’accès, voir Contrôle d’accès dans Snowflake.

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