Fonctions définies par l’utilisateur et procédures stockées

Automatisation des pipelines de données dans Snowflake

Emily Melhuish

Technical Curriculum Developer, Snowflake

Qu’est-ce qu’une UDF?

nanobanana: moitié: Deux icônes de cadenas abstraites côte à côte, l’un ouvert et l’autre fermé, design minimaliste plat, couleurs bleu marine et vert DataCamp, fond blanc, sans texte

Fonctions définies par l’utilisateur (UDF)

Opérations personnalisées au-delà des fonctions SQL intégrées
Écrites en SQL, Python, Java, Scala ou JavaScript
Pas de DDL ni de DML — renvoie seulement des valeurs
Standard ou SECURE (masque la logique aux appelants)
Renvoie une valeur scalaire par ligne, ou une table

UDF SQL

Point de départ recommandé

CREATE FUNCTION delivery_tier(days INT)
RETURNS VARCHAR AS $$
  CASE WHEN days <= 2 THEN 'Express'
       WHEN days <= 5 THEN 'Standard'
       ELSE 'Delayed' END $$;

UDF en Python et autres langages

Quand SQL ne suffit pas

CREATE FUNCTION parse_weight(label STRING)
RETURNS FLOAT LANGUAGE PYTHON
RUNTIME_VERSION = '3.9' HANDLER = 'parse'
AS $$ def parse(s): return float(s.split('kg')[0]) $$;

UDF dans le pipeline

mermaid: couche de transformation UDF dans le pipeline

Les UDF se situent dans la couche de transformation — entre le stockage brut et le reporting

Appelées dans SELECT; renvoient une valeur par ligne
Centralisent la logique d’affaires partagée par plusieurs requêtes
Ajouter SECURE pour masquer la définition aux autres utilisateurs

Qu’est-ce qu’une procédure stockée?

Déclarer des variables, boucler sur les résultats, gérer les erreurs
Exécuter du DML sur plusieurs tables
Appel autonome avec CALL

mermaid: datacamp-violet: flux d’orchestration d’une procédure stockée

Procédure stockée : Snowflake Scripting

CREATE OR REPLACE PROCEDURE archive_old_shipments(cutoff INT)
RETURNS STRING LANGUAGE SQL AS $$
DECLARE                                    -- 1. Declare variables
    rows_moved INT DEFAULT 0;
BEGIN                                      -- 2. Begin the procedure
    INSERT INTO shipments_archive          -- 3. Execute SQL commands
        SELECT * FROM shipments WHERE delivery_days > :cutoff;
    rows_moved := SQLROWCOUNT;
    DELETE FROM shipments WHERE delivery_days > :cutoff;
    RETURN 'Archived ' || rows_moved       -- 4. Return the result
        || ' shipments.';
END;                                       -- 5. End the procedure
$$;

UDF vs procédures stockées

	UDF	Procédure stockée
Appelée dans	`SELECT`	`CALL`
Renvoie	Une valeur par ligne	Valeur unique
Peut exécuter du DML	Non	Oui
Langages	SQL, Python, Java, JS	SQL (Snowflake Scripting), Python, Java, JS, Scala
Usage dans le pipeline	Transformer les données	Orchestrer la logique

Passons à la pratique !

Automatisation des pipelines de données dans Snowflake