3.1 Historical Data Management - Gs1TestTeam/MongoDB_Task GitHub Wiki

3.1 Historical Data Management

1. What is the purpose of historical data?

Analyzing audit fields for security reason
Tracking data changes

2. Schema Design for Historical Data

When we are talking about Schema Design for Historical Data, there is no specific rules. However, there are some considerable parts like below:

All collections include a sequential number field
All collections include an audit trail
Historical collections store data on different physical space (collection)
Historical collections include full document of active collection

2-1. what is audit trail: a security-relevant chronological record (Wikipedia)

Example:

CREATE_PROGRAM_ID: application id that creates data

CREATE_OBJECT_TIME: created time

UPDATE_PROGRAM_ID: application id that updates data

UPDATE_OBJECT_TIME: updated time

ARCHIVE_PROGRAM_ID: application id that archive data

ARCHIVE_OBJECT_TIME: archived time

ARCHIVE_STATUS_CODE: archive yes/no (*: be subject to archiving)

2-2. Example of Historical Collection

[Active Collection]

{
     _ID: "1", // 1) increment version number
     GTIN: "1000000001",
     NAME: "RED",
     ...
        CREATE_PROGRAM_ID: "CreateGtinPgm", // 2) Audit fields
        CREATE_OBJECT_TIME: "2018-01-01 21:00.36",
        UPDATE_PROGRAM_ID: "UpdateGtinPgm",
        UPDATE_OBJECT_TIME: "2018-01-05 21:00.36",
        ARCHIVE_PROGRAM_ID: "PurgeDataBatPgm",
        ARCHIVE_OBJECT_TIME: "2018-04-05 21:00.36",
        ARCHIVE_STATUS_CODE: "*"
}

[Historical Collection]

{ // 3) different physical space
     _ID: 1,
     _ID_ORIGIN: 1, // 4)  full document of active collection
     GTIN_ORIGIN: "1000000001",
     NAME_ORIGIN: "RED",
     ...
        CREATE_PROGRAM_ID_ORIGIN: "CreateGtinPgm",
        CREATE_OBJECT_TIME_ORIGIN: "2018-01-01 21:00.36",
        UPDATE_PROGRAM_ID_ORIGIN: "UpdateGtinPgm",
        UPDATE_OBJECT_TIME_ORIGIN: "2018-01-05 21:00.36",
        ARCHIVE_PROGRAM_ID_ORIGIN: "PurgeDataBatPgm",
        ARCHIVE_OBJECT_TIME_ORIGIN: "2018-04-05 21:00.36",
        ARCHIVE_STATUS_CODE_ORIGIN: "*",
        CREATE_PROGRAM_ID : "CreateHisPgm",
        CREATE_OBJECT_TIME : "2018-01-01 21:00.36",
        UPDATE_PROGRAM_ID : "UpdateHisPgm",
        UPDATE_OBJECT_TIME: "2018-01-05 21:00.36",
        ARCHIVE_PROGRAM_ID : "PurgeDataBatPgm",
        ARCHIVE_OBJECT_TIME : "2018-04-05 21:00.36",
        ARCHIVE_STATUS_CODE : "*"
}

3. Managing Historical Data: implement functionalities in application level

When the application inserts new rows in progress table, then it inserts the rows to history table at once.

Historical Data

When the application update the existing rows in progress table, then it inserts the rows to history table at once.

Historical Data

Using the gathering data in historical table, we can track data changes history and analyze audit fields for security reason.

Historical Data

4. Archiving and Purging Data: implement Batch Jobs and run them in scheduler

Archiving: moving data that is no longer actively used to a separate storage such as .tar or .zip file (techtarget.com)
Purging: freeing up space in the database (dba.stackexchange.com)
Active data and Historical data are different in archiving and purging period.
The period of Active data is shorter because of the query speed ( about 3 months)
The period of Historical data is longer because of analysis purpose ( about 3 years)

Procedure for Archiving and Purging: 

