UDFs SQL scalaires¶

Créer 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. Vous pouvez créer un mémoisable pour fonctionner avec ou sans arguments. En utilisant des arguments, vous avez plus de liberté pour définir l’UDF SQL. Lorsque vous écrivez une politique pour appeler la fonction mémoisable, vous disposez d’une plus grande liberté dans la définition de la politique.

Si vous spécifiez des arguments, ceux-ci doivent être des valeurs constantes avec l’un des types de données suivants :

• VARCHAR et d’autres types de données de type chaîne.

• NUMBER et d’autres types de données numériques.

• TIMESTAMP et d’autres types de données de date.

• BOOLEAN.

Les valeurs non constantes et leurs types de données, tels que les types de données semi-structurées et les colonnes de tables ne sont pas pris en charge.

Lorsque vous écrivez une fonction mémoïsable :

• Spécifiez BOOLEAN ou d’autres types de données scalaires comme result_data_type.

  Soyez prudent lorsque vous spécifiez ARRAY comme result_data_type car la taille du cache est limitée.

• Ne spécifiez pas d’autres types de données tels que OBJECT et VARIANT.

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

Appeler une fonction mémoïsable¶

Une fonction mémoisable peut être appelée dans une instruction SELECT ou être incluse dans une définition de politique, qui appelle ensuite la fonction mémoisable 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.

• 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.

Exemple(s) d’UDF scalaires 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'
  ;

SELECT pi_udf();   

Sortie :

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

Exemples SQL courants¶

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)
   ;

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

SELECT profit();

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

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 $$
  ;

WITH
    radii AS (SELECT diameter_to_radius(diameter) AS radius FROM circles)
  SELECT radius FROM radii
    ORDER BY radius
  ;

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

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

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
  $$
  ;

SELECT store_profit();

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

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);

Utilisation des variables SQL dans une 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);

CREATE OR REPLACE FUNCTION my_filter_function()
RETURNS TABLE (id int)
AS
$$
SELECT id FROM table1 WHERE id > $id_threshold
$$
;

Fonctions mémoïsables¶

Pour des exemples, voir :

• Fonction mémoisable sans arguments dans une politique d’accès aux lignes.

• Fonction mémoisable avec des arguments dans une politique de masquage.