Data query method, electronic device, and computer-readable storage medium

Abstract

The application belongs to the technical field of big data, and discloses a data query method, an electronic device and a computer readable storage medium. The data query method comprises the following steps: analyzing a SQL query statement to obtain a query engine of the SQL query statement for data query in a multi-engine query system; optimizing a syntax tree of the SQL query statement according to preset statement optimization information to obtain an optimized syntax tree, wherein the preset statement optimization information comprises general optimization information and preset engine optimization information; rewriting the optimized syntax tree according to a preset engine syntax library of the query engine to obtain an engine statement matched with the query engine; and performing data query in the query engine based on the engine statement. The application aims to improve the query performance of SQL under the condition of supporting multiple query engines.

Description

Technical Field

[0001] This application belongs to the field of big data technology and relates to a data query method, electronic device and computer-readable storage medium. Background Technology

[0002] When using SQL (Structured Query Language) to query data, users generally need to manually write SQL according to the query engine to retrieve data. However, there are currently many query engines available. When users need to query on different query engines, they need to manually write different query statements, which makes the user's query workload too large and the query efficiency low.

[0003] While ORM (Object Relational Mapping) frameworks exist that standardize SQL for data retrieval across multiple query engines, they only standardize simple SQL queries. When dealing with function syntax and complex queries, users still need to write queries according to the specific engine's syntax, making data retrieval inconvenient. Additionally, service-based query components exist, which process SQL statements and send them to multiple query engines, eliminating the need for manual statement writing. However, because these components process the SQL before sending it to the engine, there is a significant performance penalty compared to sending the SQL directly. Therefore, it is currently difficult to achieve both high performance and multi-engine querying using SQL statements simultaneously.

[0004] The above content is only used to help understand the technical solution of this application and does not represent an admission that the above content is prior art. Summary of the Invention

[0005] The main objective of this application is to provide a data query method, electronic device, and computer-readable storage medium, which aim to improve the query performance of SQL while supporting multiple query engines.

[0006] To achieve the above objectives, this application provides a data query method, which includes:

[0007] Parse the SQL query statement to obtain the query engine in the multi-engine query system where the SQL query statement performs data querying;

[0008] Based on preset statement optimization information, the syntax tree of the SQL query statement is optimized to obtain an optimized syntax tree, wherein the preset statement optimization information includes general optimization information and preset engine optimization information;

[0009] The optimized syntax tree is rewritten according to the preset engine syntax library of the query engine to obtain an engine statement that matches the query engine.

[0010] Data is retrieved in the query engine based on the engine statement.

[0011] To achieve the above objectives, this application provides a data query device, the data query device comprising:

[0012] The parsing module is used to parse SQL query statements to obtain the query engine for data querying in the multi-engine query system.

[0013] The optimization module is used to optimize the syntax tree of the SQL query statement according to preset statement optimization information to obtain an optimized syntax tree. The preset statement optimization information includes general optimization information and preset engine optimization information.

[0014] The rewriting module is used to rewrite the optimized syntax tree according to the preset engine syntax library of the query engine to obtain an engine statement that matches the query engine.

[0015] The query module is used to perform data queries on the query engine based on the engine statement.

[0016] This application also provides an electronic device, the electronic device comprising: a memory, a processor, and a program of the data query method stored in the memory and executable on the processor, wherein when the program of the data query method is executed by the processor, it can implement the steps of the data query method as described above.

[0017] This application also provides a computer-readable storage medium storing a program implementing a data query method, wherein when the program is executed by a processor, it implements the steps of the data query method as described above.

[0018] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the steps of the data query method described above.

[0019] This application provides a data query method, an electronic device, and a computer-readable storage medium. The method involves parsing an SQL query statement to obtain a query engine for performing data queries in a multi-engine query system; optimizing the syntax tree of the SQL query statement according to preset statement optimization information to obtain an optimized syntax tree, wherein the preset statement optimization information includes general optimization information and preset engine optimization information; rewriting the optimized syntax tree according to the preset engine syntax library of the query engine to obtain an engine statement matching the query engine; and performing data queries in the query engine based on the engine statement.

[0020] This application determines the corresponding query engine for an SQL query statement in a multi-engine query system. Based on preset statement optimization information, it optimizes the syntax tree of the SQL query statement to obtain an optimized syntax tree, thus optimizing the SQL query statement. Furthermore, based on the preset engine syntax library of the query engine, the optimized syntax tree can be rewritten into the corresponding engine statement for the query engine. This allows for data querying based on the engine statement and the query engine, automatically converting the SQL query statement into the corresponding engine statement for data querying within the query engine. This eliminates the need for users to manually write SQL query statements, supporting queries from multiple query engines. Moreover, the optimization of the SQL query statement reduces its complexity, thereby improving its performance. Ultimately, this improves the query performance of SQL queries while supporting data querying from multiple query engines. Attached Figure Description

[0021] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0022] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0023] Figure 1 This is a flowchart illustrating the first embodiment of the data query method of this application;

[0024] Figure 2 This is a flowchart illustrating the second embodiment of the data query method of this application;

