Query system, method executed by the query system, and computer-readable medium

Abstract

Generally, the present disclosure describes techniques for query systems, methods performed by query systems, and computer-readable media. For example, a query system includes an interface configured to receive a query for accessing first data stored in a plurality of data sources. The query system includes a plurality of connector interfaces for interfacing with the plurality of data sources. The query system further includes a compiler configured to compile the query to generate a compiled query that includes one or more instructions for obtaining, when the compiled query is executed, one or more metrics for performing portions of the compiled query at one or more data sources. The query system further includes an optimizer configured to optimize a query plan for a subsequent query based on the one or more metrics in response to the interface receiving the subsequent query for accessing second data stored in the plurality of data sources.

Description

[0001] This application claims the benefit of U.S. Provisional Patent Application No. 62 / 928,108, filed on October 30, 2019, the entire content of which is incorporated herein by reference. Technical Field

[0002] The present disclosure relates to a computing system, and more specifically, to optimizing queries in a computing system. Background Art

[0003] Data is stored in repositories such as databases. Data is stored in different types of databases (such as relational databases, non-relational databases, stream databases, etc.). For example, a relational database stores data as rows and columns in a series of related tables. In contrast, a non-relational database stores data based on models other than tabular relationships. For example, non-relational databases can include document databases, key-value stores, graph stores, and column stores.

[0004] Queries are used to access (e.g., write to and retrieve) data stored in a database. Depending on the type of database, different query languages are used to access the data stored in the database. For example, a user can use a relational database query (e.g., Structured Query Language (SQL)) to query a relational database. A relational database query can return rows of a relational database. Alternatively, a user can use a non-relational database query (e.g., NoSQL) to query a non-relational database (such as a document database). A non-relational database query can return, for example, a document containing data (such as a JavaScript Object Notation (JSON) or Extensible Markup Language (XML) document). Other data repositories include data lakes, static web pages, data streams, files stored to a file system, etc. Summary of the Invention

[0005] Generally, the present disclosure describes techniques for dynamically optimizing queries. For example, a query system can access multiple heterogeneous data sources that store data required to satisfy a query received by the query system. The query system includes a common interface for receiving queries and adaptively pushes down queries to retrieve data from the heterogeneous data sources. The query system can dynamically measure or otherwise obtain one or more metrics based on the attributes of the query or the execution of the query at different data sources. Using these metrics, the query system can optimize the current or subsequent queries. For example, a query compiler of the query system can use Just-In-Time (JIT) or other compilers to compile the current or subsequent query into bytecode, which can optimize the current or subsequent query based on the attributes of the query or the latest metrics of the query for execution at various data sources, thereby dynamically adapting query compilation to, for example, dynamic resource or network conditions that affect query execution or data retrieval from data sources. In this way, in some examples of the technology, at least in part because the compilation of the query occurs just before execution, the query system can perform JIT compilation that is optimized for the query or data source based on the attributes of the query or metrics indicating the performance of previous queries at multiple data sources.

[0006] In some examples, an optimizer of the query system can instruct the compiler to add instrumentation code (e.g., counters) when compiling a query to obtain one or more metrics when the compiled query is executed. The metrics can include the size of the operands (e.g., the number of rows returned from the query), the selectivity of the predicates, the cardinality from tables, or other metrics generated by the execution of the compiled query. In response to receiving a subsequent query, the optimizer can cause the query system to optimize the subsequent query based on the metrics of the previously executed query.

[0007] The techniques described herein can provide one or more technical advantages that provide at least one practical application. For example, the techniques described in the present disclosure mix the use of coarser hash lookup techniques to produce an effect ranging from traditional cost-based optimization (when high-confidence statistics are available or have been obtained) to a low-confidence "hunch" that is more or less based on an inspection of the query itself and the application of some general rules through a "softer" learning model (based on metrics obtained from past similar queries).

[0008] In one example, the techniques described herein include a method performed by a query system, the method comprising: receiving a query for accessing first data stored in a plurality of data sources; compiling the query to generate a compiled query comprising one or more instructions for obtaining one or more metrics when the compiled query is executed, the one or more metrics for performing portions of the compiled query at one or more of the data sources; receiving a subsequent query for accessing second data stored in the plurality of data sources; and optimizing a query plan for the subsequent query based on the one or more metrics.

[0009] In another example, the techniques described herein include a query system comprising: an interface configured to receive a query for accessing first data stored in a plurality of data sources; a plurality of connector interfaces for interfacing with the plurality of data sources; a compiler configured to compile the query to generate a compiled query comprising one or more instructions for obtaining one or more metrics when the compiled query is executed, the one or more metrics for performing portions of the compiled query at one or more of the data sources; and an optimizer configured to optimize a query plan for a subsequent query based on the one or more metrics in response to the interface receiving the subsequent query for accessing second data stored in the plurality of data sources.

[0010] In another example, the techniques described herein include a non-transitory computer-readable medium comprising instructions for causing one or more programmable processors to: receive a query for accessing first data stored in a plurality of data sources; compile the query to generate a compiled query comprising one or more instructions for obtaining one or more metrics when the compiled query is executed, the one or more metrics for performing portions of the compiled query at one or more of the data sources; receive a subsequent query for accessing second data stored in the plurality of data sources; and optimize a query plan for the subsequent query based on the one or more metrics.

[0011] For example, these techniques can enable a query system to optimize queries using up-to-date metrics for query execution at various data sources accessed by the query system. Dynamically adjusting query compilation to obtain information (such as, by using the obtained information to intelligently apply or arrange joins or other operations and / or aspects of query execution among data sources) from a running query (or from assumptions based on the query itself or data source properties), can improve the performance of the query system. As another example, by adding instrumentation code to a query to obtain one or more metrics of the query, the technique can dynamically obtain metrics when executing the query, without relying on histograms or additional queries that send requests for metrics of the database. This can facilitate the query system to access up-to-date metrics for query execution, can provide access to metrics that are not available when using traditional database or other data source metric requests, can reduce the processing burden on the query system and / or the data source, and / or can reduce communication traffic in the network.

[0012] Details of one or more examples are set forth in the accompanying drawings and the following description. Other features, objects, and advantages will be apparent from the specification, drawings, and claims. BRIEF DESCRIPTION OF THE DRAWINGS

[0013] Figure 1 is a block diagram of an example computing system configured to dynamically optimize queries to multiple data sources, which illustrates one or more aspects of the disclosed technology;

[0014] Figure 2 is a more detailed block diagram of a query system in accordance with one or more aspects of the disclosed technology;

[0015] Figures 3A to 3C is a conceptual diagram of an example optimization operation in accordance with one or more aspects of the technology in the present disclosure;

[0016] Figure 4 is a flowchart showing the operation of a query system configured to dynamically optimize queries in accordance with one or more aspects of the disclosed technology;

[0017] Figure 5 is a block diagram of an example computing device operating in accordance with one or more techniques of the present disclosure. DETAILED DESCRIPTION

[0018] Figure 1FIG. 2 is a block diagram of an example computing system 2 in accordance with one or more aspects of the disclosed technology, the example computing system being configured to dynamically optimize queries to a plurality of data sources. In this example, the computing system 2 includes a query system 10 that is configured to provide unified access to data sources 8A - 8N (collectively "data sources 8"). In some examples, the data sources 8 may represent heterogeneous data sources. As further described below, in some examples, the query system 10 is implemented by a plurality of distributed nodes.

