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.

Avant de commencer

Attribuez aux utilisateurs des rôles IAM (Identity and Access Management) incluant les autorisations nécessaires pour effectuer l'ensemble des tâches du présent document.

Autorisations requises

Pour récupérer les métadonnées de réservation à l'aide des tables INFORMATION_SCHEMA, vous devez disposer des autorisations IAM suivantes :

  • Pour RESERVATION_CHANGES_BY_PROJECT et RESERVATIONS_BY_PROJECT, vous avez besoin des éléments suivants :

    • bigquery.reservations.list pour le projet

      Chacun des rôles IAM prédéfinis suivants inclut les autorisations ci-dessus :

      • roles/bigquery.resourceAdmin
      • roles/bigquery.resourceEditor
      • roles/bigquery.resourceViewer
      • roles/bigquery.user
      • roles/bigquery.admin

  • Pour CAPACITY_COMMITMENT_CHANGES_BY_PROJECT et CAPACITY_COMMITMENTS_BY_PROJECT, vous avez besoin des éléments suivants :

    • bigquery.capacityCommitments.list pour le projet

      Chacun des rôles IAM prédéfinis suivants inclut les autorisations ci-dessus :

      • roles/bigquery.resourceAdmin
      • roles/bigquery.resourceEditor
      • roles/bigquery.resourceViewer
      • roles/bigquery.user
      • roles/bigquery.admin

  • Pour ASSIGNMENT_CHANGES_BY_PROJECT et ASSIGNMENTS_BY_PROJECT, vous avez besoin des éléments suivants :

    • bigquery.reservationAssignments.list dans le projet

      Chacun des rôles IAM prédéfinis suivants inclut les autorisations ci-dessus :

      • roles/bigquery.resourceAdmin
      • roles/bigquery.resourceEditor
      • roles/bigquery.resourceViewer
      • roles/bigquery.user
      • roles/bigquery.admin

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

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
ddl STRING Instruction LDD utilisée pour créer la réservation.
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
ddl STRING Instruction LDD utilisée pour créer l'engagement de capacité.
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
ddl STRING Instruction LDD utilisée pour créer l'attribution.
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

Les exemples suivants illustrent diverses utilisations des vues de réservations.

Attribution de réservation actuelle pour un projet

L'exemple suivant récupère la réservation actuellement attribuée à un projet et 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.

SELECT
  reservation.reservation_name,
  reservation.slot_capacity
FROM
  `reservation-admin-project.region-us`.
  INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT assignment
INNER JOIN
  `reservation-admin-project.region-us`.
  INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT AS reservation
ON
  (assignment.reservation_name = reservation.reservation_name)
WHERE
   assignment.assignee_id = "my-project"
  AND job_type = "QUERY";

Historique des réservations

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, telles que la création ou la suppression de la réservation.

SELECT
  *
FROM
  `reservation-admin-project.region-us`.
  INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
WHERE
  reservation_name = "my-reservation"
ORDER BY
  change_timestamp DESC;

Utilisation des emplacements au fil du temps

L'exemple suivant montre l'utilisation des emplacements, la capacité d'emplacements et la réservation attribuée pour un projet avec une attribution de réservation, au cours de l'heure précédente. L'utilisation des emplacements est indiquée en unités d'emplacements de millisecondes par seconde.

WITH
  job_data AS (
  SELECT
    job.period_start,
    job.reservation_id,
    job.period_slot_ms,
    job.job_id,
    job.job_type
  FROM
    `my-project.region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT AS job
  WHERE
    job.period_start > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR))
SELECT
  reservation.reservation_name AS reservation_name,
  job.period_start,
  reservation.slot_capacity,
  job.period_slot_ms,
  job.job_id,
  job.job_type
FROM
  job_data AS job
INNER JOIN
  `reservation-admin-project.region-us`.INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT AS reservation
ON
  (job.reservation_id = CONCAT(reservation.project_id, ":", "US", ".", reservation.reservation_name));

Le résultat ressemble à ce qui suit :

+------------------+---------------------+---------------+----------------+------------------+----------+
| reservation_name |    period_start     | slot_capacity | period_slot_ms |           job_id | job_type |
+------------------+---------------------+---------------+----------------+------------------+----------+
| my_reservation   | 2021-04-30 17:30:54 |           100 |          11131 | bquxjob_66707... | QUERY    |
| my_reservation   | 2021-04-30 17:30:55 |           100 |          49978 | bquxjob_66707... | QUERY    |
| my_reservation   | 2021-04-30 17:30:56 |           100 |           9038 | bquxjob_66707... | QUERY    |
| my_reservation   | 2021-04-30 17:30:57 |           100 |          17237 | bquxjob_66707... | QUERY    |

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 joint RESERVATIONS_BY_PROJECT à JOBS_TIMELINE_BY_PROJECT pour associer les tranches horaires de la tâche aux informations de réservation.

Étape suivante