[0025] Figure 3 This is a schematic diagram of the multi-engine query system for the data query method of this application;

[0026] Figure 4 This is a schematic diagram of an apparatus according to an embodiment of the data query method of this application;

[0027] Figure 5 This is a schematic diagram of the device structure of the hardware operating environment involved in the data query method in this application embodiment.

[0028] The purpose, features, and advantages of this application will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0029] To make the above-mentioned objectives, features, and advantages of this application more apparent and understandable, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are merely some embodiments of this application, and not all embodiments. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0030] Example 1

[0031] Reference Figure 1 This application provides a data query method applied to a multi-engine query system. In a first embodiment of the data query method, the data query method includes:

[0032] Step S10: Parse the SQL query statement to obtain the query engine in the multi-engine query system for querying data using the SQL query statement;

[0033] Step S20: Optimize the syntax tree of the SQL query statement according to the preset statement optimization information to obtain an optimized syntax tree. The preset statement optimization information includes general optimization information and preset engine optimization information.

[0034] Step S30: Rewrite the optimized syntax tree according to the preset engine syntax library of the query engine to obtain an engine statement that matches the query engine.

[0035] Step S40: Perform a data query in the query engine based on the engine statement.

[0036] It should be noted that the multi-engine query system includes multiple query engines. The system receives SQL query statements from users and converts them into the engine language of the corresponding query engine. The SQL query statement is the statement entered by the user in the multi-engine query system. The SQL query statement is generated based on SQL2003 syntax. Users only need to master SQL2003 syntax to determine the SQL query statement and then perform data queries in the multi-engine query system using the SQL query statement, without needing to master the engine language supported by each query engine. The multi-engine query system may include OLAP engines such as ClickHouse (an open-source columnar database for online analytical processing), Gauss200 (a distributed database), and Kylin (an open-source distributed analytical processing engine). Preset statement optimization information represents SQL optimization rules. This preset statement optimization information includes general optimization information and preset engine optimization information. General optimization information represents SQL optimization rules independent of the query engine, while preset engine optimization information represents SQL optimization rules related to the preset query engine.

[0037] In this embodiment, the user-input SQL query statement is parsed and a syntax tree (AST) is generated. The syntax tree is an Abstract Syntax Tree (AST). The preset engine syntax library contains the syntax rules of SQL used when querying data in the query engine. This library provides a basis for rewriting the optimized syntax tree. The optimized syntax tree is obtained by optimizing the syntax tree with preset statement optimization information. The engine statement is the optimized and rewritten SQL query statement. The syntax rules in the engine statement are those supported by the query engine, allowing data queries to be performed within the query engine based on the engine statement.

[0038] Prior to step S10, the data query method further includes, in response to a user's data query instruction, obtaining the SQL query statement entered by the user in the multi-engine query system. It should be noted that the user can enter the SQL query statement using JDBC (Java Database Connectivity) or via HTTP (Hypertext Transfer Protocol) interface in the multi-engine query system.

[0039] As an example, steps S10 to S40 include: parsing the SQL query statement to obtain the query engine for data querying in the multi-engine query system; converting the SQL query statement into a syntax tree; optimizing the syntax tree of the SQL query statement according to preset statement optimization information to obtain an optimized syntax tree; rewriting the optimized syntax tree according to the preset engine syntax library of the query engine to obtain an engine statement that matches the query engine; and performing data querying in the query engine based on the engine statement.

[0040] This application determines the corresponding query engine for an SQL query statement in a multi-engine query system. Based on preset statement optimization information, it optimizes the syntax tree of the SQL query statement to obtain an optimized syntax tree. Since the preset statement optimization information includes general optimization information and preset engine optimization information, optimization can be achieved whether the SQL query statement is related to general optimization information or preset engine optimization information. This allows for optimization of both general SQL and SQL specific to preset engines, expanding the optimization scope of the SQL query statement. Furthermore, based on the preset engine syntax library of the query engine, the optimized syntax tree can be rewritten into the engine statement corresponding to the query engine. This enables data querying based on the engine statement and the query engine, automatically converting the SQL query statement into the corresponding engine statement for data querying within the query engine. This eliminates the need for users to manually write SQL query statements, supporting queries from multiple query engines. Furthermore, the optimization of the SQL query statement reduces its complexity, thereby improving its performance. Ultimately, this improves the query performance of SQL queries while supporting data querying from multiple query engines.

[0041] Furthermore, step S10 includes:

[0042] Step S11: Parse the SQL query statement to obtain the table object of the SQL query statement;

[0043] Step S12: Search the preset object query table for a query engine that matches the table object to determine the query engine of the SQL query statement.

[0044] It should be noted that the preset object query table includes table objects and query engines associated with those table objects. After parsing the SQL query statement, the table object of the SQL query statement can be obtained. Therefore, the query engine associated with the table object can be searched in the preset object query table based on the table object, thus determining the query engine of the SQL query statement. This embodiment of the application obtains the query engine used by the SQL query statement by parsing it, facilitating the subsequent conversion of the SQL query statement into engine statements supported by the query engine based on a preset engine syntax library.

[0045] In one feasible embodiment, step S20 includes:

