Tableaux génériques

Les tables génériques vous permettent d'interroger plusieurs tables à l'aide d'instructions SQL concises. Les tables génériques ne sont disponibles qu'en langage SQL standard. Pour des fonctionnalités équivalentes en ancien SQL, consultez la section Fonctions de caractères génériques de table.

Syntaxe des tables génériques

Syntaxe d'une table générique :

SELECT
FROM
  `<project-id>.<dataset-id>.<table-prefix>*`
WHERE
  bool_expression
<project-id>
ID du projet Cloud Platform. Facultatif si vous utilisez l'ID de votre projet par défaut.
<dataset-id>
ID d'ensemble de données BigQuery.
<table-prefix>
Chaîne commune à toutes les tables qui correspondent au caractère générique. Le préfixe de la table est facultatif. S'il est omis, toutes les tables de l'ensemble de données sont mises en correspondance.
* (caractère générique)
Le caractère générique "*" représente un ou plusieurs autres caractères du nom d'une table. Il ne peut apparaître que comme le dernier caractère d'un nom de table générique.

La clause WHERE de table générique accepte également la pseudo-colonne _TABLE_SUFFIX, qui contient les valeurs correspondant au caractère générique. Vous pouvez utiliser _TABLE_SUFFIX pour filtrer votre requête à l'aide d'un opérateur de comparaison. Par exemple, les clauses WHERE suivantes emploient des opérateurs de comparaison :

WHERE
  _TABLE_SUFFIX BETWEEN '29' AND '40'

WHERE
  _TABLE_SUFFIX = '1929'

WHERE
  _TABLE_SUFFIX < '1941'

Pour en savoir plus sur la pseudo-colonne _TABLE_SUFFIX, consultez la section Filtrer des tables sélectionnées avec _TABLE_SUFFIX.

Entourer les noms de tables génériques par des accents graves

Le nom de la table générique contient le caractère spécial (*), ce qui signifie que vous devez l'entourer par des accents graves (`). Par exemple, la requête suivante est valide car elle utilise des accents graves :

#standardSQL
/* Valid standard SQL query */
SELECT
  max
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

La requête suivante n'est PAS valide, car elle n'est pas correctement délimitée par des accents graves :

#standardSQL
/* Syntax error: Expected end of statement but got "-" at [4:11] */
SELECT
  max
FROM
  # missing backticks
  bigquery-public-data.noaa_gsod.gsod*
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Les guillemets ne fonctionnent pas :

#standardSQL
/* Syntax error: Unexpected string literal: 'bigquery-public-data.noaa_gsod.gsod*' at [4:3] */
SELECT
  max
FROM
  # quotes are not backticks
  'bigquery-public-data.noaa_gsod.gsod*'
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Migrer des fonctions de caractères génériques de table en ancien SQL

En ancien SQL, vous pouvez utiliser les fonctions de caractères génériques de table suivantes pour interroger plusieurs tables :

  • TABLE_DATE_RANGE() et TABLE_DATE_RANGE_STRICT()
  • TABLE_QUERY()

Fonctions TABLE_DATE_RANGE()

Les fonctions TABLE_DATE_RANGE() de l'ancien SQL peuvent être utilisées sur des tables respectant un schéma de nommage spécifique : <prefix>YYYYMMDD , où <prefix> représente la première partie d'un nom de table et YYYYMMDD la date associée aux données de la table.

Par exemple, la requête en ancien SQL suivante calcule la température moyenne à partir d'un ensemble de tables quotidiennes contenant les données météorologiques de la région de Seattle :

#legacySQL
SELECT
  ROUND(AVG(TemperatureF),1) AS AVG_TEMP_F
FROM
  TABLE_DATE_RANGE([mydataset.sea_weather_],
                    TIMESTAMP("2016-05-01"),
                    TIMESTAMP("2016-05-09"))

En SQL standard, une requête équivalente utilise un caractère générique de table ainsi que la clause BETWEEN.

