Multi-source heterogeneous data federated query system, guarantee and access method

Abstract

The invention provides a multi-source heterogeneous data federated query system, guarantee and access method. The system comprises a plurality of projects, a computing group management module, a data source management module, a project management module and a query management module. Each item comprises a calculation group and a data source; the calculation groups and the data sources among the items are mutually isolated and do not influence each other; the project management module is used for multi-user isolation and access control; the computing group management module is used for constructing and dynamically adjusting computing groups and guaranteeing mutual isolation of different computing group resources; the data source management module is used for providing addition, deletion, modification, query and distribution management of data sources and supporting wizard-type creation of data source connection; the query management module provides a cross-data-source query editing console. According to the multi-computing-group mode provided by the invention, each computing group comprises a main coordination node and a standby coordination node, so that the resource management is more flexible, the query task is shared by a plurality of computing groups, and a single coordination node is effectively prevented from becoming a performance bottleneck.

Description

Technical Field

[0001] The present invention relates to the field of federated query, and in particular to a multi-source heterogeneous data federated query system, guarantee and access method. Background Art

[0002] In today's era of rapid development of industrial informatization and intelligence, enterprises continue to generate massive amounts of data in production activities and management processes. Due to the different business needs and application scenarios, these data are scattered in many different databases and stored using a variety of data storage technologies, including common relational databases, non-relational databases, and emerging data lakes.

[0003] In such a complex data landscape, existing data processing methods have exposed many defects. When trying to access these widely distributed and heterogeneous data, the efficiency is extremely low, often consuming a lot of time and computing resources, and it is difficult to meet the company's needs for data timeliness and accuracy. In the process of data integration, it is faced with the dilemma of high costs, whether it is labor costs, technical costs or time costs, which has brought a heavy burden to the company. This situation has seriously hindered the pace of digital transformation of enterprises, making it difficult for enterprises to fully tap the value behind the data, unable to respond to market changes in a timely manner, and at a disadvantage in the fierce market competition.

[0004] Through searching patent documents, it was found that the Chinese patent with application number CN202211717003.8 disclosed a multi-data source data query method, system and system. The invention focuses on the unified management and arrangement of metadata, aiming to assist users in easily creating standardized query statements that can span multiple data sources. The patent lacks a refined management and group control mechanism for computing resources; the types of data sources supported are not rich enough, and there is a lack of support for domestic information innovation databases; and the method of metadata arrangement to generate query SQL has certain limitations in flexibility when dealing with complex and changing query requirements.

[0005] In summary, in response to the above-mentioned problems of the existing technology, studying a multi-source heterogeneous data federated query system has become a key task that needs to be solved urgently. Summary of the invention

[0006] In view of the defects in the prior art, the purpose of the present invention is to provide a multi-source heterogeneous data federation query system, guarantee and access method.

[0007] A multi-source heterogeneous data federation query system provided according to the present invention includes: multiple projects, a computing group management module, a data source management module, a project management module and a query management module;

[0008] Each project contains a computing group and a data source. The computing groups and data sources of each project are isolated from each other and do not affect each other.

[0009] Project management module, which uses projects as the basic organizational unit for multi-user isolation and access control;

[0010] The computing group management module is used to build and dynamically adjust computing groups and ensure that resources in different computing groups are isolated from each other;

[0011] The data source management module is used to provide data source addition, deletion, modification, query and allocation management, and supports wizard-based creation of data source connections;

[0012] The query management module provides a query editing console across data sources, supports queries across multiple data sources using unified SQL syntax, and also provides SQL execution history, execution information, and execution plan display pages.

[0013] Preferably, in the computing group management module, the computing group is the actual execution unit of the query, and each computing group is a set of Trino clusters, including two coordination instances and multiple working instances; the computing group is a computing engine independent of the storage, and the data required for the calculation is loaded from the external storage; the computing capacity of the computing group is determined by the resource configuration of the working node and the number of working instances; and the computing resources between different computing groups are isolated from each other and do not affect each other, so as to ensure that project operations using different computing groups do not interfere with each other.

