Method and system for processing multiple database queries on database query engine side

Abstract

A method of processing a plurality of database queries on a database query engine side. The method includes receiving database queries from a requester to the database query engine, parsing each database query using an SQL parser module and creating an abstract syntax tree. Further, the method includes creating, using a logical planner module, a modified version of the abstract syntax tree for each database query. The logic planner module receives input from the batch query optimizer module to create a global query plan. Further, the method includes inputting the global query plan to a distributed planner module, an execution planner module, and a scheduler module to create an execution plan, and executing the database query using a shared operator. The shared operator executes the operation on the data in the data source in a manner of executing the operation on the data aiming at the database query, and the database query is associated with the data in the data source.

Description

Technical Field

[0001] The present invention generally relates to the field of database management systems, and more particularly, to a method and system for processing multiple database queries at a database query engine. Background Art

[0002] Typically, massively parallel processing (MPP) database systems are designed to handle workloads in the form of large numbers of database queries by distributing the workload across a large number of processors or nodes. MPP database systems involve the use of a multi-tenant infrastructure, in which a single database system serves multiple tenants or consumers in a data warehouse application. Furthermore, due to the multi-tenant infrastructure in an MPP database system, the MPP database system receives a large number of database queries from multiple consumers at once. Typically, in an MPP database system, one database query is evaluated at a time. However, traditional approaches are inefficient in meeting the high-output requirements of a multi-tenant infrastructure, and traditional MPP database systems are not sufficiently scalable for database queries, which is undesirable.

[0003] Currently, attempts have been made to process the large number of database queries in traditional MPP database systems by introducing multi-query optimization (MQO) technology. MQO technology shares database queries among different processors in an MPP database system by searching for common subexpressions in multiple database queries. However, MQO technology does not scale well with the number of joins and workload size, which is undesirable. Some attempts have used shared workload optimization (SWO) technology, which maximizes workload sharing among different processors in an MPP database system to minimize the time required for each processor to execute database queries. The problem with SWO technology is that it is inefficient when the workload of database queries is large and has certain requirements for the order in which database queries arrive. Other attempts have included using machine learning methods to process the workload, but these methods are limited in applicability due to long training cycles, making them undesirable. Therefore, a technical challenge remains: how to efficiently process multiple database queries in a short period of time while maintaining stability with respect to the order in which queries arrive, as is typical with traditional database query engines in MPP database systems.

[0004] Therefore, based on the above discussion, there is a need to overcome the above-mentioned shortcomings associated with traditional MPP database systems. Summary of the Invention

[0005] The present invention provides a method and system for processing multiple database queries on the database query engine side. The present invention provides a technical solution to the existing problem of how to efficiently process multiple database queries in a relatively short period of time and with stability in the order of arrival of queries associated with conventional database query engines in massively parallel processing (MPP) database systems. The present invention aims to provide a technical solution that at least partially overcomes the problems encountered in the prior art and provides an improved method and system for processing multiple database queries and batch query optimization on the database query engine side.

[0006] One or more objects of the present invention are achieved by the technical solutions provided in the accompanying independent claims. Advantageous embodiments of the present invention are further defined in the dependent claims.

[0007] In one aspect, the present invention provides a method for processing multiple database queries on a database query engine side, wherein the method comprises the following steps: receiving the multiple database queries from one or more requesters into the database query engine; parsing each database query in the multiple database queries using a structured programming language (SQL) parser module of the database query engine; creating an abstract syntax tree for each database query in the multiple database queries; creating a modified version of the abstract syntax tree for each database query in the multiple database queries using a logic planner module of the database query engine, wherein the logic planner module receives input from a batch query optimizer module; generating a global query plan corresponding to the multiple database queries, wherein the batch query optimizer module reorders the queries in the multiple database queries using an overlap graph, wherein the overlap graph is generated by creating a vertex for each database query in the multiple database queries and generating a global query plan at each database query. The method comprises adding directed edges between pairs of vertices, wherein weights are assigned to the directed edges according to a degree of commonality or overlap between data sources associated with the two queries corresponding to the pair of vertices; inputting the global query plan into a distributed planner module, an execution planner module, and a scheduler module, each of which is a module of the database query engine, to create an execution plan for executing the multiple database queries according to the global query plan; and executing the multiple database queries according to the execution plan using multiple shared operators, wherein the shared operators perform operations on the data in the data source by performing operations on the data for multiple queries, each of which is associated with the data in the data source.

[0008] The method has the advantage of processing a large number of queries at once through a batch query optimizer module, eliminating the impact of the order of multiple database queries during execution. Furthermore, the batch query optimizer module disclosed in the method is stable for a wide range of database queries and can achieve data placement optimization. Furthermore, the batch query optimizer module optimizes sharing and placement decisions for the database queries, thereby improving the performance of the traditional MPP database system. Furthermore, the batch query optimization disclosed in the method allows for an efficient recovery mechanism in the context of shared operators by reusing common subexpressions across all database queries.

[0009] In one implementation, the method further comprises the step of running a topological sort on the overlay graph to derive a reordering of the queries in the plurality of database queries.

[0010] A topological sort run on the overlay graph allows for efficient determination of a linear ordering of the overlay graph vertices.

[0011] In one implementation, the first shared operator is a shared scan operator.

[0012] The shared scan operator reduces the amount of redundant work that must be performed during database query execution by allowing multiple operators to share the same scan of the database, rather than each operator performing a separate scan.

[0013] In another implementation, the second shared operator is a shared filter operator, wherein the shared filter operator receives data from a data source that has been scanned by the shared scan operator.

[0014] The shared filter operator enables multiple operators to share the same filter conditions for multiple database queries, thereby improving the execution speed of the database queries.

[0015] In another implementation, the shared filter operator examines each data tuple obtained from the data source by the shared scan operator and determines whether each data tuple satisfies a corresponding condition of each query, and annotates each data tuple with an identifier of the corresponding query whose condition is satisfied by the data tuple.

[0016] The shared filter operator annotates data tuples with corresponding queries to enhance relational operators with query set operation semantics.

[0017] In another implementation, the third sharing operator is a shared join operator that receives output from each of the plurality of shared filter operators, each shared filter operator operating on a corresponding data source scanned by a corresponding shared scan operator.

