Federal query scheduling method and apparatus, electronic device, and storage medium

Abstract

The application provides a federal query scheduling method and device, electronic equipment and storage medium, and relates to the technical field of federal query, wherein the method comprises the following steps: receiving a query request, analyzing the query request, and generating an initial logical plan; obtaining a target data source capability portrait and target statistical information according to the initial logical plan; using a global cost model, evaluating the total cost of different execution strategies according to the target data source capability portrait and the target statistical information, and determining a target execution strategy according to the evaluation result; and transforming the initial logical plan according to the target execution strategy, the target data source capability portrait and the target statistical information, and generating a distributed execution plan. Through the synergistic effect of heterogeneous data source capability sensing and global cost optimization, the application significantly reduces the query time consumption, improves the query performance, and also improves the resource utilization rate and enhances the system stability.

Description

Technical Field

[0001] This invention relates to the field of federated query technology, and in particular to a federated query scheduling method, apparatus, electronic device, and storage medium. Background Technology

[0002] As enterprises deepen their digital transformation, data is experiencing explosive growth and is widely dispersed across multiple heterogeneous data systems. These systems include, but are not limited to, traditional data warehouses (such as Hive), relational databases (such as MySQL and PostgreSQL), NoSQL databases (such as Elasticsearch), message queues (such as Kafka), and cloud storage (such as AWS S3). To enable joint analysis of this dispersed data, federated query technology has emerged. Federated queries allow users to execute queries across multiple heterogeneous data sources through a unified SQL interface without the need for cumbersome data migration and integration. The open-source distributed SQL query engine Trino (formerly PrestoSQL) is a leader in this field. It adopts a typical Master-Slave architecture, where the Coordinator is responsible for receiving SQL, parsing the syntax, generating and optimizing the execution plan, and scheduling tasks to multiple Workers for execution. Worker nodes interact with external data sources through a series of Connectors, pulling or pushing down computation tasks, and finally summarizing and returning the results.

[0003] While Trino has achieved significant success in federated queries, it has revealed numerous limitations in complex production environments, especially when faced with scenarios where data sources exhibit vastly different performance levels. These limitations manifest as query latency, wasted network bandwidth, poor system stability, and low overall resource utilization.

[0004] Therefore, there is an urgent need for an intelligent method to optimize the scheduling of federated queries in order to overcome the above-mentioned shortcomings. Summary of the Invention

[0005] This invention provides a federated query scheduling method, apparatus, electronic device, and storage medium. Through the synergistic effect of heterogeneous data source capability awareness and global cost optimization, it significantly reduces query time, improves query performance, and also enhances resource utilization and system stability.

[0006] This invention provides a federated query scheduling method, comprising: Receive a query request, parse the query request, and generate an initial logical plan; Based on the initial logical plan, obtain the target data source capability profile and target statistical information; Using a global cost model, the total cost of different execution strategies is evaluated based on the target data source capability profile and the target statistics, and the target execution strategy is determined based on the evaluation results. Based on the target execution strategy, the target data source capability profile, and the target statistical information, the initial logical plan is transformed to generate a distributed execution plan.

[0007] According to a federated query scheduling method provided by the present invention, the step of obtaining the target data source capability profile and target statistical information based on the initial logical plan includes: Obtain the target data source and target data object from the initial logical plan; Obtain the target data source capability profile corresponding to the target data source from the pre-built data source capability profile library; Obtain target statistical information corresponding to the target data object from a pre-collected statistical information database.

[0008] According to a federated query scheduling method provided by the present invention, before obtaining the target data source capability profile corresponding to the target data source from a pre-built data source capability profile library, the method further includes: By benchmarking, a first performance metric of the benchmark data source and a second performance metric of the candidate data source are obtained respectively. Based on the first performance metric and the second performance metric, the computing power score of the candidate data source is determined. By sending probe packets to the candidate data source, the network performance data of the candidate data source can be obtained; Obtain the capability declaration file from the connector corresponding to the candidate data source, and obtain pushdown support information, index support information, concurrent connection limit and custom function support information from the capability declaration file; Based on the data source capability profiles of each candidate data source, a data source capability profile library is constructed; wherein, the data source capability profile includes at least one of the following: computing power score, network performance data, pushdown support information, index support information, concurrent connection limit, and custom function support information.

[0009] According to a federated query scheduling method provided by the present invention, the method utilizes a global cost model to evaluate the total cost of different execution strategies based on the target data source capability profile and the target statistical information, and determines the target execution strategy based on the evaluation results, including: Based on the target data source capability profile and the target statistical information, calculate the network cost and computation cost of different execution strategies respectively; Obtain the CPU cost and I / O cost required for different execution strategies during execution; The total cost for different execution strategies is obtained by summing the CPU cost, the I / O cost, the network cost, and the computation cost. The execution strategy corresponding to the minimum value among the total costs is determined as the target execution strategy.

[0010] According to a federated query scheduling method provided by the present invention, the target data source capability profile includes a target computing capability score and the target bandwidth, and the target statistical information includes target table-level statistical information and target column-level statistical information. The step of calculating the network cost and computing cost of different execution strategies based on the target data source capability profile and the target statistical information includes: For each execution strategy, the target data volume is determined based on the target table-level statistics and the target column-level statistics. The network cost is calculated based on the target data volume, the target bandwidth, and the preset network coefficients. The query complexity is determined based on the initial logical plan and the execution strategy. The computational cost is calculated based on the query complexity, the target computational capability score, and the preset computation coefficient.

[0011] According to a federated query scheduling method provided by the present invention, after transforming the initial logical plan based on the target execution strategy, the target data source capability profile, and the target statistical information to generate a distributed execution plan, the method further includes: The task characteristics of each execution stage in the distributed execution plan are analyzed, and the task characteristics are determined based on the physical operators and the target data source capability profile. Based on the task characteristics and the real-time load of the working nodes, tasks in each execution stage are scheduled.

[0012] According to a federated query scheduling method provided by the present invention, the step of transforming the initial logical plan to generate a distributed execution plan based on the target execution strategy, the target data source capability profile, and the target statistical information includes: According to the target execution strategy, the logical operators in the initial logical plan are converted into physical operators; Based on the target data source capability profile and the target statistical information, mark the execution position of the physical operator and insert data exchange nodes between the physical operators; Based on the data exchange nodes, the physical operators are divided into multiple execution stages, and based on the target data source capability profile, the physical operators in each execution stage are adapted and optimized to generate a distributed execution plan.

[0013] The present invention also provides a federated query scheduling apparatus, comprising: The request parsing module is used to receive query requests, parse the query requests, and generate an initial logical plan; The data acquisition module is used to acquire the target data source capability profile and target statistical information according to the initial logical plan; The cost evaluation module is used to evaluate the total cost of different execution strategies using a global cost model, based on the target data source capability profile and the target statistical information, and to determine the target execution strategy based on the evaluation results. The plan generation module is used to transform the initial logical plan and generate a distributed execution plan based on the target execution strategy, the target data source capability profile, and the target statistical information.

[0014] The present invention also provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement any of the federated query scheduling methods described above.

[0015] The present invention also provides a non-transitory computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the federated query scheduling method as described in any of the preceding claims.

[0016] The federated query scheduling method, apparatus, electronic device, and storage medium provided by this invention parses query requests to generate an initial logical plan. Then, using a global cost model, based on the target data source capability profile and target statistics, the total cost of different execution strategies is evaluated to determine the target execution strategy. By introducing a data source capability profile to perceive heterogeneity differences and using a global cost model to comprehensively evaluate the total cost, the optimal execution strategy can be intelligently determined. Furthermore, based on the target execution strategy, the target data source capability profile, and target statistics, the initial logical plan is transformed to generate a distributed execution plan, which also fully considers the characteristic advantages of each heterogeneous target data source. This invention, through the synergistic effect of heterogeneous data source capability perception and global cost optimization, significantly reduces query time, improves query performance, and also increases resource utilization and enhances system stability. Attached Figure Description

[0017] To more clearly illustrate the technical solutions in this invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this invention. For those skilled in the art, other drawings can be obtained from these drawings without creative effort.

