Partager via

Prise en main des tables temporelles

  • Article

S’applique à : Base de données SQL Azure SQL Managed Instance Base de données Azure SQL dans Fabric

Les tableaux temporels sont une fonctionnalité de programmation qui vous permet de suivre et d'analyser l'historique complet des changements dans vos données, sans avoir besoin d'un codage personnalisé. Les tables temporelles conservent les données étroitement liées au contexte temporel afin que les faits stockés puissent être interprétés comme étant valides uniquement dans une période spécifique. Cette propriété des tables temporelles offre une analyse efficace basée sur le temps et permet d’obtenir des informations à partir de l’évolution des données.

Scénario temporel

Cet article illustre les étapes permettant d’utiliser les tables temporelles dans un scénario d’application. Supposons que vous voulez effectuer le suivi de l’activité utilisateur sur un nouveau site web en cours de développement ou sur un site web existant que vous voulez enrichir avec l’analyse de l’activité utilisateur. Dans cet exemple simplifié, nous partons du principe que le nombre de pages web visitées pendant une période de temps est un indicateur qui doit être capturé et analysé dans la base de données du site web hébergée sur Azure SQL Database ou Azure SQL Managed Instance. L’objectif de l’analyse de l’historique de l’activité utilisateur est d’obtenir des informations pour redessiner le site web et fournir une meilleure expérience pour les visiteurs.

Le modèle de base de données pour ce scénario est simple : la mesure de l’activité utilisateur est représentée par un champ d’entier unique, PageVisited, et est capturée en même temps que des informations de base sur le profil utilisateur. En outre, pour l’analyse temporelle, vous conservez une série de lignes pour chaque utilisateur, où chaque ligne représente le nombre de pages visitées par un utilisateur au cours d’une période spécifique.

schéma

Heureusement, vous n’avez pas besoin d’intervenir sur votre application pour gérer les informations de cette activité. Avec les tables temporelles, ce processus est automatise, ce qui vous offre une grande souplesse pour concevoir le site web et davantage de temps pour vous concentrer sur l’analyse des données. La seule chose que vous avez à faire est de garantir que la table WebSiteInfo est configurée en tant que table temporelle avec versions gérées par le système. Les étapes à suivre pour utiliser les tables temporelles dans ce scénario sont décrites ci-dessous.

Étape 1 : Configurer les tables comme tables temporelles

Selon que vous commencez le développement d’une nouvelle application ou que vous mettez à niveau une application existante, vous créez des tables temporelles ou vous modifiez des tables existantes en ajoutant des attributs temporels. En général, votre scénario peut être une combinaison de ces deux options. Exécutez ces opérations à l’aide de SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), Azure Data Studio ou tout autre outil de développement Transact-SQL.

Important

Nous vous recommandons d’utiliser systématiquement la dernière version de Management Studio afin de rester en cohérence avec les mises à jour d'Azure SQL Database et Azure SQL Managed Instance. Mettre à jour SQL Server Management Studio.

Créer une table

Utilisez l’élément de menu contextuel « Nouvelle table avec version système » dans l’Explorateur d’objets SSMS pour ouvrir l’éditeur de requête avec un script de modèle de table temporelle, puis utilisez « Spécifier les valeurs des paramètres du modèle » (Ctrl+Maj+M) pour remplir le modèle :

SSMSNewTable

Dans SSDT, choisissez le modèle « Table temporelle (Système par version) » quand vous ajoutez de nouveaux éléments au projet de base de données. Cette opération ouvre le concepteur de tables et vous permet de spécifier facilement la disposition de la table :

SSDTNewTable

Vous pouvez également créer une table temporelle en spécifiant les instructions Transact-SQL directement, comme indiqué dans l’exemple ci-dessous. Les éléments obligatoires de chaque table temporelle sont la définition de PERIOD et la clause SYSTEM_VERSIONING avec une référence à une autre table utilisateur qui stocke les versions de ligne historiques :

