Patent ID: 12236278

DETAILED DESCRIPTION

In the following description of the various embodiments, reference is made to the accompanying drawings, which form a part hereof, and in which is shown by way of illustration various embodiments in which aspects of the disclosure may be practiced. It is to be understood that other embodiments may be utilized and structural and functional modifications may be made without departing from the scope of the present disclosure. Aspects of the disclosure are capable of other embodiments and of being practiced or being carried out in various ways. In addition, it is to be understood that the phraseology and terminology used herein are for the purpose of description and should not be regarded as limiting. Rather, the phrases and terms used herein are to be given their broadest interpretation and meaning.

By way of introduction, aspects discussed herein may relate to methods and techniques for management of virtual warehouses which execute queries with respect to one or more data warehouses. A virtual warehouse may comprise one or more computing devices which are configured to perform tasks associated with one or more queries, such as executing the one or more queries with respect to one or more data warehouses, collecting results from those one or more queries (e.g., from the one or more data warehouses), and/or providing those collected results to one or more user devices. For example, three virtual warehouses may be instantiated on a single computing device (e.g., a server), a plurality of computing devices (e.g., a distributed network of servers), or the like. The availability of and/or use of a virtual warehouse may be associated with cost. For example, an organization may be charged based on a time in which a virtual warehouse is used, the size of a query, the amount of memory used by a query, or the like. Accordingly, virtual warehouses may be limited in their size (that is, the amount of computing resources available to them) to save money and to preserve computing resources. For example, for simple queries, a virtual warehouse may be instantiated with a relatively small quantity of computing resources (e.g., processor speed, memory) so as to lower the cost of maintaining and using that virtual warehouse. Moreover, multiple virtual warehouses may be available to an organization. For example, an organization may maintain a large virtual warehouse for significant and business-critical queries, whereas it may maintain a plurality of smaller virtual warehouses for more routine and less time-sensitive queries.

Methods, systems, apparatuses, and computer-readable media as described herein may relate to the analysis of how computing resources may be efficiently allocated to virtual warehouses in view of real-life events occurring with preexisting virtual warehouses. A log of events associated with virtual warehouses (e.g., queries provided to those virtual warehouses, sorting and/or viewing activities with respect to those virtual warehouses) may be retrieved. A duplicate copy of one or more databases may be made. Then, the events may be emulated with respect to the duplicate copy of the one or more databases using a virtual warehouse, such that various configurations of that virtual warehouse may be tested. During such testing, performance parameters may be collected. Then, an optimized virtual warehouse configuration may be selected based on the performance parameters. In this manner, configurations for a virtual warehouse may be tested using real-world queries and real-world data, providing realistic performance parameters that help accurately estimate the impact of a particular virtual warehouse configuration.

The present disclosure is significantly different than conventional optimization processes at least in that it operates in view of the particularities and unique needs of virtual warehouses. The present disclosure is far more than a mere instruction to decide the optimal size of a data warehouse: rather, the present disclosure processes and emulates a large quantity of real-world events against a duplicative database to generate test data that is, by virtue of the manner in which it is emulated, unique to the circumstance where such events are implemented through virtual warehouses. This complexity is one reason why many users of virtual warehouse as a service platforms, such as the Snowflake platform, may inadvertently overspend for excessive computing resource allocations: because they do not have the tools for accurately testing the effectiveness of a particular virtual warehouse configuration, and because the particularities of virtual warehouse as a server platforms (e.g., the Snowflake platform) introduce complexities which effectively moot conventional database sizing considerations, they have no insight into the ramifications of changing such a configuration.

The present disclosure also improves the functioning of computers by improving the manner in which queries are executed with respect to one or more data warehouses. Virtual warehouses provide an improvement to conventional query systems, but their misconfiguration and misuse can result in the waste of computing resources. As such, improvements to the manner in which queries are received by virtual warehouses may make those virtual warehouses more efficient. For example, by properly sizing a virtual warehouse based on testing various virtual warehouse configurations using real-world data and past events that have occurred with respect to that real-world data, the virtual warehouse can be configured in a manner which, e.g., does not unnecessarily waste computing resources and which does not cause queries to take an undesirably long time to execute.

