Method for supporting unified management and query of multi-cluster heterogeneous data sources

Abstract

The invention discloses a method for supporting unified management and query of multi-cluster heterogeneous data sources, and belongs to the technical field of big data, and the method comprises the following steps: S1, starting a DataLinks Executor component service; s2, a user uploads a user-defined driver for a specific data source, configures the data source by using the user-defined driver, and then clicks test connection; s3, after the test connection is passed, entering a unified query menu page, selecting a data source passing the test connection, and compiling an SQL statement needing to be executed; s4, after the SQL statements are executed, the executed SQL statements are recorded by the Duid connection pool, data source information in the Duid connection pool is collected by the DataLinks Executor in a timed mode, and summary statistics is carried out on the data source information; and S5, selecting nodes with relatively low loads in the summarized data source information by a load balancing algorithm of the DataLinks Executor, and allocating query tasks to the nodes. The configuration process is greatly simplified, so that a user can manage and operate the multi-cluster data source more conveniently and efficiently, and the problem of connection and management and control of the multi-cluster data source is solved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of big data, and in particular to a method for supporting unified management and query of multi-cluster heterogeneous data sources. BACKGROUND

[0002] In the current technical environment, the existing technology has the following main problems and challenges: (1) Insufficient support for multi-cluster data sources: For multi-cluster data sources, existing technologies often lack a unified management mechanism, and the management of data sources in each cluster is relatively independent, which makes it difficult for the system to flexibly adapt to different cluster environments. When it is necessary to manage and operate different configurations of clusters, developers have to modify and adjust the code for each special configuration of the cluster; (2) Complex connection configuration: Connecting different types of data sources requires different configuration information, such as connection addresses, usernames, passwords, drivers, etc. Existing technologies usually require developers to manually configure this information, and the configuration process is complex and prone to errors. For some complex data sources, such as distributed data sources, additional configuration and tuning are required, which increases the difficulty of development and maintenance; (3) Incomplete load balancing mechanism: Existing load balancing strategies are often single, and cannot be flexibly adjusted according to different data sources and business needs. Common load balancing strategies are mainly based on simple round robin, random or weight distribution, etc. without considering factors such as data source performance, load situation, data distribution, etc. This may cause some data source connectors to be overloaded, while other data source connectors are idle, affecting the overall performance and efficiency of the system; (4) Incomplete management functions: Existing data source management systems often only provide basic connection configuration and management functions, lacking comprehensive management of data sources. For example, they cannot monitor and analyze the connection status, performance indicators, and usage of data sources in real time, and cannot record and trace SQL execution on data sources, etc. This makes it difficult for administrators to fully understand the running status of data sources and timely discover and solve problems; (5) Driver compatibility issues: Existing data source management systems may have significant differences in drivers when facing different versions of databases, and cannot dynamically and flexibly switch data source drivers, resulting in the system being unable to normally connect to data sources. SUMMARY

[0003] The present application aims to overcome the problems of insufficient support for multi-cluster data sources, complex connection configuration, incomplete load balancing mechanism, incomplete management functions, and driver compatibility in the prior art, and provides a method for supporting unified management and query of multi-cluster heterogeneous data sources.

[0004] To solve the above technical problems, the present application provides the following technical solutions: A method for supporting unified management and query of multi-cluster heterogeneous data sources, comprising the following steps: S1: starting a DataLinks Executor component service, which supports multi-node deployment on a single cluster; S2: the user uploads a custom driver for a specific data source and configures the data source using the custom driver, then clicks on the test connection; S3: after the test connection passes, enter the unified query menu page, select the data source that has passed the test connection, and write the SQL statement to be executed; S4: after executing the SQL statement, the Druid connection pool records the executed SQL statement, and the DataLinks Executor periodically collects the data source information in the Druid connection pool and performs summary statistics; S5: the load balancing algorithm of the DataLinks Executor selects the node with lower load from the summarized data source information, and assigns query tasks to each node.

[0005] As a preferred scheme of the present application, step S1 comprises: setting the cluster name in the application.yml configuration file of the DataLinks Executor component service through the spring.cloud.nacos.discovery.cluster-name parameter, and the Nacos server automatically groups and manages the nodes according to the cluster name, realizing service discovery and isolation between clusters.

