Patent Description:
<NPL>, discloses a workload intelligence framework that exploits advanced machine learning and causality techniques to aid DBAs in their various responsibilities. DBSeer analyzes large volumes of statistics and telemetry data collected from various log files to provide the DBA with a suite of rich functionalities including performance prediction, performance diagnosis, bottleneck explanation, workload insight, optimal admission control, and what-if analysis.

The object of the invention is solved by the features of the independent claims.

Implementations described herein disclose a system for optimizing cloud-based query workloads. In one implementation, the cloud-based query workloads optimization system receives query logs from various query engines to a cloud data service, extracts various query entities from the query logs, parses query entities to generate a set of common workload features, generates intermediate representations of the query workloads, wherein the intermediate representations are agnostic to the language of the plurality of the queries, identifies a plurality of workload patterns based on the intermediate representations of the query workloads, categorizes the workloads in one or more workload type categories based on the workload patterns and the workload features, and selects an optimization scheme based on the category of workload pattern.

Database administrators (DBAs) were traditionally responsible for optimizing the on-premise database workloads. However, with the rise of cloud data services where cloud providers offer fully managed data processing capabilities, the role of a DBA is missing. At the same time, workload optimization becomes even more important for reducing the total costs of operation and making data processing economically viable in the cloud. The technology disclosed herein provides workload optimization in the context of these emerging cloud-based data services. Specifically, the workload optimization platform for cloud query engines disclosed herein includes representing query workloads that in a manner that is agnostic to the query engine and is general enough to describe a large variety of workloads, categorizing the workload patterns, optimizing the corresponding workloads in each category, and provides for adding workload-awareness to a query engines, via the notion of query annotations that are served to the query engine at compile time.

The technology disclosed herein solves a technical solution of database management and optimization that is necessitated by technology problem involving cloud-based databases. Cloud-based databases provide data services to a large number of clients where a various database structures that are different from each other may be used. In such environment, it is difficult for optimizing the query workloads due to differences in query languages, query plans, and database structures. The technology disclosed herein provides for generating database agnostic query representation, which allows to optimize the query workload and then provide feedback to developers and users of the cloud-based databases.

While various implementations disclosed herein are implemented for optimizing the query workloads to cloud-based databases and to provide workload-awareness to a query engines for queries to the cloud-based databases, these implementations can also provide optimization to private data centers supporting a large number of queries from a number of different query engines. For example, such private data centers may be implemented on a single server, a collection of servers, a private cloud, or a public cloud.

<FIG> illustrates an example implementation of a cloud-based query workload optimization system <NUM> disclosed herein. Specifically, the cloud-based query workload optimization system <NUM> may include a large number of databases DS1 <NUM>, DS2 <NUM>,. , DSn <NUM> on a cloud-based data server <NUM>. For example, the database DS1 <NUM> may be an SQL database, the database <NUM> may be a DB2 database, a database DS3 may be a FileMaker database, etc. The data server <NUM> may serve various developers <NUM> that manage these individual databases as well as various users <NUM>, <NUM>, <NUM> that use the databases DS1-DSn.

The data server <NUM> includes a workload optimization engine <NUM> that analyzes data query logs for the databases <NUM>-<NUM>, generates optimization schemes based on the analysis of the databases, and provides feedback to the developers <NUM> and the users <NUM>-<NUM> about selected optimization schemes. In one implementation, the workload optimization engine <NUM> accesses data query logs for the databases <NUM>-<NUM> to analyze the workloads <NUM>-<NUM> for these databases. The workload optimization engine <NUM> may include a workload representation module <NUM>, a workload optimization module <NUM>, and a workload feedback module <NUM>. The functionalities for each of these modules <NUM>-<NUM> are further described below in <FIG>.

