Data query method and device, computer equipment and storage medium

Abstract

The present invention discloses a data query method, device, computer equipment and storage medium. The method comprises analyzing a query request, obtaining a join algorithm and a corresponding scenario in the query request; if the join algorithm is one of full outer join, left outer join, right outer join, the scenario is that summary values in a first datasheet are sequenced while summary values in a second datasheet are not sequenced, performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides to obtain a first result set; and taking dimension values in the first result set as a dynamic filtering condition, and performing a dynamic filtering operation to which the dynamic filtering condition corresponds and the summarizing operation in the query request in a second database in which the second datasheet resides to obtain a second result set.

Description

DATA QUERY METHOD AND DEVICE, COMPUTER EQUIPMENT AND STORAGE MEDIUM BACKGROUND OF THE INVENTION Technical Field

[0001] The present invention relates to the field of computer technology, and more particularly to a data query method, a data query device, a computer equipment and a storage medium. Description of Related Art

[0002] In the age of big data, more and more attention has been paid to data analysis of massive data, business scenarios involved are also increasingly complicated, and data volumes are increasingly expanded, frequently to the magnitude of thousands or ten thousand of millions. It is usually very difficult to make use of a single olap database to solve all problems concerning data analysis, so most internet enterprises employ plural sets of olap databases to solve different problems of business analysis. However, it might be required to make united and summarized query of data stored in different databases simultaneously, and such federated query engines as spark sql, presto, impala and openLooKeng of Huawei are currently generally used.

[0003] When such federated query engines as spark sql, presto, and impala are used to perform data query and analysis, plural summary data coming from identical or different olap databases will be joined, and subsequently sequenced according to summary values of a certain sheet, and the top N pieces of data are returned. The traditional data query method merely presses the filtering condition down to the olap database, and extracts all data that satisfy the condition, then performs join calculation on a plurality of datasets in a federated query engine, and thereafter sequences and performs a limit operation on the results of the join calculation, but in such a data query method the data volume returned to the federated query engine is relatively large, and this not only causes relatively great pressure on network transmission, but also engenders relatively great resource consumption of the federated query engine, and lowers the query performance of the federated query engine. SUMMARY OF THE INVENTION

[0004] In view of the above, there is an urgent need to provide a data query method, a data query device, a computer equipment and a storage medium directed to the aforementioned technical problems.

[0005] According to the first aspect, there is provided a data query method, and the method comprises the following steps:

[0006] analyzing a query request, and obtaining a join algorithm and a corresponding scenario in the query request;

[0007] if the join algorithm is one of full outer join, left outer join, and right outer join, and the scenario is that summary values in a first datasheet are sequenced while summary values in a second datasheet are not sequenced, performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides to obtain a first result set; and

[0008] taking dimension values in the first result set as a dynamic filtering condition, and performing a dynamic filtering operation to which the dynamic filtering condition corresponds and the summarizing operation in the query request in a second database in which the second datasheet resides to obtain a second result set.

[0009] In one embodiment, the method further comprises: employing the join algorithm on the first result set and the second result set to obtain a first target result set, and extracting first target data from the first target result set to serve as a query result.

[0010] In one embodiment, if the join algorithm is full outer join, and data volume in the first datasheet is smaller than a preset limit value of the limit operation, the method further comprises performing in the second database the summarizing, sequencing and limit operation in the query request to obtain a third result set.

[0011] In one embodiment, the method further comprises:

[0012] uniting the second result set and the third result set, and removing repetitive results to obtain a fourth result set; and

[0013] employing the join algorithm on the first result set and the fourth result set to obtain a second target result set, and extracting second target data from the second target result set to serve as a query result.

[0014] In one embodiment, prior to performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides, the method further comprises:

[0015] judging whether data volumes in the first datasheet and the second datasheet satisfy a preset data volume condition, if yes, performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides.

[0016] In one embodiment, the preset data volume condition is that a ratio between the data volume in the first datasheet and the data volume in the second datasheet exceeds a preset ratio, or that the data volume in the first datasheet and the data volume in the second datasheet both exceed a preset quantity.

[0017] In one embodiment, when the join algorithm is left outer join, the first datasheet is a left sheet, when the join algorithm is right outer join, the first datasheet is a right sheet.

[0018] According to the second aspect, a data query device is provided, and the device comprises:

[0019] an analyzing module, for analyzing a query request, and obtaining a join algorithm and a corresponding scenario in the query request;

[0020] a first result set obtaining module, for performing, if the join algorithm is one of full outer join, left outer join, and right outer join, and the scenario is that summary values in a first datasheet are sequenced while summary values in a second datasheet are not sequenced, summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides to obtain a first result set; and

[0021] a second result set obtaining module, for taking dimension values in the first result set as a dynamic filtering condition, and performing a dynamic filtering operation to which the dynamic filtering condition corresponds and the summarizing operation in the query request in a second database in which the second datasheet resides to obtain a second result set.

[0022] According to the third aspect, a computer equipment is provided, which comprises a processor and a memory in which is stored a computer program, and the steps of the method are realized when the processor executes the computer program.

[0023] According to the fourth aspect, further provided is a computer-readable storage medium storing a computer program thereon, and the steps of the method are realized when the computer program is executed by a processor.

[0024] The present invention achieves the following advantageous effects.