[0014] Preferably, in the computing group management module, the coordination instance is used to receive client requests, parse SQL statements, generate an execution plan, and break the plan into one or more stages, each stage containing one or more tasks, and then assign the tasks to working nodes for execution, and finally summarize the calculation results and return them to the client; the working instance reads data from the corresponding external storage through different connectors, performs calculations on the data, and finally transmits the calculation results to the coordination node.

[0015] Preferably, the computing group management module also has a visual computing group configuration management page, and the user builds the computing group according to the wizard prompts on the page; for the built computing group, the user dynamically adds or deletes working instances according to business needs to cope with changes in query load.

[0016] Preferably, in the data source management module, the computing group obtains data from different storage engines through connectors, and the connectors define the mapping of field types to shield the differences in the underlying field types and realize joint query of data; after the connector is developed and deployed, the corresponding connection information is provided so that the working instance of the computing group can access the data stored in different addresses and different types of data sources through the connection information; the data source is added, deleted, modified, checked and allocated, and the data source is uniformly configured by the administrator and allocated to the project for use; the wizard-style creation of data source connections is supported, without the need to directly edit complex configuration files, thereby improving the usability of system functions.

[0017] Preferably, the connector defines the mapping method of the field type as follows: inherit the BaseJdbcClient class in the trino-base-jdbc package; override the toColumnMapping and toWriteMapping methods to determine the field mapping between the target database and Trino, thereby building a connector that connects the domestically-made, independently controllable database and the commercial database.

[0018] Preferably, the query management module provides a query editing console across data sources, the ability to connect multiple data sources through calculation groups, query catalog information through the show catalog statement, and display it through a tree structure;

[0019] Users can view metadata of tables and views under different storage engines on the same page, and can simultaneously access and operate data from multiple different data sources using a unified SQL syntax compatible with ANSI standards, without having to switch between data sources, thus reducing the complexity of data access and improving the convenience and efficiency of data analysis;

[0020] Provides a display page for SQL execution history, SQL execution information, and execution plan.

[0021] Preferably, the query management module is performed in the following manner:

[0022] Get the query details and status by querying the trino_queries table in the database. If the status is running, access the http interface of the coordination node through the address of the coordination node and the query ID. The request path is / v1 / query / {query_id} to obtain the real-time execution information of the corresponding query ID and display it on the page.

[0023] If the query status is finished, the query details are directly returned to the page for display.

[0024] The present invention also provides a method for ensuring the single point problem of a coordination node in a multi-source heterogeneous data federation query system, comprising the following steps:

[0025] Step S1, using Keepalived and Nginx to build high availability of coordination nodes, Keepalived is an open source software for achieving high availability and load balancing, Nginx is a high-performance HTTP and reverse proxy server, and Nginx is often used to process high concurrent requests; Keepalived and Nginx are installed and configured on two servers respectively, different virtual router identifiers (VRIDs) and virtual IP addresses (VIPs) are configured for Keepalived, and the health check function is enabled to regularly check the status of Nginx to ensure that only healthy nodes are provided with services;

[0026] Step S2: When deploying the computing group, deploy the coordination nodes on two different hosts, add a new configuration file in the Nginx configuration folder, set the access IP and port of the two coordination nodes, and set one of the nodes as backup. After the configuration is completed, execute the nginx-s reload command to make the configuration effective.

[0027] Step S3, when deploying the working node, configure the discovery.uri parameter in config.properties to the virtual IP configured in keepalived and the port configured in Nginx to achieve high availability of the coordination node and Nginx.

[0028] The present invention also provides a method for accessing SQL execution history when ensuring active / standby switching, which is applied to a multi-source heterogeneous data federation query system and includes the following steps:

[0029] By implementing a custom listening plug-in, the listening plug-in inherits Trino's EventListener class and implements the queryCreated method. When a query is accepted and created by the coordination node, the query ID and the corresponding coordination node and other information are recorded and saved in the trino_queries table of the database, and the query_state field value is set to running.

