Task scheduling using a stream of committed transactions

Patent metadata

Field	Value
Publication number	US-12153569-B2
Application number	US-202217653491-A
Country	US
Kind code	B2
Filing date	Mar 4, 2022
Priority date	Nov 28, 2018
Publication date	Nov 26, 2024
Grant date	Nov 26, 2024

Abstract

A method includes generating a task using a plurality of logical statements embedded in a database, the plurality of logical statements corresponding to a data modification. Database data is ingested into a staging table that is configured within the database. The task is executed based on applying the data modification to a first set of partitions storing the database data and generating a second set of partitions. The second set of partitions store modified data corresponding to the database data. A stream of committed transactions is advanced at least in part by adding an entry into the stream. The entry corresponds to committed transactions performed on the first set of partitions during the data modification. A data processing task is scheduled for execution on the modified data based on the advancing of the stream offset.

First claim

What is claimed is:

1. A method comprising:
generating a task using a plurality of logical statements embedded in a database, the plurality of logical statements corresponding to a data modification;
ingesting database data into a staging table, the staging table configured within the database;
executing the task based on applying the data modification to a first set of partitions storing the database data in the staging table and generating a second set of partitions in the staging table by removing at least one of the first set of partitions based on the data modification, the second set of partitions storing modified data corresponding to the database data and at least one partition storing unmodified data, the at least one partition storing unmodified data being common between the first set and the second set;
advancing a stream of committed transactions at least in part by adding an entry into the stream, the entry corresponding to committed transactions performed on the first set of partitions during the data modification resulting in the generating of the second set of partitions; and
scheduling a data processing task to be executed on the modified data based on the advancing of the stream of committed transactions.

2. The method of claim 1 , further comprising:
detecting a trigger event associated with the database; and
executing the task in response to the trigger event.

3. The method of claim 2 , wherein the trigger event comprises execution of an insert command, a delete command, or an update command on the database.

4. The method of claim 2 , wherein detecting the trigger event comprises:
detecting advancing of the stream of committed transactions based on adding a second entry into the stream, the second entry corresponding to at least another committed transaction performed on the first set of partitions before the data modification.

5. The method of claim 1 , further comprising:
generating a task history, the task history indicating at least one characteristic of the committed transactions performed on the first set of partitions during the data modification; and
outputting a report with the task history, based on completion of the data processing task.

6. The method of claim 1 , wherein the task further comprises one or more of:
a timestamp indicating when the task was received;
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.

7. The method of claim 1 , further comprising:
retrieving a task definition of the task, the task definition comprising a task schedule; and
applying the data modification to the first set of partitions storing the database data based on the task schedule.

8. The method of claim 7 , wherein the task schedule includes a WAIT condition or a SCHEDULE property.

9. The method of claim 7 , wherein the task schedule includes a WAIT condition and the method further comprising:
applying the data modification to the first set of partitions storing the database data after the WAIT condition is satisfied.

10. 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 operations comprising:
generating a task using a plurality of logical statements embedded in a database, the plurality of logical statements corresponding to a data modification;
ingesting database data into a staging table, the staging table configured within the database;
executing the task based on applying the data modification to a first set of partitions storing the database data in the staging table and generating a second set of partitions in the staging table by removing at least one of the first set of partitions based on the data modification, the second set of partitions storing modified data corresponding to the database data and at least one partition storing unmodified data, the at least one partition storing unmodified data being common between the first set and the second set;
advancing a stream of committed transactions at least in part by adding an entry into the stream, the entry corresponding to committed transactions performed on the first set of partitions during the data modification resulting in the generating of the second set of partitions; and
scheduling a data processing task to be executed on the modified data based on the advancing of the stream of committed transactions.

11. The system of claim 10 , the operations further comprising:
detecting a trigger event associated with the database; and
executing the task in response to the trigger event.

12. The system of claim 11 , wherein the trigger event comprises execution of an insert command, a delete command, or an update command on the database.

13. The system of claim 11 , wherein the operations for detecting the trigger event further comprise:
detecting advancing of the stream of committed transactions based on adding a second entry into the stream, the second entry corresponding to at least another committed transaction performed on the first set of partitions before the data modification.

14. The system of claim 10 , the operations further comprising:
generating a task history, the task history indicating at least one characteristic of the committed transactions performed on the first set of partitions during the data modification; and
outputting a report with the task history, based on completion of the data processing task.

15. The system of claim 10 , wherein the task further comprises one or more of:
a timestamp indicating when the task was received;
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.

16. The system of claim 10 , the operations further comprising:
retrieving a task definition of the task, the task definition comprising a task schedule; and
applying the data modification to the first set of partitions storing the database data based on the task schedule.

17. The system of claim 16 , wherein the task schedule includes a WAIT condition or a SCHEDULE property.

18. The system of claim 16 , wherein the task schedule includes a WAIT condition and the operations further comprise:
applying the data modification to the first set of partitions storing the database data after the WAIT condition is satisfied.

19. A non-transitory computer-readable storage medium storing instructions that, when executed by one or more processors, cause the one or more processors to perform operations comprising:
generating a task using a plurality of logical statements embedded in a database, the plurality of logical statements corresponding to a data modification;
ingesting database data into a staging table, the staging table configured within the database;
executing the task based on applying the data modification to a first set of partitions storing the database data in the staging table and generating a second set of partitions in the staging table by removing at least one of the first set of partitions based on the data modification, the second set of partitions storing modified data corresponding to the database data and at least one partition storing unmodified data, the at least one partition storing unmodified data being common between the first set and the second

Assignees

• Snowflake Inc

Inventors

• Cseri Istvan
• Grabs Torsten
• Dageville Benoit

Classifications

• G06F16/2308

  Concurrency control (transaction processing G06F9/466) · CPC title

• G06F9/466

  Transaction processing · CPC title

• G06F16/256

  in federated or virtual databases · CPC title

• G06F16/2379 Primary

  Updates performed during online database operations; commit processing · CPC title

• G06F9/4881 Primary

  Scheduling strategies for dispatcher, e.g. round robin, multi-level priority queues · CPC title