CREATE TABLE WebsiteUserInfo
(  
    [UserID] int NOT NULL PRIMARY KEY CLUSTERED
  , [UserName] nvarchar(100) NOT NULL
  , [PagesVisited] int NOT NULL
  , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )  
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WebsiteUserInfoHistory));

Quand vous créez une table temporelle avec versions gérées par le système, la table d’historique associée avec la configuration par défaut est automatiquement créée. La table d’historique par défaut contient un index cluster arbre B (B-tree) sur les colonnes de période (début, fin) pour lequel la compression de page est activée. Cette configuration est optimale pour la plupart des scénarios dans lesquels les tables temporelles sont utilisées, en particulier pour l’ audit des données.

Dans ce cas particulier, nous voulons effectuer une analyse temporelle des tendances sur un historique de données plus long et avec des jeux de données plus volumineux, le choix du stockage pour la table d’historique est donc un index cluster columnstore. L’index cluster columnstore fournit une bonne compression et des performances optimales pour les requêtes analytiques. Les tables temporelles vous donnent la possibilité de configurer complètement indépendamment des index sur les tables actuelles et temporelles.

Notes

Des index Columnstore sont disponibles aux niveaux Critique pour l'entreprise, Usage général et Premium, ainsi que Standard, S3 et versions supérieures.

Le script suivant montre comment l’index par défaut sur la table d’historique peut être modifié en index cluster columnstore :

CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
ON dbo.WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

Les tables temporelles sont représentées dans l’Explorateur d’objets avec une icône spécifique pour faciliter l’identification, tandis que la table d’historique s’affiche comme un nœud enfant.

ALTER table

Transformer une table existante en table temporelle

Examinons l’autre scénario dans lequel la table WebsiteUserInfo existe déjà, mais n’a pas été conçue pour conserver un historique des modifications. Dans ce cas, vous pouvez simplement étendre la table existante pour qu’elle devienne temporelle, comme indiqué dans l’exemple suivant :

ALTER TABLE WebsiteUserInfo
ADD
    ValidFrom datetime2 (0) GENERATED ALWAYS AS ROW START HIDDEN  
        constraint DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME())
    , ValidTo datetime2 (0)  GENERATED ALWAYS AS ROW END HIDDEN
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE WebsiteUserInfo  
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WebsiteUserInfoHistory));
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
ON dbo.WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

Étape 2 : Exécuter régulièrement votre charge de travail

Le principal avantage des tables temporelles est que vous n’avez pas besoin de modifier ou d’ajuster votre site web de quelque façon que ce soit pour effectuer le suivi des modifications. Une fois créées, les tables temporelles conservent en toute transparence les versions précédentes des lignes chaque fois que vous effectuez des modifications sur vos données.

Pour tirer parti du suivi automatique des modifications dans ce scénario particulier, nous allons simplement mettre à jour la colonne PagesVisited chaque fois qu’un utilisateur met fin à sa session sur le site web :

UPDATE WebsiteUserInfo  SET [PagesVisited] = 5
WHERE [UserID] = 1;

Notez que la requête de mise à jour n’a pas besoin de connaître l’heure exacte à laquelle s’est produite l’opération réelle, ni comment les données historiques sont conservées pour une future analyse. Ces deux aspects sont gérés automatiquement par Azure SQL Database et Azure SQL Managed Instance. Le diagramme suivant illustre la façon dont les données d’historique sont générées à chaque mise à jour.

TemporalArchitecture

Étape 3 : Analyser les données historiques

Une fois que la gestion de version des tables temporelles par le système est activée, il vous suffit d’une seule requête pour analyser les données historiques. Dans cet article, nous fournissons des exemples de scénarios d’analyse courants. Pour connaître tous les détails, explorez les diverses options introduites avec la clause FOR SYSTEM_TIME.

Pour afficher les 10 principaux utilisateurs classés par nombre de pages web visitées durant la dernière heure, exécutez cette requête :

DECLARE @hourAgo datetime2 = DATEADD(HOUR, -1, SYSUTCDATETIME());
SELECT TOP 10 * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME AS OF @hourAgo
ORDER BY PagesVisited DESC