[0030] When the SQL execution is completed, the queryCompleted method is implemented to find the record of the previous step through the query ID, update the complete information of the SQL execution to the database, and update the value of the query_state field to finished. Finally, it is displayed uniformly through the query history page in the query management function.

[0031] Compared with the prior art, the present invention has the following beneficial effects:

[0032] 1. The present invention provides a visual web management page, which greatly reduces the complexity of computing group management and data source configuration. For example, a configuration wizard is provided on the computing group management page, which can guide users to gradually complete the cluster scale, including the determination of the number of coordination nodes and working nodes, as well as the selection of core parameters such as hardware configuration and network settings; while the data source configuration provides a graphical interface, and users can quickly complete the addition and configuration of data sources through simple operations such as clicking.

[0033] 2. The present invention manages resources such as computing groups and data sources by means of project isolation. Different projects use independent computing groups (such as Figure 1 It effectively solves the problems of resource competition, data security, and management difficulty in complex multi-user scenarios, and builds an independent, secure, reliable, and easy-to-manage data analysis space for different business teams or projects.

[0034] 3. The present invention uses a multi-computing group mode to break through the performance bottleneck of the coordination node, and realizes the dynamic adjustment of the computing power of a single computing group through the dynamic expansion and contraction characteristics of the working node; realizes the master-slave deployment of the coordination node through the Nginx+keepalived service to solve the single point problem of the coordination node; and through the development of an event monitoring plug-in, the query history is uniformly stored and displayed to solve the problem of the loss of SQL execution history between coordination nodes when the master-slave switch occurs.

[0035] 4. The open source Trino does not provide an HA deployment solution. Only one coordination node is deployed in a cluster. The coordination node has performance bottlenecks and single point problems. The multi-computing group mode proposed in the present invention, each computing group includes two coordination nodes, the primary and the standby. Compared with sharing a large Trino cluster, the present invention is more flexible in resource management, and the query tasks are shared by multiple computing groups, which effectively avoids a single coordination node from becoming a performance bottleneck. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] Other features, objects and advantages of the present invention will become more apparent from the detailed description of non-limiting embodiments made with reference to the following drawings:

[0037] Figure 1 A schematic diagram of computing resource isolation in an embodiment of the present invention;

[0038] Figure 2 The figure is a schematic diagram of high-availability deployment of a computing group coordination node in an embodiment of the present invention. DETAILED DESCRIPTION

[0039] The present invention is described in detail below in conjunction with specific embodiments. The following embodiments will help those skilled in the art to further understand the present invention, but are not intended to limit the present invention in any form. It should be noted that, for those of ordinary skill in the art, several changes and improvements can also be made without departing from the concept of the present invention. These all belong to the protection scope of the present invention.

[0040] The present invention innovatively utilizes Trino's excellent cross-database query capability and carefully designs a query system specifically for cross-heterogeneous data sources. This system aims to break the existing dilemma and provide enterprises with an ideal solution that is both efficient and low-cost. Through this system, data sources scattered in multiple different geographical locations and using different data storage technologies can be quickly integrated and accessed conveniently and quickly.

[0041] Embodiment 1:

[0042] This embodiment provides a multi-source heterogeneous data federation query system, including: multiple projects, a computing group management module, a data source management module, a project management module and a query management module.

[0043] Each project contains computing groups and data sources; computing groups and data sources between projects are isolated from each other and do not affect each other. Projects are the basic organizational units of the system and the main boundaries for multi-user isolation and access control. Users use computing groups and data sources within a project to perform federated query operations.

[0044] The project management module uses the project as the basic organizational unit for multi-user isolation and access control.

[0045] The computing group management module is used to build and dynamically adjust computing groups and ensure that resources in different computing groups are isolated from each other.

[0046] Figure 1 Schematic diagram of computing resource isolation in an embodiment of the present invention.

