Task scheduling in database systems
Systems, methods, and devices for executing a task on database data in response to a trigger event are disclosed. A method includes executing a transaction on a table comprising database data, wherein executing the transaction comprises generating a new table version. The method includes, in response to the transaction being fully executed, generating a change tracking entry comprising an indication of one or more modifications made to the table by the transaction and storing the change tracking entry in a change tracking stream. The method includes executing a task on the new table version in response to a trigger event.
1. A method comprising:
executing a transaction on a table comprising one or more immutable micro-partitions storing database data, the executing of the transaction comprising generating a new table version on a new micro-partition, the new table version including updated database data that reflects the transaction;
in response to the transaction being fully executed, generating a change tracking entry distinct from the new table, comprising an indication of one or more modifications made to the table by the transaction;
entering the change tracking entry into a change tracking stream;
executing a task, distinct from the transaction, on the new table version;
querying the change tracking stream to determine whether the table can be discarded; and
discarding the table based on a determination that a query result from the change tracking stream indicates the table can be discarded.
2. The method of claim 1 , wherein the transaction comprises ingesting new data into the table, the method further comprising:
storing the new data in a staging table; and
transforming the new data in the staging table for storage in one or more target tables.
3. The method of claim 1 , further comprising advancing a stream offset in the change tracking stream in response to the change tracking entry being entered into in the change tracking stream.
4. The method of claim 1 , further comprising:
removing at least one immutable micro-partition of the one or more immutable micro-partitions including database data prior to execution of the transaction.
5. The method of claim 1 , wherein the transaction comprises one or more of an insertion of data into the table, a deletion of data from the table, and an update of data in the table.
6. The method of claim 1 , wherein the change tracking entry further comprises one or more of:
a timestamp indicating when the transaction was requested;
a timestamp indicating when the transaction was fully executed;
an identifier of a user that requested the transaction;
an identifier of an account that requested the transaction; and
a minimum and maximum data value pair for data inserted by the transaction.
7. The method of claim 1 , wherein the task comprises user-defined logic comprising one or more structured query language (SQL) statements.
8. The method of claim 1 , wherein:
the change tracking stream advances after the transaction is fully and successfully executed; and
the task is executed on the new table version once.
9. The method of claim 1 , further comprising, in response to executing the task on the new table version, generating a task history entry comprising one or more of:
a task name;
a task identification;
an execution timestamp indicating when the task was executed;
an execution status indicating whether the task was successfully executed or whether an error was returned;
a message comprising an error code in response to the task not being executed successfully; and
one or more results returned by executing the task.
10. The method of claim 1 , further comprising retrieving the task from database schema, wherein the task comprises one or more of:
a timestamp indicating when the task was received;
a timestamp indicating when the task was generated;
a task name;
a database name indicating the database the task is to be executed;
an identifier of an owner of the task;
an identifier of a creator of the task;
a task schedule for the task;
a structured query language (SQL) script;
a last execution timestamp indicating a last time the task was executed; and
a last execution status indicating whether the task was executed successfully the last time the task was executed.
11. A system comprising:
at least one processor; and
one or more non-transitory computer readable storage media containing instructions that, when executed by the at least one processor, cause the at least one processor to perform the operations comprising:
executing a transaction on a table comprising one or more immutable micro-partitions storing database data, the executing of the transaction comprising generating a new table version on a new micro-partition, the new table version including updated database data that reflects the transaction;
generating a change tracking entry in response to the transaction being fully executed, the change tracking entry being distinct from the new table and comprising an indication of one or more modifications made to the table by the transaction;
entering the change tracking entry into a change tracking stream;
executing a task on the new table version;
querying the change tracking stream for determining the table can be discarded; and
discarding the table based on a determination that a query result from the change tracking stream indicates the table can be discarded.
12. The system of claim 11 , wherein the transaction comprises ingesting new data into the table, the system further comprising:
storing the new data in a staging table; and
transforming the new data in the staging table for storage in one or more target tables.
13. The system of claim 11 , further comprising advancing a stream offset in the change tracking stream in response to the change tracking entry being entered into in the change tracking stream.
14. The system of claim 11 , further comprising:
removing at least one immutable micro-partition of the one or more immutable micro-partitions including database data prior to execution of the transaction.
15. The system of claim 11 , wherein:
the change tracking stream advances after the transaction is fully and successfully executed; and
the task is executed on the new table version once.
16. Non-transitory computer readable storage media storing instructions that, when executed by one or more processors, cause the one or more processors to perform operations comprising:
executing a transaction on a table comprising one or more immutable micro-partitions storing database data, the executing of the transaction comprising generating a new table version on a new micro-partition, the new table version including updated database data that reflects the transaction;
in response to the transaction being fully executed, generating a change tracking entry distinct from the new table, comprising an indication of one or more modifications made to the table by the transaction;
entering the change tracking entry into a change tracking stream;
executing a task on the new table version;
querying the change tracking stream for determining the table can be discarded; and
discarding the table based on a determination that a query result from the change tracking stream indicates the table can be discarded.
17. The non-transitory computer readable storage media of claim 16 , wherein the transaction comprises ingesting new data into the table, the operations further comprising:
storing the new data in a staging table; and
transforming the new data in the staging table for storage in one or more target tables.
18. The non-transitory computer readable storage media of claim 16 , the operations further comprising advancing a stream offset in the change tracking stream in response to the change tracking entry being entered into in the change tracking stream.
19. The non-transitory computer readable storage media of claim 16 , further comprising:
removing at least one immutable micro-partition of the one or more immutable micro-partitions including database data prior to execution of the transaction.
20. The non-transitory computer readable storage media of claim 16 , wherein:
the change tracking stream advances after the transaction is fully and successfully executed; and
the task is executed on the new table version once.
21. The method of claim 1 , further comprising advancing a stream offset in the change tracking stream after a determination that the transaction fully commits.
22. The system of claim 11 , further comprising advancing a stream offset in the change tracking stream after a determination that the transaction fully commits.
23. The computer readable storage media of claim 16 , the operations further comprising advancing a stream offset in the change tracking stream after a determination that the transaction fully commits.
24. A system comprising:
one or more processors; and
data storage containing instructions executable by the one or more processors to perform operations comprising:
executing a transaction on a table comprising one or more immutable micro-partitions storing database data, the executing of the transaction comprising generating a new table version on a new micro-partition, the new table version including updated database data that reflects the transaction;
in response to the transaction being fully executed, generating a change tracking entry distinct from the new table, comprising an indication of one or more modifications made to the table by the transaction;
entering the change tracking entry into a change tracking stream;
executing a task on the new table version;
querying the change tracking stream for determining the table can be discarded; and
discarding the table based on a determination that a query result from the change tracking stream indicates the table can be discarded.
25. The system of claim 24 , wherein the transaction comprises ingesting new data into the table, the operations further comprising:
storing the new data in a staging table; and
transforming the new data in the staging table for storage in one or more target tables.
26. The system of claim 24 , the operations further comprising advancing a stream offset in the change tracking stream in response to the change tracking entry being entered into in the change tracking stream.
27. The system of claim 24 , further comprising:
removing at least one immutable micro-partition of the one or more immutable micro-partitions including database data prior to execution of the transaction.
28. The system of claim 24 , wherein the transaction comprises one or more of an insertion of data into the table, a deletion of data from the table, and an update of data in the table.
29. The system of claim 24 , wherein the change tracking entry further comprises one or more of:
a timestamp indicating when the transaction was requested;
a timestamp indicating when the transaction was fully executed;
an identifier of a user that requested the transaction;
an identifier of an account that requested the transaction; and
a minimum and maximum data value pair for data inserted by the transaction.
30. The system of claim 24 , wherein the task comprises user-defined logic comprising one or more structured query language (SQL) statements.
31. The system of claim 24 , wherein:
the change tracking stream advances after the transaction is fully and successfully executed; and
the task is executed on the new table version once.
32. The system of claim 24 , the operations further comprising, in response to executing the task on the new table version, generating a task history entry comprising one or more of:
a task name;
a task identification;
an execution timestamp indicating when the task was executed;
an execution status indicating whether the task was successfully executed or whether an error was returned;
a message comprising an error code in response to the task not being executed successfully; and
one or more results returned by executing the task.
33. The system of claim 24 , the operations further comprising retrieving the task from database schema, wherein the task comprises one or more of:
a timestamp indicating when the task was received;
a timestamp indicating when the task was generated;
a task name;
a database name indicating the database the task is to be executed;
an identifier of an owner of the task;
an identifier of a creator of the task;
a task schedule for the task;
a structured query language (SQL) script;
a last execution timestamp indicating a last time the task was executed; and
a last execution status indicating whether the task was executed successfully the last time the task was executed.
34. The system of claim 24 , the operations further comprising advancing a stream offset in the change tracking stream after a determination that the transaction fully commits.