[0025] According to the present invention, if the join algorithm is one of full outer join, left outer join, and right outer join, and the scenario is that summary values in a first datasheet are sequenced while summary values in a second datasheet are not sequenced, then summarizing, sequencing and limit operation in the query request are performed in a first database in which the first datasheet resides to obtain a first result set; dimension values in the first result set are taken as a dynamic filtering condition, and a dynamic filtering operation to which the dynamic filtering condition corresponds and the summarizing operation in the query request are performed in a second database to obtain a second result set, whereby data volumes in the first result set and the second result set as returned to the query engine are apparently reduced, and pressure of data transmission over the network is lowered; moreover, by taking the dimension values in the first result set as a dynamic filtering condition in the second database, data volume summarized in the second database is apparently reduced, the time taken to return the query result is shortened, and query performance of the query engine is enhanced. When the operating mode employed by the present invention is full outer join, considering that when the data volume in the first datasheet is smaller than a preset limit value of the limit operation, the summarizing, sequencing and limit operation in the query request are further performed in the second database to obtain a third result set, so that the data returned to the second database is dynamically readjusted, to avoid the circumstance of erroneous result caused by the fact that the second datasheet may contain summarized dimension information that is not contained in the first datasheet. In the present invention, prior to performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides to obtain a first result set, it is further required to take into consideration the cost for performing summarizing, sequencing and limit operation in the first database, the summarizing, sequencing and limit operation are performed in the first database only after a preset data volume condition is satisfied, so as to avoid the circumstance in which relatively great cost might be engendered by directly pushing the summarizing, limiting and filtering operations down to the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0026] To more clearly explain the technical solutions in the embodiments of the present invention, drawings required for use in the following explanation of the embodiments are briefly described below. Apparently, the drawings described below are merely directed to some embodiments of the present invention, while it is further possible for persons ordinarily skilled in the art to base on these drawings to acquire other drawings, and no creative effort will be spent in the process.

[0027] Fig. 1 illustrates a data query method in prior-art technology;

[0028] Fig. 2 illustrates a data query method in an embodiment;

[0029] Fig. 3 illustrates a data query method under an application scenario in an embodiment;

[0030] Fig. 4 illustrates a dynamic readjustment process of a data query method under an application scenario in an embodiment; and

[0031] Fig. 5 illustrates a data query device in an embodiment. DETAILED DESCRIPTION OF THE INVENTION

[0032] To make more lucid and clear the objectives, technical solutions and advantages of the present invention, the present invention will be described in greater detail below with reference to accompanying drawings and embodiments. As should be understood, specific embodiments described here are merely meant to explain the present invention, rather than to restrict the present invention.

[0033] As noted in the Description of Related Art of the present invention, the traditional data query method presses the filtering condition down to the olap database, and extracts all data that satisfy the condition, then performs join calculation on a plurality of datasets in a federated query SQL engine, and thereafter sequences and performs a limit operation on the data results, for instance, it might be required in the field of e-commerce to analyze sales circumstances and flow rates by analyzing flow rate UV (number of people with different IP addresses accessing a certain website or clicking a certain piece of news) and PV (page views, or click rate) of commodities ranking top 100 in the sales amount ranking within a period of time, for this end, sales data is stored in druid, flow rate data is deposited in clickhouse, which druid and clickhouse are different olap databases; if the query request is that summary values in the sales datasheet are sequenced, while summary values in the flow rate datasheet are not sequenced, and calculation is subsequently performed by using the full outer join mode, then, as shown in Fig. 1, a common data query method firstly summarizes the sales datasheet in druid, and summarizes the flow rate datasheet in clickhouse, and thereafter returns the total summary data back to the SQL engine, by which time the data volumes returned by druid and clickhouse are colossal indeed, as the data volume returned from druid is 10,000,000, and the data volume returned from clickhouse is 50,000,000, in this way, the data volumes returned by druid and clickhouse are respectively exchanged, sequenced by sorting, and thereafter calculated by join calculation in SQL, the result sets as obtained are sequenced according to sales amounts, limit operation is performed on the sequencing results, and projecting operation is then performed to get the record of top 100 in terms of sales amounts; during this process, since the data volumes returned by druid and clickhouse might be extremely huge, great pressure is brought thereby to network transmission, so the time taken to return the results is relatively long; moreover, sort merge join algorithms are required during subsequent full outer join calculation of the two sheets to complete the full outer join calculation of the big sheets, and such sort merge should be performed with great quantities of shuffler and sort operations, the cost is extremely high, very much resources are consumed, and execution efficiency is very low.

[0034] Accordingly, in the present invention is provided a data query method, if the join algorithm is one of full outer join, left outer join, and right outer join, and the scenario is that summary values in a first datasheet are sequenced while summary values in a second datasheet are not sequenced, then summarizing, sequencing and limit operation in the query request are performed in a first database in which the first datasheet resides to obtain a first result set; dimension values in the first result set are taken as a dynamic filtering condition, and a dynamic filtering operation to which the dynamic filtering condition corresponds and the summarizing operation in the query request are performed in a second database to obtain a second result set, whereby data volumes in the first result set and the second result set as returned to the query engine are apparently reduced, and pressure of data transmission over the network is lowered; moreover, by taking the dimension values in the first result set as a dynamic filtering condition in the second database, data volume summarized in the second database is apparently reduced, the time taken to return the query result is shortened, and query performance of the query engine is enhanced. The data query method, data query device, computer equipment and storage medium will be described in greater detail below in conjunction with specific embodiments. Embodiment 1

[0035] As shown in Fig. 2, this embodiment provides a data query method that is applied in a query engine, and at least comprises the following steps.

[0036] S1 - analyzing a query request, and obtaining a join algorithm and a corresponding scenario in the query request.

[0037] In actual operation, a user inputs a corresponding join algorithm and a corresponding scenario in a federated query engine, specifically, there are many types of join algorithms between multiple tables / sheets, for example, join and union, of which join is further divided into full outer join, left outer join, right outer join, and inner join; the corresponding scenario includes many types, such as not to sequence summary values, to sequence the summary values of one sheet but not to sequence the summary values of another sheet, and to sequence dimensions, etc.