[0046] Step S21: Convert the SQL query statement into a syntax tree, traverse all parent nodes in the syntax tree, and obtain the child nodes associated with the parent nodes to determine the syntax information composed of the parent nodes and the child nodes;

[0047] Step S22: If the grammar information satisfies the statement optimization conditions, then the grammar information is optimized based on the preset statement optimization information to update the parent node and the child node;

[0048] Step S23: After traversing all parent nodes in the syntax tree, the optimized syntax tree is obtained.

[0049] It should be noted that the SQL query statement is parsed to convert it into a syntax tree (AST). The syntax tree is then traversed, and the child nodes associated with each parent node are retrieved. The content of the parent node and the content of its associated child nodes are combined to form the syntax information. This syntax information represents the syntax information of the parent node and its associated child nodes within the syntax tree. The syntax information includes the syntax content and the node syntax association order. The node syntax association order refers to the relationship between parent and child nodes. The syntax content consists of the content of the parent node and the content of the child nodes. Statement optimization conditions are used to determine whether the syntax information can be optimized. If it can be optimized, the syntax information is optimized according to preset statement optimization information, and the parent and child nodes are updated.

[0050] As an example, steps S21 to S23 include: converting the SQL query statement into a syntax tree; traversing all parent nodes in the syntax tree to obtain the child nodes associated with the parent nodes; using the node content of the parent node and the node content of the associated child nodes together as syntax information; determining whether the syntax information meets the statement optimization conditions; if the syntax information meets the statement optimization conditions, optimizing the syntax information based on preset statement optimization information to update the parent nodes and child nodes; if the syntax information does not meet the statement optimization conditions, determining whether the syntax information of the next parent node meets the statement optimization conditions; after traversing all parent nodes in the syntax tree, an optimized statement tree is obtained. When all parent nodes in the syntax tree have been traversed, it indicates that the parent nodes and child nodes that can be updated in the syntax tree have been updated, thus obtaining the optimized syntax tree. An inorder traversal can be performed during the syntax tree traversal.

[0051] Example 2

[0052] Furthermore, referring to Figure 2 Based on the above embodiments of this application, in another embodiment of this application, the same or similar content as the above embodiments can be referred to the above description, and will not be repeated hereafter. Based on this, before the step of optimizing the syntax information based on the preset statement optimization information to update the parent node and the child node if the syntax information meets the statement optimization conditions, the data query method includes:

[0053] Step A10: Determine whether the query engine of the SQL query statement meets the preset engine conditions;

[0054] Step A20: If the query engine of the SQL query statement meets the preset engine conditions, then determine whether the syntax information meets the statement optimization conditions based on the general optimization information and the preset engine optimization information.

[0055] Step A30: If the syntax information matches the general comparison information of the general optimization information or matches the preset engine comparison information of the preset engine optimization information, then it is determined that the syntax information satisfies the statement optimization condition.

[0056] Step A40: If the query engine of the SQL query statement does not meet the preset engine conditions, then determine whether the syntax information meets the statement optimization conditions based on the general optimization information.

[0057] Step A50: If the syntax information matches the general comparison information of the general optimization information, then it is determined that the syntax information satisfies the statement optimization conditions.

[0058] It should be noted that the preset engine condition is used to determine whether the query engine of the SQL query statement is a preset engine. If the query engine of the SQL query statement is not a preset engine, it is only necessary to determine whether the syntax information meets the statement optimization conditions based on the general optimization information. If the query engine of the SQL query statement is a preset engine, it is possible to determine whether the syntax information meets the statement optimization conditions based on the general optimization information and the preset engine optimization information. A preset engine refers to the query engine that needs to be optimized. The preset engine optimization information contains the syntax optimization rules corresponding to the preset engine. In this embodiment, the preset engine can be the ClickHouse query engine. The general optimization information includes general comparison information and general optimization rules. The preset engine optimization information includes preset engine comparison information and preset engine optimization rules. Specifically, the general comparison information represents the general SQL before optimization, and includes a general comparison statement, which is the general SQL before optimization. The general optimization rules represent the general SQL after optimization. The preset engine comparison information represents the engine SQL before optimization, and includes an engine comparison statement, which is the engine SQL before optimization. The preset engine optimization rules include statements representing the engine SQL after optimization. General SQL is SQL that is independent of the query engine, while default SQL is SQL that is related to the default engine.

[0059] As an example, steps A10 to A50 include: determining whether the query engine of the SQL query statement is a preset engine; if the query engine of the SQL query statement is a preset engine, then determining whether the syntax information meets the statement optimization conditions based on the general optimization information and the preset engine optimization information; specifically, determining whether the syntax information meets the statement optimization conditions based on the general optimization information; if the syntax content and node syntax association order of the syntax information match the general comparison information in the general optimization information, then determining that the syntax information meets the statement optimization conditions; if the syntax content or node syntax association order of the syntax information does not match the general comparison information in the general optimization information, then determining whether the syntax information meets the statement optimization conditions based on the preset engine optimization information; if the syntax content or node syntax association order of the syntax information does not match the preset engine comparison information in the preset engine optimization information, then determining that the syntax information does not meet the statement optimization conditions; if the syntax content or node syntax association order of the syntax information matches the preset engine comparison information in the preset engine optimization information, then determining that the syntax information meets the statement optimization conditions. If the query engine of the SQL query statement does not meet the preset engine conditions, then based on the general optimization information, it is determined whether the syntax information meets the statement optimization conditions; if the syntax content and node syntax association order of the syntax information match the general optimization information, then it is determined that the syntax information meets the statement optimization conditions; if the syntax content and node syntax association order of the syntax information do not match the general optimization information.

