System and method for querying a backup of a database system based on a temporal table
A method and system for generating a temporal, query-ready database backup. The method includes generating a plurality of backups of a database, each of the backups including a plurality of backup data objects, each of the backup data objects including a plurality of rows stored in a file having a column-oriented format, and each backup corresponding to a unique point in time; assigning each row in a file to a row group, the row group having a unique identifier; generating a temporal table including a column of row group identifiers for each backup of the plurality of backups; receiving a query for a backup of the plurality of backups created at different point in times; and generating a query result based on the temporal table and at least the respective backup data object, wherein results are from backups of the plurality of backups related to different point in time.
1 . A method for generating a temporal query ready database backup, the method comprising:
generating a plurality of backups of a database, each of the backups including a plurality of backup data objects, each of the backup data objects including a plurality of rows stored in a file having a column-oriented format, and each backup corresponding to a unique point in time;
assigning each row in a file to a row group, the row group having a unique identifier;
generating a temporal table including a first column of row group identifiers for each backup of the plurality of backups and a second column of point-in-time identifiers, each point-in-time identifier identifying a specific backup point;
receiving a query for a backup of the plurality of backups;
generating, based on the query and the temporal table, a temporal query;
executing the temporal query on a plurality of the backup data objects, wherein the temporal query is modified to return a value from a column indicating a row identifier; and
generating a query result based on the temporal table and at least the respective backup data object.
2 . The method of claim 1 , further comprising:
generating a relevancy table based on each unique identifier; and
inserting a first value in the relevancy table indicating a first time of entry for a unique identifier.
3 . The method of claim 2 , further comprising:
inserting a second value in the relevancy table indicating a final time of entry for the unique identifier.
4 . The method of claim 3 , further comprising:
inserting the second value in response to determining that a row group corresponding to the unique identifier includes a row with a changed value.
5 . The method of claim 2 , further comprising:
generating the query result further based on the relevancy table.
6 . The method of claim 1 , further comprising:
joining a plurality of query results, each query result based on a backup data object.
7 . The method of claim 6 , further comprising:
joining the plurality of query results based on a row identifier.
8 . The method of claim 1 , further comprising:
assigning each row to a row group based on a hash value generated for a row of the row group.
9 . The method of claim 1 , further comprising:
generating a plurality of temporal tables, each temporal table including identifiers of row groups stored in a backup generated at a time corresponding to a time associated with the temporal table.
10 . A non-transitory computer-readable medium storing a set of instructions for generating a temporal, query-ready database backup, the set of instructions comprising:
one or more instructions that, when executed by one or more processors of a device, cause the device to:
generate a plurality of backups of a database, each of the backups including a plurality of backup data objects, each of the backup data objects including a plurality of rows stored in a file having a column-oriented format, and each backup corresponding to a unique point in time;
assign each row in a file to a row group, the row group having a unique identifier;
generate a temporal table including a first column of row group identifiers for each backup of the plurality of backups and a second column of point-in-time identifiers, each point-in-time identifier identifying a specific backup point;
receive a query for a backup of the plurality of backups; generate, based on the query and the temporal table, a temporal query;
execute the temporal query on a plurality of the backup data objects, wherein the temporal query is modified to return a value from a column indicating a row identifier; and
generate a query result based on the temporal table and at least the respective backup data object.
11 . A system for generating a temporal, query-ready database backup, comprising:
one or more processors configured to:
generate a plurality of backups of a database, each of the backups including a plurality of backup data objects, each of the backup data objects including a plurality of rows stored in a file having a column-oriented format, and each backup corresponding to a unique point in time;
assign each row in a file to a row group, the row group having a unique identifier;
generate a temporal table including a first column of row group identifiers for each backup of the plurality of backups and a second column of point-in-time identifiers, each point-in-time identifier identifying a specific backup point;
receive a query for a backup of the plurality of backups;
generate, based on the query and the temporal table, a temporal query;
execute the temporal query on a plurality of the backup data objects, wherein the temporal query is modified to return a value from a column indicating a row identifier; and
generate a query result based on the temporal table and at least the respective backup data object.
12 . The system of claim 11 , wherein the one or more processors are further configured to:
generate a relevancy table based on each unique identifier; and
insert a first value in the relevancy table indicating a first time of entry for a unique identifier.
13 . The system of claim 12 , wherein the one or more processors are further configured to:
insert a second value in the relevancy table indicating a final time of entry for the unique identifier.
14 . The system of claim 13 , wherein the one or more processors are further configured to:
insert the second value in response to determining that a row group corresponding to the unique identifier includes a row with a changed value.
15 . The system of claim 12 , wherein the one or more processors are further configured to:
generate the query result further based on the relevancy table.
16 . The system of claim 11 , wherein the one or more processors are further configured to:
join a plurality of query results, each query result based on a backup data object.
17 . The system of claim 16 , wherein the one or more processors are further configured to:
join the plurality of query results based on a row identifier.
18 . The system of claim 11 , wherein the one or more processors are further configured to:
assign each row to a row group based on a hash value generated for a row of the row group.
19 . The system of claim 11 , wherein the one or more processors are further configured to:
generate a plurality of temporal tables, each temporal table including identifiers of row groups stored in a backup generated at a time corresponding to a time associated with the temporal table.