Obtenir des métadonnées de réservation à 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, des réservations et des données de streaming.

Vous pouvez récupérer les métadonnées en temps réel des réservations BigQuery en interrogeant les vues des réservations INFORMATION_SCHEMA. Ces vues contiennent une liste des modifications apportées aux réservations, aux attributions et aux engagements de capacité, ainsi qu'une chronologie des réservations.

Autorisations requises

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

  • RESERVATION_CHANGES_BY_PROJECT et RESERVATIONS_BY_PROJECT nécessitent l'autorisation bigquery.reservations.list pour le projet et sont disponibles pour les rôles BigQuery User, BigQuery Resource Admin et BigQuery Admin.
  • CAPACITY_COMMITMENT_CHANGES_BY_PROJECT et CAPACITY_COMMITMENTS_BY_PROJECT nécessitent l'autorisation bigquery.capacityCommitments.list pour le projet et sont disponibles pour les rôles BigQuery User, BigQuery Resource Admin et BigQuery Admin.
  • ASSIGNMENT_CHANGES_BY_PROJECT et ASSIGNMENTS_BY_PROJECT nécessitent l'autorisation bigquery.reservationAssignments.list pour le projet et sont disponibles pour les rôles BigQuery User, BigQuery Resource Admin et BigQuery Admin.

Schémas

Lorsque vous interrogez les vues de réservation INFORMATION_SCHEMA, les résultats de la requête contiennent des informations sur les réservations BigQuery. Pour en savoir plus sur les réservations BigQuery, consultez la page sur les concepts de réservation.

  • INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT contient la liste de toutes les modifications apportées aux réservations dans le projet d'administration. Chaque ligne représente une modification apportée à une seule réservation.
  • INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT contient la liste de toutes les réservations en cours dans le projet d'administration. Chaque ligne représente une seule réservation en cours. Une réservation en cours est une réservation qui n'a pas été supprimée.
  • INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT contient une liste de toutes les modifications apportées aux engagements de capacité dans le projet d'administration. Chaque ligne représente une modification apportée à un seul engagement de capacité.
  • INFORMATION_SCHEMA.CAPACITY_COMMITMENTS_BY_PROJECT contient la liste de tous les engagements de capacité en cours dans le projet d'administration. Chaque ligne représente un seul engagement de capacité en cours. Un engagement de capacité en cours est en attente ou actif, et n'a pas été supprimé.
  • INFORMATION_SCHEMA.ASSIGNMENT_CHANGES_BY_PROJECT contient la liste de toutes les modifications apportées aux attributions dans le projet d'administration. Chaque ligne représente une modification apportée à une seule attribution.
  • INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT contient la liste de toutes les attributions en cours dans le projet d'administration. Chaque ligne représente une seule attribution en cours. Une attribution en cours est en attente ou active, et n'a pas été supprimée.

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

Nom de la colonne Type de données Valeur
change_timestamp TIMESTAMP Heure à laquelle la modification s'est produite.
project_id STRING ID du projet d'administration.
project_number INTEGER Numéro du projet d'administration.
reservation_name STRING Nom de réservation fourni par l'utilisateur.
ignore_idle_slots BOOL Si la valeur est définie sur "false", toute requête utilisant cette réservation peut exploiter des emplacements inactifs provenant d'autres engagements de capacité.
action STRING Type d'événement survenu lors de la réservation. Il peut s'agir de CREATE, UPDATE ou DELETE.
slot_capacity INTEGER Capacité d'emplacements associée à la réservation.
user_email STRING Adresse e-mail de l'utilisateur qui a effectué la modification. google pour les modifications apportées par Google. NULL si l'adresse e-mail est inconnue.

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

Nom de la colonne Type de données Valeur
project_id STRING ID du projet d'administration.
project_number INTEGER Numéro du projet d'administration.
reservation_name STRING Nom de réservation fourni par l'utilisateur.
ignore_idle_slots BOOL Si la valeur est définie sur "false", toute requête utilisant cette réservation peut exploiter des emplacements inactifs provenant d'autres engagements de capacité.
slot_capacity INTEGER Capacité d'emplacements associée à la réservation.

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

