Optimization of virtual warehouse computing resource allocation
Methods, systems, and apparatuses for optimizing the configuration of virtual warehouses for execution of queries on one or more data warehouses are described herein. A plurality of different events associated with a data sharing platform may be logged. The data sharing platform may enable users to access one or more databases managed by the data sharing platform. The data sharing platform may be configured to provide access to the data stored by the data sharing platform via one or more of a plurality of virtual warehouses. A testing database may be generated. An optimized virtual warehouse configuration may be predicted for a first virtual warehouse by selecting a plurality of different warehouse configurations for the first virtual warehouse, measuring performance parameters of each of the plurality of different warehouse configurations by emulating, and selecting the optimized virtual warehouse configuration based on the performance parameters.
1 . A computing device comprising:
one or more processors; and
memory storing instructions that, when executed by the one or more processors, cause the computing device to:
log a plurality of different queries executed by a data sharing platform, wherein the data sharing platform enables users to access one or more databases managed by the data sharing platform, wherein the data sharing platform is configured to provide access to the data stored by the data sharing platform via one or more of a plurality of virtual warehouses, wherein each of the plurality of virtual warehouses comprises a respective set of computing resources configured to:
execute one or more queries with respect to at least a portion of a plurality of data warehouses;
collect results from the one or more queries; and
provide access to the collected results;
generate a testing database by duplicating at least one of the one or more databases;
predict an optimized virtual warehouse configuration for a first virtual warehouse by:
selecting a plurality of different warehouse configurations for the first virtual warehouse, wherein the plurality of different warehouse configurations each correspond to a different set of computing resources available to the first virtual warehouse;
emulating, via the first virtual warehouse, the plurality of different queries at the testing database by, for each query of the plurality of different queries, for each warehouse configuration of the plurality of different warehouse configurations, and at different times, causing a virtual warehouse configured based on the warehouse configuration to execute the query, collect results based on the query, and provide access to the results based on the query;
monitoring the emulating of the plurality of different queries to measure performance parameters of each of the plurality of different warehouse configurations; and
selecting the optimized virtual warehouse configuration based on the performance parameters; and
output the optimized virtual warehouse configuration.
2 . The computing device of claim 1 , wherein the instructions, when executed by the one or more processors, cause the computing device to emulate the plurality of different queries at the testing database by causing the computing device to:
cause each of the plurality of different queries to be executed by the virtual warehouse configured based on the warehouse configuration at a different time.
3 . The computing device of claim 1 , wherein the plurality of different queries comprise one or more of:
a write action to a database of the one or more databases; or
a read action to the database of the one or more databases.
4 . The computing device of claim 1 , wherein the instructions, when executed by the one or more processors, cause the computing device to log the plurality of different queries during a time period.
5 . The computing device of claim 1 , wherein the instructions, when executed by the one or more processors, cause the computing device to log the plurality of different queries by causing the computing device to log:
at least one first query that satisfies a processing time threshold; and
at least one second query that does not satisfy the processing time threshold.
6 . The computing device of claim 1 , wherein the instructions, when executed by the one or more processors, cause the computing device to predict the optimized virtual warehouse configuration by causing the computing device to:
train, using training data, a machine learning model to output a recommended virtual warehouse configuration, wherein the training data comprises a history of different virtual warehouse configurations and a history of different query processing times;
provide, to the trained machine learning model, input comprising the performance parameters; and
receive, from the trained machine learning model, output indicating the optimized virtual warehouse configuration.
7 . The computing device of claim 1 , wherein the performance parameters indicate a processing time corresponding to each of the plurality of different queries.
8 . A method comprising:
logging, by a computing device, a plurality of different queries executed by a data sharing platform, wherein the data sharing platform enables users to access one or more databases managed by the data sharing platform, wherein the data sharing platform is configured to provide access to the data stored by the data sharing platform via one or more of a plurality of virtual warehouses, wherein each of the plurality of virtual warehouses comprises a respective set of computing resources configured to:
execute one or more queries with respect to at least a portion of a plurality of data warehouses;
collect results from the one or more queries; and
provide access to the collected results;
generating a testing database by duplicating at least one of the one or more databases;
predicting an optimized virtual warehouse configuration for a first virtual warehouse by:
selecting a plurality of different warehouse configurations for the first virtual warehouse, wherein the plurality of different warehouse configurations each correspond to a different set of computing resources available to the first virtual warehouse;
emulating, via the first virtual warehouse, the plurality of different queries at the testing database by, for each query of the plurality of different queries, for each warehouse configuration of the plurality of different warehouse configurations, and at different times, causing a virtual warehouse configured based on the warehouse configuration to execute the query, collect results based on the query, and provide access to the results based on the query;
monitoring the emulating of the plurality of different queries to measure performance parameters of each of the plurality of different warehouse configurations; and
selecting the optimized virtual warehouse configuration based on the performance parameters; and
outputting the optimized virtual warehouse configuration.
9 . The method of claim 8 , wherein emulating the plurality of different queries at the testing database comprises:
causing each of the plurality of different queries to be executed by the virtual warehouse configured based on the warehouse configuration at a different time.
10 . The method of claim 8 , wherein the plurality of different queries comprise one or more of:
a write action to a database of the one or more databases; or
a read action to the database of the one or more databases.
11 . The method of claim 8 , wherein logging the plurality of different queries comprises logging the plurality of different events during a time period.
12 . The method of claim 8 , wherein logging the plurality of different queries comprises logging:
at least one first query that satisfies a processing time threshold; and
at least one second query that does not satisfy the processing time threshold.
13 . The method of claim 8 , wherein predicting the optimized virtual warehouse configuration comprises:
training, using training data, a machine learning model to output a recommended virtual warehouse configuration, wherein the training data comprises a history of different virtual warehouse configurations and a history of different query processing times;
providing, to the trained machine learning model, input comprising the performance parameters; and
receiving, from the trained machine learning model, output indicating the optimized virtual warehouse configuration.
14 . The method of claim 8 , wherein the performance parameters indicate a processing time corresponding to each of the plurality of different queries.
15 . One or more non-transitory computer-readable media storing instructions that, when executed by one or more processors of a computing device, cause the computing device to:
log a plurality of different queries executed by a data sharing platform, wherein the data sharing platform enables users to access one or more databases managed by the data sharing platform, wherein the data sharing platform is configured to provide access to the data stored by the data sharing platform via one or more of a plurality of virtual warehouses, wherein each of the plurality of virtual warehouses comprises a respective set of computing resources configured to:
execute one or more queries with respect to at least a portion of a plurality of data warehouses;
collect results from the one or more queries; and
provide access to the collected results;
generate a testing database by duplicating at least one of the one or more databases;
predict an optimized virtual warehouse configuration for a first virtual warehouse by:
selecting a plurality of different warehouse configurations for the first virtual warehouse, wherein the plurality of different warehouse configurations each correspond to a different set of computing resources available to the first virtual warehouse;
emulating, via the first virtual warehouse, the plurality of different queries at the testing database by, for each query of the plurality of different queries, for each warehouse configuration of the plurality of different warehouse configurations, and at different times, causing a virtual warehouse configured based on the warehouse configuration to execute the query, collect results based on the query, and provide access to the results based on the query;
monitoring the emulating of the plurality of different queries to measure performance parameters of each of the plurality of different warehouse configurations; and
selecting the optimized virtual warehouse configuration based on the performance parameters; and
output the optimized virtual warehouse configuration.
16 . The non-transitory computer-readable media of claim 15 , wherein the instructions, when executed by the one or more processors, cause the computing device to emulate the plurality of different queries at the testing database by causing the computing device to:
cause each of the plurality of different queries to be executed by the virtual warehouse configured based on the warehouse configuration at a different time.
17 . The non-transitory computer-readable media of claim 15 , wherein the plurality of different queries comprise one or more of:
a write action to a database of the one or more databases; or
a read action to the database of the one or more databases.
18 . The non-transitory computer-readable media of claim 15 , wherein the instructions, when executed by the one or more processors, cause the computing device to log the plurality of different queries during a time period.
19 . The non-transitory computer-readable media of claim 15 , wherein the instructions, when executed by the one or more processors, cause the computing device to log the plurality of different queries by causing the computing device to log:
at least one first query that satisfies a processing time threshold; and
at least one second query that does not satisfy the processing time threshold.
20 . The non-transitory computer-readable media of claim 15 , wherein the instructions, when executed by the one or more processors, cause the computing device to predict the optimized virtual warehouse configuration by causing the computing device to:
train, using training data, a machine learning model to output a recommended virtual warehouse configuration, wherein the training data comprises a history of different virtual warehouse configurations and a history of different query processing times;
provide, to the trained machine learning model, input comprising the performance parameters; and
receive, from the trained machine learning model, output indicating the optimized virtual warehouse configuration.