#standardSQL
SELECT
  ROUND(AVG(TemperatureF),1) AS AVG_TEMP_F
FROM
  `mydataset.sea_weather_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20160501' AND '20160509'

Fonction TABLE_QUERY()

La fonction TABLE_QUERY() de l'ancien SQL permet de rechercher des noms de table basés sur des modèles. Lors de la migration d'une fonction TABLE_QUERY() vers le langage SQL standard, qui n'est pas compatible avec la fonction TABLE_QUERY(), vous pouvez filtrer les tables à l'aide de la pseudo-colonne _TABLE_SUFFIX. Prenez en compte les différences suivantes lors de la migration :

  • En ancien SQL, vous placez la fonction TABLE_QUERY() dans la clause FROM, tandis qu'en SQL standard, vous filtrez les tables en utilisant la pseudo-colonne _TABLE_SUFFIX dans la clause WHERE.

  • En ancien SQL, la fonction TABLE_QUERY() opère sur le nom complet de la table (ou table_id), tandis qu'en SQL standard, la pseudo-colonne _TABLE_SUFFIX contient tout ou partie du nom de la table, selon la façon dont vous utilisez le caractère générique.

Appliquer le filtre dans la clause WHERE

Lorsque vous passez de l'ancien SQL au langage SQL standard, vous devez déplacer le filtre vers la clause WHERE. Par exemple, la requête suivante recherche les températures maximales pour toutes les années se terminant par le chiffre 0 :

#legacySQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  year
FROM
  TABLE_QUERY([bigquery-public-data:noaa_gsod],
               'REGEXP_MATCH(table_id, r"0$")')
WHERE
  max != 9999.9 # code for missing data
  AND max > 100 # to improve ORDER BY performance
ORDER BY
  max DESC

En SQL standard, une requête équivalente utilise un caractère générique de table et place la fonction d'expression régulière REGEXP_CONTAINS() dans la clause WHERE :

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  max != 9999.9 # code for missing data
  AND max > 100 # to improve ORDER BY performance
  AND REGEXP_CONTAINS(_TABLE_SUFFIX, r"0$")
ORDER BY
  max DESC

Différences entre table_id et _TABLE_SUFFIX

Dans la fonction TABLE_QUERY(dataset, expr) de l'ancien SQL, le deuxième paramètre est une expression qui agit sur le nom complet de la table à l'aide de la valeur table_id. Lorsque vous passez au langage SQL standard, le filtre que vous créez dans la clause WHERE opère sur la valeur de _TABLE_SUFFIX, qui peut inclure tout ou partie du nom de la table, selon la façon dont vous utilisez le caractère générique.

Par exemple, la requête en ancien SQL suivante utilise le nom complet de la table pour rechercher les températures maximales pour toutes les années se terminant par le chiffre 0 :

#legacySQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  year
FROM
  TABLE_QUERY([bigquery-public-data:noaa_gsod],
               'REGEXP_MATCH(table_id, r"gsod\d{3}0")')
WHERE
  max != 9999.9 # code for missing data
  AND max > 100 # to improve ORDER BY performance
ORDER BY
  max DESC

En SQL standard, une requête équivalente peut employer le nom complet de la table ou seulement une partie du nom. Vous pouvez utiliser un préfixe vide en langage SQL standard pour que votre filtre agisse sur le nom complet de la table :

# Standard SQL empty prefix
FROM
  `bigquery-public-data.noaa_gsod.*`

Sachez toutefois que les préfixes plus longs fonctionnent mieux que les préfixes vides. L'exemple suivant utilise un préfixe long, ce qui signifie que la valeur de _TABLE_SUFFIX ne constitue qu'une partie du nom de la table.

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  max != 9999.9 # code for missing data
  AND max > 100 # to improve ORDER BY performance
  AND REGEXP_CONTAINS(_TABLE_SUFFIX, r"\d{3}0")
ORDER BY
  max DESC
Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.