AT | BEFORE¶

Syntaxe¶

SELECT ...
FROM ...
  {
   AT( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> | STREAM => '<name>' } ) |
   BEFORE( STATEMENT => <id> )
  }
[ ... ]

Paramètres¶

TIMESTAMP => timestamp

Spécifie une date et une heure exactes à utiliser pour Time Travel. La valeur doit être explicitement convertie en type de données TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ ou TIMESTAMP_TZ.

Si aucune conversion explicite n’est spécifiée, l’horodatage dans la clause AT est traité comme un horodatage avec le fuseau horaire UTC (équivalent à TIMESTAMP_NTZ). Utiliser le type de données TIMESTAMP pour une conversion explicite peut également entraîner le traitement de la valeur comme une valeur TIMESTAMP_NTZ. Pour plus de détails, voir Types de données de date et heure.

OFFSET => time_difference

Spécifie la différence en secondes par rapport au temps actuel à utiliser pour Time Travel, sous la forme -N où N peut être un entier ou une expression arithmétique (par exemple, -120 correspond à 120 secondes, -30*60 correspond à 1800 secondes ou 30 minutes).

STATEMENT => id

Spécifie l’ID d’une requête à utiliser comme point de référence pour Time Travel. Ce paramètre prend en charge toute instruction de l’un des types suivants :

• DML (par ex. INSERT, UPDATE, DELETE)

• TCL (transaction BEGIN, COMMIT)

• SELECT

L’ID de requête doit faire référence à une requête qui a été exécutée au cours des 14 derniers jours. Si l’ID de requête fait référence à une requête datant de plus de 14 jours, l’erreur suivante est renvoyée :

Error: statement <query_id> not found

Pour contourner cette limitation, utilisez l’horodatage de la requête référencée.

STREAM => 'name'

Spécifie l’identificateur (c’est-à-dire le nom) d’un flux existant sur la table ou la vue interrogée. Le décalage actuel dans le flux est utilisé comme le point AT dans le temps pour renvoyer les données de changement pour l’objet source.

Ce mot-clé est pris en charge uniquement lors de la création d’un flux (à l’aide de CREATE STREAM) ou de l’interrogation de données de modifications (à l’aide de la clause CHANGES). Pour des exemples, voir ces rubriques.

Utilisation du paramètre AT TIMESTAMP¶

Dans la clause AT, vous pouvez spécifier le mot-clé TIMESTAMP suivi d’une chaîne qui représente un horodatage et une conversion explicite facultative vers le type de données TIMESTAMP, TIMESTAMP_TZ, TIMESTAMP_LTZ ou TIMESTAMP_NTZ. Les exemples suivants sont tous valides :

AT ( TIMESTAMP => '2024-06-05 12:30:00'::TIMESTAMP_LTZ )

AT ( TIMESTAMP => '2024-06-05 12:30:00'::TIMESTAMP )

AT ( TIMESTAMP => '2024-06-05 12:30:00' )

Si aucune conversion explicite n’est spécifiée, l’horodatage dans la clause AT est traité comme un horodatage avec le fuseau horaire UTC (équivalent à TIMESTAMP_NTZ). Utiliser le type de données TIMESTAMP pour une conversion explicite peut également entraîner le traitement de la valeur comme une valeur TIMESTAMP_NTZ, comme indiqué dans Types de données de date et heure.

La conversion explicite que vous choisissez affecte les résultats des requêtes Time Travel car les horodatages sont interprétés par rapport au fuseau horaire actuel de la session et à la valeur du paramètre TIMESTAMP_TYPE_MAPPING. Pour plus de détails sur ce comportement, voir Interrogation des données Time Travel dans une session avec un fuseau horaire différent d’UTC.

Par exemple, vous exécutez des requêtes dans une session SQL où le fuseau horaire actuel est America/Los_Angeles et TIMESTAMP_TYPE_MAPPING est réglé sur TIMESTAMP_NTZ. Créez un tableau et insérez immédiatement deux lignes :

