Predictive resource allocation for distributed query execution
The subject technology receives a query directed to a set of source tables, each source table organized into a set of micro-partitions. The subject technology determines a set of metadata, the set of metadata comprising table metadata, query metadata, and historical data related to the query. The subject technology predicts, using a machine learning model, an indicator of an amount of computing resources for executing the query based at least in part on the set of metadata. The subject technology generates a query plan for executing the query based at least in part on the predicted indicator of the amount of computing resources. The subject technology executes the query based at least in part on the query plan.
1 . A system comprising:
at least one hardware processor; and
at least one memory storing instructions that cause the at least one hardware processor to perform operations comprising:
receiving a query directed to one or more source tables a set of source tables, each source table organized into a set of micro-partitions;
predicting, using a machine learning model, an indicator of an amount of computing resources for executing the query by applying query data associated with one or more characteristics of the received query and source table data associated with one or more characteristics of the one or more source tables as input to the machine learning model, the machine learning model trained to generate a prediction of the amount of computing resources for queries based on inputted query data and source table data, the amount of computing resources indicating a number of query-executing servers predicted to execute the query, the predicting of the amount of computing resources being associated with an allocation of a number of additional query-executing servers for executing the query by:
identifying a parallelization limit characteristic for the received query, the parallelization limit characteristic corresponding to (1) a number of query-executing servers and (2) a ratio between a number of additional query-executing servers and a decrease in execution time of the query responsive to the additional query-executing servers, wherein the additional query-executing servers indicate an amount of increase to the number of query-executing servers;
determining that the predicted amount of computing resources for executing the query is at or above a parallelization threshold for the parallelization limit characteristic; and
executing the query based at least in part on the predicted indicator of the amount of computing resources.
2 . The system of claim 1 , wherein the one or more source tables include a set of source tables, each source table organized into a set of micro-partitions.
3 . The system of claim 1 , the operations further comprising determining a set of metadata, the machine learning model trained to generate a prediction of the amount of computing resources for queries further based on the set of metadata.
4 . The system of claim 3 , wherein the set of metadata comprising table metadata, query metadata, and historical data related to the query.
5 . The system of claim 3 , wherein processing, using the machine learning model, at least the set of metadata comprises:
providing the set of metadata as input data to the machine learning model;
running the machine learning model to generate a value indicating the amount of computing resources for executing the query, the value corresponding to a prediction of the amount of computing resources to utilize for executing the query in an execution platform; and
providing the value indicating the amount of computing resources to a query compiler to utilize when generating a query plan.
6 . The system of claim 1 , the operations further comprising generating a query plan for executing the query based at least in part on the predicted indicator of the amount of computing resources.
7 . The system of claim 1 , wherein the operations further comprise:
analyzing the query against historical data related to the query to determine whether a previous query, being a same query as the query, has been executed at a previous time prior to receiving the query; and
in response to the query not being executed at the previous time, analyzing global history information of previous queries, the global history information comprising query execution times of the previous queries and corresponding computing resources utilized to execute the previous queries.
8 . The system of claim 7 , wherein running the machine learning model further comprises:
receiving input data at an input layer of the machine learning model;
forwarding, from the input layer, at least the received input data to a hidden layer of the machine learning model;
applying, by the hidden layer, an activation function to the received input data to generate first output data, the first output data being received by an output layer of the machine learning model; and
applying, by the output layer, a second activation function to the first output data.
9 . The system of claim 1 , wherein predicting, using the machine learning model, the indicator of the amount of computing resources for executing the query by applying a set of metadata comprising table metadata, query metadata, and historical data related to the query as input to the machine learning model further comprises:
generating a particular prediction that parallelization is not needed for executing the query, the parallelization comprising an allocation of additional virtual warehouses to a number of virtual warehouse predicted to execute the query, the prediction that parallelization is not needed being based on determining that the query executes in a particular period of time that is a same execution time as when one virtual warehouse is being utilized to execute the query.
10 . The system of claim 9 , wherein the operations further comprise:
providing second output data of a second activation function as the prediction of the amount of computing resources to utilize for executing the query in an execution platform.
11 . The system of claim 8 , wherein the operations further comprise:
retrieving global query metadata from a database, the global query metadata comprising information of queries that were previously executed; and
providing the global query metadata as second input data to the machine learning model.
12 . The system of claim 11 , wherein the operations further comprise:
determining that retrieved global query metadata includes a threshold amount of new data since a previous time that the machine learning model was trained using a previous set of global query metadata;
training the machine learning model based at least in part on the retrieved global query metadata; and
deploying the trained machine learning model as a new machine learning model to predict the indicator of the amount of computing resources for executing the query.
13 . The system of claim 1 , wherein predicting the indicator of the amount of computing resources for executing the query is further based on an expert system including a set of rules, the set of rules emulating a decision making of a human, the set of rules utilizing information stored in a knowledge base.
14 . A method comprising:
receiving a query directed to one or more source tables a set of source tables, each source table organized into a set of micro-partitions;
predicting, using a machine learning model, an indicator of an amount of computing resources for executing the query by applying query data associated with one or more characteristics of the received query and source table data associated with one or more characteristics of the one or more source tables as input to the machine learning model, the machine learning model trained to generate a prediction of the amount of computing resources for queries based on inputted query data and source table data, the amount of computing resources indicating a number of query-executing servers predicted to execute the query, the predicting of the amount of computing resources being associated with an allocation of a number of additional query-executing servers for executing the query by:
identifying a parallelization limit characteristic for the received query, the parallelization limit characteristic corresponding to (1) a number of query-executing servers and (2) a ratio between a number of additional query-executing servers and a decrease in execution time of the query responsive to the additional query-executing servers, wherein the additional query-executing servers indicate an amount of increase to the number of query-executing servers;
determining that the predicted amount of computing resources for executing the query is at or above a parallelization threshold for the parallelization limit characteristic; and
executing the query based at least in part on the predicted indicator of the amount of computing resources.
15 . The method of claim 14 , wherein the one or more source tables include a set of source tables, each source table organized into a set of micro-partitions.
16 . The method of claim 14 , the method further comprising determining a set of metadata, the machine learning model trained to generate a prediction of the amount of computing resources for queries further based on the set of metadata.
17 . The method of claim 14 , the method further comprising generating a query plan for executing the query based at least in part on the predicted indicator of the amount of computing resources.
18 . The method of claim 14 , the method further comprising:
analyzing the query against historical data related to the query to determine whether a previous query, being a same query as the query, has been executed at a previous time prior to receiving the query; and
in response to the query not being executed at the previous time, analyzing global history information of previous queries, the global history information comprising query execution times of the previous queries and corresponding computing resources utilized to execute the previous queries.
19 . The method of claim 14 , wherein predicting, using the machine learning model, the indicator of the amount of computing resources for executing the query by applying a set of metadata comprising table metadata, query metadata, and historical data related to the query as input to the machine learning model further comprises:
generating a particular prediction that parallelization is not needed for executing the query, the parallelization comprising an allocation of additional virtual warehouses to a number of virtual warehouse predicted to execute the query, the prediction that parallelization is not needed being based on determining that the query executes in a particular period of time that is a same execution time as when one virtual warehouse is being utilized to execute the query.
20 . A non-transitory computer-storage medium comprising instructions that, when executed by one or more processors of a machine, configure the machine to perform operations comprising:
receiving a query directed to one or more source tables a set of source tables, each source table organized into a set of micro-partitions;
predicting, using a machine learning model, an indicator of an amount of computing resources for executing the query by applying query data associated with one or more characteristics of the received query and source table data associated with one or more characteristics of the one or more source tables as input to the machine learning model, the machine learning model trained to generate a prediction of the amount of computing resources for queries based on inputted query data and source table data, the amount of computing resources indicating a number of query-executing servers predicted to execute the query, the predicting of the amount of computing resources being associated with an allocation of a number of additional query-executing servers for executing the query by:
identifying a parallelization limit characteristic for the received query, the parallelization limit characteristic corresponding to (1) a number of query-executing servers and (2) a ratio between a number of additional query-executing servers and a decrease in execution time of the query responsive to the additional query-executing servers, wherein the additional query-executing servers indicate an amount of increase to the number of query-executing servers;
determining that the predicted amount of computing resources for executing the query is at or above a parallelization threshold for the parallelization limit characteristic; and
executing the query based at least in part on the predicted indicator of the amount of computing resources.