Obtenir des métadonnées de tâche à l'aide de INFORMATION_SCHEMA

INFORMATION_SCHEMA est une série de vues donnant accès aux métadonnées sur des ensembles de données, des routines, des tables, des vues, des tâches et des réservations.

Vous pouvez récupérer les métadonnées en temps réel des tâches BigQuery en interrogeant la vue INFORMATION_SCHEMA.JOBS_BY_*. Cette vue contient les tâches en cours d'exécution, ainsi que l'historique des tâches terminées des 180 derniers jours.

Autorisations requises

La récupération des métadonnées de tâche à l'aide des tables INFORMATION_SCHEMA nécessite des autorisations dotées de niveaux d'accès appropriés :

  • JOBS_BY_USER nécessite l'autorisation bigquery.jobs.list pour le projet et est disponible pour les rôles Project Viewer et BigQuery User.
  • JOBS_BY_PROJECT nécessite l'autorisation bigquery.jobs.listAll pour le projet et est disponible pour les rôles Project Owner et BigQuery Admin.
  • JOBS_BY_ORGANIZATION nécessite l'autorisation bigquery.jobs.listAll pour l'organisation et est disponible pour les rôles Organization bigquery.resourceAdmin, Organization Owner et Organization Admin. Sachez que JOBS_BY_ORGANIZATION n'est disponible que pour les utilisateurs associés à des organisations Google Cloud définies.

Pour plus d'informations sur les autorisations BigQuery précises, consultez la section Rôles et autorisations.

Schéma

Lorsque vous interrogez les vues INFORMATION_SCHEMA.JOBS_BY_*, les résultats de la requête contiennent une ligne pour chaque tâche BigQuery.

  • INFORMATION_SCHEMA.JOBS_BY_USER ne renvoie que les tâches envoyées par l'utilisateur actuel dans le projet en cours.
  • INFORMATION_SCHEMA.JOBS_BY_PROJECT renvoie toutes les tâches envoyées dans le projet en cours.
  • INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION renvoie toutes les tâches envoyées au sein de l'organisation associée au projet en cours.

La vue INFORMATION_SCHEMA.JOBS_BY_* présente le schéma suivant :

Nom de la colonne Type de données Valeur
creation_time TIMESTAMP (Colonne de partitionnement) Heure de création de la tâche. Le partitionnement est basé sur l'heure UTC de l'horodatage.
project_id STRING (Colonne de mise en cluster) ID du projet.
project_number INTEGER Numéro du projet
user_email STRING (Colonne de mise en cluster) Adresse e-mail ou compte de service de l'utilisateur ayant exécuté la tâche.
job_id STRING ID de la tâche. Par exemple, "bquxjob_1234".
job_type STRING Type de la tâche. Il peut s'agir de QUERY, LOAD, EXTRACT, COPY ou null. Le type de tâche null indique une tâche interne, telle que l'évaluation d'une instruction de tâche de script ou l'actualisation d'une vue matérialisée.
statement_type STRING Type d'instruction de requête, si celui-ci est valide. Par exemple, SELECT, INSERT, UPDATE ou DELETE.
start_time TIMESTAMP Heure de début de la tâche
end_time TIMESTAMP Heure de fin de la tâche
query STRING Texte de requête SQL. Remarque : La vue JOBS_BY_ORGANIZATION ne contient pas de colonne query.
state STRING État de fonctionnement de la tâche. Les états valides sont les suivants : PENDING, RUNNING et DONE.
reservation_id STRING Nom de la réservation principale attribuée à cette tâche, le cas échéant.
total_bytes_processed INTEGER Nombre total d'octets traités par la tâche
total_slot_ms INTEGER Emplacements de millisecondes pour la tâche sur toute sa durée.
error_result RECORD Détails de l'erreur (le cas échéant) en tant que ErrorProto
cache_hit BOOLEAN Indique si les résultats de la requête ont été mis en cache.
destination_table RECORD Table de destination des résultats (le cas échéant)
referenced_tables RECORD Tableau de tables référencées par la tâche
labels RECORD Tableau de libellés appliqués à la tâche sous la forme de chaînes key et value
timeline RECORD Chronologie de la requête de la tâche. Contient des instantanés de l'exécution des requêtes.
job_stages RECORD Étapes de la requête pour la tâche.

Conservation des données