[0060] The step of optimizing the syntax information based on the preset statement optimization information includes:

[0061] Step B10: If the grammar information matches the general comparison information of the general optimization information, then optimize the grammar information based on the general optimization rules of the general optimization information;

[0062] Step B20: If the syntax information matches the preset engine comparison information of the preset engine optimization information, then the syntax information is optimized based on the preset engine optimization rules of the preset engine optimization information.

[0063] It should be noted that the general optimization rules are rules for optimizing general SQL, while the default engine optimization rules are rules for optimizing engine SQL. The general optimization rules include folding, rewriting, and adjusting the order of syntax content in the syntax information, and the default engine optimization rules also include folding, rewriting, and adjusting the order of syntax content in the syntax information.

[0064] For example, when the grammar information matches the general optimization information, a general matching statement matching the grammar information is determined in the general optimization information. The grammar information is then optimized according to the general optimization rules associated with the general matching statement, and the parent and child nodes corresponding to the grammar information are updated after optimization. If the grammar information matches the preset engine optimization information, an engine matching statement matching the grammar information is determined in the preset engine optimization information. The grammar information is then optimized according to the preset engine optimization rules associated with the engine matching statement, and the parent and child nodes corresponding to the grammar information are updated after optimization. This application embodiment reduces the complexity of general SQL by optimizing it, thereby improving the query performance of SQL statements when performing queries based on SQL. Furthermore, by optimizing the engine SQL of the preset engine, it enables optimization of the engine SQL based on the preset engine optimization information when the query engine cannot optimize the SQL, thereby reducing the complexity of the engine SQL and improving the query performance of SQL statements.

[0065] Furthermore, the step of optimizing the syntax information based on the general optimization rules of the general optimization information includes:

[0066] Step B11: If the syntax information contains a date function, then optimize the date function into a preset date format according to the general optimization rules;

[0067] Step B12: If there is a filtering statement in the syntax information, then optimize the option statement after the filtering statement into a preset simplified option statement according to the general optimization rule;

[0068] Step B13: If there is a connection statement in the syntax information, then the connection statement is optimized into a preset multi-table statement according to the general optimization rules.

[0069] It should be noted that date functions are used to represent dates in SQL query statements. Preset date formats represent optimized date functions, with preset date formats being ">=" and "<". Preset date formats can be used in SQL query statements. Filtering statements are WHERE or HAVING, option statements are CASE WHEN, and preset simplified option statements are IN or NOT IN. Join statements are LEFT JOIN or RIGHT JOIN, and the preset multi-table statement is inner join. The general optimization rules include preset date formats, preset simplified option statements, and preset multi-table statements. Before step B11, the data query method also includes adding restrictions to the syntax information that satisfies the general statement optimization conditions; for example, automatically adding LIMIT to the syntax content in the syntax information. For example, if the syntax information contains the date function YEAR(date), and the detected statement for the date function is YEAR(date) = 2022, then after optimization, the result is: date >= '2022-01-01' AND date < '2023-01-01'. That is, the date range is greater than or equal to January 1, 2022, and less than January 1, 2023. Alternatively, it can be changed to: Date >= '2022-01-01 00:00:00' AND Date < '2023-01-01 00:00:00', that is, the range of dates is greater than or equal to 00:00 on January 1, 2022, and less than 00:00 on January 1, 2023. When there is a filter statement WHERE or HAVING in the syntax information, the CASEWHEN statement after the filter statement is determined. When the CASE WHEN statement is: CASE WHEN COL = 'A' THEN 1 WHEN COL = 'B' THEN 1 ELSE NULL END (if the value of field COL is equal to A or B, it returns 1; otherwise, it returns null), the CASE WHEN statement is optimized to: COL IN('A', 'B'). For example, when the syntax information contains LEFT JOIN or RIGHT JOIN statements, the unoptimized join statement is: "TABLEA LEFT OUTER JOIN TABLEBON XXXWHERE TABLEB.COL2[comparison]constant (table A is joined with table B, and the value of the COL2 field in table B is equal to | less than | greater than a constant)". After optimization, the join statement becomes "TABLEA INNER JOIN TABLEBON XXXWHERE TABLEB.COL2[comparison]constant". This achieves the optimization of the syntax information.

[0070] The step of optimizing the syntax information based on the preset engine optimization rules according to the preset engine optimization information includes:

[0071] Step B21: If there are restrictive statements in the syntax information, then optimize the restrictive statements into preset pushdown restrictive statements according to the preset engine optimization rules;

[0072] Step B22: If there are connection statements in the syntax information, optimize the order of the connection statements according to the preset engine optimization rules, and configure preset added statements for the connection statements;

