Multi-data-source unified query system and method

Abstract

The invention relates to the field of data query, in particular to a multi-data-source unified query system and method.The system comprises a metadata layer used for caching and managing mapping configuration information between a logic theme and a physical data source; the application layer serves as an interaction entrance of the system and is used for receiving the query task and returning query results which correspond to the query task and are uniform in format; the analysis layer is used for carrying out analysis and semantic verification on the query task based on the mapping configuration information to generate a logic execution plan; a dialect difference processing mechanism for different heterogeneous data sources is built in the adaptation layer, and the adaptation layer is used for converting the logic execution plan into a structured query instruction compatible with a target data source; and the driving layer is used for executing the structured query instruction and returning a query result to the application layer. Compared with the prior art, the system can adapt to different database dialects and is higher in compatibility.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data query. More particularly, the present application relates to a multi-data-source unified query system and method. BACKGROUND

[0002] With the deepening of enterprise digital transformation, the amount of data query related tasks has shown explosive growth. In order to meet the data storage and query requirements in different business scenarios, enterprises usually build complex data warehouse systems to store and query different types of data. For example, using MySQL to store high-frequency concurrent business transaction data, using ClickHouse or Doris to store massive logs to support real-time analysis, using Presto or Trino for cross-source federated query, etc. However, this heterogeneous multi-data-source architecture brings serious challenges to the development and maintenance of upper-layer business applications, mainly in the following aspects: Firstly, in the traditional development mode, the application side usually directly introduces the driver package of a specific database and writes SQL statements specific to the database. This means that the business logic is directly dependent on the specific physical storage selection. Once the underlying database needs to be replaced due to business development, for example, from MySQL to Doris, the developer must modify a large amount of business code, recompile, test and release, with high maintenance cost, and the system has poor flexibility and scalability.

[0003] Secondly, although most databases support standard SQL syntax, there are obvious differences in function definition, data type, paging syntax, and reference identifier in specific implementation, i.e. compatibility barriers. Under this compatibility barrier, the query result object structures returned by different drivers are different in the data query process, leading to system crashes due to type conversion errors or precision loss when the application side processes cross-library query results, and the consistency and accuracy of data are difficult to guarantee.

[0004] Therefore, the existing multi-data-source query system has the technical problems of weak flexibility and poor compatibility. SUMMARY

[0005] To solve the technical problems of weak flexibility and poor compatibility of the above multi-data-source query system, the present application discloses a multi-data-source unified query system and method.

[0006] In a first aspect, the present application discloses a multi-data-source unified query system, comprising: a metadata layer for caching and managing mapping configuration information between logical topics and physical data sources; an application layer as an interactive entrance of the system, for receiving query tasks and returning query results in a unified format corresponding to the query tasks; The analysis layer is configured to analyze and perform semantic verification on the query task based on the mapping configuration information, and generate a logical execution plan; The adaptation layer is configured to have a dialect difference processing mechanism for different heterogeneous data sources, and is configured to convert the logical execution plan into a structured query instruction compatible with the target data source. The driving layer is configured to execute the structured query instruction and return the query result to the application layer.

[0007] Beneficial effects: The system has three core architectures, namely the analysis layer, the adaptation layer and the driving layer, which correspond to the functions of logical execution plan generation, adaptation conversion and driving. Through the above three core architectures, the system supports the compatibility query and return of the heterogeneous data source. The application layer serves as a unified entrance, shielding the protocol and syntax differences of the underlying heterogeneous data source, so that the business end does not need to pay attention to the switching of the underlying database, realizes zero-intrusion expansion, reduces the maintenance cost of the system and improves the scalability of the architecture. Compared with the prior art, the system solves the technical problems of weak flexibility and poor compatibility of the existing multi-data source query system.

[0008] Preferably, the adaptation layer comprises: The dialect routing unit is configured to identify the type of the target data source and load the corresponding dialect strategy. The syntax rewriting unit is configured to traverse the logical execution plan and perform the following steps in each traversed logical execution plan: According to the dialect strategy, the logical execution plan is rewritten into a structured query instruction supported by the target data source.

