Comment stocker des données historiques

Certains collègues et moi-même sums entrés dans un débat sur la meilleure façon de stocker des données historiques. Actuellement, pour certains systèmes, j’utilise une table distincte pour stocker les données historiques et je conserve une table originale pour l’enregistrement actif en cours. Alors, disons que j’ai la table FOO. Sous mon système, tous les enregistrements actifs iront dans FOO, et tous les enregistrements historiques iront dans FOO_Hist. De nombreux champs différents dans FOO peuvent être mis à jour par l’utilisateur, je veux donc garder un compte exact de tout ce qui a été mis à jour. FOO_Hist contient exactement les mêmes champs que FOO à l’exception d’un HIST_ID auto-incrémenté. Chaque fois que FOO est mis à jour, j’effectue une instruction d’insertion dans FOO_Hist similaire à: insert into FOO_HIST select * from FOO where id = @id .

Mon collègue dit que c’est une mauvaise conception parce que je ne devrais pas avoir une copie exacte d’une table pour des raisons historiques et que je devrais simplement insérer un autre enregistrement dans la table active avec un indicateur indiquant que c’est pour des raisons historiques.

Existe-t-il une norme pour gérer le stockage de données historiques? Il me semble que je ne veux pas encombrer mes dossiers actifs avec tous mes dossiers historiques dans la même table, étant donné que cela pourrait dépasser le million d’enregistrements (je pense à long terme).

Comment vous ou votre entreprise gérez-vous cela?

J’utilise MS SQL Server 2008, mais j’aimerais conserver la réponse générique et arbitraire de tout SGBD.

La prise en charge des données historiques directement dans un système opérationnel rendra votre application beaucoup plus complexe qu’elle ne le serait autrement. En règle générale, je ne recommanderais pas de le faire, sauf si vous avez des difficultés à manipuler les versions historiques d’un enregistrement dans le système.

Si vous regardez attentivement, la plupart des exigences relatives aux données historiques appartiennent à l’une des deux catégories suivantes:

  • Journalisation de l’audit: Il est préférable de procéder avec les tables d’audit. Il est assez facile d’écrire un outil qui génère des scripts pour créer des tables et des déclencheurs de journal d’audit en lisant les métadonnées du dictionnaire de données système. Ce type d’outil peut être utilisé pour convertir la journalisation de l’audit sur la plupart des systèmes. Vous pouvez également utiliser ce sous-système pour modifier la capture de données si vous souhaitez implémenter un entrepôt de données (voir ci-dessous).

  • Rapports historiques: Rapports sur l’état historique, les positions «au fur et à mesure» ou les rapports analytiques au fil du temps. Il est possible de satisfaire aux exigences de rapport historiques simples en interrogeant les tables de journalisation d’audit du type décrit ci-dessus. Si vous avez des exigences plus complexes, il peut être plus économique d’implémenter un datamart pour le reporting que d’essayer d’intégrer directement l’historique dans le système opérationnel.

    Les dimensions qui changent lentement sont de loin le mécanisme le plus simple pour suivre et interroger l’état historique et une grande partie du suivi de l’historique peut être automatisée. Les gestionnaires génériques ne sont pas si difficiles à écrire. En règle générale, les rapports historiques ne nécessitent pas l’utilisation de données actualisées, de sorte qu’un mécanisme d’actualisation par lots est normalement correct. Cela permet de conserver une architecture de système de base et de génération de rapports relativement simple.

Si vos besoins relèvent de l’une de ces deux catégories, il est probablement préférable de ne pas stocker de données historiques dans votre système opérationnel. Séparer la fonctionnalité historique en un autre sous-système sera probablement moins compliqué dans l’ensemble et produira des bases de données transactionnelles et d’audit / de rapport qui fonctionneront beaucoup mieux aux fins prévues.

Je ne pense pas qu’il existe un moyen standard de le faire, mais je pensais que je lancerais une méthode possible. Je travaille dans Oracle et dans notre cadre d’application Web interne qui utilise XML pour stocker les données d’application.

