Fonctions de table

Une fonction de table, également appelée fonction de valeur de table (table-valued function, TVF), est une fonction définie par l'utilisateur qui renvoie une table. Vous pouvez utiliser une fonction de table partout où vous pouvez utiliser une table. Les fonctions de table se comportent de la même manière que les vues, mais une fonction de table peut accepter des paramètres.

Créer des fonctions de table

Pour créer une fonction de table, utilisez l'instruction CREATE TABLE FUNCTION. Une fonction de table contient une requête qui génère une table. La fonction renvoie le résultat de la requête. La fonction de table suivante utilise un paramètre INT64 et utilise cette valeur dans une clause WHERE d'une requête sur un ensemble de données public appelé bigquery-public-data.usa_names.usa_1910_current :

CREATE OR REPLACE TABLE FUNCTION mydataset.names_by_year(y INT64)
AS (
  SELECT year, name, SUM(number) AS total
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  WHERE year = y
  GROUP BY year, name
);

Pour filtrer les données de différentes manières, vous pouvez transmettre plusieurs paramètres à une fonction de table. La fonction de table suivante filtre les données par année et par préfixe de nom :

CREATE OR REPLACE TABLE FUNCTION mydataset.names_by_year_and_prefix(
  y INT64, z STRING)
AS (
  SELECT year, name, SUM(number) AS total
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  WHERE
    year = y
    AND STARTS_WITH(name, z)
  GROUP BY year, name
);

Noms des paramètres

Si un paramètre de fonction de table correspond au nom d'une colonne de table, il peut créer une référence ambigu. Dans ce cas, BigQuery interprète le nom comme une référence à la colonne de la table, et non au paramètre. Il est recommandé d'utiliser des noms de paramètres distincts des noms des colonnes de table référencées.

Utiliser des fonctions de table

Vous pouvez appeler une fonction de table dans tous les contextes dans lesquels une table est valide. L'exemple suivant appelle la fonction mydataset.names_by_year dans la clause FROM d'une instruction SELECT :

SELECT * FROM mydataset.names_by_year(1950)
  ORDER BY total DESC
  LIMIT 5

Les résultats se présentent comme suit :

+------+--------+-------+
| year |  name  | total |
+------+--------+-------+
| 1950 | James  | 86447 |
| 1950 | Robert | 83717 |
| 1950 | Linda  | 80498 |
| 1950 | John   | 79561 |
| 1950 | Mary   | 65546 |
+------+--------+-------+

Vous pouvez joindre le résultat d'une fonction de table à une autre table :

SELECT *
  FROM `bigquery-public-data.samples.shakespeare` AS s
  JOIN mydataset.names_by_year(1950) AS n
  ON n.name = s.word

Vous pouvez également utiliser une fonction de table dans une sous-requête :

SELECT ARRAY(
  SELECT name FROM mydataset.names_by_year(1950)
  ORDER BY total DESC
  LIMIT 5)

Répertorier des fonctions de table

Les fonctions de table sont un type de routine. Pour répertorier toutes les routines d'un ensemble de données, consultez la page Répertorier des routines.

Supprimer des fonctions de table

Pour supprimer une fonction de table, utilisez l'instruction DROP TABLE FUNCTION :

DROP TABLE FUNCTION mydataset.names_by_year

Autoriser les routines

Vous pouvez autoriser les fonctions de table en tant que routines. Les routines autorisées vous permettent de partager des résultats de requête avec des utilisateurs ou des groupes spécifiques, sans leur donner accès aux tables sous-jacentes qui ont généré les résultats. Par exemple, une routine autorisée peut calculer une agrégation sur des données ou rechercher une valeur de table et utiliser cette valeur dans un calcul. Pour en savoir plus, consultez la section Routines autorisées.

Limites

Les paramètres doivent être des valeurs scalaires. Les fonctions de table dans BigQuery ne peuvent pas prendre les tables en tant que paramètres d'entrée.
Le corps de la requête doit être une instruction SELECT et ne peut rien modifier. Par exemple, les instructions LDD (langage de définition de données) et LMD (langage de manipulation de données) ne sont pas autorisées dans les fonctions de table. Si vous avez besoin d'effets secondaires, envisagez plutôt d'écrire une procédure.
Les fonctions de table doivent être stockées au même emplacement que les tables auxquelles elles font référence.

Quotas

Pour en savoir plus sur les quotas et les limites des fonctions de table, consultez la page Quotas et limites.