The workload optimization engine <NUM> may be implemented using a computing device, such as the computing device disclosed below in <FIG>, where one or more of the modules and their related operations may be implemented by computer executable instructions that are stored in a computer-readable medium. While in the illustrated implementation, the workload optimization engine <NUM> is illustrated as being implemented on the cloud-based data server <NUM>, in an alternative implementation, the workload optimization engine <NUM> may be implemented at a different server that is communicatively connected to the cloud-based data server <NUM>.

The workload representation module <NUM> takes as input the logs from the query engines <NUM>-<NUM> related to one or more of the databases <NUM>-<NUM> as well as related runtime information from the underlying platforms (job scheduler, job manager, resource manager, storage service). The workload representation module <NUM> processes these logs and generates one or more intermediate workload representations that are common across workloads <NUM>-<NUM> and query engines <NUM>-<NUM>.

These intermediate representations may be input to engine-agnostic optimization algorithms of the workload optimization module <NUM>. The workload optimization module <NUM> may be configured to mine the query workload for generating various query patterns and run optimization algorithms to tune those query patterns. Such identifying of the patterns and optimizing for them makes workload optimization practical and less open-ended. In one implementation, each of the pattern class may be associated with one or more optimization algorithms.

The workload feedback module <NUM> collects the output of the workload optimization module <NUM> and converts them into actionable feedback that could be either consumed by the users <NUM>-<NUM> and developers <NUM> in the form of insights and recommendations. Alternatively, the workload feedback module <NUM> may feedback the output of the to the workload optimization module <NUM> to the query engines <NUM>-<NUM> for self-tuning. Specifically, for self-tuning, the feedback is encoded as query annotations and loaded onto a feedback server.

The workload optimization engine <NUM> provides an interface using the query logs that can be implemented for different query engines <NUM>-<NUM>. In one implementation, the workload optimization engine <NUM> provides an extensible infrastructure wherein one or more instrumentations, parsers, patterns, optimizations, and feedback can be added based on evolving workloads <NUM>-<NUM>. Furthermore, the workload optimization engine <NUM> may also provide a library of implementations for each of the one or more instrumentations, parsers, patterns, optimizations, which may act as a starting point for covering more scenarios.

<FIG> illustrates an alternative block diagram of a cloud-based query workload optimization system <NUM> disclosed herein. Specifically, the workload optimization system <NUM> provides one example implementation of the workload optimization engine <NUM> disclosed in <FIG>. The workload optimization system <NUM> may include workload-aware query engines <NUM> that includes query engines QE1, QE2,. The query engines <NUM> may receive queries from various users of cloud-based databases. Various query logs <NUM> (also referred to as the workload logs <NUM>) from the query engines <NUM> are fed to a workload representation module <NUM>.

An implementation of the workload representation module <NUM> provides a mechanism for capturing query plan traits <NUM> from the query logs <NUM>. In one implementation, the query plan traits <NUM> may be logged as signatures in the query logs <NUM>. Specifically, the signatures may capture an internal optimizer state, corresponding to different query plan traits, into fixed sized hashes and output the fixed sized hashes as part of the query logs <NUM>. For example, the signatures may be of different types to capture different query plan traits. In one implementation, the signatures may be composed to identify combined traits so that they may be used across multiple query engines QE1-QEn. Table I below illustrates example signatures depending on whether for a given operator, its underlying subgraph, the subgraph parameters, and the subgraph inputs are hashed or not.

In one implementation of the workload representation module <NUM>, an application programming interface (API) may be provided to take a query plan subexpression from a query engine to generate a signature as an output.

The workload representation module <NUM> also includes a feature store <NUM> that stores features from query logs <NUM>. Specifically, the workload representation module <NUM> parses the query logs <NUM> to generate a common set of features that are stored in the feature store <NUM>. For example, the workload representation module <NUM> extracts one or more relevant entities from the query logs <NUM> (examples of such entities are illustrated and discussed below in <FIG>). Specifically, the information about the query entities includes query metadata, query plans, and runtime statistics - together referred to as the query traces.