Nom de la colonne Type de données Valeur
change_timestamp TIMESTAMP Heure à laquelle la modification s'est produite.
project_id STRING ID du projet d'administration.
project_number INTEGER Numéro du projet d'administration.
capacity_commitment_id STRING ID qui identifie de manière unique l'engagement de capacité.
commitment_plan STRING Forfait de l'engagement de capacité.
state STRING État de l'engagement de capacité. Il peut s'agir de PENDING ou ACTIVE.
slot_count INTEGER Nombre d'emplacements associés à l'engagement de capacité.
action STRING Type d'événement survenu avec l'engagement de capacité. Il peut s'agir de CREATE, UPDATE ou DELETE.
user_email STRING Adresse e-mail de l'utilisateur qui a effectué la modification. google pour les modifications apportées par Google. NULL si l'adresse e-mail est inconnue.
commitment_start_time TIMESTAMP Début de la période d'engagement en cours. Ne s'applique qu'aux engagements de capacité ACTIVE. Sinon, la valeur est NULL.
commitment_end_time TIMESTAMP Fin de la période d'engagement en cours. Ne s'applique qu'aux engagements de capacité ACTIVE. Sinon, la valeur est NULL.
failure_status RECORD Indique le motif de l'échec pour un forfait avec engagement à l'état FAILED. Sinon, la valeur est NULL. RECORD est constitué d'un code et d'un message.
renewal_plan STRING Forfait vers lequel cet engagement de capacité est converti une fois le champ commitment_end_time transmis. Après modification du forfait, la période d'engagement est prolongée conformément à celui-ci. Ne s'applique qu'aux engagements ANNUAL et TRIAL. Sinon, la valeur est NULL.

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

Nom de la colonne Type de données Valeur
project_id STRING ID du projet d'administration.
project_number INTEGER Numéro du projet d'administration.
capacity_commitment_id STRING ID qui identifie de manière unique l'engagement de capacité.
commitment_plan STRING Forfait de l'engagement de capacité.
state STRING État de l'engagement de capacité. Il peut s'agir de PENDING ou ACTIVE.
slot_count INTEGER Nombre d'emplacements associés à l'engagement de capacité.

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

Nom de la colonne Type de données Valeur
change_timestamp TIMESTAMP Heure à laquelle la modification s'est produite.
project_id STRING ID du projet d'administration.
project_number INTEGER Numéro du projet d'administration.
assignment_id STRING ID qui identifie de manière unique l'attribution.
reservation_name STRING Nom de la réservation utilisée par l'attribution.
job_type STRING Type de tâche pouvant utiliser la réservation. Il peut s'agir de PIPELINE ou QUERY.
assignee_id STRING ID qui identifie de manière unique la ressource affectée.
assignee_number INTEGER Numéro qui identifie de manière unique la ressource affectée.
assignee_type STRING Type de ressource affectée. Il peut s'agir de organization, folder ou project.
action STRING Type d'événement survenu lors de l'attribution. Il peut s'agir de CREATE ou DELETE.
user_email STRING Adresse e-mail de l'utilisateur qui a effectué la modification. google pour les modifications apportées par Google. NULL si l'adresse e-mail est inconnue.
state STRING État de l'attribution. Il peut s'agir de PENDING ou ACTIVE.

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

Nom de la colonne Type de données Valeur
project_id STRING ID du projet d'administration.
project_number INTEGER Numéro du projet d'administration.
assignment_id STRING ID qui identifie de manière unique l'attribution.
reservation_name STRING Nom de la réservation utilisée par l'attribution.
job_type STRING Type de tâche pouvant utiliser la réservation. Il peut s'agir de PIPELINE ou QUERY.
assignee_id STRING ID qui identifie de manière unique la ressource affectée.
assignee_number INTEGER Numéro qui identifie de manière unique la ressource affectée.
assignee_type STRING Type de ressource affectée. Il peut s'agir de organization, folder ou project.

Conservation des données

Les réservations, les engagements de capacité et les attributions en cours sont conservés dans les vues de réservations jusqu'à ce qu'ils soient supprimés. Les réservations, les engagements de capacité et les attributions supprimés sont respectivement conservés dans les vues RESERVATION_CHANGES_BY_PROJECT, CAPACITY_COMMITMENT_CHANGES_BY_PROJECT et ASSIGNMENT_CHANGES_BY_PROJECT pendant une durée maximale de 41 jours, après quoi ils sont supprimés des vues.

Régionalité

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

Effectuer une jointure entre les vues de réservations et les vues de tâches

Les vues de tâches contiennent la colonne reservation_id. Si votre tâche a été exécutée dans un projet auquel une réservation est attribuée, reservation_id doit respecter le format suivant : reservation-admin-project:reservation-location.reservation-name.

Pour effectuer une jointure entre les vues de réservations et les vues de tâches, vous pouvez associer la colonne reservation_id des vues de tâches aux colonnes des vues de réservations project_id et reservation_name. Consultez cet exemple.

Exemples

Exemple 1

Dans l'exemple suivant, on récupère la réservation actuellement attribuée au projet ainsi que sa capacité d'emplacements. Ces informations peuvent vous aider à déboguer les performances des tâches en comparant l'utilisation de l'emplacement du projet avec la capacité d'emplacements attribuée à ce même projet.