[0073] Step B23: If there are near-real-time statements in the syntax information, then configure a set function for the near-real-time statements according to the preset engine optimization rules.

[0074] It should be noted that in this embodiment, the grammar information is optimized when it matches the preset engine comparison information of the preset engine optimization information. The constraint statement is LIMIT; when a constraint statement exists in the grammar information, it is pushed down according to the preset engine optimization rules to obtain a preset pushed-down constraint statement. The join statement is JOIN; when a join statement is detected in the grammar information, it is optimized according to the preset engine optimization rules to adjust the order of the join statements; and a preset add statement is configured for the join statement, wherein the preset add statement is GLOBAL JOIN; when a real-time statement exists in the grammar information, a set function is added to the real-time statement, and the set function is argMax (optimization function). For example, when the syntax information contains the LIMIT clause, the original LIMIT statement is "FROM(SELECT XXX FROM TABLEB)LIMIT XXX (retrieves N rows from row M in the result set of a subquery of a single table)". After optimization, it becomes "FROM(SELECT XXX FROM TABLEB LIMIT XXX)", thus pushing down the LIMIT clause. Similarly, when the syntax information contains the JOIN clause, the original JOIN statement is "SMALLTABLEA INNER JOIN BIGTABLE ON XXX (inner join between the smaller and larger tables)". After optimization, it becomes "BIGTABLE INNER JOIN SMALLTABLEA ON XXX".

[0075] The step of rewriting the optimized syntax tree according to the preset engine syntax library of the query engine to obtain an engine statement that matches the query engine includes:

[0076] Step C10: Rewrite the optimized syntax tree according to the preset engine syntax library of the query engine to obtain the rewritten syntax tree of the query engine.

[0077] Step C20: Convert the rewritten syntax tree into the engine statements.

[0078] It should be noted that the preset engine syntax library includes a preset engine rewrite reference set and preset engine rewrite rules. The preset engine rewrite reference set represents the SQL before the syntax tree is rewritten, and the preset engine rewrite rules represent the SQL after the syntax tree is rewritten. Specifically, according to the preset engine rewrite rules, the optimized syntax tree is rewritten into the rewritten syntax tree corresponding to the query engine.

[0079] As an example, steps C10 to C20 include: matching the preset engine rewrite reference set in the preset engine syntax library of the query engine, matching the preset engine rewrite reference syntax corresponding to the optimized syntax tree in the preset engine rewrite reference set, converting the optimized syntax tree into a rewritten syntax tree based on the preset engine rewrite rules associated with the preset engine rewrite reference syntax, and converting the rewritten syntax tree into an engine statement. This embodiment of the application rewrites the syntax tree based on the preset engine syntax library to determine the engine statement, thereby automatically rewriting the SQL query statement into an engine statement supported by the query engine. This allows for querying on the query engine based on the engine statement, eliminating the need for manual coding of the corresponding query engine syntax. Users only need to master SQL2003 syntax to perform data queries across multiple query engines. The preset engine syntax library stores the underlying SQL language related to the query engine.

[0080] Furthermore, in this embodiment, the multi-engine query system includes the ClickHouse query engine, the Gauss200 query engine, and the Kylin query engine. The default engine syntax library for the ClickHouse query engine can be based on the ClickHouse dialect, the default engine syntax library for the Gauss200 query engine can be based on the Gauss200 dialect, and the default engine syntax library for the Kylin query engine is based on the ApahceKylin dialect. For example, the default engine syntax library for the Kylin query engine includes rewriting expressions based on the ApahceKylin dialect, such as rewriting LTRIM to TRIM(LEADING$0) and RTRIM to TRIM(TRAILING$0). Similarly, the default engine syntax library for the Gauss200 query engine includes rewriting CAST and time expressions based on the Gauss200 dialect, such as rewriting TINYINT to SMALLINT. For example, the default engine syntax library for the ClickHouse query engine includes rewrites of CAST, COUNT, TIMESTAMPADD, and time expressions based on the ClickHouse dialect.

[0081] Furthermore, prior to the step of parsing the SQL query statement to obtain the query engine for data querying in the multi-engine query system, the data query method includes:

[0082] Step X10: Configure each query engine in the multi-engine query system via the engine connector.

[0083] It should be noted that the multi-engine query system includes multiple query engines. If data can be queried through multiple query engines in the multi-engine query system, the query engines need to be configured in the multi-engine query system. In this embodiment, each query engine is configured in the multi-engine query system through an engine connector, thereby enabling data querying in the multi-engine query system.