[0009] Preferably, the driving layer comprises a driving manager and a plurality of driving plug-ins: The driving manager is configured to maintain an independent class loader for each driving plug-in, and dynamically load the driving plug-in according to the connector configuration of the metadata layer. The driving plug-in is configured to execute the structured query instruction in the isolated environment provided by the class loader.

[0010] Preferably, the application layer comprises a data standardization module. The data standardization module defines a unified data type. When receiving the query result returned by the driving layer, the data standardization module maps and converts the heterogeneous data type of the query result to the unified data type according to the field mapping relationship predefined by the metadata layer, to obtain a query result in a unified format.

[0011] In a second aspect, the application discloses a multi-data source unified query method, which adopts the multi-data source unified query system of the first aspect. The method comprises: In response to the receiving of the query task, the query task is parsed according to preset mapping configuration information to generate a logical execution plan; According to a preset dialect difference processing mechanism, the logical execution plan is converted into a target data source compatible structured query instruction; The structured query instruction is executed, and a corresponding and unified format query result is returned. Preferably, the conversion of the logical execution plan into the target data source compatible structured query instruction comprises: A general instruction node in the logical execution plan is identified; It is judged whether there is a difference between a function or operator in the general instruction node and a syntax definition of the target data source; If not, the logical execution plan is rewritten into a structured query instruction supported by the target data source by using a preset dialect strategy.

[0012] Preferably, the returning of the corresponding and unified format query result comprises: An original result set returned after the execution of the structured query instruction is obtained; Based on a preset global type mapping table, heterogeneous data types of each field in the original result set are identified; For a numerical type in the heterogeneous data types, different precision floating point numbers are uniformly converted into high-precision fixed point numbers.

[0013] Preferably, if the logical execution plan contains a paging instruction, the method of the present application further comprises an adaptation step for the paging instruction query: It is judged whether the target data source supports a standard paging syntax; If not, a nested subquery or a row number filtering condition is generated to generate a structured query instruction with equivalent paging logic.

[0014] Preferably, before the execution of the structured query instruction, the method of the present application further comprises: Connector configuration in the metadata layer is read; A drive plug-in corresponding to the target data source is dynamically loaded through an independent class loader; In an isolated environment constructed by the class loader, the drive plug-in is called to establish a connection with a physical data source.

[0015] Preferably, the target data source includes but is not limited to MySQL, Presto and / or Doris.

[0016] The present application has the following advantages: (1) Compared with the prior art, the system of the present application can adapt to different database dialects and has stronger compatibility.

[0017] (2) Compared with existing technologies, the above application layer serves as a unified entry point, shielding the protocol and syntax differences of the underlying heterogeneous data sources. This eliminates the need for the business side to focus on switching the underlying database, achieving zero-intrusion extension, reducing system maintenance costs, and improving the scalability of the architecture.

[0018] (3) Compared with the prior art, the system of the present invention has functions such as metadata caching, connection pool reuse and parallel loading, and has better performance. Attached Figure Description

[0019] The above and other objects, features, and advantages of exemplary embodiments of the present invention will become readily apparent upon reading the following detailed description with reference to the accompanying drawings. In the drawings, several embodiments of the invention are illustrated by way of example and not limitation, and like or corresponding reference numerals denote like or corresponding parts, wherein: Figure 1 This is a schematic diagram of the structure of the unified query system for multiple data sources in Embodiment 1 of the present invention; Figure 2 This is a flowchart of the unified query method for multiple data sources in Embodiment 2 of the present invention. Detailed Implementation

[0020] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0021] The specific embodiments of the present invention will now be described in detail with reference to the accompanying drawings.

[0022] Example 1 like Figure 1 As shown in the figure, this embodiment discloses a unified query system for multiple data sources, including a metadata layer, an application layer, a parsing layer, an adaptation layer, and a driver layer.

[0023] The metadata layer is used to cache and manage the mapping configuration information between logical topics and physical data sources. It includes a metadata manager, a topic definition module, materialized view configurations carrying the aforementioned mapping configuration information, connector configurations, and driver mappings. In this embodiment, the metadata manager caches and manages all metadata. The topic definition module stores the topic definitions for various business logic views. Connector configurations and driver mappings serve as credentials for loading driver plugins.