CREATE OR REPLACE TABLE tt1 (c1 INT, c2 INT);
INSERT INTO tt1 VALUES(1,2);
INSERT INTO tt1 VALUES(2,3);

Vérifiez l’heure de création de la table avec une commande SHOW TABLES :

SHOW TERSE TABLES LIKE 'tt1';

+-------------------------------+------+-------+---------------+----------------+
| created_on                    | name | kind  | database_name | schema_name    |
|-------------------------------+------+-------+---------------+----------------|
| 2024-06-05 15:25:35.557 -0700 | TT1  | TABLE | TRAVEL_DB     | TRAVEL_SCHEMA  |
+-------------------------------+------+-------+---------------+----------------+

Notez le décalage de fuseau horaire dans la colonne created_on. Cinq minutes plus tard, insérez une autre ligne :

INSERT INTO tt1 VALUES(3,4);

Exécutez maintenant la requête Time Travel suivante, en espérant qu’elle renvoie les deux premières lignes :

SELECT * FROM tt1 at(TIMESTAMP => '2024-06-05 15:29:00'::TIMESTAMP);

000707 (02000): Time travel data is not available for table TT1. The requested time is either beyond the allowed time travel period or before the object creation time.

La requête échoue car le fuseau horaire de la session est UTC, et la conversion explicite sur TIMESTAMP honore ce fuseau horaire. Par conséquent, la table est supposée avoir été créée après l’horodatage spécifié. Pour résoudre ce problème, exécutez à nouveau la requête avec une conversion explicite vers TIMESTAMP_LTZ (fuseau horaire local) :

SELECT * FROM tt1 at(TIMESTAMP => '2024-06-05 15:29:00'::TIMESTAMP_LTZ);

+----+----+
| C1 | C2 |
|----+----|
|  1 |  2 |
|  2 |  3 |
+----+----+

Comme prévu, la requête renvoie les deux premières lignes qui ont été insérées. Enfin, exécutez la même requête mais spécifiez un horodatage légèrement ultérieur :

SELECT * FROM tt1 at(TIMESTAMP => '2024-06-05 15:31:00'::TIMESTAMP_LTZ);

+----+----+
| C1 | C2 |
|----+----|
|  1 |  2 |
|  2 |  3 |
|  3 |  4 |
+----+----+

Cette requête renvoie les trois lignes, compte tenu de l’horodatage le plus récent.

Utilisation de la clause BEFORE¶

Le paramètre STATEMENT dans la clause BEFORE doit faire référence à un ID de requête. Le point dans le passé utilisé par Time Travel se situe juste avant que l’instruction de cet ID de requête ne soit terminée plutôt qu’avant le début de l’instruction. Si des requêtes simultanées valident des modifications apportées aux données entre le début et la fin de l’instruction, ces modifications sont incluses dans vos résultats.

Par exemple, les instructions suivantes sont en cours d’exécution sur la table my_table en parallèle dans deux threads distincts :

Pour contourner le problème, vous pouvez utiliser un paramètre TIMESTAMP qui spécifie un point dans le temps juste avant le début de l’instruction.

Notes sur l’utilisation¶

• Les données contenues dans Snowflake sont identifiées par des horodatages qui peuvent légèrement différer de la valeur exacte de l’heure système.

• La valeur de TIMESTAMP ou OFFSET doit être une expression constante.

• La résolution temporelle la plus petite pour TIMESTAMP est exprimée en millisecondes.

• Si les données demandées sont au-delà de la période de conservation de Time Travel (1 jour par défaut), l’instruction échoue.

  De plus, si les données demandées se trouvent dans la période de conservation de Time Travel, mais qu’aucune donnée historique n’est disponible (par exemple, si la période de conservation a été prolongée), l’instruction échoue.