[0018] The shared join operator may merge multiple tables in the data source to retrieve data from multiple tables in a single database query to improve data integrity and reduce data redundancy.

[0019] In another implementation, the fourth sharing operator is a router operator that routes the output of the shared join operator to a requester that has sent the corresponding query to the database query engine.

[0020] The router operator enables efficient query execution by directing the results of database queries to operators based on specific conditions.

[0021] In another implementation, the router operator sends tuples to multiple downstream operators.

[0022] The downstream operators enable flexible database query execution by breaking down the query into smaller, more manageable parts and enabling each part to be processed independently.

[0023] In another implementation, the global query plan is created according to a cost-based optimization algorithm.

[0024] The cost-based optimization algorithm executes the database query to reduce the execution cost of the database query.

[0025] In one implementation, shared operator calculations are pre-computed and stored in a cache.

[0026] The cache is used to rewrite database queries in an efficient manner and reduce the execution time of database queries by providing auxiliary memory for storing immediate database query execution results.

[0027] In another aspect, the present invention provides a module comprising a method adapted to perform all the steps according to any of the above method claims.

[0028] The disclosed system achieves all the advantages and technical effects of the dipole of the present invention.

[0029] It should be understood that all the above embodiments can be combined together. It should be noted that all devices, elements, circuits, units and modules described in this application can be implemented in software elements or hardware elements or any type of combination thereof. The steps performed by the various entities described in this application and the functions to be performed by the various entities described are intended to refer to the various entities for performing the various steps and functions. Even if in the description of the following specific embodiments, the specific functions or steps to be performed by the external entity are not reflected in the description of the specific detailed elements of the entity performing the specific steps or functions, it should be clear to the technician that these methods and functions can be implemented in the corresponding software or hardware elements, or in any combination of such elements. It is understandable that the features of the present invention are easy to be combined in various combinations without departing from the scope of the present invention as defined by the appended claims.

[0030] Other aspects, advantages, features and objects of the present invention will become apparent from the accompanying drawings and detailed description of illustrative implementations when read in conjunction with the following appended claims. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] The foregoing summary of the invention and the following detailed description of illustrative embodiments may be better understood when read in conjunction with the accompanying drawings. For purposes of illustrating the present invention, exemplary configurations of the invention are shown in the accompanying drawings. However, the present invention is not limited to the specific methods and means disclosed herein. Furthermore, those skilled in the art will appreciate that the drawings are not drawn to scale. Where possible, identical elements are represented by identical numerals.

[0032] The following describes the embodiments of the present invention by way of example only and in conjunction with the following drawings.

[0033] Figure 1 A flowchart of a method for processing multiple database queries on a database query engine side provided by an embodiment of the present invention;

[0034] Figure 2 A block diagram of a system for processing multiple database queries on a database query engine side provided by an embodiment of the present invention;

[0035] Figure 3 A block diagram of a database query engine provided by an embodiment of the present invention;

[0036] Figure 4A and Figure 4B Different diagrams illustrating various exemplary implementations of common connection templates with different filter predicates provided for embodiments of the present invention;

[0037] Figure 5A diagram illustrating an exemplary implementation of shared execution in a select-project-join (SPJ) query according to an embodiment of the present invention;

[0038] Figure 6A and Figure 6B Different block diagrams illustrating various exemplary implementations of demonstrating the importance of query arrival order in public sharing opportunities provided for embodiments of the present invention;

[0039] Figure 7A and Figure 7B Different block diagrams illustrating various exemplary implementations of the DATAPATH system in operating modes provided for embodiments of the present invention;

[0040] Figure 8 A block diagram illustrating an exemplary implementation of shared execution provided for an embodiment of the present invention;

[0041] Figure 9 A block diagram illustrating an exemplary implementation of the present invention that demonstrates the advantages of replication and partitioning relationships;

[0042] Figure 10 A block diagram illustrating an embodiment of the architecture of a data placement-aware optimizer provided in accordance with an embodiment of the present invention;

[0043] Figure 11 A block diagram describing an algorithm for selecting a materialized view according to an embodiment of the present invention.

[0044] In the accompanying drawings, underlined numbers are used to indicate the item in which the underlined number appears or the item adjacent to the underlined number. Non-underlined numbers are associated with the item identified by the line linking the non-underlined number to the item. When a number is not underlined but has an associated arrow, the non-underlined number identifies the general item to which the arrow points. DETAILED DESCRIPTION

[0045] The following detailed description describes embodiments of the present invention and how to implement it. Although some embodiments of the present invention have been disclosed, those skilled in the art will recognize that other embodiments can be implemented to implement or practice the invention.

[0046] Figure 1 This is a flowchart of a method for processing multiple database queries on a database query engine side provided by an embodiment of the present invention. Figure 1 , a flow chart of a method 100 for processing multiple database queries at a database query engine side is shown. The method 100 includes steps 102 to 112.

[0047] A method 100 for processing multiple database queries on a database query engine is provided. A database query refers to a request for information from a database through a database management system (DBMS). A database is a collection of data stored in an organized manner that can be accessed electronically. Examples of databases include, but are not limited to, hierarchical databases, network databases, object-oriented databases, relational databases, and NoSQL databases. A DBMS is a system for creating and managing databases containing large amounts of data. In one implementation, the DBMS is a massively parallel processing (MPP) database system, which processes the database using two or more processors or nodes. An MPP database system processes data related to one or more database queries in parallel. In one implementation, a database query engine is a processing module configured within the MPP database system that executes one or more database queries received from a requester by organizing and retrieving data from the database. In one example, a requester requests multiple database queries, i.e., a batch of queries from the database at once. Furthermore, based on the requests received from the requester, the database query engine retrieves relevant data from the database and provides the retrieved data to the requester in accordance with the requirements specified in the database query.

[0048] At step 102, method 100 includes receiving a plurality of database queries from one or more requesters into a database query engine. Furthermore, the database query engine processes data stored in the database based on conditions or predicates specified in the database queries. In one implementation, the requesters correspond to users or systems that require data stored in a database in a specific format. In another implementation, the database queries are in the form of a structured programming language (SQL), which is used for communication between the requesters and the database query engine to retrieve information from the database.