[0084] Furthermore, for a better understanding of this application, refer to Figure 3 , Figure 3 This is a schematic diagram of a multi-engine query system. Figure 3Application 1 in the text represents a user entering an SQL query statement in a multi-engine query system by calling an HTTP interface through Application 1. Application 1 also represents entering an SQL query statement in a multi-engine query system through the JDBC driver of Application 1. After an SQL query statement is entered in the multi-engine query system, the SQL query statement is parsed to determine the corresponding query engine and the syntax tree of the SQL query statement. The syntax tree of the SQL can be optimized based on general optimization information to achieve general SQL optimization. After general SQL optimization, query engine SQL optimization can also be performed. In this embodiment, the Gauss200 query engine and the Kylin query engine do not require configuration of optimization information to optimize the SQL. However, the ClickHouse query engine is the preset engine in this embodiment, and preset engine optimization information needs to be configured for the ClickHouse query engine. Then, the SQL is optimized based on the preset engine optimization information. After completing general SQL optimization and query engine SQL optimization, the SQL is rewritten based on the preset engine syntax library of the query engine, thereby connecting the ClickHouse query engine, the Gauss200 query engine, and the Kylin query engine through the OLAP engine connector. This enables the multi-engine query system to support multiple query engines for data retrieval.

[0085] Example 3

[0086] Reference Figure 4 This application embodiment also provides a data query device, the data query device comprising:

[0087] Parsing module 10 is used to parse SQL query statements to obtain the query engine for data querying in the multi-engine query system;

[0088] The optimization module 20 is used to optimize the syntax tree of the SQL query statement according to preset statement optimization information to obtain an optimized syntax tree. The preset statement optimization information includes general optimization information and preset engine optimization information.

[0089] The rewriting module 30 is used to rewrite the optimized syntax tree according to the preset engine syntax library of the query engine to obtain an engine statement that matches the query engine.

[0090] The query module 40 is used to perform data queries in the query engine based on the engine statement.

[0091] Optionally, the parsing module 10 is further configured to:

[0092] The SQL query statement is parsed to obtain the table object of the SQL query statement;

[0093] The query engine for the SQL query statement is determined by searching a preset object lookup table for a query engine that matches the table object.

[0094] Optionally, the optimization module 20 is further configured to:

[0095] The SQL query statement is converted into a syntax tree. All parent nodes in the syntax tree are traversed to obtain the child nodes associated with the parent nodes to determine the syntax information composed of the parent nodes and the child nodes.

[0096] If the grammatical information satisfies the statement optimization conditions, then the grammatical information is optimized based on the preset statement optimization information to update the parent node and the child node;

[0097] After traversing all parent nodes in the syntax tree, the optimized syntax tree is obtained.

[0098] Optionally, the optimization module 20 is further configured to:

[0099] Determine whether the query engine of the SQL query statement meets the preset engine conditions;

[0100] If the query engine of the SQL query statement meets the preset engine conditions, then based on the general optimization information and the preset engine optimization information, it is determined whether the syntax information meets the statement optimization conditions.

[0101] If the syntax information matches the general comparison information of the general optimization information or the preset engine comparison information of the preset engine optimization information, then the syntax information is determined to satisfy the statement optimization condition.

[0102] If the query engine of the SQL query statement does not meet the preset engine conditions, then based on the general optimization information, it is determined whether the syntax information meets the statement optimization conditions.

[0103] If the grammatical information matches the general comparison information of the general optimization information, then the grammatical information is determined to satisfy the statement optimization conditions.

[0104] Optionally, the optimization module 20 is further configured to:

[0105] If the grammatical information matches the general comparison information of the general optimization information, then the grammatical information is optimized based on the general optimization rules of the general optimization information;

[0106] If the grammar information matches the preset engine comparison information of the preset engine optimization information, then the grammar information is optimized based on the preset engine optimization rules of the preset engine optimization information.

[0107] Optionally, the optimization module 20 is further configured to:

[0108] If the syntax information contains a date function, then the date function is optimized into a preset date format according to the general optimization rules;

[0109] If there is a filter statement in the grammatical information, then the option statement after the filter statement is optimized into a preset simplified option statement according to the general optimization rules;

[0110] If the syntax information contains a join statement, then the join statement is optimized into a preset multi-table statement according to the general optimization rules.

[0111] Optionally, the optimization module 20 is further configured to:

[0112] If there are restrictive statements in the grammatical information, the restrictive statements will be optimized into preset pushdown restrictive statements according to the preset engine optimization rules;

[0113] If there are connection statements in the syntax information, the order of the connection statements is optimized according to the preset engine optimization rules, and preset added statements are configured for the connection statements;

[0114] If the syntax information contains near-real-time statements, then a set of functions are configured for the near-real-time statements according to the preset engine optimization rules.

[0115] Optionally, the rewriting module 30 is further configured to:

[0116] According to the preset engine syntax library of the query engine, the optimized syntax tree is rewritten with syntax rules to obtain the rewritten syntax tree of the query engine;

[0117] The rewritten syntax tree is converted into the engine statements.

[0118] The data query apparatus provided in this application employs the data query method described in the above embodiments, aiming to improve SQL query performance while supporting multiple query engines. Compared with the prior art, the beneficial effects of the data query method provided in this application are the same as those of the data query method provided in the above embodiments, and other technical features in this data query apparatus are the same as those disclosed in the methods of the above embodiments, and will not be repeated here.

[0119] Example 4

[0120] This application provides an electronic device, which can be a playback device. The electronic device includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the data query method described in the above embodiment.