• Si la durée Time Travel spécifiée est égale ou antérieure au moment où l’objet a été créé, l’instruction échoue. Voir Utilisation du paramètre AT TIMESTAMP.

• Lorsque vous accédez aux données historiques d’une table, les résultats incluent les colonnes, les valeurs par défaut, etc. de la définition actuelle de la table. Il en va de même pour les vues non matérialisées. Par exemple, si vous modifiez une table pour y ajouter une colonne, la recherche de données historiques antérieures au moment où la colonne a été ajoutée renvoie des résultats qui incluent la nouvelle colonne.

• Les données historiques ont les mêmes exigences de contrôle d’accès que les données actuelles. Toutes les modifications sont appliquées rétroactivement.

• Les clauses AT et BEFORE ne permettent pas de sélectionner des données historiques à partir d’un CTE.

  Par exemple, la requête suivante n’est pas prise en charge :

  WITH mycte AS
    (SELECT mytable.* FROM mytable)
  SELECT * FROM mycte AT(TIMESTAMP => '2024-03-13 13:56:09.553 +0100'::TIMESTAMP_TZ);
  

  Copy

  Toutefois, ces clauses sont prises en charge dans une requête dans une clause WITH. Par exemple, la requête suivante est prise en charge :

  WITH mycte AS
    (SELECT * FROM mytable AT(TIMESTAMP => '2024-03-13 13:56:09.553 +0100'::TIMESTAMP_TZ))
  SELECT * FROM mycte;
  

  Copy

• Les requêtes Time Travel sur les tables hybrides présentent les limitations suivantes :

  • Seul le paramètre TIMESTAMP est pris en charge dans la clause AT. Les paramètres OFFSET, STATEMENT et STREAM ne sont pas pris en charge.

  • La valeur du paramètre TIMESTAMP doit être la même pour toutes les tables appartenant à la même base de données. Si les tables appartiennent à des bases de données différentes, différentes valeurs TIMESTAMP peuvent être utilisées.

  • La clause BEFORE n’est pas prise en charge.

Résolution des problèmes¶

Time travel data is not available for table <tablename>

... AT(TIMESTAMP => '2018-07-27 12:00:00')               -- fails
... AT(TIMESTAMP => '2018-07-27 12:00:00'::TIMESTAMP)    -- succeeds

Exemples¶

Sélectionnez les données historiques d’une table en utilisant un horodatage spécifique. Dans les deux premiers exemples, qui utilisent le paramètre TIMESTAMP, my_table pourrait être une table standard ou une table hybride.

SELECT * FROM my_table AT(TIMESTAMP => 'Wed, 26 Jun 2024 09:20:00 -0700'::TIMESTAMP_LTZ);

SELECT * FROM my_table AT(TIMESTAMP => TO_TIMESTAMP(1432669154242, 3));

Sélectionnez les données historiques d’une table datant d’il y a 5 minutes :

SELECT * FROM my_table AT(OFFSET => -60*5) AS T WHERE T.flag = 'valid';

Sélectionnez les données historiques d’une table jusqu’à, mais sans inclure les modifications apportées par la transaction spécifiée :

SELECT * FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');

Renvoie la différence dans les données de table résultant de la transaction spécifiée :

SELECT oldt.* ,newt.*
  FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS oldt
    FULL OUTER JOIN my_table AT(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS newt
    ON oldt.id = newt.id
  WHERE oldt.id IS NULL OR newt.id IS NULL;

L’exemple suivant exécute une requête de jointure Time Travel sur deux tables de la même base de données, dont l’une est une table hybride. La même expression TIMESTAMP doit être utilisée pour les deux tables.

SELECT *
  FROM db1.public.htt1
    AT(TIMESTAMP => '2024-06-05 17:50:00'::TIMESTAMP_LTZ) h
    JOIN db1.public.tt1
    AT(TIMESTAMP => '2024-06-05 17:50:00'::TIMESTAMP_LTZ) t
    ON h.c1=t.c1;