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 autrement, 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
);

Paramètres de tableau

Vous pouvez définir des paramètres de TVF comme tables. Après le nom du paramètre de table, vous devez spécifier explicitement le schéma de table requis, de la même manière que vous spécifiez les champs d'une structure. L'argument de table que vous transmettez à la TVF peut contenir des colonnes supplémentaires en plus de celles spécifiées dans le schéma de paramètre, et les colonnes peuvent apparaître dans n'importe quel ordre.

La fonction de table suivante renvoie une table contenant le total des ventes pour item_name à partir de la table orders :

CREATE TABLE FUNCTION mydataset.compute_sales (
  orders TABLE<sales INT64, item STRING>, item_name STRING)
AS (
  SELECT SUM(sales) AS total_sales, item
  FROM orders
  WHERE item = item_name
  GROUP BY item
);

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)

Lorsque vous appelez une fonction de table comportant un paramètre de table, vous devez utiliser le mot clé TABLE avant le nom de l'argument de table. L'argument de table peut comporter des colonnes qui ne sont pas listées dans le schéma du paramètre de table :

CREATE TABLE FUNCTION mydataset.compute_sales (
  orders TABLE<sales INT64, item STRING>, item_name STRING)
AS (
  SELECT SUM(sales) AS total_sales, item
  FROM orders
  WHERE item = item_name
  GROUP BY item
);

WITH my_orders AS (
    SELECT 1 AS sales, "apple" AS item, 0.99 AS price
    UNION ALL
    SELECT 2, "banana", 0.49
    UNION ALL
    SELECT 5, "apple", 0.99)
SELECT *
FROM mydataset.compute_sales(TABLE my_orders, "apple");

/*-------------+-------+
 | total_sales | item  |
 +-------------+-------+
 | 6           | apple |
 +-------------+-------*/

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

  • 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.