Federal query method and device, electronic equipment and storage medium
By building a query relationship tree and calculating the penalty and specification weight of the data source, and optimizing the federal query path, the problem of low efficiency of cross-data source query in big data analysis is solved, and more efficient data processing and analysis is achieved.
Patent Information
- Application Number
- CN202411080799.X
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-07
- Publication Date
- 2024-11-26
AI Technical Summary
In the field of big data analysis, federated queries across different data sources are less efficient, mainly due to the data structure complexity of the data source, network latency and bandwidth limitations caused by geographical distribution, and the load and performance bottlenecks of the data source.
By building a query relationship tree, calculate the root node generation value based on the penalty weight and specification weight of the data source, determine the query path of the minimum generation value for the query, and perform federated queries, and optimize data transmission and processing paths.
It significantly improves the query speed and efficiency in a variety of different data sources in federal query scenarios, ensuring more efficient data processing and analysis.
Smart Images

Figure CN119025575A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of big data technology, and in particular to a federated query method, device, electronic device and storage medium. Background Art
[0002] In the field of big data analysis, federated queries are often involved between various data sources with different data structures, and even federated queries across different regions and different types of data sources. The data sources of federated queries are relatively complex, resulting in low efficiency of federated queries. Summary of the invention
[0003] The main purpose of the embodiments of the present application is to propose a federated query method, device, electronic device and storage medium to improve the efficiency of federated query.
[0004] To achieve the above object, an embodiment of the present application provides that the method comprises the following steps:
[0005] Identify data tables from multiple different data sources;
[0006] Construct different query relationship trees according to each of the data tables; wherein the connection tables obtained after the federated query between each of the data tables are used as nodes of the query relationship tree;
[0007] Calculating the root node cost value of each query relationship tree according to the penalty weight and specification weight of each data source;
[0008] A federated query is performed on each of the data tables according to a federated query path; wherein the query relationship tree corresponding to the minimum root node cost value serves as the federated query path.
[0009] In some embodiments, the step of calculating the root node cost value of each query relationship tree according to the penalty weight and specification weight of each data source includes the following steps:
[0010] Calculate the cost of all the nodes in each query relationship tree according to the penalty weight and the specification weight of each data source;
[0011] The cost value of each child node under each parent node in each query relationship tree is added to the cost value of the corresponding parent node to obtain a total cost value; wherein the total cost value of the root node is used as the root node cost value.
[0012] In some embodiments, the calculating the cost of each node in the query relationship tree according to the penalty weight and the specification weight of each data source includes the following steps:
[0013] Calculate the cost value of each node in the query relationship tree according to a cost calculation formula;
[0014] The cost calculation formula is:
[0015] C self =RowCount×(1+W cpu +W eo +W net +W ds ×W dscpus );
[0016] Among them, C self represents the cost value of the node itself, RowCount represents the total number of rows of data expected to be output by the node, and W cpu represents the CPU weight of the node corresponding to the data source, W io represents the disk input and output weight of the node corresponding to the data source, W net represents the network cost weight of the node corresponding to the data source, W ds represents the penalty weight of the node corresponding to the data source, W dscpus Indicates the specification weight of the node corresponding to the data source.
[0017] In some embodiments, the method further comprises the following steps:
[0018] Determine a mapping table for each of the data tables; wherein the data source where the mapping table is located is used as a mapping data source;
[0019] Calculate the own cost value corresponding to the mapping table according to the penalty weight and the specification weight of the mapping data source;
[0020] If the intrinsic cost value of the mapping table is less than the intrinsic cost value of the corresponding data table, the corresponding data table in the query relationship tree is replaced with the mapping table.
[0021] In some embodiments, before determining the mapping table of each of the data tables, the method further includes the following steps:
[0022] Determining whether the mapping table needs to be created according to the resource occupancy status of the mapping data source for which the mapping table is to be created and the specification, data volume and data transmission cost of the mapping data;
[0023] If necessary, the mapping table is created in the mapping data source.
[0024] In some embodiments, before calculating the root node cost value of each query relationship tree according to the penalty weight and specification weight of each data source, the method further includes the following steps:
[0025] Determine the query time consumed by each of the data sources when querying data of the same magnitude;
[0026] The penalty weight of each of the data sources is determined according to each of the query times.
[0027] In some embodiments, before calculating the root node cost value of each query relationship tree according to the penalty weight and specification weight of each data source, the method further includes the following steps:
[0028] The reciprocal of the number of CPU cores of each of the data sources is determined as the specification weight.
[0029] To achieve the above objective, another aspect of an embodiment of the present application provides a federated query device, the device comprising:
[0030] A data table determination unit, used to determine data tables of multiple different data sources;
[0031] A relationship tree construction unit, used to construct different query relationship trees according to each of the data tables; wherein the connection tables obtained after the federated query between each of the data tables are used as nodes of the query relationship tree;
[0032] A cost value calculation unit, used to calculate the cost value of the root node of each query relationship tree according to the penalty weight and specification weight of each data source;
[0033] A federated query unit is used to perform a federated query on each of the data tables according to a federated query path; wherein the query relationship tree corresponding to the minimum root node cost value serves as the federated query path.
[0034] In some embodiments, the cost value calculation unit includes:
[0035] A first intrinsic cost calculation unit, configured to calculate intrinsic cost values of all the nodes in each of the query relationship trees according to the penalty weight and the specification weight of each of the data sources;
[0036] The own cost value adding unit is used to add the own cost value of each child node under each parent node in each query relationship tree to the own cost value of the corresponding parent node to obtain a total cost value; wherein the total cost value of the root node is used as the root node cost value.
[0037] In some embodiments, the first self-cost calculation unit includes:
[0038] The self-cost calculation subunit is used to calculate the self-cost value of each node in the query relationship tree according to the cost calculation formula;
[0039] The cost calculation formula is:
[0040] C self =RowCount×(1+W cpu +W io +W net +W ds ×W dscpus );
[0041] Among them, C self represents the cost value of the node itself, RowCount represents the total number of rows of data expected to be output by the node, and W cpu represents the CPU weight of the node corresponding to the data source, W io represents the disk input and output weight of the node corresponding to the data source, W net represents the network cost weight of the node corresponding to the data source, W ds represents the penalty weight of the node corresponding to the data source, W dscpus Indicates the specification weight of the node corresponding to the data source.
[0042] In some embodiments, the apparatus further comprises a mapping table processing unit, wherein the mapping table processing unit comprises:
[0043] A mapping table determination unit, used to determine a mapping table of each of the data tables; wherein the data source where the mapping table is located is used as a mapping data source;
[0044] A second own cost value calculation unit, configured to calculate the own cost value corresponding to the mapping table according to the penalty weight and the specification weight of the mapping data source;
[0045] A mapping table replacement unit is used to replace the corresponding data table in the query relationship tree with the mapping table if the intrinsic cost value of the mapping table is less than the intrinsic cost value of the corresponding data table.
[0046] In some embodiments, the apparatus further comprises:
[0047] A mapping table determination unit, used for determining whether it is necessary to create the mapping table according to the resource occupancy status of the mapping data source for creating the mapping table and the specification, data volume and data transmission cost of the mapping data before determining the mapping table of each data table;
[0048] A mapping table creating unit is used to create the mapping table at the mapping data source before determining the mapping table of each of the data tables, if necessary.
[0049] In some embodiments, the apparatus further comprises:
[0050] A query time determination unit, used to determine the query time consumed by each of the data sources when querying data of the same magnitude before calculating the root node cost value of each of the query relationship trees according to the penalty weight and specification weight of each of the data sources;
[0051] A penalty weight determination unit is used to determine the penalty weight of each data source according to each query time before calculating the root node cost value of each query relationship tree according to the penalty weight and specification weight of each data source.
[0052] In some embodiments, the apparatus further comprises:
[0053] The specification weight determination unit is used to determine the reciprocal of the number of CPU cores of each data source as the specification weight before calculating the root node cost value of each query relationship tree according to the penalty weight and specification weight of each data source.
[0054] To achieve the above-mentioned purpose, another aspect of an embodiment of the present application provides an electronic device, the electronic device comprising a memory and a processor, the memory storing a computer program, and the processor implementing the above-mentioned federated query method when executing the computer program.
[0055] To achieve the above-mentioned purpose, another aspect of an embodiment of the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the above-mentioned federated query method is implemented.
[0056] The embodiments of the present application include at least the following beneficial effects:
[0057] The present application can determine data tables of multiple different data sources; construct different query relationship trees according to each data table; wherein the connection table obtained after the federated query between each data table and each data table is used as the node of the query relationship tree; the root node cost value of each query relationship tree is calculated according to the penalty weight and specification weight of each data source; and each data table is federated queried according to the federated query path; wherein the query relationship tree corresponding to the minimum root node cost value is used as the federated query path. The penalty weight and specification weight can more realistically calculate the root node cost value of each query relationship tree in the federated query scenario. Since the smaller the root node cost value, the higher the federated query efficiency, the present application can determine the optimal federated query path according to the root node cost value, that is, the query relationship tree according to the minimum root node cost value can significantly improve the query speed and efficiency in the federated query scenario of multiple different data sources. BRIEF DESCRIPTION OF THE DRAWINGS
[0058] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.
[0059] Figure 1 A flowchart of a federated query method provided in an embodiment of the present application;
[0060] Figure 2 Provided in the embodiments of this application Figure 1 The step flow chart of S120;
[0061] Figure 3 A flowchart of the mapping table processing steps provided in an embodiment of the present application;
[0062] Figure 4 An example diagram of the cost value of each node of the query relationship tree provided in an embodiment of the present application;
[0063] Figure 5 An example diagram of the cost value of querying each node of the relationship tree after adding the penalty weight and specification weight provided in the embodiment of the present application;
[0064] Figure 6 A schematic diagram of replacing a data table with a mapping table provided in an embodiment of the present application;
[0065] Figure 7 A schematic diagram of the structure of a federated query device provided in an embodiment of the present application;
[0066] Figure 8 A schematic diagram of the structure of a cost value calculation unit provided in an embodiment of the present application;
[0067] Fig. 9 A schematic diagram of the structure of a mapping table processing unit provided in an embodiment of the present application;
[0068] Fig.10 A schematic diagram of the hardware structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION
[0069] In order to make the purpose, technical solutions and advantages of the present application clearer, the present application is further described in detail below in conjunction with the accompanying drawings and examples. It should be understood that the specific embodiments described herein are only used to explain the present application and are not intended to limit the present application. When the following description refers to the drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the embodiments of the present application. They are only examples of devices and methods consistent with some aspects of the embodiments of the present application as detailed in the attached claims.
[0070] It is understood that the terms "first", "second", etc. used in this application can be used to describe various concepts in this article, but unless otherwise specified, these concepts are not limited by these terms. These terms are only used to distinguish one concept from another concept. For example, without departing from the scope of the embodiment of the present application, the first information may also be referred to as the second information, and similarly, the second information may also be referred to as the first information. Depending on the context, the words "if" and "if" as used herein can be interpreted as "at the time of" or "when" or "in response to determination".
[0071] The terms "at least one", "multiple", "each", "any", etc. used in this application, at least one includes one, two or more, multiple includes two or more, each refers to each of the corresponding multiple, and any refers to any one of the multiple.
[0072] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as those commonly understood by those skilled in the art to which this application belongs. The terms used herein are only for the purpose of describing the embodiments of this application and are not intended to limit this application.
[0073] Before describing the embodiments of the present application in detail, some terms and related technologies involved in the embodiments of the present application are first described. Some terms and related technologies involved in the embodiments of the present application are applicable to the following explanations:
[0074] Data source: The source or provider of data. Data sources can be databases, files, web services, APIs, sensors, user input, etc.
[0075] Federated query: A technology for querying across multiple different data sources. Federated query allows users to retrieve information from multiple autonomous, distributed, and heterogeneous data sources without having to migrate the data to a central location.
[0076] JOIN operation: A keyword in SQL used to combine rows in two or more data tables to obtain a JOIN table (i.e., a connection table). This embodiment can query data from each data table through a JOIN operation based on the relationship between certain related columns between the data tables.
[0077] In the field of big data analysis, federated queries of different data sources are often involved, and even federated queries across different regions and different types of data sources. The federated queries of related technologies have the following problems:
[0078] (1) Federated queries usually involve multiple different data sources. Each data source may have different data structures, storage methods, access protocols, etc. Therefore, it is necessary to consider the characteristics and limitations of each data source, which makes federated queries complicated.
[0079] (2) In federated queries, data needs to be transmitted between different data sources, which may cause significant network latency and bandwidth limitations, especially when the data sources are distributed in geographically distant locations. The overhead of data transmission directly affects the query time of federated queries.
[0080] (3) The load and performance of the data source itself will also affect the speed of federated queries. If the data source is processing a large number of concurrent requests or encounters a performance bottleneck (such as a disk I / O bottleneck, CPU bottleneck, etc.), the query time of the federated query may be affected.
[0081] Therefore, the embodiments of the present application provide a federated query method, device, electronic device and storage medium. The technical solution of the present application includes: determining data tables of multiple different data sources; constructing different query relationship trees according to each data table; wherein the connection table obtained after the federated query between each data table and each data table is used as the node of the query relationship tree; calculating the root node cost value of each query relationship tree according to the penalty weight and specification weight of each data source; performing a federated query on each data table according to the federated query path; wherein the query relationship tree corresponding to the minimum root node cost value is used as the federated query path. The root node cost value of each query relationship tree can be more realistically calculated in the federated query scenario through the penalty weight and specification weight. Since the smaller the root node cost value, the higher the federated query efficiency, the present application can determine the optimal federated query path according to the root node cost value, that is, the query relationship tree according to the minimum root node cost value can significantly improve the query speed and efficiency in the federated query scenario of multiple different data sources.
[0082] The embodiment of the present application provides a federated query method, which relates to the field of big data technology. The federated query method provided in the embodiment of the present application can be applied to a terminal, can be applied to a server, or can be software running in a terminal or a server. In some embodiments, the terminal can be a smart phone, a tablet computer, a laptop computer, a desktop computer, a smart speaker, a smart watch, and a car terminal, etc., but is not limited to this; the server side can be configured as an independent physical server, or a server cluster or distributed system composed of multiple physical servers, and can also be configured as a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, CDN, and big data and artificial intelligence platforms. The server can also be a node server in a blockchain network; the software can be an application that implements the federated query method, etc., but is not limited to the above forms.
[0083] The present application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronics, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, etc. The present application can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. The present application can also be practiced in distributed computing environments, in which tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.
[0084] Reference Figure 1 The embodiment of the present application provides a federated query method, which may include but is not limited to S100 to S140, as follows:
[0085] S100: Determine data tables of multiple different data sources.
[0086] It can be understood that in an embodiment of the present application, a federated query can be performed on multiple different data sources, for example, a federated query can be performed on at least three different data sources, where the different data sources may include different types of data sources or data sources of the same type but different specifications; the specifications of the data source may include the hardware configuration or computing performance of the data source, etc.
[0087] It should be noted that each data source in the embodiment of the present application may correspond to a data table.
[0088] S110: construct different query relationship trees according to the data tables; wherein the connection tables obtained after the federation query between the data tables are used as nodes of the query relationship trees.
[0089] Specifically, the connection table can be used as a parent node, and different data tables of the federated query connection table can be used as child nodes. The connection table can be used as both a parent node and a child node, and the data table is used as a child node. The parent node at the top level is the root node, that is, the root node of each query relationship tree is the connection table finally obtained by the federated query.
[0090] It can be understood that although the content of each data table that needs to be federated queried is relatively fixed, each data table can be set at a different level of the query relationship tree, that is, the position of the node of the data table is variable, so the content of the generated connection table is also variable, and then different query relationship trees can be generated according to each data table, and each query relationship tree can correspond to a federal query path.
[0091] S120: Calculate the root node cost value of each query relationship tree according to the penalty weight and specification weight of each data source.
[0092] It is understandable that since the federated query can be performed between multiple different data sources, in order to include the data source itself in the factors affecting the efficiency of the federated query, the embodiment of the present application can combine the penalty weight and specification weight of the corresponding data source when calculating the cost value of each node. Among them, the penalty weight is the weight determined by the type of data source, and the specification weight is the weight determined by the specification of the data source. The specification description of the data source can refer to the description of the above S100 embodiment.
[0093] Combining the penalty weight and the specification weight can more realistically calculate the root node cost of each query relationship tree in the federated query scenario. The smaller the root node cost, the lower the cost of obtaining the connection table finally obtained by the federated query, and the higher the efficiency. Therefore, this embodiment can plan a better federated query path according to the root node cost value to improve the speed of the federated query.
[0094] Further, refer to Figure 2 , S120 may include S121-S122:
[0095] S121: Calculate the cost of all the nodes in each query relationship tree according to the penalty weight and the specification weight of each data source.
[0096] It is understandable that each node has a corresponding own cost value. In order to improve the accuracy of calculating the cost value, in addition to calculating the node's own cost value, if a node has child nodes (the node can be used as a parent node), it is also necessary to calculate the impact of the child nodes under its specifications on the node's own cost value. Therefore, this embodiment can calculate the own cost value of each node separately, and then calculate the final total cost value of the parent node based on the own cost value of each node.
[0097] Furthermore, S121 may include:
[0098] Calculate the cost value of each node in the query relationship tree according to a cost calculation formula;
[0099] The cost calculation formula is:
[0100] C self =RowCount×(1+W cpu +W io +W net +W ds ×W dscpus );
[0101] Among them, C self represents the cost value of the node itself, RowCount represents the total number of rows of data expected to be output by the node, and W cpu represents the CPU weight of the node corresponding to the data source, W io represents the disk input and output weight of the node corresponding to the data source, W net represents the network cost weight of the node corresponding to the data source, W ds represents the penalty weight of the node corresponding to the data source, W dscpus Indicates the specification weight of the node corresponding to the data source.
[0102] Specifically, this embodiment can calculate the cost value of each node according to the above cost calculation formula.
[0103] S122: Add the intrinsic cost value of each child node under each parent node in each query relationship tree to the intrinsic cost value of the corresponding parent node to obtain a total cost value; wherein the total cost value of the root node is used as the root node cost value.
[0104] It can be understood that the final total cost of the parent node includes not only its own cost, but also the cost of all its child nodes. The final total cost of the parent node is calculated as follows:
[0105] C total =C self +C children ;
[0106] Among them, C total Indicates the final total cost of the parent node, C self Indicates the parent node’s own generation value, C children Represents the sum of the cost values of all child nodes under the parent node.
[0107] It should be noted that when a child node under a parent node is the parent node of a node in a lower layer, the final total cost value of the child node is also calculated according to the above total cost value calculation formula. Among them, the parent node of the top layer is the root node, and the final total cost value of the parent node of the top layer is the root node cost value.
[0108] As a further optional implementation, before S120, the embodiment of the present application may further include the following steps:
[0109] Determine the query time consumed by each of the data sources when querying data of the same magnitude;
[0110] The penalty weight of each of the data sources is determined according to each of the query times.
[0111] It is understandable that in this embodiment, different penalty weights can be set for different data sources to reflect that different data source types have different data processing and analysis capabilities, thereby improving the accuracy of the cost value of each node itself.
[0112] As another further optional implementation, before S120, the embodiment of the present application may further include the following steps:
[0113] The reciprocal of the number of CPU cores of each of the data sources is determined as the specification weight.
[0114] Since the data sources have the same type but different specifications, their processing capabilities are also different. Considering that CPU and memory generally have a certain proportional relationship, in order to simplify the problem processing, this embodiment can only take the number of CPU cores of the data source as the basis for measuring the cost value. Since the more cores, the smaller the cost, therefore, if the number of CPU cores of the data source is CPUs, then the specification weight of the data source is
[0115] S130: performing a federated query on each of the data tables according to a federated query path; wherein the query relationship tree corresponding to the minimum root node cost value is used as the federated query path.
[0116] Specifically, this embodiment can first determine the query relationship tree corresponding to the minimum root node cost value, then use the query relationship tree as the federal query path, and then perform federal query according to the federal query path, so as to realize federal query at the optimal cost and improve the speed and efficiency of federal query.
[0117] To further improve the efficiency of federated query, the embodiment of the present application may further include a mapping table processing step, which may include: Figure 3 S141~S143 shown:
[0118] S141: Determine a mapping table for each of the data tables; wherein the data source where the mapping table is located is used as a mapping data source.
[0119] Specifically, this embodiment can use a table mapping method to further optimize data tables of different data sources. Table mapping means that the original data table and the mapping table are across data sources, and the data in the mapping table and the original data table are consistent. This mapping relationship is called table mapping.
[0120] S142: Calculate the own cost corresponding to the mapping table according to the penalty weight and the specification weight of the mapping data source.
[0121] Exemplarily, this embodiment may refer to the implementation of S120 to calculate the cost value of the mapping table itself.
[0122] S143: If the intrinsic cost value of the mapping table is less than the intrinsic cost value of the corresponding data table, the corresponding data table in the query relationship tree is replaced with the mapping table.
[0123] It is understandable that if the cost of the mapping table itself is less than the cost of the corresponding data table itself, the corresponding data table in the query relationship tree is replaced with the mapping table, thereby reducing the root node cost and improving the speed and efficiency of the federated query.
[0124] Furthermore, before S141, the embodiment of the present application may further include:
[0125] Determining whether the mapping table needs to be created according to the resource occupancy status of the mapping data source for which the mapping table is to be created and the specification, data volume and data transmission cost of the mapping data;
[0126] If necessary, the mapping table is created in the mapping data source.
[0127] It is understandable that the present embodiment can create a mapping table according to the control strategy parameters, which include the resource occupancy status of the mapping data source and the specifications, data volume, and data transmission cost of the mapping data, etc. Based on the control strategy parameters, it can be determined whether a mapping table needs to be created when a federated query is performed for the first time, and then the idle resources of each data source can be used to accelerate the query, and data sources with stronger computing power can be used as much as possible to accelerate data processing and analysis.
[0128] Next, the solution of the embodiment of the present application will be introduced and explained in conjunction with specific application examples:
[0129] This embodiment can perform effective query optimization for the scenario of performing multi-table JOIN operations across data sources in a federated query.
[0130] For example, Figure 4 The cost value example diagram of each node of the query relationship tree in the federated query scenario provided in this embodiment involves 3 data tables, namely tableA, tableB and tableC, and the RowCount (number of rows) of the data tables are 100, 1000 and 1500 respectively. First, this embodiment can be optimized based on the RowCount, CPU performance, and disk IO (input and output) performance of the three data tables as the cost value, that is, it is strongly related to the RowCount of the data table. The CPU performance and disk IO performance are reflected in the final cost value as weights, and finally a federated query path with the smallest overall cost value is selected, for example, Figure 4 The query relationship tree on the right is selected as the federated query path. The final total cost of each parent node in the query relationship tree is calculated according to the following formula:
[0131] C total =C self +C children ;
[0132] Among them, C total Indicates the final total cost of the parent node, C self Indicates the parent node’s own generation value, C children It represents the sum of the cost values of all child nodes under the parent node. The cost value of a node is calculated as follows:
[0133] C self =RowCount×(1+W cpu +W io );
[0134] Among them C self Represents the node's own cost, RowCount represents the total number of rows of data that the node is expected to output, and W cpu Indicates the CPU weight of the node corresponding to the data source, W io Indicates the disk input and output weight of the data source corresponding to the node.
[0135] This embodiment can calculate the cost value of each node, and add the cost value of the node and the cost values of all its child nodes as the final total cost value of the node. Figure 4As shown in the figure, in the JOIN operation scenario of three data tables, the cost of tableA joining tableB is calculated first, and then the cost of the JOIN operation of the Join result (that is, the joined table) and tableC is calculated. Then, according to the commutative and associative laws of relational algebra, Join ReOrder is performed and the cost of the JOIN operation of the root node is calculated, until the query relation tree with the smallest root node cost is selected.
[0136] The above implementation can be performed on a single-machine data source because it does not involve data transmission across multiple data sources across the network. If it is a distributed data source, this embodiment can also be combined with network cost, then C self The calculation formula can be transformed into:
[0137] C self =RowCount×(1+W cpu +W io +W net );
[0138] Among them, W net Represents the network cost. The above calculation formula is feasible in distributed data sources. The computing nodes of distributed data sources all run the same process, and the specifications of each node are generally required to be consistent during deployment. However, it is not applicable in the federated query scenario, because each data source is different, and the computing power and specifications of the data source are also different. Therefore, it is necessary to consider the cost of the data source additionally. Therefore, this embodiment uses the following calculation formula to calculate the cost of each node:
[0139] C self =RowCount×(1+W cpu +W io +W net +W ds ×W dscpus );
[0140] Among them, W ds represents the penalty weight of the data source, W dscpus Indicates the specification weight of the data source.
[0141] Since the computing capabilities of different data sources are different, for example, MySQL and Doris have different query times for the same level of query under the same specifications, this embodiment can set different penalty weights W for different data sources. ds , to reflect that different data source types have different data processing and analysis capabilities.
[0142] The same type of data source but different specifications and processing capabilities are also different. For example, a 90-core Doris and a 10-core Doris have different processing capabilities. Considering that CPU and memory generally have a certain ratio, in order to simplify the problem processing, this embodiment can only take the number of CPU cores as the basis for measuring the cost. Since the more cores, the lower the cost, so take As the specification weight W dscpus .
[0143] Still Figure 4 Take the federated query scenario of the three data tables shown in the figure as an example. In the ad hoc query scenario, most of them are cross-data source scenarios. At this time, the data sources of tableA, tableB, and tableC are Hive, Doris, and Hive respectively. When the penalty weight W of the data source is increased, ds and data source specification weight W dscpus In this case, the penalty weight is related to the type of data source. For example, the penalty weight of Hive is 50, and the penalty weight of Doris is 10. Assuming that the number of available cores of Hive is 4 and the number of available cores of Doris is also 4, then W dscpus is 1 / 4, so Figure 4 The cost value of each node shown becomes Figure 5 The cost value shown in the figure is that the root node cost value of the query relationship tree on the right is higher than the root node cost value of the query relationship tree on the left. The optimal root node cost value is as follows: Figure 5 As shown on the left, because the performance of Doris with the same configuration is much better than Hive, it can truly express the actual cost value in the actual execution process, making the generated cost value more accurate.
[0144] Based on the calculation formula for calculating the cost value of each node in this embodiment, the table mapping method can also be used to further optimize the data tables of different data sources. Table mapping means that the original data table and the mapping table are across data sources, and the data in the mapping table and the original data table are consistent. This mapping relationship is called table mapping. For example, there is a tableA in Hive and a tableA' in Doris. TableA' is called the mapping table of tableA. Figure 6 As shown, if the cost value of tableA' is less than the cost value of tableA, tableA is used to query in SQL. In this embodiment, tableA can be replaced with tableA' before actually performing the federated query, and tableA' can be used to query, thereby further reducing the cost value of the federated query. Figure 4 , Figure 5 , Figure 6 The rows in it indicates the number of rows in the data table or the connection table, and Scan indicates the query operation.
[0145] Table mapping is different from materialized view. Materialized view is materialized data based on a certain logic. Creating materialized view involves not only reading and writing data but also data calculation. Moreover, materialized view can only be adapted to certain specific scenarios and has limitations. Although table mapping involves reading and writing data, it does not involve calculation. It is particularly suitable for ad hoc query scenarios and does not occupy the computing resources of the data source. In addition, mapping table can be created according to control strategy parameters. The control strategy parameters include the resource occupancy status of the mapped data source and the specifications, data volume and data transmission cost of the mapped data. Based on the control strategy parameters, it can be determined whether the mapping table needs to be created when SQL performs the first federated query, so that the idle resources of each data source can be used more effectively to accelerate the query, and the data source with stronger computing power can be used as much as possible to accelerate data processing and analysis.
[0146] Reference Figure 7 The embodiment of the present application also provides a federated query device, which can implement the above-mentioned federated query method, and the device includes:
[0147] A data table determination unit, used to determine data tables of multiple different data sources;
[0148] A relationship tree construction unit, used to construct different query relationship trees according to each of the data tables; wherein the connection tables obtained after the federated query between each of the data tables are used as nodes of the query relationship tree;
[0149] A cost value calculation unit, used to calculate the cost value of the root node of each query relationship tree according to the penalty weight and specification weight of each data source;
[0150] A federated query unit is used to perform a federated query on each of the data tables according to a federated query path; wherein the query relationship tree corresponding to the minimum root node cost value serves as the federated query path.
[0151] Reference Figure 8 In some embodiments, the cost value calculation unit includes:
[0152] A first intrinsic cost calculation unit, configured to calculate intrinsic cost values of all the nodes in each of the query relationship trees according to the penalty weight and the specification weight of each of the data sources;
[0153] The own cost value adding unit is used to add the own cost value of each child node under each parent node in each query relationship tree to the own cost value of the corresponding parent node to obtain a total cost value; wherein the total cost value of the root node is used as the root node cost value.
[0154] In some embodiments, the first self-cost calculation unit includes:
[0155] The self-cost calculation subunit is used to calculate the self-cost value of each node in the query relationship tree according to the cost calculation formula;
[0156] The cost calculation formula is:
[0157] C self =RowCount×(1+W cpu +W io +W net +W ds ×W dscpus );
[0158] Among them, C self represents the cost value of the node itself, RowCount represents the total number of rows of data expected to be output by the node, and W cpu represents the CPU weight of the node corresponding to the data source, W io represents the disk input and output weight of the node corresponding to the data source, W net represents the network cost weight of the node corresponding to the data source, W ds represents the penalty weight of the node corresponding to the data source, W dscpus Indicates the specification weight of the node corresponding to the data source.
[0159] Reference Fig. 9 In some embodiments, the apparatus further comprises a mapping table processing unit, wherein the mapping table processing unit comprises:
[0160] A mapping table determination unit, used to determine a mapping table of each of the data tables; wherein the data source where the mapping table is located is used as a mapping data source;
[0161] A second own cost value calculation unit, configured to calculate the own cost value corresponding to the mapping table according to the penalty weight and the specification weight of the mapping data source;
[0162] A mapping table replacement unit is used to replace the corresponding data table in the query relationship tree with the mapping table if the intrinsic cost value of the mapping table is less than the intrinsic cost value of the corresponding data table.
[0163] In some embodiments, the apparatus further comprises:
[0164] A mapping table determination unit, used for determining whether it is necessary to create the mapping table according to the resource occupancy status of the mapping data source for creating the mapping table and the specification, data volume and data transmission cost of the mapping data before determining the mapping table of each data table;
[0165] A mapping table creating unit is used to create the mapping table at the mapping data source before determining the mapping table of each of the data tables, if necessary.
[0166] In some embodiments, the apparatus further comprises:
[0167] A query time determination unit, used to determine the query time consumed by each of the data sources when querying data of the same magnitude before calculating the root node cost value of each of the query relationship trees according to the penalty weight and specification weight of each of the data sources;
[0168] A penalty weight determination unit is used to determine the penalty weight of each data source according to each query time before calculating the root node cost value of each query relationship tree according to the penalty weight and specification weight of each data source.
[0169] In some embodiments, the apparatus further comprises:
[0170] The specification weight determination unit is used to determine the reciprocal of the number of CPU cores of each data source as the specification weight before calculating the root node cost value of each query relationship tree according to the penalty weight and specification weight of each data source.
[0171] It can be understood that the contents of the above method embodiments are all applicable to the present device embodiments, the functions specifically implemented by the present device embodiments are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.
[0172] The embodiment of the present application also provides an electronic device, the electronic device includes a memory and a processor, the memory stores a computer program, and the processor implements the above-mentioned federated query method when executing the computer program. The electronic device can be any intelligent terminal including a tablet computer, a car computer, etc.
[0173] It can be understood that the contents of the above method embodiments are all applicable to the present device embodiments, the functions specifically implemented by the present device embodiments are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.
[0174] See also Fig.10 , Fig.10 The hardware structure of an electronic device of another embodiment is illustrated, and the electronic device includes:
[0175] The processor 1001 may be implemented by a general-purpose CPU (Central Processing Unit), a microprocessor, an application-specific integrated circuit (Application Specific Integrated Circuit, ASIC), or one or more integrated circuits, and is used to execute relevant programs to implement the technical solutions provided in the embodiments of the present application;
[0176] The memory 1002 can be implemented in the form of a read-only memory (ROM), a static storage device, a dynamic storage device, or a random access memory (RAM). The memory 1002 can store an operating system and other application programs. When the technical solution provided in the embodiment of this specification is implemented by software or firmware, the relevant program code is stored in the memory 1002, and the processor 1001 calls and executes the federated query method of the embodiment of this application;
[0177] Input / output interface 1003, used to implement information input and output;
[0178] The communication interface 1004 is used to realize the communication interaction between the device and other devices. The communication can be realized through a wired manner (such as USB, network cable, etc.) or a wireless manner (such as mobile network, WIFI, Bluetooth, etc.);
[0179] A bus 1005 , which transmits information between various components of the device (e.g., the processor 1001 , the memory 1002 , the input / output interface 1003 , and the communication interface 1004 );
[0180] The processor 1001 , the memory 1002 , the input / output interface 1003 and the communication interface 1004 are connected to each other in communication within the device via the bus 1005 .
[0181] An embodiment of the present application also provides a computer-readable storage medium, which stores a computer program. When the computer program is executed by a processor, the above-mentioned federal query method is implemented.
[0182] It can be understood that the contents of the above method embodiments are all applicable to the present storage medium embodiments, the functions specifically implemented by the present storage medium embodiments are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.
[0183] The memory, as a non-transient computer-readable storage medium, can be used to store non-transient software programs and non-transient computer executable programs. In addition, the memory may include a high-speed random access memory, and may also include a non-transient memory, such as at least one disk storage device, a flash memory device, or other non-transient solid-state storage device. In some embodiments, the memory may optionally include a memory remotely disposed relative to the processor, and these remote memories may be connected to the processor via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.
[0184] The embodiments described in the embodiments of the present application are intended to more clearly illustrate the technical solutions of the embodiments of the present application and do not constitute a limitation on the technical solutions provided in the embodiments of the present application. Those skilled in the art will appreciate that with the evolution of technology and the emergence of new application scenarios, the technical solutions provided in the embodiments of the present application are also applicable to similar technical problems.
[0185] Those skilled in the art will appreciate that the technical solutions shown in the figures do not constitute a limitation on the embodiments of the present application, and may include more or fewer steps than shown in the figures, or a combination of certain steps, or different steps.
[0186] The device embodiments described above are merely illustrative, and the units described as separate components may or may not be physically separated, that is, they may be located in one place or distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of this embodiment.
[0187] Those skilled in the art will appreciate that all or some of the steps in the methods disclosed above, and the functional modules / units in the systems and devices may be implemented as software, firmware, hardware, or a suitable combination thereof.
[0188] The terms "first", "second", "third", "fourth", etc. (if any) in the specification of the present application and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present application described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device comprising a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.
[0189] It should be understood that in the present application, "at least one (item)" means one or more, and "plurality" means two or more. "And / or" is used to describe the association relationship of associated objects, indicating that three relationships may exist. For example, "A and / or B" can mean: only A exists, only B exists, and A and B exist at the same time, where A and B can be singular or plural. The character " / " generally indicates that the objects associated before and after are in an "or" relationship. "At least one of the following" or similar expressions refers to any combination of these items, including any combination of single or plural items. For example, at least one of a, b or c can mean: a, b, c, "a and b", "a and c", "b and c", or "a and b and c", where a, b, c can be single or multiple.
[0190] In the several embodiments provided in the present application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic. For example, the division of the above units is only a logical function division. There may be other division methods in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of devices or units, which can be electrical, mechanical or other forms.
[0191] The units described above as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.
[0192] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.
[0193] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including multiple instructions to enable a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods of various embodiments of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (Read-Only Memory, referred to as ROM), random access memory (Random Access Memory, referred to as RAM), disk or optical disk and other media that can store programs.
[0194] The preferred embodiments of the present invention are described above with reference to the accompanying drawings, but the scope of the rights of the present invention is not limited thereto. Any modification, equivalent substitution and improvement made by a person skilled in the art without departing from the scope and essence of the present invention should be within the scope of the rights of the present invention.
Claims
1. A federated query method, characterized in that: The method comprises the following steps: Identify data tables from multiple different data sources; Construct different query relationship trees according to each of the data tables; wherein the connection tables obtained after the federated query between each of the data tables are used as nodes of the query relationship tree; Calculating the root node cost value of each query relationship tree according to the penalty weight and specification weight of each data source; A federated query is performed on each of the data tables according to a federated query path; wherein the query relationship tree corresponding to the minimum root node cost value serves as the federated query path.
2. A federated query method according to claim 1, characterized in that: The step of calculating the root node cost value of each query relationship tree according to the penalty weight and specification weight of each data source comprises the following steps: Calculate the cost of all the nodes in each query relationship tree according to the penalty weight and the specification weight of each data source; The cost value of each child node under each parent node in each query relationship tree is added to the cost value of the corresponding parent node to obtain a total cost value; wherein the total cost value of the root node is used as the root node cost value.
3. A federated query method according to claim 2, characterized in that: The step of calculating the cost of all the nodes in each query relationship tree according to the penalty weight and the specification weight of each data source comprises the following steps: Calculate the cost value of each node in the query relationship tree according to a cost calculation formula; The cost calculation formula is: C self =RowCount×(1+W cpu +W io +W net +W ds ×W dscpus ); Among them, C self represents the cost value of the node itself, RowCount represents the total number of rows of data expected to be output by the node, and W cpu represents the CPU weight of the node corresponding to the data source, W io represents the disk input and output weight of the node corresponding to the data source, W net represents the network cost weight of the node corresponding to the data source, W ds represents the penalty weight of the node corresponding to the data source, W dscpus Indicates the specification weight of the node corresponding to the data source.
4. A federated query method according to claim 2, characterized in that: The method further comprises the following steps: Determine a mapping table for each of the data tables; wherein the data source where the mapping table is located is used as a mapping data source; Calculate the own cost value corresponding to the mapping table according to the penalty weight and the specification weight of the mapping data source; If the intrinsic cost value of the mapping table is less than the intrinsic cost value of the corresponding data table, the corresponding data table in the query relationship tree is replaced with the mapping table.
5. A federated query method according to claim 4, characterized in that: Before determining the mapping tables of the data tables, the method further comprises the following steps: Determining whether the mapping table needs to be created according to the resource occupancy status of the mapping data source for which the mapping table is to be created and the specification, data volume and data transmission cost of the mapping data; If necessary, the mapping table is created in the mapping data source.
6. A federated query method according to claim 1, characterized in that: Before calculating the root node cost value of each query relationship tree according to the penalty weight and specification weight of each data source, the method further includes the following steps: Determine the query time consumed by each of the data sources when querying data of the same magnitude; The penalty weight of each of the data sources is determined according to each of the query times.
7. A federated query method according to any one of claims 1 to 6, characterized in that: Before calculating the root node cost value of each query relationship tree according to the penalty weight and specification weight of each data source, the method further includes: The reciprocal of the number of CPU cores of each of the data sources is determined as the specification weight.
8. A federated query device, characterized in that: The device comprises: A data table determination unit, used to determine data tables of multiple different data sources; A relationship tree construction unit, used to construct different query relationship trees according to each of the data tables; wherein the connection tables obtained after the federated query between each of the data tables are used as nodes of the query relationship tree; A cost value calculation unit, used to calculate the cost value of the root node of each query relationship tree according to the penalty weight and specification weight of each data source; A federated query unit is used to perform a federated query on each of the data tables according to a federated query path; wherein the query relationship tree corresponding to the minimum root node cost value serves as the federated query path.
9. An electronic device, characterized in that: The electronic device includes a memory and a processor, the memory stores a computer program, and the processor implements a federated query method as described in any one of claims 1 to 7 when executing the computer program.
10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, a federated query method as described in any one of claims 1 to 7 is implemented.