[0024] The application layer, serving as the interaction entry point for the entire system, receives query tasks and returns query results in a unified format. It should be noted that in this embodiment, the target data sources for multi-source compatible queries supported by this system are primarily MySQL, Presto, and / or Doris. To output query results in a unified format, the application layer designs a data standardization module, which defines a unified data type. When the data standardization model receives the query results returned by the driver layer, it maps and converts the heterogeneous data types of the query results into a unified data type according to the predefined field mapping relationships in the metadata layer, thus obtaining query results in a unified format. More specifically, the aforementioned data standardization module defines a unified type mapping table to map to the native types of different databases. For example, this table (excerpt) can be:

[0025] It should be noted that the above type mapping table does not represent the mapping of all data types. In actual processing, the mapping types need to be extended according to the specific scenario. After the above type mapping table is defined, when the query result is returned from the next level, the data type is converted according to the mapping relationship, thereby outputting query results in a unified data format.

[0026] As one of the core layers of the system in this embodiment, the parsing layer is used to parse and semantically validate query tasks based on mapping configuration information, and generate a logical execution plan. It should be noted that the parsing layer includes a built-in ANTLR4-based SQL parser, SQL semantic analyzer, and logical SQL builder to generate the aforementioned logical execution plan.

[0027] More specifically, an ANTLR4-based SQL parser is a parsing tool built using the ANTLR4 framework, specifically designed for parsing query tasks (natural language). It transforms a query task into an Abstract Syntax Tree (AST) according to predefined grammar rules, facilitating subsequent traversal, analysis, or transformation operations. After the SQL parser generates the corresponding AST, the SQL semantic analyzer traverses the AST in visitor mode and constructs the corresponding LogicalSql object. Then, the logical SQL builder combines the aforementioned LogicalSql object to rewrite the logical SQL, obtaining the logical execution plan.

[0028] The adaptation layer, also one of the core layers of the system in this embodiment, has a built-in dialect difference processing mechanism for different heterogeneous data sources, which is used to convert the logical execution plan into a structured query instruction compatible with the target data source.

[0029] Specifically, the aforementioned adaptation layer includes a dialect routing unit and a syntax rewriting unit. The dialect routing unit identifies the type of the target data source and loads the corresponding dialect strategy. The syntax rewriting unit iterates through the logical execution plan and performs the following steps in each iterated logical execution plan: rewriting the logical execution plan into structured query instructions supported by the target data source according to the dialect strategy.

[0030] It should be explained that the SQL languages ​​of different databases (target data sources) differ significantly. In the adaptation layer, the system uses the visitor pattern for dynamic adaptation. In this embodiment, the dialect routing unit has dialect strategies for multiple databases. For different target data source types, it loads the corresponding dialect strategy. This dialect strategy is a conversion function / script that adapts to the corresponding database. For example, taking the same query task but different target data sources as an example, the dialect conversion strategy can be: For MySQL: / / MysqlExpressionVisitor.java public String visitFunctionCall(FunctionCall function,ExpressionContext context) { if (function.getName().equalsIgnoreCase("DATE_FORMAT")) { / / MySQL: DATE_FORMAT(date, format) return String.format("DATE_FORMAT(%s, %s)", process(function.getArguments().get(0), context), process(function.getArguments().get(1), context)); } } For Presto: / / PrestoExpressionVisitor.java public String visitFunctionCall(FunctionCall function,ExpressionContext context) { if (function.getName().equalsIgnoreCase("DATE_FORMAT")) { / / Presto: date_format(date, format) - function name lowercase return String.format("date_format(%s, %s)", process(function.getArguments().get(0), context), process(function.getArguments().get(1), context)); } } Clearly, in logic involving time conversion, the dialect strategy for MySQL requires strict case sensitivity and the use of native MySQL syntax. However, the dialect strategy for Presto requires forcibly converting the generated physical SQL to lowercase to conform to Presto's function naming conventions.

[0031] It should be noted that the dialect strategy code described above is merely an example. In actual applications, it needs to be predefined based on the number and type of databases, or differences in string concatenation syntax, and then encapsulated into a data transformation engine. Loading the dialect strategy is essentially a selection of a data engine. The adaptation layer relies on the connector configuration stored in the metadata layer to match and identify the corresponding target data source type and other related information, specifically the database host address, port, database name, username, password, and connection pool configuration. Once this information is matched / verified, the corresponding data transformation engine is invoked to rewrite the logical execution plan into physical SQL (structured query instructions) supported by the target data source.