[0019] The data sources 8 may include relational databases, non-relational databases, data streams, file systems such as Hadoop Distributed File System (HDFS), data lakes, web pages, or any other type of repository or data source accessible by the query system 10 that satisfies a query. A relational database may store data as rows and columns in a series of related tables. In contrast, a non-relational database stores data based on models other than tabular relationships. For example, non-relational databases may include document databases, key-value stores, graph stores, and column stores. In some examples, the data sources 8 are distributed. In some examples, one or more of the data sources 8 may be external to the query system 10. In some examples, one or more of the data sources 8 may be internal to the query system 10, such as an in-memory data store that stores data in a memory shared with other components of the query system 10.

[0020] The query system 10 can adjust queries that run against heterogeneous data sources 8. The query system 10 can restructure one or more queries from one or more users (e.g., user 4) to access data stored in multiple heterogeneous data sources 8. For example, the query can be in a neutral dialect (such as, Structured Query Language (SQL)). As an example, in response to receiving a query from user 4, the query system 10 can determine one or more types of data to access and process and where that data is stored. For example, the query system 10 can determine to query data stored in a relational database (e.g., data source 8A) and a non-relational database (e.g., data source 8B). The query system 10 can compile the query (or a portion of the query) into corresponding subqueries for the query to access data from a corresponding one of the data sources 8. For example, to extract data stored in the relational database 8A, the query system 10 can compile the query (or a portion of the query) to generate a relational database query (a subquery of the query) to retrieve data (e.g., rows of a table) from the relational database 8A. For example, the relational database query can include an SQL query. The query system 10 can also compile the query (or a portion of the query) into a non-relational database query (a subquery of the query) to retrieve data from the non-relational database 8B (e.g., a document such as a JavaScript Object Notation (JSON) or Extensible Markup Language (XML) document). For example, the non-relational database query can include a NoSQL query.

[0021] Generally, to compile a received query, the query system 10 generates a query plan (or "execution plan") as a set of steps or tasks for obtaining and processing data stored in the data sources 8. The query plan includes subqueries for accessing one or more of the data sources 8. The query system 10 can distribute the subqueries generated for the query in parallel to multiple data sources 8. Each of the data sources 8 that receives a subquery accesses and / or processes its corresponding stored data to satisfy the subquery and returns the subquery result data. The query system 10 can receive the subquery result data returned individually from one or more of the data sources 8 and integrate the data for user 4. The query system 10 can perform further query processing operations on the individual subquery result data returned from one or more of the data sources 8. For example, the query system 10 can perform a join or other operation on the tables of the subquery result data. As other examples, the query system 10 can aggregate, modify, analyze, compute, or otherwise process the returned subquery result data to generate an overall query result in response to the query.

[0022] According to the techniques described herein, the query system 10 includes an optimizer 14 to dynamically optimize queries. In Figure 1In the example shown, the query system 10 can receive, from a user 4, a data query 20 for accessing data stored in one or more data sources 8. In response to receiving query 20 from user 4, the query system 10 can determine one or more types of data to access and process and the location where the data is stored. For example, the query system 10 can determine that query 20 requests data stored in data sources 8B and 8N. To extract the data stored in data sources 8B and 8N, the query system 10 can compile query 20 to generate a compiled query for accessing one or more data sources 8. More specifically, the query system 10 can compile query 20 to generate a query plan for obtaining and processing the data stored in data sources 8. The query plan for query 20 includes subqueries 22A - 22B (collectively referred to as "subqueries 22") for accessing one or more of data sources 8. In some examples, the query system 10 can dynamically measure or otherwise obtain one or more metrics based on the execution of subqueries 22 at data sources 8B and 8N. For example, the optimizer 14 can cause the query system 10 to add instrumentation code when compiling query 20 to obtain one or more metrics when executing the compiled query (e.g., subqueries 22).

[0023] Metrics can include the size of operands, the selectivity of predicates, the cardinality of tables or columns, performance and execution metrics (such as query latency or CPU usage), and other metrics generated by executing the query.

[0024] The size of an operand can include the extraction size of a query or subquery. As an example, the optimizer 14 can cause the query system 10 to add a counter to a relational database query to count each row of a table returned from the relational database. In some examples, the optimizer 14 can cause the query system 10 to add a counter to a query to count each element array or nested array in a document stored in a non - relational database. In the above examples, in response to an indication from the optimizer 14, the query system 10 can dynamically generate query execution code that includes instructions and data for implementing such a counter.

[0025] In some examples, a metric can include information about the selectivity of a predicate. A predicate can define the conditions under which a row applies to a table. The selectivity of a predicate is the subset of the data set identified by the predicate. For example, the optimizer 14 can instruct the query system 10 to add a counter to a query to count each row returned based on a predicate (e.g., customers under 21 years old from a data set of all - age customers). In the above example, in response to an indication from the optimizer 14, the query system 10 can update or generate query execution code that includes instructions and data for implementing such a counter to obtain information about the selectivity of the predicate.

[0026] The cardinality of a table is the uniqueness of the data contained in the columns of the table. To measure the cardinality of a table, the optimizer 14 can, for example, instruct the query system 10 to add a counter to a query to count each duplicate entry in a column of the table (e.g., customers in a particular state). The number of duplicate entries can represent the degree of cardinality. For example, the more duplicate entries in a column of a table, the lower the cardinality, and the fewer duplicate entries in a column of a table, the higher the cardinality. In the above example, in response to an indication from the optimizer 14, the query system 10 can update or generate query execution code that includes instructions and data for implementing such a counter to obtain information about the above metric.

[0027] The query system 10 receives the returned subquery result data 24A-24B (collectively "result data 24"), which includes one or more metrics. The obtained metrics are stored in the metric repository 16 ("metric 16"), which can include internal memory, external memory, local or remote storage devices. In some examples, the metric repository 16 is a distributed information repository. The query system 10 integrates the result data 24 (e.g., integrated result data 26) for the user 4.

[0028] When the query system 10 receives a subsequent query 28 from the user 4 for accessing data stored in one or more data sources 8, the query system 10 can optimize the subsequent query based on one or more metrics dynamically obtained from the execution of the subquery 22. To optimize the subsequent query 28, the query system 10 can compile the subsequent query 28 to generate a query plan for the subsequent query 28 that is different from the query plan that the query system 10 would generate without the metrics. For example, based on the metrics of the data sources 8B and / or 8N obtained dynamically, the query system 10 can: (1) select a particular data source 8 for a data set; (2) determine the type or ordering of tasks within a subquery sent to the data source 8; (3) determine one or more tasks of the query to be processed by the query system 10 rather than the data source 8; (4) determine the task ordering of a query or subquery (such as a join operation); (5) determine whether to compress the operators of a query or subquery; (6) or other determinations that affect the query plan and the compiled query generated by the query system 10 (such as selectively determining whether to push join operands to the data source).