[0018] Figure 1 This is one of the flowcharts illustrating the federated query scheduling method provided by the present invention; Figure 2 This is a schematic diagram of the structure of the federated query scheduling system provided by the present invention; Figure 3 This is the second flowchart of the federated query scheduling method provided by the present invention; Figure 4 This is the third flowchart of the federated query scheduling method provided by the present invention; Figure 5 This is a performance comparison chart between the federated query scheduling method provided by this invention and existing federated query scheduling methods; Figure 6 This is a schematic diagram of the structure of the federated query scheduling device provided by the present invention; Figure 7 This is a schematic diagram of the structure of the electronic device provided by the present invention. Detailed Implementation

[0019] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of this invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of this invention. All other embodiments obtained by those skilled in the art based on the embodiments of this invention without creative effort are within the scope of protection of this invention.

[0020] As enterprises deepen their digital transformation, data is experiencing explosive growth and is widely dispersed across multiple heterogeneous data systems. These systems include, but are not limited to, traditional data warehouses (such as Hive), relational databases (such as MySQL and PostgreSQL), NoSQL databases (such as Elasticsearch), message queues (such as Kafka), and cloud storage (such as AWS S3). To enable joint analysis of this dispersed data, federated query technology has emerged. Federated queries allow users to execute queries across multiple heterogeneous data sources through a unified SQL interface without the need for cumbersome data migration and integration. The open-source distributed SQL query engine Trino (formerly PrestoSQL) is a leader in this field. It adopts a typical Master-Slave architecture, where the Coordinator is responsible for receiving SQL, parsing the syntax, generating and optimizing the execution plan, and scheduling tasks to multiple Workers for execution. Worker nodes interact with external data sources through a series of Connectors, pulling or pushing down computation tasks, and finally summarizing and returning the results.

[0021] While Trino has achieved significant success in federated queries, it has revealed numerous limitations in complex production environments, especially when faced with scenarios where data sources exhibit vastly different performance levels. These limitations manifest as query latency, wasted network bandwidth, poor system stability, and low overall resource utilization.

[0022] Analysis reveals that the modules of existing systems are typically designed in isolation and statically. When making local decisions, they primarily rely on Trino's own scheduling and optimization mechanisms. Specifically, Trino has a built-in Cost-Based Optimizer (CBO), which estimates the cost of different execution plans based on table statistics and selects the plan with the lowest estimated cost. In federated query scenarios, external data sources (such as MySQL and Elasticsearch) often cannot provide, or cannot provide in real-time, accurate and detailed statistical information. Trino's CBO heavily relies on this information; if the information is inaccurate, its cost model will fail, easily generating suboptimal or even extremely poor execution plans. Furthermore, Trino's CBO cost model is inward-looking, only calculating the CPU (Central Processing Unit) and I / O (Input / Output) costs executed within the Trino cluster, completely ignoring the significant cost differences associated with interacting with external data sources.

[0023] In summary, existing federated scheduling query methods based on native Trino fail to fully perceive and quantify the significant differences in computing power, network conditions, and real-time load among external heterogeneous data sources. Furthermore, they fail to provide a unified cost assessment and linkage between these external factors and the system's internal computing resources. This localized optimization approach often results in unbalanced query execution plans and resource allocation strategies in real-world heterogeneous data environments, leading to a series of problems such as high query latency, wasted network bandwidth, poor system stability, and low overall resource utilization.

[0024] Based on the above, this invention proposes a federated query scheduling method, apparatus, electronic device, and storage medium, which are described below in conjunction with... Figures 1-7 Describe it.

[0025] Figure 1 This is one of the flowcharts illustrating the federated query scheduling method provided by the present invention, such as... Figure 1 As shown, the federated query scheduling method includes steps S110, S120, S130 and S140.

[0026] Step S110: Receive a query request, parse the query request, and generate an initial logical plan.

[0027] In this embodiment, the federated query scheduling method of this invention is implemented by improving the original Trino architecture. Specifically, as shown below... Figure 2 As shown, the federated query scheduling system of this invention adds a data source capability manager and a federated cost optimizer to the original Trino architecture. The data source capability manager is used to build and maintain data source capability profiles, while the federated cost optimizer is used to optimize query plans based on a global cost model.

[0028] When users need to retrieve data from multiple data sources, they can submit an SQL (Structured Query Language) query to trigger a query request. Correspondingly, when the Trino query engine receives this query request, it parses the SQL query using the initial optimizer to generate an initial logical plan.

[0029] The initial logical plan is an abstract execution plan generated after parsing the SQL query. It describes the sequence of operations to be executed (such as scanning, filtering, joining, aggregation, etc.) and their logical relationships. It does not involve the specific execution algorithm and location.

[0030] The initial optimizer is part of the Trino native architecture and runs on the coordinator node.

[0031] Step S120: According to the initial logical plan, obtain the target data source capability profile and target statistical information.

[0032] Obtain the data source (denoted as the target data source) and data object (denoted as the target data object) involved in the initial logical plan. Obtain the data source capability profile corresponding to the target data source from the pre-built data source capability profile library, denoted as the target data source capability profile. Obtain the statistical information corresponding to the target data object from the pre-collected statistical information library, denoted as the target statistical information.

[0033] The target data source capability profile includes at least one of the following: target computing capability score, target network performance data, target pushdown support information, target index support information, target concurrent connection limit, and target custom function support information. The target network performance data includes at least one of the following: target latency and target bandwidth.

[0034] The target statistics include at least one of the following: target table-level statistics, target column-level statistics, and target index statistics.

[0035] Step S130: Using the global cost model, based on the target data source capability profile and the target statistical information, evaluate the total cost of different execution strategies, and determine the target execution strategy based on the evaluation results.

[0036] The global cost model is a framework for estimating the total cost by comprehensively considering CPU cost, I / O cost, network cost, and computation cost. Specifically: Total Cost = CPU Cost + I / O Cost + Network Cost + Computation Cost.

[0037] The execution strategy includes pushdown strategy and join strategy. The pushdown strategy includes pushdown and pullback, and the join strategy includes join order and join algorithm.

[0038] First, based on the target data source capability profile and target statistics, calculate the network cost and computation cost of different execution strategies. At the same time, obtain the CPU cost and I / O cost required for different execution strategies during execution. Then, sum the CPU cost, I / O cost, network cost, and computation cost to obtain the total cost of different execution strategies. The execution strategy corresponding to the minimum total cost is determined as the target execution strategy.

[0039] Furthermore, for each execution strategy, the network cost is calculated as follows: the target data volume is determined based on the target table-level statistics and the target column-level statistics; then, the network cost is calculated based on the target data volume, the target bandwidth, and the preset network coefficients.

[0040] Furthermore, for each execution strategy, the computation cost is calculated as follows: based on the initial logical plan and execution strategy, the query complexity is determined; then, based on the query complexity, the target computing power score, and the preset computation coefficient, the computation cost is calculated.

[0041] The specific execution process can be found in the following examples, which will not be elaborated here.

[0042] Step S140: Based on the target execution strategy, the target data source capability profile, and the target statistical information, the initial logical plan is transformed to generate a distributed execution plan.

[0043] A distributed execution plan is a physical plan that is ultimately generated and can be executed on a federated query cluster. It explicitly specifies the execution location, execution algorithm, and data flow path for each operation.

[0044] Specifically, based on the target execution strategy, the logical operators in the initial logical plan are transformed into physical operators. Then, based on the target data source capability profile and target statistics, the execution positions of the physical operators are marked, and data exchange nodes are inserted between the physical operators. Furthermore, based on the data exchange nodes, the physical operators are divided into multiple execution stages, and based on the target data source capability profile, the physical operators within each execution stage are adapted and optimized to generate a distributed execution plan. The specific execution process can be found in the following embodiment, which will not be elaborated upon here.

