Dans les derniers articles on a vu pas mal de briques de base de Snowflake :

Ici, on zoome sur un objet un peu moins connu et rarement utilisé mais super pratique dès que tu commences à factoriser ton code et c'est les UDTF (User-Defined Table Functions), et qu'on peut voir aussi comme des vues paramétrables pour simplifier.

C’est quoi concrètement une UDTF ( User-Defined Table Function )?

Une UDTF, c’est une fonction qu'on définit dans Snowflake, à laquelle on peut passer des paramètres en entrée, et qui nous renvoie des lignes comme une table ou une vue. On l'utilise dans le FROM avec TABLE(...).

La syntaxe de base : 

CREATE OR REPLACE FUNCTION ma_fonction(
    arg1 TYPE,
    arg2 TYPE
)
RETURNS TABLE (
    col1 TYPE,
    col2 TYPE
)
AS
$$
  SELECT ...
$$;

Et pour l'appeler :

SELECT *
FROM TABLE(ma_fonction(valeur1, valeur2));

Exemple d'une fonction UDTF

Comme d'habitude rien de mieux qu'un exemple pour comprendre le concept.

On crée une table orders :

CREATE OR REPLACE TABLE orders (
  order_id     NUMBER,
  order_date   DATE,
  customer_id  NUMBER,
  amount       NUMBER
);

On crée une UDTF qui renvoie les commandes d'une année donnée :

CREATE OR REPLACE FUNCTION f_orders_of_year(order_year NUMBER)
  RETURNS TABLE (
    order_id     NUMBER,
    order_date   DATE,
    customer_id  NUMBER,
    amount       NUMBER
  )
AS
$$
  SELECT
    order_id,
    order_date,
    customer_id,
    amount
  FROM orders
  WHERE EXTRACT(YEAR FROM order_date) = order_year
$$;

on l'utilise comme suite :

SELECT *
FROM TABLE(f_orders_of_year(2024));
  • f_orders_of_year(2023) → toutes les commandes 2023,
  • f_orders_of_year(2024) → toutes les commandes 2024, etc.

Même logique que si tu avais plusieurs vues : v_orders_2023, v_orders_2024, …
mais ici avec une seule fonction.

Exemple 2

On veut avoir une table qui contient que les commandes >1000. 

-- Vue classique
CREATE VIEW v_orders AS
SELECT * FROM orders WHERE amount > 1000;

SELECT * FROM v_orders;

-- UDTF (vue paramétrable)
CREATE FUNCTION f_orders_min_amount(min_amount NUMBER)
RETURNS TABLE (order_id NUMBER, amount NUMBER)
AS
$$
  SELECT order_id, amount
  FROM orders
  WHERE amount > min_amount
$$;

SELECT *
FROM TABLE(f_orders_min_amount(1000));

Utilisation de Python, JavaScript ou Java

Mes précédents exemples étaient uniquement en SQL, mais une UDTF n’est pas du tout limitée au SQL. Oui, tu as bien lu car Snowflake permet aussi de créer des fonctions de table en Python, JavaScript ou Java.

L'idée est la même que SQL sauf qu'on code la logique dans un langage diffèrent.

Exemple :

CREATE OR REPLACE FUNCTION f_split_words(text STRING)
  RETURNS TABLE (word STRING)
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.10'
  HANDLER = 'SplitWords'
AS
$$
class SplitWords:
  def process(self, text):
    if text is None:
      return
    for w in text.split():
      # chaque tuple correspond à une ligne retournée
      yield (w,)
$$;

L'utilisation reste la même que les exemples précédentes:

SELECT *
FROM TABLE(f_split_words('hello snowflake udtf'));
💡
Il y a quand même pas mal de limitations quand on utilise ces langages-là, donc je te recommande d'aller jeter un œil à la doc officielle Snowflake pour voir le détail.

Bonnes pratiques avec les UDTF

Une UDTF c'est comme une vue, elle ne stocke rien et est réévaluée à chaque requête. Donc elle n’est pas là pour optimiser les perfs comme une materialized view, mais surtout pour factoriser du SQL et éviter le copier-coller.

Attention à ne pas en faire des usines à gaz car il est recommandé de

  • Garder chaque fonction UDTF centrée sur un seul objectif (filtrage, agrégation ...), et de ne pas tout mettre dans une seule fonction
  • Donner un nom clair et bien documenter les fonctions

Concernant les droits, ça se gère comme une vue et on peut les créer en SECURE et jouer avec les GRANT pour exposer la fonction sans forcément ouvrir toutes les tables derrière.

Aller plus loin : Formation Snowflake

J’ai regroupé tous mes articles Snowflake dans un parcours complet.

👉 Accéder à la Formation Snowflake

Vous voulez que je vous accompagne sur votre projet data (Snowflake, ingestion, modélisation, performance, coûts, gouvernance) ?
👉 Réserver un appel de 30 minutes