[0006] As a preferred scheme of the present application, step S2 comprises: S21: after the user creates a custom driver and uploads the corresponding driver file, the DataLinks Web sends the test connection instruction and the driver instruction to the specified cluster of the DataLinks Executor through the Nacos cluster load balancing mechanism; S22: after the DataLinks Executor receives the test connection instruction, it identifies whether the user uses a custom driver; S23: if a custom driver is used, the DataLinks Executor downloads the user-uploaded driver file through HTTP byte stream remote or HDFS distributed storage, and then loads the driver file based on the URLClassLoader class loader; S24: connect the data source through JDBC, and return the test result to the DataLinks Web.

[0007] As a preferred scheme of the present application, the connection of the data source by the JDBC mode in step S24 comprises: The HA mode automatically adds serviceDiscoveryMode=zookeeper; The Kerberos authentication automatically supplements the principal parameter related parameters; The SSL encryption automatically configures ssl=true and certificate related parameters.

[0008] As a preferred scheme of the present application, step S3 comprises: the DataLinks Web sends the SQL statement executed by the user and the basic connection information of the corresponding data source to the DataLinks Executor on the specified cluster through the Nacos cluster load balancing mechanism; After receiving the execution SQL statement instruction, the DataLinks Executor first judges whether the corresponding data source exists a connection pool, if the data source connection pool does not exist, the DataLinks Executor creates a connection pool through the Druid data source connection pool framework, and obtains a connection through the connection pool, and then executes the SQL statement of the user; After the execution is completed, the SQL statement execution result is returned to the DataLinks Web service, and finally the execution result is rendered by the DataLinks Web and displayed to the user.

[0009] As a preferred scheme of the present application, the Nacos cluster load balancing mechanism comprises: firstly, the cluster is marked based on spring.cloud.nacos.discovery.cluster-name in the application.yml configuration of DataLinksExecutor; When the data source executes a query request, the target cluster identifier is carried; The load balancer filters out the instance matching the specified cluster label from the active instance list.

[0010] As a preferred scheme of the present application, the data source information in step S4 is a key connection pool index, comprising: total query times, total update times, and total batch processing times.

[0011] As a preferred scheme of the present application, step S4 further comprises: the DataLinks Executor acquires the CPU usage and the memory usage of the server through the Hutool framework, so as to count the performance index of the server, and records the performance index in the Guava cache of the DataLinks Executor; the DataLinks Web collects the cache content recorded in all DataLinks Executors in time, and displays the cache content to the operation and maintenance personnel for analysis and statistics.

[0012] As a preferred scheme of the present application, the load balancing algorithm formula of the DataLinks Executor in step S5 is: Load score = active connection ratio weight value * (active number of the connection pool of the data source / maximum value of the connection pool of the data source) + connection pool average load weight value * average load value of the connection pool.

[0013] As a preferred scheme of the present application, the active connection ratio weight value is 0.7, and the connection pool average load weight value is 0.3.

[0014] Compared with the prior art, the present application has the following advantages: (1) The present application realizes effective connection and management and control of multi-cluster data sources, successfully shields the difference problems of different cluster data source environment configurations by means of the DataLinks Executor service, and this innovative measure significantly reduces the use complexity of users, greatly simplifies the configuration process, enables users to more conveniently and efficiently manage and operate multi-cluster data sources, and thus solves the problem of connection and management and control of multi-cluster data sources; (2) By adopting the SpringCloud+Nacos micro-service architecture, the present application effectively reduces repeated development work, and this architecture has good compatibility and expansibility, can be easily embedded into other micro-service products, and other micro-service products can realize query of multiple heterogeneous data sources by calling the DataLinks Api, without the need to repeatedly write JDBC codes for data source connection, which not only saves the time and effort of developers and avoids repeated development, but also improves the development efficiency and reduces the development cost; (3) The present application provides the DataLinks Executor monitoring overview function, based on the Druid data source connection pool framework and the Hutool framework, the system can accurately collect key index information, and the operation and maintenance personnel can easily perceive the connection condition of each data source of the DataLinks Executor through the monitoring overview, and timely discover potential problems, which helps the operation and maintenance personnel to quickly locate faults and take response measures in time, so as to guarantee the stability and reliability of the data source connection and reduce the system downtime caused by data source faults; (4) For the case that the version difference of the data source is large and the data source driver needs to be flexibly switched, the application provides a custom driver function, realizes a hot loading mechanism based on URLClassLoader, can dynamically load and switch different versions of database drivers without restarting the system, and this technical innovation effectively solves the problem of incompatibility of different database version drivers, improves the adaptability and flexibility of the system to different database environments; (5) The application realizes a specific load balancing algorithm, which accurately locates low-load nodes by comprehensively calculating multiple dimensions of the data source connection pool indicators, and distributes SQL execution tasks to these nodes, which effectively reduces the idle situation of DataLinks Executor resources, optimizes load balancing, improves the overall resource utilization, makes the system more efficiently process a large number of SQL query requests, and improves the performance and response speed of the system. BRIEF DESCRIPTION OF DRAWINGS