The present disclosure is also fundamentally rooted in computing devices and, in particular, an environment with virtual warehouses. Presently, virtual warehouse as a service platform architectures (e.g., Snowflake's architecture) are unique in that they allow for different configurations for different compute environments (e.g., different virtual warehouses). In contrast, other database systems rely on monolithic systems to handle all enterprise needs. It is precisely this flexibility of these virtual warehouse as a service platforms that is addressed by the improvements discussed herein.

FIG.1shows a system100. The system100may include one or more computing devices110, one or more data warehouses120, and/or one or more virtual warehouse servers130in communication via a network140. It will be appreciated that the network connections shown are illustrative and any means of establishing a communications link between the computers may be used. The existence of any of various network protocols such as TCP/IP, Ethernet, FTP, HTTP and the like, and of various wireless communication technologies such as GSM, CDMA, WiFi, and LTE, is presumed, and the various computing devices described herein may be configured to communicate using any of these network protocols or technologies. Any of the devices and systems described herein may be implemented, in whole or in part, using one or more computing systems described with respect toFIG.2.

The computing devices110may, for example, provide queries to the virtual warehouse servers130and/or receive query results from the virtual warehouse servers130, as described herein. The data warehouses120may store data and provide, in response to queries, all or portions of the stored data, as described herein. The data warehouses120may include, but are not limited to relational databases, hierarchical databases, distributed databases, in-memory databases, flat file databases, XML databases, NoSQL databases, graph databases, and/or a combination thereof. The virtual warehouse servers130may execute, manage, resize, and otherwise control one or more virtual warehouses, as described herein. Thus, for example, one or more of the computing devices110may send a request to execute a query to one or more of the virtual warehouse servers130, and one or more virtual warehouses of the virtual warehouse servers130may perform steps which effectuate that query with respect to one or more of the data warehouses120. The network140may include a local area network (LAN), a wide area network (WAN), a wireless telecommunications network, and/or any other communication network or combination thereof.

The virtual warehouse servers130and/or the data warehouses120may be all or portions of a cloud system. In this manner, the computing devices110may be located in a first location (e.g., the offices of a corporation), and the virtual warehouse servers130and/or the data warehouses120may be located in a variety of locations (e.g., distributed in a redundant manner across the globe). This may protect business resources: for example, if the Internet goes down in a first location, the distribution and redundancy of various devices may allow a business to continue operating despite the outage.

The virtual warehouse servers130may be all or portions of a virtual warehouse as a service system. One example of such a virtual warehouse as a service system is the Snowflake architecture. With that said, any type of virtual warehouse as a service system may be implemented using the present disclosure. For example, the computing devices110and/or the data warehouses120may be managed by an organization. In contrast, the virtual warehouse servers130may be managed by a different entity, such as Snowflake Inc. In this manner, a third party (e.g., Snowflake) may provide, as a service, virtual warehouses which may operate on behalf of organization-managed computing devices (e.g., the computing device110) to perform queries with respect to organization-managed data warehouses (e.g., the data warehouses120).

As used herein, a data warehouse, such as any one of the data warehouses120, may be one or more databases or other devices which store data. For example, a data warehouse may be a single database, a collection of databases, or the like. A data warehouse may be structured and/or unstructured, such that, for example, a data warehouse may comprise a data lake. A data warehouse may store data in a variety of formats and in a variety of manners. For example, a data warehouse may comprise textual data in a table, image data as stored in various file system folders, and the like.

The data transferred to and from various computing devices in a system100may include secure and sensitive data, such as confidential documents, customer personally identifiable information, and account data. Therefore, it may be desirable to protect transmissions of such data using secure network protocols and encryption, and/or to protect the integrity of the data when stored on the various computing devices. For example, a file-based integration scheme or a service-based integration scheme may be utilized for transmitting data between the various computing devices. Data may be transmitted using various network communication protocols. Secure data transmission protocols and/or encryption may be used in file transfers to protect the integrity of the data, for example, File Transfer Protocol (FTP), Secure File Transfer Protocol (SFTP), and/or Pretty Good Privacy (PGP) encryption. In many embodiments, one or more web services may be implemented within the various computing devices. Web services may be accessed by authorized external devices and users to support input, extraction, and manipulation of data between the various computing devices in the system100. Web services built to support a personalized display system may be cross-domain and/or cross-platform, and may be built for enterprise use. Data may be transmitted using the Secure Sockets Layer (SSL) or Transport Layer Security (TLS) protocol to provide secure connections between the computing devices. Web services may be implemented using the WS-Security standard, providing for secure SOAP messages using XML encryption. Specialized hardware may be used to provide secure web services. For example, secure network appliances may include built-in features such as hardware-accelerated SSL and HTTPS, WS-Security, and/or firewalls. Such specialized hardware may be installed and configured in the system100in front of one or more computing devices such that any external devices may communicate directly with the specialized hardware.

Turning now toFIG.2, a computing device200that may be used with one or more of the computational systems is described. The computing device200may be the same or similar as any one of the computing devices110, the virtual warehouse servers130, and/or the data warehouses120ofFIG.1. The computing device200may include a processor203for controlling overall operation of the computing device200and its associated components, including RAM205, ROM207, input/output device209, communication interface211, and/or memory215. A data bus may interconnect processor(s)203, RAM205, ROM207, memory215, I/O device209, and/or communication interface211. In some embodiments, computing device200may represent, be incorporated in, and/or include various devices such as a desktop computer, a computer server, a mobile device, such as a laptop computer, a tablet computer, a smart phone, any other types of mobile computing devices, and the like, and/or any other type of data processing device.

Input/output (I/O) device209may include a microphone, keypad, touch screen, and/or stylus through which a user of the computing device200may provide input, and may also include one or more of a speaker for providing audio output and a video display device for providing textual, audiovisual, and/or graphical output. Software may be stored within memory215to provide instructions to processor203allowing computing device200to perform various actions. For example, memory215may store software used by the computing device200, such as an operating system217, application programs219, and/or an associated internal database221. The various hardware memory units in memory215may include volatile and nonvolatile, removable and non-removable media implemented in any method or technology for storage of information such as computer-readable instructions, data structures, program modules, or other data. Memory215may include one or more physical persistent memory devices and/or one or more non-persistent memory devices. Memory215may include, but is not limited to, random access memory (RAM)205, read only memory (ROM)207, electronically erasable programmable read only memory (EEPROM), flash memory or other memory technology, optical disk storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other medium that may be used to store the desired information and that may be accessed by processor203.

Communication interface211may include one or more transceivers, digital signal processors, and/or additional circuitry and software for communicating via any network, wired or wireless, using any protocol as described herein.

Processor203may include a single central processing unit (CPU), which may be a single-core or multi-core processor, or may include multiple CPUs. Processor(s)203and associated components may allow the computing device200to execute a series of computer-readable instructions to perform some or all of the processes described herein. Although not shown inFIG.2, various elements within memory215or other components in computing device200, may include one or more caches, for example, CPU caches used by the processor203, page caches used by the operating system217, disk caches of a hard drive, and/or database caches used to cache content from database221. For embodiments including a CPU cache, the CPU cache may be used by one or more processors203to reduce memory latency and access time. A processor203may retrieve data from or write data to the CPU cache rather than reading/writing to memory215, which may improve the speed of these operations. In some examples, a database cache may be created in which certain data from a database221is cached in a separate smaller database in a memory separate from the database, such as in RAM205or on a separate computing device. For instance, in a multi-tiered application, a database cache on an application server may reduce data retrieval and data manipulation time by not needing to communicate over a network with a back-end database server. These types of caches and others may be included in various embodiments, and may provide potential advantages in certain implementations of devices, systems, and methods described herein, such as faster response times and less dependence on network conditions when transmitting and receiving data.

Although various components of computing device200are described separately, functionality of the various components may be combined and/or performed by a single component and/or multiple computing devices in communication without departing from the invention.

Discussion will now turn to an example of how the computing devices ofFIG.1, such as the computing devices110, the virtual warehouse servers130, and the databases120, may operate to fulfill a query by selecting one or more of a plurality of virtual warehouses.

FIG.3shows a system comprising the computing devices110, the virtual warehouse servers130, and the data warehouses120ofFIG.1.FIG.3may depict all or portions of a system configured according to the Snowflake architecture or a similar architecture permitting use of one or more virtual warehouses.FIG.3also depicts various elements which may be portions of those computing devices, as well as transmissions between those devices. In particular, the computing devices110are shown having a request application301, the virtual warehouse servers130are shown having a virtual warehouse manager application302and three virtual warehouses (a virtual warehouse A303a, a virtual warehouse B303b, and a virtual warehouse C303c), and the data warehouses120are shown comprising a data warehouse A304aand a data warehouse B304b. All or portions of these devices may be part of the Snowflake architecture or another architecture. For example, the computing devices110may be users' personal computing devices, whereas the virtual warehouse servers130may be cloud servers managed by a virtual warehouse as a service platform organization, such as Snowflake Inc., of San Mateo, CA.

As part of step305a, the request application301may transmit, to the virtual warehouse manager application302, a request for a query. The transmitted request may be in a variety of formats which indicate a request for a query to be executed. For example, the request may comprise a structured query which may be directly executed on one or more of the data warehouses120(such as an SQL query), and/or may comprise a vaguer request for data (e.g., a natural language query, such as a request for “all data in the last month”).

The request application301may be any type of application which may transmit a request to the virtual warehouse manager application302, such as a web browser (e.g., showing a web page associated with the virtual warehouse manager application302), a special-purpose query application (e.g., as part of a secure banking application, such as may execute on a tablet or smartphone), an e-mail application (e.g., such that the request to the virtual warehouse manager application302may be transmitted via e-mail), or the like. As such, the request may be input by a user in a user interface of the request application301and using, for example, a keyboard, a mouse, voice commands, a touchscreen, or the like.

As part of step305b, the virtual warehouse manager application302may select one of a plurality of available virtual warehouses (in this case, the virtual warehouse C303C) to execute the query. As part of this process, the virtual warehouse manager application may determine which of a plurality of virtual warehouses should address the request received in step305. The virtual warehouse manager application302may identify an execution plan for the query by determining one or more sub-queries to be executed with respect to one or more of the data warehouses120. For example, the request may comprise querying both the data warehouse A304aand the data warehouse B304bfor different portions of data. The virtual warehouse manager application302may, based on the query and the execution plan, predict a processing complexity of the query. The processing complexity of the query may correspond to a time to complete the query (e.g., the time required to perform all steps of the execution plan), a quantity of computing resources (e.g., processor time, memory) required to execute the query, or the like. The virtual warehouse manager application302may additionally and/or alternatively determine an operating status of the plurality of virtual warehouses and/or processing capabilities of the plurality of virtual warehouses. For example, the virtual warehouse A303ais shown as being large (e.g., having relatively significant processing capabilities) but having a utilization of 99% (that is, being quite busy), the virtual warehouse B303bis shown as being large and having a utilization of 5% (that is, being quite free), and the virtual warehouse C303cis shown as being small and having a utilization of 5%. Based on the processing complexity, the operating status of the plurality of virtual warehouses, and/or the processing capabilities of the plurality of virtual warehouses, a subset of the plurality of virtual warehouses may be selected. For example, that subset may comprise both the virtual warehouse B303band the virtual warehouse C303c, at least because both have a low utilization rate and thus may be capable of handling the request received from the request application301. From that subset, one or more virtual warehouses may be selected to execute the query. For example, as shown in the example provided inFIG.3, the virtual warehouse C303chas been selected to address the query. This may be because, for example, the query may be small (that is, the execution plan may be simple or otherwise quick to handle), such that executing the query on the virtual warehouse C303cmay be cheaper and may free up the virtual warehouse B303bfor handling larger, more complex queries.

Virtual warehouses, such as the virtual warehouse A303a, the virtual warehouse B303b, and/or the virtual warehouse C303c, may comprise a respective set of computing resources. For example, each virtual warehouse may execute on one or a plurality of servers (e.g., the virtual warehouse servers130), and each virtual warehouse may be apportioned a particular quantity of computing resources (e.g., computing processor speed, memory, storage space, bandwidth, or the like). Broadly, such quantities of computing resources may be referred to via “t-shirt sizes,” such that one virtual warehouse may be referred to as “large,” whereas another may be referred to as “small.” Virtual warehouses may be resized such that, for example, the virtual warehouse A303a(which is large) may be shrunk down to a smaller size to save money and/or to allocate resources to another virtual warehouse. Virtual warehouses may also have different utilization rates. For example, a virtual warehouse using substantially all of its resources to execute a query may be said to be fully occupied (that is, to have a utilization rate of approximately 100%), whereas a virtual warehouse not performing any tasks may be said to be free (that is, to have a utilization rate of approximately 0%). The size of the virtual warehouses may affect the utilization rate: for example, a larger virtual warehouse may be capable of handling more queries at the same time as compared to a relatively smaller virtual warehouse. Moreover, as indicated by the various steps described with respect toFIG.3, virtual warehouses may be configured to execute one or more queries with respect to at least a portion of the data warehouses120, collect results from the one or more queries, and provide, to one or more computing devices, access to the collected results. As such, the size and/or utilization of a particular virtual warehouse may impact its ability to execute queries, collect results, and provide those results.

Virtual warehouses, such as the virtual warehouse A303a, the virtual warehouse B303b, and/or the virtual warehouse C303c, may be resized based on a schedule. For example, a single virtual warehouse (e.g., the virtual warehouse A303a) may be resized based on a schedule specific to that virtual warehouse (and/or a group of virtual warehouses) such that it is larger during business hours (e.g., 9:00 AM to 5:00 PM) as compared to other hours. Such a schedule may be defined by an administrator, may be based on a use pattern specific to the virtual warehouse, and/or may be based on a pattern of activity, by one or more users, corresponding to one or more different virtual warehouses. For example, the virtual warehouse manager application302may monitor use of virtual warehouses and determine that, during business hours, the virtual warehouses are used more frequently. Based on such a determination, the virtual warehouse manager application302may configure one or more virtual warehouses with a schedule that causes those one or more virtual warehouses to be larger during business hours and smaller during non-business hours. This may advantageously save money for an organization: by dynamically scaling the size of virtual warehouses, needlessly large (and thereby needlessly expensive) virtual warehouses need not be maintained.

Though the virtual warehouse manager application302is shown as part of the virtual warehouse servers130, the virtual warehouse manager application302may execute on a wide variety of computing devices. For example, the virtual warehouse manager application may execute on one or more of the computing devices110, such as the same computing device110hosting the request application301. As another example, the virtual warehouse manager application may execute on an entirely separate computing device. Because the virtual warehouse manager application302may perform steps above and beyond conventional virtual warehouse functionality, the application may execute on an entirely separate computing device and may interface with preexisting virtual warehouse systems, e.g., Snowflake.

As part of step305cand305d, the selected virtual warehouse (in this case, the virtual warehouse C303c) may execute the query requested by the request application301. As shown inFIG.3, this entails querying both the data warehouse A304aand the data warehouse B304b. The data warehouses120, such as the data warehouse A304aand the data warehouse B304b, need not be the same: for example, the data warehouse A304amay have an entirely different format, may have entirely different schedules which affect their size at any given time, and may have an entirely different structure as compared to the data warehouse B304b. For instance, the data warehouse A304amay comprise a SQL database, whereas the data warehouse B304bmay comprise a file server which stores files according to the File Allocation Table (FAT) file system. As part of this process, the virtual warehouse C303cmay receive, store, and/or organize results from the data warehouses120. For example, the virtual warehouse C303cmay receive query results from the data warehouse A304aand the data warehouse B304b, may store those results in memory, and then may encrypt those results for security purposes.

As part of step305e, the virtual warehouse C303cprovides the collected results to the virtual warehouse manager application302. Then, as part of step305f, the virtual warehouse manager application302provides the results to one or more of the computing devices110. This process is optional, as the virtual warehouse C303cmay, in some instances, provide the results directly to one or more of the computing devices110. Moreover, the results need not be provided back to the request application301: for example, the results may be provided to an entirely different computing device (e.g., such that the request may have been received from a smartphone but the results may be delivered to an associated laptop) and/or may be provided to an entirely different application (e.g., such that the request may have been received via the request application301, but the results may be received by a separate application, such as a spreadsheet application, executing on one or more of the computing devices110).

The steps depicted inFIG.3are illustrative, and represent simplified examples of processes which may be performed by the elements depicted inFIG.3. For example, while step305ais reflected as an arrow directly leading from the request application301to one or more of the virtual warehouse servers130, the request may in fact be routed through various other computing devices as part of the network140. As another example, the query process reflected in step305cand step305dmay involve a plurality of different transmissions between the virtual warehouse C303cand the data warehouses120.

Discussion will now turn to steps which may be performed from the perspective of a computing device executing the virtual warehouse manager application302.

FIG.4depicts a flowchart with steps which may be performed by a computing device, such as one or more of the computing devices110, the virtual warehouse servers130, and/or the data warehouses120. One or more non-transitory computer-readable media may store instructions that, when executed by one or more processors of a computing device, cause performance of one or more of the steps ofFIG.4. The steps depicted inFIG.4may operate on a Snowflake environment or other virtual warehouse environment, such that they may be performed by a computing device within or external to such an environment. For example, the steps depicted inFIG.4may be performed on a user device external to a preexisting virtual warehouse environment.

In step401, the computing device may log events. For example, the computing device may log a plurality of different events associated with a data sharing platform, such as Snowflake. An event may comprise any activity associated with one or more databases, virtual warehouses, or the like. One example of an event may be a write action to a database of one or more databases. Another example of an event may be a read action to a database of one or more databases. As such, the logged events may represent one or more actions that have been taken with respect to one or more virtual warehouses and/or one or more databases, such as queries, requests to view content, or the like.

In some instances, step401may comprise the computing device receiving a preexisting log of events. In some virtual warehouse platform (e.g., Snowflake) implementations, a log may be maintained of one or more events that have occurred with respect to one or more virtual warehouses and/or one or more databases. In such a circumstance, the computing device may receive (e.g., retrieve) this log, rather than generating it.

Events may be associated with different sizes. For example, one query may be considered large in that it requires a relatively large quantity of computing resources to complete, whereas another query may be considered small in that it requires a relatively smaller quantity of computing resources to complete. The size of an event may implicate how long it takes for a virtual warehouse to complete the event. For example, a large sized query may be executed quickly on a large virtual warehouse, whereas a small virtual warehouse may take quite a long time to complete the large sized query. A user need not specify the size of a query; rather, the size of the query itself may be determined based on an execution plan associated with the query. For example, as indicated above, an execution time may be predicted based on an execution plan corresponding to a query. As such, the particular size of a query may correlate to the predicted execution time of a query: for example, a query requiring less than one second may be considered small, whereas any query over one minute may be considered large.

In turn, a plurality of events may comprise events of a variety of different sizes. For example, the log determined in step401may comprise a first event that satisfies a processing time threshold (e.g., and is considered “large”), whereas the log may also comprise a second event that does not satisfy the processing time threshold (and, e.g., is not considered “large”). In this manner, the plurality of events may comprise a wide variety of different types of events, thus better representing the real variety of events which may occur with respect to a real-world virtual warehouse. This is but one example of an advantage of the present disclosure over other systems: by using real-world events, a wide variety of such events can be collected (and, later, emulated), thus providing significantly more robust and accurate simulation data.

As one example of how events may be divided into different sizes, queries may be added to a group of small queries if the queries took less than a minute to execute on a virtual warehouse. Queries may be added to a group of medium queries if the queries took between one and five minutes to execute on a virtual warehouse. Queries may be added to a group of large queries if the queries took over five minutes to execute.

The log may correspond to a particular time period. For example, a log of a plurality of events may correspond to workdays, the workweek, or the like. The log may be segregated and/or otherwise arranged based on time, such that, e.g., events may be categorized based on time of day (e.g., during the workday, outside of working hours), based on day of the week (e.g., weekdays versus weekends), or the like. Such information may be advantageously used to emulate different times, such that, e.g., the computing device may emulate both workday conditions and weekend conditions. Such information may be advantageous to determine whether, for example, a virtual warehouse should be resized during certain hours (e.g., the evening).

One manner in which the log may be retrieved is through a query history functionality, such as the Query History functionality of the Snowflake architecture. Query history functionalities can store information about previously-executed queries. For example, the Query History functionality of the Snowflake platform stores detailed information about any query that has been executed in a Snowflake system over a time period (e.g., one year). Each query is assigned a unique query identifier. In turn, each query can be uniquely identified and, as will be described in more detail below, emulated.

In step402, the computing device may generate a testing database. For example, the computing device may generate a testing database by duplicating at least one of the one or more databases. This process may comprise generating a zero-copy form of a database at a particular time. For example, the testing database may be a copy of a database at a particular time corresponding to immediately before the events in the log of step401began. In this manner, the events from the log of step401may be re-run on the testing database to produce the current version of a database.

As will be discussed in greater detail below with respect toFIG.7, the computing device may generate one or more testing databases in a manner which copies the state of one or more databases at a particular point in time. For example, the computing device may generate a testing database in a manner which causes the testing database to represent the state of a particular database at a particular point in time before the events logged in step401. In this manner, the computing device may generate a testing database that reflects the state of a database before the events logged in step401were logged, such that one or more of those events may be replayed with respect to the database. To perform this process, a time travel functionality (such as the Snowflake time travel functionality), which enables accessing of historical data at any point within a defined period, may be used.

With that said, the testing database need not always reflect the database at a point in time before the events logged in step401. For example, the testing database may reflect a latest form of the database, such that older queries are executed against the current form of the database. In this manner, changes to the database (e.g., a recent significant growth in size and/or complexity) may be accounted for via the testing database. In such a circumstance, the events may be filtered and/or otherwise adjusted such that they may be performed against the testing database without causing inconsistencies to emerge. For example, if an event logged in step401indicates that a user changed a particular cell (e.g., cell 03215) to a particular value (e.g., “Red”), then the testing database may already reflect that change. In that circumstance, the computing device may modify the event (e.g., to instead change the cell to a different value, such as “Black” or “Blue”), the computing device may may remove the event from the log, and/or the computing device may allow the event to remain in the log (e.g., such that the value “Red” is changed to “Red,” though doing so would ordinarily be a waste of computing resources).

One way the testing database may be generated is by copying all or portions of an existing portion of a virtual warehouse environment. For example, the testing database may be a copy of an existing database in a virtual warehouse environment. Additionally and/or alternatively, the texting database may be generated using a zero-copy functionality, such as the zero-copy functionality of the Snowflake platform. Snowflake's zero-copy cloning feature, for instance, may take a snapshot of any table, schema, or database and creates a derived copy of that object which initially shares the underlying storage. This functionality may be useful for creating instant backups that might not incur any particular storage costs in a virtual warehouse architecture. In other words, this process may allow for the creation of a backup that does not require that data be copied from one storage device or another. For example, when a zero-copy clone of a database is created, the clone might not use data storage because it shares all of the existing micro-partitions of the original table at the time it was closed. That said, as rows are added/deleted/updated in the clone (and independently from the original table), new micro-partitions may be created that are associated with the clone (and not the original database). In this manner, the original data may be preserved even when the testing database is modified during testing.

The testing database may be a copy of the database with which the events from the log in step401were performed. For example, if the events in the log received in step401are with respect to a particular database, then the testing database may be a copy of that particular database. In this manner, the same events may be emulated against the same data.

In step403, the computing device may select a plurality of different virtual warehouse configurations. For example, the computing device may select a plurality of different virtual warehouse configurations for the first virtual warehouse. Each of the plurality of different warehouse configurations may correspond to a different set of computing resources available to the first virtual warehouse. For example, one virtual warehouse configuration may have a different quantity of memory as compared to another configuration. As another example, one virtual warehouse configuration may have a different processor speed than another configuration. As indicated above, such configurations may be referred to in terms oft-shirt sizes. For example, one virtual warehouse configuration may be referred to as “large” if it has at least a first amount of memory, whereas a virtual warehouse may be referred to as “small” if it has less than or equal to a second amount of memory. In some instances, each respective size may be double the number of computing resources (e.g., nodes) as a previous size. For example, a “large” size may be double the size of a “medium” size.

Different virtual warehouse configurations may correspond to different numbers of nodes and/or clusters in use. Broadly, a virtual warehouse may be multi-cluster in that they may use multiple sets of computing resources. Additionally and/or alternatively, a virtual warehouse may be multi-node in that it may entail use of multiple nodes. As such, for example, one “extra small” virtual warehouse may comprise a single node, whereas a “large” virtual warehouse may comprise eight nodes. As such, one virtual warehouse configuration may indicate that a virtual warehouse is to comprise only one node and/or one cluster, whereas another virtual warehouse configuration may indicate that a virtual warehouse can comprise up to a certain number of nodes and/or clusters.

Different virtual warehouse configurations may correspond to different scaling policies. Different virtual warehouses may scale to different sizes (e.g., quantity of computing resources, quantity of clusters, quantity of nodes) at different times. For example, one virtual warehouse configuration may cause a virtual warehouse to scale down its number of computing resources at night, whereas another virtual warehouse configuration may cause a virtual warehouse to dynamically add or remove clusters and/or nodes based on its workload.

Different virtual warehouse configurations may correspond to different auto suspend policies. Virtual warehouses need not be available at all times, and some virtual warehouses may be disabled during certain periods of time and/or based on utilization. For example, a virtual warehouse configuration may cause a virtual warehouse to automatically suspend itself (and thereby preserve computing resources and money) when it has not been used for over an hour. As another example, a virtual warehouse configuration may cause a virtual warehouse to automatically suspend itself at night.

Different virtual warehouse configurations may correspond to different durations of time. For example, certain virtual warehouses may be instantiated for different periods of time (e.g., one hour, one day, one week). For example, one virtual warehouse configuration may indicate that a virtual warehouse should be available for only an hour, whereas another virtual warehouse configuration may indicate that a virtual warehouse should be available for only a week.

In step404, the computing device may measure performance parameters for each of the plurality of different warehouse configurations by, e.g., emulating the logged events from step401. For example, the computing device may measure performance parameters of each of the plurality of different warehouse configurations by emulating, via the first virtual warehouse, the plurality of different events at the testing database. Such performance parameters may comprise, for example, the execution time of a particular event (e.g., as recorded by the EXECUTION TIME field of the Snowflake architecture). An example of such performance parameters is provided asFIG.6, which is discussed in further detail below.

As an example of how the plurality of different events may be emulated, the computing device may first select, from the plurality of different warehouse configurations, a particular virtual warehouse configuration. The computing device may then configure (e.g., instantiate) a first virtual warehouse based on the particular virtual warehouse configuration. Then, the computing device may cause the plurality of different events to be executed with respect to the first virtual warehouse. The computing device need not provide all of the plurality of different events to the first virtual warehouse at once: after all, doing so may easily overwhelm even the largest virtual warehouse, particularly if the plurality of different events is particularly voluminous. Instead, the computing device may, e.g., emulate real activity by causing the plurality of different events to be executed with respect to the first virtual warehouse across a time period. For example, the computing device may insert random delays between the initiation of each of the plurality of different events, thereby emulating the potentially random nature of the plurality of different events. After all, it may be largely unpredictable as to when certain events may initiate in real life.

As such, emulation of the plurality of different events may comprise causing each of the plurality of different events to be initiated at a different time. For example, a first event may be started at 10:01, whereas a second event may be started at 10:04. As some events may be scheduled at particular times, the emulation of the plurality of different events may take into account that certain events may be initiated at those particular times. For example, if a particular event is initiated every two minutes, then the particular event may be emulated such that it repeats every two minutes. As another example, if a particular event only occurs at a particular wall clock time (e.g., midnight), then the particular event may be emulated along with other events that were logged at or around midnight.

Events may be randomly selected during emulation. For example, to emulate the unpredictability of events at a virtual warehouse, the computing device may randomly select ten different events from the log in step401. These events may then be randomly executed in a non-sequential manner by adding a randomized wait time between the initiation of each event.

The performance parameters measured as part of step404may indicate one or more processing times corresponding to each of the plurality of different events. For example, the performance parameters may indicate, for each emulated event of the emulated plurality of different events, a quantity of time taken by a virtual warehouse to complete the emulated event. Such values may be collected (e.g., averaged) based on the size of the event in question. For example, the performance parameters may indicate, for example, that a large virtual warehouse took ten seconds on average to complete large queries, five seconds on average to complete medium queries, and one second on average to complete small queries. Those performance parameters may also indicate, for example, that a small virtual warehouse took twenty minutes on average to complete large queries, one minute on average to complete medium queries, and thirty seconds on average to complete small queries.

As one example of how performance parameters may be collected, performance parameters may be collected for each query emulated as part of step404. To implement such a functionality, performance parameters may be correlated with unique query identifiers (e.g., the query identifiers assigned to queries in the Snowflake Query History functionality).

One way in which performance parameters may be measured is a quantity of events that changed size. The size of a virtual warehouse may change the speed with which a particular event is completed by the virtual warehouse. In turn, based on the size of a virtual warehouse, a size of an event (that is, the processing time required for a particular event) may change. For example, a small virtual warehouse may take fifteen minutes to perform a particular query, meaning that the query may in that circumstance belong to a group of large queries. That said, a large virtual warehouse may take less than a minute to perform the same query, meaning that the same query may in that circumstance belong to a group of small queries. This shift from a large query to a small query may be a useful indicator of the value of increasing the size of a virtual warehouse form small to large.

In step405, the computing device may select an optimized virtual warehouse configuration. This selection may be based on the performance parameters measured in step404. The computing device need not select the virtual warehouse that performs the most quickly or efficiently. Rather, the selection of the optimized virtual warehouse configuration may be based on a balance between the increasing cost of the size of a virtual warehouse as compared to the incremental value such size provides in processing speed.

The selection of the optimized virtual warehouse configuration may be based on the intended use of the virtual warehouse. In some circumstances, events (e.g., queries) may be so time sensitive that the speed of the queries may be worth paying significant sums. In such a circumstance, even if a larger virtual warehouse is extremely expensive, paying for such a virtual warehouse may be worthwhile to ensure that queries are speedily processed. On the other hand, even if time is of a concern, if a small virtual warehouse is approximately equally speedy, then using such a small virtual warehouse may be tolerable.

To provide an example of step405, the performance parameters may indicate that a large virtual warehouse takes one minute on average to process large queries, whereas a small virtual warehouse takes fifteen minutes on average to process the same queries. The cost difference between the large virtual warehouse and the small virtual warehouse may be, for example, fifty thousand dollars a month. In such a circumstance, it may be optimal for the virtual warehouse to be small if, for example, the virtual warehouse is to be used occasionally and/or by a department of an organization that does not require their queries to be answered particularly quickly. In other words, the incremental value of the fourteen minutes saved might not be worth the fifty thousand dollar a month cost. On the other hand, if the virtual warehouse is to be used by a particularly time-sensitive department of an organization, then the incremental value of the fourteen minutes saved may be entirely worth the fifty thousand dollar a month additional cost.

The process depicted in step403through405may entail use of a machine learning model. An example of a neural network architecture via which such machine learning models may be implemented is provided asFIG.5, which is discussed below. Machine learning models may be trained to aid in the selection of an optimized virtual warehouse configuration such that, for example, wider varieties of data may be taken into account when recommending a particular virtual warehouse configuration. The computing device may train, using training data, a machine learning model to output a recommended virtual warehouse configuration. That training data may comprise a history of different virtual warehouse configurations and a history of different event processing times. In other words, the training data need not be specific to certain data, but may reflect a broad set of event processing times across a variety of different virtual warehouse configurations and/or a variety of different databases. The computing device may then provide, to the trained machine learning model, input comprising the performance parameters determined in step404. The computing device may then receive, from the trained machine learning model, output indicating the optimized virtual warehouse configuration. In this manner, the machine learning model may recommend an optimized virtual warehouse configuration based on both historical processing times (as reflected in the training data) and the performance parameters (e.g., that are specific to a particular set of data).

In step406, the computing device may output the optimized virtual warehouse configuration. The output may be in a user interface. For example, the output may comprise a user interface element which prompts a user to accept the optimized virtual warehouse configuration. In such a circumstance, upon user acceptance (by, e.g., selecting a button), one or more existing virtual warehouses may be automatically resized based on the optimized virtual warehouse configuration.

FIG.5depicts an example deep neural network architecture500. The architecture depicted inFIG.5need not be performed on a single computing device, and may be performed by, e.g., a plurality of computers (e.g., any one of the devices depicted inFIG.1). An artificial neural network may be a collection of connected nodes, with the nodes and connections each having assigned weights used to generate predictions. Each node in the artificial neural network may receive input and generate an output signal. The output of a node in the artificial neural network may be a function of its inputs and the weights associated with the edges. Ultimately, the trained model may be provided with input beyond the training set and used to generate predictions regarding the likely results. Artificial neural networks may have many applications, including object classification, image recognition, speech recognition, natural language processing, text recognition, regression analysis, behavior modeling, and others.

An artificial neural network may have an input layer510, one or more hidden layers520, and an output layer530. A deep neural network, as used herein, may be an artificial network that has more than one hidden layer. Illustrated network architecture500is depicted with three hidden layers, and thus may be considered a deep neural network. The number of hidden layers employed in deep neural network500may vary based on the particular application and/or problem domain. For example, a network model used for image recognition may have a different number of hidden layers than a network used for speech recognition. Similarly, the number of input and/or output nodes may vary based on the application. Many types of deep neural networks are used in practice, such as convolutional neural networks, recurrent neural networks, feed forward neural networks, combinations thereof, and others.

During the model training process, the weights of each connection and/or node may be adjusted in a learning process as the model adapts to generate more accurate predictions on a training set. The weights assigned to each connection and/or node may be referred to as the model parameters. The model may be initialized with a random or white noise set of initial model parameters. The model parameters may then be iteratively adjusted using, for example, stochastic gradient descent algorithms that seek to minimize errors in the model.

FIG.6depicts an example of performance parameters600via which an optimized virtual warehouse configuration may be selected. The performance parameters600depicted inFIG.6may be, for example, the same or similar as the performance parameters discussed with respect to step404ofFIG.4.

The performance parameters600are depicted as a table with four columns: a virtual warehouse configuration column601a, an average large event speed column601b, an average medium event speed column601c, an average small event speed601d, and a cost column60. These columns are illustrative, and represent one way in which performance parameters might be collected. For example, in some instances, each separate event might be measured, such that averages (such as those depicted in stepFIG.6) need not be collected.

The first data row602aindicates that a large virtual warehouse configuration costing $30,000 a month completed large events after an average of seven seconds, medium events after an average of two seconds, and small events in less than one second. The second data row602bindicates that a medium virtual warehouse configuration costing $5,000 a month completed large events after an average of fifty-eight seconds, medium events after an average of ten seconds, and small events after an average of five seconds. The third data row602cindicates that a small virtual warehouse configuration costing $1,000 a month completed large events after an average of five minutes, medium events after an average of one minute, and small events after an average of fifty seconds.

The performance parameters600shown inFIG.6illustrate a simplified form of the type of analysis which may be performed to select an optimized virtual warehouse configuration. In this example, the large virtual warehouse configuration is quite costly, but might also guarantee the speedy handling of large events (e.g., large queries). In some circumstances (e.g., time-sensitive business situations), the cost of the large virtual warehouse configuration might be worthwhile. On the other hand, if events (e.g., queries) are not high priority, then it may be more cost-effective to use the medium and/or small virtual warehouse configuration. In practice, this analysis may be significantly more complicated: for example, one virtual warehouse configuration might entail setting a virtual warehouse to be large during certain times of day when large and time-sensitive queries are expected, but small otherwise. Indeed, in practice, the performance parameters600might be hundreds of different rows which reflect a variety of different possible combinations of virtual warehouse configurations (e.g., virtual warehouse size, number of clusters and/or nodes in use at any given time, scaling policies, auto suspend policies, duration(s) of time with which the virtual warehouse is available, etc.).

Discussion will now turn to a manner in which an optimized virtual warehouse configuration may be determined by replaying events on a past version of a database. In particular, as will be discussed below, a snapshot of one or more databases may be captured, and events logged after that snapshot may be replayed in a variety of different configurations. Such a testing approach may be taken where, for example, the size and/or complexity of the database remains substantially the same over time, such that testing of an older form of a database might indicate appropriate configurations for virtual warehouses implementing queries on future forms of that same database. Advantageously, such an approach may allow for testing to be more accurate in certain circumstances: because the events logged are replayed against the same database in the same state (and can be replayed in what amounts to a time-shifted manner), realistic performance parameters may be measured.

FIG.7depicts a flowchart with steps which may be performed by a computing device, such as one or more of the computing devices110, the virtual warehouse servers130, and/or the data warehouses120. One or more non-transitory computer-readable media may store instructions that, when executed by one or more processors of a computing device, cause performance of one or more of the steps ofFIG.7. The steps depicted inFIG.7may operate on a Snowflake environment or other virtual warehouse environment, such that they may be performed by a computing device within or external to such an environment. For example, the steps depicted inFIG.7may be performed on a user device external to a preexisting virtual warehouse environment.

In step701, the computing device may snapshot an initial state of one or more databases. This initial state may comprise an initial state of the data stored of the one or more databases at a particular point in time, such as a point in time before one or more events are logged. This snapshot may be taken by performing a copy (e.g., a zero copy) of the one or more databases, using the time travel functionality of Snowflake, or similar methods. For example, the computing device may generate the testing database by generating a zero-copy clone of the one or more databases. In this manner, the computing device may store a particular version of one or more databases at a particular point in time. For example, the computing device may generate, at a first time, a testing database that corresponds to the state of one or more databases managed by a data sharing platform at a point in time.

Because the time travel functionality of a virtual warehouse environment (e.g., Snowflake) may allow the retrieval of historical states of data, step701need not be performed before events are logged. For example, in some instances, various database states may be preserved over time, and various events may be logged over time. As such, it may be possible to simply select a particular state of a database at a particular point in time, then to retrieve all events performed with respect to that database after the particular point in time.

In step702, the computing device may determine a log of one or more events. For example, the computing device may log the one or more events by logging one or more queries executed with respect to the one or more databases. This step may be the same or similar as step401ofFIG.4. For example, the computing device may determine a log of a plurality of different events executed, via one or more of the plurality of virtual warehouses and after a point in time (e.g., that corresponding to a snapshot of a database), with respect to the one or more databases. The logging in step702may be performed on a continual basis, such that it need not be performed after snapshot(s) of databases are captured, and/or need not end at any particular time. For example, this logging may comprise use of the Snowflake Query History functionality. In circumstances where the one or more events are continually logged, the events may be filtered and/or otherwise organized such that the logged events in step702comprise events that occurred after a time associated with the initial state of the databases snapshot in step701.

In step703, the computing device may generate a testing database based on the snapshot taken in step701. This step may be the same or similar as step402ofFIG.4.

In step704, the computing device may select a plurality of different warehouse configurations. In the example depicted inFIG.7, two configurations (configuration A705aand configuration B705b) have been selected, and are depicted as branching paths. With that said, any number of configurations may be selected and tested. Indeed, hundreds of different possible configurations might be selected and tested. This step may be the same or similar as step403ofFIG.4.

ThoughFIG.7depicts the configuration A705and configuration B705bas branching paths, this need not suggest that the steps in these branching paths be performed in parallel. Indeed, depending on various considerations (e.g., computing resource availability, speed requirements), the steps in each branching path might be performed sequentially, partially in parallel, entirely in parallel, or some combination thereof.

In step706a, the computing device may configure the virtual warehouses with the configuration A705a. Similarly, in step706b, the computing device may configure the virtual warehouses with the configuration B705b. For example, if the configuration A705aentails use of five nodes and/or clusters, then step706amay comprise configuring one or more virtual warehouses to use five nodes and/or clusters. As another example, if the configuration B705bentails use of large-sized nodes and/or clusters, then step706bmay comprise configuring one or more virtual warehouses to use large-sized nodes and/or clusters.

In step707a, the computing device may measure performance parameters for the configuration A705aby running the events, logged in step702, against the testing database generated in step703. For example, the computing device may measure performance parameters of each of a plurality of different warehouse configurations by replaying, via the first virtual warehouse, the plurality of different events at the testing database. Similarly, in step707b, the computing device may measure performance parameters for the configuration B705bby running the events, logged in step702, against the testing database generated in step703. These steps may be the same or similar as step404ofFIG.4.

As part of the processes described above, the computing device may modify the testing database by rolling the testing database back to the state of the database to which it corresponds. For example, after performing step707a, the computing device may roll back the testing database to its status prior to when step707awas performed. In turn, this may allow step707bto be performed against the same testing database.

In step708, the computing device may select an optimized virtual warehouse configuration. This step may be the same or similar as step405ofFIG.4.

In step709, the computing device may output the optimized virtual warehouse configuration. This step may be the same or similar as step406ofFIG.4.

One or more aspects discussed herein may be embodied in computer-usable or readable data and/or computer-executable instructions, such as in one or more program modules, executed by one or more computers or other devices as described herein. Generally, program modules include routines, programs, objects, components, data structures, and the like, that perform particular tasks or implement particular abstract data types when executed by a processor in a computer or other device. The modules may be written in a source code programming language that is subsequently compiled for execution, or may be written in a scripting language such as (but not limited to) HTML or XML. The computer executable instructions may be stored on a computer readable medium such as a hard disk, optical disk, removable storage media, solid-state memory, RAM, and the like. As will be appreciated by one of skill in the art, the functionality of the program modules may be combined or distributed as desired in various embodiments. In addition, the functionality may be embodied in whole or in part in firmware or hardware equivalents such as integrated circuits, field programmable gate arrays (FPGA), and the like. Particular data structures may be used to more effectively implement one or more aspects discussed herein, and such data structures are contemplated within the scope of computer executable instructions and computer-usable data described herein. Various aspects discussed herein may be embodied as a method, a computing device, a system, and/or a computer program product.

Although the present invention has been described in certain specific aspects, many additional modifications and variations would be apparent to those skilled in the art. In particular, any of the various processes described above may be performed in alternative sequences and/or in parallel (on different computing devices) in order to achieve similar results in a manner that is more appropriate to the requirements of a specific application. It is therefore to be understood that the present invention may be practiced otherwise than specifically described without departing from the scope and spirit of the present invention. Thus, embodiments of the present invention should be considered in all respects as illustrative and not restrictive. Accordingly, the scope of the invention should be determined not by the embodiments illustrated, but by the appended claims and their equivalents.