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

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 avec la valeur correspondant au caractère générique.

Pour en savoir plus sur la syntaxe des tables génériques, consultez la section Tables génériques dans le document de référence SQL standard.

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. Cela est valable même si votre requête contient une clause WHERE sur la colonne pseudo _TABLE_SUFFIX pour filtrer la vue.
  • Actuellement, 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 natif. Vous ne pouvez pas utiliser de caractères génériques lorsque vous interrogez une table externe ou une vue.
  • 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.

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 natif. Vous ne pouvez pas utiliser de caractères génériques lorsque vous interrogez une table externe ou une vue.

Interroger des ensembles de tables avec des tables génériques

Les tables génériques vous permettent d'interroger plusieurs tables de manière concise. Par exemple, l'ensemble de données météorologiques GSOD (Global Surface Summary of the Day) de l'Agence américaine d'observation océanique et atmosphérique contient des tables annuelles qui partagent le préfixe commun gsod, suivi de l'année à quatre chiffres. Les tables sont nommées de la manière suivante : 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 des tables sélectionnées avec _TABLE_SUFFIX

Pour limiter la requête à l'analyse d'un ensemble arbitraire de tables, utilisez la colonne pseudo _TABLE_SUFFIX dans la clause WHERE. La colonne pseudo _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 colonne pseudo _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.

Les filtres sur _TABLE_SUFFIX qui incluent des sous-requêtes ne peuvent pas être utilisés pour limiter le nombre de tables analysées d'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* :

#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_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id LIKE 'gsod194%')

La requête suivante limite l'analyse selon la condition du filtre, _TABLE_SUFFIX BETWEEN '40' and '60', mais ne la limite pas selon la condition impliquant la sous-requête :

#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_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id LIKE 'gsod194%')

Pour appliquer des limitations selon la condition impliquant la sous-requête, vous pouvez effectuer deux requêtes distinctes.

Première requête :

#standardSQL
# Get the list of tables that match the required table name prefixes
SELECT SUBSTR(MAX(table_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id 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'

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 avec une date d'ingestion à l'aide de _PARTITIONTIME

Pour analyser plusieurs tables partitionnées avec une date d'ingestion, utilisez la colonne pseudo _PARTITIONTIME avec la colonne pseudo _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 colonne pseudo _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 colonne pseudo _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 en SQL standard 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. Si le schéma n'est pas cohérent entre les tables correspondant à la table générique, BigQuery affiche une erreur.

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

Étape suivante

Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…