[0015] Various other advantages and benefits will become apparent to those of ordinary skill in the art upon reading the following detailed description of the preferred embodiments. The accompanying drawings are included only serving the purpose of illustrating the preferred embodiments and are not considered as limiting the present application. Throughout the drawings, the same reference numerals represent the same components. In the drawings: Figure 1 A flowchart of a method for supporting unified management and query of multi-cluster heterogeneous data sources according to the application embodiment 1; Figure 2 A software architecture diagram of a method for supporting unified management and query of multi-cluster heterogeneous data sources according to the application embodiment 1; Figure 3 An internal component architecture diagram of a method for supporting unified management and query of multi-cluster heterogeneous data sources according to the application embodiment 1; Figure 4 A data source test connection flowchart of a method for supporting unified management and query of multi-cluster heterogeneous data sources according to the application embodiment 1; Figure 5 A DataLinks Executor index monitoring diagram of a method for supporting unified management and query of multi-cluster heterogeneous data sources according to the application embodiment 1. DETAILED DESCRIPTION

[0016] The technical solutions in the embodiments of the present application will be described clearly and completely below with reference to the accompanying drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, rather than all the embodiments. The components of the embodiments of the present application described and shown in the accompanying drawings can be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of the present application provided in the accompanying drawings is not intended to limit the scope of the claimed present application, but only represents selected embodiments of the present application. Based on the embodiments of the present application, all other embodiments obtained by those skilled in the art without creative work fall within the scope of the present application.

[0017] It should be noted that similar reference numerals and letters represent similar items in the following drawings, so once an item is defined in one drawing, it does not need to be further defined and explained in subsequent drawings. Meanwhile, in the description of the present application, the terms "first", "second", etc. are only used to distinguish the description, and cannot be understood as indicating or implying relative importance, or implying any such actual relationship or order between these entities or operations. In addition, the terms "connected", "connected", etc. can be direct connection between components, or indirect connection via other components.

[0018] Embodiment 1 A method for supporting unified management and query of multi-cluster heterogeneous data sources, as shown in Figure 1 , includes the following steps: S1: DataLinks Executor component service starts: the operation and maintenance personnel starts the DataLinks Executor component service on the machines of each cluster, which supports multi-node deployment on a single cluster, and the software architecture is as shown in Figure 2 , the internal component architecture is as shown in Figure 3 , and the DataLinks Executor component is mainly responsible for SQL query and service monitoring of data sources in the cluster; Specifically, in the application.yml configuration file of the DataLinks Executor component service, the cluster name is set through the spring.cloud.nacos.discovery.cluster-name parameter, and the Nacos server automatically groups and manages the nodes according to the cluster name, realizing service discovery and isolation between clusters. This cluster identification method based on configuration not only ensures the deployment standardization, but also can be seamlessly integrated with the original service discovery mechanism of Nacos.