[0047] like Figure 1 As shown in the figure, the computing group is the actual execution unit of the query. Each computing group is a set of Trino clusters, including two coordination instances and multiple working instances. The computing group is a computing engine independent of the storage, and the data required for the calculation is loaded from the external storage. The computing capacity of the computing group is determined by the resource configuration of the working node and the number of working instances. The computing resources between different computing groups are isolated from each other and do not affect each other, so as to ensure that the project operations using different computing groups do not interfere with each other.

[0048] The coordination instance is used to receive client requests, parse SQL statements, generate execution plans, and break the plans into one or more stages. Each stage contains one or more tasks. The tasks are then assigned to working nodes for execution, and finally the calculation results are summarized and returned to the client. The working instance reads data from the corresponding external storage through different connectors, calculates the data, and finally transmits the calculation results to the coordination node.

[0049] The computing group management module also has a visual computing group configuration management page. Users build computing groups according to the wizard prompts on the page. For the built computing groups, users can dynamically add or delete working instances according to business needs to cope with changes in query load.

[0050] The data source management module is used to provide data source addition, deletion, modification, query and allocation management, and supports wizard-style creation of data source connections to facilitate work instances to access data from different data sources.

[0051] Specifically, the computing group obtains data from different storage engines through connectors. The connectors define the mapping of field types to mask the differences in the underlying field types and implement joint queries of data. After the connector is developed and deployed, the corresponding connection information is provided so that the working instance of the computing group can access data stored in different addresses and different types of data sources through the connection information. Perform addition, deletion, modification, query and allocation management on data sources, and the data sources are uniformly configured by the administrator and allocated to the project for use. Supports wizard-style creation of data source connections without the need to directly edit complex configuration files, thereby improving the ease of use of system functions.

[0052] In this embodiment, the connector defines the mapping method of the field type as follows: inherit the BaseJdbcClient class in the trino-base-jdbc package; override the toColumnMapping and toWriteMapping methods to determine the field mapping between the target database and Trino, thereby building a connector that connects the domestically produced, independently controllable database and the commercial database.

[0053] The query management module provides a query editing console across data sources, supports queries across multiple data sources using unified SQL syntax, and also provides SQL execution history, execution information, and execution plan display pages.

[0054] Specifically, the query management module provides a query editing console across data sources, the ability to connect multiple data sources through calculation groups, query catalog information through the show catalog statement, and display it in a tree structure;

[0055] Users can view the metadata of tables and views under different storage engines on the same page and write SQL for query. They can also access and operate data from multiple different data sources at the same time with a unified SQL syntax compatible with ANSI standards, without switching between data sources, reducing the complexity of data access and improving the convenience and efficiency of data analysis;

[0056] Provides a display page for SQL execution history, SQL execution information, and execution plan.

[0057] In this embodiment, the method of querying the management module is:

[0058] Get the query details and status by querying the trino_queries table in the database. If the status is running, access the http interface of the coordination node through the address of the coordination node and the query ID. The request path is / v1 / query / {query_id} to obtain the real-time execution information of the corresponding query ID and display it on the page.

[0059] If the query status is finished, the query details are directly returned to the page for display.

[0060] Embodiment 2:

[0061] Figure 2 The figure is a schematic diagram of high-availability deployment of a computing group coordination node in an embodiment of the present invention.

[0062] like Figure 2 As shown, this embodiment provides a method for ensuring the single point problem of coordination nodes in a multi-source heterogeneous data federation query system, and adopts the above-mentioned embodiment 1 to be applied to a multi-source heterogeneous data federation query system, including the following steps:

[0063] Step S1, using Keepalived and Nginx to build high availability of coordination nodes, Keepalived is an open source software for achieving high availability and load balancing, Nginx is a high-performance HTTP and reverse proxy server, Nginx is often used to process high concurrent requests; Keepalived and Nginx are installed and configured on two servers respectively, different virtual router identifiers (VRIDs) and virtual IP addresses (VIPs) are configured for Keepalived, and the health check function is enabled to regularly check the status of Nginx to ensure that only healthy nodes are provided with services.