[0038] S2 - if the join algorithm is one of full outer join, left outer join, and right outer join, and the scenario is that summary values in a first datasheet are sequenced while summary values in a second datasheet are not sequenced, performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides to obtain a first result set.

[0039] In this embodiment, before query data, it is firstly required to judge the join algorithm and the corresponding scenario. The operating mode applicable to the present invention includes full outer join, left outer join, and right outer join; when full outer join matches two sheets, it is not required for the data between the two sheets to be matched, i.e., a collection of data of the two sheets is returned; left outer join centers on the left sheet, and it is required to return all data in the left sheet, if records in the right sheet can match with those in the left sheet, values in the result set are set as the values in the right sheet, otherwise the values are set as null; right outer join centers on the right sheet, and it is required to return all data in the right sheet, if records in the left sheet can match with those in the right sheet, values in the result set are set as the values in the left sheet, otherwise the values are set as null. Thereafter the corresponding scenario is judged, and the scenario applicable to the present invention is that summary values in a first datasheet are sequenced while summary values in a second datasheet are not sequenced; as should be emphasized, the sheet in which summary values are sequenced is taken as the first datasheet and the sheet in which summary values are not sequenced is taken as the second datasheet in the present invention to facilitate comprehension, while the modifiers "first" and "second" are not meant to have any restrictive function on the datasheets.

[0040] In this embodiment, if the join algorithm is one of full outer join, left outer join, and right outer join, and the scenario is that summary values in a first datasheet are sequenced while summary values in a second datasheet are not sequenced, summarizing, sequencing and limit operation in the query request are performed in a first database in which the first datasheet resides to obtain a first result set. In other words, when the join algorithm and the scenario both satisfy the condition, the query engine pushes the summarizing, sequencing and limit operation in the query request down to the first database for execution, so as to make full use of features inherent in the database to return the first result set with relatively small data volume to the query engine, whereby the pressure on data transmission over the network is significantly lowered.

[0041] S3 - taking dimension values in the first result set as a dynamic filtering condition, and performing a dynamic filtering operation to which the dynamic filtering condition corresponds and the summarizing operation in the query request in a second database in which the second datasheet resides to obtain a second result set.

[0042] In this embodiment, after obtaining the first result set, the query engine takes dimension values in the first result set as a dynamic filtering condition to be transmitted to the second database in which the second datasheet resides, pushes the dynamic filtering operation and the summarizing operation down to the second database for execution, and returns the second result set to the query engine, whereby data volumes in the first result set and the second result set as returned to the query engine are apparently reduced, and pressure of data transmission over the network is lowered; moreover, by taking the dimension values in the first result set as a dynamic filtering condition in the second database, data volume summarized in the second database is apparently reduced, the time taken to return the query result is shortened, and query performance of the query engine is enhanced.

[0043] In a preferred embodiment, the join algorithm is employed on the first result set and the second result set to obtain a first target result set, and first target data is extracted from the first target result set to serve as a query result.

[0044] In this embodiment, the first result set and the second result set are both datasheets with small data volumes; the query engine passes the first result set and the second result set through the join algorithm to obtain a first target result set, and the join algorithm employed can be the foregoing full outer join, left outer join, and right outer join; when the full outer join algorithm is employed, broadcast hash join algorithm can be used; broadcast hash join is a highly effective join algorithm, as it avoids the resource- consuming shuffle and sort calculations, moderates the problem of time delay in the complicated data analysis, shortens the time taken to obtain the target result set, and further enhances the query performance of the federated query engine. In addition, when the full outer join algorithm is employed, it is also possible to use the aforementioned sort merge join algorithm; even if the data query method according to the present invention uses the complicated sort merge join algorithm, it still achieves higher query efficiency than data query methods in the state of the art.

[0045] In one embodiment, prior to employing the join algorithm on the first result set and the second result set to obtain a first target result set, the method further comprises exchanging data in the first result set to obtain a first exchanged set, and the first exchanged set is thereafter joined with the second result set; a preferred exchanging method is broadcast exchange, which is a highly effectively exchanging method capable of markedly enhancing the efficiency in obtaining an exchanging result.

[0046] In one embodiment, the step of employing the join algorithm on the first result set and the second result set to obtain a first target result set further includes sequencing and performing a limit operation on the results obtained by employing the join algorithm, so as to obtain the first target result set.

[0047] The aforementioned method according to the present invention will be described in greater detail below with reference to the concrete example in Fig. 3.

[0048] As previously mentioned, when it might be required in the field of e-commerce to analyze sales circumstances and flow rates, the circumstances of flow rate UV (number of people with different IP addresses accessing a certain website or clicking a certain piece of news) and PV (page views, or click rate) of commodities ranking top 100 in the sales amount ranking within a period of time are analyzed, for this end, sales data is stored in druid, flow rate data is deposited in clickhouse; if the query request is that summary values in the sales datasheet are sequenced, while summary values in the flow rate datasheet are not sequenced, and calculation is subsequently performed by using the full outer join mode, then, in the data query method according to the present invention, as shown in Fig. 3, a Spark SQL engine enables druid to execute the summarizing, sequencing and limit operation in the query request by transmitting a first query statement into druid, and the first query statement can be:

[0049] select goods_id,sum(sales) as total_sales from druid_sales where time>=2020-09-01 and time<=2020-09-30 group by goods id (this step is directed to summarizing the data in the first datasheet)

[0050] order by total sales desc limit 100 (this step is directed to sequencing and performing a limit operation on the summarized results)

[0051] druid returns top 100 records as the first result set to be returned to the Spark SQL engine.

[0052] Next, the Spark SQL engine transmits a second query statement into clickhouse, the dimension values in the first result set are taken as a dynamic filtering condition in the second query statement, a dynamic filtering operation to which the dynamic filtering condition corresponds and the summarizing operation are performed in clickhouse, and the second query statement can be:

[0053] select goods_id, count(visitor_id) as pv ,count(disitinct visitor_id) as uv from ch_traffic

[0054] where time>=2020-09-01 and time<=2020-09-30 and

[0055] goods id in (value1, value2, .....) (this step is directed to dynamically filtering and summarizing the data in the second datasheet)

[0056] group by goods id

[0057] wherein goods id in (value1, value2, .....) in the second query statement is the dynamic filtering condition, and the Spark SQL engines obtains the results returned from clickhouse to serve as the second result set.

[0058] After obtaining the first result set and the second result set, the Spark SQL engine employs broadcast exchange to exchange the data in the first result set to obtain a first exchanged set, then employs broadcast hash join to perform join calculation on the first exchanged set and the second result set, subsequently performs sort sequencing and limit operation to return results of rows, thereafter performs a projecting operation to select corresponding columns to obtain a first target result set, and extracts target data from the first target result set to serve as the query result; the time taken to obtain the target result set is effectively shortened through broadcast hash join, so that the time taken to obtain the query result is shortened.

[0059] The aforementioned query method according to the present invention can return the query result within several minutes, whereas use of a prior-art query method necessitates to return over 10,000,000 result sets from druid, and this often directly results in report of errors as timeout, while the query result cannot be obtained.

[0060] In a preferred embodiment, if the join algorithm is full outer join, and data volume in the first datasheet is smaller than a preset limit value of the limit operation, the method further comprises performing in the second database the summarizing, sequencing and limit operation in the query request to obtain a third result set.

[0061] The data query method introduced before should satisfy a certain condition, i.e., data volume in the first datasheet is greater than a preset limit value of the limit operation; whereas when full outer join is employed, if data volume in the first datasheet is smaller than a preset limit value of the limit operation, in other words, the data volume in the first datasheet is relatively small, then during full outer join calculation, if the dimension values in the first result set are still taken as a dynamic filtering condition to execute dynamic filtering operation and summarizing operation in the second database, the second datasheet may contain summarized dimension information that is not contained in the first datasheet, so as to cause the problem that returned data volume in the second result set is extremely small, and this circumstance is absolutely not allowed, so it is required to dynamically readjust the data query method.

[0062] Accordingly, a dynamic readjustment process of the data query method is involved in this embodiment, at this time, the query engine transmits the third query statement into the second database to perform the summarizing, sequencing and limit operation in the query request to obtain a third result set, in other words, the query engine pushes the summarizing, sequencing and limit operation down to the second database to obtain a corresponding result set.

[0063] In a preferred embodiment, the method further comprises: uniting the second result set and the third result set, and removing repetitive results to obtain a fourth result set; and employing the join algorithm on the first result set and the fourth result set to obtain a second target result set, and extracting second target data from the second target result set to serve as a query result.

[0064] In this embodiment, the query engine performs a uniting calculation on the second result set and the third result set, removes repetitive results to obtain a fourth result set, employs the join algorithm on the first result set and the fourth result set to obtain a second target result set, and extracts second target data from the second target result set to serve as a query result. The join algorithm employed can be any of the foregoing full outer join, left outer join, and right outer join, when the full outer join algorithm is employed, both the broadcast hash join algorithm and the sort merge join algorithm can be used, to which no repetition will be made in this context.

[0065] In one embodiment, prior to employing the join algorithm on the first result set and the fourth result set to obtain a second target result set, the method further comprises exchanging data in the first result set to obtain a second exchanged set, and the second exchanged set is thereafter joined with the fourth result set; a preferred exchanging method is broadcast exchange, which is a highly effectively exchanging method capable of markedly enhancing the efficiency in obtaining an exchanging result.

[0066] In one embodiment, the step of employing the join algorithm on the first result set and the fourth result set to obtain a second target result set includes sequencing and performing a limit operation on the results obtained by employing the join algorithm, so as to obtain the second target result set.

[0067] To make more apparent and clear the dynamic readjustment process of the query method according to the present invention, description is further made below with reference to the contents of Fig. 4.

[0068] As shown in Fig. 4, the query method is dynamically readjusted on the basis of the example shown in Fig. 3, if the join algorithm in the query request is full outer join, and data volume in the first datasheet is smaller than a preset limit value of the limit operation, the Spark SQL engine transmits the third query statement into clickhouse to perform the summarizing, sequencing and limit operation, and the third query statement can be:

[0069] select goods id, count(visitor id) as pv, count(disitinct visitor id) as uv

[0070] from ch_traffic where __time>=2020-09-01 and __time<=2020-09-30

[0071] group by goods id (this step is directed to summarizing the second datasheet)

[0072] / / order by goods id desc (this step is directed to sequencing the summarized results)

[0073] limit 100 (this step is directed to performing a limit operation on the sequenced results)

[0074] Thereafter, clickhouse returns the results that satisfy the condition to the Spark SQL engine, and the Spark SQL engine obtains the third result set.

[0075] After obtaining the third result set, the Spark SQL engine performs a uniting operation on the second result set and the third result set to calculate a collection thereof, performs a distinct operation to remove repetitive results, removes data repeating in analytic dimensions from the collection to obtain the fourth result set – for example, the analytic dimensions of the second result set and the third result set both have the product of refrigerator, then repetitive data should be deleted – thereafter exchanges the data in the first result set by employing the broadcast exchange algorithm to obtain the second exchanged set, subsequently performs join calculation on the second exchanged set and the fourth result set by employing broadcast hash join, next performs sort sequencing and limit operation to return the result of rows, thereafter performs a projecting operation to select the corresponding columns to obtain the second target result set, and extracts target data from the second target result set to serve as the query result.

[0076] In a preferred embodiment, in order to further enhance data query efficiency, prior to performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides, the method further comprises:

[0077] judging whether data volumes in the first datasheet and the second datasheet satisfy a preset data volume condition, if yes, performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides.

[0078] In the present invention, the data query method performs summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides to obtain a first result set, takes dimension values in the first result set as a dynamic filtering condition, and performs a dynamic filtering operation to which the dynamic filtering condition corresponds and the summarizing operation in the query request in a second database in which the second datasheet resides to obtain a second result set, in other words, the summarizing calculations of the first datasheet and the second datasheet are sequentially performed, whereas the common data query method concurrently performs the summarizing calculations of the first datasheet and the second datasheet, accordingly, if data volumes of the first datasheet and the second datasheet are both relatively small, or the data volumes as returned are both relatively small, it might be possible to firstly perform the summarizing calculation on the first datasheet and subsequently perform the summarizing calculation on the second datasheet, whereby query time is prolonged. Therefore, the data query cost should be judged before performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides to obtain a first result set, only when the data volumes in the first datasheet and the second datasheet satisfy the preset data volume condition, can the summarizing, sequencing and limit operation in the query request be performed in a first database in which the first datasheet resides.

[0079] Cost based optimizer technique is employed here, whereby costs of all possible physical plans are calculated, and a physical execution plan with the least cost is selected. The core rests in collecting statistic information in advance, and the cost is calculated according to features inherent in the data (such as size, distribution) and features of the operator (distribution and size of the intermediate result set), so as to better select the physical execution plan with the least execution cost. However, if cost estimation is made through statistic information, and the size of values returned after the summarizing calculation is calculated through statistic information, partitioned summarized fields and the filtering condition, cost calculating models of various olap databases are somehow different, to which no discussion will be made in the present invention.

[0080] The present invention employs the cost based optimizer technique to make cost estimation before performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides, and collects the following indices during the actual data query process: estimation index value while performing cost calculation (for instance, calculated data volume, returned data volume), actual execution result (actual query duration, actually returned value of each summarizing calculation), and effect estimation is made on these collected index values. If the estimated value differs relatively greatly from the actual value, further optimization is needed to estimate statistic value collection in cost calculation and the cause of the deviation in cost calculation, to improve the algorithm, to reduce estimation deviation, and to enhance calculation precision.

[0081] In a preferred embodiment, the preset data volume condition is that a ratio between the data volume in the first datasheet and the data volume in the second datasheet exceeds a preset ratio, or that the data volume in the first datasheet and the data volume in the second datasheet both exceed a preset quantity.

[0082] It is relatively complicated to perform total cost estimation during the actual data query process, and derivation points introduced midway into the process are also relatively many, and it is probable to import some additional delays; in this embodiment, for the sake of brevity, the cost estimation is firstly simplified as a preset data volume condition, and the contents of the following two aspects are specifically included.

[0083] According to the first aspect, a ratio between the data volume in the first datasheet and the data volume in the second datasheet exceeds a preset ratio, in which case the data volume in the first datasheet is relatively small while the data volume in the second datasheet is relatively large, and a relatively long time would be taken if total calculation is to be performed; the preset ratio can be set according to actual circumstances, and its value is usually set as 5, that is to say, the data volume in the second datasheet is 5 times that in the first datasheet.

[0084] According to the second aspect, the data volume in the first datasheet and the data volume in the second datasheet both exceed a preset quantity, in which case the preset quantity can be set according to actual circumstances, for instance, the estimated summary data volume is over a million.

[0085] It suffices to satisfy any of the above two conditions.

[0086] In a preferred embodiment, in order to further enhance the data query efficiency, prior to performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides, it is further required to judge the magnitude of the preset limit value of the limit operation, if the preset limit value is smaller than a limit threshold, which can be 500 for example, in other words, the preset limit value is relatively small, then the summarizing, sequencing and limit operation in the query request are directly performed in the first database in which the first datasheet resides, that is to say, the query engine pushes the summarizing, sequencing and limit operation down to the first database, thus making it possible to return the corresponding query result in a shorter period of time.

[0087] In a preferred embodiment, when the join algorithm is left outer join, the first datasheet is a left sheet, when the join algorithm is right outer join, the first datasheet is a right sheet.

[0088] The query engine used in the above concrete example is a Spark SQL engine, the most important two functions in Spark 3.0 are dynamic partition pruning and adaptive query engine functions; these two functions can greatly enhance the query performance under specific scenarios, whereas the present invention further enhances the query performance with respect to special circumstances, and this is slightly different from the two functions of Spark. Firstly, what dynamic partition pruning of Spark 3.0 is directed is a hive partition table, and partition fields must rest in the on condition of join; filtered dimension values of the partition fields are obtained by query a dimension table, and dynamic partition pruning is performed on the hive partition table, to reduce data scanning amount and to enhance the query performance; whereas in the present invention dynamic filtering operation is the more precise expression, whereby dimension values in the first result set are taken as a filtering condition to be forwarded to the second database in which the second datasheet resides, and plural dimensions can be supported as join condition. Secondly, the adaptive query engine function of Spark 3.0 is employed to solve the problem concerning dynamic readjustment / data skew / dynamic optimization of the executed plan of the Reduce number, whereas the present invention makes further improvement thereto by using it mainly to address the circumstance in which the data volume in the first datasheet is smaller than the preset limit value of the limit operation, and develops expanded functions to the self-adaptive execution framework of Spark 3.0.

[0089] Of course, the present invention merely takes the Spark SQL engine by way of example, as the optimizing method of the present invention is applicable to such similar federated query engines as presto, impala, and drill, and the databases employed can also be variegated, including, but not being limited to, clickhouse, doris, druid, kylin, elasticsearch, mysql, postgres, kudu, hbase, etc.