[0019] S2: Upload custom driver and test connection with data source: Users can upload custom drivers for specific data sources and configure the data source using the custom driver, then click Test Connection. DataLinks Web supports the uploading of custom drivers. When users create custom drivers and upload the corresponding driver files, DataLinks Web sends the test connection information and driver information to the specified cluster of DataLinksExecutor through the Nacos cluster load balancing mechanism. After receiving the test connection instruction, DataLinks Executor identifies whether the user uses a custom driver. If a custom driver is used, DataLinks Executor downloads the user-uploaded driver file through HTTP byte stream remote or HDFS distributed storage, then loads the driver file based on the URLClassLoader class loader. Finally, the data source is connected through JDBC, and the test results are returned. The flowchart of this process is shown in Figure 4 ; Specifically, connecting the data source through JDBC includes: HA mode automatically adds serviceDiscoveryMode=zookeeper; Kerberos authentication automatically supplements principal parameter related parameters; SSL encryption automatically configures ssl=true and certificate related parameters.

[0020] The cross-cluster collaboration mechanism of DataLinks Executor is as follows: Communication protocol: Use the OpenFeign framework to communicate across clusters based on the TCP-based HTTP protocol to ensure the reliability, order, and integrity of data transmission; Service discovery: Achieve cross-cluster service discovery and routing through the Nacos cluster registry center.

[0021] When there are multiple driver versions for a data source, the URLClassLoader is loaded into the custom driver classLoader, the current thread context original classLoader content is obtained Thread.currentThread().getContextClassLoader() (i.e. the default driver), and then the current thread context classLoader is set to the user uploaded driver, and the SQL query is executed Thread.currentThread().setContextClassLoader(jdbcClassLoader), action.get(); When the execution is completed, the original driver classLoader obtained in step one is loaded back into the thread, Thread.currentThread().setContextClassLoader(origintClassLoader).

[0022] Specifically, different configuration information (such as connection address, username, password, driver program, etc.) of different types of data sources has format verification, as well as a default value generation mechanism, and provides file upload method for dependent files to reduce the risk of input errors.

[0023] First, data format verification: data source initialization connection number; minimum connection number; maximum connection number; query timeout; DataLinksExecutor quantity, etc. There is format verification, and the user is required to input numerical type.

[0024] Second, authentication configuration optimization: support KeyTab file upload (Hive / Spark), SSL configuration supports certificate file upload, and automatically hide irrelevant configuration items according to authentication mode.

[0025] Finally, default value optimization: the default trust library type of Kafka SSL is set to the commonly used JKS method, the data source identifier in the "IP@library name" format is automatically generated, and the custom naming ability is retained.

[0026] S3: Data source query and SQL execution: After the user tests the connection, he can enter the unified query menu page, select the data source that has passed the test connection, and write the SQL statement that needs to be executed. DataLinks Web will send the SQL statement executed by the user and the basic connection information of the data source to the specified cluster DataLinks Executor through the Nacos cluster load balancing mechanism.

[0027] After receiving the execution SQL instruction, the DataLinks Executor first determines whether the connection pool of the data source exists. If the connection pool of the data source does not exist, the DataLinks Executor creates a connection pool through the Druid data source connection pool framework, obtains a connection through the connection pool, and then executes the SQL statement of the user. After the execution is completed, the SQL execution result is returned to the DataLinks Web service, and finally the execution result is rendered by the DataLinks Web and displayed to the user; Among them, the implementation method of the Nacos multi-cluster task scheduling fault tolerance mechanism is as follows: Fault detection: automatically identify abnormal nodes through Nacos health check mechanism; Service isolation: manually offline (soft offline, keep process) the fault node in the Nacos console; State synchronization: the offline state is synchronized in real time in the DataLinkWeb management interface to obtain the latest service instance state; Traffic transfer: the load balancer automatically excludes offline nodes and only selects from healthy instances; Recovery mechanism: manually re-online the node after fault repair.

[0028] Specifically, the Nacos cluster load balancing mechanism includes: first, cluster marking based on cluster-name in the application.yml configuration of DataLinksExecutor; When the data source executes a query request, the target cluster identifier is carried; The load balancer selects the instance matching the specified cluster label from the active instance list. This mechanism ensures that requests can be accurately routed to the specified cluster while maintaining load balancing within the cluster.

[0029] S4: DataLinks Executor execution overview monitoring: The operation and maintenance personnel can view the execution overview of the DataLinks Executor through the DataLinks monitoring menu page. When the user executes SQL, Druid connection pool records the executed SQL. The DataLinks Executor collects data source information in the Druid connection pool at regular intervals and performs summary statistics, including total query times, total update times, total batch processing times, and other key connection pool indicators.

