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 concernant des ensembles de données, des routines, des tables, des vues, des tâches, des réservations et des données de streaming.

Vous pouvez récupérer en temps réel les métadonnées 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 effectuées au cours 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_FOLDER nécessite l'autorisation bigquery.jobs.listAll pour le dossier parent et est disponible pour les rôles Folder Admin et BigQuery Admin pour le dossier.
  • 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_FOLDER renvoie toutes les tâches envoyées dans le dossier parent du projet actuel, y compris les tâches des sous-dossiers qu'il contient.
  • 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
folder_numbers RECORD ID de compte Google et d'administration d'ID (GAIA) des dossiers appartenant à l'ancêtre d'un projet, en commençant par le dossier le plus proche du projet. Cette colonne n'est renseignée que dans JOBS_BY_FOLDER.
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, DELETE ou SCRIPT. Consultez la section QueryStatementType pour obtenir la liste des valeurs valides.
priority STRING Priorité de cette tâche. Les valeurs autorisées sont INTERACTIVE et BATCH.
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 : Seule la vue JOBS_BY_PROJECT possède la 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. Si votre tâche a été exécutée dans un projet attribué à une réservation, elle doit respecter le format suivant : RESERVATION_ADMIN_PROJECT:RESERVATION_LOCATION.RESERVATION_NAME.

Dans ce résultat :

  • RESERVATION_ADMIN_PROJECT : nom du projet Google Cloud qui gère la réservation
  • RESERVATION_LOCATION : emplacement de la réservation.
  • RESERVATION_NAME : nom de la réservation.
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) sous forme de ErrorProto.
cache_hit BOOLEAN Indique si les résultats de requête de cette tâche 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.
total_bytes_billed INTEGER Si le projet est configuré pour utiliser la tarification à la demande, ce champ contient le nombre total d'octets facturés pour la tâche. Si le projet est configuré pour utiliser la tarification forfaitaire, le nombre d'octets ne vous est pas facturé, et ce champ est fourni à titre d'information seulement.
parent_job_id STRING ID de la tâche parente (le cas échéant).
transaction_id STRING ID de la transaction dans laquelle cette tâche a été exécutée, le cas échéant. (Aperçu)
dml_statistics RECORD

Si la tâche est une requête avec une instruction LMD, la valeur est un enregistrement avec les champs suivants :

  • inserted_row_count : nombre de lignes insérées.
  • deleted_row_count : nombre de lignes supprimées.
  • updated_row_count : nombre de lignes qui ont été mises à jour.

Pour toutes les autres tâches, la valeur est NULL.

Cette colonne est présente dans les vues INFORMATION_SCHEMA.JOBS_BY_USER et INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Conservation des données

L'historique des tâches des 180 derniers jours est conservé dans les vues des tâches INFORMATION_SCHEMA.

Remarques

Lorsque vous interrogez INFORMATION_SCHEMA.JOBS_BY_* pour rechercher un coût récapitulatif des tâches de requête, excluez le type d'instruction SCRIPT, sinon certaines valeurs seront comptabilisées deux fois. La ligne SCRIPT inclut des valeurs récapitulatives pour toutes les tâches enfants exécutées dans le cadre de cette tâche.

Régionalité

Les vues des tâches INFORMATION_SCHEMA de BigQuery sont régionalisées. Pour interroger ces vues, vous devez utiliser un qualificatif de région.

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
Remplacez l'élément suivant :

  • PROJECT_ID : ID du projet
  • REGION_NAME : région de votre projet
  • VIEW : nom de la vue pour laquelle vous souhaitez exécuter la requête

Par exemple, `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Pour exécuter la requête :

Console

  1. Dans Cloud Console, ouvrez la page "BigQuery".

    Accéder à BigQuery

  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 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 statement_type != "SCRIPT"
   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. Pour les tâches de script, la tâche parente indique également l'utilisation totale des emplacements par ses tâches enfants. Pour éviter de comptabiliser deux fois, utilisez WHERE statement_type != "SCRIPT" pour exclure la tâche parente.

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
Par exemple, `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Pour exécuter la requête :

Console

  1. Dans Cloud Console, ouvrez la page "BigQuery".

    Accéder à BigQuery

  2. Saisissez la requête en 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 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
Par exemple, `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Pour exécuter la requête :

Console

  1. Dans Cloud Console, ouvrez la page "BigQuery".

    Accéder à BigQuery

  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 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
Par exemple, `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Pour exécuter la requête :

Console

  1. Dans Cloud Console, ouvrez la page "BigQuery".

    Accéder à BigQuery

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