Utiliser l'historique des modifications

L'historique des modifications de BigQuery vous permet de suivre l'historique des modifications apportées à une table BigQuery. L'historique des modifications d'une table est exposé en tant que fonction de valeur de table (TVF) SQL qui indique les types de modifications particuliers effectués au cours d'une période donnée. Cette fonctionnalité vous permet de traiter les modifications incrémentielles apportées à une table. Comprendre les modifications apportées à une table peut vous aider à effectuer des opérations telles que la gestion incrémentielle d'une instance dupliquée en dehors de BigQuery tout en évitant les copies coûteuses.

Autorisations requises

Pour afficher l'historique des modifications d'une table, vous devez disposer de l'autorisation bigquery.tables.getData sur cette table. Les rôles de gestion de l'authentification et des accès (IAM) prédéfinis suivants incluent cette autorisation:

roles/bigquery.dataViewer
roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin

Si une table dispose ou a eu des règles d'accès au niveau des lignes, seul un administrateur de table peut accéder aux données historiques de la table. L'autorisation bigquery.rowAccessPolicies.overrideTimeTravelRestrictions est requise pour la table et est incluse dans le rôle IAM roles/bigquery.admin prédéfini.

Si une table dispose d'une sécurité au niveau des colonnes, vous ne pouvez afficher l'historique des modifications que sur les colonnes auxquelles vous avez accès.

TVF `APPENDS`

La TVF APPENDS renvoie une table de toutes les lignes ajoutées à une table pour une période donnée. Les opérations suivantes ajoutent des lignes à l'historique des modifications APPENDS:

CREATE TABLE Instruction LDD
INSERT Instruction LMD
MERGE Instruction LMD
Charger des données dans BigQuery
Ingestion en flux continu

Syntaxe

APPENDS(
  TABLE table,
  start_timestamp DEFAULT NULL,
  end_timestamp DEFAULT NULL)

table : nom de la table BigQuery. Il ne peut pas s'agir d'une vue, d'une sous-requête, d'une table externe, d'une vue matérialisée ou d'une table générique. Cet argument doit être précédé du mot TABLE.
start_timestamp: TIMESTAMP indiquant la première heure possible à laquelle une modification est incluse dans la sortie. Si la valeur est NULL, toutes les modifications apportées depuis la création de la table sont renvoyées. Si la table est créée après start_timestamp, c'est l'heure de création réelle qui est utilisée. Si la période est antérieure à la limite autorisée par la fonctionnalité temporelle, une erreur est renvoyée. Pour les tables standards, cette période est de sept jours, mais vous pouvez configurer la fenêtre temporelle pour qu'elle soit inférieure à cette valeur.
end_timestamp: TIMESTAMP indiquant la dernière heure exclusive à laquelle une modification est incluse dans la sortie. Si la valeur est NULL, toutes les modifications apportées jusqu'au début de la requête sont incluses.

Valeur renvoyée

La TVF APPENDS renvoie une table avec les colonnes suivantes:

Toutes les colonnes de la table d'entrée au moment de l'exécution de la requête. Si une colonne est ajoutée après end_timestamp, elle affiche des valeurs NULL renseignées dans les lignes insérées avant l'ajout de la colonne.
_CHANGE_TYPE: STRING indiquant le type de modification ayant généré la ligne. Pour APPENDS, la seule valeur acceptée est INSERT.
_CHANGE_TIMESTAMP: TIMESTAMP indiquant la date et l'heure de commit de la transaction ayant effectué la modification.

Détails

Les enregistrements des lignes insérées sont conservés même si ces données sont supprimées par la suite. Les suppressions ne sont pas reflétées dans la TVF APPENDS. Si une table est copiée, l'appel de la TVF APPENDS sur la table copiée renvoie chaque ligne telle qu'elle a été insérée au moment de la création de la table. Si une ligne est modifiée en raison d'une opération UPDATE, il n'y a aucun effet.

Examples

Cet exemple montre l'historique des modifications renvoyé par APPENDS lorsque diverses modifications sont apportées à une table appelée Produce. Cet exemple peut ne pas fonctionner si la durée de l'opération est supérieure à votre fenêtre de temps. Commencez par créer la table.

CREATE TABLE mydataset.Produce (product STRING, inventory INT64) AS (
  SELECT "apples" AS product, 10 AS inventory);

Insérez ensuite deux lignes dans la table.

INSERT INTO mydataset.Produce
VALUES
  ("bananas", 20),
  ("carrots", 30);

Affichez l'historique complet des modifications des ajouts. Utilisez les valeurs NULL pour obtenir l'historique complet dans la période temporelle.

SELECT
  product,
  inventory,
  _CHANGE_TYPE AS change_type,
  _CHANGE_TIMESTAMP AS change_time
FROM
  APPENDS(TABLE mydataset.Produce, NULL, NULL);

Le résultat ressemble à ce qui suit :

+---------+-----------+-------------+--------------------------------+
| product | inventory | change_type | change_time                    |
+---------+-----------+-------------+--------------------------------+
| apples  | 10        | INSERT      | 2022-04-15 20:06:00.488000 UTC |
| bananas | 20        | INSERT      | 2022-04-15 20:06:08.490000 UTC |
| carrots | 30        | INSERT      | 2022-04-15 20:06:08.490000 UTC |
+---------+-----------+-------------+--------------------------------+

Ensuite, ajoutez une colonne, insérez une nouvelle ligne de valeurs, mettez à jour l'inventaire et supprimez la ligne "Bananes".

ALTER TABLE mydataset.Produce ADD COLUMN color STRING;
INSERT INTO mydataset.Produce VALUES ("grapes", 40, "purple");
UPDATE mydataset.Produce SET inventory = inventory + 5 WHERE TRUE;
DELETE mydataset.Produce WHERE product = "bananas";

Affichez la nouvelle table.

SELECT * FROM mydataset.Produce;

+---------+-----------+--------+
| product | inventory | color  |
+---------+-----------+--------+
| apples  | 15        | NULL   |
| carrots | 35        | NULL   |
| grapes  | 45        | purple |
+---------+-----------+--------+

Affichez l'historique complet des modifications des ajouts.

SELECT
  product,
  inventory,
  color,
  _CHANGE_TYPE AS change_type,
  _CHANGE_TIMESTAMP AS change_time
FROM
  APPENDS(TABLE mydataset.Produce, NULL, NULL);