[0030] Meanwhile, the DataLinks Executor obtains the CPU usage and memory usage of the server through the Hutool framework to count the performance indicators of the server, which are recorded in the Guava cache of the DataLinks Executor. The DataLinks Web collects the cache content recorded in all DataLinks Executors at regular intervals and displays it to the operation and maintenance personnel for analysis and statistics. The design diagram of this part is shown in FIG. 8. Figure 5 For example, the Druid connection pool default parameters are not automatically adjusted according to the data source type at present, but are uniformly configured according to empirical values. The following are the default configurations: Initial connection number: 5; minimum connection number: 5; maximum connection number: 50; query timeout: 120; and Executor number: 1.

[0031] When the Druid connection pool is edited in the DataLinks Web page, a new connection pool will be created because if the user changes the core connection parameters such as the database, account and password, the SQL query may not be executed if the source connection pool is not destroyed. Therefore, when the user edits the data source, a new connection pool will be created instead of being reused.

[0032] S5: Load balancing algorithm of DataLinks Executor: The load balancing algorithm of the DataLinks Executor aims to select nodes with low load and reasonably allocate query tasks to each node to improve service stability and node utilization. When each data source query connection is connected, the load balancing algorithm will recalculate the load value of each node to update the latest load of each node in real time. The specific algorithm is as follows: Load score = active connection ratio weight value * (connection pool active number of data source / connection pool maximum value of data source) + pool average load weight value * average load value of connection pool.

[0033] The active connection ratio weight value is 0.7, and the pool average load weight value is 0.3. The active connection ratio weight value and the pool average load weight value can be dynamically adjusted by configuring selector.default.active-count-weight and selector.default.pool-load-average-weight in the application.yml configuration file of the DataLinks Web service.

[0034] ​Specifically, the core goal: the algorithm realizes dynamic resource allocation by comprehensively evaluating the real-time activity ratio (activeRatio) of the thread pool and the historical load trend (poolLoadAverage). Among them: activeRatio (weight 0.3) reflects the current instantaneous pressure, but may be noisy due to sudden requests; poolLoadAverage (weight 0.7) smooths short-term fluctuations through moving average, reflecting long-term stability requirements. According to domain experience: according to the design principles of distributed systems, historical load indicators usually have a greater impact on long-term stability.

[0035] Among them, the node with low load is the node with the smallest load selected each time in the optional DataLinksExecutor node; The weight calculation formula is "1-source+0.01"; Where source is the load value of each node, and 0.01 is added to prevent the weight value from being 0, otherwise when all Executors are full load, only the first one will be selected.

[0036] The actual calculation index of the average load value of the connection pool is: The active number index of the connection pool of the specified data source is: 1. Corresponding to Druid connection pool activity number; 2. The maximum value of the connection pool of the specified data source: corresponding to the maximum active number of Druid connection pool; 3. The number of threads waiting to acquire connections of the specified data source: corresponding to Druid waiting thread number; 4. Total active connection number of connection pool: total Druid connection pool active number; 5. Total connection pool maximum value: total Druid connection pool maximum active number; 6. Total number of threads waiting to acquire connections: total Druid waiting thread number.

[0037] The sampling frequency is updated each time the data source initiates a SQL query.

[0038] Among them, the database design, its data relationship is as follows: One user can create multiple data sources; one user has multiple SQL execution records; one user can create multiple custom data source drivers; one custom driver is associated with multiple driver files; one data source is associated with zero or one custom driver; one data source is associated with multiple query consoles; one data source is associated with multiple SQL execution records.

[0039] It should be further noted that various specific technical features described in the above specific embodiments can be combined in any suitable manner, and the disclosure will not be repeated here for various possible combinations.

[0040] In addition, various different embodiments of the disclosure can also be combined with each other as long as they do not contradict the idea of the disclosure, and they should also be considered as disclosed by the disclosure.

Claims

1. A method for supporting unified management and querying of heterogeneous data sources across multiple clusters, characterized in that, Includes the following steps: S1: Start the DataLinks Executor component service, which supports multi-node deployment on a single cluster; S2: The user uploads a custom driver for a specific data source, configures the data source using the custom driver, and then clicks "Test Connection". S3: After the connection test is successful, enter the unified query menu page, select the data source for which the connection test was successful, and write the SQL statement to be executed; S4: After an SQL statement is executed, the Druid connection pool records the executed SQL statement. The DataLinks Executor periodically collects data source information from the Druid connection pool and performs summary statistics. S5: The DataLinks Executor's load balancing algorithm selects nodes with lower loads from the aggregated data source information and assigns query tasks to each node.