[0029] As an example (and as relative to Figures 3A - 3CAs further described, the optimizer 14 can cause the query system 10 to reorder the join operands of the query plan based on metrics obtained from the data source 8 as learned from the execution of the subquery 22. For example, the optimizer 14 can reorder the join operands of the query plan for a subsequent query 28 to first process the join operands for the table with the smallest table size (e.g., fewest number of rows), and then process the join operands for the table with the largest table size (e.g., highest number of rows).

[0030] In this way, by adjusting query compilation to obtain information from running queries (or from assumptions based on the query itself or data source properties), and by using the information obtained to intelligently apply or set joins or other operations and / or aspects of query execution between data sources, the performance of the query system can be improved. As another example, by adding instrumentation code to a query to obtain one or more metrics for that query, the technique can dynamically obtain metrics when the query is executed, without relying on histograms or additional queries that request metrics from the database. This can help the query system access up-to-date metrics for query execution, can provide access to metrics that are not available when using traditional database or other data source metric requests, can reduce the processing burden on the query system and / or data source, and / or can reduce communication traffic in the network.

[0031] Figure 2 is in accordance with one or more aspects of the disclosed technology Figure 1 more detailed block diagram of the query system 10. In Figure 2 the example, the query system 200 can represent Figure 1 an example implementation of the query system 10. The query system 200 can include a client application programming interface (API) 202, nodes 204A - 204N (collectively referred to as "nodes 204"), connector interfaces 206A - 206N (collectively referred to as "connector interfaces 206"), an execution engine 210, a compiler 212, an optimizer 214, and a metric repository 216. The query system 200 can provide access to data sources 208A - 208N (collectively referred to as "data sources 208"). In Figure 2 the example, the data sources 208 can be heterogeneous data sources. Each of the connector interfaces 206, the execution engine 210, the compiler 212, and the optimizer 214 can represent software having machine-readable instructions executable by a processing circuit.

[0032] The query system 200 includes a cluster of nodes (e.g., node 204) coupled to the query system 200. In some examples, node 204 is part of a distributed network. A user can access node 204 via the client application programming interface 202 to send a query to node 204 of the query system 200 to access data stored in one or more of the data sources 208. In some examples, the client application programming interface 202 can include a web-based management console, a command line interface (CLI), or other programming interfaces, client APIs, graphical user interfaces, and / or native interfaces built using Java, Scala, C++, Python, or other languages. In some examples, node 204 can implement a decentralized peer-to-peer network protocol (e.g., wired) for a user to send a query to the query system 200. Examples of programming interfaces can include Java Database Connectivity (JDBC), Java Fluent API, Stateless HTTP (SQL), Stateless HTTP (Component), etc.

[0033] As an example of implementing JDBC in the client application programming interface 202, the query received by the client application programming interface 202 is associated with a previously constructed session and user. The client application programming interface 202 can parse the SQL text to create a parse tree structure. Traverse the parse tree to generate a table name preview for the connector interface. Traverse the parse tree to generate an operator graph using the table operators provided by the connector interface.

[0034] As another example of implementing the Java Fluent API in the client application programming interface 202, fluent Java code assembles a query component tree that contains a mix of text or abstract specification elements and executable Java elements. Traverse the query component tree to generate a table name preview for the connector interface. Traverse the query component tree to generate an operator graph using the table operators provided by the connector interface.

[0035] In yet another example of implementing Stateless HTTP (SQL) in the client application programming interface 202, session and user authentication are provided on each query. Parse the SQL text to create a parse tree structure. Traverse the parse tree to generate a table name preview for the connector interface. Traverse the parse tree to generate an operator graph using the table operators supplied by the connector interface.

[0036] As another example of implementing Stateless HTTP (Component) in the client application programming interface 202, session and user authentication are provided on each query. Parse the XML text to create a parse tree structure. Traverse the parse tree to generate a table name preview for the connector interface. Traverse the parse tree to generate an operator graph using the table operators supplied by the connector interface.

[0037] In some examples, one or more nodes (e.g., node 204A), referred to as a distributed query coordinator (DQC), may receive a query via the client application programming interface 202. Other nodes (e.g., nodes 204B - 204N), referred to as distributed query executors (DQE), may perform query execution and are used to connect to the data source 208 via the connector interface 206. Although the above example describes node 204A operating as a DQC and nodes 204B - 204N operating as DQEs, each node may operate as a DQC, a DQE, or both.

[0038] The query system 200 may include a compiler 212 that compiles an input query to build a query plan for the input query. The compiler 212 may include a just-in-time (JIT) compiler that compiles the input query into bytecode. The query plan may include an ordered set of steps for completing the query. That is, the steps describe the data source operations taken to create the query result. In some examples, the query plan is represented as an abstract graph (such as a tree structure). The abstract graph may include nodes representing operands (e.g., extraction operands) to access data in one or more of the data sources 208. In some examples, the query plan may use an object-oriented design in which leaf nodes (e.g., extraction operators) are provided by the connector interface 206 associated with the corresponding data source 208.

[0039] As an example, the query system 200 may receive a query for accessing data stored in a relational database (e.g., data source 208A) and a non-relational database (e.g., data source 208B). The compiler 212 compiles the query and builds a query plan to leverage the native capabilities of the relational database 208A and the non-relational database 208B. In this example, the compiler 212 may build a query plan (such as an abstract graph) in which a first node represents an operand to access data stored in the relational database 208A and a second node represents an operand to access data stored in the non-relational database 208B. More specifically, the compiler 212 may restructure the query (e.g., a neutral dialect query) into subqueries (such as a relational database query (e.g., SQL) including an operand to access data stored in the relational database 208A and a non-relational database query (e.g., NoSQL) including an operand to access data stored in the non-relational database 208B).

[0040] The execution engine 210 of the query system 200 may execute a query plan generated by the compiler 212. For example, the execution engine 210 may push down portions of the query plan that can be directly executed by the data source 208. Continuing with the above example, the execution engine 210 may push down a relational database query to be directly executed by the relational database 208A and may push down a non-relational database query to be directly executed by the non-relational database 208B. The execution engine 210 may also execute the remaining portion of the query plan by pulling up data required to complete the execution of the query from the underlying data source 208. For example, the execution engine 210 may receive separate subquery result data returned from the data sources 208A and 208B and integrate the data for the user.

[0041] The execution engine 210 may use multiple connector interfaces 206 to access the underlying data source 208, each of the connector interfaces 206 providing an interface to a corresponding one of the data sources 208. The connector interface 206 is responsible for executing portions of the query plan (e.g., subqueries) generated by the optimizer 214 and passing the subquery result data returned from the data source 208 to the execution engine 210. In some examples, the connector interface 206 may provide extraction operators (e.g., as nodes of the query plan) to the compiler 212 for use in generating the query plan to access the data source connected to the connector interface 206.

[0042] In some examples, the connector interface 206 may provide metrics about tables, and the connector interface 206 may access the compiler 212 for the table when the query plan is generated. For example, when the query plan is generated, the compiler 212 may identify the tables requested by the query and survey the connector interface 206 to determine whether the connector interface 206 can access the identified tables. In these examples, the connector interface 206 may provide metadata of the data structure in its underlying data source (e.g., granularity information of the table such as row count, or general information such as large text files) for use in generating the query plan. In these examples, the connector interface 206 may include a cache or other memory to store the metadata.

[0043] In some examples, the connector interface 206 may provide a standard American National Standards Institute (ANSI) 3-part naming interface (e.g., catalog.schema.table) for the data managed by the database. For example, a relational database (e.g., the data source 208A) may support the ANSI 3-part naming interface, and the connector interface 206A may use the 3-part name to identify each table. In some examples where one or more of the data sources 208 do not support the ANSI 3-part naming interface, the connector interface 206 may provide the missing portions of the ANSI 3-part name.

[0044] As an example, data source 208A can be, for example, a Postgres database (e.g., catalog) named "Sales" (e.g., schema) and including tables named "Customers", "Items", and "Orders". The query plan can include the following operands:

[0045] Select * from Postgres.Sales.Customers

[0046] Connector interface 206A can pass through <postgres> . <sales> . <customers>Identify the table used for querying customer information by the three - part name, and receive the data extracted from the identified table. In some examples, some data sources (e.g., data sources for streaming data) may not support the ANSI three - part naming interface. In this example, the connector interface 206C can provide the missing parts of the ANSI three - part name (such as the catalog and schema names).

[0047] According to the disclosed technology, the query system 200 can include an optimizer 214 for dynamically optimizing queries for multiple data sources 208. For example, the optimizer 214 can cause the query system 200 to dynamically obtain one or more metrics of a compiled query when executing the compiled query, and optimize subsequent queries based on the dynamically obtained metrics. Although the optimizer 214 is shown as a separate module, the optimizer 214 can be part of any other module (such as the compiler 212), or implemented in a distributed environment (such as implemented by the nodes 204).

[0048] In Figure 2 the example, the optimizer 214 can cause the compiler 212 to add instrumentation code when compiling a query to obtain one or more metrics when executing the compiled query. For example, to obtain metrics about the size of the operands and / or the selectivity of the predicates generated by the execution of a query, the optimizer 214 can instruct the compiler 212 to add a counter to the sub - query (e.g., relational database query) code to count each row returned from the relational database (or count each row returned based on the predicate). Alternatively or additionally, the optimizer 214 can cause the compiler 212 to add a counter to the sub - query (e.g., non - relational database query) code to count array elements or nested arrays in a document such as a JSON document. In other examples, to obtain a metric about the cardinality of a column of a table, the optimizer 214 can cause the compiler 212 to add instrumentation code to estimate the number of distinct values of the column.

[0049] The execution engine 210 can process the obtained metrics, for example, tabulate the counts based on the execution of the compiled query. The obtained metrics can be stored in the metric repository 216, which can include internal memory, external memory, local or remote storage devices (as shown by the internal memory in Figure 2 ). In some examples, the metric repository 16 is a distributed information repository.

[0050] When the query system 200 receives a subsequent query, the optimizer 214 may cause the query system 200 to optimize the query plan for the subsequent query based on the obtained metrics. For example, the optimizer 214 may request metrics (e.g., row count information) stored in the metric repository 216 and determine how to optimize the subsequent query. As an example, the optimizer 214 may apply decision and / or declarative factors, heuristic models, machine learning models, and / or feature learning models to the obtained metrics to determine how to optimize the query plan. Decision factors may include operator cardinality, column data type, column uniqueness, selectivity of predicates, join density, plan enumeration benefits, bloom filter benefits, distribution benefits, replication benefits, repartitioning benefits, connector pushdown benefits, cache benefits, function cost, evaluation order, likelihood of generated class reuse or JIT de-optimization, index strategies (e.g., hash vs. tree, hash table format, hash function), transaction scope, transaction size or duration, transaction priority, etc. Declarative factors may include primary key or foreign key relationships, unique declarations, declared indexes, query hints, etc. In some examples, the optimizer 214 may apply heuristic (also referred to herein as "rules of thumb") models, machine learning models, and / or feature learning models. Heuristic models may include consistent approximations, polynomial approximations, and / or other heuristic models. Machine learning models may include seed learning, dynamic learning, decision trees, naive Bayes classifiers, K-nearest neighbors (KNN), support vector machines (SVM), random forests, or other machine learning models. Feature learning models use features (e.g., variables) to build predictive models. Features may include distribution profiles, workload profiles, sessions or users, tables, column operators, predicate expressions, projection expressions, and other attributes of the query.

[0051] In some examples, the metric repository 216 includes one or more operator attributes. The attributes include context attributes, table attributes, column contribution attributes, and predicate summaries. For example, context attributes can include the type of API used for the query (e.g., JDBC, network, SQL, Java, etc.), the user who initiated the query, the time the query was executed, and / or the workload profile (e.g., CPU load, memory usage, active users, active query count, etc.). Table attributes can include attributes of tables or streams that appear in a subgraph of a diagram (e.g., a query plan). For example, the attribute is formed by the system global "feature-ID" of the table or stream and any explicit query hints applied. Column contribution attributes can include attributes of columns used in the subgraph, such as whether a column is declared as a primary key or a foreign key, whether the column is declared unique, whether the column is declared as an index column, whether the column is a projection expression (e.g., the column appears directly in the output of the query), whether the column contributes to a projection expression, whether the column is used in a join predicate in the subgraph, whether the column is used in a grouping in the subgraph, whether the column is used in a sorting in the subgraph, and / or whether the column is named but not used. Predicate summaries can include, in a simplified "summary" form, attributes of predicates in the subgraph, where the attributes of the predicate indicate the shape of the predicate and the columns to which the predicate applies. Magnitude comparisons (e.g., less than, greater than) are normalized to be inclusive (e.g., less than or equal to, greater than or equal to). Comparisons between columns are normalized with the column with the lower number as the primary column. Comparisons with complex expressions or between complex expressions are recorded as "complex", or, if the predicate involves a user-defined function (UDF), the predicate is recorded as "complex with UDF". Equality between columns with similar names (e.g., variants based on the Levenshtein distance) is marked as a possible hint of a key relationship. The attributes of the predicate can also include equality with another column, equality with another column with name similarity, less than another column, greater than another column, equal to a constant, less than a constant, greater than a constant, range (represented as a base value and a range), "like" or "regex" summary, other or complex predicate, and / or other or complex predicate with UDF.

[0052] As described above, the optimizer 214 can cause the query system 200 to optimize the current query plan or the query plan for a subsequent query based on the metrics obtained. As an example, the optimizer 214 can perform predicate pushdown and constraint propagation. For example, whenever possible, query predicates and filter operators are repositioned closer to the table operators in the query plan (e.g., the diagram). New predicates are inferred and propagated through the query plan.

[0053] As another example, the optimizer 214 can reorder multiple join operands in the current query plan or the query plan of a subsequent query such that the join operand with the smallest table is joined first and the join operand with the largest table is joined last. For example, the metrics repository 216 can store the row counts of the tables obtained when executing previous queries. In other examples, the optimizer 214 can convert outer joins to inner joins whenever possible. Using these metrics, the optimizer 214 knows the sizes of the tables and can reorder or convert the join operands of the query plan.

[0054] In another example, the optimizer 214 can provide subquery de-correlation and de-nesting. For example, the optimizer 214 can convert a nested subquery into a stand-alone query and then re-attach the stand-alone query to the main query via a join operator.

[0055] As yet another example, the optimizer 214 can push down a limit operator. For example, the optimizer 214 can relocate a row limit operator downward (e.g., closer to a table operator) towards the lowest table operator in the query plan that can limit rows.

[0056] In yet another example, the optimizer 214 can perform push-down sorting on a row sorting operator. For example, the optimizer 214 can relocate a row sorting operator downward towards the point that will perform any required sorting operations on the smallest possible input.

[0057] As another example, the optimizer 214 can cause the query system 200 to optimize the ordering of operators. For example, the optimizer 214 can generate hypotheses by fitting operators or query plans to models generated from metrics measured in previous queries (or statistics provided by the connector interface) or pre-loaded heuristic models. These hypotheses can be used to build sort priorities and / or control the degree of plan enumeration. That is, the optimizer 214 can identify patterns by examining the characteristics and properties of the query itself, use the characteristics and properties of the query to associate the query (or a part of the query) with previously seen information or examples, generate hypotheses based on that information, and ask the connector interface 206 to correct or confirm the hypotheses. In this way, the optimizer 214 can configure the query to: (1) collect information for the same or similar subsequent queries, and (2) restart for all or part of the query if the hypotheses are poor, whenever possible.

[0058] As another example, the optimizer 214 can cause the query system 200 to compress operators of a query plan (or a part of the query plan). For example, the optimizer 214 can compress an extract operator and an associated predicate into a single query that only extracts predicate rows. In this example, the optimizer 214 can determine to compress the extract operator and the associated predicate into a single query based on the sizes of the operands stored in the metric repository 216 (e.g., the extraction sizes of the extract operator and the predicate operator). In some examples where the connector interface 206 provides leaf nodes (e.g., fetch operators) for the query plan, the connector interface 206 (such as by adapting or transforming the compressed operators as needed) can mediate the compression operation.

[0059] In some examples, the optimizer 214 can provide column optimization. For example, the optimizer 214 can push down expression components to allow for maximum visibility of the connector interface during the compression phase.

[0060] In some examples, the optimizer 214 can selectively determine whether to push join operands in a query plan for a subsequent query to the database. In certain examples, the optimizer 214 can prevent pushing join operands to the database based on the cardinality of the tables to be joined. For example, the data source 208A can include a table with a large number of duplicate entries. If the join operands are pushed to the data source 208A, the joined table may result in a dataset larger than the data source 208A, which is less efficient in some cases when returning data to the query system 200. In these examples, the optimizer 214 can actively prevent the join operands from being pushed to the data source 208.

[0061] In yet another example, the optimizer 214 can identify subqueries that produce the same or similar output. If a compatible subgraph is found, the subgraphs are merged into a single subgraph. The deleted subgraphs are replaced with references to the merged subgraph and, in some cases, with additional predicates, inferred predicates, or column pruning projections.

[0062] Alternatively or additionally, the optimizer 214 may cause the query system 200 to optimize the query plan for the current query or subsequent queries based on assumptions generated from one or more attributes of the operators. An assumption is a prediction about a subgraph based on attributes such as the number of rows, whether a subgraph produces fewer rows than another subgraph, whether a subgraph has a higher join priority than another subgraph, and / or whether a subgraph produces a given cardinality for a column. Models such as a dynamic learning model, a pre-loaded heuristic, a basic dynamic model with a set of previously encountered attributes and metrics (e.g., a table T with a range predicate on column C produced in n rows) may be used to generate assumptions. Assumptions may be generated by performing a series of hash lookups to find potentially applicable models. If an applicable model is found, the optimizer 214 generates an assumption about the subgraph. A confidence level is assigned to the assumption based on the goodness of fit and the general confidence associated with the model. Some heuristic models have a high confidence level (e.g., because they are based on declared metadata or high-confidence statistics or metrics). Other models are rough rule-of-thumb heuristics (e.g., a table with more predicates has a higher join priority). The hash lookup terminates when a suitable high-confidence assumption is generated or when a default, simple rule-of-thumb heuristic is invoked, resulting in a low-confidence assumption that triggers a measurement and control request. In some examples, conflicting assumptions are blended based on the confidence levels of the conflicting assumptions. When multiple dynamic models match during the hash lookup, the dynamic models are marked. If the same set of elements is marked repeatedly, those elements are merged. Effectively, the individual elements become the training set for a clustering or regression model. This produces a more accurate assumption than blending assumptions based on confidence levels and results in a better model for repeatedly encountered query elements or arrangements of elements.

[0063] When assumption generation involves a single connector interface, the assumption is presented to the connector interface. The connector interface may reject the assumption, modify the assumption, or change the confidence level of the assumption. For a high-value, low-confidence assumption, the connector interface may choose to perform a data sampling operation or a data collection query to obtain high-confidence metrics for optimizing the current query and subsequent queries.

[0064] In response to optimizing the query plan, the optimizer 214 may generate a measurement and control request for an assumption (e.g., a high-value, low-confidence assumption). Assumptions based on rough heuristics are low-confidence, which results in queries or subgraphs that rely on low-confidence assumptions generating requests for the acquisition of metrics. The type of assumption determines the metrics requested. For example, an assumption of uniform join density (e.g., based on a heuristic or a loose match of similar joins) may generate a request for measurements of the affected joins.

[0065] In some examples, when the observed metrics are inconsistent with the optimization decisions made during initial optimization, the execution engine 210 may stop the running query or a portion thereof and cause the optimizer 214 to re-optimize the query. For example, if the optimization decision depends on a low-confidence assumption and the subgraph is restartable, one or more monitoring instruments (e.g., instrumentation code) that monitor the execution of the query are marked as re-optimization triggers. For example, the monitoring instrument may be augmented with an expected value or ratio. If the measured value or ratio deviates significantly from the expected value, the monitoring instrument will trigger a restart.

[0066] Figures 3A - 3C is a conceptual diagram of an example optimization operation according to one or more aspects of the techniques in this disclosure. Figure 3A is a conceptual illustration of an example optimization operation that reorders operations of a query plan according to one or more aspects of the techniques in this disclosure. With respect to Figure 2 query system 200 described Figure 3A .

[0067] In Figure 3A the example of, query system 200 may receive a subsequent query 302. The compiler 212 of query system 200 compiles the subsequent query 302 to build a query plan 304 for the subsequent query. In this example, query plan 304 includes a grouping operator 306 to group data returned from data sources A, B, and C (e.g., data sources 208A - 208C of Figure 2 respectively). For example, query plan 304 includes extract operators 310A - 310C (collectively "extract operators 310") for subqueries generated for query 302 to access data stored in data source 208 (e.g., via respective connector interfaces 206). Query plan 304 also includes join operators 308A - 308B (collectively "join operators 308") to join data retrieved from data source 208. In the initial query plan 304, extract operator 310A extracts data from data source 208A, and extract operator 310B extracts data from data source 208B. Join operator 308A joins the data returned as a result of executing extract operators 310A, 310B. Join operator 308B then joins the data returned as a result of executing extract operator 310C.

[0068] In this example, the optimizer 214 may cause the query system 200 to reorder operands based on one or more metrics obtained from previous queries executed on any data source 208. For example, the optimizer 214 may reorder the join operators (illustrated as join operators 308C - 308D) in the query plan 304 such that the join operator with the smallest table joins first and the join operator with the largest table joins last. In this example, the optimizer 214 may determine the size of the tables of the data source 208 based on the row counts of the tables obtained when executing previous queries. For example, the tables of data sources 208A and 208C may have lower row counts than the table of data source 208B. Based on the size of the tables of the data source 208, the optimizer 214 may reorder the join operators in the query plan 312 ("optimized query plan 312") such that the join operator 308C first joins the data returned as a result of executing the extraction operators 310A and 310C, and then the join operator 308D joins the data returned as a result of executing the extraction operator 310B.

[0069] Figure 3B is a conceptual illustration of an example optimization operation of compressing query plan operations according to one or more aspects of the techniques in the present disclosure. With respect to Figure 2 the query system 200 described Figure 3B . In Figure 3B the example, the query system 200 may receive a subsequent query 322. The compiler 212 of the query system 200 compiles the subsequent query 322 to construct a query plan 324 for the subsequent query. In this example, the query plan 324 includes a grouping operator 326 to group data from data sources A, B, and C (e.g., respectively Figure 2 Group the data returned by the data sources 208A - 208C). For example, the query plan 324 includes extraction operators 330A - 330C (collectively referred to as "extraction operators 330") for sub - queries generated for the query 322 to access the data stored in the data source 208 respectively (e.g., via the corresponding connector interface 206). The query plan 324 may also include predicate operators 331A - 331B (collectively referred to as "predicate operators 331") to access a subset of the data stored in the data source 208. For example, the predicate operator 331A requests to access a subset of the data in the data source 208C, where z = 8. The predicate operator 331B requests to access a subset of the data in the data source 208B, where y < 5. The query plan 324 also includes join operators 328A - 328B (collectively referred to as "join operators 328") to join the data returned as a result of executing the extraction operators 330 and / or predicate operators 331. For example, the join operator 328A joins the data returned as a result of executing the extraction operator 330C, the predicate operator 331A, the extraction operator 330A, and the predicate operator 331B. The join operator 328B then joins the data returned as a result of executing the extraction operator 330B.

[0070] In this example, the optimizer 214 may cause the query system 200 to compress the extraction operator 330C and the predicate operator 331A into a single query (e.g., sub - query 334) that only extracts predicate rows. The optimizer 214 may compress the extraction operator 330C and the predicate operator 331A based on one or more metrics obtained from previous queries executed on any data source 208. For example, the optimizer 214 may determine the size of the operands (such as the extraction size of the extraction operator 330C and the extraction size of the predicate operator 331A). Based on the size of the operands, the optimizer 214 may compress the predicate operator 331A (e.g., "where z = 8") with the extraction operator 330C (e.g., "select k from C") to form a single sub - query 334 (e.g., "select k from C where z = 8"). In this way, the prediction is applied at the data source 208C, thereby reducing the network traffic required to obtain the query data in some instances.

[0071] Figure 3C is a conceptual illustration of another example optimization operation of the operation of compressing a query plan according to one or more aspects of the techniques in the present disclosure. With respect to Figure 2 the query system 200 described Figure 3C . In Figure 3C In the example, the query system 200 can receive a subsequent query 342. The compiler 212 of the query system 200 compiles the subsequent query 342 to build a query plan 344 for the subsequent query. In this example, the query plan 344 includes a grouping operator 346 to group data returned from data sources A, B, and C (e.g., data sources 208A - 208C respectively Figure 2 ). For example, the query plan 344 includes extraction operators 350A - 350C (collectively referred to as "extraction operators 350") for subqueries generated for query 342 to access data stored in the data sources 208 (e.g., via the respective connector interfaces 206). The query plan 344 may also include predicate operators 351A - 351B (collectively referred to as "predicate operators 351") to access a subset of the data stored in the data sources 208. For example, the predicate operator 351A requests access to a subset of the data in the data source 208C where z = 8. The predicate operator 351B requests access to a subset of the data in the data source 208B where y < 5. The query plan 344 also includes join operators 348A - 348B (collectively referred to as "join operators 348") to join the data returned as a result of executing the extraction operators 350 and / or the predicate operators 351. For example, the join operator 348A joins the data returned as a result of executing the extraction operator 350C, the predicate operator 351A, the extraction operator 350A, and the predicate operator 351B. The join operator 348B then joins the data returned as a result of executing the extraction operator 350B.

[0072] In this example, the optimizer 214 can cause the query system 200 to compress the extraction operators 350A, 350C and the predicate operators 351A, 351B into a single query (e.g., subquery 354) that only extracts predicate rows. The optimizer 214 can compress the extraction operators 350A, 350C and the predicate operators 351A, 351B based on one or more metrics obtained from previous queries executed on any of the data sources 208. For example, the optimizer 214 can determine the size of the operands (such as the extraction sizes of the extraction operators 350A, 350C and the extraction sizes of the predicate operators 351A, 351B). Based on the size of the operands, the optimizer 214 can compress the extraction operators 350A, 350C, the predicate operators 351A, 351B, and the join operator 348A to form a single operator for the subquery 354 (e.g., "select C.k from C where C.k = A.k and C.z = 8 and A.y < 5"), thereby accessing the data sources 208A and 208C.

[0073] Figure 4 is a flowchart showing the operation of a query system configured to dynamically optimize queries according to one or more aspects of the disclosed technology. It is described with respect to Figure 2 the example query system 200 described in Figure 4 .

[0074] In Figure 4 the example, the query system 200 receives a query (402) for accessing first data stored in multiple data sources. For example, a user may specify a query based on a neutral dialect (such as SQL). One or more nodes 204 engaged with the query system 200 may receive the query to access data stored in one or more data sources 208.

[0075] The query system 200 compiles the query to generate a compiled query that includes one or more instructions for obtaining one or more metrics when the compiled query is executed. For example, a compiler 212 of the query system 200 may compile the query to build a query plan to utilize the native capabilities of the data source 208. More specifically, the compiler 212 may restructure the query (e.g., a neutral dialect query) into subqueries (e.g., relational / non-relational database queries) to access data stored in multiple data sources 208. An optimizer 214 may cause the compiler 212 to add instrumentation code (e.g., counters) when compiling the query to obtain one or more metrics when the compiled query is executed.

[0076] The query system 200 obtains one or more metrics and uses these metrics to execute portions of the compiled query at one or more data sources. For example, an execution engine 210 of the query system 200 may execute the query plan generated by the compiler 212. For example, the execution engine 210 may distribute subqueries in parallel via a connector interface 206 to multiple data sources 208, each of the connector interfaces 206 providing an interface to a corresponding one of the data sources 208. The connector interface 206 is responsible for executing portions (e.g., subqueries) of the query plan generated by the optimizer 214 and passing the subquery result data returned from the data source 208 to the execution engine 210.

[0077] When query system 200 receives a subsequent query (406) to access second data stored in multiple data sources, query system 200 optimizes the query plan (408) for the subsequent query based on one or more metrics. For example, when query system 200 receives a subsequent query, compiler 212 of query system 200 may compile the subsequent query to generate a query plan for the subsequent query, which is different from the query plan that query system 200 would generate without metrics. For example, based on metrics dynamically obtained from data source 208, optimizer 214 may cause query system 200 to: (1) select a specific data source 8 for a data set; (2) determine the type or ordering of tasks within a subquery sent to data source 8; (3) determine one or more tasks of the query to be processed by query system 10 rather than data source 8; (4) determine the task ordering (such as a join operation) of the query or subquery; (5) determine whether to compress operators of the query or subquery; (6) or other determinations that affect the query plan and the compiled query generated by query system 10 (such as selectively determining whether to push join operands to the data source).

[0078] Figure 5 is a block diagram of an example computing device 500 that operates in accordance with one or more techniques of the present disclosure. Figure 5 A specific example of a computing device may be shown, which includes one or more processors 502 for executing Figure 2 any one of the nodes 204 in or any other computing device described herein. In Figure 5 the example, computing device 500 may be a workstation, server, mainframe computer, notebook or laptop computer, desktop computer, tablet computer, smart phone, feature phone, or any other kind of programmable data processing device. For example, Figure 5 the computing device 500 of Figure 2 may represent any node of query system 200 as shown in Figure 5 . Other examples of computing device 500 may be used in other instances. Although shown as a stand-alone computing device 500 in Figure 5 for purposes of illustration, the computing device may be any component or system that includes one or more processors or other suitable computing environment for executing software instructions and, for example, need not include

[0079] As Figure 5 As shown in the specific example, computing device 500 includes one or more processors 502, one or more input devices 504, one or more communication units 506, one or more output devices 512, one or more storage devices 508, and one or more user interface (UI) devices 510. In one example, computing device 500 also includes one or more application programs 522 and an operating system 516 that can be executed by computing device 500. Each of components 502, 504, 506, 508, 510, and 512 is (physically, communicatively, and / or operably) coupled for communication between components. In some examples, communication channel 514 can include a system bus, a network connection, an interprocess communication data structure, or any other method for transferring data. As an example, components 502, 504, 506, 508, 510, and 512 can be coupled via one or more communication channels 514.

[0080] In one example, processor 502 is configured to implement functions and / or process instructions for execution within computing device 500. For example, processor 502 is capable of processing instructions stored in storage device 508. Examples of processor 502 can include any one or more of a microprocessor, a controller, a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field programmable gate array (FPGA), or equivalent discrete or integrated logic circuitry.

[0081] One or more storage devices 508 can be configured to store information within computing device 500 during operation. In some examples, storage device 508 is described as a computer-readable storage medium. In some examples, storage device 508 is temporary memory, which means that the primary purpose of storage device 508 is not long-term storage. In some examples, storage device 508 is described as volatile memory, which means that when the computer is turned off, storage device 508 does not maintain the stored content. Examples of volatile memory include random access memory (RAM), dynamic random access memory (DRAM), static random access memory (SRAM), and other forms of volatile memory known in the art. In some examples, storage device 508 is used to store program instructions for execution by processor 502. In one example, storage device 508 is used by software or an application program running on computing device 500 to temporarily store information during program execution.

[0082] In some examples, the storage device 508 further includes one or more computer-readable storage media. The storage device 508 can be configured to store a larger amount of information than volatile memory. The storage device 508 can also be configured for long-term storage of information. In some examples, the storage device 508 includes non-volatile storage elements. Examples of such non-volatile storage elements include magnetic hard disks, optical disks, floppy disks, flash memory, or various forms of electrically programmable memory (EPROM) or electrically erasable programmable (EEPROM) memory.

[0083] In some examples, the computing device 500 further includes one or more communication units 506. In one example, the computing device 500 uses the communication unit 506 to communicate with external devices via one or more networks (such as, one or more wired / wireless / mobile networks). The communication unit 506 can include a network interface card (such as an Ethernet card), an optical transceiver, a radio frequency transceiver, or any other type of device that can send and receive information. Other examples of such network interfaces can include 3G, 4G, 5G, and WiFi radios. In some examples, the computing device 500 uses the communication unit 506 to communicate with external devices.

[0084] [[ID=⑥]]In one example, the computing device 500 further includes one or more user interface devices 510. In some examples, the user interface device 510 is configured to receive input from a user through tactile, audio, or visual feedback. Examples of the (one or more) user interface devices 510 include a presence-sensitive display, a mouse, a keyboard, a voice response system, a camera, a microphone, or any other type of device for detecting commands from a user. In some examples, the presence-sensitive display includes a touch-sensitive screen. [[ID=⑦]] [[ID=⑧]]

[0085] [[ID=⑨]]The computing device 500 may also include one or more output devices 512. In some examples, the output device 512 is configured to provide output to a user using tactile, audio, or visual stimuli. In one example, the output device 512 includes a presence-sensitive display, a sound card, a video graphics adapter card, or any other type of device for converting a signal into an appropriate form understandable by a human or a machine. Additional examples of the output device 512 include speakers, a cathode ray tube (CRT) monitor, a liquid crystal display (LCD), or any other type of device that can generate an understandable output to a user. [[ID=⑩]] [[ID=⑪]]

[0086] It should be noted that in the translation of the Chinese text in item [6], the numbers in the original text are in Chinese characters, and they are translated according to the numbers in the English text for better correspondence. If you have any other questions, please feel free to let me know.The computing device 500 may include an operating system 516. In some examples, the operating system 516 controls the operation of the components of the computing device 500. For example, in one example, the operating system 516 facilitates communication between one or more application programs 522 and the processor 502, the communication unit 506, the storage device 508, the input device 504, the user interface device 510, and the output device 512. The application programs 522 may also include program instructions and / or data executable by the computing device 500.

[0087] The optimizer 524 may include instructions for causing the computing device 500 to perform the techniques described in this disclosure with respect to Figure 1 optimizer 14 of Figure 2 and the techniques described for optimizer 214 of

[0088] For example, the optimizer 524 may include instructions for causing the computing device 500 to dynamically optimize queries in accordance with the techniques of this disclosure.

[0088] The techniques described in this disclosure may be implemented, at least in part, in hardware, software, firmware, or any combination thereof. For example, aspects of the described techniques may be implemented in one or more processors, which may include one or more microprocessors, digital signal processors (DSPs), application specific integrated circuits (ASICs), field programmable gate arrays (FPGAs), or any other equivalent integrated or discrete logic circuitry, as well as any combination of such components. The term "processor" or "processing circuitry" generally may refer to any of the foregoing logic circuitry, alone or in combination with other logic circuitry, or any other equivalent circuitry. A control unit including hardware may also perform one or more of the techniques of this disclosure.

[0089] Such hardware, software, and firmware may be implemented within the same device or in separate devices to support the various operations and functions described in this disclosure. Additionally, any of the described units, modules, or components may be implemented together or separately as discrete but interoperable logic devices. Describing different features as modules or units or engines is intended to highlight different functional aspects and does not necessarily imply that such modules or units must be implemented by separate hardware or software components. Rather, the functions associated with one or more modules or units may be performed by separate hardware or software components, or integrated in common or separate hardware or software components.

[0090] The techniques described in this disclosure may also be embodied or encoded in a computer-readable medium that includes instructions (such as, a computer-readable storage medium). The instructions embodied or encoded in the computer-readable storage medium may cause a programmable processor or other processor (e.g., when executing the instructions) to perform the method. The computer-readable storage medium may include random access memory (RAM), read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), flash memory, a hard disk, a CD-ROM, a floppy disk, a cassette tape, a magnetic medium, an optical medium, or other computer-readable media.< / customers> < / sales> < / postgres>