[0090] According to the present invention, if the join algorithm is one of full outer join, left outer join, and right outer join, and the scenario is that summary values in a first datasheet are sequenced while summary values in a second datasheet are not sequenced, then summarizing, sequencing and limit operation in the query request are performed in a first database in which the first datasheet resides to obtain a first result set; dimension values in the first result set are taken as a dynamic filtering condition, and a dynamic filtering operation to which the dynamic filtering condition corresponds and the summarizing operation in the query request are performed in a second database to obtain a second result set, whereby data volumes in the first result set and the second result set as returned to the query engine are apparently reduced, and pressure of data transmission over the network is lowered; moreover, by taking the dimension values in the first result set as a dynamic filtering condition in the second database, data volume summarized in the second database is apparently reduced, the time taken to return the query result is shortened, and query performance of the query engine is enhanced. When the operating mode employed by the present invention is full outer join, considering that when the data volume in the first datasheet is smaller than a preset limit value of the limit operation, the summarizing, sequencing and limit operation in the query request are further performed in the second database to obtain a third result set, so that the data returned to the second database is dynamically readjusted, to avoid the circumstance of erroneous result caused by the fact that the second datasheet may contain summarized dimension information that is not contained in the first datasheet. In the present invention, prior to performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides to obtain a first result set, it is further required to take into consideration the cost for performing summarizing, sequencing and limit operation in the first database, the summarizing, sequencing and limit operation are performed in the first database only after a preset data volume condition is satisfied, so as to avoid the circumstance in which relatively great cost might be engendered by directly pushing the summarizing, limiting and filtering operations down to the database. Embodiment 2

[0091] As shown in Fig. 5, a data query device is provided, and the device comprises:

[0092] an analyzing module, for analyzing a query request, and obtaining a join algorithm and a corresponding scenario in the query request;

[0093] a first result set obtaining module, for performing, if the join algorithm is one of full outer join, left outer join, and right outer join, and the scenario is that summary values in a first datasheet are sequenced while summary values in a second datasheet are not sequenced, summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides to obtain a first result set; and

[0094] a second result set obtaining module, for taking dimension values in the first result set as a dynamic filtering condition, and performing a dynamic filtering operation to which the dynamic filtering condition corresponds and the summarizing operation in the query request in a second database in which the second datasheet resides to obtain a second result set.

[0095] In one embodiment, the data query device further comprises a first target result set obtaining module for employing the join algorithm on the first result set and the second result set to obtain a first target result set, and a query result obtaining module for extracting first target data from the first target result set to serve as a query result.

[0096] In one embodiment, if the join algorithm is full outer join, and data volume in the first datasheet is smaller than a preset limit value of the limit operation, the data query device further comprises a third result set obtaining module for performing in the second database the summarizing, sequencing and limit operation in the query request to obtain a third result set.

[0097] In one embodiment, the data query device further comprises a fourth result set obtaining module for uniting the second result set and the third result set, and removing repetitive results to obtain a fourth result set; the query result obtaining module is further employed for employing the join algorithm on the first result set and the fourth result set to obtain a second target result set, and extracting second target data from the second target result set to serve as a query result.

[0098] In one embodiment, the data query device further comprises a judging module for judging whether data volumes in the first datasheet and the second datasheet satisfy a preset data volume condition, if yes, performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides.

[0099] In one embodiment, the preset data volume condition is that a ratio between the data volume in the first datasheet and the data volume in the second datasheet exceeds a preset ratio, or that the data volume in the first datasheet and the data volume in the second datasheet both exceed a preset quantity.

[0100] In one embodiment, when the join algorithm is left outer join, the first datasheet is a left sheet, when the join algorithm is right outer join, the first datasheet is a right sheet.

[0101] The data query device provided by this embodiment pertains to the same application conception as the data query method provided by Embodiment 1, is capable of executing the data query method of Embodiment 1, and possesses functional modules and achieves advantageous effects corresponding to the data query method. Technical details not described in detail in this embodiment can be inferred from the relevant descriptions of the data query method provided by Embodiment 1 of the present application, while no repetition is made in this context. Embodiment 3

[0102] In this embodiment a computer equipment is provided, and the computer equipment can be a server. The computer equipment comprises a processor, a memory, and a network interface connected to each other via a system bus. The processor of the computer equipment is employed to provide computing and controlling capabilities. The memory of the computer equipment includes a nonvolatile storage medium and an internal memory. The nonvolatile storage medium stores therein an operating system, a computer program and a database. The internal memory provides environment for the running of the operating system and the computer program in the nonvolatile storage medium. The network interface of the computer equipment is employed to connect to an external terminal via network for communication. The computer program realizes a data query method when it is executed by a processor.

[0103] In this embodiment a computer equipment is provided, which comprises a processor and a memory in which is stored a computer program, and the data query method according to Embodiment 1 is realized when the processor executes the computer program. The process of executing the method and the technical effects achievable thereby can be inferred from the relevant descriptions in Embodiment 1, while no repetition will be made in this context. Embodiment 4

[0104] In this embodiment a computer-readable storage medium is provided, which stores thereon a computer program, and the data query method according to Embodiment 1 is realized when the computer program is executed by a processor. The process of executing the method and the technical effects achievable thereby can be inferred from the relevant descriptions in Embodiment 1, while no repetition will be made in this context.

[0105] As comprehensible to persons ordinarily skilled in the art, the entire or partial flows in the methods according to the aforementioned embodiments can be completed via a computer program instructing relevant hardware, the computer program can be stored in a nonvolatile computer-readable storage medium, and the computer program can include the flows as embodied in the aforementioned various methods when executed. Any reference to the memory, storage, database or other media used in the various embodiments provided by the present application can all include at least one of nonvolatile memory and volatile memory. The nonvolatile memory can include a read- only memory (ROM), a magnetic disk, a floppy disk, a flash memory, or an optical memory. The volatile memory can include a random-access memory (RAM) or an external cache memory. To serve as explanation rather than restriction, the RAM can be embodied in many forms, such as static random-access memory (SRAM) or dynamic random-access memory (DRAM), etc.