2. The method for supporting unified management and querying of heterogeneous data sources across multiple clusters according to claim 1, characterized in that, Step S1 includes: setting the cluster name in the application.yml configuration file of the DataLinks Executor component service through the spring.cloud.nacos.discovery.cluster-name parameter. The Nacos server automatically groups and manages the nodes according to the cluster name to achieve service discovery and isolation between clusters.

3. The method for supporting unified management and querying of heterogeneous data sources across multiple clusters according to claim 2, characterized in that, Step S2 includes: S21: After the user creates a custom driver and uploads the corresponding driver file, DataLinks Web sends the test connection command and driver command to the DataLinks Executor of the specified cluster through the Nacos cluster load balancing mechanism. S22: After receiving the test connection command, the DataLinks Executor identifies whether the user is using a custom driver; S23: If a custom driver is used, the DataLinks Executor downloads the driver file uploaded by the user remotely via HTTP byte stream or HDFS distributed storage, and then loads the driver file based on the URLClassLoader class loader; S24: Connect to the data source via JDBC and return the test results to DataLinks Web.

4. The method for supporting unified management and querying of heterogeneous data sources across multiple clusters according to claim 3, characterized in that, Step S24, connecting to the data source via JDBC, includes: In HA mode, serviceDiscoveryMode=zookeeper is automatically added; Kerberos authentication automatically supplements principal parameters and related parameters; Automatically configure SSL encryption with ssl=true and certificate-related parameters.

5. The method for supporting unified management and querying of heterogeneous data sources across multiple clusters according to claim 2, characterized in that, Step S3 includes: DataLinks Web sends the SQL statement executed by the user and the basic connection information of the corresponding data source to the DataLinks Executor on the specified cluster through the Nacos cluster load balancing mechanism; After receiving the SQL statement execution instruction, the DataLinks Executor first determines whether the corresponding data source has a connection pool. If the data source connection pool does not exist, the DataLinks Executor creates a connection pool through the Druid data source connection pool framework, obtains a connection through the connection pool, and then executes the user's SQL statement. After execution, the SQL statement execution result is returned to the DataLinks Web service, which then renders the execution result and displays it to the user.

6. A method for supporting unified management and querying of heterogeneous data sources across multiple clusters, as described in claim 3 or 5, characterized in that... The Nacos cluster load balancing mechanism includes: firstly, cluster tagging based on the spring.cloud.nacos.discovery.cluster-name configuration in the application.yml file of DataLinksExecutor; When the data source executes a query request, it carries the target cluster identifier; The load balancer filters out instances that match the specified cluster label from the list of active instances.

7. The method for supporting unified management and querying of heterogeneous data sources across multiple clusters according to claim 1, characterized in that, The data source information mentioned in step S4 is key connection pool metrics, including: total number of queries, total number of updates, and total number of batch processing operations.

8. The method for supporting unified management and querying of heterogeneous data sources across multiple clusters according to claim 1, characterized in that, Step S4 further includes: the DataLinks Executor obtains the server's CPU utilization and memory utilization through the Hutool framework, thereby calculating the server's performance indicators and recording the performance indicators in the DataLinks Executor's Guava cache. DataLinks Web periodically collects all cached content recorded in all DataLinks Executors and displays it to operations and maintenance personnel for analysis and statistics.

9. The method for supporting unified management and querying of heterogeneous data sources across multiple clusters according to claim 1, characterized in that, The load balancing algorithm formula for the DataLinks Executor mentioned in step S5 is as follows: Load score = Active connection ratio weight value * (Active number of connection pools of the data source / Maximum number of connection pools of the data source) + Average load weight value of the connection pool * Average load value of the connection pool.

10. A method for supporting unified management and querying of heterogeneous data sources across multiple clusters, as described in claim 9, characterized in that... The active connection ratio weight is 0.7, and the connection pool average load weight is 0.3.

Images