[0049] At step 104, method 100 includes parsing each of the plurality of database queries using a SQL parser module of a database query engine. In one example, the SQL parser module parses each database query from the plurality of database queries received from the requester. During the parsing process, the SQL parser module breaks each database query (in the form of an SQL statement) into a plurality of components or parts. Parsing the database queries facilitates reducing the processing time of the database query engine in executing the database queries.

[0050] In step 106, method 100 includes creating an abstract syntax tree for each database query in a plurality of database queries. That is, after parsing each database query from a plurality of database queries, an abstract syntax tree is created based on the corresponding parts. The abstract syntax tree is a tree representation of an abstract syntax structure in the form of a root, nodes, and leaves for each database query in a plurality of database queries. In one implementation, the abstract syntax tree is used by the SQL parser module to validate each database query (in the form of an SQL statement) and to support the creation of an executable query plan for processing the plurality of database queries. The abstract syntax tree contains clauses, expressions, and operators for each database query. In one implementation, the SQL parser module creates a separate abstract syntax tree for each database query in a plurality of database queries.

[0051] In step 108, method 100 further includes creating, using a logic planner module of the database query engine, a modified version of an abstract syntax tree for each of the multiple database queries. In other words, the logic planner module takes the abstract syntax tree as input to the SQL parser module and provides output as a modified version of the abstract syntax tree. In one implementation, the modified version of the abstract syntax tree includes rearranging the nodes of the abstract syntax tree to reflect the order in which the corresponding database queries will be executed, or adding new nodes to the abstract syntax tree to represent additional operations required to execute the queries. Furthermore, the logic planner module receives input from the batch query optimizer module to create a global query plan corresponding to the multiple database queries. A global query plan is a single execution plan that is common to all database queries received from the requester. In one example, a global query plan is a process by which each of the multiple database queries is efficiently executed. In one implementation, the global query plan is created incrementally as database queries arrive, which facilitates sharing multiple database queries across processors in an MPP database system and allows for efficient query optimization. In one implementation, the batch query optimizer module is configured with a DATAPATH system that generates a query plan based on a greedy algorithm to maximize the sharing of each database query, thereby making a suboptimal choice for the global query plan. The batch query optimizer module has the advantage of being stable in terms of the arrival order of database queries in multiple database queries and supporting data placement optimization. In one implementation, multiple database queries are received by a logic planner module, and the multiple database queries are integrated into the global query plan. In one example, if the number of multiple database queries integrated into the global query plan (i.e., the number of received queries) reaches a predefined size, the corresponding database query is considered for further execution. In such an embodiment, the multiple database queries received by the logic planner module are cached until the predefined size is reached, and the multiple database queries are reordered by the logic planner module and integrated one by one into the global query plan. According to one embodiment, the global query plan is created based on a cost-based optimization algorithm. A cost-based optimization algorithm is an algorithm that evaluates the cost of performing a specific join operation in each of the multiple database queries. In one implementation, each join in a database query is an SQL statement that combines data from two or more tables in a database. In one example, the cost of joining each database query refers to the amount of resources (e.g., time and memory) required to perform the corresponding join operation. In one implementation, the cost-based optimization algorithm operates on a shared cost model. In the shared cost model, the join operator is used across N queries (e.g., Q1, Q2, ..., and Q n) to execute multiple database queries. The cost of executing this batch of database queries is estimated by executing the union of N database queries:

[0052]

[0053] Here, σ represents a selection or filtering operator, which is used to retrieve data from the database based on the determined conditions. Here, the cost of processing multiple queries is reduced by sharing the execution of queries.

[0054] Furthermore, the batch query optimizer module reorders the queries in the multiple database queries using an overlap graph, formed by creating vertices for each database query in the multiple database queries and adding directed edges between each pair of vertices. In one implementation, the vertices of each database query are nodes in a graph representation of the database, with each table in the database represented by the node. In other implementations, directed edges represent relationships between vertices (or nodes). In this implementation, one or more database queries are reordered to expose more sharing opportunities between the multiple database queries. In one implementation, the multiple database queries are reordered to achieve an order that maximizes the number of common subexpressions. A common subexpression is a portion of a database query that is reused in multiple places across the multiple database queries. Furthermore, weights are assigned to the directed edges based on the degree of commonality or overlap between the data sources associated with the two queries corresponding to the vertex pair. In other words, if there is more common data between the data sources associated with the two database queries, weights are assigned proportionally to the extent of the common data. According to one embodiment, method 100 includes the step of running a topological sort on the overlap graph to derive a reordering of the queries in the multiple database queries. In one implementation, a topological sort is an algorithm that performs a linear ordering of the vertices of a database query. In this implementation, an overlap graph is a form of directed acyclic graph (DAG) that is used to represent the relationships between data in a database. For example, there are two database queries, Q i and Q j , where is the query Q i Create a vertex for query Q j Create another vertex. In addition, in Q i and Q j Add directed edges Qi–>Qj between the vertices of Qj. Topological sorting creates Qj. i and Q j The linear ordering of the vertices of Q i Located in Q jIn one implementation, the overlay graph is created based on an algorithm, wherein the first two lines of the algorithm create the overlay graph and the third line of the algorithm derives the order in which database queries are fed to the DATAPATH system.

[0055] In step 110, method 100 includes inputting a global query plan into a distributed planner module, an execution planner module, and a scheduler module, each of which is a module of the database query engine, to create an execution plan for executing the multiple database queries based on the global query plan. In one implementation, the global query plan created by the logic planner module is fed to the distributed planner module, which creates a distributed plan for the multiple database queries, which is also fed to the execution planner module to formulate an execution plan. The execution plan includes a set of operations to be performed by the database query engine to execute the multiple database queries. In one implementation, the distributed and execution planners are used as a single module to create an execution plan for the multiple database queries. In addition, the scheduler module creates a schedule regarding the time and order in which each of the multiple database queries is sent to one or more processors in the database query engine for further processing.