[0045] The federated query scheduling method provided in this invention parses query requests to generate an initial logical plan. Then, using a global cost model, it evaluates the total cost of different execution strategies based on the target data source capability profile and target statistics to determine the target execution strategy. By introducing a data source capability profile to perceive heterogeneity differences and using a global cost model to comprehensively evaluate the total cost, the optimal execution strategy can be intelligently determined. Furthermore, based on the target execution strategy, the target data source capability profile, and target statistics, the initial logical plan is transformed to generate a distributed execution plan, which also fully considers the characteristic advantages of each heterogeneous target data source. This invention, through the synergistic effect of heterogeneous data source capability awareness and global cost optimization, significantly reduces query time, improves query performance, and also increases resource utilization and enhances system stability.

[0046] Based on any of the above embodiments, step S120 includes: step S121, step S122 and step S123.

[0047] It should be noted that the execution order of steps S122 and S123 is not important and they can be executed in parallel.

[0048] Step S121: Obtain the target data source and target data object in the initial logical plan.

[0049] The initial logical plan is parsed to obtain the data source and data object, which are denoted as the target data source and target data object, respectively.

[0050] Step S122: Obtain the target data source capability profile corresponding to the target data source from the pre-built data source capability profile library.

[0051] Then, retrieve the data source capability profile corresponding to the target data source from the pre-built data source capability profile library, and denot it as the target data source capability profile.

[0052] The target data source capability profile includes at least one of the following: target computing capability score, target network performance data, target pushdown support information, target index support information, target concurrent connection limit, and target custom function support information. The target network performance data includes at least one of the following: target latency and target bandwidth.

[0053] The data source capability profile library can be built through the data source capability manager. The specific construction method is as follows: Through benchmark testing, obtain the first performance index of the benchmark data source and the second performance index of the candidate data sources. Based on the first and second performance indices, determine the computing power score of the candidate data sources. Obtain the network performance data of the candidate data sources by sending probe packets to them. Obtain the capability declaration file from the connector corresponding to the candidate data source, and extract pushdown support information, index support information, concurrent connection limit, and custom function support information from the capability declaration file. Construct the data source capability profile library based on the data source capability profiles of each candidate data source. The data source capability profile includes at least one of the following: computing power score, network performance data, pushdown support information, index support information, concurrent connection limit, and custom function support information. The specific construction process can be found in the following embodiment, and will not be elaborated here.

[0054] Step S123: Obtain target statistical information corresponding to the target data object from a pre-collected statistical information database.

[0055] At the same time, statistical information corresponding to the target data object is obtained from a pre-collected statistical information database and recorded as target statistical information.

[0056] The target statistics include at least one of the following: target table-level statistics, target column-level statistics, and target index statistics.

[0057] Furthermore, such as Figure 2 As shown, the federated query scheduling system of this invention adds a statistics collector to the original Trino architecture.

[0058] The statistical information database can be collected and built using a statistical information collector. The specific construction process is as follows: periodically connect to candidate data sources, collect statistical information from the physical tables in the candidate data sources, and build the statistical information database based on the collected statistical information.

[0059] The statistical information includes, but is not limited to, table-level statistics, column-level statistics, and index statistics. Table-level statistics include, but are not limited to, the number of rows, table size, and storage format. Column-level statistics include, but are not limited to, column cardinality, the proportion of NULL values, and data distribution histograms. Index statistics include, but are not limited to, index type, coverage, and selectivity.

[0060] Furthermore, based on the statistical information collected above, a statistical information database is constructed. Specifically, the collected statistical information can be stored in a metadata store for later querying.

[0061] By periodically obtaining statistical information from the target data source, the statistical information of the target data source can be quickly read directly from the statistical information database when parsing SQL queries in the future. Compared with remotely connecting to each data source to query statistical information, this can greatly improve the overall query scheduling efficiency.

[0062] The federated query scheduling method provided in this invention, through a pre-built data source capability profile library and statistical information library, can quickly obtain accurate target data source performance characteristics and statistical information during query optimization, avoiding the overhead and latency of real-time collection. Simultaneously, the target data source capability profile represents the data source's processing capabilities, and the target statistical information represents the characteristics of the data object. By obtaining the target data source capability profile and target statistical information, subsequent cost evaluation based on complete information can be performed, thereby improving the accuracy of target execution decisions and ultimately enhancing the quality of the execution plan.

[0063] Based on any of the above embodiments Figure 2 This is the second flowchart illustrating the federated query scheduling method provided by this invention, as follows: Figure 2 As shown, before step S121, the steps include: step S11, step S12, step S13 and step S14.

[0064] Step S11: Through benchmark testing, obtain the first performance index of the benchmark data source and the second performance index of the candidate data source respectively, and determine the computing power score of the candidate data source based on the first performance index and the second performance index.

[0065] Analysis reveals that the existing Trino scheduler treats all data sources as black boxes or tables with completely identical performance and capabilities, lacking awareness of the heterogeneity of the data sources themselves. It cannot perceive the differences in computing power between different data sources, nor can it detect the differences in network latency and bandwidth between queries from different data sources. This approach is one of the root causes of execution plan imbalance and low performance. Therefore, this invention introduces a data source capability manager to construct a dynamic, multi-dimensional profile of data source capabilities, achieving accurate awareness of heterogeneous environments and providing a solid data foundation for subsequent intelligent optimization and scheduling.

[0066] Benchmarking refers to executing a pre-defined benchmark query set on a data source to obtain performance metrics of the data source.

[0067] A pre-defined benchmark query set is a set of representative queries used to evaluate the performance of a data source. Specifically, multiple representative queries can be selected from TPC-H (Transaction Processing Performance Council-H, TPC Provisional Query and Decision Support Benchmark) or TPC-DS (Transaction Processing Performance Council - Decision Support, TPC Organizational Decision Support Benchmark), or a custom set of queries can be defined based on business characteristics.

[0068] Performance metrics include, but are not limited to: execution time, CPU utilization, peak memory usage, etc.

[0069] Execute a preset benchmark query set on the benchmark data source to obtain the performance metrics of the benchmark data source on each query, which are denoted as the first performance metric. The benchmark data source refers to the data source instance used as a performance comparison reference standard; for example, it can be a standard configured MySQL instance.

[0070] Execute a predefined benchmark query set on the candidate data source and obtain the performance metrics of the candidate data source on each query, which are denoted as the second performance metric. Here, the candidate data source refers to the data source instance whose performance needs to be evaluated.

[0071] The timing of query execution can be scheduled, such as at midnight every day, or it can be executed on demand, such as when registering a new data source. The specific settings can be configured according to actual needs, and no specific limitations are made here.

[0072] By setting a benchmark data source, a unified reference system can be provided for each candidate data source, making the performance comparison of different candidate data sources business-meaningful and feasible.

[0073] After obtaining the first and second performance metrics, the second performance metrics of the candidate data sources are converted into relative scores using a normalization algorithm, with the first performance metric of the benchmark data source as the reference. These scores are then weighted and averaged using preset weighting coefficients to obtain the final computational capability score of the candidate data source. The preset weighting coefficients include preset metric weighting coefficients and preset query statement weighting coefficients. This comprehensive scoring method provides a more scientific basis for subsequent federated query scheduling decisions.

[0074] For example, the first performance metric data of the benchmark data source is as follows: for the TPC-H Q1 query, the execution time is 30s, CPU utilization is 60%, and peak memory usage is 1.5GB; for the TPC-H Q6 query, the execution time is 15s, CPU utilization is 40%, and peak memory usage is 0.8GB. The second performance metric data of the candidate data source is as follows: for the TPC-H Q1 query, the execution time is 45s, CPU utilization is 50%, and peak memory usage is 1.2GB; for the TPC-H Q6 query, the execution time is 12s, CPU utilization is 35%, and peak memory usage is 0.6GB. The preset weight coefficients for execution time, CPU utilization, and peak memory usage are 0.5, 0.25, and 0.25, respectively, and the preset query statement weight coefficients for TPC-H Q1 and TPC-H Q6 queries are 0.6 and 0.4, respectively.