[0032] The driver layer, also a core layer of the system in this embodiment, includes a driver manager and multiple driver plugins. The driver manager maintains an independent class loader for each driver plugin and dynamically loads the driver plugins based on the connector configuration of the metadata layer. The driver plugins execute structured query instructions within the isolated environment provided by the class loader.

[0033] The driver layer design described above resolves the common "JAR package dependency conflict" problem in multi-data source environments. By maintaining an independent class loader for each plugin through the driver manager, strict isolation of the runtime environment is achieved. Furthermore, the dynamic loading mechanism supports hot-swapping of new data source drivers without restarting the service. Combined with connection pooling reuse technology, this improves system stability and resource utilization.

[0034] Based on the above system design description, the system in this embodiment has at least the following advantages: First, it has a three-layer core architecture (logic-adaptation-driver), which enables consistent query processing independent of data sources, and the process of returning query results is highly stable.

[0035] Secondly, when adding a new data source to the system, only three interfaces need to be adapted, connected, or maintained: the driver plugin in the driver layer, the encapsulated data conversion engine, and the visitor mode switch. In other words, the system in this embodiment has strong scalability and is easy to maintain.

[0036] Third, the system of the present invention has metadata caching, connection pool reuse and parallel loading functions, resulting in superior performance.

[0037] Example 2 like Figure 2 As shown, this embodiment also discloses a unified query method for multiple data sources, which adopts the unified query system for multiple data sources described in Embodiment 1. The method of this embodiment includes: S10: In response to receiving the query task, parse the query task according to the preset mapping configuration information and generate a logical execution plan.

[0038] The parsing process is executed in the parsing layer and is implemented using the SQL parser, SQL semantic analyzer and logical SQL builder described in Example 1, which have been clearly described above and will not be repeated here.

[0039] It should be noted that the syntax definition differs for each target data source. This embodiment introduces the concept of a materialized view to decouple logical tables and physical tables, serving as a means to accelerate parsing in the parsing layer. This materialized view is essentially a logical-to-physical table mapping, used to more quickly determine which database the current logical execution plan should query. The materialized view is stored in a hierarchical structure, specifically including: Logical table definition: Defines the name of the virtual table from a business perspective (such as "Sales Order Table") and the standard fields it contains.

[0040] Physical Candidate Set: For each logical table, multiple physical tables of different granularities are associated as a candidate set. This candidate set includes, but is not limited to: original detail tables, lightly summarized materialized views, highly summarized materialized views, and feature labels. The feature labels mark characteristic parameters for each physical table, including the time range covered, the list of included dimension fields, and data storage cost weights.

[0041] Regarding the table selection process for materialized views, this embodiment also introduces an intelligent table selection engine, which is used to more quickly select the best database for querying. This step specifically involves: S100: The system parses the received query task and extracts the target dimension set (e.g., region, product type) and time span condition (e.g., 2023-01-01 to 2023-01-31) required for the query. Then, it iterates through the above physical candidate set and removes those physical tables that "do not contain the target dimension" or "do not cover the time range required by the query", thus obtaining a valid candidate list.

[0042] S200: Identify the aggregation granularity required for the query task (e.g., daily statistics). The system will compare the statistical granularity of each physical table in the valid candidate list. If the granularity of the physical table is coarser than the query granularity (e.g., the query requires "daily", but the physical table is "monthly"), then the physical table is unusable and will be removed.

[0043] S300: Among the remaining candidate tables that meet the statistical granularity of the above physical tables, scores are performed based on a preset cost estimation model. The principle of "minimum dataset priority" is generally followed, meaning that the materialized view with the fewest rows and the highest degree of aggregation is preferentially selected as the target physical table. Only when a materialized view cannot be matched is the original detail table selected as a fallback.

[0044] S400: After determining the optimal physical table, replace the logical table name in the query task with the actual name of the physical table (e.g., replace Table_Sales with dws_sales_day_agg) to generate the final logical execution plan.

