UDFs SQL scalaires

Ce chapitre couvre des concepts et des détails d’utilisation spécifiques aux UDFs SQL (fonctions définies par l’utilisateur).

Dans ce chapitre :

Utilisation générale

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. Par exemple, voici un exemple de base d’un UDF scalaire qui calcule l’aire d’un cercle :

CREATE FUNCTION area_of_circle(radius FLOAT)
  RETURNS FLOAT
  AS
  $$
    pi() * radius * radius
  $$
  ;
Copy
SELECT area_of_circle(1.0);
Copy

Sortie :

SELECT area_of_circle(1.0);
+---------------------+
| AREA_OF_CIRCLE(1.0) |
|---------------------|
|         3.141592654 |
+---------------------+
Copy

L’expression peut être une expression de requête (une expression SELECT). Par exemple :

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

Lorsque vous utilisez une expression de requête dans un UDF SQL, vous n’avez pas besoin d’inclure un point-virgule dans le corps de l’UDF pour terminer l’expression de la requête.

Vous ne pouvez inclure qu’une seule expression de requête. L’expression peut inclure UNION [ALL].

Note

Bien que le corps d’un UDF puisse contenir une instruction SELECT complète, il ne peut pas contenir d’instructions DDL ni toute instruction DML autre que SELECT.

Note

Les fonctions scalaires (UDFs) ont une limite de 500 arguments d’entrée.

UDFs mémoïsables

Une UDF SQL scalaire peut être mémoïsable. Une fonction mémoïsable met en cache le résultat de l’appel d’une UDF SQL scalaire et renvoie ensuite le résultat en cache lorsque la sortie est nécessaire ultérieurement. L’avantage de l’utilisation d’une fonction mémoïsable est d’améliorer les performances pour les requêtes complexes, telles que les recherches de colonnes multiples dans les tables de mappage référencées dans une politique d’accès aux lignes ou une politique de masquage.

Les propriétaires de la politique (par exemple, le rôle ayant le privilège OWNERSHIP sur la politique d’accès aux lignes) peuvent mettre à jour leurs conditions de politique pour remplacer les sous-requêtes qui ont des tables de mappage par une fonction mémoïsable. Lorsque les utilisateurs font référence à la colonne protégée par la politique dans une requête ultérieure, les résultats mis en cache par la fonction mémoïsable sont disponibles pour être utilisés selon les besoins.

Écriture d’une fonction mémoïsable

Vous pouvez définir une UDF SQL scalaire à mémoïser dans l’instruction CREATE FUNCTION en spécifiant le mot-clé MEMOIZABLE. Les fonctions mémoïsables ne comprennent pas d’arguments.

Écriture d’une fonction mémoïsable :

  • Permet de retourner un ARRAY result_data_type et d’autres types de données scalaires.

    Notez que la mise en cache ne se produit pas lorsque les types de données retournés sont OBJECT et VARIANT :

    • Pour le type de données OBJECT, la fonction mémoïsable agit comme une UDF normale sans mise en cache.

    • Pour le type de données VARIANT, Snowflake renvoie une erreur.

    • Pour plus d’informations, voir RETURNS ... dans la rubrique CREATE FUNCTION.

  • Ne prend pas en charge la référence à une autre fonction mémoïsable de quelque manière que ce soit.

Appel d’une fonction mémoïsable

Une fonction mémoïsable peut être appelée dans une instruction SELECT ou être incluse dans une définition de politique, qui appelle ensuite la fonction mémoïsable en fonction des conditions de la politique.

Lorsque vous appelez une fonction mémoïsable, notez :

  • Pour les UDFs SQL qui renvoient le type de données ARRAY ou spécifient une valeur non scalaire, utilisez la fonction mémoïsable comme argument dans la fonction ARRAY_CONTAINS.

  • Limite de la taille du cache :

    Chaque fonction mémoïsable a une limite 10 KB pour la session Snowflake en cours.

    Si la fonction mémoïsable dépasse cette limite pour le cache du jeu de résultats, Snowflake ne met pas en cache le résultat de l’appel de la fonction mémoïsable. Au lieu de cela, l’UDF agit comme une UDF scalaire normale selon la façon dont la fonction est écrite.

  • Utilisation du cache :

    Les fonctions mémoïsables disposent d’un cache de résultats réutilisable pour différentes instructions SQL lorsque l’environnement et le contexte de la requête ne changent pas. En général, cela signifie que le cache de résultat s’applique à différentes instructions SQL à condition que :

    • L’autorisation de contrôle d’accès sur les objets et les colonnes référencés dans une requête reste la même.

    • Les objets référencés dans la requête ne sont pas modifiés (par exemple, par des instructions DML).

    La colonne CHILD_QUERIES_WAIT_TIME de la vue Account Usage QUERY_HISTORY enregistre le temps (en millisecondes) nécessaire pour effectuer la recherche en cache lors de l’appel d’une fonction mémoïsable.

    Par défaut, la colonne CHILD_QUERIES_WAIT_TIME n’est pas incluse dans la sortie.

    Pour inclure la colonne, vous devez activer le groupe de versions de changement de comportement 2023_01.

    Pour plus d’informations sur l’activation des groupes de versions de changement de comportement, voir Gestion des changements de comportement.

  • Les fonctions mémoïsables ne réutilisent pas les résultats mis en cache lorsque :

    • La fonction référence une table ou un autre objet et il y a une mise à jour de la table référencée.

    • Il y a un changement dans le contrôle d’accès à la table.

    • La fonction appelle une fonction non déterministe.

    • La fonction appelle une fonction externe ou une UDF qui n’est pas une UDF SQL.