[0075] First, the overall score for the TPC-HQ1 query is calculated as: (30 / 45×100×0.5) + (60% / 50%×100×0.25) + (1.5 / 1.2×100×0.25) = 94.585 points. Simultaneously, the overall score for the TPC-HQ6 query is calculated as: (15 / 12×100×0.5) + (40% / 35%×100×0.25) + (0.8 / 0.6×100×0.25) = 124.405 points. It should be noted that since execution time, CPU utilization, and peak memory usage are all negative indicators (i.e., lower is better), the above calculations use a ratio of the performance metrics of the baseline data source to the performance metrics of the candidate data sources to obtain the relative scores. If it is a positive indicator, that is, an indicator that the larger the better, such as throughput or concurrency, then the performance indicator of the candidate data source / the performance indicator of the benchmark data source is used to obtain a relative score.

[0076] Then, based on the combined scores of the TPC-H Q1 query and the TPC-H Q6 query, the computational capability score of the candidate data source is calculated. Specifically: (94.585 × 0.6) + (124.405 × 0.4) = 106.513 points.

[0077] Furthermore, the computing power score of the candidate data source can be stored, and a second performance index of the candidate data source can also be stored for subsequent querying and analysis.

[0078] For example, it can be saved as: {"score": 106.513, "details": {"TPC-H Q1": {"execution time": "45s", "CPU utilization": "40%", "memory peak": "0.8GB"}, "TPC-H Q6": {"execution time": "12s", "CPU utilization": "35%", "memory peak": "0.6GB"}}.

[0079] Step S12: Obtain network performance data of the candidate data source by sending a probe packet to the candidate data source.

[0080] Probe packets include, but are not limited to, latency probe packets and bandwidth probe packets. Latency probe packets are used to measure the response time of a network connection; bandwidth probe packets are used to assess network transmission capacity.

[0081] Network performance data, including but not limited to latency data and bandwidth data.

[0082] Trino's worker nodes send probe packets to candidate data sources, and the latency and bandwidth of these data sources can be determined based on the acquired measurement data. The timing of these probe packets can be scheduled periodically, such as at midnight every day, or executed on demand, such as during the registration of a new data source. The specific timing can be configured according to actual needs; no specific limitations are set here.

[0083] In one implementation, the delay data can be obtained by probing with ICMP Ping (Internet Control Message Protocol Packet Internet Groper): sending ICMP (Internet Control Message Protocol) echo request packets, measuring the round-trip time, and taking the average of multiple measurements.

[0084] In another implementation, the delayed data can be obtained by probing a TCP (Transmission Control Protocol) connection: establish a TCP connection with the service port of the candidate data source, measure the time from sending a SYN (Synchronize) packet to receiving a SYN-ACK (Synchronize-Acknowledgment) packet, and take the average of multiple measurements.

[0085] Bandwidth data can be obtained by transmitting test files or virtual data packets. Specifically, a standard-sized test file or virtual data packet is prepared as a bandwidth probe packet, sent to a candidate data source, and the transmission time is calculated. Then, based on the size of the test file or virtual data packet and the transmission time, the available bandwidth is calculated.

[0086] Step S13: Obtain the capability declaration file from the connector corresponding to the candidate data source, and obtain pushdown support information, index support information, concurrent connection limit and custom function support information from the capability declaration file.

[0087] The data source capability profile includes at least one of the following: computing power score, network performance data, pushdown support information, index support information, concurrent connection limit, and custom function support information.

[0088] Trino interacts with various data sources through connectors. Each connector has a pre-defined capability declaration file, which systematically describes the functionalities and limitations supported by the data source it interfaces with. The capability declaration file can be located within the connector code, in an external file, or in the dynamic configuration center. It is written by the Trino connector developers based on the characteristics and supported operations of the data source during connector development.

[0089] By obtaining the capability declaration file, information such as pushdown support, index support, concurrent connection limits, and custom function support can be retrieved. This method of retrieval is simple to implement.

[0090] Pushdown support information refers to information related to supported pushdowns, used to determine which types of operations can be pushed down to the data source for execution. This includes, but is not limited to, supported pushdown types and supported pushdown expressions. Pushdown types include, but are not limited to, predicate pushdown and aggregation pushdown. Different pushdown types have corresponding supported pushdown expressions. For example, predicate pushdown expressions include, but are not limited to, comparison operators (e.g., =, >, <), logical operators (e.g., AND, OR, NOT), pattern matching (e.g., LIKE, ILIKE), set operations (e.g., IN, NOT IN), and range queries (e.g., BETWEEN). Aggregate pushdown expressions include, but are not limited to, aggregate functions (e.g., COUNT(), SUM(), AVG(), MIN(), MAX()), grouping operations (GROUP BY), and deduplication operations (DISTINCT).

[0091] For example, the capability declaration file of the MySQL connector will specify support for predicate pushdown such as =, >, <, BETWEEN, and IN, and support for aggregation pushdown such as count and sum. Correspondingly, its pushdown support information is {"predicate pushdown": ["=",">", "<", "LIKE", "IN"], "aggregation pushdown": ["count", "sum"]}.

[0092] It should be noted that pushdown support information can be obtained not only through capability declaration files but also through test queries. Test queries are sent to candidate data sources, containing various types of operators, functions, and data type conversions. The specific design can be based on the predicate pushdown expression. Then, the success of the pushdown is determined by parsing the query plan or execution logs, and the pushdown support information is determined based on the parsing results. While the test query method is relatively complex to implement, it effectively reveals the actual capabilities of the data source.

[0093] It should be understood that, in actual implementation, the above two methods can be combined to jointly determine the pushdown support information. First, obtain the pushdown support information from the capability declaration file; if this is incomplete, further obtain it through test queries.

[0094] Index support information includes whether the data source supports indexes and the supported index types. For example, MySQL supports B-Tree indexes, and the corresponding index support information is: {"index supported": true, "types": ["BTREE", "FULLTEXT"]}. Index support information can be obtained not only through the capability declaration file but also by querying system tables: querying the system tables of the data source to obtain the index information existing on a specific table. For example, for a MySQL database, you can query its system table INFORMATION SCHEMA.STATISTICS.

[0095] The concurrent connection limit includes the maximum number of concurrent connections to the data source. For example, if the MySQL `maxconnections` is 500, the corresponding concurrent connection limit is: `{"max connections": 500}`. The concurrent connection limit can be obtained not only through the capability declaration file but also through stress testing: simulate establishing multiple concurrent connections to a candidate data source, gradually increasing the number of connections until the data source returns a "connection limit exceeded" error, and record the number of connections before the error is triggered as the actual maximum number of connections.

[0096] Furthermore, the concurrent connection limit can also include the number of currently used connections and / or the number of available connections. The number of available connections can be dynamically calculated by monitoring the number of currently used connections: Available connections = Maximum concurrent connections - Number of currently used connections.

[0097] Custom function support information refers to the information on supported custom functions. For example, if the Oracle data source supports the custom functions "pkg calculate bonus()" and "pkg encrypt id()", the corresponding custom function support information would be {"udfs": ["pkg calculate bonus", "pkg encrypt id"]}.

[0098] Step S14: Construct the data source capability profile library based on the data source capability profiles of each candidate data source; wherein the data source capability profile includes at least one of the following: computing power score, network performance data, pushdown support information, index support information, concurrent connection limit, and custom function support information.

[0099] Based on the data source capability profiles of each candidate data source, a data source capability profile library is constructed. Specifically, these data source capability profiles can be stored in a metadata store.

[0100] The data source capability profile includes at least one of the following: computing power score, network performance data, pushdown support information, index support information, concurrent connection limit, and custom function support information.

[0101] Furthermore, the data source capability profile is described in JSON format, for example: { "source": "mysql-prod" "compute score": 85 "network bandwidth": 1000 "pushdown": ["filter", "aggregation", "limit"], "max connections": 200 "index support": true "custom functions": ["spatial analysis", "text similarity"] }

[0102] The federated query scheduling method provided in this invention introduces a data source capability manager. Through benchmark testing, sending probe packets, and obtaining capability declaration files, it uses unified and quantifiable standards to measure key indicators such as computing power scores, network performance data, pushdown support information, index support information, concurrent connection limits, and custom function support information of different target data sources. This constructs a dynamic, multi-dimensional profile of data source capabilities, enabling accurate perception of heterogeneous environments and providing a solid data foundation for subsequent intelligent optimization and scheduling.

[0103] Based on any of the above embodiments Figure 4 This is the third flowchart of the federated query scheduling method provided by the present invention, as shown below. Figure 4 As shown, step S130 includes: step S131, step S132, step S133 and step S134.

[0104] Step S131: Calculate the network cost and computation cost of different execution strategies based on the target data source capability profile and the target statistical information.

[0105] In this embodiment, analysis reveals that Trino's original cost-based optimizer uses a traditional single-database cost model, primarily calculating CPU and I / O costs within the Trino cluster, while completely ignoring the costs of interacting with external data sources. Due to the inability to obtain accurate external information and the flawed cost model, the generated distributed execution plans are often suboptimal, or even the worst, in federated query scenarios. Therefore, this embodiment introduces two key external costs—network cost and computational cost—to improve the cost model, constructing a global cost model. This model calculates the total cost of any unworkable execution strategy, and the target execution strategy is determined based on this total cost.

[0106] Network cost refers to the cost incurred in transferring data between the federated query engine and external data sources.

[0107] Computational cost refers to the cost of computing resources consumed when performing pushdown operations at an external data source, including the CPU, memory, and other resources consumed by the data source.

[0108] The execution strategy includes pushdown and connection strategies. The pushdown strategy includes push-down and pull-back, while the connection strategy includes connection order and connection algorithm.

[0109] Based on the target data source capability profile and target statistics, calculate the network cost and computation cost for different execution strategies.

[0110] Specifically, the target data source capability profile includes the target computing power score and target bandwidth, while the target statistics include target table-level statistics and target column-level statistics. For each execution strategy, the network cost is obtained as follows: based on the target table-level and target column-level statistics, the target data volume is determined; then, based on the target data volume, target bandwidth, and preset network coefficients, the network cost is calculated. For each execution strategy, the cost calculation process is as follows: based on the initial logical plan and execution strategy, the query complexity is determined; based on the query complexity, target computing power score, and preset computing coefficients, the computational cost is calculated.

[0111] Step S132: Obtain the CPU cost and I / O cost required for different execution strategies during execution.

[0112] CPU cost refers to the estimated CPU resources consumed when performing operations on the local data source of the query or on the coordinating node of the Trino query engine. Specifically, it includes the time cost of all operations that require CPU, such as data filtering, joining, sorting, aggregation, and function calculation.

[0113] IO cost refers to the estimated disk I / O resources required when performing data reads either locally on the data source executing the query or within the Trino query engine.

[0114] In one implementation, the CPU cost can be the product of the basic cost of the connection operation and the data volume factor, and the I / O cost can be the cost of reading and writing intermediate results.

[0115] In another implementation, the CPU cost can be the product of the number of rows processed, the cost per row processed, and the operation complexity factor, while the IO cost can be the product of the amount of data scanned, the cost per byte read, and the weight of the data source.

[0116] Step S133: Summate the CPU cost, the IO cost, the network cost, and the computation cost to obtain the total cost for different execution strategies.

[0117] The global cost model is as follows: Total Cost=CPU Cost+IO Cost+Network Cost+Compute Cost Where Total Cost represents the total cost, CPU Cost represents the CPU cost, IO Cost represents the IO cost, Network Cost represents the network cost, and Compute Cost represents the computation cost.

[0118] For each execution strategy, after obtaining the CPU cost, I / O cost, network cost, and computation cost, the CPU cost, I / O cost, network cost, and computation cost are summed according to the global cost model described above to obtain the total cost of different execution strategies.

[0119] Step S134: Determine the execution strategy corresponding to the minimum value in the total cost as the target execution strategy.

[0120] The execution strategy corresponding to the minimum total cost is determined as the target execution strategy.

[0121] The cost evaluation process for different execution strategies will be discussed below.

[0122] Current technologies lack an intelligent, cost-based pushdown decision-making mechanism. They often blindly push down all possible operations regardless of whether the target data source is capable of handling such computations; conversely, they are overly conservative, pulling all data back to Trino for processing, thus missing the opportunity to utilize the computing power of high-performance data sources. Both scenarios result in massive cross-network data transfer volumes, causing network congestion and making Trino worker nodes performance bottlenecks. In other words, existing pushdown strategies are rigid and prone to causing unnecessary network and computational loads. The global cost model described above can accurately evaluate the true costs of both pushdown and pull-back strategies, thereby selecting the optimal solution. This approach maximizes the efficiency of pushdown computation, ensures that computations are executed on the most suitable data source, significantly reduces cross-network data transfer volumes, alleviates network bandwidth pressure and the computational load on the Trino cluster, and thus improves query performance.

[0123] For example, suppose the federated query engine Trino needs to process a query that involves retrieving data from data source A and applying a filter condition. The federated cost optimizer needs to decide whether to push the filtering operation down to local execution on data source A, or to pull all the data from data source A back to the Trino coordinating node before performing the filtering.

[0124] Determine the cost of pushdown calculation from the perspective of the Trino federated cost optimizer. The specific formula for the cost of Cost pushdown is as follows: Cost pushdown = CPU Cost local + IO Cost local + [(Data_Volume_result / Bandwidth) × Network Coefficient]. Here, CPU Cost local is the cost of performing a filtering operation on data source A, IO Cost local represents the cost of scanning data on data source A, and (Data Volume result / Bandwidth) × Network Coefficient represents the network cost, specifically referring to the cost of transmitting only the small amount of filtered result Data Volume result.

[0125] Determine the cost of pulling back data from the perspective of the Trino federated cost optimizer. The specific formula for the cost of Cost pull is as follows: Cost pull = IO Cost local + (Data Volume full / Bandwidth) × NetworkCoefficient + CPU Cost central. Here, IO Cost local represents the cost of scanning data on data source A, (Data Volume full / Bandwidth) × Network Coefficient represents the network cost, specifically referring to the cost of transmitting the full volume of data Data Volume full back to Trino, and CPU Cost central represents the cost of performing a filtering calculation on the Trino coordinator node.

[0126] If Cost pushdown < Cost pull, then pushdown is more optimal. When pushdown is more optimal, it is usually because only a smaller result set (Data Volume result) needs to be transmitted after pushdown. Although it increases the CPU cost at the data source end, it saves a large amount of network cost.

[0127] Through the above method, it is possible to intelligently decide whether to pushdown, how to sort the Join, and which algorithm to select, thereby significantly reducing unnecessary data movement and reducing query latency from the source.

[0128] For the join strategy, it includes determining the join order and join algorithm.

[0129] Different join orders can lead to significant differences in the amount of data in the intermediate result set. The federated cost optimizer needs to select the optimal join order from all possible arrangements. Specifically, the federated cost optimizer enumerates multiple join orders, uses target statistics to estimate the amount of intermediate data generated after each join step, and then calculates the total cost for each join order based on the estimated amount of intermediate data. Finally, it selects the join order with the lowest total cost as the target join order.

[0130] Join algorithms include, but are not limited to: Hash Join, Sort-Merge Join, and Nested Loop Join. For the same join operation, the federated cost optimizer uses a global cost model to estimate the total cost required to use different join algorithms, and then determines the join algorithm corresponding to the minimum total cost as the target join algorithm.

[0131] The target connection strategy includes the target connection order and target connection algorithm mentioned above.

[0132] Through the quantification and comparison based on total cost described above, the federated cost optimizer can select the optimal join order and the most suitable join algorithm. Compared to selection based on fixed rules, this embodiment of the invention fully considers the dynamic changes in data characteristics and system resources, thereby achieving a significant improvement in query performance in a heterogeneous federated query environment.

[0133] The federated query scheduling method provided in this invention, based on the traditional single-database cost model, adds consideration to network transmission and heterogeneous computing capabilities in federated queries. Specifically, in addition to CPU and I / O costs, network and computation costs are added, constructing a more comprehensive global cost model. This global cost model takes into account both internal resource consumption and the characteristics of external data sources, avoiding any single resource becoming a performance bottleneck, thereby shortening query time, reducing network transmission volume, and improving overall resource utilization. Furthermore, by utilizing cross-data source capability profiles and statistical information for comprehensive evaluation, intelligent execution strategies can be determined, which helps generate a globally optimal execution plan.

[0134] Based on any of the above embodiments, the target data source capability profile includes the target computing capability score and the target bandwidth, the target statistical information includes target table-level statistical information and target column-level statistical information, and step S131 includes: step S1311, step S1312, step S1313 and step S1314.

[0135] Step S1311: For each execution strategy, determine the target data volume based on the target table-level statistics and the target column-level statistics.

[0136] Step S1312: Calculate the network cost based on the target data volume, the target bandwidth, and the preset network coefficients.

[0137] Network cost refers to the cost incurred in transferring data between the federated query engine and external data sources. It is calculated as follows: Network Cost=(Data Volume / DataSource Bandwidth)×Network Coefficient; Among them, Network Cost represents network cost, Data Volume represents data volume, DataSourceBandwidth represents data source bandwidth, and Network Coefficient represents network coefficient.

[0138] Specifically, data volume represents the amount of data that needs to be transferred from the remote data source to the node performing subsequent operations (such as final connection, aggregation); data source bandwidth represents the network transmission bandwidth between the data source and the data source storing the data; and the network coefficient is a preset weighting coefficient used to adjust the importance of network transmission costs in the total cost. The network coefficient is set because the Data Volume / DataSource Bandwidth calculation is a theoretical time, while in actual networks there are other influencing factors such as latency, jitter, and protocol overhead. This coefficient is used to simulate these factors, amplifying the theoretical time to a more realistic cost. Its value is usually greater than 1.

[0139] For each execution strategy, the target data volume is first determined based on the target table-level statistics and target column-level statistics. Then, the target data volume and target bandwidth are substituted into the above formula to calculate the network cost based on the target data volume, target bandwidth, and preset network coefficients.

[0140] Step S1313: Determine the query complexity based on the initial logical plan and the execution strategy.

[0141] Step S1314: Calculate the computation cost based on the query complexity, the target computing power score, and the preset computation coefficient.

[0142] Computational cost refers to the cost of computing resources consumed when performing a pushdown operation on an external data source, and is calculated as follows: Compute Cost=(Query Complexity / DataSource Compute Score)×ComputeCoefficient; Wherein, Compute Cost represents the computation cost, Query Complexity represents the query complexity, DataSource Compute Score represents the data source's computational capability score, and Compute Coefficient represents the computation coefficient.

[0143] Specifically, query complexity represents a quantitative assessment of the computational burden of a query operation. This is a comprehensive metric calculated by the federated cost optimizer based on the structure of the query plan tree and the type of operation. Specifically, the initial logical plan is analyzed, and the basic query complexity is estimated based on the operation type, number of operations, and data volume. Then, for each execution strategy, the estimated actual query complexity is adjusted based on the basic query complexity and the strategy-specific operation distribution.

[0144] A data source computing power score is a relative score representing the computing power of the data source server itself. In a heterogeneous federated environment, the hardware capabilities of different data sources may vary significantly. This score is used to standardize the processing speed of different data sources; a higher score indicates stronger computing power.

[0145] The computation coefficient is a preset weighting factor used to adjust the importance of the cost differences in performing computations on data sources with varying computing capabilities. Similar to the network coefficient, it is used to amplify or reduce the impact of differences in computing capabilities. For example, if an environment is highly heterogeneous (with large differences in computing capabilities), this coefficient can be set higher, causing the optimizer to be more inclined to push complex computations down to high-performance data sources.

[0146] For each execution strategy, the query complexity is first determined based on the initial logical plan and execution strategy. Then, the query complexity and the target computing power score are substituted into the above formula to calculate the computational cost based on the query complexity, the target computing power score, and the preset computation coefficients.

[0147] The federated query scheduling method provided in this invention estimates the target data volume based on target table-level and column-level statistics, then accurately quantifies the network cost by combining data source bandwidth and preset network coefficients. Simultaneously, it determines the query complexity based on the initial logical plan and execution strategy, and accurately evaluates the computation cost using data source computing power scores and preset computation coefficients. This constructs a comprehensive and quantitative cost evaluation model, effectively overcoming the limitations of traditional optimizers that rely on empirical estimation. It can provide accurate cost prediction for query optimization in heterogeneous data source environments, thereby significantly improving the accuracy of execution strategy selection.

[0148] Based on any of the above embodiments, after step S140, the method further includes steps S150 and S160.

[0149] Step S150: Analyze the task characteristics of each execution stage in the distributed execution plan. The task characteristics are determined based on the physical operators and the target data source capability profile.

[0150] Analysis revealed that Trino's native scheduling strategy is static and indiscriminate, often leading to scheduling problems. For example, assigning a task requiring extensive interaction with a high-latency data source to a worker node with limited network bandwidth significantly amplifies latency and fails to achieve optimal utilization of cluster resources. Furthermore, issuing overloaded requests to a weak data source impacts system stability. In other words, Trino's native scheduling strategy lacks flexibility and specificity, failing to differentiate scheduling based on task characteristics and the real-time status of the data source. Therefore, this invention employs secondary scheduling based on task characteristics and data source capability profiles, achieving precise matching between tasks and worker node resources, thereby greatly improving cluster resource utilization and system stability.

[0151] like Figure 2 As shown, the federated query scheduling system of this invention adds an elastic resource scheduler to the original Trino architecture. This elastic resource scheduler is responsible for intelligent scheduling based on data source characteristics and cluster load.

[0152] Analyze the task characteristics of each execution stage in the distributed execution plan. These task characteristics include, but are not limited to: computational intensity, I / O intensity, and target data source type.

[0153] The computational and I / O intensive aspects can be determined based on the types of the main physical operators within the execution phase. For example, if the execution phase includes at least one of the following operations: complex aggregation, join, sorting, or user-defined functions, the computational intensity is high because these operations require a large number of CPU cycles, thus classifying the task type of this execution phase as computationally intensive. Similarly, if the execution phase includes at least one of the following operations: full table scan, data shuffling, or writing results, the I / O intensive aspect is high because these operations require high-throughput disk or network I / O but have low CPU requirements, thus classifying the task type of this execution phase as I / O intensive.

[0154] Target data source types include high-performance data sources and high-latency data sources, which can be determined through the computing power score and bandwidth in the target data source capability profile. Data sources with high computing power scores and high bandwidth are high-performance data sources, and correspondingly, tasks that point to data sources with high computing power scores and high bandwidth are high-performance data source tasks. Data sources with low computing power scores and low bandwidth are high-latency data sources, and tasks that point to data sources with low computing power scores and low bandwidth are high-latency data source tasks.

[0155] Step S160: Schedule tasks for each execution stage based on the task characteristics and the real-time load of the working nodes.

[0156] Real-time load refers to the current resource usage status of a worker node, including real-time metrics such as CPU utilization, memory utilization, network bandwidth utilization, and disk I / O utilization.

[0157] Tasks are scheduled for each execution stage based on task characteristics and the real-time load of worker nodes. For example, tasks involving compute-intensive and / or high-performance data sources are selected using worker nodes with strong computing power. Conversely, tasks involving I / O-intensive and / or high-latency data sources are selected using worker nodes with ample network bandwidth.

[0158] By scheduling computationally intensive tasks and / or high-performance data source tasks to high-computation worker nodes, and I / O-intensive tasks and / or high-latency data source tasks to high-bandwidth worker nodes, resource contention and bottlenecks are avoided. At the same time, it protects less capable data sources from overloaded requests, enhancing the overall robustness of the system.

[0159] Furthermore, during execution, the task's execution status is monitored, and resource allocation strategies are dynamically adjusted based on the monitoring data. In one implementation, when slow task execution is detected, corresponding resources are increased based on the task's characteristics. For example, for compute-intensive tasks, the number of CPU cores can be increased or more powerful worker nodes can be allocated; for I / O-intensive tasks, network bandwidth can be increased or data locality strategies can be adjusted. In another implementation, when unbalanced worker node load is detected, tasks to be executed are reassigned or currently executing tasks are migrated.

[0160] The federated query scheduling method provided in this invention performs secondary scheduling based on the data source capability profile and task characteristics, achieving precise matching between tasks and worker node resources, and greatly improving cluster resource utilization and system stability.

[0161] Based on any of the above embodiments, step S140 includes: step S141, step S142 and step S143.

[0162] Step S141: According to the target execution strategy, convert the logical operators in the initial logical plan into physical operators.

[0163] Step S142: Based on the target data source capability profile and the target statistical information, mark the execution position of the physical operator and insert data exchange nodes between the physical operators.

[0164] The initial logical plan is an abstract execution plan generated after parsing the SQL query. It describes the sequence of operations to be executed (such as scanning, filtering, joining, aggregation, etc.) and their logical relationships. It does not involve the specific execution algorithm and location.

[0165] Physical operators are the concrete implementations of logical operators, defining how they are performed.

[0166] After determining the target execution strategy, the logical operators in the initial logical plan are transformed into physical operators according to the target execution strategy. The execution location of the physical operators is then marked based on the target data source capability profile and target statistics. In this way, the initial logical plan of what to do is transformed into a physical plan of how and where to do it. Then, data exchange nodes are inserted between the physical operators to explicitly indicate the operation nodes for data transfer between different computing nodes or data sources, such as Shuffle (repartitioning) and Broadcast.

[0167] For example, suppose you need to execute the following SQL query in a heterogeneous environment containing a MySQL data source and a Spark computing cluster: SELECT u.user name, COUNT(o.order id),AVG(o.amount) FROM users u JOINorders o ON u.user id=o.user id WHERE u.age>30 AND o.create date>= '2024-01-01' GROUP BY u.user id,u.user name.

[0168] By parsing the above SQL, a logical plan tree is generated, whose structure is as follows: DataScan(users)->Filter(age>30)->DataScan(orders)->Filter(create date>=...)->Join(user id)->Aggregate(groupby,count,avg)->Result.

[0169] Assuming the target execution strategy is to push the filtering conditions to the data source, and the target connection strategy is to prioritize broadcast connections to reduce shuffle overhead, this strategy takes effect when the small table data volume is less than 100MB.

[0170] The obtained MySQL data source capability profile is as follows: Computational capability score = 70, Network bandwidth = 1Gbps, Pushdown support information = [Supports predicate pushdown, does not support join pushdown, does not support aggregation pushdown], Index support information = [The users table has a secondary index on the age column]. The obtained Spark data source capability profile is as follows: Computational capability score = 95, Network bandwidth = 10Gbps, Pushdown support information = [Supports all pushdown operations].

[0171] The obtained target statistics include: ① users table: total number of rows 10 million, with an estimated number of rows with age > 30 of 3 million; ② orders table: total number of rows 200 million, with an estimated number of rows with create date >= '2024-01-01' of 50 million; ③ after filtering, the estimated data size of the users table is 60MB, and the estimated data size of the orders table is 1.2GB.

[0172] Based on the above, the logical operators in the initial logical plan are first converted into physical operators. Specifically: ① For the logical operator Filter(age>30): based on the target pushdown strategy and MySQL's pushdown support information (supports predicate pushdown), it is converted into the physical operator DataSourceFilter; ② For the logical operator Filter(create date>=...): similarly, it is converted into DataSourceFilter; ③ For the logical operator Join: based on the target join strategy, it is determined that the amount of data in the users table after filtering (60MB) is less than the strategy threshold (100MB), so BroadcastHashJoin is selected as the physical join operator; ④ For the logical operator Aggregate: since MySQL's capability profile shows that it does not support aggregation pushdown, it is converted into HashAggregate, which is executed in the computing engine.

[0173] Next, the execution locations of the physical operators are marked. Specifically, the execution locations of the two DataSourceFilter physical operators are marked as MySQL data sources, and the execution locations of the BroadcastHashJoin and HashAggregate physical operators are marked as Spark data sources.

[0174] Next, data exchange nodes are inserted between the physical operators. Specifically, since BroadcastHashJoin needs to be executed in Spark, and its right table (smaller table) data resides in MySQL, a BroadcastExchange node needs to be inserted after the MySQL DataSourceFilter (users table) and before Spark's BroadcastHashJoin. This node is responsible for broadcasting the filtered users table data from MySQL to all nodes in the Spark cluster. Simultaneously, a PartitioningExchange node is inserted after the MySQL DataSourceFilter (orders table) and before Spark's BroadcastHashJoin. This node is responsible for transferring the filtered orders table data from MySQL to the Spark nodes according to a specific partitioning method, preparing for the connection.

[0175] Step S143: Based on the data exchange node, the physical operator is divided into multiple execution stages, and based on the target data source capability profile, the physical operator in each execution stage is adapted and optimized to generate a distributed execution plan.

[0176] Using data exchange nodes as boundaries, physical operators are divided into multiple execution phases, thus dividing physical plans into multiple execution phases.

[0177] For example, the above example can be divided into 3 execution stages: stage 1 is the DataSourceFilter of the users table in MySQL, stage 2 is the DataSourceFilter of the orders table in MySQL, and stage 3 is BroadcastHashJoin and HashAggregate in Spark.

[0178] Then, based on the target data source capability profile, the physical operators within each execution stage are adapted and optimized to generate a distributed execution plan. The target data source capability profile also includes target pushdown information, target index support information, and target concurrent connection limits.

[0179] The adaptation optimizations include, but are not limited to: replacing the memory aggregation physical operator with the data source aggregation physical operator for data sources that support specific aggregation pushdown based on the target pushdown information; replacing the full table scan physical operator with the index scan physical operator for data sources that support indexes based on the target index support information; and adjusting the parallelism of the execution phase based on the target concurrent connection limit.

[0180] Furthermore, the target data source capability profile also includes target custom function support information, which can be used to determine whether to push the custom function down to the target data source for execution.

[0181] For example, in the example above, in stage 1, based on MySQL's index support information, DataSourceFilter is optimized to the more efficient IndexRangeScan physical operator, which directly uses the index to quickly locate the data instead of filtering after a full table scan.

[0182] The federated query scheduling method provided in this invention achieves precise allocation of computational tasks by transforming logical operators into physical operators based on the target execution strategy, and marking execution positions and inserting data exchange nodes according to the target data source capability profile and target statistical information, while minimizing cross-network data transmission. Then, by dividing the execution phases and performing adaptation optimization based on the target data source capability profile, the characteristics and advantages of each heterogeneous data source are fully utilized, resulting in more balanced and efficient overall system resource utilization. Through the above methods, a significant improvement in query performance, a substantial reduction in network transmission overhead, and the maximization of heterogeneous computing resource utilization are achieved collaboratively.

[0183] Furthermore, through performance tests of typical cross-source join queries, the performance of the federated query scheduling method provided in this embodiment of the invention is compared with that of the existing federated query scheduling method (i.e., native Trino). The results are as follows: Figure 5 As shown.

[0184] according to Figure 5 It is evident that the query time of the native Trino increases dramatically with the increase in data volume. This is because its optimizer and scheduler cannot perceive the heterogeneity of the data source, easily generating suboptimal execution plans and coarse resource allocation, resulting in a large amount of unnecessary network transmission and computational waste, and a significant performance bottleneck. In contrast, the query time of the method in this invention is significantly lower than that of the native Trino, and the performance advantage becomes more obvious with larger data volumes, with an average query time reduction of up to 40%-60%.

[0185] The federated query scheduling apparatus provided by the present invention is described below. The federated query scheduling apparatus described below can be referred to in correspondence with the federated query scheduling method described above.

[0186] Figure 6 This is a schematic diagram of the structure of the federated query scheduling device provided by the present invention, as shown below. Figure 6 As shown, the device includes a request parsing module 610, a data acquisition module 620, a cost evaluation module 630, and a plan generation module 640; wherein: The request parsing module 610 is used to receive query requests, parse the query requests, and generate an initial logical plan; The data acquisition module 620 is used to acquire the target data source capability profile and target statistical information according to the initial logical plan; The cost evaluation module 630 is used to evaluate the total cost of different execution strategies using a global cost model, based on the target data source capability profile and the target statistical information, and to determine the target execution strategy based on the evaluation results. The plan generation module 640 is used to transform the initial logical plan and generate a distributed execution plan based on the target execution strategy, the target data source capability profile and the target statistical information.

[0187] The federated query scheduling device provided in this invention parses query requests to generate an initial logical plan. Then, using a global cost model, it evaluates the total cost of different execution strategies based on the target data source capability profile and target statistics to determine the target execution strategy. By introducing a data source capability profile to perceive heterogeneity differences and using a global cost model to comprehensively evaluate the total cost, the optimal execution strategy can be intelligently determined. Furthermore, based on the target execution strategy, the target data source capability profile, and target statistics, the initial logical plan is transformed to generate a distributed execution plan, which also fully considers the characteristic advantages of each heterogeneous target data source. This invention, through the synergistic effect of heterogeneous data source capability awareness and global cost optimization, significantly reduces query time, improves query performance, and also increases resource utilization and enhances system stability.

[0188] It should be noted that the federated query scheduling device provided in this embodiment of the invention can implement all the method steps implemented in the federated query scheduling method embodiment and can achieve the same technical effect. Here, the parts that are the same as those in the method embodiment and the beneficial effects will not be described in detail.

[0189] Figure 7 An example is a schematic diagram of the physical structure of an electronic device, such as... Figure 7As shown, the XR device may include a processor 710, a communications interface 720, a memory 730, and a communication bus 740. The processor 710, communications interface 720, and memory 730 communicate with each other via the communication bus 740. The processor 710 can invoke logical instructions in the memory 730 to execute a federated query scheduling method. This method includes: receiving a query request, parsing the query request, and generating an initial logical plan; obtaining a target data source capability profile and target statistical information based on the initial logical plan; evaluating the total cost of different execution strategies using a global cost model based on the target data source capability profile and the target statistical information, and determining the target execution strategy based on the evaluation result; and transforming the initial logical plan based on the target execution strategy, the target data source capability profile, and the target statistical information to generate a distributed execution plan.

[0190] Furthermore, the logical instructions in the aforementioned memory 730 can be implemented as software functional units and, when sold or used as independent products, can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, essentially, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0191] On the other hand, the present invention also provides a non-transitory computer-readable storage medium storing a computer program thereon. When executed by a processor, the computer program implements the federated query scheduling method provided in the above embodiments. The method includes: receiving a query request, parsing the query request, and generating an initial logical plan; obtaining a target data source capability profile and target statistical information based on the initial logical plan; evaluating the total cost of different execution strategies using a global cost model based on the target data source capability profile and the target statistical information, and determining a target execution strategy based on the evaluation result; and transforming the initial logical plan based on the target execution strategy, the target data source capability profile, and the target statistical information to generate a distributed execution plan.