Example query metadata may include flags and parameters provided with the query, user and account names, query submit, start, and end times, available resources, etc., including flags and parameters provided with the query, user and account names, query submit, start, and end times, available resources, etc. Example query plans include the logical (input, analyzed, optimized), the physical, and the execution plans for the query. Whereas example runtime statistics may include row counts, latency, CPU time, I/O time, memory usage, etc..

The workload representation module <NUM> parses the query traces from various query logs <NUM>. In one implementation, a number of parsers are used with each parser configured to parse query traces from a particular type of query engine, such as a parser for XML queries, a parser for JSON queries, a parser for plain text queries, etc. These parsers output a set of common workload features that are stored in the query feature store <NUM>. In one implementation, the query feature store <NUM> has an extensible design to add more query engines, extract other pieces of information from the query <NUM> log, add new parsers for custom query formats, and add newer query workload features as they emerge.

The workload representation module <NUM> uses the entities in the query feature store <NUM> to generate intermediate workload representations <NUM>. Specifically, the intermediate workload representations <NUM> are generalized across various disparate query processors, such that they can be used to run various optimization algorithms. Examples of the intermediate workload representations <NUM> are discussed in further detail below in <FIG>.

The intermediate workload representations <NUM> are analyzed to identify query patterns <NUM>. For example, such query patterns may include a recurring query pattern, a similarity query pattern, a dependency query pattern, etc. Example structures of these query patterns <NUM> are further illustrated below in <FIG>. Once the query patterns <NUM> are identified, a workload optimization module <NUM> selects an optimization type based on the query patterns <NUM>.

Specifically, if the query pattern <NUM> is identified as a recurring query pattern, learned optimization algorithms <NUM> are used to optimize the workload. Similarly, if the query pattern <NUM> is identified as a similarity query pattern, multi-query optimization algorithms <NUM> are used to optimize the workload. On the other hand, if the query pattern <NUM> is identified as a dependency query pattern, one of dependency-driven optimization algorithm <NUM> is used to optimize the workload.

An example of a learned optimization algorithm <NUM> includes models that analyze recurring workloads and provide it as feedback to the query engine, such as for example, a neural network. An example of a multi-query optimization algorithm <NUM> may involve caching data at various layers in the data service so as to serve multiple query engines without having to execute the queries at multiple times. On the other hand, a dependency-driven query optimization algorithm <NUM> may include computing the relative importance of queries in a data pipeline and scheduling them according to their importance. Another example of a dependency-driven query optimization algorithm <NUM> may consist of a pipeline of queries that have data dependencies between them, such that the output of a producer query may be used in a subsequent consumer query.

The workload optimization module <NUM> outputs feedback <NUM> that can be used for various actions by the users <NUM> and developers <NUM>. The feedback <NUM> may include insights <NUM>, recommendations <NUM>, and self-tunings <NUM>. Specifically, the insights <NUM> may be summaries and reports over the workload intermediate representations to help users understand their workload and take any appropriate tuning actions based on their interpretation. An example of such a summary is a summary over subexpression intermediate representations. On the other hand, the recommendations <NUM> are outputs of the optimization algorithms that are provided as hints to the users. Users can apply these hints using the tuning knobs provided by the query engines. For example, such hints may include a row count hint, an operator algorithm hint, and forcing a join order hint.

Finally, the self-tunings <NUM> may include encoding workload optimization decisions into query annotations <NUM>, which are in a format that is extensible to add more optimizations and that may be integrated with multiple query engines. The query annotations <NUM> provide an interface between the workload optimization feedback and the changes in the query engines to consume that feedback. In one implementation, a query annotation <NUM> may include a signature, an action, and a set of parameters. Here the signature is a query plan identifier as described above in Table <NUM>. The actions are the names of the self-tunings to be performed by the query engine, such as for example, the configuration to apply, the tuning knob to set, the query optimizer rule to invoke, etc. The parameters provide the information needed for the action, such as for example, the configuration value or the optimizer rule parameter. Note that in some instances, the signature may have several actions, or a given action may be applied to several signatures. Thus, the query annotations <NUM> specify the self-tuning actions using the parameters and conditioned upon the query plan signatures.