[0121] The following is for reference. Figure 5 The diagram illustrates a structural schematic of an electronic device suitable for implementing embodiments of the present disclosure. The electronic devices in the embodiments of the present disclosure may include, but are not limited to, mobile terminals such as mobile phones, laptops, digital broadcast receivers, PDAs (personal digital assistants), PADs (portable Android devices), PMPs (portable media players), in-vehicle terminals (e.g., in-vehicle navigation terminals), and fixed terminals such as digital TVs and desktop computers. Figure 5 The electronic device shown is merely an example and should not be construed as limiting the functionality and scope of the embodiments disclosed herein.

[0122] like Figure 5 As shown, the electronic device may include a processing unit 1001 (e.g., a central processing unit, a graphics processing unit, etc.), which can perform various appropriate actions and processes according to a program stored in ROM (Read-Only Memory) 1002 or a program loaded from storage device 1003 into RAM (Random Access Memory) 1004. RAM 1004 also stores various programs and data required for the operation of the electronic device. The processing unit 1001, ROM 1002, and RAM 1004 are interconnected via bus 1005. Input / output (I / O) interface 1006 is also connected to the bus.

[0123] Typically, the following systems can be connected to I / O interface 1006: input devices 1007 including, for example, touchscreens, touchpads, keyboards, mice, image sensors, microphones, tachometers, gyroscopes, etc.; output devices 1008 including, for example, LCDs (Liquid Crystal Displays), speakers, vibrators, etc.; storage devices 1003 including, for example, magnetic tapes, hard disks, etc.; and communication devices 1009. The communication devices allow electronic devices to communicate wirelessly or wiredly with other devices to exchange data. Although electronic devices with various systems are shown in the figures, it should be understood that it is not required to implement or possess all the systems shown. More or fewer systems may be implemented alternatively.

[0124] In particular, according to embodiments of this disclosure, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, embodiments of this disclosure include a computer program product comprising a computer program carried on a computer-readable medium, the computer program containing program code for performing the methods shown in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via a communication system, or installed from a storage system, or installed from a ROM. When the computer program is executed by a processing system, it performs the functions defined above in the methods of embodiments of this disclosure.

[0125] The electronic device provided in this application, employing the data query method in Embodiment 1 above, aims to improve SQL query performance while supporting multiple query engines. Compared with the prior art, the beneficial effects of the product traffic data allocation provided in this application embodiment are the same as the beneficial effects of the data query method provided in the above embodiments, and other technical features in this data query device are the same as those disclosed in the methods of the above embodiments, and will not be repeated here.

[0126] It should be understood that various parts of this disclosure can be implemented using hardware, software, firmware, or a combination thereof. In the description of the above embodiments, specific features, structures, materials, or characteristics may be combined in any suitable manner in one or more embodiments or examples.

[0127] The above description is merely a specific embodiment of this application, but the scope of protection of this application is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the scope of the technology disclosed in this application should be included within the scope of protection of this application. Therefore, the scope of protection of this application should be determined by the scope of the claims.

[0128] Example 5

[0129] This embodiment provides a computer-readable storage medium having computer-readable program instructions stored thereon, which are used to execute the data query method in the first embodiment described above.

[0130] The computer-readable storage medium provided in this application embodiment may be, for example, a USB flash drive, but is not limited to electrical, magnetic, optical, electromagnetic, infrared, or semiconductor devices, apparatuses, or any combination thereof. More specific examples of computer-readable storage media may include, but are not limited to: electrical connections with one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable EPROM (Electrical Programmable Read Only Memory) or flash memory, optical fiber, portable compact disk CD-ROM (compact discread-only memory), optical storage devices, magnetic storage devices, or any suitable combination thereof. In this embodiment, the computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution device, apparatus, or apparatus. The program code contained on the computer-readable storage medium may be transmitted using any suitable medium, including but not limited to: wires, optical cables, RF (Radio Frequency), etc., or any suitable combination thereof.

[0131] The aforementioned computer-readable storage medium may be included in an electronic device or may exist independently without being assembled into an electronic device.

[0132] The aforementioned computer-readable storage medium carries one or more programs that, when executed by an electronic device, cause the electronic device to: parse an SQL query statement to obtain a query engine for performing data queries in the multi-engine query system; optimize the syntax tree of the SQL query statement according to preset statement optimization information to obtain an optimized syntax tree, wherein the preset statement optimization information includes general optimization information and preset engine optimization information; rewrite the optimized syntax tree according to the preset engine syntax library of the query engine to obtain an engine statement that matches the query engine; and perform data queries in the query engine based on the engine statement.

[0133] Computer program code for performing the operations of this disclosure can be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, and C++, and conventional procedural programming languages ​​such as the "C" language or similar programming languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including LAN (local area network) or WAN (wide area network)—or can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0134] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of devices, methods, and computer program products according to various embodiments of this application. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in the block diagrams and / or flowcharts, and combinations of blocks in the block diagrams and / or flowcharts, may be implemented using a dedicated hardware-based device that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0135] The modules described in the embodiments of this disclosure can be implemented in software or hardware. The names of the modules do not necessarily limit the functionality of the unit itself.