Claims

1. A method performed by a query system, the method comprising: Receiving a query for accessing first data stored in a plurality of data sources; Compiling the query to generate a plurality of compiled queries, each compiled query for retrieving data from a corresponding one of the plurality of data sources, wherein compiling the query to generate the plurality of compiled queries includes: determining a confidence level of an assumption about one or more properties of one or more operators of the query, and based on the determined confidence level, including one or more instructions within each of the plurality of compiled queries, wherein the one or more instructions are for obtaining one or more metrics when one of the plurality of compiled queries is executed, the one or more metrics for performing a portion of the one compiled query at the corresponding one of the plurality of data sources; Receiving a subsequent query for accessing second data stored in the plurality of data sources; and Optimizing a query plan for the subsequent query based on the one or more metrics obtained from the execution of each of the plurality of compiled queries.

2. The method according to claim 1, wherein The one or more metrics include at least one of a size of an operand, a selectivity of a predicate, and a cardinality of a table.

3. The method according to claim 2, Among them, the size of the operand includes the number of rows of a table returned as a result of the execution of the query, and wherein the one or more instructions for obtaining the one or more metrics include a counting function for counting the number of rows of the table returned from the execution of the query.

4. The method according to claim 2, Among them, the selectivity of the predicate includes a subset of a data set identified by a condition applied to rows of the table, and wherein the one or more instructions for obtaining the one or more metrics include a counting function for counting the number of rows of the table returned based on the predicate as a result of the execution of the query.