[0064] Step S2: When deploying the computing group, deploy the coordination nodes on two different hosts, add a new configuration file in the Nginx configuration folder, set the access IP and port of the two coordination nodes, and set one of the nodes as backup. After the configuration is completed, execute the nginx-s reload command to make the configuration effective.

[0065] Step S3, when deploying the working node, configure the discovery.uri parameter in config.properties to the virtual IP configured in keepalived and the port configured in Nginx to achieve high availability of the coordination node and Nginx.

[0066] Embodiment 3:

[0067] The present embodiment provides a method for accessing the SQL execution history during the master-slave switching in a multi-source heterogeneous data federated query system. The above-mentioned embodiment 1 is adopted to be applied to the multi-source heterogeneous data federated query system. In the master-slave mode, when the master coordination node hangs up, the previous SQL execution history cannot be seen on the page of the backup coordination node. The present invention provides a corresponding guarantee method to save the SQL execution information to the database in real time, and then access the SQL history through a unified page.

[0068] Specifically, the method for ensuring access to SQL execution history during active / standby switching includes the following steps:

[0069] By implementing a custom listening plug-in, the listening plug-in inherits Trino's EventListener class and implements the queryCreated method. When the query is accepted and created by the coordination node, the query ID and the corresponding coordination node and other information are recorded and saved in the trino_queries table of the database, and the query_state field value is set to running.

[0070] When the SQL execution is completed, the queryCompleted method is implemented to find the record of the previous step through the query ID, update the complete information of the SQL execution to the database, and update the value of the query_state field to finished. Finally, it is displayed uniformly through the query history page in the query management function.

[0071] Those skilled in the art know that, in addition to realizing the system and its various devices, modules, and units provided by the present invention in a purely computer-readable program code, it is entirely possible to realize the same functions in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers, and embedded microcontrollers by logically programming the method steps. Therefore, the system and its various devices, modules, and units provided by the present invention can be considered as a hardware component, and the devices, modules, and units included therein for realizing various functions can also be regarded as structures within the hardware component; the devices, modules, and units for realizing various functions can also be regarded as both software modules for realizing the method and structures within the hardware component.

[0072] The above describes the specific embodiments of the present invention. It should be understood that the present invention is not limited to the above specific embodiments, and those skilled in the art can make various changes or modifications within the scope of the claims, which does not affect the essence of the present invention. In the absence of conflict, the embodiments of the present application and the features in the embodiments can be combined with each other arbitrarily.

Claims

1. A multi-source heterogeneous data federation query system, characterized in that: include: Multiple projects, computing group management module, data source management module, project management module and query management module; Each project contains a computing group and a data source. The computing groups and data sources of each project are isolated from each other and do not affect each other. The project management module uses the project as a basic organizational unit for multi-user isolation and access control; The computing group management module is used to construct and dynamically adjust the computing groups and ensure that resources of different computing groups are isolated from each other; The data source management module is used to provide the management of adding, deleting, modifying, checking and allocating the data source, and supports wizard-style creation of data source connections; The query management module provides a query editing console across data sources, supports querying across multiple data sources through unified SQL syntax, and also provides SQL execution history, execution information and execution plan display pages.

2. A multi-source heterogeneous data federation query system according to claim 1, characterized in that: In the computing group management module, the computing group is the actual execution unit of the query, and each computing group is a set of Trino clusters, including two coordination instances and multiple working instances; the computing group is a computing engine independent of storage, and the data required for calculation is loaded from external storage; the computing capacity of the computing group is determined by the resource configuration of the working node and the number of the working instances; and the computing resources between different computing groups are isolated from each other and do not affect each other, so as to ensure that project operations using different computing groups do not interfere with each other.

3. A multi-source heterogeneous data federation query system according to claim 2, characterized in that: In the computing group management module, the coordination instance is used to receive client requests, parse SQL statements, generate execution plans, and decompose the plans into one or more stages, each stage containing one or more tasks, and then assign tasks to working nodes for execution, and finally summarize the calculation results and return them to the client; The working instance reads data from corresponding external storage through different connectors, performs calculations on the data, and finally transmits the calculation results to the coordination node.

