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 obtenir des informations sur les UDFs (y compris les UDTFs), voir Vue d’ensemble des fonctions définies par l’utilisateur.

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

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

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

INFER_SCHEMA

Pour plus d’informations, reportez-vous à Chargement de données dans Snowflake.

VALIDATE

Génération de données

GENERATOR

Conversion des données

SPLIT_TO_TABLE

STRTOK_SPLIT_TO_TABLE

Analyse alimentée par ML

TOP_INSIGHTS

Pour plus d’informations, reportez-vous à Fonctions basées sur Cortex Snowflake ML.

Modélisation d’objet

GET_OBJECT_REFERENCES

Requêtes semi-structurées

FLATTEN

Pour plus d’informations, reportez-vous à Interrogation de données semi-structurées.

Résultats de la requête

RESULT_SCAN

Peut être utilisé pour effectuer des opérations SQL sur la sortie d’une autre opération SQL (par ex. SHOW).

Profil de requête

GET_QUERY_OPERATOR_STATS

Historique et informations d’utilisation

(Schéma d’information de Snowflake, Account Usage):

Connexion utilisateur

LOGIN_HISTORY , LOGIN_HISTORY_BY_USER

Requêtes

QUERY_HISTORY , QUERY_HISTORY_BY_*

QUERY_ACCELERATION_HISTORY

Pour plus d’informations, reportez-vous à Utilisation du service Query Acceleration.

Utilisation de l’entrepôt et du stockage

DATABASE_STORAGE_USAGE_HISTORY

WAREHOUSE_LOAD_HISTORY

WAREHOUSE_METERING_HISTORY

STAGE_STORAGE_USAGE_HISTORY

Sécurité au niveau des colonnes et au niveau des lignes

POLICY_REFERENCES

Balisage d’objets

TAG_REFERENCES

Fonction de table Information Schema.

TAG_REFERENCES_ALL_COLUMNS

Fonction de table Information Schema.

TAG_REFERENCES_WITH_LINEAGE

Fonction de table Account Usage.

Réplication des comptes

REPLICATION_GROUP_REFRESH_HISTORY

Pour plus d’informations, reportez-vous à Présentation de la réplication et du basculement à travers plusieurs comptes

REPLICATION_GROUP_REFRESH_PROGRESS, REPLICATION_GROUP_REFRESH_PROGRESS_BY_JOB

REPLICATION_GROUP_USAGE_HISTORY

Réplication de base de données

DATABASE_REFRESH_HISTORY

Pour plus d’informations, reportez-vous à Réplication de bases de données sur plusieurs comptes.

DATABASE_REFRESH_PROGRESS , DATABASE_REFRESH_PROGRESS_BY_JOB

DATABASE_REPLICATION_USAGE_HISTORY

Chargement et transfert de données

COPY_HISTORY

DATA_TRANSFER_HISTORY

PIPE_USAGE_HISTORY

STAGE_DIRECTORY_FILE_REGISTRATION_HISTORY

VALIDATE_PIPE_LOAD

Clustering des données (au sein des tables)

AUTOMATIC_CLUSTERING_HISTORY

Pour plus d’informations, reportez-vous à Clustering automatique.

Tables dynamiques

DYNAMIC_TABLE_GRAPH_HISTORY

Pour plus d’informations, reportez-vous à À propos de l’utilisation des tables dynamiques.

DYNAMIC_TABLE_REFRESH_HISTORY

Fonctions externes

EXTERNAL_FUNCTIONS_HISTORY

Pour plus d’informations, reportez-vous à Écriture de fonctions externes.

Tables externes

AUTO_REFRESH_REGISTRATION_HISTORY

Pour plus d’informations, reportez-vous à Travailler avec des tables externes.

EXTERNAL_TABLE_FILES

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY

Maintenance des vues matérialisées

MATERIALIZED_VIEW_REFRESH_HISTORY

Pour plus d’informations, reportez-vous à Travailler avec des vues matérialisées.

Notifications

NOTIFICATION_HISTORY

Pour plus d’informations, reportez-vous à Envoi de notifications par e-mail.

Maintenance SCIM

REST_EVENT_HISTORY

Pour plus d’informations, reportez-vous à Audit avec SCIM

Maintenance de l’optimisation de la recherche

SEARCH_OPTIMIZATION_HISTORY

Pour plus d’informations, reportez-vous à Service d’optimisation de la recherche.

Flux

SYSTEM$STREAM_BACKLOG

Pour plus d’informations, reportez-vous à Suivi des modifications à l’aide de flux de table.

Tâches

COMPLETE_TASK_GRAPHS

Pour plus d’informations, reportez-vous à Exécution d’instructions SQL sur une planification à l’aide de tâches.

CURRENT_TASK_GRAPHS

SERVERLESS_TASK_HISTORY

TASK_DEPENDENTS

TASK_HISTORY

Syntaxe

SELECT ...
  FROM [ <input_table> [ [AS] <alias_1> ] ,
         [ LATERAL ]
       ]
       TABLE( <table_function>( [ <arg_1> [, ... ] ] ) ) [ [ AS ] <alias_2> ];
Copy

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.