[0192] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without any creative effort.

[0193] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0194] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A federated query scheduling method, characterized in that, include: Receive a query request, parse the query request, and generate an initial logical plan; According to the initial logical plan, the target data source capability profile is obtained from the pre-built data source capability profile library, and the target statistical information is obtained. Using the global cost model, the network cost and computation cost of different execution strategies are calculated based on the target data source capability profile and the target statistical information. Obtain the CPU cost and I / O cost required for different execution strategies during execution; The total cost for different execution strategies is obtained by summing the CPU cost, the I / O cost, the network cost, and the computation cost. The execution strategy corresponding to the minimum value among the total costs is determined as the target execution strategy; Based on the target execution strategy, the target data source capability profile, and the target statistical information, the initial logical plan is transformed to generate a distributed execution plan; this includes: converting logical operators in the initial logical plan into physical operators according to the target execution strategy; marking the execution positions of the physical operators according to the target data source capability profile and the target statistical information, and inserting data exchange nodes between the physical operators; dividing the physical operators into multiple execution stages according to the data exchange nodes, and adapting and optimizing the physical operators in each execution stage according to the target data source capability profile to generate a distributed execution plan; The adaptation and optimization of physical operators within each execution phase includes: replacing memory aggregation physical operators with data source aggregation physical operators for data sources that support specific aggregation pushdown, based on the target pushdown information in the target data source capability profile; replacing full table scan physical operators with index scan physical operators based on the target index support information in the target data source capability profile; and adjusting the parallelism of the execution phase based on the target concurrent connection limit in the target data source capability profile.