4. A multi-source heterogeneous data federation query system according to claim 1, characterized in that: The computing group management module also has a visual computing group configuration management page, and the user builds a computing group according to the wizard prompts on the page; for the built computing group, the user dynamically adds or deletes the working instance according to business needs to cope with changes in query load.

5. A multi-source heterogeneous data federation query system according to claim 1, characterized in that: In the data source management module, the computing group obtains data from different storage engines through connectors. The connectors define the mapping of field types to shield the differences in underlying field types and implement joint query of data. After the connector is developed and deployed, the corresponding connection information is provided so that the working instance of the computing group can access data stored in different addresses and different types of data sources through the connection information. Perform addition, deletion, modification, query and allocation management on data sources, and data sources are uniformly configured by administrators and allocated to projects for use; Supports wizard-style creation of data source connections without the need to directly edit complex configuration files, thereby improving the usability of system functions.

6. A multi-source heterogeneous data federation query system according to claim 5, characterized in that: The connector defines the mapping method of the field type as follows: inherit the BaseJdbcClient class in the trino-base-jdbc package; override the toColumnMapping and toWriteMapping methods to determine the field mapping between the target database and Trino, thereby building a connector that connects the domestically-made, independently controllable database and the commercial database.

7. A multi-source heterogeneous data federation query system according to claim 1, characterized in that: The query management module provides a query editing console across data sources, the ability to connect multiple data sources through calculation groups, query catalog information through the showcatalog statement, and display it in a tree structure; Users can view metadata of tables and views under different storage engines on the same page, and can simultaneously access and operate data from multiple different data sources using a unified SQL syntax compatible with ANSI standards, without having to switch between data sources, thus reducing the complexity of data access and improving the convenience and efficiency of data analysis; Provides a display page for SQL execution history, SQL execution information, and execution plan.

8. A multi-source heterogeneous data federation query system according to claim 7, characterized in that: The method of querying the management module is: Get the query details and status by querying the trino_queries table in the database. If the status is running, access the http interface of the coordination node through the address of the coordination node and the query ID. The request path is / v1 / query / {query_id} to obtain the real-time execution information of the corresponding query ID and display it on the page. If the query status is finished, the query details are directly returned to the page for display.

9. A method for ensuring single-point problems of coordination nodes, using a multi-source heterogeneous data federation query system as claimed in any one of claims 1 to 8, characterized in that: The steps include: Step S1, using Keepalived and Nginx to build high availability of coordination nodes, wherein Keepalived is open source software for achieving high availability and load balancing, and Nginx is a high-performance HTTP and reverse proxy server, which is often used to process high concurrent requests; Keepalived and Nginx are installed and configured on two servers respectively, different virtual router identifiers (VRIDs) and virtual IP addresses (VIPs) are configured for Keepalived, and the health check function is enabled to regularly check the status of Nginx to ensure that only healthy nodes are provided with services; Step S2: When deploying the computing group, deploy the coordination nodes on two different hosts, add a new configuration file in the Nginx configuration folder, set the access IP and port of the two coordination nodes, and set one of the nodes as backup. After the configuration is completed, execute the nginx-s reload command to make the configuration effective. Step S3, when deploying the working node, configure the discovery.uri parameter in config.properties to the virtual IP configured in keepalived and the port configured in Nginx to achieve high availability of the coordination node and Nginx.

10. A method for accessing SQL execution history during active / standby switching, using a multi-source heterogeneous data federation query system as claimed in any one of claims 1 to 8, characterized in that: The steps include: By implementing a custom listening plug-in, the listening plug-in inherits Trino's EventListener class and implements the queryCreated method. When a query is accepted and created by a coordination node, the query ID and the corresponding coordination node and other information are recorded and saved in the trino_queries table of the database, and the query_state field value is set to running. When the SQL execution is completed, the queryCompleted method is implemented to find the record of the previous step through the query ID, update the complete information of the SQL execution to the database, and update the value of the query_state field to finished. Finally, it is displayed uniformly through the query history page in the query management function.

Images