5. The method according to claim 2, Among them, the cardinality of the table includes the number of duplicate entries of a column of the table, and the one or more instructions for obtaining the one or more metrics include a counting function for counting the number of duplicate entries of the column of the table.

6. The method according to claim 1, wherein Optimizing the query plan for the subsequent query includes adding join operands to tables of the plurality of data sources.

7. The method according to claim 1, wherein Optimizing the query plan for the subsequent query includes reordering a series of join operands of the subsequent query for tables of the plurality of data sources.

8. The method according to claim 1, wherein Optimizing the query plan for the subsequent query includes: Determining whether the join operands of the subsequent query are optimal; and In response to determining that the join operands of the subsequent query are not optimal, rejecting the implementation of the join operands of the subsequent query for tables of the plurality of data sources.

9. The method according to claim 1, wherein Optimizing the query plan for the subsequent query based on the one or more metrics includes optimizing the query plan based on one or more decision factors, where the one or more decision factors include one or more of the following: operator cardinality, column data type, column uniqueness, selectivity of predicates, join density, plan enumeration benefits, Bloom filter benefits, distribution benefits, replication benefits, repartitioning benefits, connector pushdown benefits, cache benefits, function cost, evaluation order, generated class reuse, likelihood of compiler de-optimization, index strategy, transaction scope, transaction size, transaction duration, and transaction priority.