The query annotations <NUM> are fed back by a feedback service engine <NUM> to the query engines <NUM> and they may be consumed by query engines <NUM> during compilation. In one implementation, the query annotations <NUM> are output into a file in cloud storage location. In such an implementation, the feedback service engine <NUM> periodically polls this file for new query annotations <NUM> and bulk loads any new query annotations <NUM> to the query engines <NUM>. Alternatively, the feedback service engine <NUM> provides APIs to lookup the query annotations <NUM> by their signatures, such that it can return all query annotations <NUM> for a given signature. Furthermore, each of the query annotations <NUM> may be associated with a customer account such that a query from a customer account can load only those query annotations <NUM> that are associated with that customer account. In an alternative implementation, the feedback service engine <NUM> allows to add tags to the query annotations <NUM> and to batch lookup all annotations for given tag(s). An example of such a tag may be a recurring job name, such as a periodic job that appears with a similar name each time. For such jobs, the query engine may load all query annotations <NUM> corresponding to that recurring job name in a single lookup.

An implementation of the feedback service engine <NUM> provides an index on the signatures and the tags of the query annotations <NUM> to make the batch lookups faster. Furthermore, the feedback service engine <NUM> bulk loads the annotations for a customer account, thereby not having to update the indexes incrementally. Because many queries may have common subexpressions and therefore common query annotations <NUM> as well, in another implementation, the feedback service engine <NUM> caches the query annotations <NUM> in an application layer. In another implementation, the feedback service engine <NUM> expires query annotations <NUM> when new annotations for the same signatures and the same actions are available, such that the new query annotations <NUM> over-ride the older query annotations <NUM>.

Providing the query annotations <NUM> via the feedback service engine <NUM> makes the query engines <NUM> workload aware such that they are learning from how things went in the past workloads and taking optimization actions into consideration for future queries. An example implementation of a workload aware query engine is disclosed below in <FIG>.

<FIG> illustrates example entity relations <NUM> of various entities in a feature store of the cloud-based query workload optimization system disclosed herein. Specifically, the entity relations <NUM> shows common workload features that are output by a parser that parses query traces from query logs. As illustrated in the entity relations <NUM>, a workload entity <NUM> is related to applications entity <NUM>. Thus, a workload <NUM> may be for an application <NUM>, such as an SQL based application, a DB2 based application, etc. The applications <NUM> are related to queries <NUM>, metadata <NUM>, and aggregate metrics <NUM> generated from the applications. Each of the applications <NUM> and the queries <NUM> may have related metadata <NUM>. The queries <NUM> are also related to query plans. Furthermore, query plans <NUM> have query operators <NUM> and the operators have query operator instances <NUM>.

As shown in <FIG>, each of the applications <NUM>, the queries <NUM>, the query plans <NUM>, query operators <NUM>, and query operator instances <NUM> are related to their respective aggregate metrics 320a-320e.

<FIG> illustrates example intermediate workload representations <NUM> for the cloud-based query workload optimization system disclosed herein. Specifically, the intermediate workload representations <NUM> include plans intermediate representations <NUM>, subexpressions intermediate representations <NUM>, and operator instances intermediate representations <NUM>. Specifically, the intermediate workload representations <NUM> denormalizes the workload entities (such as the workload entities <NUM> disclosed in <FIG>) for more efficient processing by various optimization applications. For example, optimization applications can create and use on or more of these intermediate workload representations <NUM> depending on the granularity of the information they need. The intermediate workload representations <NUM> may be stored using the same set of connectors as used for accessing the query logs <NUM>, and may be used for quickly drawing insights and building optimizations for a query workload. Furthermore, the intermediate workload representations <NUM> may also be shared across multiple workload optimization applications. For example, the subexpressions intermediate representations <NUM> may be used for finding subexpressions to materialize, learning cardinalities over recurring workloads, mining physical design hints, etc. In one implementation, the intermediate workload representations <NUM> are generalized across query engines.