[0056] In step 112, method 100 further includes: executing multiple database queries using multiple shared operators according to the execution plan. A shared operator is an operator shared in a database query engine and is used to process multiple database queries at a time. In one example, the shared operator includes a shared join operator or a shared scan operator, which are used for multiple database queries at the same time. In addition, the shared operator performs operations on the data in the data source by performing operations on the data for multiple database queries, and the multiple database queries are all associated with the data in the data source. The shared operator performs shared execution of all database queries by retrieving data from the data source. In one implementation, the shared operators in method 100 include a first shared operator, a second shared operator, a third shared operator, and a fourth shared operator. According to one embodiment, the first shared operator is a shared scan operator. The shared scan operator performs a data scan from the data source to the relevant data for each database query in the multiple database queries. The shared scan operator reads the data in the data source sequentially.

[0057] According to another embodiment, the second sharing operator is a shared filter operator. The shared filter operator filters data present in the data source based on predicates included in each database query. In one implementation, the shared filter operator is based on a data query model that filters data within the data source and identifies data that satisfies the predicates or conditions specified in the query. In one embodiment, the shared filter operator examines each data tuple obtained from the data source via the shared scan operator and determines whether each data tuple satisfies the corresponding conditions of each query, and annotates each data tuple with an identifier of the corresponding query for which the data tuple satisfies the conditions. Data is stored in a tabular format (i.e., in rows and columns) in the data source. The data source includes multiple tables for storing data. A data tuple is a row contained in one of the tables stored in the data source. Furthermore, before applying the shared filter operator, the shared scan operator reads all data tuples in the data source to identify relevant data tuples. Furthermore, the shared filter operator examines each data tuple obtained via the shared scan operator and identifies data tuples from multiple database queries that satisfy the conditions of each query. During a filtering operation, a shared filter operator annotates each data tuple that satisfies a condition for the data tuple with an identifier of a corresponding database query. In one example, the identifier corresponds to identity information that is specific to each of the plurality of database queries. According to one embodiment, the shared filter operator uses a grouped by attribute filter to store predicates. In one example, an attribute is a column in any table stored in a data source. In one implementation, a grouped filter is applied to store predicates by first grouping rows of a table in a data source and applying a filter condition to each group of rows separately.

[0058] According to one embodiment, an intersection of multiple grouping filters is computed to obtain an output of the shared filter operator, wherein the output is a query set satisfied by a particular input tuple. In one example, the grouping filter processes all data tuples in a related table in a data source and annotates each data tuple with a corresponding query set, which includes the query set satisfied by each data tuple.

[0059] According to one embodiment, the third shared operator is a shared join operator that receives the output from each shared filter operation in a plurality of shared filter operators. Furthermore, each shared filter operator operates on a corresponding data source, which has been scanned by its respective shared scan operator. A shared join operator is a join operator that efficiently executes database queries involving multiple joins. The shared join operator joins tables in a data source for database queries involving multiple tables. The shared join operator is applied to the output query set received from the shared filter operator. Thereafter, the shared filter operator performs operations on the data sources (i.e., the related tables), and the shared scan operator scans these data sources. In this embodiment, each of the multiple database queries is passed through a shared scan operator. The shared scan operator scans the database to identify relevant data for each database query. Furthermore, the shared filter operator filters the data identified by the shared scan operator based on the predicates included in each database query. Furthermore, the shared filter operator identifies a relevant query set for each data tuple in the data source, and the shared join operator eliminates redundant tuples and executes the multiple database queries. The shared join operator also performs query set intersection on the corresponding joined tuples. Using a shared join operator is beneficial in that it can help reduce the amount of redundant work required for multiple similar database queries. According to one embodiment, the shared join operator supports query set processing semantics, wherein all pages in the build and probe sides of the join operator contain query set blocks. In one implementation, the build and probe sides of the shared join operator correspond to a build table and a probe table associated with the shared join operator. The shared join operator joins the build and probe tables to execute a database query.

[0060] According to one embodiment, the fourth shared operator is a router operator, which routes the output of the shared join operator to the requester that has submitted the corresponding query to the database query engine. The results of the database queries executed by the shared join operator are fed to the router operator. The router operator directs the results of each of the multiple database queries obtained from the shared join operator to the corresponding requester. In one implementation, the router operator ensures that each joined record obtained after the join operation is forwarded to the appropriate database query to execute the corresponding database query. The router operator is used to improve database query performance by reducing the number of rows that the shared join operator needs to process. According to one embodiment, the router operator sends tuples to multiple downstream operators. Downstream operators are operators that follow a source operator and process data generated by the source operator. According to this embodiment, at least one of the multiple downstream operators is a shared operator. Furthermore, the router operator acts as a source operator, sending the execution results obtained by the shared join operator to the downstream operators. The downstream operators facilitate splitting the processing of database queries into multiple stages, improving the performance of database query execution. Downstream operators can also be reused for different database queries, reducing the cost of processing database queries.

[0061] According to one embodiment, the router operator operates according to an operational model in which, for each subtree, there is a single producer, and the number of consumers equals the number of downstream operators. In one implementation, a consumer is an operator that consumes or processes data rows produced by downstream operators. In this implementation, a consumer receives data regarding database query results from downstream operators. In this implementation, a producer is an operator that produces a set of rows that are passed to one or more downstream operators. According to one embodiment, shared operator computations are precomputed and stored in a cache. The precomputed shared operators produce materialized intermediate results or views. In one implementation, the cache maintains the materialized intermediate results as well as information about the partitioning and placement of data within the database. The cache serves as a secondary source for storing data. The cache allows the DBMS to quickly retrieve data from the database during database query execution, which can improve query execution performance. Furthermore, the batch query optimizer module uses information in the cache to rewrite database queries more efficiently. By storing shared operator computations in the cache, subexpressions across different database queries can be reused, avoiding database query recalculation. In one implementation, a swap operator is used to shuffle data in the database during execution of the multiple database queries. Furthermore, during execution of the database queries, the database queries are divided into a number of blocks, referred to as partitions. The swap operator shuffles the data in the database to enable parallel execution across the multiple partitions.