Nous utilisons quelque chose appelé un modèle Master-Detail qui consiste à:

Master Table par exemple appelée Widgets ne contenant souvent qu’un identifiant. Contiendra souvent des données qui ne changeront pas avec le temps / ne sont pas historiques.

Table Detail / History appelée par exemple Widget_Details contenant au moins:

  • ID – clé primaire. ID détail / historique
  • MASTER_ID – par exemple dans ce cas appelé ‘WIDGET_ID’, ceci est le FK à l’enregistrement maître
  • START_DATETIME – horodatage indiquant le début de cette ligne de firebase database
  • END_DATETIME – horodatage indiquant la fin de la ligne de cette firebase database
  • STATUS_CONTROL – colonne de caractère unique indiquant le statut de la ligne. “C” indique le courant, NULL ou “A” serait historique / archivé. Nous l’utilisons uniquement parce que nous ne pouvons pas indexer sur END_DATETIME étant NULL
  • CREATED_BY_WUA_ID – stocke l’ID du compte à l’origine de la création de la ligne
  • XMLDATA – stocke les données réelles

Ainsi, une entité commence par avoir 1 ligne dans le maître et 1 dans le détail. Le détail ayant une date de fin NULL et STATUS_CONTROL de ‘C’. Lorsqu’une mise à jour se produit, la ligne en cours est mise à jour pour avoir END_DATETIME de l’heure actuelle et status_control est défini sur NULL (ou ‘A’ si vous préférez). Une nouvelle ligne est créée dans la table de détail, toujours liée au même maître, avec status_control ‘C’, l’id de la personne effectuant la mise à jour et les nouvelles données stockées dans la colonne XMLDATA.

C’est la base de notre modèle historique. La logique de création / mise à jour est gérée dans un package Oracle PL / SQL afin que vous passiez simplement à la fonction l’ID en cours, votre ID utilisateur et les nouvelles données XML et en interne toutes les mises à jour / insertions pour les représenter dans le modèle historique . Les heures de début et de fin indiquent à quel moment cette ligne de la table est active.

Le stockage est bon marché, nous ne supprimons généralement pas les données et préférons conserver une piste d’audit. Cela nous permet de voir à quoi nos données ressemblaient à un moment donné. En indexant status_control = ‘C’ ou en utilisant une vue, l’encombrement n’est pas exactement un problème. Évidemment, vos requêtes doivent prendre en compte le fait que vous devez toujours utiliser la version actuelle (NULL end_datetime et status_control = ‘C’) d’un enregistrement.

Je pense que vous approchez est correct. La table historique doit être une copie de la table principale sans index, assurez-vous également que l’horodatage de la mise à jour figure également dans la table.

Si vous essayez l’autre approche assez rapidement, vous rencontrerez des problèmes:

  • frais généraux d’entretien
  • plus de drapeaux dans les sélections
  • ralentissement des requêtes
  • croissance des tables, des index

cette question est plutôt ancienne mais les gens travaillent encore sur ce sujet. donc, si vous utilisez oracle, vous pourriez être intéressé par le retour en arrière d’Oracle: http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm

Vous pourriez juste partitionner les tables non?

“Stratégies de tables et d’index partitionnées utilisant SQL Server 2008 Lorsqu’une table de firebase database atteint des centaines de giga-octets ou plus, il peut s’avérer plus difficile de charger de nouvelles données, de supprimer d’anciennes données et de gérer des index. Les données devant être chargées ou supprimées peuvent être très importantes, rendant les opérations INSERT et DELETE sur la table peu pratiques.Le logiciel de firebase database Microsoft SQL Server 2008 fournit un partitionnement de table pour rendre ces opérations plus faciles à gérer. ”

