Fonctions de table¶
Une fonction de tableau renvoie un ensemble de lignes pour chaque ligne d’entrée. L’ensemble renvoyé peut contenir zéro, une ou plusieurs lignes. Chaque ligne peut contenir une ou plusieurs colonnes.
Les fonctions de table sont parfois appelées « fonctions tabulaires ».
Dans ce chapitre :
Que sont les fonctions de table ?¶
Les fonctions de table sont généralement utilisées lorsqu’une fonction renvoie plusieurs lignes pour chaque entrée individuelle.
Chaque fois qu’une fonction de table est appelée, elle peut renvoyer un nombre différent de lignes. Par exemple, une fonction record_high_temperatures_for_date(), qui renvoie une liste de températures maximales record pour une date donnée, peut renvoyer 0 ligne pour le mois d’avril 2010, 1 ligne pour le mois de juin 2010 et 40 lignes pour le mois d’août 2020.
Exemples simples de fonctions de table¶
Par exemple, les fonctions suivantes sont appropriées en tant que fonctions de table :
Une fonction qui accepte un numéro de compte et une date, et renvoie tous les frais facturés à ce compte à cette date. (Plus d’un frais peut avoir été facturé à une date donnée).
Une fonction qui accepte un ID utilisateur et renvoie les rôles de base de données attribués à cet utilisateur. (Un utilisateur peut avoir plusieurs rôles, notamment « sysadmin » et « useradmin »).
Fonctions dans lesquelles chaque ligne de sortie dépend de plusieurs lignes d’entrée¶
Les fonctions de table peuvent être regroupées en deux catégories en fonction du nombre de lignes d’entrée qui affectent chaque ligne de sortie :
1 à N
M à N
Les fonctions décrites précédemment sont des fonctions de table 1 à N : chaque ligne de sortie dépend d’une seule ligne d’entrée. Par exemple, une fonction record_high_temperatures_for_date() peut produire plusieurs lignes de sortie (une pour chaque ville ayant atteint un record à cette date). Chaque ligne de sortie pour une date d’entrée spécifique ne dépend que de cette date ; chaque ligne de sortie est indépendante des lignes pour toutes les autres dates.
Snowflake prend en charge également les fonctions de table M à N : chaque ligne de sortie peut dépendre de plusieurs lignes d’entrée. Par exemple, si une fonction génère une moyenne mobile des prix des actions, cette fonction utilise les prix des actions de plusieurs lignes d’entrée (plusieurs dates) pour générer chaque ligne de sortie.
De façon plus générale, dans une fonction M à N, un groupe de M lignes d’entrée produit un groupe de N lignes de sortie. M peut être une ou plusieurs lignes. N peut être zéro, une ou plusieurs lignes.
Par exemple, dans une moyenne mobile de 10 jours, M est 10. N est 1, car chaque groupe de 10 lignes d’entrée produit un prix moyen.
Fonctions de table intégrées et fonctions de table définies par l’utilisateur¶
Snowflake fournit des centaines de fonctions intégrées, dont beaucoup sont des fonctions de table. Les fonctions de table intégrées sont répertoriées dans des fonctions de table définies par le système.
Les utilisateurs peuvent également écrire leurs propres fonctions, appelées fonctions définies par l’utilisateur ou « UDFs ». Certaines UDFs sont scalaires, d’autres sont tabulaires. Les fonctions de table définies par l’utilisateur sont appelées « UDTFs ». Pour des informations sur les UDFs (y compris les UDTFs), voir Fonctions définies par l’utilisateur (UDFs).
Les fonctions de table intégrées et les fonctions de table définies par l’utilisateur suivent généralement les mêmes règles ; par exemple, elles sont appelées de la même manière à partir d’instructions SQL.
Utilisation d’une fonction de table¶
Utilisation d’une fonction de table dans la clause FROM¶
Une table contient un ensemble de lignes. Une fonction de table renvoie un ensemble de lignes. Les tables et les fonctions de table sont utilisées dans des contextes qui attendent un ensemble de lignes. Plus précisément, les fonctions de table sont utilisées dans la clause FROM d’une instruction SQL.
Pour aider le compilateur SQL à reconnaître une fonction de table comme une source de lignes, Snowflake exige que l’appel de la fonction de table soit encapsulé par le mot-clé TABLE().
Par exemple, l’instruction suivante appelle une fonction de table nommée record_high_temperatures_for_date(), qui prend une valeur DATE comme argument :
SELECT city_name, temperature FROM TABLE(record_high_temperatures_for_date('2021-06-27'::DATE)) ORDER BY city_name;
Pour plus d’informations sur la syntaxe de TABLE(), voir Littéraux de table.
Les fonctions de table, comme les fonctions en général, peuvent accepter zéro, un ou plusieurs arguments d’entrée à chaque appel. Chaque argument doit être une expression scalaire.
Pour plus de détails sur la syntaxe des appels de fonctions de table, voir Syntaxe (dans cette rubrique).
Utilisation d’une table comme entrée d’une fonction de table¶
L’argument d’une fonction de table peut être un littéral ou une expression, telle qu’une colonne d’une table. Par exemple, l’instruction SELECT ci-dessous transmet les valeurs d’une table comme arguments à une fonction de table :
CREATE OR REPLACE table dates_of_interest (event_date DATE);
INSERT INTO dates_of_interest (event_date) VALUES
('2021-06-21'::DATE),
('2022-06-21'::DATE);
CREATE OR REPLACE FUNCTION record_high_temperatures_for_date(d DATE)
RETURNS TABLE (event_date DATE, city VARCHAR, temperature NUMBER)
as
$$
SELECT d, 'New York', 65.0
UNION ALL
SELECT d, 'Los Angeles', 69.0
$$;
SELECT
doi.event_date as "Date",
record_temperatures.city,
record_temperatures.temperature
FROM dates_of_interest AS doi,
TABLE(record_high_temperatures_for_date(doi.event_date)) AS record_temperatures
ORDER BY doi.event_date, city;
+------------+-------------+-------------+
| Date | CITY | TEMPERATURE |
|------------+-------------+-------------|
| 2021-06-21 | Los Angeles | 69 |
| 2021-06-21 | New York | 65 |
| 2022-06-21 | Los Angeles | 69 |
| 2022-06-21 | New York | 65 |
+------------+-------------+-------------+
Les arguments d’une fonction de table peuvent provenir d’autres sources de type table, notamment de vues et d’autres fonctions de table.
Liste des fonctions de table définies par le système¶
Snowflake offre les fonctions de table suivantes définies par le système (par exemple intégrées) :
Sous-catégorie |
Fonction |
Remarques |
|---|---|---|
Chargement des données |
Pour plus d’informations, voir Chargement de données dans Snowflake. |
|
Génération de données |
||
Conversion des données |
||
Modélisation d’objet |
||
Requêtes semi-structurées |
Pour plus d’informations, voir Interrogation de données semi-structurées. |
|
Résultats de la requête |
Peut être utilisé pour effectuer des opérations SQL sur la sortie d’une autre opération SQL (par ex. SHOW). |
|
Historique et informations d’utilisation |
||
Connexion utilisateur
|
||
Requêtes
|
||
Disponible dans le cadre de l’avant-première du service Query Acceleration. |
||
Utilisation de l’entrepôt et du stockage
|
||
Sécurité au niveau des colonnes et au niveau des lignes
|
||
Balisage d’objets
|
Fonction de table Information Schema. |
|
Fonction de table Information Schema. |
||
Fonction de table Account Usage. |
||
Réplication des comptes
|
Pour plus d’informations, voir Réplication et basculement de compte |
|
REPLICATION_GROUP_REFRESH_PROGRESS, REPLICATION_GROUP_REFRESH_PROGRESS_BY_JOB |
||
Réplication de base de données
|
Pour plus d’informations, voir Réplication de bases de données sur plusieurs comptes. |
|
DATABASE_REFRESH_PROGRESS , DATABASE_REFRESH_PROGRESS_BY_JOB |
||
Chargement et transfert de données
|
||
Clustering des données (au sein des tables)
|
Pour plus d’informations, voir Clustering automatique. |
|
Fonctions externes
|
Pour plus d’informations, voir Fonctions externes. |
|
Tables externes
|
Pour plus d’informations, voir Travailler avec des tables externes. |
|
Maintenance des vues matérialisées
|
Pour plus d’informations, voir Travailler avec des vues matérialisées. |
|
Maintenance SCIM
|
Pour plus d’informations, voir Audit avec SCIM |
|
Maintenance de l’optimisation de la recherche
|
Pour plus d’informations, voir Utilisation du service d’optimisation de la recherche. |
|
Flux
|
Pour plus d’informations, voir Suivi des modifications à l’aide de flux de table. |
|
Tâches
|
Pour plus d’informations, voir Exécution d’instructions SQL sur une planification à l’aide de tâches. |
|
Syntaxe¶
SELECT ...
FROM [ <input_table> [ [AS] <alias_1> ] ,
[ LATERAL ]
]
TABLE( <table_function>( [ <arg_1> [, ... ] ] ) ) [ [ AS ] <alias_2> ];
Pour connaître la syntaxe spécifique à une fonction, reportez-vous à la documentation relative aux fonctions de table définies par le système.
Notes sur l’utilisation¶
Les fonctions de table peuvent également être appliquées à un ensemble de lignes à l’aide de la construction LATERAL.
Pour activer l’utilisation des expressions de table, Snowflake prend en charge la syntaxe standard ANSI/ISO pour les expressions de table dans la clause FROM des requêtes et sous-requêtes. Cette syntaxe est utilisée pour indiquer qu’une expression renvoie une collection de lignes au lieu d’une seule.
Cette syntaxe ANSI/ISO n’est valide que dans la clause FROM de la liste SELECT. Vous ne pouvez pas omettre ces mots-clés et parenthèses d’une spécification de sous-requête de collection dans un autre contexte.