Exemples

Exemple(s) UDF scalaire SQL de base

Cet exemple renvoie une approximation codée en dur de la constante mathématique pi.

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

Sortie :

SELECT pi_udf();
+-------------+
|    PI_UDF() |
|-------------|
| 3.141592654 |
+-------------+
Copy

Exemples SQL courants

Expression de requête avec l’instruction SELECT

Créez la table et les données à utiliser :

CREATE TABLE purchases (number_sold INTEGER, wholesale_price NUMBER(7,2), retail_price NUMBER(7,2));
INSERT INTO purchases (number_sold, wholesale_price, retail_price) VALUES 
   (3,  10.00,  20.00),
   (5, 100.00, 200.00)
   ;
Copy

Créez l’UDF :

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

Appelez l’UDF dans une requête :

SELECT profit();
Copy

Sortie :

SELECT profit();
+----------+
| PROFIT() |
|----------|
|   530.00 |
+----------+
Copy

UDF dans une clause WITH

CREATE TABLE circles (diameter FLOAT);

INSERT INTO circles (diameter) VALUES
    (2.0),
    (4.0);

CREATE FUNCTION diameter_to_radius(f FLOAT) 
  RETURNS FLOAT
  AS 
  $$ f / 2 $$
  ;
Copy
WITH
    radii AS (SELECT diameter_to_radius(diameter) AS radius FROM circles)
  SELECT radius FROM radii
    ORDER BY radius
  ;
Copy

Sortie :

+--------+
| RADIUS |
|--------|
|      1 |
|      2 |
+--------+
Copy

Fonctionnement JOIN

Cet exemple utilise une requête plus complexe, qui inclut une opération JOIN :

Créez la table et les données à utiliser :

CREATE TABLE orders (product_ID varchar, quantity integer, price numeric(11, 2), buyer_info varchar);
CREATE TABLE inventory (product_ID varchar, quantity integer, price numeric(11, 2), vendor_info varchar);
INSERT INTO inventory (product_ID, quantity, price, vendor_info) VALUES 
  ('X24 Bicycle', 4, 1000.00, 'HelloVelo'),
  ('GreenStar Helmet', 8, 50.00, 'MellowVelo'),
  ('SoundFX', 5, 20.00, 'Annoying FX Corporation');
INSERT INTO orders (product_id, quantity, price, buyer_info) VALUES 
  ('X24 Bicycle', 1, 1500.00, 'Jennifer Juniper'),
  ('GreenStar Helmet', 1, 75.00, 'Donovan Liege'),
  ('GreenStar Helmet', 1, 75.00, 'Montgomery Python');
Copy

Créez l’UDF :

CREATE FUNCTION store_profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
  SELECT SUM( (o.price - i.price) * o.quantity) 
    FROM orders AS o, inventory AS i 
    WHERE o.product_id = i.product_id
  $$
  ;
Copy

Appelez l’UDF dans une requête :

SELECT store_profit();
Copy

Sortie :

SELECT store_profit();
+----------------+
| STORE_PROFIT() |
|----------------|
|         550.00 |
+----------------+
Copy

La rubrique CREATE FUNCTION contient des exemples supplémentaires.

Utilisation d’UDFs dans différentes clauses

Un UDF scalaire peut être utilisé partout où une expression scalaire peut être utilisée. Par exemple :

-- ----- These examples show a UDF called from different clauses ----- --

select MyFunc(column1) from table1;

select * from table1 where column2 > MyFunc(column1);
Copy

Utilisation des variables SQL dans un UDF

Cet exemple montre comment définir une variable SQL et utiliser cette variable dans un UDF :

SET id_threshold = (SELECT COUNT(*)/2 FROM table1);
Copy
CREATE OR REPLACE FUNCTION my_filter_function()
RETURNS TABLE (id int)
AS
$$
SELECT id FROM table1 WHERE id > $id_threshold
$$
;
Copy