[0045] By using the steps S100-S400 above, a logical execution plan can be generated more quickly and accurately.

[0046] S20: Based on the preset dialect difference handling mechanism, convert the logical execution plan into a structured query instruction compatible with the target data source.

[0047] Specifically, step S20 above includes: S21: Identify the common instruction nodes in the logical execution plan.

[0048] It should be noted that the aforementioned general instruction node refers to an abstract intermediate object that represents specific computational logic (such as functions, operations, or pagination) but has not yet been translated into specific database syntax during the execution of the above logic.

[0049] S22: Determine if there are any differences between the function or operator in the general instruction node and the syntax definition of the target data source.

[0050] The above process involves verifying the consistency of the syntax definitions related to the general instruction nodes, such as capitalization, and verifying that there are differences between the syntax definitions related to the selection of the logical execution plan and the basic syntax conditions of the target data source.

[0051] S23: If not, then the default dialect strategy will be used to rewrite the logical execution plan into structured query instructions supported by the target data source.

[0052] Specifically, after the above verification is passed, the corresponding data transformation engine is called to rewrite the logical execution plan into physical SQL (structured query instructions) supported by the target data source.

[0053] If so, then a different target data source than step S22 is invoked for retesting.

[0054] S30: Execute the structured query instruction and return the corresponding query results in a uniform format.

[0055] Step S30 above includes: S31: Retrieve the original result set returned after executing the structured query command.

[0056] S32: Based on a preset global type mapping table, identify the heterogeneous data types of each field in the original result set.

[0057] It should be explained that the aforementioned global type mapping table is actually the one described in Implementation Example 1. Type mapping table. Heterogeneous data types include floating-point types and integer storage types.

[0058] S33: For numeric types in heterogeneous data types, convert floating-point numbers of different precisions into high-precision fixed-point numbers.

[0059] It should be explained that when multiple target data sources return numerical values ​​of different precision floating-point types, the value can be amplified by multiplying it by a power of 2 and then rounded to convert it into a high-precision fixed-point number, thereby achieving a unified format output of the numerical type. This method can avoid floating-point operation errors.

[0060] Similarly, for string types, timestamp types, integer types, or other heterogeneous data types, the global type mapping table mentioned above can also be used for identification and unification.

[0061] Furthermore, if the logical execution plan contains paging instructions, the method of the present invention also includes an adaptation step for querying paging instructions: Determine whether the target data source supports standard pagination syntax.

[0062] If not, generate nested subqueries or add row number filtering conditions to generate structured query instructions with equivalent pagination logic.

[0063] It should be noted that pagination query refers to dividing a large dataset into multiple pages for batch retrieval, which is used to achieve efficient data loading and display. In the databases adapted to this embodiment, MySQL, Presto, and Doris generally support standard pagination syntax. However, when adding a new database, adding the above script can facilitate faster compatibility. In this case, adding a new database only requires editing one ExpressionVisitor subclass, without modifying the AST node definitions (70+ node classes). That is, the above steps enhance the scalability of the method in this embodiment.

[0064] Furthermore, before executing the structured query instruction, the method in this embodiment also includes: First, the connector configuration in the metadata layer is read. Then, the driver plugin corresponding to the target data source is dynamically loaded through an independent class loader. Finally, in the isolated environment built by the class loader, the driver plugin is called to establish a connection with the physical data source.

[0065] Based on the aforementioned class loader mechanism, the driver manager achieves complete isolation of the plugin runtime environment, effectively avoiding dependency conflicts. Furthermore, thanks to the combination of dynamic loading and connection pooling technology, the method in this embodiment has the ability to dynamically access or update data source drivers during service runtime, thereby optimizing resource utilization while ensuring service continuity.

[0066] Based on the above technical description, the method of the present invention has at least the following technical advantages: Firstly, it achieves a unified query interface, which can automatically adapt to different database dialects and output query results with a unified data structure.

[0067] Secondly, it achieves zero-intrusion extension; adding a new data source only requires implementing the standard interface, without modifying the business code.

[0068] Thirdly, there is a specific intelligent routing mechanism that can automatically select the optimal data source and physical table based on metadata and query characteristics (corresponding to the intelligent table selection engine mentioned above).