2. The federated query scheduling method according to claim 1, characterized in that, The step of obtaining the target data source capability profile from the pre-built data source capability profile library and obtaining target statistical information according to the initial logical plan includes: Obtain the target data source and target data object from the initial logical plan; Obtain the target data source capability profile corresponding to the target data source from the pre-built data source capability profile library; Obtain target statistical information corresponding to the target data object from a pre-collected statistical information database.

3. The federated query scheduling method according to claim 2, characterized in that, Before obtaining the target data source capability profile corresponding to the target data source from the pre-built data source capability profile library, the method further includes: By benchmarking, a first performance metric of the benchmark data source and a second performance metric of the candidate data source are obtained respectively. Based on the first performance metric and the second performance metric, the computing power score of the candidate data source is determined. By sending probe packets to the candidate data source, the network performance data of the candidate data source can be obtained; Obtain the capability declaration file from the connector corresponding to the candidate data source, and obtain pushdown support information, index support information, concurrent connection limit and custom function support information from the capability declaration file; Based on the data source capability profiles of each candidate data source, a data source capability profile library is constructed; wherein, the data source capability profile includes at least one of the following: computing power score, network performance data, pushdown support information, index support information, concurrent connection limit, and custom function support information.

4. The federated query scheduling method according to claim 1, characterized in that, The target data source capability profile includes a target computing capability score and a target bandwidth; the target statistics include target table-level statistics and target column-level statistics; and the calculation of network costs and computing costs for different execution strategies based on the target data source capability profile and the target statistics includes: For each execution strategy, the target data volume is determined based on the target table-level statistics and the target column-level statistics. The network cost is calculated based on the target data volume, the target bandwidth, and the preset network coefficients. The query complexity is determined based on the initial logical plan and the execution strategy. The computational cost is calculated based on the query complexity, the target computational capability score, and the preset computation coefficient.