[0106] Technical features of the aforementioned embodiments are randomly combinable, while all possible combinations of the technical features in the aforementioned embodiments are not exhausted for the sake of brevity, but all these should be considered to fall within the scope recorded in the Description as long as such combinations of the technical features are not mutually contradictory.

[0107] The foregoing embodiments are merely directed to several modes of execution of the present application, and their descriptions are relatively specific and detailed, but they should not be hence misunderstood as restrictions to the inventive patent scope. As should be pointed out, persons with ordinary skill in the art may further make various modifications and improvements without departing from the conception of the present application, and all these should pertain to the protection scope of the present application. Accordingly, the patent protection scope of the present application shall be based on the attached Claims.

Claims

<pat:ClaimStatement>CLAIMS What is claimed is:< / pat:ClaimStatement> <pat:Claims com:id="claims"> <pat:Claim com:id="CLM-00001"> <pat:ClaimNumber>1< / pat:ClaimNumber> <pat:ClaimText>1. A data query method, the method comprising: analyzing a query request, and obtaining a join algorithm and a corresponding scenario in the query request; if the join algorithm is one of full outer join, left outer join, and right outer join, and the scenario is that summary values in a first datasheet are sequenced while summary values in a second datasheet are not sequenced, performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides to obtain a first result set; taking dimension values in the first result set as a dynamic filtering condition; and performing a dynamic filtering operation to which the dynamic filtering condition corresponds and the summarizing operation in the query request in a second database in which the second datasheet resides to obtain a second result set. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00002"> <pat:ClaimNumber>2< / pat:ClaimNumber> <pat:ClaimText>2. The method of claim 1, the method further comprising a receiving a user input of the join algorithm and a corresponding scenario in a federated query engine. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00003"> <pat:ClaimNumber>3< / pat:ClaimNumber> <pat:ClaimText>3. The method of claim 2, wherein the join algorithm comprises a union algorithm. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00004"> <pat:ClaimNumber>4< / pat:ClaimNumber> <pat:ClaimText>4. The method of claim 2 or 3, wherein the corresponding scenario includes not to sequence summary values, to sequence the summary values of one sheet but not to sequence the summary values of another sheet, and to sequence dimensions. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00005"> <pat:ClaimNumber>5< / pat:ClaimNumber> <pat:ClaimText>5. The method of any one of claims 1 to 4, wherein if the join algorithm is one of full outer join, left outer join, and right outer join, and the scenario is that summary values in a first datasheet are sequenced while summary values in a second datasheet are not sequenced, summarizing, sequencing and limit operation in the query request are performed in a first database in which the first datasheet resides to obtain a first result set. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00006"> <pat:ClaimNumber>6< / pat:ClaimNumber> <pat:ClaimText>6. The method of any one of claims 1 to 5, wherein the query engine takes dimension values in the first result set as a dynamic filtering condition to be transmitted to the second database in which the second datasheet resides, pushes the dynamic filtering operation and the summarizing operation down to the second database for execution, and returns the second result set to the query engine. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00007"> <pat:ClaimNumber>7< / pat:ClaimNumber> <pat:ClaimText>7. The method of any one of claims 1 to 6, the method further comprising: employing the join algorithm on the first result set and the second result set to obtain a first target result set, and extracting a first target data from the first target result set to serve as a query result. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00008"> <pat:ClaimNumber>8< / pat:ClaimNumber> <pat:ClaimText>8. The method of claim 7, wherein the first result set and the second result set are both datasheets with small data volumes. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00009"> <pat:ClaimNumber>9< / pat:ClaimNumber> <pat:ClaimText>9. The method of claim 7 or 8, wherein prior to employing the join algorithm on the first result set and the second result set to obtain a first target result set, the method further comprises exchanging data in the first result set to obtain a first exchanged set, and the first exchanged set is thereafter joined with the second result set. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00010"> <pat:ClaimNumber>10< / pat:ClaimNumber> <pat:ClaimText>10. The method of claim 9, wherein exchanging data comprises broadcast exchange. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00011"> <pat:ClaimNumber>11< / pat:ClaimNumber> <pat:ClaimText>11. The method of any one of claims 8 to 10, wherein employing the join algorithm on the first result set and the second result set to obtain a first target result set further includes sequencing and performing a limit operation on results obtained by employing the join algorithm, so as to obtain the first target result set. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00012"> <pat:ClaimNumber>12< / pat:ClaimNumber> <pat:ClaimText>12. The method of any one of claims 1 to 6, wherein if the join algorithm is the full outer join, and data volume in the first datasheet is smaller than a preset limit value of the limit operation, the method further comprises performing in the second database the summarizing, sequencing and limit operation in the query request to obtain a third result set. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00013"> <pat:ClaimNumber>13< / pat:ClaimNumber> <pat:ClaimText>13. The method of claim 12, the method further comprising: uniting the second result set and the third result set, and removing repetitive results to obtain a fourth result set; and employing the join algorithm on the first result set and the fourth result set to obtain a second target result set, and extracting a second target data from the second target result set to serve as a query result. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00014"> <pat:ClaimNumber>14< / pat:ClaimNumber> <pat:ClaimText>14. The method of claim 13, wherein prior to employing the join algorithm on the first result set and the fourth result set to obtain a second target result set, the method further comprises exchanging data in the first result set to obtain a second exchanged set, and the second exchanged set is thereafter joined with the fourth result. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00015"> <pat:ClaimNumber>15< / pat:ClaimNumber> <pat:ClaimText>15. The method of claim 14, wherein exchanging data comprises broadcast exchange. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00016"> <pat:ClaimNumber>16< / pat:ClaimNumber> <pat:ClaimText>16. The method of any one of claims 13 to 15, wherein employing the join algorithm on the first result set and the fourth result set to obtain a second target result set includes sequencing and performing a limit operation on the results obtained by employing the join algorithm, so as to obtain the second target result set. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00017"> <pat:ClaimNumber>17< / pat:ClaimNumber> <pat:ClaimText>17. The method of any one of claims 1 to 16, wherein prior to performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides, the method further comprises: judging whether data volumes in the first datasheet and the second datasheet satisfy a preset data volume condition, if data volumes in the first datasheet and the second datasheet satisfy the preset data volume condition, performing summarizing, sequencing and limit operation in the query request in the first database in which the first datasheet resides. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00018"> <pat:ClaimNumber>18< / pat:ClaimNumber> <pat:ClaimText>18. The method according to claim 17, characterized in that the preset data volume condition is that a ratio between the data volume in the first datasheet and the data volume in the second datasheet exceeds a preset ratio, or that the data volume in the first datasheet and the data volume in the second datasheet both exceed a preset quantity. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00019"> <pat:ClaimNumber>19< / pat:ClaimNumber> <pat:ClaimText>19. The method of any one of claims 1 to 18, wherein prior to performing summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides, the method further comprises judging magnitude of a preset limit value of the limit operation, to determine if the preset limit value is smaller than a limit threshold. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00020"> <pat:ClaimNumber>20< / pat:ClaimNumber> <pat:ClaimText>20. The method of any one of claims 1 to 6, wherein when the join algorithm is the left outer join, the first datasheet is a left sheet, when the join algorithm is the right outer join, the first datasheet is a right sheet. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00021"> <pat:ClaimNumber>21< / pat:ClaimNumber> <pat:ClaimText>21. A data query device, the device comprising: an analyzing module, for analyzing a query request, and obtaining a join algorithm and a corresponding scenario in the query request; a first result set obtaining module, for performing, if the join algorithm is one of full outer join, left outer join, and right outer join, and the scenario is that summary values in a first datasheet are sequenced while summary values in a second datasheet are not sequenced, summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides to obtain a first result set; and a second result set obtaining module, for taking dimension values in the first result set as a dynamic filtering condition, and performing a dynamic filtering operation to which the dynamic filtering condition corresponds and the summarizing operation in the query request in a second database in which the second datasheet resides to obtain a second result set. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00022"> <pat:ClaimNumber>22< / pat:ClaimNumber> <pat:ClaimText>22. The device of claim 21, the device further comprising a first target result set obtaining module for employing the join algorithm on the first result set and the second result set to obtain a first target result set, and a query result obtaining module for extracting first target data from the first target result set to serve as a query result. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00023"> <pat:ClaimNumber>23< / pat:ClaimNumber> <pat:ClaimText>23. The device of claim 21 or 22, wherein the device further comprises a third result set obtaining module for performing in the second database the summarizing, sequencing and limit operation in the query request to obtain a third result set. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00024"> <pat:ClaimNumber>24< / pat:ClaimNumber> <pat:ClaimText>24. The device of any one of claims 21 to 23, wherein the device further comprises a fourth result set obtaining module for uniting the second result set and a third result set, and removing repetitive results to obtain a fourth result set. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00025"> <pat:ClaimNumber>25< / pat:ClaimNumber> <pat:ClaimText>25. The device of claim 24, wherein the query result obtaining module is further employed for employing the join algorithm on the first result set and the fourth result set to obtain a second target result set, and extracting second target data from the second target result set to serve as a query result. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00026"> <pat:ClaimNumber>26< / pat:ClaimNumber> <pat:ClaimText>26. The device of any one of claims 21 to 25, the device further comprising a judging module for judging whether data volumes in the first datasheet and the second datasheet satisfy a preset data volume condition. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00027"> <pat:ClaimNumber>27< / pat:ClaimNumber> <pat:ClaimText>27. The device of claim 26, wherein if the data volumes in the first datasheet and the second datasheet satisfy a preset data volume condition, the device is further configured to perform summarizing, sequencing and limit operation in the query request in a first database in which the first datasheet resides. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00028"> <pat:ClaimNumber>28< / pat:ClaimNumber> <pat:ClaimText>28. The device of any one of claims 26 to 27, the preset data volume condition is that a ratio between the data volume in the first datasheet and the data volume in the second datasheet exceeds a preset ratio. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00029"> <pat:ClaimNumber>29< / pat:ClaimNumber> <pat:ClaimText>29. The device of any one of claims 26 to 27, the preset data volume condition is the data volume in the first datasheet and the data volume in the second datasheet both exceed a preset quantity. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00030"> <pat:ClaimNumber>30< / pat:ClaimNumber> <pat:ClaimText>30. The device of any one of claims 21 to 29, wherein when the join algorithm is left outer join, the first datasheet is a left sheet. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00031"> <pat:ClaimNumber>31< / pat:ClaimNumber> <pat:ClaimText>31. The device of any one of claims 21 to 30, wherein when the join algorithm is right outer join, the first datasheet is a right sheet. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00032"> <pat:ClaimNumber>32< / pat:ClaimNumber> <pat:ClaimText>32. A computer device, comprising a processor and a memory in which is stored a computer program, which when executed by the processor causes the device to perform the method according to any one of claims 1 to 20. < / pat:ClaimText> < / pat:Claim> <pat:Claim com:id="CLM-00033"> <pat:ClaimNumber>33< / pat:ClaimNumber> <pat:ClaimText>33. A non-transitory computer-readable storage medium, storing computer-executable instructions that, when executed by one or more processors, causes the one or more processors to perform the method according to any one of claims 1 to 20. < / pat:ClaimText> < / pat:Claim> < / pat:Claims>