[0069] Fourth, it possesses superior performance.

[0070] In the description of this specification, "multiple" means at least two, such as two, three or more, etc., unless otherwise expressly and specifically defined.

[0071] While this specification has shown and described numerous embodiments of the invention, it will be apparent to those skilled in the art that such embodiments are provided by way of example only. Many modifications, alterations, and alternatives will occur to those skilled in the art without departing from the spirit and essence of the invention. It should be understood that various alternatives to the embodiments of the invention described herein may be employed in the practice of this invention.

Claims

1. A unified query system for multiple data sources, characterized in that, include: The metadata layer is used to cache and manage the mapping configuration information between logical topics and physical data sources; The application layer, as the system's interaction entry point, is used to receive query tasks and return query results in a unified format corresponding to the query tasks. The parsing layer is used to parse and semantically validate the query task based on the mapping configuration information, and generate a logical execution plan; The adaptation layer has a built-in dialect difference handling mechanism for different heterogeneous data sources, which is used to convert the logical execution plan into a structured query instruction compatible with the target data source. as well as, The driver layer is used to execute the structured query instructions and return the query results to the application layer.

2. The unified query system for multiple data sources according to claim 1, characterized in that, The adapter layer includes: A dialect routing unit is used to identify the type of the target data source and load the corresponding dialect strategy; A syntax rewriting unit is used to traverse the logical execution plan and perform the following steps in each traversed logical execution plan: Based on the dialect strategy, the logical execution plan is rewritten as a structured query instruction supported by the target data source.

3. The unified query system for multiple data sources according to claim 1, characterized in that, The driver layer includes a driver manager and multiple driver plugins: The driver manager is used to maintain an independent class loader for each driver plugin and dynamically load the driver plugin according to the connector configuration of the metadata layer; The driver plugin is used to execute the structured query instruction in the isolated environment provided by the class loader.

4. The unified query system for multiple data sources according to claim 1, characterized in that, The application layer includes a data standardization module; The data standardization module defines a unified data type; when the data standardization model receives the query results returned by the driver layer, it maps and converts the heterogeneous data types of the query results into the unified data type according to the field mapping relationship predefined by the metadata layer, so as to obtain query results in a unified format.

5. A unified query method for multiple data sources, characterized in that, The method of using the multi-data source unified query system according to any one of claims 1-4 includes: In response to receiving a query task, the query task is parsed according to the preset mapping configuration information to generate a logical execution plan; Based on the preset dialect difference processing mechanism, the logical execution plan is converted into a structured query instruction compatible with the target data source; Execute the structured query instruction and return the corresponding query results in a uniform format.

6. The multi-data source unified query method according to claim 5, characterized in that, Converting the logical execution plan into structured query instructions compatible with the target data source includes: Identify the common instruction nodes in the logical execution plan; Determine whether there are differences between the functions or operators in the general instruction node and the syntax definitions of the target data source; If not, a preset dialect strategy is adopted to rewrite the logical execution plan into structured query instructions supported by the target data source.

7. The multi-data source unified query method according to claim 5, characterized in that, Returns corresponding and uniformly formatted query results, including: Obtain the original result set returned after executing the structured query instruction; Based on a preset global type mapping table, the heterogeneous data types of each field in the original result set are identified; For numeric types in heterogeneous data types, floating-point numbers of different precisions are uniformly converted into high-precision fixed-point numbers.

8. The unified query method for multiple data sources according to claim 5, characterized in that, If the logical execution plan contains pagination instructions, the method further includes an adaptation step for pagination instruction queries: Determine whether the target data source supports standard pagination syntax; If not, generate nested subqueries or add row number filtering conditions to generate structured query instructions with equivalent pagination logic.

9. The multi-data source unified query method according to claim 5, characterized in that, Before executing the structured query instruction, the method further includes: Read the connector configuration from the metadata layer; The driver plugin corresponding to the target data source is dynamically loaded using an independent class loader. In the isolated environment built by the class loader, the driver plugin is invoked to establish a connection with the physical data source.

10. The multi-data source unified query method according to claim 5, characterized in that, The target data sources include, but are not limited to, MySQL, Presto, and / or Doris.

Images