5. The federated query scheduling method according to any one of claims 1 to 4, characterized in that, After transforming the initial logical plan based on the target execution strategy, the target data source capability profile, and the target statistical information to generate a distributed execution plan, the process further includes: The task characteristics of each execution stage in the distributed execution plan are analyzed, and the task characteristics are determined based on the physical operators and the target data source capability profile. Based on the task characteristics and the real-time load of the working nodes, tasks in each execution stage are scheduled.

6. A federated query scheduling device, characterized in that, include: The request parsing module is used to receive query requests, parse the query requests, and generate an initial logical plan; The data acquisition module is used to acquire the target data source capability profile from the pre-built data source capability profile library and acquire target statistical information according to the initial logical plan. The cost evaluation module is used to calculate the network cost and computation cost of different execution strategies based on the target data source capability profile and the target statistical information using a global cost model. Obtain the CPU cost and I / O cost required for different execution strategies during execution; The total cost for different execution strategies is obtained by summing the CPU cost, the I / O cost, the network cost, and the computation cost. The execution strategy corresponding to the minimum value among the total costs is determined as the target execution strategy; The plan generation module is used to transform the initial logical plan based on the target execution strategy, the target data source capability profile, and the target statistical information to generate a distributed execution plan; Specifically, it is used to: convert logical operators in the initial logical plan into physical operators according to the target execution strategy; mark the execution position of the physical operators according to the target data source capability profile and the target statistical information, and insert data exchange nodes between the physical operators; Based on the data exchange node, the physical operator is divided into multiple execution stages, and based on the target data source capability profile, the physical operator in each execution stage is adapted and optimized to generate a distributed execution plan. The adaptation and optimization of physical operators within each execution phase includes: replacing memory aggregation physical operators with data source aggregation physical operators for data sources that support specific aggregation pushdown, based on the target pushdown information in the target data source capability profile; replacing full table scan physical operators with index scan physical operators based on the target index support information in the target data source capability profile; and adjusting the parallelism of the execution phase based on the target concurrent connection limit in the target data source capability profile.

7. An electronic device comprising a memory, a processor, and a computer program stored in the memory and running on the processor, characterized in that, When the processor executes the computer program, it implements the federated query scheduling method as described in any one of claims 1 to 5.

8. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the federated query scheduling method as described in any one of claims 1 to 5.

Images