Interroger plusieurs tables avec une table générique

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

Une table générique représente l'union de toutes les tables qui correspondent à l'expression générique. Par exemple, la clause FROM suivante utilise l'expression générique gsod* pour faire correspondre toutes les tables de l'ensemble de données noaa_gsod commençant par la chaîne gsod.

FROM
  `bigquery-public-data.noaa_gsod.gsod*`

Chaque ligne de la table générique contient une colonne spéciale, _TABLE_SUFFIX, avec la valeur correspondant au caractère générique.

Limites

Les requêtes de table génériques sont soumises aux limites suivantes :

  • La fonctionnalité de table générique n'est pas compatible avec les vues. Si la table générique correspond à une vue dans l'ensemble de données, la requête affiche une erreur même si votre requête contient une clause WHERE sur la pseudo-colonne _TABLE_SUFFIX pour filtrer la vue.
  • Les résultats mis en cache ne sont pas compatibles avec les requêtes sur plusieurs tables utilisant un caractère générique, même si l'option Utiliser les résultats mis en cache est cochée. Si vous exécutez plusieurs fois la même requête générique, chaque requête est facturée sur votre compte.
  • Les tables génériques ne sont compatibles qu'avec le stockage BigQuery intégré. Vous ne pouvez pas utiliser de caractères génériques pour interroger une table externe ou une vue.
  • Vous ne pouvez pas utiliser de requêtes génériques sur des tables avec un partitionnement incompatible ou une combinaison de tables partitionnées et non partitionnées. Les tables interrogées doivent également présenter des spécifications de clustering identiques.
  • Vous pouvez utiliser des tables génériques avec des tables partitionnées, et l'élimination des partitions et l'élimination des clusters sont tous deux disponibles. Toutefois, les tables qui sont en cluster, mais non partitionnées, ne bénéficient pas de l'élimination des clusters grâce à l'utilisation de caractères génériques.
  • Les requêtes contenant des instructions LMD (langage de manipulation de données) ne peuvent pas utiliser une table générique comme cible de la requête. Par exemple, une table générique peut être utilisée dans la clause FROM d'une requête UPDATE, mais ne peut pas être utilisée comme cible de l'opération UPDATE.
  • Les filtres sur les pseudo-colonnes _TABLE_SUFFIX ou _PARTITIONTIME qui incluent des fonctions JavaScript définies par l'utilisateur ne limitent pas le nombre de tables analysées dans une table générique.
  • Les requêtes génériques ne sont pas acceptées pour les tables protégées par des clés de chiffrement gérées par le client (CMEK).
  • Toutes les tables référencées dans une requête générique doivent avoir exactement le même ensemble de valeurs et de clés de tag.

  • Lorsque vous utilisez des tables génériques, toutes les tables de l'ensemble de données qui commencent par le nom de la table figurant avant * sont analysées, même si _TABLE_SUFFIX est utilisé en combinaison avec REGEXP_CONTAINS et reçoit une expression régulière telle que ^[0-9]{2}$. Exemple :

    SELECT *