10. The method according to claim 1, wherein, Optimizing the query plan for the subsequent query based on the one or more metrics includes optimizing the query plan based on one or more stated factors, where the one or more stated factors include one or more of the following: primary key relationships, foreign key relationships, unique statements, stated indexes, and query hints.

11. The method according to claim 1, wherein, Optimizing the query plan for the subsequent query based on the one or more metrics includes: optimizing the query plan based on one or more heuristic operator models including consistent approximation and polynomial approximation.

12. The method according to claim 1, wherein Optimizing the query plan for the subsequent query based on the one or more metrics includes optimizing the query plan based on one or more learned operator models, where the one or more learned operator models include one or more of the following: seeded learning, dynamic learning, decision trees, naive Bayes classifiers, K-nearest neighbors, support vector machines, and random forests.

13. The method according to any one of claims 1 to 12, wherein, Optimizing the query plan for the subsequent query based on the one or more metrics includes optimizing the query plan based on one or more learning models of features, the features including distribution profiles, workload profiles, sessions or users, tables, column operators, predicate expressions, and projection expressions.

14. The method according to any one of claims 1 to 12, wherein, The plurality of data sources includes a plurality of heterogeneous data sources, the heterogeneous data sources including at least one relational database and one non-relational database.

15. The method according to any one of claims 1 to 12, wherein Compiling the query includes just-in-time compilation using recently obtained metrics.

