Resource provisioning in database systems

Patent metadata

Field	Value
Publication number	US-11514064-B2
Application number	US-202217663248-A
Country	US
Kind code	B2
Filing date	May 13, 2022
Priority date	Apr 10, 2019
Publication date	Nov 29, 2022
Grant date	Nov 29, 2022

Abstract

Resource provisioning systems and methods are described. In an embodiment, a system includes a plurality of shared storage devices collectively storing database data, an execution platform, and a compute service manager. The compute service manager is configured to determine a task to be executed in response to a trigger event and determine a query plan for executing the task, wherein the query plan comprises a plurality of discrete subtasks. The compute service manager is further configured to assign the plurality of discrete subtasks to one or more nodes of a plurality of nodes of the execution platform, determine whether execution of the task is complete, and in response to determining the execution of the task is complete, store a record in the plurality of shared storage devices indicating the task was completed.

First claim

What is claimed is:

1. A method for managing and executing database tasks, the method comprising:
determining a set of actions to be executed on a database table in response to a trigger event;
determining a query plan for executing the set of actions, the query plan comprising a plurality of statements to be executed;
determining whether the plurality of statements of the query plan associated with the set of actions are to be performed on a row of the database table; and
in response to determining whether the plurality of statements are to be performed on the row of the database table, assigning each of the plurality of statements to be performed on the row of the database table to one or more nodes of a plurality of nodes of an execution platform.

2. The method of claim 1 , further comprising:
determining whether execution of the set of actions is complete; and
storing a record of each completed action of the set of actions.

3. The method of claim 1 , wherein the trigger event comprises at least one of an insert, update, or delete operation.

4. The method of claim 1 , further comprising retrieving the set of actions from a queue, wherein the queue is stored across one or more of a plurality of shared storage devices that are separate from the execution platform.

5. The method of claim 4 , further comprising scaling a number of available execution nodes in the execution platform up or down based on a number of sets of actions waiting in the queue.

6. The method of claim 1 , further comprising deserializing the plurality of statements to resolve a context for the plurality of statements, wherein the context comprises at least one of an account identification, a role, or a user for executing the task.

7. The method of claim 1 , wherein determining the query plan for executing the set of actions comprises generating a Structured Query Language (SQL) job, wherein the plurality of statements must be processed to complete the SQL job.

8. The method of claim 7 , further comprising dividing the processing of the plurality of statements based on metadata, wherein the metadata pertains to database data that is stored across a plurality of shared storage devices, and wherein the metadata is stored separately from the database data.

9. The method of claim 1 , further comprising:
predicting a time required to execute a given statement of the plurality of statements;
determining a current processing delay associated with the one or more nodes based on the predicted time; and
in response to determining that the current processing delay exceeds a threshold value, creating a new node for processing the given statement.

10. The method of claim 1 , wherein assigning each of the plurality of statements to one or more nodes further comprises:
assigning a first statement of the plurality of statements to a first node, the assigning the first statement to the first node comprises determining that the first node includes cached data associated with the first statement; and
assigning a second statement of the plurality of statements to a second node, the second node being distinct from the first node.

11. The method of claim 1 , further comprising prioritizing user queries over internal jobs of the execution platform.

12. The method of claim 1 , further comprising:
determining a future workload of the plurality of nodes; and
scheduling each of the plurality of statements based on the future workload of the plurality of nodes.

13. A system comprising:
a plurality of storage devices collectively storing database data;
a service manager comprising a software program stored in memory and executed by one or more processors to perform operations comprising:
determining a set of actions to be executed on a database table in response to a trigger event;
determining a query plan for executing the set of actions, the query plan comprising a plurality of statements to be executed;
determining whether the plurality of statements of the query plan associated with the set of actions are to be performed on a row of the database table; and
in response to determining whether the plurality of statements are to be performed on the row of the database table, assigning each of the plurality of statements to be performed on the row of the database table to one or more nodes of a plurality of nodes of an execution platform.

14. The system of claim 13 , wherein the operations further comprise:
determining whether execution of the set of actions is complete; and
storing a record of each completed action of the set of actions.

15. The system of claim 13 , wherein the operations further comprise:
determining whether the one or more nodes of the plurality of nodes of the execution platform has finished executing the query plan.

16. The system of claim 13 , wherein the operations further comprise scaling a number of available execution nodes in the execution platform up or down based on a number of sets of tasks within a queue.

17. The system of claim 13 , wherein the operations further comprise:
deserializing the plurality of statements to resolve a context for the set of actions, wherein the context comprises at least one of an account identification, a role, or a user for executing the set of actions; and
dividing processing of the plurality of statements based on metadata, wherein the metadata pertains to the database data and is stored separately from the database data.

18. The system of claim 13 , wherein the operations further comprise retrieving the set of actions from a queue, wherein the queue is stored across one or more of a plurality of shared storage devices that are separate from the execution platform.

19. The system of claim 13 , wherein the trigger event comprises at least one of an insert, update, or delete operation.

20. The system of claim 13 , wherein determining the query plan for executing the set of actions comprises generating a Structured Query Language (SQL) job, wherein the plurality of statements must be processed to complete the SQL job.

21. The system of claim 13 , wherein the operations further comprise:
predicting a time required to execute a given statement of the plurality of statements;
determining a current processing delay associated with the one or more nodes based on the predicted time; and
in response to determining that the current processing delay exceeds a threshold value, creating a new node for processing the given statement.

22. A non-transitory computer-readable medium comprising instructions, which when executed by at least one processor, cause the at least one processor to perform operations comprising:
determining a set of actions to be executed on a database table in response to a trigger event;
determining a query plan for executing the set of actions, the query plan comprising a plurality of statements to be executed;
determining whether the plurality of statements of the query plan associated with the set of actions are to be performed on a row of the database table; and
in response to determining whether the plurality of statements are to be performed on the row of the database table, assigning each of the plurality of statements to be performed on the row of the database table to one or more nodes of a plurality of nodes of an execution platform.

23. The non-transitory computer-readable medium of claim 22 , the operations further comprises:
determining whether execution of the set of actions is complete;

Assignees

• Snowflake Inc

Inventors

• Rosen Jeffrey
• Munir Abdul
• Yan Jiaqi
• Waddington William
• Rajaperumal Prasanna
• Cruanes Thierry

Classifications

• G06F2209/5017

  Task decomposition · CPC title

• G06F9/4843 Primary

  by program, e.g. task dispatcher, supervisor, operating system · CPC title

• G06F9/5038

  considering the execution order of a plurality of tasks, e.g. taking priority or time dependency constraints into consideration (scheduling strategies G06F9/4881 and subgroups) · CPC title

• G06F16/24542

  Plan optimisation · CPC title

• G06F9/5077

  Logical partitioning of resources; Management or configuration of virtualized resources (specific details on emulation or internal functioning of virtual machines G06F9/455) · CPC title