Tracking changes in data
The subject technology executes a change on an existing micro-partition of a table, the executing of the change comprising generating a new micro-partition that embodies the change and deleting the existing micro-partition. The subject technology receives a request for a delta for the table between a first timestamp and a second timestamp. The subject technology responsively determines the delta based on a table history, the delta including information indicating at least one operation that was performed to at least one row of a set of rows of the table, without including information as to intermediate changes made to at least one row of the set of rows of the table between the first timestamp and the second timestamp, that facilitates a reduction in storage of historical versions of the set of rows of the table.
1. A method comprising:
executing a change on an existing micro-partition of a table, the executing of the change comprising generating a new micro-partition that embodies the change and deleting the existing micro-partition from the table;
receiving a request for a delta for the table between a first timestamp and a second timestamp;
responsively determining the delta based on a table history, the delta including information indicating at least one operation that was performed to at least one row of a set of rows of the table, without including information as to intermediate changes made to at least one row of the set of rows of the table between the first timestamp and the second timestamp, that facilitates a reduction in storage of historical versions of the set of rows of the table; and
providing a micro-partition storing the delta for the table and corresponding data indicating prior values of the at least one row of the set of rows of the table.
2. The method of claim 1 , further comprising:
updating the table history that comprises a log of changes made to the table, each change in the log of changes comprising a timestamp, the updating of the table history comprising inserting an indication of the change into the log of changes.
3. The method of claim 2 , wherein the timestamp indicates when a corresponding change was made or indicates a transaction that initiated the corresponding change.
4. The method of claim 2 , wherein the log of changes include historical data indicating rows that changed, a micro-partition where a row was originally stored, a prior row identifier for the row, or an indication of whether the row was updated.
5. The method of claim 2 , wherein the log of changes is stored as one or more change tracking columns stored within the table.
6. The method of claim 2 , wherein the delta indicates that a transaction based on the updated table history has completed.
7. The method of claim 2 , wherein the log of changes in the table history comprises a lineage for each row in the table over a time period.
8. The method of claim 7 , wherein the lineage for each row comprises a log of modifications to each row with a timestamp associated with each modification.
9. The method of claim 7 , wherein the lineage for each row includes null values when a new row is generated in the table.
10. The method of claim 7 , wherein the lineage for a particular row comprises a first lineage of micro-partitions in which the particular row has been stored and a second lineage of ordinal numbers for the particular row in the micro-partitions in which the particular row has been stored.
11. A system comprising:
at least one processor; and
one or more non-transitory computer readable storage media containing instructions executable by the at least one processor for causing the at least one processor to perform operations comprising:
executing a change on an existing micro-partition of a table, the executing of the change comprising generating a new micro-partition that embodies the change and deleting the existing micro-partition;
receiving a request for a delta for the table between a first timestamp and a second timestamp; and
responsively determining the delta based on a table history, the delta including information indicating at least one operation that was performed to at least one row of a set of rows of the table, without including information as to intermediate changes made to at least one row of the set of rows of the table between the first timestamp and the second timestamp, that facilitates a reduction in storage of historical versions of the set of rows of the table; and
providing a micro-partition storing the delta for the table and corresponding data indicating prior values of the at least one row of the set of rows of the table.
12. The system of claim 11 , wherein the operations further comprise:
updating the table history that comprises a log of changes made to the table, each change in the log of changes comprising a timestamp, the updating of the table history comprising inserting an indication of the change into the log of changes.
13. The system of claim 12 , wherein the timestamp indicates when a corresponding change was made or indicates a transaction that initiated the corresponding change.
14. The system of claim 12 , wherein the log of changes include historical data indicating rows that changed, a micro-partition where a row was originally stored, a prior row identifier for the row, or an indication of whether the row was updated.
15. The system of claim 12 , wherein the log of changes is stored as one or more change tracking columns stored within the table.
16. The system of claim 12 , wherein the delta indicates that a transaction based on the updated table history has completed.
17. The system of claim 12 , wherein the log of changes in the table history comprises a lineage for each row in the table over a time period.
18. The system of claim 17 , wherein the lineage for each row comprises a log of modifications to each row with a timestamp associated with each modification.
19. The system of claim 17 , wherein the lineage for each row includes null values when a new row is generated in the table.
20. The system of claim 17 , wherein the lineage for a particular row comprises a first lineage of micro-partitions in which the particular row has been stored and a second lineage of ordinal numbers for the particular row in the micro-partitions in which the particular row has been stored.
21. A non-transitory computer readable storage media containing instructions executable by at least one processor for causing the at least one processor to perform operations comprising:
executing a change on an existing micro-partition of a table, the executing of the change comprising generating a new micro-partition that embodies the change and deleting the existing micro-partition from the table;
receiving a request for a delta for the table between a first timestamp and a second timestamp;
responsively determining the delta based on a table history, the delta including information indicating at least one operation that was performed to at least one row of a set of rows of the table, without including information as to intermediate changes made to at least one row of the set of rows of the table between the first timestamp and the second timestamp, that facilitates a reduction in storage of historical versions of the set of rows of the table; and
providing a micro-partition storing the delta for the table and corresponding data indicating prior values of the at least one row of the set of rows of the table.
22. The non-transitory computer readable storage media of claim 21 , wherein the operations further comprise:
updating the table history that comprises a log of changes made to the table, each change in the log of changes comprising a timestamp, the updating of the table history comprising inserting an indication of the change into the log of changes.
23. The non-transitory computer readable storage media of claim 22 , wherein the timestamp indicates when a corresponding change was made or indicates a transaction that initiated the corresponding change.
24. The non-transitory computer readable storage media of claim 22 , wherein the log of changes include historical data indicating rows that changed, a micro-partition where a row was originally stored, a prior row identifier for the row, or an indication of whether the row was updated.
25. The non-transitory computer readable storage media of claim 22 , wherein the log of changes is stored as one or more change tracking columns stored within the table.
26. The non-transitory computer readable storage media of claim 22 , wherein the operations further comprise:
providing the micro-partition storing the delta for the table, in response to the request for the delta, the delta indicating that a transaction based on the updated table history has completed.
27. The non-transitory computer readable storage media of claim 22 , wherein the log of changes in the table history comprises a lineage for each row in the table over a time period.
28. The non-transitory computer readable storage media of claim 27 , wherein the lineage for each row comprises a log of modifications to each row with a timestamp associated with each modification.
29. The non-transitory computer readable storage media of claim 27 , wherein the lineage for each row includes null values when a new row is generated in the table.
30. The non-transitory computer readable storage media of claim 27 , wherein the lineage for a particular row comprises a first lineage of micro-partitions in which the particular row has been stored and a second lineage of ordinal numbers for the particular row in the micro-partitions in which the particular row has been stored.