First batch job marks asterisk (*) to the subject data for purging
Second batch job archives the marked data
Third batch job purges the archived data

For example, if GTIN 10000001 is subject to purging, then First Batch job marks the '*'s

Historical Data

Second Batch job makes archiving file and move to other storage in proper time

umount /dev/vg0/mdb-snap01 dd if=/dev/vg0/mdb-snap01 | gzip > mdb-snap01.gz

Third Batch job purges the marked data in proper time

Historical Data

5. MongoDB solution

Automatic Archiving and Purging

Mongo DB has not supported native Job scheduling. We should use other scheduling tools such as Linux Cron, Windows Scheduler, or other scheduling tools.

Batch Job Program List

Seq	Program Name	Description
1	ExtractDataJob	Find purge data and mark '*'
2	ArchiveDataJob	Create an archiving file with the marked data
3	PurgeDataJob	Delete the marked data

Batch Job Program Detail

ExtractDataJob: Find purge data and mark '*'

pseudo code:

1) Create 'ExtractDataForPurge' store procedure

function ExtractDataForPurge () {
    1) Extract the purge data // refer to the purge rule
    2) Mark the '*' in the extracted data
}
2) Periodically call 'ExtractDataForPurge' procedure in ExtractDataJob on scheduler
     // refer to the business rule

ArchiveDataJob: Create an archiving file with the marked data

1) Create 'ArchiveDataJob' store procedure

function ArchiveDataForPurge () {
    1) Create a 'Snapshot' for the marked data 
        // cursor.snapshot()
    2) Create an 'Archving file'
       // umount /dev/vg0/mdb-snap01 dd if=/dev/vg0/mdb-snap01 | gzip > mdb-snap01.gz
    3) Move an 'Archving file' to the specific location of specific server
    4) Update ARCHIVE_PROGRAM_ID, ARCHIVE_OBJECT_TIME
}

2) Periodically, call 'ArchiveDataForPurge' procedure in ArchivingDataJob  on scheduler
     // refer to the business rule
```

- PurgeDataJob: Delete the marked data

```js
1) Create 'PurgeDataForPurge' store procedure

function PurgeDataJob () {
    1) Delete the marked data
        // db.delete({ARCHIVE_OBJECT_TIME: not null})
}

2) Periodically, call 'PurgeDataForPurge' procedure in PurgeDataJob  on scheduler
     // refer to the business rule

Restoring: store data from archiving file to MongoDB

To restore data, issue the following sequence of commands:

lvcreate --size 1G --name mdb-new vg0 gzip -d -c mdb-snap01.gz | dd of=/dev/vg0/mdb-new mount /dev/vg0/mdb-new /srv/mongodb

As more considerable topics:

What is difference between archiving and backup? 

Archival vs Backup

An archive is a collection of historical records that are kept for long-term retention and used for future reference. Typically, archives contain data that is not actively used. Basically, a backup is a copy of a set of data, while an archive holds original data that has been removed from its original location.

https://www.solarwindsmsp.com/content/backup-archive

What is difference between purging and deleting?

Purging

Database administrators purge data from tables when the table rows exceed several hundreds or even millions of records that are no longer needed. However, purging lets you archive the records even though you remove them from tables. Administrators move records from a large table to an archive table. Purging records from a large table speeds up queries.

Deleting

Deleting information removes it permanently from a table, and this task doesn't keep a copy of the records. Deleting is done by the administrator or you can delete records from your users' application. Deleting a record, for example, is used when the data entry employee makes a mistake when creating a record and needs to delete the bad record from the database.

https://itstillworks.com/difference-between-purging-deleting-data-entry-12082.html

What is difference between recovery and restore?

Difference between restoring and recovering. ... Recovering files typically refers to saving one or more files, while a restore usually refers replacing a complete system or hard drive from a full system backup. Restore means restoring a database backup from a backup medium. Recovery" means recovering redolog information.

https://pc.net/helpcenter/answers/difference_between_recover_and_restore