Vous pouvez facilement modifier cette requête pour analyser les visites du site au cours du dernier jour, du dernier mois ou à n’importe quel moment dans le passé.

Pour effectuer l’analyse statistique de base du jour précédent, utilisez l’exemple suivant :

DECLARE @twoDaysAgo datetime2 = DATEADD(DAY, -2, SYSUTCDATETIME());
DECLARE @aDayAgo datetime2 = DATEADD(DAY, -1, SYSUTCDATETIME());

SELECT UserID, SUM (PagesVisited) as TotalVisitedPages, AVG (PagesVisited) as AverageVisitedPages,
MAX (PagesVisited) AS MaxVisitedPages, MIN (PagesVisited) AS MinVisitedPages,
STDEV (PagesVisited) as StDevViistedPages
FROM dbo.WebsiteUserInfo
FOR SYSTEM_TIME BETWEEN @twoDaysAgo AND @aDayAgo
GROUP BY UserId

Pour rechercher les activités d’un utilisateur spécifique dans une période de temps, utilisez la clause CONTAINED IN :

DECLARE @hourAgo datetime2 = DATEADD(HOUR, -1, SYSUTCDATETIME());
DECLARE @twoHoursAgo datetime2 = DATEADD(HOUR, -2, SYSUTCDATETIME());
SELECT * FROM dbo.WebsiteUserInfo
FOR SYSTEM_TIME CONTAINED IN (@twoHoursAgo, @hourAgo)
WHERE [UserID] = 1;

La visualisation graphique est particulièrement pratique pour les requêtes temporelles, car vous pouvez afficher les tendances et les modèles d’utilisation de façon intuitive très facilement :

TemporalGraph

Évolution du schéma de table

En règle générale, vous devez modifier le schéma de table temporelle pendant le développement d’applications. Pour ce faire, exécutez simplement des instructions ALTER TABLE standard et Azure SQL Database ou Azure SQL Managed Instance propage les modifications de manière appropriée dans la table d’historique. Le script suivant montre comment ajouter un attribut supplémentaire pour le suivi :

/*Add new column for tracking source IP address*/
ALTER TABLE dbo.WebsiteUserInfo
ADD  [IPAddress] varchar(128) NOT NULL CONSTRAINT DF_Address DEFAULT 'N/A';

De même, vous pouvez modifier la définition de colonne pendant que votre charge de travail est active :

/*Increase the length of name column*/
ALTER TABLE dbo.WebsiteUserInfo
    ALTER COLUMN  UserName nvarchar(256) NOT NULL;

Enfin, vous pouvez supprimer une colonne dont vous n’avez plus besoin.

/*Drop unnecessary column */
ALTER TABLE dbo.WebsiteUserInfo
    DROP COLUMN TemporaryColumn;

Vous pouvez également utiliser la dernière version de SSDT pour modifier un schéma de table temporelle quand vous êtes connecté à la base de données (mode en ligne) ou dans le cadre du projet de base de données (mode hors connexion).

Contrôle de la rétention des données historiques

Avec les tables temporelles avec versions gérées par le système, la table d’historique peut augmenter la taille de la base de données davantage que les tables normales. Une table d’historique volumineuse et qui ne cesse de croître peut devenir un problème en termes de coûts de stockage purs et parce qu’elle nuit aux performances d’interrogation temporelle. Par conséquent, le développement d’une stratégie de rétention des données pour gérer les données dans la table d’historique est un aspect important de la planification et de la gestion du cycle de vie de chaque table temporelle. Avec Azure SQL Database et Azure SQL Managed Instance, vous bénéficiez des approches suivantes pour gérer les données historiques dans la table temporelle :

Notes

Dans les deux bases de données Azure SQL configuré pour la mise en miroir dans Fabric et base de données SQL Fabric, vous pouvez créer des tables temporelles, mais les tables d’historique respectives ne sont pas mises en miroir vers Fabric OneLake. Pour un comportement spécifique autour de la définition de l’indicateur de SYSTEM_VERSIONING sur les tables temporelles, consultez Créer une table temporelle avec version système.

Contenu connexe