16. A query system, comprising: An interface configured to receive a query for accessing first data stored in a plurality of data sources; A plurality of connector interfaces for interfacing with the plurality of data sources; A compiler configured to compile the query to generate a plurality of compiled queries, each compiled query for retrieving data from a corresponding one of the plurality of data sources, where compiling the query to generate the plurality of compiled queries includes: determining a confidence level of an assumption about one or more properties of one or more operators of the query, and based on the determined confidence level, including one or more instructions within each of the plurality of compiled queries, where the one or more instructions are for obtaining one or more metrics when one of the plurality of compiled queries is executed, the one or more metrics for executing a portion of the one compiled query at the corresponding one of the plurality of data sources; and An optimizer configured to optimize a query plan for a subsequent query in response to the interface receiving the subsequent query for accessing second data stored in the plurality of data sources, based on the one or more metrics obtained from the execution of each of the plurality of compiled queries.

17. The query system according to claim 16, wherein, Each of the plurality of connector interfaces is further configured to: Execute a subquery of the query plan on the plurality of data sources; and Return result data of the subquery from the plurality of data sources.

18. The query system according to claim 16, wherein To optimize the query plan for the subsequent query based on the one or more metrics, the optimizer is further configured to: Optimize the query plan based on one or more decision factors, where the one or more decision factors include one or more of the following: operator cardinality, column data type, column uniqueness, selectivity of predicates, join density, plan enumeration benefits, Bloom filter benefits, distribution benefits, replication benefits, repartitioning benefits, connector pushdown benefits, cache benefits, function cost, evaluation order, generated class reuse, likelihood of compiler de-optimization, index strategy, transaction scope, transaction size, transaction duration, and transaction priority.