[0062] Method 100 provides the advantages of processing a large number of queries simultaneously by an MPP database system through a batch query optimizer module, while eliminating the impact of the order of multiple database queries on query execution. Furthermore, the batch query optimizer module disclosed in method 100 is stable for a wide range of database queries and is capable of optimizing data placement. Furthermore, the batch query optimizer module disclosed in method 100 optimizes sharing and placement decisions for database queries, which improves the performance of the MPP database system. Furthermore, the batch query optimization disclosed in method 100 enables efficient recovery mechanisms in the context of shared operators by reusing common subexpressions across all database queries.

[0063] A computer program including instructions for performing all steps of method 100 is provided. The computer program is executed on a computer system. The computer program is implemented as an algorithm, embedded in software stored in a non-transitory computer-readable storage medium, the software having program instructions stored thereon, which can be executed by one or more processors in the computer system to perform method 100. The non-transitory computer-readable storage module may include, but is not limited to, for example, an electronic storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any appropriate combination of the above devices. Examples of implementations of the computer-readable storage medium include, but are not limited to, an electrically erasable programmable read-only memory (EEPROM), a random access memory (RAM), a read-only memory (ROM), a hard disk drive (HDD), a flash memory, a secure digital (SD) card, a solid-state drive (SSD), a computer-readable storage medium, and / or a CPU cache.

[0064] Figure 2 A block diagram of a system for processing multiple database queries on a database query engine side provided by an embodiment of the present invention. Figure 2 , a block diagram 200 of a system 202 for processing a plurality of database queries 208 on the side of a database query engine 204 is shown.

[0065] System 202 is configured to process a plurality of database queries 208 via a database query engine 204. System 202 receives a plurality of database queries 208 from one or more requesters 206, such as a first requester 206A, a second requester 206B, and finally an Nth requester 206N. In one example, requester 206 is a machine interface that converts database queries 208, which are requested in a human language, into structured query language (SQL) and sends the plurality of database queries 208 to system 202 for execution. Database query engine 204 is a processing unit that executes the plurality of database queries 208. Examples of database query engine 204 include, but are not limited to, an integrated circuit, a coprocessor, a microprocessor, a complex instruction set computing (CISC) processor, an application-specific integrated circuit (ASIC) processor, a very long instruction word (VLIW) processor, a central processing unit (CPU), a state machine, a data processing unit, or other processors or circuits. Furthermore, the database query engine 204 may refer to one or more separate processors, i.e., processing units, that are part of a blockchain machine.

[0066] Furthermore, the system 202 is configured to parse each database query in the plurality of database queries 208 using the SQL parser module 210 of the database query engine 204. The system 202 creates an abstract syntax tree for each database query in the plurality of database queries 208. Furthermore, the system 202 creates a modified version of the abstract syntax tree for each database query in the plurality of database queries 208 using the logic planner module 214 of the database query engine 204. Furthermore, the logic planner module 214 receives input from the batch query optimizer module 212 to create a global query plan corresponding to the plurality of database queries 208. During the creation of the global query plan, the database query engine 204 reorders the queries in the plurality of database queries 208 using an overlap graph formed by creating vertices for each database query in the plurality of database queries 208 using the batch query optimizer module 212 and adding directed edges between each pair of vertices. Furthermore, the system 202 assigns weights to the directed edges based on the degree of commonality or overlap between the data sources associated with the two database queries in the plurality of database queries 208 corresponding to the vertex pair.

[0067] Furthermore, system 202 inputs the global query plan into a distributed planner module 216, an execution planner module 218, and a scheduler module 220. Furthermore, distributed planner module 216, execution planner module 218, and scheduler module 220 are modules of database query engine 204. Furthermore, system 202 creates an execution plan for executing multiple database queries 208 based on the global query plan. Based on the execution plan, system 202 executes multiple database queries 208 according to the execution plan using multiple shared operators 222. Furthermore, shared operators 222 perform operations on data in a data source 224 by performing operations on the data for multiple database queries 208, each of which is associated with data in data source 224. In one implementation, data source 224 is a database from which a requester requests data via multiple database queries 208.

[0068] Figure 3 A block diagram of a database query engine provided by an embodiment of the present invention. Figure 2 The component description Figure 3 . refer to Figure 3 , a block diagram 300 of a database query engine 302 including a controller node 304 and an agent node 306 is shown.

[0069] In one implementation, controller node 304 and proxy node 306 are processing units configured in an MPP database system. Examples of controller node 304 and proxy node 306 may include, but are not limited to, integrated circuits, coprocessors, microprocessors, complex instruction set computing (CISC) processors, application-specific integrated circuit (ASIC) processors, very long instruction word (VLIW) processors, central processing units (CPUs), state machines, data processing units, and other processors or circuits. Furthermore, controller node 304 and proxy node 306 may refer to one or more separate processors, i.e., processing units that are part of a blockchain machine.

