Tracking changes in database data
The subject technology executes a change on an existing micro-partition of a table of a database, the executing of the change comprising generating a new micro-partition that embodies the change. The subject technology receives a request for a delta for the table between a first timestamp and a second timestamp. The subject technology queries at least one change tracking column to determine the delta between the first timestamp and the second timestamp, the delta including information indicating at least one database 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 immutable micro-partition of a table of a database, the executing of the change comprising generating a new immutable micro-partition that embodies the change;
receiving, from a request processing service, a request for a delta for the table bet wen a first timestamp and a second timestamp; and
querying at least one change tracking column to determine the delta between the first timestamp and the second timestamp, the delta including information indicating at least one database 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, to the request processing service, the delta for the table, the delta indicating that a transaction based on executing the change has completed.
2. The method of claim 1 , further comprising:
updating a 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 table history is stored within an immutable micro-partition.
4. The method of claim 1 , further comprising:
updating one or more change tracking columns in the new micro-partition.
5. The method of claim 4 , wherein the one or more change tracking columns indicate one or more of:
a prior immutable micro-partition associated with a row in the new immutable micro-partition, a prior row identification associated with a row in the new immutable micro-partition, or a prior transaction associated with a row in the new immutable micro-partition.
6. The method of claim 1 , further comprising:
comparing a first set of data associated with the first timestamp and a second set of data associated with the second timestamp, wherein the comparing comprises determining row granularity changes between added and removed micro-partitions between the first set of data and the second set of data.
7. The method of claim 1 , wherein the at least one change tracking column indicates a most recent change that occurred on a row, or a log of changes that have occurred on the table.
8. The method of claim 1 , wherein the new immutable micro-partition comprises an immutable storage device that is unable to be updated in place.
9. The method of claim 1 , further comprising:
determining a timestamp to a transaction that occurred on the table; and
determining a timestamp to each modification that occurs on one or more rows of the table.
10. 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 immutable micro-partition of a table of a database, the executing of the change comprising generating a new immutable micro-partition that embodies the change;
receiving, from a request processing service, a request for a delta for the table between a first timestamp and a second timestamp; and
querying at least one change tracking column to determine the delta between the first timestamp and the second timestamp, the delta including information indicating at least one database 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 to the request processing service, the delta for the table, the delta indicating that a transaction based on executing the change has completed.
11. The system of claim 10 , wherein the operations further comprise:
updating a 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.
12. The system of claim 11 , wherein the table history is stored within an immutable micro-partition.
13. The system of claim 10 , wherein the operations further comprise:
updating one or more change tracking columns in the new micro-partition.
14. The system of claim 13 , wherein the one or more change tracking columns indicate one or more of:
a prior immutable micro-partition associated with a row in the new immutable micro-partition, a prior row identification associated with a row in the new immutable micro-partition, or a prior transaction associated with a row in the new immutable micro-partition.
15. The system of claim 10 , wherein the operations further comprise:
comparing a first set of data associated with the first timestamp and a second set of data associated with the second timestamp, wherein the comparing comprises determining row granularity changes between added and removed micro-partitions between the first set of data and the second set of data.
16. The system of claim 10 , wherein the at least one change tracking column indicates a most recent change that occurred on a row, or a log of changes that have occurred on the table.
17. The system of claim 10 , wherein the new immutable micro-partition comprises an immutable storage device that is unable to be updated in place.
18. The system of claim 10 , wherein the operations further comprise:
determining a timestamp to a transaction that occurred on the table; and
determining a timestamp to each modification that occurs on one or more rows of the table.
19. 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 immutable micro-partition of a table of a database, the executing of the change comprising generating a new immutable micro-partition that embodies the change;
receiving, from a request processing service, a request for a delta for the table between a first timestamp and a second timestamp;
querying at least one change tracking column to determine the delta between the first timestamp and the second timestamp, the delta including information indicating at least one database 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, to the request processing service, the delta for the table, the delta indicating that a transaction based on executing the change has completed.
20. The non-transitory computer readable storage media of claim 19 , wherein the operations further comprise:
updating a 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.
21. The non-transitory computer readable storage media of claim 20 , wherein the table history is stored within an immutable micro-partition.
22. The non-transitory computer readable storage media of claim 19 , wherein the operations further comprise:
updating one or more change tracking columns in the new micro-partition.
23. The non-transitory computer readable storage media of claim 22 , wherein the one or more change tracking columns indicate one or more of:
a prior immutable micro-partition associated with a row in the new immutable micro-partition, a prior row identification associated with a row in the new immutable micro-partition, or a prior transaction associated with a row in the new immutable micro-partition.
24. The non-transitory computer readable storage media of claim 19 , wherein the operations further comprise:
comparing a first set of data associated with the first timestamp and a second set of data associated with the second timestamp, wherein the comparing comprises determining row granularity changes between added and removed micro-partitions between the first set of data and the second set of data.
25. The non-transitory computer readable storage media of claim 19 , wherein the at least one change tracking column indicates a most recent change that occurred on a row, or a log of changes that have occurred on the table.
26. The non-transitory computer readable storage media of claim 19 , wherein the new immutable micro-partition comprises an immutable storage device that is unable to be updated in place.
27. The non-transitory computer readable storage media of claim 19 , wherein the operations further comprise:
determining a timestamp to a transaction that occurred on the table; and
determining a timestamp to each modification that occurs on one or more rows of the table.