19. The query system according to claim 16, wherein, To optimize the query plan for the subsequent query based on the one or more metrics, the optimizer is further configured to: Optimize the query plan based on one or more stated factors, where the one or more stated factors include one or more of the following: primary key relationships, foreign key relationships, unique declarations, declared indexes, and query hints.

20. The query system according to claim 16, wherein, To optimize the query plan for the subsequent query based on the one or more metrics, the optimizer is further configured to: Optimize the query plan based on one or more heuristic operator models including consistent approximation and polynomial approximation.

21. The query system according to claim 16, wherein, To optimize the query plan for the subsequent query based on the one or more metrics, the optimizer is further configured to: Optimize the query plan based on one or more learned operator models, where the one or more learned operator models include one or more of the following: seeded learning, dynamic learning, decision trees, naive Bayes classifiers, K-nearest neighbors, support vector machines, and random forests.

22. The query system according to any one of claims 16 to 21, wherein, To optimize the query plan for the subsequent query based on the one or more metrics, the optimizer is further configured to: Optimize the query plan based on one or more learning models of features, the features including distribution profiles, workload profiles, sessions or users, tables, column operators, predicate expressions, and projection expressions.

23. A non-transitory computer-readable medium containing instructions for causing one or more programmable processors to: Receive a query for accessing first data stored in a plurality of data sources; Compile the query to generate a plurality of compiled queries, each compiled query for retrieving data from a corresponding one of the plurality of data sources, where, Compiling the query to generate the plurality of compiled queries includes: determining a confidence level of an assumption about one or more properties of one or more operators of the query, and based on the determined confidence level, including one or more instructions within each of the plurality of compiled queries, wherein the one or more instructions are for obtaining one or more metrics when one of the plurality of compiled queries is executed, the one or more metrics being for executing a portion of the one compiled query at a corresponding data source of the plurality of data sources; receiving a subsequent query for accessing second data stored in the plurality of data sources; and optimizing a query plan for the subsequent query based on the one or more metrics obtained from the execution of each of the plurality of compiled queries.

Images