FROM `my_project.my_dataset.my_table_*`
WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, '^[0-9]{2}$');

  • Si une seule table analysée présente une incompatibilité de schéma (c'est-à-dire qu'une colonne du même nom est d'un type différent), la requête échoue avec l'erreur Cannot read field of type X as Y Field: column_name (Impossible de lire le champ de type X en tant que champ Y : nom_de_colonne). Toutes les tables sont mises en correspondance même si vous utilisez l'opérateur d'égalité =. Par exemple, dans la requête suivante, la table my_dataset.my_table_03_backup est également analysée. Par conséquent, la requête peut échouer en raison d'une incompatibilité de schéma. Toutefois, en cas d'incompatibilité de schéma, les résultats proviennent uniquement de la table my_dataset.my_table_03, comme attendu. 

    SELECT *
FROM my_project.my_dataset.my_table_*
WHERE _TABLE_SUFFIX = '03'

Avant de commencer

Quand utiliser les tables de caractères génériques ?

Les tables de caractères génériques sont utiles lorsqu'un ensemble de données contient plusieurs tables dotées du même nom avec des schémas compatibles. En règle générale, ces ensembles de données contiennent des tables qui représentent chacune des données pour un jour, un mois ou une année. Par exemple, le résumé des données météorologiques journalières mondiales collectées par l'Agence américaine d'observation océanique et atmosphérique est un ensemble de données public, hébergé par BigQuery, qui contient une table pour chaque année, de 1929 à aujourd'hui.

Effectuer une requête pour analyser tous les ID de table de 1929 à 1940 serait une opération très longue. Elle nécessiterait de nommer les 12 tables de la clause FROM (la plupart des tables sont omises dans cet exemple) :

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM (
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1929` UNION ALL
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1930` UNION ALL
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1931` UNION ALL

  # ... Tables omitted for brevity

  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1940` )
WHERE
  max != 9999.9 # code for missing data
ORDER BY
  max DESC

Avec une table générique, la même requête apparaît beaucoup plus concise :

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '29'
  AND '40'
ORDER BY
  max DESC
Les tables génériques ne sont compatibles qu'avec le stockage BigQuery intégré. Vous ne pouvez pas utiliser de caractères génériques lorsque vous interrogez une table externe ou une vue.

Syntaxe des tables de caractères 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 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.

Les requêtes avec des tables génériques acceptent la pseudo-colonne _TABLE_SUFFIX dans la clause WHERE. Cette colonne contient les valeurs correspondant au caractère générique, afin que les requêtes puissent filtrer les tables accessibles. Par exemple, les clauses WHERE suivantes emploient des opérateurs de comparaison pour filtrer les tables correspondantes :

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.

Encadrer les noms de tables génériques avec 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 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

Interroger des tables en utilisant des tables génériques

Les tables génériques vous permettent d'interroger plusieurs tables de manière concise. Par exemple, le résumé des données météorologiques journalières mondiales (GSOD) de l'Agence américaine d'observation océanique et atmosphérique est un ensemble de données public hébergé par BigQuery, qui contient une table pour chaque année, de 1929 à aujourd'hui. Chaque table comporte le même préfixe gsod suivi de quatre chiffres représentant l'année. Les tables sont nommées gsod1929, gsod1930, gsod1931, etc.

Pour interroger un groupe de tables qui partagent un préfixe commun, indiquez le symbole générique de table (*) après le préfixe de table dans votre instruction FROM. Par exemple, la requête suivante recherche les températures maximales enregistrées dans les années 1940 :

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod194*`
WHERE
  max != 9999.9 # code for missing data
ORDER BY
  max DESC

Filtrer les tables sélectionnées avec _TABLE_SUFFIX

Pour limiter une requête afin qu'elle n'analyse qu'un ensemble de tables spécifique, utilisez la pseudo-colonne _TABLE_SUFFIX dans une clause WHERE avec une condition qui est une expression constante.

La pseudo-colonne _TABLE_SUFFIX contient les valeurs qui correspondent au caractère générique de la table. Ainsi, l'exemple de requête précédent qui analyse toutes les tables des années 1940 utilise un caractère générique de table pour représenter le dernier chiffre de l'année :

FROM
  `bigquery-public-data.noaa_gsod.gsod194*`

La pseudo-colonne _TABLE_SUFFIX correspondante contient des valeurs comprises entre 0 et 9, représentant les tables gsod1940 à gsod1949. Ces valeurs _TABLE_SUFFIX peuvent être utilisées dans la clause WHERE pour filtrer des tables spécifiques.

Par exemple, pour filtrer les températures maximales enregistrées dans les années 1940 et 1944, utilisez les valeurs 0 et 4 pour _TABLE_SUFFIX :

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod194*`
WHERE
  max != 9999.9 # code for missing data
  AND ( _TABLE_SUFFIX = '0'
    OR _TABLE_SUFFIX = '4' )
ORDER BY
  max DESC

L'utilisation de _TABLE_SUFFIX peut considérablement réduire le nombre d'octets analysés, réduisant ainsi le coût d'exécution de vos requêtes.

Toutefois, les filtres sur _TABLE_SUFFIX qui incluent des conditions sans expression constante ne limitent pas le nombre de tables analysées dans une table générique. Par exemple, la requête suivante ne limite pas les tables analysées de la table générique bigquery-public-data.noaa_gsod.gsod19*, car le filtre utilise la valeur dynamique de la colonne table_id :

#standardSQL
# Scans all tables that match the prefix `gsod19`
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

Autre exemple, la requête suivante limite l'analyse selon la première condition de filtre, _TABLE_SUFFIX BETWEEN '40' and '60', car il s'agit d'une expression constante. Toutefois, la requête suivante ne limite pas l'analyse en fonction de la deuxième condition de filtre, _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1) FROM bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'gsod194%'), car il s'agit d'une expression dynamique :

#standardSQL
# Scans all tables with names that fall between `gsod1940` and `gsod1960`
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  _TABLE_SUFFIX BETWEEN '40' AND '60'
  AND _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

Pour contourner le problème, vous pouvez exécuter deux requêtes distinctes. Exemple :

Première requête :

#standardSQL
# Get the list of tables that match the required table name prefixes
SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%'

Deuxième requête :