Actuellement, seuls les 180 derniers jours de l'historique des tâches sont conservés dans les vues des tâches INFORMATION_SCHEMA.

Régionalité

Les vues des tâches INFORMATION_SCHEMA de BigQuery sont régionalisées. Pour interroger ces vues, vous devez ajouter un préfixe au nom de région pertinent en respectant le format suivant : `region-region_name`.INFORMATION_SCHEMA.view.

Exemple :

  • Pour interroger des données dans l'emplacement multirégional "US", utilisez `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.
  • Pour interroger des données dans l'emplacement multirégional "EU", utilisez `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.
  • Pour interroger des données dans la région asia-northeast1, utilisez `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Pour plus d'informations, consultez la liste des régions disponibles.

Exemples

Exemple 1 : Utilisation moyenne des emplacements

Dans l'exemple suivant, on calcule l'utilisation moyenne des emplacements pour toutes les requêtes effectuées au cours des sept derniers jours sur un projet donné. Notez que ce calcul est plus précis pour les projets dont l'utilisation des emplacements est cohérente pendant la semaine. Si l'utilisation des emplacements n'est pas cohérente dans votre projet, ce nombre peut être inférieur à celui attendu.

Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet en respectant le format suivant : `project_id`.`region-region_name`.INFORMATION_SCHEMA.view. Exemple : `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Pour exécuter la requête :

Console

  1. Dans Cloud Console, ouvrez l'interface utilisateur Web de BigQuery.

    Accéder à Cloud Console

  2. Dans la zone Éditeur de requête, saisissez la requête en SQL standard suivante. INFORMATION_SCHEMA requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans Cloud Console.

    SELECT
     SUM(total_slot_ms) / (1000*60*60*24*7) AS avg_slots
    FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
    WHERE
     -- Filter by the partition column first to limit the amount of data scanned. Eight days
     -- allows for jobs created before the 7 day end_time filter.
     creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY) AND CURRENT_TIMESTAMP()
     AND job_type = "QUERY"
     AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()
    
  3. Cliquez sur Run (Exécuter).

bq

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'option --nouse_legacy_sql ou --use_legacy_sql=false. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA.

Pour exécuter la requête, saisissez :

bq query --nouse_legacy_sql \
'SELECT
   SUM(total_slot_ms) / (1000*60*60*24*7) AS avg_slots
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE
   -- Filter by the partition column first to limit the amount of data scanned. Eight days
   -- allows for jobs created before the 7 day end_time filter.
   creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY) AND CURRENT_TIMESTAMP()
   AND job_type = "QUERY"
   AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()'

Les résultats doivent se présenter sous la forme suivante :

  +------------+
  | avg_slots  |
  +------------+
  | 3879.1534  |
  +------------+
  

Vous pouvez vérifier l'utilisation d'une réservation spécifique à l'aide de WHERE reservation_id = "…". Cela peut vous aider à déterminer le pourcentage d'utilisation d'une réservation sur une période donnée.

Si vous préférez vérifier l'utilisation moyenne des emplacements pour des tâches individuelles, utilisez total_slot_ms / TIMESTAMP_DIFF(end_time,start_time, MILLISECOND).

Exemple 2 : Historique des tâches de chargement

Dans l'exemple suivant, on répertorie tous les utilisateurs ou comptes de service ayant envoyé une tâche de chargement par lot sur un projet donné. Comme aucune limite de temps n'est spécifiée, cette requête analyse l'intégralité de l'historique disponible (par exemple, les 30 derniers jours).

Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet en respectant le format suivant : `project_id`.`region-region_name`.INFORMATION_SCHEMA.view. Exemple : `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Pour exécuter la requête :

Console

  1. Dans Cloud Console, ouvrez l'interface utilisateur Web de BigQuery.

    Accéder à Cloud Console

  2. Saisissez la requête SQL standard suivante dans la zone Éditeur de requête. INFORMATION_SCHEMA requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans Cloud Console.

    SELECT
     DISTINCT(user_email) AS user
    FROM
     `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
    WHERE
     job_type = "LOAD"
    
  3. Cliquez sur Run (Exécuter).

bq

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'option --nouse_legacy_sql ou --use_legacy_sql=false. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA.

Pour exécuter la requête, saisissez :