La requête doit s'exécuter sur le projet d'administration contenant les réservations. 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 : `reservation-admin-project`.`region-us`.INFORMATION_SCHEMA.RESERVATION_CHANGES_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
  reservation.reservation_name,
  reservation.slot_capacity
FROM
  `reservation-admin-project.region-us`.
  INFORMATION_SCHEMA.ASSIGNMENT_CHANGES_BY_PROJECT assignment
INNER JOIN
  `reservation-admin-project.region-us`.
  INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT AS reservation
ON
  (assignment.reservation_name = reservation.reservation_name)
WHERE
  assignment.action = "CREATE"
  AND assignment.assignee_id = "my-project"
  AND job_type = "QUERY" /* can also be "PIPELINE */
ORDER BY
  assignment.change_timestamp DESC,
  reservation.change_timestamp DESC
LIMIT
  1;

  3. Cliquez sur Exécuter.

gcloud

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
  reservation.reservation_name,
  reservation.slot_capacity
FROM
  `reservation-admin-project.region-us`.
  INFORMATION_SCHEMA.ASSIGNMENT_CHANGES_BY_PROJECT assignment
INNER JOIN
  `reservation-admin-project.region-us`.
  INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT AS reservation
ON
  (assignment.reservation_name = reservation.reservation_name)
WHERE
  assignment.action = "CREATE"
  AND assignment.assignee_id = "my-project"
  AND job_type = "QUERY" /* can also be "PIPELINE */
ORDER BY
  assignment.change_timestamp DESC,
  reservation.change_timestamp DESC
LIMIT
  1;'

Exemple 2

Dans l'exemple suivant, on récupère l'historique des modifications pour une réservation donnée. Utilisez ces informations pour afficher la liste des modifications apportées à une réservation spécifique.

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 : `reservation-admin-project`.`region-us`.INFORMATION_SCHEMA.RESERVATION_CHANGES_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
  *
FROM
  `reservation-admin-project.region-us`.
  INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
WHERE
  reservation_name = "..."
ORDER BY
  change_timestamp DESC;

  3. Cliquez sur Exécuter.

gcloud

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
  *
FROM
  `reservation-admin-project.region-us`.
  INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
WHERE
  reservation_name = "..."
ORDER BY
  change_timestamp DESC;'

Exemple 3

L'exemple suivant effectue une jointure entre les vues JOBS_BY_PROJECT et RESERVATIONS_BY_PROJECT, qui fournissent à la fois l'utilisation de l'emplacement par un projet spécifique attribué au cours de l'heure précédente et la capacité d'emplacements de chaque réservation dans le projet d'administration donné. Un projet attribué est un projet auquel une réservation est attribuée, et un projet d'administration est le projet qui contient des réservations. Pour en savoir plus, consultez la documentation sur les réservations. Cette requête utilise la vue RESERVATIONS_BY_PROJECT pour obtenir des informations sur une réservation. Si les réservations ont été modifiées au cours de l'heure précédente, la colonne reservation_slot_capacity peut ne pas être exacte.

La requête doit s'exécuter à l'aide du projet d'administration contenant des réservations ou du projet attribué. 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 :`reservation-admin-project`.`region-us`.INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT

Pour exécuter la requête :

Console

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

    Accéder à BigQuery

  2. Dans le champ É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.

     WITH
  job_data AS (
  SELECT
    job.reservation_id,
    job.total_slot_ms
  FROM
    `assignee-project.region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS job
  WHERE
    job.start_time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) )
SELECT
  reservation.reservation_name AS reservation_name,
  ANY_VALUE(reservation.slot_capacity) AS reservation_slot_capacity,
  SUM(job.total_slot_ms) / (1000 * 60 * 60) AS avg_slots_used_by_hour
FROM
  job_data AS job
INNER JOIN
  `admin-project.region-us`.INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT AS reservation
ON
  (job.reservation_id = CONCAT(reservation.project_id, ":", "US", ".", reservation.reservation_name))
GROUP BY
  1
ORDER BY
  1 DESC;

  3. Cliquez sur Exécuter.

gcloud

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 \
'WITH
   job_data AS (
   SELECT
     job.reservation_id,
     job.total_slot_ms
   FROM
     `assignee-project.region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS job
   WHERE
     job.start_time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) )
 SELECT
   reservation.reservation_name AS reservation_name,
   ANY_VALUE(reservation.slot_capacity) AS reservation_slot_capacity,
   SUM(job.total_slot_ms) / (1000 * 60 * 60) AS avg_slots_used_by_hour
 FROM
   job_data AS job
 INNER JOIN
   `admin-project.region-us`.INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT AS reservation
 ON
   (job.reservation_id = CONCAT(reservation.project_id, ":", "US", ".", reservation.reservation_name))
 GROUP BY
   1
 ORDER BY
   1 DESC;'

Étapes suivantes