[0070] The controller node 304 includes a structured programming language (SQL) parser module 308, a logical planner module 310, a batch query optimizer module 312, a distributed planner module 314, an execution planner module 316, and a scheduler module 318. In addition, the proxy node 306 includes a plurality of processors, each of which includes a plurality of shared operators 322. In addition, the controller node 304 receives a plurality of database queries 208 ( Figure 2 In one implementation, the plurality of database queries 208 received by the controller node 304 are in the form of structured query language SQL. The plurality of database queries 208 are received by the controller node 304 via the SQL parser module 308. In operation, the database query engine 302 parses each database query in the plurality of database queries 208 using the SQL parser module 308. Furthermore, the database query engine 302 creates an abstract syntax tree 320 based on the parsing information provided by the SQL parser module 308. Based on the abstract syntax tree 320, the database query engine 302 uses the logic planner module 310 to create a modified version of the abstract syntax tree 320 for each database query in the plurality of database queries 208. Furthermore, the logic planner module 310 receives input from a batch query optimizer module 312 and metadata 322. Based on the input from the batch query optimizer module 312 and the metadata 322, the database query engine 302 creates a global query plan 324 corresponding to the plurality of database queries 208. In addition, the global query plan 324 is fed to the distributed planner module 314 to create a distributed plan 332, the execution planner module 316, and the scheduler module 318 to create an execution plan 326 for executing the plurality of database queries 208 according to the global query plan 324. In addition, the scheduler module 318 sends the execution plan for processing the plurality of database queries 208 to the proxy node 306. The proxy node 306 executes the plurality of database queries 208 according to the execution plan using a plurality of shared operators 328. The proxy node 306 operates on data from a data source 330 using the shared operators 328 to execute the plurality of database queries 208. The proxy node 306 also processes the data from the data source 330 to execute the plurality of database queries 208.

[0071] Figure 4A and Figure 4B Different figures describing various exemplary implementations of common connection templates with different filter predicates are provided for embodiments of the present invention. Figure 2 and Figure 3 The component description Figure 4A and Figure 4B . refer to Figure 4A, block diagrams 400A and 400B are shown that depict exemplary implementations of common connection templates with different filter predicates. Figure 4B , a block diagram 400A depicting another exemplary implementation of a common connection template with different filtering predicates is shown.

[0072] In one implementation, there are multiple database queries 208 ( Figure 2 ), for example, a first query Q1 (e.g., Figure 4A As shown) and the second query Q2 (as Figure 4B In addition, the data in the database of the first query Q1 includes two tables, such as the first table 402A and the second table 404A. Similarly, the data in the database of the second query Q1 includes two tables, such as the third table 402B and the fourth table 404B. The first table 402A and the third table 402B include network sales data (in Figure 4A and 4B The second table 404A and the fourth table 404B include customer data (shown as "wsales" in Figure 4A and 4B shown as “cust” in the example).

[0073] The first query Q1 includes two predicates: "quantity is greater than 10" and "status is equal to CA", and the second query Q2 includes two predicates: "quantity is less than 30" and "status is equal to WA". Here, query Q1 requires data from the first table 402A and the second table 404A. In addition, query Q2 requires data from the third table 402B and the fourth table 404B. In this example, the sharing operator includes the filter operator (σ) and the join operator Furthermore, during the execution of query Q1, the filter operator selects entries from the first table 402A that satisfy the predicate "quantity is greater than 10" and selects entries from the second table 404A that satisfy the predicate "status is equal to CA" (e.g., Figure 4A ). Furthermore, during execution of query Q2, the filter operator selects entries from third table 402B that satisfy the predicate "quantity is less than 30," and selects entries from fourth table 404B that satisfy the predicate "status is equal to WA." In this example, the share operator operates on the same tables, namely, first table 402A, second table 404A, third table 402B, and fourth table 404B, to satisfy the predicates of queries Q1 and Q2.

[0074] Figure 5 A diagram illustrating an exemplary implementation of shared execution in a select-project-join (SPJ) query provided by an embodiment of the present invention. Figure 2 、 Figure 3 、 Figure 4A and Figure 4B The component description Figure 5 . refer to Figure 5 , a block diagram 500 is shown, which describes an exemplary implementation of shared execution in a select-project-join (SPJ) query.

[0075] In one implementation, the shared operators include a shared scan operator 502, a shared filter operator (σ), a shared join operator and router operator 504. In addition, there are two tables in the database, a first table 506 and a second table 508. The first table 506 includes "Internet sales" data, and the second table 508 includes "consumer" data. During the execution of the database query, the shared scan operator 502 scans the first table 506 and the second table 508 to identify related data tuples from the first table 506 and the second table 508. In addition, the shared filter operator (σ) filters the data tuples scanned by the shared scan operator 502, using multiple database queries 208 ( Figure 2 The query set includes a list of data tuples from the first table 506 and the second table 508 and the corresponding database queries satisfied by the data tuples, such as Figure 5 In addition, the shared join operator The data tuples from the first table 506 and the second table 506 are joined, redundant data tuples are removed and a new query set is created. In addition, the router operator 504 directs the newly created query set to the appropriate database query of the corresponding requester.

[0076] Figure 6A and Figure 6B Different block diagrams are provided for describing various exemplary implementations of demonstrating the importance of query arrival order in public sharing opportunities for embodiments of the present invention. Figure 2 、 Figure 3 、 Figure 4A 、 Figure 4B and Figure 5 The component description Figure 6A and Figure 6B See also Figure 6A and Figure 6B , block diagrams 600A and 600B are shown that depict exemplary scenario implementations that demonstrate the importance of query arrival order in exposing sharing opportunities that include the operation of shared join operators for database queries received in different orders.

[0077] In one implementation, multiple database queries 208 ( Figure 2) includes four database queries, including two different arrival orders. For example, these four queries include a first query Q1, a second query Q2, a third query Q3, and a fourth query Q4. In addition, there are five tables in the database, such as a first table A, a second table B, a third table C, a fourth table D, and a fifth table E. The first query Q1 needs to connect the fourth table D and the fifth table E. In addition, the second query Q2 needs to connect the third table C, the fourth table D, and the fifth table E. In addition, the third query Q3 needs to connect the second table B, the third table C, the fourth table D, and the fifth table E. In addition, the fourth query Q4 needs to connect the first table A, the second table B, the third table C, the fourth table D, and the fifth table E.

[0078] In the first scenario, the database query is performed in the database query engine 204 ( Figure 2 The arrival order in ) is the first query Q1, then the second query Q2, then the third query Q3, and finally the fourth query Q4, as shown in Figure 6A As shown:

[0079]

[0080] First shared join operator Join the fourth table D and the fifth table E according to the first query Q1. In addition, the second query Q2 arrives, the second sharing operator Join the third table C with the fourth table D and the fifth table E; In addition, the third query Q3 arrives, the third shared join operator Join the second table B with the third table C, the fourth table D, and the fifth table E; In addition, the fourth query Q4 arrives, the fourth sharing operator Join the first table A with the second table B, the third table C, the fourth table D, and the fifth table E.

[0081] In the second scenario, the database query is performed in the database query engine 204 ( Figure 2 The arrival order in ) is the fourth query Q4, then the third query Q3, then the second query Q2, and finally the first query Q1, as shown in Figure 6A As shown:

[0082]

[0083] Shared join operators, such as the fifth shared join operator Sixth shared join operator Seventh shared join operator Eighth shared join operator Ninth shared join operator Tenth shared join operator Eleventh shared connection operator 12th shared connection operator Thirteenth shared connection operator and the fourteenth shared concatenation operator Operate on the database and execute database queries Q1, Q2, Q3 and Q4. In the above scenario, there are 5 tables and 4 queries, that is, N tables and N-1 queries. Figure 6A ) results in the use of 4 (i.e., N–1) shared join operators for 5 (i.e., N) tables. In addition, the second scenario (as Figure 6B The arrival order in the query results in the use of 10 (i.e., N(N–1) / 2) shared join operators. Therefore, the arrival order of database queries has an impact on the sharing opportunities (i.e., the more shared operators, the fewer sharing opportunities).

[0084] Figure 7A and Figure 7B Different block diagrams describing various exemplary implementations of the DATAPATH system in operating mode are provided for embodiments of the present invention. Figure 2 、 Figure 3 、 Figure 4A 、 Figure 4B 、 Figure 6A and Figure 6B The component description Figure 7A and Figure 7B . refer to Figure 7A and Figure 7B , block diagrams 700A and 700B are shown that depict an exemplary implementation of a system in an operational mode that includes changes in the global query plan due to the arrival of new database queries.

[0085] In this example, the database query engine 204 ( Figure 2 The global query plan 324 (created by Figure 3 ) receives a new database query that requires a join of four tables, such as "wsales", "cust", "item", and "cdemo", as shown in the following way:

[0086]

[0087] In the first scenario (such as Figure 7A ), the global query plan before inserting the new query is shown. The global query plan in the first scenario includes three shared join operators to connect four tables. Figure 7B ), the global query plan 324 before the new query is inserted is shown. In addition, after the new query is inserted, a single join operator is added to the existing shared join operators. When the new database query is added to the global query plan 324, the batch query optimizer module 212 ( Figure 2 ) employs a heuristic algorithm to search the space of join operators for the input database query and selects the best join order based on the join selectivity and join operators already present in the global query plan. According to the first and second scenarios, the batch query optimizer module 214 associated with the DATAPATH system adds only a single join operator for the new database query, even though three join operators are already present in the global query plan. In one implementation, a dynamic programming (DP) algorithm is used during the global plan construction process to prune candidate query plans.

[0088] Figure 8 A block diagram illustrating an exemplary implementation of shared execution in TPC-DS mode provided in an embodiment of the present invention. Figure 2 、 Figure 3 、 Figure 4A 、 Figure 4B 、 Figure 6A 、 Figure 6B 、 Figure 7A and Figure 7B The component description Figure 8 . refer to Figure 8 , a block diagram 800 is shown describing an exemplary implementation of shared execution in TPC-DS mode, which includes processing database queries involving common sub-expressions by a data-agnostic optimizer.

[0089] In this example, the database query engine 204 ( Figure 2 ) requests four database queries, for example, a first query Q1, a second query Q2, a third query Q3, and a fourth query Q4. In addition, in order to execute multiple database queries 208 ( Figure 2 ), such as Figure 8 As shown, by sharing the connection operator Join the store sales table (ss), the item table (i), the store table (s), and the dimension table (d). In this case, each database query shares a common subexpression, such as "ss join d." Furthermore, during the execution of the first query Q1, the second query Q2, the third query Q3, and the fourth query Q4, the data-agnostic optimizer generates a plan that maximizes sharing at the expense of expensive exchange operators.

[0090] Figure 9 A block diagram illustrating an exemplary implementation of the present invention that demonstrates the advantages of replication and partitioning relationships. Figure 2 、 Figure 3 、 Figure 4A 、 Figure 4B 、 Figure 6A 、 Figure 6B 、 Figure 7A 、 Figure 7B and Figure 8 The component description Figure 9 . refer to Figure 9 , a block diagram 900 depicting an exemplary implementation demonstrating the advantages of replication and partitioning relationships, including processing two copies of a table during shared execution of database queries by a batch processing query optimizer.

[0091] In such an example, the batch query optimizer module 212 ( Figure 2 ) shuffle multiple database queries 208 ( Figure 2 ), and join the "item" (i), "store" (s), "dimension" (d), and partitions of the "store sales" (ss) table by executing Figure 9 The shared join operation shown To execute database queries, such as the first query Q1, the second query Q2, the third query Q3, and the fourth query Q4. In one implementation, shared execution occurs between the first query Q1 and the second query Q2, and between the third query Q3 and the fourth query Q4. In one implementation, the store sales (ss) table has two copies, each of which is partitioned on a different attribute, such as "ss_item_sk" and "ss_store_sk". The batch query optimizer module 212 only shuffles the i, s, and d tables, and the join between the ss table partitioned by item and the ss table partitioned by store occurs locally. The batch query optimizer module 212 takes advantage of partitioning at the cost of limited sharing.

[0092] Figure 10 A block diagram of an embodiment of the architecture of a data placement aware optimizer provided in accordance with an embodiment of the present invention. Figure 2 、 Figure 3 、 Figure 4A 、 Figure 4B 、 Figure 6A 、 Figure 6B 、 Figure 7A 、 Figure 7B 、 Figure 8 and Figure 9 The component description Figure 10 . refer to Figure 10 , a block diagram 1000 is shown describing the architecture of a data placement-aware optimizer, the optimizer including a cache 1002 , a tuner 1004 , and an optimizer 1006 .

[0093] In one implementation, cache 1002 maintains materialized intermediate results obtained after executing a database query. Cache 1002 also contains information about data partitioning and placement in the database. The information stored in cache 1002 is sent to optimizer 1006, which rewrites the database query in an efficient manner. Optimizer 1006 (or Figure 2 The batch query optimizer module 212) replaces multiple database queries 208 ( Figure 2 In one implementation, the optimizer 1006 obtains information from the cache 1002, rewrites the database queries and integrates the multiple database queries 208 into the global query plan 324 ( Figure 3 ). Furthermore, the tuner 1004 uses the analysis information to speculatively optimize database queries and sends materialized information to the cache 1002. In one implementation, the tuner 1004 uses the shared cost model of the optimizer 1006 to predict the execution time of multiple database queries 208 when a particular sub-expression is materialized. The tuner 1004 calculates an estimated cost for each database query and adds the cost information to the cache 1002. In one implementation, the tuner 1004 is periodically called to generate candidate data sets for storage in the cache 1002.

[0094] Figure 11 A block diagram of an algorithm for selecting a materialized view provided in an embodiment of the present invention. Figure 2 、 Figure 3 、 Figure 4A 、 Figure 4B 、 Figure 6A 、 Figure 6B 、 Figure 7A 、 Figure 7B 、 Figure 8 、 Figure 9 and Figure 10 The component description Figure 10 . Reference Figure 11 , a block diagram 1100 describing an algorithm for selecting a materialized view including operations from 1102 to 1108 is shown.

[0095] The algorithm includes multiple operations for selecting materialized views. In operation 1102, the algorithm finds a suitable candidate plan from multiple sub-plans that are not covered by the materialized view. In operation 1104, the algorithm finds the candidate plan with the maximum benefit. Further, in operation 1106, the algorithm deletes the cached view with the minimum benefit until there is enough budget (if there is not enough space in the cache 1002). Figure 10 ). In operation 1108, the algorithm selects a materialized view to store in cache 1002. Furthermore, if there is sufficient free space in cache 1002, operation 1108 is performed immediately after operation 1104. The algorithm provides the advantage of being able to better select materialization, is sharing-aware, and improves runtime during processing of multiple database queries 208.

[0096] Without departing from the scope of the invention as defined by the appended claims, the embodiments of the present invention described above may be modified. Expressions such as "including", "comprising", "combining", "having", "being" used to describe and advocate the present invention should be considered to be interpreted in a non-exclusive manner, i.e., allowing items, parts or elements that are not clearly described to appear. References to the singular should also be interpreted as being related to the plural. The word "exemplary" used herein means "as an example, instance or illustration". Any embodiment described as "exemplary" is not necessarily interpreted as taking precedence over or being superior to other embodiments and / or not including features combined with other embodiments. The word "optionally" used herein means "provided in some embodiments and not provided in other embodiments". It should be understood that certain features of the present invention described in the context of a separate embodiment for the sake of clarity may also be provided in a single embodiment by combination. On the contrary, the various features of the present invention described in the context of a single embodiment for the sake of clarity may also be provided individually or by any suitable combination or as any other described embodiment of the present invention.

Claims

1. A method (100) for processing a plurality of database queries (208) at a database query engine (204, 302), characterized in that: The following steps are involved: receiving the plurality of database queries (208) from one or more requesters (206) into the database query engine (204, 302); parsing each of the plurality of database queries (208) using an SQL parser module (308) of the database query engine (204, 302); creating an abstract syntax tree (320) for each database query in the plurality of database queries (208); creating a modified version of the abstract syntax tree (320) for each of the plurality of database queries (208) using a logic planner module (214, 310) of the database query engine (204, 302), wherein the logic planner module (214, 310) receives input from a batch query optimizer module (212, 312); thereby creating a global query plan (324) corresponding to the plurality of database queries (208); wherein the batch query optimizer module (212, 312) reorders the queries in the plurality of database queries (208) using an overlap graph, the overlap graph being formed by creating vertices for each database query in the plurality of database queries (208) and adding directed edges between each pair of vertices, wherein the directed edges are assigned weights based on a degree of commonality or overlap between the data sources associated with the two queries corresponding to the vertex pair; inputting the global query plan (324) to a distributed planner module (216, 314), an execution planner module (218, 316), and a scheduler module (220, 318), each of which is a module of the database query engine (204, 302), to create an execution plan (326) for executing the plurality of database queries (208) based on the global query plan (324); The plurality of database queries (208) are executed according to the execution plan (326) using a plurality of shared operators (222, 328), wherein the shared operators perform operations on data in a data source (224, 330) by performing operations on the data for a plurality of queries, the plurality of queries being associated with the data in the data source (224, 330).

2. The method (100) according to claim 1, characterized in that The method (100) further comprises the step of running a topological sort on the overlay graph to derive a reordering of the queries in the plurality of database queries (208).

3. The method (100) according to claim 1, characterized in that The first shared operator is the shared scan operator.

4. The method (100) according to claim 3, characterized in that The second shared operator is a shared filter operator, wherein the shared filter operator receives data from a data source (224, 330) that has been scanned by the shared scan operator.

5. The method (100) according to claim 4, characterized in that The shared filter operator examines each data tuple obtained from the data source (224, 330) by the shared scan operator and determines whether each data tuple satisfies a corresponding condition of each query, and annotates each data tuple with an identifier of the corresponding query whose condition the data tuple satisfies.

6. The method (100) according to claim 5, characterized in that The third shared operator is a shared join operator that receives an output from each of the plurality of shared filter operators, each shared filter operator operating on a corresponding data source scanned by a corresponding shared scan operator.

7. The method (100) according to claim 6, characterized in that A fourth shared operator is a router operator that routes the output of the shared join operator to a requester that has sent the corresponding query to the database query engine (204, 302).

8. The method (100) according to claim 7, characterized in that The router operator sends tuples to multiple downstream operators.

9. The method (100) according to claim 8, characterized in that At least one downstream operator of the plurality of downstream operators is a shared operator.

10. The method (100) according to claim 7, characterized in that The router operator operates according to an operation model where, for each subtree, there is a single producer and the number of consumers is equal to the number of downstream operators.

11. The method (100) according to claim 6, characterized in that The shared join operator supports query set processing semantics, where all pages in the build and probe sides of the join operator contain query set blocks.

12. The method (100) according to claim 1, characterized in that The global query plan (324) is created according to a cost-based optimization algorithm.

13. The method (100) according to claim 1, characterized in that Shared operator calculations are precomputed and stored in cache.

14. The method (100) according to claim 4, characterized in that The shared filter operator uses filters grouped by attributes to store predicates.

15. The method (100) according to claim 14, characterized in that An intersection of multiple grouping filters is computed to obtain an output of the shared filter operator, the output being the set of queries satisfied by the particular input tuple.

16. A system (202), characterized in that Comprising modules adapted to perform all the steps of the method (100) according to any one of the preceding claims.

17. A computer program, characterized in that Comprising instructions for executing all the steps of the method (100) according to any one of the preceding claims on a computer system.

Images