bq query --nouse_legacy_sql \
'SELECT
   DISTINCT(user_email) AS user
 FROM
   `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE
   job_type = "LOAD"'

Les résultats doivent se présenter sous la forme suivante :

  +--------------+
  | user         |
  +--------------+
  | abc@xyz.com  |
  +--------------+
  | def@xyz.com  |
  +--------------+
  

Exemple 3 : Tâches les plus onéreuses

L'exemple suivant montre comment localiser les cinq tâches ayant analysé le plus d'octets au sein d'une organisation sur la journée en cours. Vous pouvez appliquer davantage de filtres sur statement_type pour rechercher des informations supplémentaires, telles que des chargements, des exportations et des requêtes.

Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet en respectant le format suivant : `project_id`.`region-region_name`.INFORMATION_SCHEMA.view. Exemple : `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Pour exécuter la requête :

Console

  1. Dans Cloud Console, ouvrez l'interface utilisateur Web de BigQuery.

    Accéder à Cloud Console

  2. Dans la zone Éditeur de requête, saisissez la requête en SQL standard suivante. INFORMATION_SCHEMA requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans Cloud Console.

    SELECT
     job_id,
     user_email,
     total_bytes_processed
    FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
    WHERE EXTRACT(DATE FROM  creation_time) = current_date()
    ORDER BY total_bytes_processed DESC
    LIMIT 5
    
  3. Cliquez sur Run (Exécuter).

bq

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'option --nouse_legacy_sql ou --use_legacy_sql=false. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA.

Pour exécuter la requête, saisissez :

bq query --nouse_legacy_sql \
'SELECT
   job_id,
   user_email,
   total_bytes_processed
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
 WHERE EXTRACT(DATE FROM  creation_time) = current_date()
 ORDER BY total_bytes_processed DESC
 LIMIT 5'

Les résultats doivent se présenter sous la forme suivante :

  +--------------+--------------+---------------------------+
  | job_id       |  user_email  |  total_bytes_processed    |
  +--------------+--------------+---------------------------+
  | bquxjob_1    |  abc@xyz.com |    999999                 |
  +--------------+--------------+---------------------------+
  | bquxjob_2    |  def@xyz.com |    888888                 |
  +--------------+--------------+---------------------------+
  | bquxjob_3    |  ghi@xyz.com |    777777                 |
  +--------------+--------------+---------------------------+
  | bquxjob_4    |  jkl@xyz.com |    666666                 |
  +--------------+--------------+---------------------------+
  | bquxjob_5    |  mno@xyz.com |    555555                 |
  +--------------+--------------+---------------------------+
  

Exemple 4 : Tâches en attente et en cours d'exécution

Dans l'exemple suivant, on répertorie les tâches qui ont été démarrées par l'utilisateur actuel et qui sont actuellement en attente ou en cours d'exécution.

Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet en respectant le format suivant : `project_id`.`region-region_name`.INFORMATION_SCHEMA.view. Exemple : `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Pour exécuter la requête :

Console

  1. Dans Cloud Console, ouvrez l'interface utilisateur Web de BigQuery.

    Accéder à Cloud Console

  2. Dans la zone Éditeur de requête, saisissez la requête en SQL standard suivante. INFORMATION_SCHEMA requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans Cloud Console.

    SELECT
     job_id,
     creation_time,
     query
    FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
    WHERE state != "DONE"
    
  3. Cliquez sur Run (Exécuter).

bq

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'option --nouse_legacy_sql ou --use_legacy_sql=false. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA.

Pour exécuter la requête, saisissez :

bq query --nouse_legacy_sql \
'SELECT
   job_id,
   creation_time,
   query
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
 WHERE state != "DONE"'

Les résultats doivent se présenter sous la forme suivante :

  +--------------+--------------+----------------------------------------------+
  | job_id       |  creation_time            |  query                          |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_1    |  2019-10-10 00:00:00 UTC  |  SELECT ... FROM dataset.table1 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_2    |  2019-10-10 00:00:01 UTC  |  SELECT ... FROM dataset.table2 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_3    |  2019-10-10 00:00:02 UTC  |  SELECT ... FROM dataset.table3 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_4    |  2019-10-10 00:00:03 UTC  |  SELECT ... FROM dataset.table4 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_5    |  2019-10-10 00:00:04 UTC  |  SELECT ... FROM dataset.table5 |
  +--------------+--------------+----------------------------------------------+
  

Étapes suivantes