Data multi-source joint retrieval method and device
Through containerized deployment and resource scheduling optimization, the problem of low efficiency of large-scale cross-domain data query in the existing technology is solved, efficient and accurate data retrieval and integration are achieved, and the real-time and resource utilization of the system are improved.
Patent Information
- Application Number
- CN202411514829.3
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-10-29
- Publication Date
- 2024-11-29
- Estimated Expiration
- Not applicable · inactive patent
AI Technical Summary
When the existing technology handles large-scale, cross-domain heterogeneous data query, resource restrictions and deployment methods are rigid, resulting in low retrieval efficiency and inability to meet real-time requirements. Especially in high-concurrency scenarios, too many query tasks lead to prolonging waiting time, and system resources It is easy to reach bottlenecks.
Through containerized deployment of Trino work nodes, use Docker and Kubernetes technology to preconfigure resource-limited container group pods, design data identification and feature analysis module to calculate table complexity, realize asynchronous real-time monitoring and resource scheduling, combining result caching and materialization acceleration.
It significantly improves data processing efficiency and resource utilization, reduces resource waste, improves data retrieval response speed and query task execution efficiency, can quickly respond to complex query tasks, and meet time-sensitive decision support.
Smart Images

Figure CN119046346A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of cloud information technology, and specifically provides a data multi-source joint retrieval method and device. Background Art
[0002] In the existing technology, the amount of data involved in daily work is huge and diverse. These data are not only complex and changeable, but also scattered and stored in different business systems. These systems often use different technical architectures, data formats and storage methods, which makes it difficult for data to be directly communicated and shared, bringing considerable challenges to the development of business.
[0003] In actual operation, cross-departmental, cross-source and cross-domain information retrieval and integration is a common and important task. Especially in the process of formulation and resource allocation, it is often necessary to coordinate data from multiple departments, conduct comprehensive analysis and comparison, in order to make effective decisions. However, due to the diversity and complexity of data sources, the implementation of this process faces many difficulties.
[0004] In the cross-source and cross-domain data retrieval solution, Trino, as a mature and powerful distributed query engine, has been widely used in the industry. It can connect to a variety of data sources, including relational databases, non-relational databases, big data platforms, etc., provide a unified query interface, and realize the joint retrieval of cross-source and cross-domain data. Although Trino is an excellent distributed cross-source data retrieval tool, it still faces some challenges when facing complex and changing data.
[0005] Data is characterized by diversity, complexity, and large amounts of data. When processing large-scale, cross-domain heterogeneous data queries, Trino often suffers from low retrieval efficiency and cannot meet the real-time requirements of the work due to resource limitations and rigid deployment methods. Especially in high-concurrency scenarios, the extended waiting time caused by too many query tasks and the inability to perform elastic scaling under traditional deployment methods further exacerbate the execution efficiency issues of large-scale retrieval tasks. When multiple departments initiate query requests at the same time, system resources are prone to bottlenecks, resulting in slower query responses or even failure to return results in a timely manner.
[0006] Therefore, how to achieve efficient and accurate cross-source and cross-domain information retrieval and integration in massive, complex and scattered data has become a technical challenge that needs to be solved urgently in the industry. Summary of the invention
[0007] The present invention aims at the above-mentioned deficiencies of the prior art and provides a highly practical data multi-source joint retrieval method.
[0008] A further technical task of the present invention is to provide a data multi-source joint retrieval device that is rationally designed, safe and applicable.
[0009] The technical solution adopted by the present invention to solve its technical problem is: A data multi-source joint retrieval method has the following steps: S1. Deploy the original Trino working node in a containerized manner; S2, accessing heterogeneous data through a heterogeneous data protocol conversion adapter; S3, data identification and feature analysis; S4, asynchronous real-time monitoring and analysis; S5. Resource scheduling and optimization; S6, result cache, materialization acceleration.
[0010] Furthermore, in step S1, based on Docker and Kubernetes container technologies, container groups Pods with different resource restrictions are pre-configured to adapt to query requirements under different loads; Multiple container group Pods are preconfigured based on different computing performance, and each Pod has different resource limits and requests.
[0011] Furthermore, in step S3, a new data recognition and analysis module Data Insight Module is added to automatically collect and analyze key indicators of the connected data source through a scheduled task. On this basis, a calculation formula for the complexity of the database table is designed, and the formula is as follows: TableComplexityScore (TCS) = D × (w 1 ×log (S) + w 2 × F + w 3 × I + w 4 × R + w 5 × C) ; The parameters are defined as follows: S is the size of the table, measured in terms of the number of rows or amount of data; F is the number of table fields; I is the number of indexes; R It is the complexity of the table structure, which is the reciprocal of the normal form level 1NF, 2NF, 3NF, or a quantitative indicator of the structural complexity; C is the number of foreign keys, w 1 , w 2 , w 3 , w 4 , w 5 It is a weight factor, which is used to adjust the influence of different factors on complexity. These weights are adjusted according to the actual situation; D The database type coefficient reflects the impact of different database types on table complexity. Different database types may have different optimization mechanisms and usage scenarios, and therefore have different impacts on table complexity. D RDBMS : The coefficient of relational database, D Columnar : The coefficients of column-based databases, D In-Memory : The coefficient of the in-memory database, D Document : coefficients of document databases; The calculation steps are as follows: (1) Determine the size of the table S , and calculate its logarithm log (S) To balance the impact of data volume on complexity; (2) Calculate the number of fields in the table F , index number I , Number of foreign keys C , and evaluate the complexity of the table structure R ; (3) Determine the coefficient according to the database type D ; (4) Assign weights to each factor w 1 ,w 2 ,w 3 ,w 4 ,w 5 ; (5) Substitute these values into the formula to calculate TCS; According to the above formula, the table complexity of each access data source is calculated and sorted from large to small. According to the actual situation, the table with higher header complexity is selected for marking and annotation, and the relevant information is recorded for query optimization and resource scheduling.
[0012] Furthermore, in step S3, it further includes: (1) Regularly scan and identify information in each database table, set scheduled tasks, and use scripts or database management tools to automatically count key indicators and calculate the complexity of the table; (2) Threshold setting: According to business requirements and system resource conditions, set reasonable thresholds for each data source or table. These thresholds will be used to determine the size and complexity of the query task; (3) Marking and recording: Tables that reach or exceed the threshold are marked and the relevant information is stored in a dedicated cache or database for query and subsequent resource scheduling.
[0013] Furthermore, in step S4, it further includes: S4-1, asynchronous monitoring and real-time analysis; S4-2, multi-dimensional prediction; S4-3, real-time calculation and prediction; S4-4, Feedback and prompts; S4-5. Optimization and adjustment.
[0014] Furthermore, in step S4-1, a front-end asynchronous listener InputMonitor is constructed, which uses asynchronous processing to monitor the SQL statements input by the user client in real time, performs preliminary analysis on the SQL statements input by the user, and extracts key information; In step S4-2, it includes: (1) Analysis of complex large table data characteristics: Based on the calculation results of the data table complexity, check whether the table queried by the user is a table with high complexity. If a table with high complexity is hit, further analyze the query conditions and data distribution, and evaluate the amount of data involved in the query and the processing time; (2) Cross-domain query structure analysis: Analyze the SQL statements of user queries to check whether the cross-domain queries involve multiple data sources or tables, evaluate the complexity of the JOIN operations in the query and the data distribution, and the impact of cross-domain queries on system performance; (3) Check whether the SQL statements of user queries contain aggregate functions, statistics, or calculation operations, analyze the data volume and calculation complexity of these operations, and whether index support is involved, and evaluate the complexity and execution time of the query operations; (4) Index matching efficiency analysis: Compare the SQL statements of user queries with the table index to determine whether the query content is supported by the index, analyze the index coverage, selectivity, and the matching degree between the query conditions and the index, and evaluate the effect of the index on improving the query speed; In this step, the following formula is designed to calculate and determine the complexity of user queries; Query Complexity Score (QCS)=TCS + w 6 × E + w 7 × Joins + w 8 ×(1- Acc ); in: QCS is the query complexity score, which measures the overall complexity of the query; TCS It is scored based on the complexity of the data table described above; E is the number of tables involved in the query; Joins is the number of JOIN operations involved in the query; Acc It is the usage of the aggregate function involved in the query. Here we use 1- Acc To indicate that no aggregate function is used, that is Acc When it is 0, it means that no aggregation function is used and the complexity is the highest; w 6 ,w 7 ,w 8 It is a weight factor, which is used to adjust the influence of different factors on query complexity; Calculation steps: (1) Calculate the TCS ; (2) Determine the number of tables involved in the query E ; (3) Calculate the number of join operations involved in the query Joins ; (4) Evaluate the use of aggregate functions in queries Acc , if an aggregate function is used, then Acc Close to 1, if not used, then Acc Close to 0; (5) Assign weights to each factorw 6 ,w 7 ,w 8 ; (6) Substitute these values into the formula to calculate QCS ; According to the actual situation, set a threshold. QCS When the value exceeds the threshold, it is determined to be a large or complex query task, and resource scheduling is further optimized.
[0015] Furthermore, in step S4-3, during the process of the user inputting the SQL statement, the above-mentioned multi-dimensional prediction is performed in real time, and the resource pre-scheduling strategy is dynamically adjusted according to the prediction result; If the prediction result is a large or complex query task, the resource pre-scheduling mechanism is immediately triggered to reserve high-performance Pods in advance or expand the Pod capacity to ensure the execution efficiency of the query task and the overall performance of the cluster; In step S4-4, a corresponding prompt is given through the user interface according to the prediction result. If the prediction result is a simple query task, the query is directly executed without resource pre-scheduling or capacity expansion; In step S4-5, according to the execution status of the actual query task and the resource usage, the multi-dimensional prediction algorithm and threshold are continuously optimized and adjusted, and the data pre-statistical results are regularly updated and maintained to ensure that the prediction algorithm can accurately reflect the actual situation of the data.
[0016] Furthermore, in step S5, it includes: S5-1. Resource reservation and adjustment based on prediction; With the help of prediction results and the elastic scaling mechanism of containerized deployment, resource scheduling is triggered intelligently. For large query tasks to be executed, high-performance Pod groups are reserved in advance to execute large tasks. During the execution of the query task, the system will continuously monitor the real-time load situation, and use the characteristics of Kubernetes API Aggregator to support the registration of third-party services to the Kubernetes API, access external services through the Kubernetes API, use prometheus as the monitoring data source, and register prometheus-adapeter as an extended service to the Kubernetes API to dynamically adjust the number of Pods and resource allocation to maintain efficient execution of query tasks; S5-2. Manually configure the scheduled task management Pod group size; HPA and CronHPA are combined to deal with different scenarios. HPA is used to control according to business indicators. CronHPA is used to automatically schedule resources in advance according to the rules of busy and idle business, expand resources before business peaks, and release resources when business is idle.
[0017] Furthermore, in step S6, the data is intelligently cached in a high-speed storage area, and cache strategies are formulated and optimized to support multiple cache update strategies to ensure the timeliness and accuracy of cached data. In addition, the aggregate cache table is optimized to improve query performance and data access efficiency.
[0018] A data multi-source joint retrieval device, comprising: at least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is used to call the machine-readable program to execute a data multi-source joint retrieval method.
[0019] Compared with the prior art, the data multi-source joint retrieval method and device of the present invention have the following outstanding beneficial effects: The present invention improves data processing efficiency: through the containerized query engine and resource elastic scaling mechanism, the system can dynamically adjust resource allocation according to real-time data load, significantly improving data processing efficiency. In particular, in policy analysis and public management tasks with large data volumes and complex queries, it reduces resource waste and improves resource utilization.
[0020] Accelerate data retrieval response: Real-time SQL query analysis combined with intelligent resource scheduling optimization realizes multi-dimensional prediction and resource pre-scheduling of query tasks. This mechanism effectively improves the performance and response speed of data retrieval, allowing staff to obtain key information faster and thus quickly respond to various public service needs.
[0021] Optimize query task execution: Through real-time monitoring and analysis of SQL statements, the system can identify complex query tasks that require a large amount of computing resources in advance, and adjust the size of the container group in time to ensure that these query tasks can be executed in an environment with sufficient resources. This real-time optimization mechanism avoids query delays caused by insufficient resources and improves work efficiency, especially in time-sensitive formulation and decision support, and can provide fast and accurate data support for decision-making. BRIEF DESCRIPTION OF THE DRAWINGS
[0022] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.
[0023] Attached Figure 1 It is a flowchart of a data multi-source joint retrieval method; Attached Figure 2 It is an optimized Trino architecture diagram in a data multi-source joint retrieval method; Attached Figure 3 It is a real-time SQL analysis diagram in a data multi-source joint retrieval method; Attached Figure 4 It is a schematic diagram of containerized resource scheduling and optimization in a multi-source joint retrieval method of data; Attached Figure 5 This is a traditional Trino architecture diagram in a multi-source joint retrieval method for data. DETAILED DESCRIPTION
[0024] In order to enable those skilled in the art to better understand the solution of the present invention, the present invention is further described in detail below in conjunction with specific implementation methods. Obviously, the described embodiments are only part of the embodiments of the present invention, rather than all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.
[0025] A best embodiment is given below: like Figure 5 As shown, Client: includes the client and JDBC driver, ODBC or Driver implemented in other languages; Discovery-node: It is a service that combines script parsing nodes and working nodes. After starting, the working node registers with the discovery node, and the script parsing node obtains a working node that can work normally from the discovery node.
[0026] Analysis & Scheduling-node: It is mainly used to receive queries submitted by clients, parse query statements, perform lexical analysis to generate query execution plans, and generate query plans (stages) and tasks (Tasks) for scheduling; then merge the results and return the results to the client (Client).
[0027] Worker: Mainly responsible for reading and writing data and executing query plans.
[0028] Heterogeneous Data Protocol Conversion Adapter (Data-Connector): used to connect Trino to data sources (such as Hive, RDBMS). The adapter was designed with full reference to JDBC and uses standard API interfaces to interact with different data sources. Each data source has a specific adapter. In the configuration file of each data source, the adapter of the data source is specified through the connector.name property. The number of data sources and adapters is one-to-one, and future expansion scenarios are fully considered during the design, so as to achieve unified access and management of heterogeneous data sources.
[0029] Accelerate cache: used to cache commonly used cross-domain collaborative computing result sets, thereby enabling fast access to hot data.
[0030] It can be seen from the traditional Trino architecture that the overall architecture of the system is relatively rigid, the number of working nodes is fixed, and when executing query tasks, it is impossible to flexibly allocate resources according to actual conditions. In the process of high concurrency or large tasks, query performance will face great challenges.
[0031] like Figure 2 As shown, the new real-time SQL query analysis, data identification and analysis modules in this embodiment are added. At the same time, the working nodes are deployed in containers, and the parsing and scheduling are optimized and upgraded to improve the overall performance of the cluster and enhance the prediction and resource scheduling capabilities for large SQL and concurrent tasks.
[0032] like Figure 1 As shown, the specific implementation process is as follows: S1. Deploy the original Trino working node in a containerized manner; Through cloud-native technology, the original Trino working nodes are deployed in containers, based on container technologies such as Docker and Kubernetes, to ensure the portability, scalability and high availability of services. By pre-configuring container groups (Pods) with different resource restrictions, flexible management and dynamic scaling of computing resources are achieved to adapt to query requirements under different loads. At the same time, the containerized deployment method has greatly improved the overall stability and reliability of the system.
[0033] Pre-configure multiple container groups (Pods) based on different computing performance, each with different resource limits (such as CPU, memory) and requests. For example, create some Pods with fewer resources to perform small tasks, and create some high-performance, resource-rich Pods to perform complex queries.
[0034] S2, accessing heterogeneous data through a heterogeneous data protocol conversion adapter; Access heterogeneous data (such as relational databases, non-relational databases, big data platforms, etc.) through heterogeneous data protocol conversion adapters, and provide a unified data access interface.
[0035] S3, data identification and feature analysis; Design a new data recognition and analysis module Data Insight Module, which automatically collects and analyzes key indicators of connected data sources through scheduled tasks, such as database type, table structure, table data volume, index status, data characteristics, foreign key status, etc. On this basis, design a calculation formula for the complexity of the database table, the formula is as follows: TableComplexityScore (TCS) = D × (w 1 ×log (S) + w 2 × F + w 3 × I + w 4 × R + w 5 × C); The parameters are defined as follows: S is the size of the table, measured in number of rows or amount of data (e.g., MB); F is the number of table fields, I is the number of indexes; R It is the complexity of the table structure, which can be the inverse of the normal form level (1NF, 2NF, 3NF), or a quantitative indicator of structural complexity; C is the number of foreign keys, w 1 , w 2 , w 3 , w 4 , w 5 It is a weight factor, which is used to adjust the influence of different factors on complexity. These weights can be adjusted according to actual conditions; D It is the database type coefficient, which reflects the impact of different database types on table complexity. Different database types may have different optimization mechanisms and usage scenarios, and therefore have different impacts on table complexity.
[0036] D RDBMS : The coefficient of the relational database, for example, 1.0, D Columnar : The coefficient of the column database, for example, 0.8, D In-Memory : The coefficient of the in-memory database, for example 1.2, D Document : The coefficient for the document database, for example 1.1.
[0037] The calculation steps are as follows: (1) Determine the size of the table S , and calculate its logarithm log (S) To balance the impact of data volume on complexity.
[0038] (2) Calculate the number of fields in the table F , index number I , Number of foreign keys C , and evaluate the complexity of the table structure R .
[0039] (3) Determine the coefficient according to the database type D .
[0040] (4) Assign weights to each factor w 1 ,w 2 ,w 3 ,w 4 ,w 5 .
[0041] (5) Substitute these values into the formula to calculate TCS.
[0042] Assume that the characteristics of a data table are as follows: Database type: relational database ( D RDBMS =1.0); Table size: 1 million rows of data, number of fields: 20 fields, number of indexes: 5 indexes, table structure complexity: follows 3NF (assuming quantitative index 3), number of foreign keys: 5 foreign keys, weight factor: w1 =0.5, w 2 =0.2, w 3 =0.1, w 4 =0.1, w 5 =0.1; Then the calculation of TCS is as follows: TCS =1.0×(0.5×log(1000000)+0.2×20+0.1×5+0.1×3+0.1×5); TCS =1.0×(0.5×13.8155+4+0.5+0.3+0.5); TCS =1.0×(6.9078+4+0.5+0.3+0.5); TCS = 1.0×12.7078; TCS = 12.7078; According to the above formula, the table complexity of each access data source is calculated and sorted from large to small. According to the actual situation, the table with higher header complexity is selected for marking and annotation, and the relevant information is recorded for query optimization and resource scheduling.
[0043] Scheduled scanning and identification of database table information: Set up scheduled tasks (such as the off-peak hours in the early morning every day), and use scripts or database management tools to automatically count key indicators such as the data volume and size of each data source and table, and calculate the complexity of the table. This helps the system understand the data distribution and scale, and provides a basis for subsequent query analysis and resource scheduling.
[0044] Threshold setting: According to business requirements and system resources, set reasonable thresholds (such as data volume, number of rows, etc.) for each data source or table. These thresholds will be used to determine the size and complexity of query tasks.
[0045] Marking and recording: Mark tables that reach or exceed the threshold, and store the relevant information in a dedicated cache or database for fast query and subsequent resource scheduling.
[0046] S4, asynchronous real-time monitoring and analysis; By deploying traditional Trino in containers, Trino's architecture has resource elasticity, dynamically and elastically expanding resources during business peaks and releasing resources when resource load is low. Use resources dynamically on demand and in quantity. However, Kubernetes' HPA supports monitoring data in CPU and memory dimensions as elasticity indicators, but lacks the ability to predictively expand capacity for large query tasks. For example, when executing a TB-level cross-source data query, the system needs to occupy a large amount of resources. Although the high CPU and memory usage triggers the elastic expansion of K8S and the performance of cluster sorting is enhanced, the newly popped-up container does not participate in the calculation of this task, resulting in the inability to improve the efficiency of this complex query task.
[0047] Based on this, we can ensure the execution efficiency of such large tasks by expanding the container or reserving resources in advance. However, parsing the user SQL at the submission stage will also waste a certain amount of time. Therefore, we adopt the method of asynchronous real-time monitoring of the SQL input box to predict the complexity and resource requirements of the user query based on the data features and index information provided by the data identification and analysis module. It works together with parsing and scheduling to ensure that the query task can be executed in an environment with sufficient resources, thereby improving query efficiency and performance.
[0048] The details are as follows: S4-1, asynchronous monitoring and real-time analysis; Build a front-end asynchronous listener InputMonitor to monitor the SQL statements input by the user client in real time, and use asynchronous processing to avoid blocking user operations. Perform preliminary analysis on the SQL statements input by the user to extract key information, such as table name, column name, query conditions, aggregate functions (such as GROUP BY), statistics or calculation operations, etc.
[0049] S4-2, multi-dimensional prediction; Analysis of large table data characteristics: Based on the calculation results of the data table complexity, check whether the table queried by the user is a table with high complexity. If a table with high complexity is hit, further analyze the query conditions and data distribution, and evaluate the amount of data and processing time that the query may involve.
[0050] Cross-domain query structure analysis: Analyze the SQL statements of user queries to check whether they involve cross-domain queries of multiple data sources or tables. Evaluate the complexity of the join (JOIN) operations and data distribution in the query, as well as the impact of cross-domain queries on system performance.
[0051] Query operation complexity analysis: Check whether the SQL statement of the user's query contains aggregate functions (such as GROUPBY), statistical or calculation operations (such as SUM, COUNT, AVG, etc.). Analyze the data volume and calculation complexity of these operations, and whether index support is involved, and evaluate the complexity and execution time of the query operation.
[0052] Index matching efficiency analysis: Compare the SQL statements of user queries with the table index to determine whether the query content is supported by the index. Analyze the coverage, selectivity and matching degree of the query conditions and the index, and evaluate the effect of the index on improving the query speed.
[0053] In this step, the following formula is designed to calculate and determine the complexity of user queries.
[0054] Query Complexity Score (QCS)=TCS + w 6 × E + w 7 × Joins + w 8 ×(1- Acc ); in: QCS is the query complexity score, which measures the overall complexity of the query; TCS It is scored based on the complexity of the data table described above; E is the number of tables involved in the query; Joins is the number of JOIN operations involved in the query; Acc The usage of aggregate functions (such as COUNT, AVG, SUM, MAX, MIN, etc.) involved in the query. Here, 1− Acc To indicate that no aggregate function is used, that is Acc When it is 0, it means that no aggregation function is used and the complexity is the highest.
[0055] w 6 ,w 7 ,w 8 It is a weight factor used to adjust the influence of different factors on query complexity.
[0056] Calculation steps: (1) Calculate theTCS .
[0057] (2) Determine the number of tables involved in the query E .
[0058] (3) Calculate the number of join operations involved in the query Joins .
[0059] (4) Evaluate the use of aggregate functions in queries Acc , if an aggregate function is used, then Acc Close to 1, if not used, then Acc Close to 0.
[0060] (5) Assign weights to each factor w 6 ,w 7 ,w 8 .
[0061] (6) Substitute these values into the formula to calculate QCS .
[0062] Assume a query with the following characteristics: Number of tables involved: 3 tables Number of join operations involved in the query: 2 JOINs No aggregate function is used in the query (Acc=0) Data Table Complexity Score TCS : 12.7078 (according to the previous calculation) Weighting Factor: w 6 = 0.3, w 7 = 0.2, w 8 = 0.5 but QCS The calculation of is as follows: QCS = 12.7078 + 0.3×3 + 0.2×2 + 0.5×(1-0) QCS = 12.7078+0.9+0.4+0.5 QCS = 14.5078 According to the actual situation, set a threshold. QCS When the value exceeds the threshold, it is determined to be a large or complex query task, and resource scheduling is further optimized.
[0063] S4-3, real-time calculation and prediction; like Figure 3 As shown, during the process of the user inputting the SQL statement, the above-mentioned multi-dimensional prediction is performed in real time, and the resource pre-scheduling strategy is dynamically adjusted according to the prediction result.
[0064] If the predicted result is a large or complex query task, the resource pre-scheduling mechanism is immediately triggered to reserve high-performance Pods in advance or expand the Pod capacity to ensure the execution efficiency of the query task and the overall performance of the cluster. Figure 3 As shown, when querying data across three data sources, Oracle, PostgreSQL, and MySQL, real-time analysis is performed and conclusions are drawn while the user is inputting SQL.
[0065] S4-4, Feedback and prompts; Based on the prediction results, corresponding prompts are given through the user interface, such as "This query may involve a large amount of data and the estimated execution time is long. Please wait patiently" or "High-performance resources are being reserved for you. Please wait."
[0066] If the prediction result is a simple query task, the query is executed directly without the need for resource pre-scheduling or capacity expansion.
[0067] S4-5. Based on the actual query task execution and resource usage, continuously optimize and adjust the multi-dimensional prediction algorithm and threshold to improve the accuracy and efficiency of prediction. Regularly update and maintain the results of data pre-statistics to ensure that the prediction algorithm can accurately reflect the actual situation of the data.
[0068] S5. Resource scheduling and optimization; Responsible for receiving SQL queries submitted by clients, performing lexical analysis to generate query execution plans, and dynamically adjusting resource allocation based on real-time performance indicators. Comprehensively consider query complexity, data size, index matching efficiency, and business-specific monitoring indicators, such as message queue backlogs and request processing wait time, to achieve multi-dimensional prediction. Utilize the elastic scaling function of the container orchestration platform to automatically expand the size of the container group during business peaks, ensure efficient execution of query tasks, and recycle resources during business troughs to save costs. In addition, it is also responsible for monitoring query execution time, resource usage, and system health, adjusting resource scheduling strategies based on monitoring data, optimizing query performance, and finally merging the calculation results of the computing engine and returning them to the client to achieve the goals of high performance, high availability, and cost optimization.
[0069] The details are as follows: (1) Resource reservation and adjustment based on prediction; With the help of prediction results and the elastic scaling mechanism of containerized deployment, the system can intelligently trigger resource scheduling. For large query tasks to be executed, the system will reserve high-performance Pod groups for the execution of large tasks in advance to ensure the smooth progress of the tasks.
[0070] During the execution of the query task, the system will continue to monitor the real-time load (such as CPU, memory, task queue waiting status, message queue backlog, etc.). Since K8S's HPA supports CPU and memory dimension monitoring data as elasticity indicators, it does not support custom elasticity indicators (task queue waiting status, message queue backlog) very well. Therefore, using the characteristics of Kubernetes API Aggregator, it supports registering third-party services to Kubernetes API. It enables direct access to external services through Kubernetes API. External services are used as data sources to achieve a richer range of indicator detection and flexible scalability, which is more suitable for complex and frequent data retrieval scenarios.
[0071] like Figure 4 As shown in the figure, in the specific implementation process, prometheus can be used as the monitoring data source, and prometheus-adapeter can be registered to the Kubernetes API as an extended service. By dynamically adjusting the number of Pods and resource allocation, it helps to maintain efficient execution of query tasks while avoiding resource waste. The specific process is as follows: 1) Node and Pod monitoring starting point In a Kubernetes cluster, Node and Pod are the starting points for resource monitoring. Multiple Pods run on these Nodes, and each Pod is an instance of an application.
[0072] 2) Container resource monitoring tools collect data Container resource monitoring tools (such as cAdvisor and kubelet) are responsible for collecting metrics on Nodes and Pods, including CPU, memory, and possible custom metrics. This data is the basis for subsequent decisions.
[0073] 3) Index server stores data The collected metrics are sent to the metrics-server, which is responsible for storing and providing these metrics so that other components can access and use them.
[0074] 4) Prometheus monitoring component collects more data The Prometheus monitoring component further collects custom metrics of Node and Pod metrics, such as task queue waiting status, message queue backlog, etc., through its probes (such as Prometheus-agent) to expand the overall monitoring scope and store these metrics in the Prometheus database.
[0075] 5) Monitoring component adapter converts data format Monitoring component adapters (such as Prometheus-adapter) convert metrics in Prometheus into a format that Kubernetes can understand so that Kubernetes can use this data to make decisions such as autoscaling.
[0076] 6) Metrics Aggregator Consolidates Data Metrics-aggregators may be used to aggregate metrics from multiple monitoring components (such as Prometheus and other monitoring tools) to provide a unified view for Kubernetes to use.
[0077] 7) Horizontal Pod Autoscaler Decision The Horizontal Pod Autoscaler (HPA) makes decisions based on metrics (such as CPU usage, memory usage, custom metrics, etc.) obtained from metrics aggregators or other sources to trigger Pod scaling operations.
[0078] 8) Deployment and replica set to adjust the number of Pods Based on the decision results of HPA, Kubernetes resources such as Deployment and ReplicaSet will adjust the number of Pods accordingly to maintain system stability and performance.
[0079] (2) Manually configure the scheduled task management Pod group size; In order to further save resources, HPA and CronHPA are combined to deal with different scenarios. In daily work scenarios, HPA is used to control according to business indicators, and CronHPA is used to automatically schedule resources in advance according to the rules of busy and idle business, expand resources before business peaks, and release resources when business is idle, thereby greatly shortening the application cold start time.
[0080] By combining manual configuration with automatic adjustment, resource scheduling is made more flexible and efficient, helping to save resource costs to the maximum extent while meeting business needs.
[0081] S6, result cache, materialization acceleration; Through containerization and Trino's own elastic scaling, users' real-time query and analysis performance has been greatly improved. However, when facing frequent queries of data volumes above GB, the overall performance will still be reduced due to the I / O bottleneck limitations of each data source. In order to overcome this problem, through dynamic projection, the data access pattern is deeply analyzed to accurately identify the frequently accessed hot data and large-scale data analysis query results. These data are intelligently cached in high-speed storage areas, such as in-memory databases or SSDs. These high-speed storage areas have extremely high read and write speeds and capacities, which can ensure that data is quickly provided when needed. By caching hot data and query results in these areas, the process of recalculating and retrieving from the original data source is avoided every time a query is made, thereby greatly reducing the latency of query and analysis. At the same time, it supports cache strategy formulation and optimization. In order to achieve "data freshness", it supports multiple cache update strategies, including periodic refresh, incremental update, etc., to ensure the timeliness and accuracy of cached data. In addition, it supports optimization of aggregate cache tables to improve query performance and data access efficiency.
[0082] Based on the above method, a data multi-source joint retrieval device in this embodiment includes: at least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is used to call the machine-readable program to execute a data multi-source joint retrieval method.
[0083] The above-mentioned specific implementations are only specific cases of the present invention. The patent protection scope of the present invention includes but is not limited to the above-mentioned specific implementations. Any technical solutions that conform to the above-mentioned specific implementations of the present invention and any appropriate changes or substitutions made by ordinary technicians in the relevant technical field shall fall within the patent protection scope of the present invention.
[0084] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the appended claims and their equivalents.
Claims
1. A data multi-source joint retrieval method, characterized in that: The steps are as follows: S1. Deploy the original Trino working node in a containerized manner; S2, accessing heterogeneous data through a heterogeneous data protocol conversion adapter; S3, data identification and feature analysis; S4, asynchronous real-time monitoring and analysis; S5. Resource scheduling and optimization; S6, result cache, materialization acceleration.
2. A data multi-source joint retrieval method according to claim 1, characterized in that: In step S1, based on Docker and Kubernetes container technologies, container groups Pods with different resource limits are pre-configured to adapt to query requirements under different loads; Multiple container group Pods are preconfigured based on different computing performance, and each Pod has different resource limits and requests.
3. A data multi-source joint retrieval method according to claim 2, characterized in that: In step S3, a new data recognition and analysis module Data Insight Module is added to automatically collect and analyze key indicators of the connected data source through scheduled tasks. On this basis, a calculation formula for the complexity of the database table is designed. The formula is as follows: TableComplexityScore (TCS) = D × (w 1 ×log (S) + w 2 × F + w 3 × I + w 4 × R + w 5 × C) ; The parameters are defined as follows: S is the size of the table, measured in terms of the number of rows or amount of data; F is the number of table fields; I is the number of indexes; R It is the complexity of the table structure, which is the reciprocal of the normal form level 1NF, 2NF, 3NF, or a quantitative indicator of the structural complexity; C is the number of foreign keys, w 1 , w 2 , w 3 , w 4 , w 5 It is a weight factor, which is used to adjust the influence of different factors on complexity. These weights are adjusted according to the actual situation; D The database type coefficient reflects the impact of different database types on table complexity. Different database types may have different optimization mechanisms and usage scenarios, and therefore have different impacts on table complexity. D RDBMS : The coefficient of relational database, D Columnar : The coefficients of column-based databases, D In-Memory : The coefficient of the in-memory database, D Document : coefficients of the document database; The calculation steps are as follows: (1) Determine the size of the table S , and calculate its logarithm log (S) To balance the impact of data volume on complexity; (2) Calculate the number of fields in the table F , index number I , Number of foreign keys C , and evaluate the complexity of the table structure R ; (3) Determine the coefficient according to the database type D ; (4) Assign weights to each factor w 1 ,w 2 ,w 3 ,w 4 ,w 5 ; (5) Substitute these values into the formula to calculate TCS; According to the above formula, the table complexity of each access data source is calculated and sorted from large to small. According to the actual situation, the table with higher header complexity is selected for marking and annotation, and the relevant information is recorded for query optimization and resource scheduling.
4. A data multi-source joint retrieval method according to claim 3, characterized in that: In step S3, further comprising: (1) Regularly scan and identify information in each database table, set scheduled tasks, and use scripts or database management tools to automatically count key indicators and calculate the complexity of the table; (2) Threshold setting: According to business requirements and system resource conditions, set reasonable thresholds for each data source or table. These thresholds will be used to determine the size and complexity of the query task; (3) Marking and recording: Tables that reach or exceed the threshold are marked and the relevant information is stored in a dedicated cache or database for query and subsequent resource scheduling.
5. A data multi-source joint retrieval method according to claim 4, characterized in that: In step S4, further comprising: S4-1, asynchronous monitoring and real-time analysis; S4-2, multi-dimensional prediction; S4-3, real-time calculation and prediction; S4-4, Feedback and prompts; S4-5. Optimization and adjustment.
6. A data multi-source joint retrieval method according to claim 5, characterized in that: In step S4-1, a front-end asynchronous listener InputMonitor is constructed, which uses asynchronous processing to monitor the SQL statements input by the user client in real time, performs preliminary analysis on the SQL statements input by the user, and extracts key information; In step S4-2, it includes: (1) Analysis of complex large table data characteristics: Based on the calculation results of the data table complexity, check whether the table queried by the user is a table with high complexity. If a table with high complexity is hit, further analyze the query conditions and data distribution, and evaluate the amount of data involved in the query and the processing time; (2) Cross-domain query structure analysis: Analyze the SQL statements of user queries to check whether the cross-domain queries involve multiple data sources or tables, evaluate the complexity of the JOIN operations in the query and the data distribution, and the impact of cross-domain queries on system performance; (3) Check whether the SQL statements of user queries contain aggregate functions, statistics, or calculation operations, analyze the data volume and calculation complexity of these operations, and whether index support is involved, and evaluate the complexity and execution time of the query operations; (4) Index matching efficiency analysis: Compare the SQL statements of user queries with the table index to determine whether the query content is supported by the index, analyze the index coverage, selectivity, and the matching degree between the query conditions and the index, and evaluate the effect of the index on improving the query speed; In this step, the following formula is designed to calculate and determine the complexity of user queries; Query Complexity Score (QCS)=TCS + w 6 × E + w 7 × Joins + w 8 ×(1- Acc ); in: QCS is the query complexity score, which measures the overall complexity of the query; TCS It is scored based on the complexity of the data table described above; E is the number of tables involved in the query; Joins is the number of JOIN operations involved in the query; Acc It is the usage of the aggregate function involved in the query. Here we use 1- Acc To indicate that no aggregate function is used, that is Acc When it is 0, it means that no aggregation function is used and the complexity is the highest; w 6 ,w 7 ,w 8 It is a weight factor, which is used to adjust the influence of different factors on query complexity; Calculation steps: (1) Calculate the TCS ; (2) Determine the number of tables involved in the query E ; (3) Calculate the number of join operations involved in the query Joins ; (4) Evaluate the use of aggregate functions in queries Acc , if an aggregate function is used, then Acc Close to 1, if not used, then Acc Close to 0; (5) Assign weights to each factor w 6 ,w 7 ,w 8 ; (6) Substitute these values into the formula to calculate QCS ; According to the actual situation, set a threshold. QCS When the value exceeds the threshold, it is determined to be a large or complex query task, and resource scheduling is further optimized.
7. A data multi-source joint retrieval method according to claim 6, characterized in that: In step S4-3, during the process of the user inputting the SQL statement, the above multi-dimensional prediction is performed in real time, and the resource pre-scheduling strategy is dynamically adjusted according to the prediction result; If the prediction result is a large or complex query task, the resource pre-scheduling mechanism is immediately triggered to reserve high-performance Pods in advance or expand the Pod capacity to ensure the execution efficiency of the query task and the overall performance of the cluster; In step S4-4, a corresponding prompt is given through the user interface according to the prediction result. If the prediction result is a simple query task, the query is directly executed without resource pre-scheduling or capacity expansion; In step S4-5, according to the execution status of the actual query task and the resource usage, the multi-dimensional prediction algorithm and threshold are continuously optimized and adjusted, and the data pre-statistical results are regularly updated and maintained to ensure that the prediction algorithm can accurately reflect the actual situation of the data.
8. A data multi-source joint retrieval method according to claim 7, characterized in that: In step S5, it includes: S5-1. Resource reservation and adjustment based on prediction; With the help of prediction results and the elastic scaling mechanism of containerized deployment, resource scheduling is triggered intelligently. For large query tasks to be executed, high-performance Pod groups are reserved in advance to execute large tasks. During the execution of the query task, the system will continuously monitor the real-time load situation, and use the characteristics of Kubernetes API Aggregator to support the registration of third-party services to the Kubernetes API, access external services through the Kubernetes API, use prometheus as the monitoring data source, and register prometheus-adapeter as an extended service to the Kubernetes API to dynamically adjust the number of Pods and resource allocation to maintain efficient execution of query tasks; S5-2. Manually configure the scheduled task management Pod group size; HPA and CronHPA are combined to deal with different scenarios. HPA is used to control according to business indicators. CronHPA is used to automatically schedule resources in advance according to the rules of busy and idle business, expand resources before business peaks, and release resources when business is idle.
9. A data multi-source joint retrieval method according to claim 8, characterized in that: In step S6, the data is intelligently cached to a high-speed storage area, using cache strategy formulation and optimization to support multiple cache update strategies to ensure the timeliness and accuracy of cache data. In addition, the aggregate cache table is optimized to improve query performance and data access efficiency.
10. A data multi-source joint retrieval device, characterized in that: include: at least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is configured to call the machine-readable program to execute the method according to any one of claims 1 to 9.
Citation Information
Patent Citations
Progress identifying method and device as well as terminal equipment
CN102930200A
Method for solving joint retrieval of cross-domain heterogeneous data
CN113886457A
Time sequence RDF data storage and query method based on bit matrix model
CN114791967A
Elastic management method and device based on cloud native architecture
CN117992228A
System and method for optimizing storage of multi-dimensional data in data storage
US20160019249A1
Cited By
High-performance large-data-volume cross-library data analysis method
CN120492452A