<FIG> illustrates example workload patterns <NUM> detected by the cloud-based query workload optimization system disclosed herein. Specifically, the recurring query pattern <NUM> represents repetitive query workloads where same queries executed periodically with new inputs and parameters. The recurring query pattern <NUM> indicates the predictive nature of the workload. On the other hand, the similarity query pattern <NUM> represent the similarity between queries on the same data. This results when queries are written by multiple users who access the same sets of inputs in the cloud infrastructure. Finally, the dependency query pattern <NUM> illustrate data pipelines where the output of one query (from the previous recurring interval) is consumed by a subsequent query (in the subsequent recurring interval).

<FIG> illustrates example operations <NUM> of the cloud-based query workload optimization system disclosed herein. An operation <NUM> receives query logs from various query engines for a cloud-based data service. For example, the cloud-based data service may have databases based on SQL, DB2, etc. And the operation <NUM> receives queries for one or more of these databases. Query logs are generated over time based on the queries to these databases. Subsequently, an operation <NUM> extracts query entities from the query logs. Examples of various query entities are discussed in further detail above in <FIG>.

An operation <NUM> parses the query entities to generate a set of common workload features along with relationships between these features. The workload features may include, for example, counts and cardinalities for each operator of the query, size of the data returned for the query, etc. An operation <NUM> generates intermediate workload representations that are agnostic to the query engine and is general enough to describe a large variety of workloads. Specifically, operation <NUM> generates more efficient workload intermediate representations (IRs), which generalize across query processors, and can be used to run various optimization algorithms on top. Example intermediate workload representations <NUM>-<NUM>, which denormalizes the workload entities for more efficient processing, are illustrated above in <FIG>.

An operation <NUM> uses the intermediate workload representations to identify one or more workload patterns. Example workload patterns may be a recurring pattern, a similarity pattern, a dependency pattern, etc., as illustrated above in <FIG>. An operation <NUM> categorizes workloads in various workload categories based on the workload patterns and based on the categorization, an operation <NUM> selects an optimization scheme based on the workload category. Thus, for example, a workload categorized to have a recurring pattern may be optimized based on a learned optimization scheme, a workload categorized to have a similarity pattern may be optimized based on a multi-query optimization scheme, and a workload categorized to have a dependency pattern may be optimized based on a dependency-driven optimization scheme. An operation <NUM> optimizes the workload using the selected optimization scheme. Subsequently, an operation <NUM> generates query annotations for feedback and actions by users and developers of the cloud-based databases. A feedback engine <NUM> providing the feedback to various workload-aware query engines is disclosed in further detail above with respect to <FIG>.

<FIG> illustrates an example implementation of a workload aware query engine system <NUM>. A query engine <NUM> may be configured using various configuration rules <NUM> for one or more queries from user. The query engine <NUM> is configured to learn from how things went in the past workloads and taking optimization actions for future queries. In the illustrated implementation, the query engine <NUM> has mechanisms to load the query annotations feedbacks for the past workloads from a feedback service <NUM> and the capability to take optimization decisions based on that feedback using an optimizer <NUM>.

Specifically, the feedback service <NUM> is configured to load query annotations <NUM> into a feedback loop and action module <NUM> that includes a compiler <NUM> and the optimizer <NUM>. In one implementation, the optimizer <NUM> looks up a query annotation from the query annotations <NUM> for each signature in the optimizer <NUM>. Alternatively, relevant signatures, using the tags defined in the feedback service <NUM>, are preloaded into the compiler <NUM>. Alternatively, for a smaller self-contained applications, all available signatures are pre-loaded into the compiler <NUM> or the optimizer <NUM> for later use when applicable.