[0136] The computer-readable storage medium provided in this application stores computer-readable program instructions for executing the above-described data query method, aiming to improve the query performance of SQL while supporting multiple query engines. Compared with the prior art, the beneficial effects of the computer-readable storage medium provided in this application are the same as the beneficial effects of the data query method provided in the above-described embodiments, and will not be repeated here.

[0137] Example 6

[0138] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the steps of the data query method described above.

[0139] The computer program product provided in this application aims to improve SQL query performance while supporting multiple query engines. Compared with the prior art, the beneficial effects of the computer program product provided in this application are the same as those of the data query method provided in the above embodiments, and will not be repeated here.

[0140] The above are merely preferred embodiments of this application and do not limit the patent scope of this application. Any equivalent structural or procedural transformations made using the content of this application's specification and drawings, or direct or indirect applications in other related technical fields, are similarly included within the patent scope of this application.

Claims

1. A data query method, characterized in that, The data query method, applied to a multi-engine query system, includes: Parse the SQL query statement to obtain the query engine in the multi-engine query system where the SQL query statement performs data querying; Based on preset statement optimization information, the syntax tree of the SQL query statement is optimized to obtain an optimized syntax tree, wherein the preset statement optimization information includes general optimization information and preset engine optimization information; The optimized syntax tree is rewritten according to the preset engine syntax library of the query engine to obtain an engine statement that matches the query engine. Data is queried in the query engine based on the engine statement; The step of optimizing the syntax tree of the SQL query statement according to preset statement optimization information to obtain an optimized syntax tree, wherein the preset statement optimization information includes general optimization information and preset engine optimization information, includes: The SQL query statement is converted into a syntax tree. All parent nodes in the syntax tree are traversed to obtain the child nodes associated with the parent nodes to determine the syntax information composed of the parent nodes and the child nodes. Determine whether the query engine of the SQL query statement meets the preset engine conditions; If the query engine of the SQL query statement meets the preset engine conditions, then based on the general optimization information and the preset engine optimization information, it is determined whether the syntax information meets the statement optimization conditions. If the syntax information matches the general comparison information of the general optimization information or the preset engine comparison information of the preset engine optimization information, then the syntax information is determined to satisfy the statement optimization condition. If the query engine of the SQL query statement does not meet the preset engine conditions, then based on the general optimization information, it is determined whether the syntax information meets the statement optimization conditions. If the grammatical information matches the general comparison information of the general optimization information, then the grammatical information is determined to satisfy the statement optimization condition; If the grammatical information satisfies the statement optimization conditions, then the grammatical information is optimized based on the preset statement optimization information to update the parent node and the child node; After traversing all parent nodes in the syntax tree, the optimized syntax tree is obtained.

2. The data query method as described in claim 1, characterized in that, The step of parsing the SQL query statement to obtain the corresponding query engine in the multi-engine query system includes: The SQL query statement is parsed to obtain the table object of the SQL query statement; The query engine for the SQL query statement is determined by searching a preset object lookup table for a query engine that matches the table object.

3. The data query method as described in claim 1, characterized in that, The step of optimizing the syntax information based on the preset statement optimization information includes: If the grammatical information matches the general comparison information of the general optimization information, then the grammatical information is optimized based on the general optimization rules of the general optimization information; If the grammar information matches the preset engine comparison information of the preset engine optimization information, then the grammar information is optimized based on the preset engine optimization rules of the preset engine optimization information.

4. The data query method as described in claim 3, characterized in that, The steps for optimizing the syntax information based on the general optimization rules derived from the general optimization information include: If the syntax information contains a date function, then the date function is optimized into a preset date format according to the general optimization rules; If there is a filter statement in the grammatical information, then the option statement after the filter statement is optimized into a preset simplified option statement according to the general optimization rules; If the syntax information contains a join statement, then the join statement is optimized into a preset multi-table statement according to the general optimization rules.

5. The data query method as described in claim 3, characterized in that, The step of optimizing the syntax information based on the preset engine optimization rules according to the preset engine optimization information includes: If there are restrictive statements in the grammatical information, the restrictive statements will be optimized into preset pushdown restrictive statements according to the preset engine optimization rules; If there are connection statements in the syntax information, the order of the connection statements is optimized according to the preset engine optimization rules, and preset added statements are configured for the connection statements; If the syntax information contains near-real-time statements, then a set of functions are configured for the near-real-time statements according to the preset engine optimization rules.

6. The data query method as described in claim 1, characterized in that, The step of rewriting the optimized syntax tree according to the preset engine syntax library of the query engine to obtain an engine statement that matches the query engine includes: According to the preset engine syntax library of the query engine, the optimized syntax tree is rewritten with syntax rules to obtain the rewritten syntax tree of the query engine; The rewritten syntax tree is converted into the engine statements.

7. An electronic device, characterized in that, The electronic device includes: At least one processor; and a memory communicatively connected to the at least one processor; The memory stores instructions that can be executed by the at least one processor, which, when executed by the at least one processor, enables the at least one processor to perform the steps of the data query method according to any one of claims 1 to 6.

8. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a program that implements the data query method, which is executed by a processor to implement the steps of the data query method as described in any one of claims 1 to 6.

Images