#standardSQL
# Construct the second query based on the values from the first query
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX = '49'

Ces exemples de requêtes utilisent la vue INFORMATION_SCHEMA.TABLES. Pour en savoir plus sur la table INFORMATION_SCHEMA, consultez la section Obtenir des métadonnées de table à l'aide de INFORMATION_SCHEMA.

Analyser plusieurs tables avec _TABLE_SUFFIX

Pour analyser plusieurs tables, utilisez la colonne pseudo _TABLE_SUFFIX et la clause BETWEEN. Par exemple, pour trouver les températures maximales enregistrées entre 1929 et 1935 comprise, utilisez le caractère générique de la table pour représenter les deux derniers chiffres de l'année :

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '29' and '35'
ORDER BY
  max DESC

Analyser plusieurs tables partitionnées par date d'ingestion avec _PARTITIONTIME

Pour analyser plusieurs tables partitionnées par date d'ingestion, utilisez la pseudo-colonne _PARTITIONTIME avec la pseudo-colonne _TABLE_SUFFIX. Par exemple, la requête suivante analyse la partition du 1er janvier 2017 dans la table my_dataset.mytable_id1.

#standardSQL
SELECT
  field1,
  field2,
  field3
FROM
  `my_dataset.mytable_*`
WHERE
  _TABLE_SUFFIX = 'id1'
  AND _PARTITIONTIME = TIMESTAMP('2017-01-01')

Interroger toutes les tables d'un ensemble de données

Pour analyser toutes les tables d'un ensemble de données, vous pouvez utiliser un préfixe vide et le caractère générique de la table, ce qui signifie que la pseudo-colonne _TABLE_SUFFIX contient les noms de table complets. Par exemple, la clause FROM suivante analyse toutes les tables de l'ensemble de données GSOD :

FROM
  `bigquery-public-data.noaa_gsod.*`

Avec un préfixe vide, la pseudo-colonne _TABLE_SUFFIX contient des noms de table complets. Par exemple, la requête suivante est équivalente à l'exemple précédent qui permet de trouver les températures maximales enregistrées dans les années 1929 à 1935, mais elle utilise des noms de table complets dans la clause WHERE :

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN 'gsod1929' and 'gsod1935'
ORDER BY
  max DESC

Toutefois, veuillez prendre en compte que les préfixes plus longs fonctionnent généralement mieux. Pour en savoir plus, consultez la section Bonnes pratiques.

Détails de l'exécution des requêtes

Schéma utilisé pour l'évaluation des requêtes

Pour exécuter une requête GoogleSQL avec une table générique, BigQuery déduit automatiquement le schéma de cette table. BigQuery utilise le schéma de la dernière table créée correspondant au caractère générique en tant que schéma pour la table générique. Même si vous limitez le nombre de tables que vous souhaitez utiliser à partir de la table générique à l'aide de la pseudo-colonne _TABLE_SUFFIX dans une clause WHERE, BigQuery utilise le schéma de la table créée le plus récemment qui correspond au caractère générique.

Si une colonne du schéma obtenu n'existe pas dans une table correspondante, BigQuery renvoie les valeurs NULL de cette colonne dans les lignes de la table qui ne contient pas la colonne.

Si le schéma n'est pas cohérent entre les tables correspondant à la requête générique, BigQuery affiche une erreur. C'est le cas lorsque les colonnes des tables correspondant à la requête générique ont des types de données différents, ou lorsque les colonnes qui ne sont pas présentes dans toutes les tables correspondant à la requête générique ne peuvent pas être considérées comme ayant une valeur nulle.

Bonnes pratiques

  • Les préfixes plus longs fonctionnent généralement mieux que les préfixes plus courts. Par exemple, la requête suivante utilise un préfixe long (gsod200) :

    #standardSQL
SELECT
max
FROM
`bigquery-public-data.noaa_gsod.gsod200*`
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX BETWEEN '0' AND '1'
ORDER BY
max DESC

    La requête suivante fonctionne généralement moins bien, car elle utilise un préfixe vide :

    #standardSQL
SELECT
max
FROM
`bigquery-public-data.noaa_gsod.*`
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX BETWEEN 'gsod2000' AND 'gsod2001'
ORDER BY
max DESC

  • Nous recommandons le partitionnement plutôt que la segmentation car les tables partitionnées sont plus performantes. La segmentation réduit les performances tout en créant davantage de tables à gérer. Pour en savoir plus, consultez la section Partitionnement et segmentation.

Pour connaître les bonnes pratiques en matière de contrôle des coûts dans BigQuery, consultez la page Contrôler les coûts dans BigQuery.

Étapes suivantes