In one implementation, the query annotations <NUM> are uploaded using an HTTP request to the feedback service <NUM>. Alternatively, a file including the query annotations <NUM> may be loaded directly to the feedback loop and action module <NUM> for debugging purpose. Furthermore, developers of the query engine <NUM> may also create and test new annotation feedback without having to go via the feedback service <NUM>. Optimization decisions may be made at either the compiler <NUM>, at the optimizer <NUM>, at the scheduler <NUM>, or at runtime <NUM>. Therefore, each of these stages may communicate with the workload representation module <NUM> that stores the workload intermediate representations used by the workload optimization module <NUM>.

<FIG> illustrates an example system <NUM> that may be useful in implementing the multi-modality video recognition system disclosed herein. The example hardware and operating environment of <FIG> for implementing the described technology includes a computing device, such as a general-purpose computing device in the form of a computer <NUM>, a mobile telephone, a personal data assistant (PDA), a tablet, smart watch, gaming remote, or other type of computing device. In the implementation of <FIG>, for example, the computer <NUM> includes a processing unit <NUM>, a system memory <NUM>, and a system bus <NUM> that operatively couples various system components including the system memory to the processing unit <NUM>. There may be only one or there may be more than one processing unit <NUM>, such that the processor of the computer <NUM> comprises a single central-processing unit (CPU), or a plurality of processing units, commonly referred to as a parallel processing environment. The computer <NUM> may be a conventional computer, a distributed computer, or any other type of computer; the implementations are not so limited.

The system bus <NUM> may be any of several types of bus structures including a memory bus or memory controller, a peripheral bus, a switched fabric, point-to-point connections, and a local bus using any of a variety of bus architectures. The system memory may also be referred to as simply the memory, and includes read only memory (ROM) <NUM> and random access memory (RAM) <NUM>. A basic input/output system (BIOS) <NUM>, containing the basic routines that help to transfer information between elements within the computer <NUM>, such as during start-up, is stored in ROM <NUM>. The computer <NUM> further includes a hard disk drive <NUM> for reading from and writing to a hard disk, not shown, a magnetic disk drive <NUM> for reading from or writing to a removable magnetic disk <NUM>, and an optical disk drive <NUM> for reading from or writing to a removable optical disk <NUM> such as a CD ROM, DVD, or other optical media.

The hard disk drive <NUM>, magnetic disk drive <NUM>, and optical disk drive <NUM> are connected to the system bus <NUM> by a hard disk drive interface <NUM>, a magnetic disk drive interface <NUM>, and an optical disk drive interface <NUM>, respectively. The drives and their associated tangible computer-readable media provide non-volatile storage of computer-readable instructions, data structures, program modules and other data for the computer <NUM>. It should be appreciated by those skilled in the art that any type of tangible computer-readable media may be used in the example operating environment.

A number of program modules may be stored on the hard disk drive <NUM>, magnetic disk <NUM>, optical disk <NUM>, ROM <NUM>, or RAM <NUM>, including an operating system <NUM>, one or more application programs <NUM>, other program modules <NUM>, and program data <NUM>. A user may generate reminders on the personal computer <NUM> through input devices such as a keyboard <NUM> and pointing device <NUM>. Other input devices (not shown) may include a microphone (e.g., for voice input), a camera (e.g., for a natural user interface (NUI)), a joystick, a game pad, a satellite dish, a scanner, or the like. These and other input devices are often connected to the processing unit <NUM> through a serial port interface <NUM> that is coupled to the system bus <NUM>, but may be connected by other interfaces, such as a parallel port, game port, or a universal serial bus (USB) (not shown). A monitor <NUM> or other type of display device is also connected to the system bus <NUM> via an interface, such as a video adapter <NUM>. In addition to the monitor, computers typically include other peripheral output devices (not shown), such as speakers and printers.