La vraie question est de savoir si vous devez utiliser des données historiques et des données actives pour créer des rapports? Si c’est le cas, conservez-les dans une table, partitionnez et créez une vue pour les enregistrements actifs à utiliser dans les requêtes actives. Si vous avez seulement besoin de les regarder de temps en temps (pour rechercher des problèmes juridiques ou autres), alors placez-les dans un tableau séparé.

Une autre option consiste à archiver les données opérationnelles sur une base [quotidienne | horaire | quelconque]. La plupart des moteurs de firebase database prennent en charge l’extraction des données dans une archive .

Fondamentalement, l’idée est de créer un travail Windows ou CRON planifié

  1. détermine les tables en cours dans la firebase database opérationnelle
  2. sélectionne toutes les données de chaque table dans un fichier CSV ou XML
  3. compresse les données exscopes dans un fichier ZIP, de préférence avec l’horodatage de la génération dans le nom du fichier pour faciliter l’archivage.

De nombreux moteurs de firebase database SQL sont fournis avec un outil pouvant être utilisé à cette fin. Par exemple, lorsque vous utilisez MySQL sous Linux, la commande suivante peut être utilisée dans un travail CRON pour planifier l’extraction:

 mysqldump --all-databases --xml --lock-tables=false -ppassword | gzip -c | cat > /media/bak/servername-$(date +%Y-%m-%d)-mysql.xml.gz 

Je connais ce vieux post mais je voulais juste append quelques points. La norme pour de tels problèmes est ce qui fonctionne le mieux pour la situation. Il est très important de comprendre le besoin d’un tel stockage et l’utilisation potentielle des données de suivi historiques / d’audit / de modification.

Audit (objective de sécurité) : utilisez un tableau commun pour toutes vos tables auditables. définir la structure pour stocker le nom de la colonne, avant les champs valeur et après valeur.

Archive / historique : pour le suivi des adresses précédentes, des numéros de téléphone, etc., créer une table distincte FOO_HIST est préférable si votre schéma de table de transactions actif ne change pas de manière significative à l’avenir (si votre table d’historique doit avoir la même structure). Si vous prévoyez la normalisation des tables, l’ajout / suppression de modifications de type de données, stockez vos données historiques au format xml. définir un tableau avec les colonnes suivantes (ID, Date, Version du schéma, XMLData). Cela permettra de gérer facilement les modifications de schéma. mais vous devez traiter avec XML et cela pourrait introduire un niveau de complication pour la récupération des données.

Vous pouvez utiliser la fonctionnalité MSSQL Server Auditing. A partir de la version SQL Server 2012, vous trouverez cette fonctionnalité dans toutes les éditions:

http://technet.microsoft.com/en-us/library/cc280386.aspx

Vous pouvez créer des vues matérialisées / indexées sur la table. Selon vos besoins, vous pouvez effectuer une mise à jour complète ou partielle des vues. Veuillez voir ceci pour créer mview et log. Comment créer des vues matérialisées dans SQL Server?

Je voulais juste append une option que j’ai commencé à utiliser parce que j’utilise Azure SQL et que la table multiple était trop lourde pour moi. J’ai ajouté un déclencheur d’insertion / mise à jour / suppression sur ma table, puis converti le changement avant / après en fichier json à l’aide de la fonctionnalité “FOR JSON AUTO”.

  SET @beforeJson = (SELECT * FROM DELETED FOR JSON AUTO) SET @afterJson = (SELECT * FROM INSERTED FOR JSON AUTO) 

Cela renvoie une représentation JSON pour l’enregistrement avant / après la modification. Je stocke ensuite ces valeurs dans une table d’historique avec un horodatage de la modification (je stocke également l’ID pour l’enregistrement en cours). En utilisant le processus de sérialisation, je peux contrôler la manière dont les données sont remplacées en cas de modification du schéma.

J’ai appris à ce sujet à partir de ce lien ici

Changer la capture de données: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017

Il est pris en charge dans SQL Server 2008 R2, il peut avoir été pris en charge dans SQL Server 2008.