The computer <NUM> may operate in a networked environment using logical connections to one or more remote computers, such as remote computer <NUM>. These logical connections are achieved by a communication device coupled to or a part of the computer <NUM>; the implementations are not limited to a particular type of communications device. The remote computer <NUM> may be another computer, a server, a router, a network PC, a client, a peer device or other common network node, and typically includes many or all of the elements described above relative to the computer <NUM>. The logical connections depicted in FIG. <NUM> include a local-area network (LAN) <NUM> and a wide-area network (WAN) <NUM>. Such networking environments are commonplace in office networks, enterprise-wide computer networks, intranets and the Internet, which are all types of networks.

When used in a LAN-networking environment, the computer <NUM> is connected to the local network <NUM> through a network interface or adapter <NUM>, which is one type of communications device. When used in a WAN-networking environment, the computer <NUM> typically includes a modem <NUM>, a network adapter, a type of communications device, or any other type of communications device for establishing communications over the wide area network <NUM>. The modem <NUM>, which may be internal or external, is connected to the system bus <NUM> via the serial port interface <NUM>. In a networked environment, program engines depicted relative to the personal computer <NUM>, or portions thereof, may be stored in the remote memory storage device. It is appreciated that the network connections shown are examples and other means of communications devices for establishing a communications link between the computers may be used.

In an example implementation, software or firmware instructions for providing attestable and destructible device identity may be stored in memory <NUM> and/or storage devices <NUM> or <NUM> and processed by the processing unit <NUM>. One or more ML, NLP, or DLP models disclosed herein may be stored in memory <NUM> and/or storage devices <NUM> or <NUM> as persistent datastores. For example, a cloud-based query workload optimization system <NUM> may be implemented on the computer <NUM> as an application program <NUM> (alternatively, the cloud-based query workload optimization system <NUM> may be implemented on a server or in a cloud environment). The cloud-based query workload optimization system <NUM> may utilize one of more of the processing unit <NUM>, the memory <NUM>, the system bus <NUM>, and other components of the personal computer <NUM>.

In contrast to tangible computer-readable storage media, intangible computer-readable communication signals may embody computer readable instructions, data structures, program modules or other data resident in a modulated data signal, such as a carrier wave or other signal transport mechanism. By way of example, and not limitation, intangible communication signals include wired media such as a wired network or direct-wired connection, and wireless media such as acoustic, RF, infrared and other wireless media.

A physical article of manufacture disclosed herein includes one or more tangible computer-readable storage media, encoding computer-executable instructions for executing on a computer system a computer process, the computer process includes receiving query logs from various query engines to a cloud data service, extracting various query entities from the query logs, parsing query entities to generate a set of common workload features, generating intermediate representations of the query workloads, wherein the intermediate representations are agnostic to the language of the plurality of the queries, identifying a plurality of workload patterns based on the intermediate representations of the query workloads, categorizing the workloads in one or more workload type categories based on the workload patterns and the workload features, and selecting an optimization scheme based on the category of workload pattern.

In an alternative implementation, the computer process further includes optimizing the workload using the selected optimization scheme. Alternatively, categorizing the workloads further includes categorizing the workloads based on recurring workload pattern, similarity workload pattern, and dependency workload pattern. Alternatively, selecting an optimization scheme based on the category of workload pattern further comprising selecting a dependency-driven optimization scheme if the workload pattern is a dependency pattern. Yet alternatively, selecting an optimization scheme based on the category of workload pattern further comprising selecting a learned optimization scheme if the workload pattern is a recurring pattern.

In an alternative implementation, selecting an optimization scheme based on the category of workload pattern further comprising selecting a multi-query optimization scheme if the workload pattern is a similarity pattern. In another implementation, the computer process further includes generating query annotations that can be used by the query engines during query compilation. Yet alternatively, the query annotations include a signature, an action, and a set of parameters to be consumed by a query compiler. Alternatively, the query annotations are output as a file on a cloud storage location to be accessed by the query engine. In one implementation, the query entities further comprising query metadata, query plans, runtime statistics.

A method of providing cloud-based query workload optimization includes receiving query logs from various query engines to a cloud data service, extracting various query entities from the query logs, parsing query entities to generate a set of common workload features, generating intermediate representations of the query workloads, wherein the intermediate representations are agnostic to the language of the plurality of the queries, identifying a plurality of workload patterns based on the intermediate representations of the query workloads, categorizing the workloads in one or more workload type categories based on the workload patterns and the workload features, and selecting an optimization scheme based on the category of workload pattern.

In one implementation, the method further includes optimizing the workload using the selected optimization scheme. In an alternative implementation, categorizing the workloads further comprising categorizing the workloads based on recurring workload pattern, similarity workload pattern, and dependency workload pattern. An alternative implementation further includes selecting a dependency-driven optimization scheme if the workload pattern is a dependency pattern, selecting a learned optimization scheme if the workload pattern is a recurring pattern, and selecting a multi-query optimization scheme if the workload pattern is a similarity pattern. An alternative implementation further includes generating query annotations that can be used by the query engines during query compilation. In another implementation the query annotations include a signature, an action, and a set of parameters to be consumed by a query compiler. Alternatively, the query annotations are output as a file on a cloud storage location to be accessed by the query engine.

An implementation disclosed herein includes a system implemented in a computing environment, wherein the system includes a memory, one or more processing units, and a cloud-based query workload optimization system stored in the memory and executable by the one or more processor units, the cloud-based query workload optimization system encoding computer-executable instructions on the memory for executing on the one or more processor units a computer process, the computer process including receiving query logs from various query engines to a cloud data service, extracting various query entities from the query logs, parsing query entities to generate a set of common workload features, generating intermediate representations of the query workloads, wherein the intermediate representations are agnostic to the language of the plurality of the queries, identifying a plurality of workload patterns based on the intermediate representations of the query workloads, categorizing the workloads in one or more workload type categories based on the workload patterns and the workload features, selecting an optimization scheme based on the category of workload pattern, and optimizing the workload using the selected optimization scheme. In one implementation, categorizing the workloads further comprising categorizing the workloads based on recurring workload pattern, similarity workload pattern, and dependency workload pattern. In another implementation, the computer process further includes selecting a dependency-driven optimization scheme if the workload pattern is a dependency pattern, selecting a learned optimization scheme if the workload pattern is a recurring pattern, and selecting a multi-query optimization scheme if the workload pattern is a similarity pattern.

The implementations described herein are implemented as logical steps in one or more computer systems. The logical operations may be implemented (<NUM>) as a sequence of processor-implemented steps executing in one or more computer systems and (<NUM>) as interconnected machine or circuit modules within one or more computer systems. The implementation is a matter of choice, dependent on the performance requirements of the computer system being utilized. Accordingly, the logical operations making up the implementations described herein are referred to variously as operations, steps, objects, or modules. Furthermore, it should be understood that logical operations may be performed in any order, unless explicitly claimed otherwise or a specific order is inherently necessitated by the claim language.

Claim 1:
A physical article of manufacture including one or more tangible computer-readable storage media, encoding computer-executable instructions for executing on a computer system a computer process, the computer process comprising:
receiving query logs (<NUM>) from various query engines (<NUM>) to a cloud data service;
extracting various query traces from the query logs (<NUM>), wherein the query traces include at least one of query metadata, query plans and runtime statistics;
parsing query traces using a plurality of parsers with each parser configured to parse query traces from a particular type of query engine to generate a set (<NUM>) of common workload features;
generating intermediate representations (<NUM>) of the query workloads using the set of common workload features, wherein the intermediate representations (<NUM>) are agnostic to the language of the plurality of the queries and are common across workloads and query engines;
identifying a plurality of workload patterns (<NUM>) based on the intermediate representations (<NUM>) of the query workloads;
categorizing the workloads in one or more workload type categories based on the workload patterns (<NUM>) and the workload features; and
selecting an optimization scheme based on the category of workload pattern.