A data query method, system, device, apparatus and storage medium

Abstract

The embodiment of the application provides a data query method, system, device, equipment and storage medium, and relates to the technical field of data processing. The specific implementation scheme is: receiving a first statistical query statement, determining a specified statistical range, a specified statistical method and a specified measurement indicated by the first statistical query statement; if there is no aggregate table containing to-be-queried data in each aggregate table stored in advance, determining whether there is a target aggregate table in each aggregate table, the statistical range of which belongs to the specified statistical range, the statistical measurement of which is the specified measurement, and the statistical method of which is the specified statistical method based on the hierarchical structure between each dimension table stored in advance; if there is, obtaining the measurement value of the specified measurement corresponding to each statistical range belonging to the specified statistical range from the target aggregate table based on the hierarchical structure, and obtaining a query result based on the obtained measurement value and the specified statistical method. It can be seen that, by the scheme, the query efficiency can be improved.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a data query method, system, apparatus, device, and storage medium. Background Technology

[0002] With the continuous development of information technology, data has become a crucial driver of industry growth. The massive increase in data volume leads to slow data query and analysis. To accelerate queries, query engines typically cache pre-built aggregation tables to reduce query time and resource consumption. An aggregation table is a table that performs pre-statistical analysis on the original data under statistical dimensions of interest to the user, resulting in measurement data for those specific dimensions.

[0003] When querying business data using a query engine, the engine caches the metric data for frequently queried statistical dimensions as aggregate tables. Different query engines cache their own aggregate tables. If a query query fails to find the cached aggregate table for a particular engine, the system needs to retrieve the raw data from the fact table in the data warehouse and perform statistical analysis, resulting in low query efficiency.

[0004] Therefore, improving query efficiency has become a pressing technical problem that needs to be solved. Summary of the Invention

[0005] The purpose of this application is to provide a data query method, system, apparatus, device, and storage medium to improve query efficiency. The specific technical solution is as follows:

[0006] In a first aspect, embodiments of this application provide a data query method, the method comprising:

[0007] In response to receiving a first statistical query statement for the data to be queried, determine the specified statistical range, specified statistical method and specified metric indicated by the first statistical query statement;

[0008] If no aggregate table containing the data to be queried exists in the pre-stored aggregate tables, then based on the hierarchical structure between the pre-stored dimension tables, it is determined whether there exists a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, whose statistical measure is the specified measure, and whose statistical method is the specified statistical method; wherein, the hierarchical structure between the dimension tables represents the relationship between the statistical ranges recorded in the dimension tables; an aggregate table records the measure values ​​obtained by performing statistics on at least one measure in the fact table in different statistical ranges according to a statistical method;

[0009] If the target aggregation table exists in each aggregation table, then based on the hierarchical structure, determine the statistical ranges recorded in the target aggregation table that belong to the specified statistical range, obtain the metric values ​​of the specified metric corresponding to each determined statistical range from the target aggregation table, and obtain the query results based on the obtained metric values ​​and the specified statistical method.

[0010] Optionally, the method is applied to an intermediate query engine, which communicates with each backend query engine; the method further includes:

[0011] In response to receiving the first statistical query statement, detect whether the first statistical query statement carries a preset keyword;

[0012] If no aggregate table containing the data to be queried exists in the pre-stored aggregate tables, then based on the hierarchical structure between the pre-stored dimension tables, it is determined whether there exists a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, whose statistical metric is the specified metric, and whose statistical method is the specified statistical method, including:

[0013] If the first statistical query statement carries the preset keyword, and if there is no aggregate table containing the data to be queried in the pre-stored aggregate tables, then based on the hierarchical structure between the pre-stored dimension tables, it is determined whether there is a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, whose statistical metric is the specified metric, and whose statistical method is the specified statistical method.

[0014] The method further includes: when the first statistical query statement does not carry the preset keyword, determining the target backend query engine that stores the fact table containing the specified metric from each backend query engine based on the recorded metadata table of each backend query engine; wherein, the metadata table of each backend query engine is used to describe the raw data managed by that backend query engine.

[0015] The first statistical query statement is sent to the target backend query engine so that the target backend query engine can query and statistically analyze the raw data it manages according to the specified statistical range, the specified statistical method and the specified metric, and obtain the query results.

[0016] Optionally, the method further includes: if the target aggregation table does not exist in any of the aggregation tables, determining, based on the metadata tables of each backend query engine, a target backend query engine that stores a fact table containing the specified metric from each backend query engine; wherein, the metadata table of each backend query engine is used to describe the raw data managed by that backend query engine.

[0017] The first statistical query statement is sent to the target backend query engine so that the target backend query engine can query and statistically analyze the raw data it manages according to the specified statistical range, the specified statistical method and the specified metric, and obtain the query results.

[0018] Optionally, each aggregation table is stored in the shared storage space of the intermediate query engine and each backend query engine;

[0019] Based on the hierarchical structure, the process involves determining the statistical ranges recorded in the target aggregation table that belong to the specified statistical range, obtaining the metric values ​​of the specified metric corresponding to each determined statistical range from the target aggregation table, and obtaining query results based on the obtained metric values ​​and the specified statistical method, including:

[0020] Based on the hierarchical structure, the first statistical query statement is rewritten to obtain a second statistical query statement; wherein, the second statistical query statement is used to indicate: determining each statistical range belonging to the specified statistical range recorded in the target aggregation table stored in the shared storage space, obtaining the metric value of the specified metric corresponding to each determined statistical range from the target aggregation table, and obtaining the query result based on the obtained metric value and the specified statistical method;

[0021] Execute the second statistical query statement; or, send the second statistical query statement to a specified backend query engine so that the specified backend query engine executes the second statistical query statement; wherein the specified backend query engine is selected from each backend query engine according to a predetermined selection rule.

[0022] Optionally, the construction methods for each aggregate table include:

[0023] Identify the statistically significant dimensions corresponding to the original data recorded in the pre-stored fact table, and use them as the root data space;

[0024] For multiple first sub-data spaces of the root data space, the processing cost of the first sub-data space is determined based on the data complexity of the projection of the original data into the first sub-data space; wherein the processing cost is positively correlated with the data complexity.

[0025] For a first sub-data space that is not a materialized sub-data space, the materialization benefit of the first sub-data space is determined based on the processing cost of the first sub-data space and the processing cost of each materialized sub-data space; wherein, the materialized sub-data space initially includes the root data space, and the materialization benefit is negatively correlated with the processing cost of the first sub-data space and positively correlated with the processing cost of each materialized sub-data space;

[0026] The first sub-data space that satisfies the preset materialized benefit conditions is determined as the new materialized sub-data space;

[0027] For each of the materialized sub-data spaces, the projection of the original data into the materialized sub-data space is aggregated to obtain an aggregated table corresponding to the materialized sub-data space.

[0028] Secondly, embodiments of this application provide a data query system, including an intermediate query engine and a backend query engine;

[0029] The intermediate query engine is used to execute any of the data query methods described above;

[0030] The backend query engine is used to receive the first statistical query statement sent by the intermediate query engine, and to perform queries and statistics on the raw data it manages according to the specified statistical range, specified statistical method and specified metric indicated by the first statistical query statement, so as to obtain the query results.

[0031] Optionally, the shared storage space of the intermediate query engine and the backend query engine stores various aggregation tables;

[0032] The backend query engine is also used to receive a second statistical query statement sent by the intermediate query engine, and execute the received second statistical query statement based on the data stored in the shared storage space.

[0033] Thirdly, embodiments of this application provide a data query device, the device comprising:

[0034] The first determining module is used to determine the specified statistical range, specified statistical method and specified metric indicated by the first statistical query statement in response to receiving a first statistical query statement for the data to be queried;

[0035] The second determining module is used to determine, based on the hierarchical structure between the pre-stored aggregation tables, whether there exists a target aggregation table in each aggregation table whose statistical range belongs to the specified statistical range, whose statistical metric is the specified metric, and whose statistical method is the specified statistical method, if no aggregation table containing the data to be queried exists in the pre-stored aggregation tables; wherein, the hierarchical structure between the dimension tables represents the relationship between the statistical ranges recorded in the dimension tables; an aggregation table records the metric values ​​obtained by statistically analyzing at least one metric in the fact table in different statistical ranges according to a statistical method;

[0036] The first query module is used to determine, based on the hierarchical structure, the statistical ranges recorded in the target aggregation table that belong to the specified statistical range, if the target aggregation table exists in each aggregation table, obtain the metric value of the specified metric corresponding to each determined statistical range from the target aggregation table, and obtain the query result based on the obtained metric value and the specified statistical method.

[0037] Optionally, the device is applied to an intermediate query engine, which is communicatively connected to each backend query engine;

[0038] The device further includes:

[0039] The detection module is used to detect whether the first statistical query statement carries a preset keyword in response to receiving the first statistical query statement;

[0040] The second determining module is specifically used for:

[0041] If the first statistical query statement carries the preset keyword, and if there is no aggregate table containing the data to be queried in the pre-stored aggregate tables, then based on the hierarchical structure between the pre-stored dimension tables, it is determined whether there is a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, whose statistical metric is the specified metric, and whose statistical method is the specified statistical method.

[0042] The device further includes:

[0043] The third determining module is used to determine, based on the metadata tables of each backend query engine recorded, a target backend query engine that stores a fact table containing the specified metric, when the first statistical query statement does not carry the preset keyword; wherein, the metadata table of each backend query engine is used to describe the raw data managed by that backend query engine.

[0044] The second query module is used to send the first statistical query statement to the target backend query engine, so that the target backend query engine can perform queries and statistics on the raw data it manages according to the specified statistical range, the specified statistical method and the specified metric, and obtain query results.

[0045] Optionally, the device further includes:

[0046] The fourth determining module is used to determine, based on the metadata tables of each backend query engine recorded, the target backend query engine that stores the fact table containing the specified metric if the target aggregation table does not exist in any of the aggregation tables; wherein, the metadata table of each backend query engine is used to describe the raw data managed by that backend query engine.

[0047] The third query module is used to send the first statistical query statement to the target backend query engine, so that the target backend query engine can perform queries and statistics on the raw data it manages according to the specified statistical range, the specified statistical method and the specified metric, and obtain query results.

[0048] Optionally, each aggregation table is stored in the shared storage space of the intermediate query engine and each backend query engine;

[0049] The second determining module includes:

[0050] The rewrite submodule is used to rewrite the first statistical query statement based on the hierarchical structure to obtain a second statistical query statement; wherein, the second statistical query statement is used to instruct: determine each statistical range belonging to the specified statistical range recorded in the target aggregation table stored in the shared storage space, obtain the metric value of the specified metric corresponding to each determined statistical range from the target aggregation table, and obtain the query result based on the obtained metric value and the specified statistical method;

[0051] An execution submodule is used to execute the second statistical query statement; or, to send the second statistical query statement to a specified backend query engine so that the specified backend query engine executes the second statistical query statement; wherein the specified backend query engine is selected from each backend query engine according to a predetermined selection rule.

[0052] Optionally, the construction methods for each aggregate table include:

[0053] Identify the statistically significant dimensions corresponding to the original data recorded in the pre-stored fact table, and use them as the root data space;

[0054] For multiple first sub-data spaces of the root data space, the processing cost of the first sub-data space is determined based on the data complexity of the projection of the original data into the first sub-data space; wherein the processing cost is positively correlated with the data complexity.

[0055] For a first sub-data space that is not a materialized sub-data space, the materialization benefit of the first sub-data space is determined based on the processing cost of the first sub-data space and the processing cost of each materialized sub-data space; wherein, the materialized sub-data space initially includes the root data space, and the materialization benefit is negatively correlated with the processing cost of the first sub-data space and positively correlated with the processing cost of each materialized sub-data space;

[0056] The first sub-data space that satisfies the preset materialized benefit conditions is determined as the new materialized sub-data space;

[0057] For each of the materialized sub-data spaces, the projection of the original data into the materialized sub-data space is aggregated to obtain an aggregated table corresponding to the materialized sub-data space.

[0058] Fourthly, embodiments of this application provide an electronic device, including:

[0059] Memory, used to store computer programs;

[0060] A processor, when executing a program stored in memory, implements any of the data query methods described above.

[0061] Fifthly, embodiments of this application provide a computer-readable storage medium storing a computer program, which, when executed by a processor, implements the data query method described in any of the preceding claims.

[0062] Sixthly, embodiments of this application provide a computer program product comprising executable instructions that, when executed on a computer, cause the computer to perform any of the data query methods described above.

[0063] Beneficial effects of the embodiments in this application:

[0064] The solution provided in this application receives a first statistical query statement for the data to be queried, determines the specified statistical range, specified statistical method, and specified metric indicated by the first statistical query statement, and if no aggregate table containing the data to be queried exists in the pre-stored aggregate tables, then based on the hierarchical structure between the pre-stored dimension tables, it determines whether there exists a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, whose statistical metric is the specified metric, and whose statistical method is the specified statistical method. If a target aggregate table exists, the query is performed using the data in the target aggregate table; that is, the metric values ​​corresponding to the specified metrics for each statistical range belonging to the specified statistical range are obtained from the target aggregate table, and then the obtained metric values ​​are statistically analyzed according to the specified statistical method to obtain the query results. Since the target aggregation table is an aggregation table whose statistical range, statistical metric, and statistical method are all specified, meaning that the data stored in the target aggregation table is data obtained by pre-statistically analyzing the original data within the specified statistical range, the query can be performed using the data in the target aggregation table when no data to be queried is directly recorded in any of the aggregation tables. In other words, the query and statistics are performed based on the target aggregation table obtained by performing certain statistics on the original data. Compared with querying and analyzing directly from the original data, this can reduce query costs and improve query efficiency.

[0065] Of course, implementing any product or method of this application does not necessarily require achieving all of the advantages described above at the same time. Attached Figure Description

[0066] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other embodiments can be obtained based on these drawings.

[0067] Figure 1 A flowchart illustrating a data query method provided in this application embodiment;

[0068] Figure 2 A schematic diagram of a system architecture for implementing the data query method provided in the embodiments of this application;

[0069] Figure 3 A flowchart illustrating a specific example of the data query method provided in this application embodiment;

[0070] Figure 4 A flowchart illustrating a method for constructing an aggregation table as provided in an embodiment of this application;

[0071] Figure 5A schematic diagram of a data cube tree provided in an embodiment of this application;

[0072] Figure 6 This is a schematic diagram of the structure of a data query system provided in an embodiment of this application;

[0073] Figure 7 This is a schematic diagram of the structure of a data query device provided in an embodiment of this application;

[0074] Figure 8 A block diagram of an electronic device for implementing the data query method provided in the embodiments of this application. Detailed Implementation

[0075] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art based on this application are within the scope of protection of this application.

[0076] The data query method provided in this application can be applied to query engines in various electronic devices. These electronic devices can be personal computers, servers, and other devices with data processing capabilities. The query engine can be a backend query engine such as Presto (an open-source distributed SQL query engine), Impala (a massively parallel SQL query engine), or Spark (a query engine for processing structured data), or an intermediate query engine like OneSQL (a query engine for processing structured and semi-structured data) that communicates with the backend query engines, etc. It is understood that the OneSQL query engine can parse and rewrite SQL (Structured Query Language) statements and send the rewritten SQL statements to the backend query engines so that the backend query engines can perform the actual query. Furthermore, it is understood that the data query method provided in this application can be implemented through software, hardware, or a combination of both.

[0077] One of the data query methods provided in this application embodiment may include the following steps:

[0078] In response to receiving a first statistical query statement for the data to be queried, determine the specified statistical range, specified statistical method and specified metric indicated by the first statistical query statement;

[0079] If there is no aggregate table containing the data to be queried in the pre-stored aggregate tables, then based on the hierarchical structure between the pre-stored dimension tables, it is determined whether there is a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, whose statistical measure is the specified measure, and whose statistical method is the specified statistical method; wherein, the hierarchical structure between the dimension tables represents the relationship between the statistical ranges recorded in the dimension tables; an aggregate table records at least one measure with measure values ​​in different statistical ranges;

[0080] If a target aggregation table exists in each aggregation table, then based on the hierarchical structure, determine the statistical ranges that belong to the specified statistical range recorded in the target aggregation table. Obtain the metric values ​​of the specified measures corresponding to each determined statistical range from the target aggregation table, and obtain the query results based on the obtained metric values ​​and the specified statistical method.

[0081] The solution provided in this application receives a first statistical query statement for the data to be queried, determines the specified statistical range, specified statistical method, and specified metric indicated by the first statistical query statement, and if no aggregate table containing the data to be queried exists in the pre-stored aggregate tables, then based on the hierarchical structure between the pre-stored dimension tables, it determines whether there exists a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, whose statistical metric is the specified metric, and whose statistical method is the specified statistical method. If a target aggregate table exists, the query is performed using the data in the target aggregate table; that is, the metric values ​​corresponding to the specified metrics for each statistical range belonging to the specified statistical range are obtained from the target aggregate table, and then the obtained metric values ​​are statistically analyzed according to the specified statistical method to obtain the query results. Since the target aggregation table is an aggregation table whose statistical range, statistical metric, and statistical method are all specified, meaning that the data stored in the target aggregation table is data obtained by pre-statistically analyzing the original data within the specified statistical range, the query can be performed using the data in the target aggregation table when no data to be queried is directly recorded in any of the aggregation tables. In other words, the query and statistics are performed based on the target aggregation table obtained by performing certain statistics on the original data. Compared with querying and analyzing directly from the original data, this can reduce query costs and improve query efficiency.

[0082] The data query method provided in the embodiments of this application will be described below with reference to the accompanying drawings, such as... Figure 1 As shown, steps S101-S103 are included:

[0083] S101, in response to receiving a first statistical query statement for the data to be queried, determine the specified statistical range, specified statistical method and specified metric indicated by the first statistical query statement;

[0084] In this embodiment, the data to be queried is the data that the first statistical query statement needs to query. The specified statistical range and specified statistical method indicated by the first statistical query statement are the query conditions for the data to be queried, and the specified metric indicated by the first statistical query statement is the metric to which the original data to which the data to be queried belongs. For example, if the data to be queried is the total sales of product A in 2020, then the original data to which the data to be queried is the sales of each order of product A in 2020, and the metric to which this original data belongs is sales, then the specified metric indicated by the first statistical query statement is sales.

[0085] For example, the specified statistical range can be a statistical range based on time, location, or a combination of time and location, etc. For instance, if the specified statistical range is based on time, it could be from 2018 to 2024; if the specified statistical range is based on location, it could be all areas within City X; and if the specified statistical range is based on a combination of time and location, it could be all areas within Province Y from 2020 to 2021.

[0086] For example, the specified statistical method can be a calculated sum, average, maximum or minimum value, etc. For example, in the scenario of querying product transaction information, the specified metric can be sales amount, sales volume, etc.; in the scenario of querying historical weather information, the specified metric can be temperature, humidity, etc.

[0087] For example, in practical applications, if the data to be queried is the total sales revenue of product A in 2020, then it is necessary to query the sales revenue of all orders for product A in 2020, and sum the results to obtain the total sales revenue of product A in 2020. Therefore, relevant technical personnel can write a first statistical query statement and input it into a client for data querying. The client then calls a query engine to parse the first statistical query statement, obtaining the specified statistical range, specified statistical method, and specified metric indicated by the first statistical query statement. Specifically, the specified metric is determined to be sales revenue, the specified statistical method is the summation method, and the specified statistical range is the entire year of 2020.

[0088] S102, if there is no aggregate table containing the data to be queried in the pre-stored aggregate tables, then based on the hierarchical structure between the pre-stored dimension tables, determine whether there is a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, whose statistical measure is the specified measure, and whose statistical method is the specified statistical method; wherein, the hierarchical structure between the dimension tables represents the relationship between the statistical ranges recorded in the dimension tables; an aggregate table records the measure values ​​obtained by statistically analyzing at least one measure in the fact table in different statistical ranges according to a statistical method;

[0089] Understandably, to speed up queries, query engines typically cache pre-built aggregation tables to reduce query time and resource consumption. An aggregation table records the values ​​of at least one metric from the fact table, calculated using a statistical method across different statistical ranges. For example, aggregation table A might be a table that sums the daily sales volume and sales revenue of product A in 2020. In this case, aggregation table A would record the total daily sales volume and total sales revenue of product A in 2020. If aggregation table A is as shown in Table 1 below, then if the data to be queried is the total sales volume of product A in the first quarter of 2020, the data to be queried will not exist in aggregation table A.

[0090] Table 1

[0091]

[0092] In one implementation, when no aggregate table containing the data to be queried exists in the pre-stored aggregate tables, the query engine will query and perform statistics from the fact table stored in the data warehouse. Specifically, it will query the fact table to find the sales volume of product A in each order during Q1 2020 and sum them up. This results in high query time and requires significant computing resources. Understandably, the fact table records data or metrics from specific business processes, such as sales order numbers, product IDs (identity), sales revenue, sales volume, etc., and the data in the fact table grows continuously over time.

[0093] To address this problem, this application determines whether a target aggregation table exists in each aggregation table based on the pre-stored hierarchical structure of the dimension tables. This target aggregation table records data whose statistical range falls within a specified statistical range, whose statistical measure is a specified measure, and whose statistical method is a specified statistical method. By utilizing the data in the target aggregation table when it exists, and since the data in the target aggregation table is pre-statistically analyzed, even if the first statistical query does not directly hit any of the aggregation tables (i.e., if none of the aggregation tables directly record the data to be queried), using the data in the target aggregation table can reduce query costs and improve query efficiency.

[0094] For example, if the data to be queried is the total sales volume of product A in the first quarter of 2020, then the first statistical query statement indicates that the specified statistical range is the first quarter of 2020, the specified statistical measure is sales volume, and the specified statistical method is summation. Since Table 1 records the total sales volume of product A every day in 2020, the statistical range from January 1, 2020 to March 31, 2020 falls within the statistical range of the first quarter of 2020. Furthermore, Table 1 includes data on the sales volume of product A calculated using the summation method for each day in 2020. In other words, Table 1 contains statistical data with the statistical measure of sales volume and the statistical method of summation; therefore, Table 1 is the target aggregation table.

[0095] For example, in practical applications, the statistical ranges recorded in each aggregation table are data under each statistical dimension. For instance, the statistical ranges recorded in Table 1, such as "T001" and "T002", are data under the daily statistical dimension. Since each aggregation table is obtained by pre-statistically analyzing the data in the fact table, and the data under each statistical dimension in the fact table actually stores identifiers that reference the data stored in each dimension table, the field values ​​of the fields related to the statistical range recorded in the aggregation table also store identifiers. Therefore, to determine whether there is an aggregation table in each aggregation table whose statistical range belongs to the specified statistical range, it is necessary to utilize the pre-stored hierarchical structure between each dimension table to determine the identifiers of each statistical range belonging to the specified statistical range from each dimension table. If there is an aggregation table in each aggregation table that contains the determined identifiers, then it is determined that there is an aggregation table in each aggregation table whose statistical range belongs to the specified statistical range.

[0096] It is understandable that, since the hierarchical structure between the dimension tables represents the relationship between the statistical ranges recorded in the dimension tables, the statistical ranges belonging to the specified statistical range can be determined based on this hierarchical structure.

[0097] For example, for a dimension table representing the statistical dimension of time, the data in that table could have a hierarchical structure of "year-quarter-month-day," and for a dimension table representing the statistical dimension of location, the data could have a hierarchical structure of "province-city-district-township." It is understood that the hierarchical structure between the dimension tables can be determined based on the foreign key relationships between the tables.

[0098] For example, if dimension table B is shown in Table 2, dimension table C is shown in Table 3, and dimension table B is associated with dimension table C through the foreign key field "FK":

[0099] Table 2

[0100]

[0101] Table 3

[0102]

[0103] Since the "FK" field in Table 2 and the "PK" field in Table 3 have a foreign key relationship (meaning the specific data corresponding to each value in the "FK" field in Table 2 is the data corresponding to the "PK" field in Table 3 with the same values), and Table 2 has a "month-day" hierarchical structure while Table 3 has a "year-quarter" hierarchical structure, the hierarchical structure between Table 2 and Table 3 is "year-quarter-month-day". Therefore, the hierarchical structure between Table 2 and Table 3 represents the following relationships between statistical ranges: the statistical range corresponding to a day belongs to the statistical range corresponding to a month, the statistical range corresponding to a month belongs to the statistical range corresponding to a quarter, and the statistical range corresponding to a quarter belongs to the statistical range corresponding to a year. Based on this hierarchical structure, to query the total sales volume of product A in the first quarter of 2020, we can determine that "F01" is the field corresponding to the specified statistical range. Then, we can query all sub-ranges belonging to the "F01" field from each dimension table as the statistical ranges belonging to the specified statistical range. For example, "T001, T002, and T003" in Table 2 are all sub-ranges belonging to the "F01" field. Since Table 1 contains data corresponding to "T001, T002, T003", meaning that Table 1 contains data whose statistical range belongs to the specified statistical range, and Table 1 contains data whose statistical measure is the specified measure and whose statistical method is the specified statistical method, Table 1 is the target aggregation table.

[0104] Additionally, it should be noted that if only one query engine exists in the query scenario, the pre-stored aggregation tables can be aggregation tables stored in the query engine executing the data query method provided in the embodiments of this application; if multiple query engines exist in the query scenario, the pre-stored aggregation tables can be aggregation tables stored in the shared storage space of each query engine, such as aggregation tables uniformly stored in the data lake in an open storage format for each query engine to call, which is reasonable.

[0105] S103. If a target aggregation table exists in each aggregation table, then based on the hierarchical structure, determine the statistical ranges that belong to the specified statistical ranges recorded in the target aggregation table, obtain the measurement values ​​of the specified measures corresponding to each determined statistical range from the target aggregation table, and obtain the query results based on the obtained measurement values ​​and the specified statistical method.

[0106] In this embodiment, if a target aggregation table exists in each aggregation table, the statistical ranges belonging to the specified statistical range recorded in the target aggregation table are determined based on the hierarchical structure. It can be understood that, based on the hierarchical structure between the dimension tables, an identifier representing the specified statistical range can be found in each dimension table. Then, based on this identifier, the identifiers corresponding to the sub-ranges belonging to the specified statistical range are determined from each dimension table. The measure values ​​of the specified metrics corresponding to the determined identifiers are obtained from the target aggregation table. The determined identifiers recorded in the target aggregation table represent the statistical ranges belonging to the specified statistical range. After determining the statistical ranges belonging to the specified statistical range recorded in the target aggregation table, the measure values ​​of the specified metrics corresponding to each statistical range are statistically analyzed according to a specified statistical method to obtain the query results.

[0107] For example, if the specified statistical range is the first quarter of 2020, the specified statistical method is summation, the specified metric is sales volume, the target aggregation table is Table 1 above, and the dimension tables are Tables 2 and 3 above, then based on the hierarchical structure between Tables 2 and 3, the identifier representing the specified statistical range, "F01," is found in each dimension table. Based on this identifier, the identifiers corresponding to the sub-ranges belonging to the specified statistical range are determined from each dimension table, including "T001" to "T091." Therefore, the sales volumes corresponding to the field values ​​"T001" to "T091" in Table 1 can be summed to obtain the total sales volume for the first quarter of 2020, which serves as the query result.

[0108] The solution provided in this application receives a first statistical query statement for the data to be queried, determines the specified statistical range, specified statistical method, and specified metric indicated by the first statistical query statement, and if no aggregate table containing the data to be queried exists in the pre-stored aggregate tables, then based on the hierarchical structure between the pre-stored dimension tables, it determines whether there exists a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, whose statistical metric is the specified metric, and whose statistical method is the specified statistical method. If a target aggregate table exists, the query is performed using the data in the target aggregate table; that is, the metric values ​​corresponding to the specified metrics for each statistical range belonging to the specified statistical range are obtained from the target aggregate table, and then the obtained metric values ​​are statistically analyzed according to the specified statistical method to obtain the query results. Since the target aggregation table is an aggregation table whose statistical range, statistical metric, and statistical method are all specified, meaning that the data stored in the target aggregation table is data obtained by pre-statistically analyzing the original data within the specified statistical range, the query can be performed using the data in the target aggregation table when no data to be queried is directly recorded in any of the aggregation tables. In other words, the query and statistics are performed based on the target aggregation table obtained by performing certain statistics on the original data. Compared with querying and analyzing directly from the original data, this can reduce query costs and improve query efficiency.

[0109] Optionally, in another embodiment of this application, the above-described data query method is applied to an intermediate query engine, which is communicatively connected to each backend query engine; Figure 1 Based on the illustrated embodiment, it also includes:

[0110] In response to receiving the first statistical query statement, detect whether the first statistical query statement carries a preset keyword;

[0111] In this embodiment, the intermediate query engine can be a OneSQL query engine, which communicates with backend query engines such as Presto, Impala, and Spark. For example, in practical applications, preset keywords can be pre-set by relevant technical personnel so that when the first statistical query statement is received, it can be detected whether the first statistical query statement carries preset keywords, so that different response processes can be executed based on different detection results. For example, the preset keyword could be "OneSQL" or other keywords, which are all reasonable.

[0112] Accordingly, in this embodiment, if there is no aggregate table containing the data to be queried in the pre-stored aggregate tables in step S102, then based on the hierarchical structure between the pre-stored dimension tables, it is determined whether there is a target aggregate table in each aggregate table whose statistical range belongs to a specified statistical range, whose statistical metric is a specified metric, and whose statistical method is a specified statistical method, including:

[0113] If the first statistical query statement carries a preset keyword, and there is no aggregate table containing the data to be queried in the pre-stored aggregate tables, then based on the hierarchical structure between the pre-stored dimension tables, it is determined whether there is a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, whose statistical measure is the specified measure, and whose statistical method is the specified statistical method.

[0114] Accordingly, in this embodiment, the above data query method further includes steps A1-A2:

[0115] A1, if the first statistical query statement does not carry a preset keyword, based on the metadata table of each backend query engine, determine the target backend query engine that stores the fact table containing the specified metric from each backend query engine; wherein, the metadata table of each backend query engine is used to describe the raw data managed by that backend query engine.

[0116] A2 sends the first statistical query statement to the target backend query engine, so that the target backend query engine can query and statistically analyze the raw data it manages according to the specified statistical range, specified statistical method and specified metric, and obtain the query results.

[0117] In this embodiment, the intermediate query engine records metadata tables for each backend query engine. Each backend query engine's metadata table describes the raw data managed by that backend query engine. For example, the raw data managed by backend query engine A includes the page views and click-through rate of product A, while the raw data managed by backend query engine B includes the sales revenue and sales volume of product A. Therefore, different metadata can be used to describe the various metrics included in the raw data managed by backend query engine A, resulting in metadata table A describing the raw data managed by backend query engine A; and similarly, different metadata can be used to describe the various metrics included in the raw data managed by backend query engine B, resulting in metadata table B describing the raw data managed by backend query engine B.

[0118] If the first statistical query statement does not contain a preset keyword, the intermediate query engine determines the target backend query engine that stores the fact table of the specified metric based on the metadata tables of each backend query engine. For example, if the specified metric is sales revenue, and the metadata tables of each backend query engine show that metadata table B manages data related to that specified metric, and metadata table B describes the raw data managed by backend query engine B, then backend query engine B can be determined as the target backend query engine.

[0119] After identifying the target backend query engine, a first statistical query statement is sent to it. This instructs the target backend query engine to retrieve the metric values ​​of a specified measure within a specified statistical range from the raw data it manages, and then perform statistical analysis on the retrieved metric values ​​according to a specified statistical method to obtain the query results. For example, if the first statistical query statement specifies the first quarter of 2020 as the statistical range, sales volume as the specified statistical measure, and summation as the specified statistical method, then upon receiving the first statistical query statement, the target backend query engine will retrieve the sales volume of product A in each order during the first quarter of 2020 from the raw data it manages, sum the sales volume, and obtain the query results.

[0120] Understandably, in practical applications, users can omit preset keywords when writing the first statistical query statement if they know the target aggregation table is not present in the pre-stored aggregation tables. This allows the intermediate query engine to directly call the backend query engine to query the fact table after receiving the first statistical query statement, without needing the intermediate query engine to determine the existence of the target aggregation table through the hierarchical structure. This speeds up the query compared to the intermediate query engine calling the backend query engine to query the fact table only after determining the absence of the target aggregation table based on the hierarchical structure. Therefore, by executing different response processes based on whether the query statement contains preset keywords upon receiving the first statistical query statement, query efficiency can be further improved.

[0121] Optionally, in one implementation, the above method further includes:

[0122] If the target aggregation table does not exist in any of the aggregation tables, the target backend query engine that stores the fact table containing the specified metric is determined from each backend query engine based on the metadata table of each backend query engine recorded; wherein, the metadata table of each backend query engine is used to describe the raw data managed by that backend query engine.

[0123] Send the first statistical query statement to the target backend query engine so that the target backend query engine can query and statistically analyze the raw data it manages according to the specified statistical range, specified statistical method and specified metric, and obtain the query results.

[0124] In this implementation, when the intermediate query engine determines, based on the pre-stored hierarchical structure between the dimension tables, that the target aggregation table does not exist in any of the aggregation tables, it needs to query and perform statistics from the fact table. That is, the intermediate query engine, based on the recorded metadata tables of each backend query engine, determines the target backend query engine that stores the fact table of the specified metric. After determining the target backend query engine, a first statistical query statement is sent to it, enabling the target backend query engine to query the metric values ​​of the specified metric within the specified statistical range from the raw data it manages, and to perform statistics on the queried metric values ​​according to the specified statistical method to obtain the query results.

[0125] As can be seen, this solution ensures that query results can be obtained even when the target aggregation table does not exist in any of the aggregation tables.

[0126] Alternatively, in one implementation, each aggregation table is stored in a shared storage space between the intermediate query engine and each backend query engine;

[0127] In step S103 above, based on the hierarchical structure, the statistical ranges belonging to the specified statistical range recorded in the target aggregation table are determined. The metric values ​​of the specified measures corresponding to each determined statistical range are obtained from the target aggregation table. Based on the obtained metric values ​​and the specified statistical method, the query results are obtained, including steps B1-B2:

[0128] B1, based on the hierarchical structure, the first statistical query statement is rewritten to obtain the second statistical query statement; wherein, the second statistical query statement is used to indicate: determine the statistical ranges belonging to the specified statistical ranges recorded in the target aggregation table stored in the shared storage space, obtain the metric values ​​of the specified metrics corresponding to the determined statistical ranges from the target aggregation table, and obtain the query results based on the obtained metric values ​​and the specified statistical method;

[0129] B2, execute the second statistical query statement; or, send the second statistical query statement to the specified backend query engine so that the specified backend query engine executes the second statistical query statement; wherein the specified backend query engine is selected from each backend query engine according to a predetermined selection rule.

[0130] In this implementation, the shared storage space for the intermediate query engine and each backend query engine can be Hudi (Hadoop Upserts Deletes and Incrementals, a streaming data lake), Iceberge (a data lake for large-scale data analysis scenarios), etc. Each aggregation table is stored in the data lake in an open storage format for use by each query engine.

[0131] For example, the system architecture corresponding to this implementation is as follows: Figure 2 As shown, the client communicates with the intermediate query engine OneSQL, which in turn communicates with each backend query engine. When a user performs a data query, they enter the query statement in the client. After receiving the query statement, the intermediate query engine OneSQL can use the intelligent scheduling module to retrieve the unified aggregate tables stored in the data lake for querying, or it can send the query statement to the backend query engines so that the backend query engines can query from the fact tables they manage.

[0132] Understandably, after receiving the first statistical query statement, the intermediate query engine, in order to speed up the query process, can parse and rewrite the first statistical query statement according to the hierarchical structure to obtain the second statistical query statement. This rewritten second statistical query statement instructs the engine to: determine the statistical ranges belonging to the specified statistical range recorded in the target aggregation table stored in the shared storage space; retrieve the metric values ​​of the specified measures corresponding to each determined statistical range from the target aggregation table; and obtain the query results based on the retrieved metric values ​​and the specified statistical method.

[0133] The logic for rewriting the first statistical query statement is as follows: combine the dimension tables and the target aggregation table according to the hierarchical structure to achieve cross-table aggregation analysis.

[0134] After receiving the second statistical query statement, the intermediate query engine can directly execute it. Alternatively, to reduce the load on the intermediate query engine, the second statistical query statement can be sent to a designated backend query engine, causing the designated backend query engine to execute it. The designated backend query engine is selected from among the backend query engines according to a predetermined selection rule. For example, this predetermined selection rule could be to determine the backend query engine with the lowest load or the backend query engine with the strongest computational performance; both are reasonable. It is understood that determining the backend query engine with the lowest load or the strongest computational performance as the designated backend query engine can improve query efficiency.

[0135] It should be noted that the embodiments of this application do not limit the predetermined selection rules. For example, the predetermined selection rules can also be related to the business to which the data to be queried belongs. For example, if backend query engine A is used to manage the data of business A, then when the data to be queried is related to business A, it is reasonable to select backend query engine A as the specified backend query engine.

[0136] To better understand the solutions provided in the embodiments of this application, the following is combined with... Figure 3 A specific example of the data query method provided in the embodiments of this application will be introduced.

[0137] This example includes the following steps:

[0138] S301, Receive query statement;

[0139] That is, it receives SQL statements (corresponding to the first statistical query statement mentioned above). The SQL statement is the query statement entered by the user through the client when querying data, and the intermediate query engine receives the SQL statement.

[0140] S302, Determine if a preset keyword exists in the query statement; if yes, execute S303; otherwise, execute S309.

[0141] After the intermediate query engine receives the SQL statement, it checks whether "OneSQL" (corresponding to the preset keyword mentioned above) exists in the SQL statement.

[0142] S303, parse out the aggregated dimension column 'a' and the aggregate function;

[0143] After receiving the SQL statement, the intermediate query engine parses the SQL statement to obtain the aggregate dimension column a (corresponding to the specified statistical range mentioned above) and the aggregate function (corresponding to the specified statistical method mentioned above).

[0144] For example, if you need to query the sales volume for each quarter, the SQL statement indicates the specified statistical range as the statistical dimension of each quarter, the specified metric as the SQL statement is the sales volume, and the specified statistical method as the SQL statement is the summation method. If the field representing the quarter in the dimension table is Dim3, then the parsed aggregate dimension column a is the column containing the Dim3 field, and the parsed aggregate function is the summation function.

[0145] S304, find the dimension column S1 and aggregate function for each aggregate table;

[0146] If aggregation table A records the total daily sales volume of product A, then the aggregation function for this aggregation table is a summation function. If the field representing day in the dimension table is Dim1, then the dimension column S1 in aggregation table A is the column containing Dim1. Dimension column S1 can be one or more columns.

[0147] S305, determine whether a depends on the column in S1; if yes, execute S306; otherwise, execute S309.

[0148] If the data in the aggregation dimension column 'a' needs to be obtained by statistically analyzing the data in dimension column 'S1', then 'a' depends on the columns in 'S1'.

[0149] S306, Determine if there are identical aggregate functions; if yes, execute S307; otherwise, execute S309.

[0150] If the aggregate function parsed from the SQL statement is the same as the aggregate function in a found aggregate table, then there are identical aggregate functions.

[0151] S307, Rewrite the query statement according to the hierarchical structure;

[0152] After determining that there are identical aggregate functions through S306, the aggregate table with the same aggregate function is the target aggregate table. Based on the hierarchical structure between the dimension tables, the target aggregate table and each dimension table can be joined. The SQL statement is then rewritten to use the rewritten SQL statement (corresponding to the second statistical query statement above) to achieve cross-table aggregate queries.

[0153] For example, if you need to query the sales volume for each quarter, the SQL statement specifies the statistical range as each quarter as the statistical dimension, the metric as sales volume, and the statistical method as summation. If the dimension table includes fields Dim4 (representing year), Dim3 (representing quarter), Dim2 (representing month), and Dim1 (representing day), and the fact table has field M1 representing sales volume, then the original SQL statement could be as follows:

[0154] select

[0155] Dim3,

[0156] M1

[0157] from

[0158] OneSQL

[0159] group by

[0160] Dim3.

[0161] The SQL statement above contains the keyword "OneSQL," indicating a query for the values ​​of the Dim3 and M1 fields, grouped according to the Dim3 dimension. The SQL statement does not specify a statistical method. If the Dim3 field is recorded in table2 (dimension table 2), and table1 (dimension table 1) is linked to table2 via a foreign key "FK" field, which points to the primary key "PK" field of table2, and if Aggregation1 (aggregation table 1) contains records of the M1 and Dim1 fields in the pre-stored aggregation tables, the rewritten SQL statement can be as follows:

[0162] select

[0163] Table2.Dim3,Agg1.M1

[0164] from

[0165] Aggregation1 Aggr1

[0166] Join (select distinct Dim1,FK from table1) t1

[0167] ON Aggr1.Dim1 = t1.Dim1

[0168] Join table2 t2

[0169] ON t1.FK = t2.PK

[0170] group by

[0171] t2.Dim3".

[0172] The SQL statement above joins Aggregation1 and table1 using the "Join" field, and also joins table2 based on the foreign key relationship between table1 and table2, thus achieving cross-table aggregation analysis.

[0173] S308, execute the rewritten query statement;

[0174] The OneSQL query engine executes the rewritten SQL statement and obtains the query results.

[0175] S309, send the query statement to the backend query engine;

[0176] The original SQL statement is sent to the backend query engine so that the backend query engine can execute the SQL statement and obtain the query results.

[0177] As can be seen, this solution, by designing preset keywords and combining them with a hierarchical structure to link tables, can utilize data from the aggregate table even when the first statistical query does not directly hit it, without needing to rebuild the aggregate table, thus greatly improving query efficiency. Only the functionality of the intermediate query engine needs improvement, enabling it to rewrite received queries according to the hierarchical structure. This rewritten query can be executed by the intermediate query engine or sent to the backend query engine for execution. By using the intermediate query engine to parse and rewrite SQL statements, a unified SQL technology is achieved, without requiring improvements to the functionality of each backend query engine, thus avoiding redundant development. This approach saves costs; avoids building separate caches for each query engine, and saves resources by building a unified cache; in dimension tables with dependencies, "joining" allows the aggregation tables to be reused even when the query does not directly hit any aggregation table, reducing the cost of rebuilding aggregation tables and avoiding the high cost of the backend query engine querying and calculating from the fact table by directly dispatching aggregation query statements to the backend query engine, thus reducing the overall system load; it greatly reduces the complexity of writing SQL statements and the risk of SQL errors; it reduces the difficulty of building caches, improves the efficiency of cache building, and improves overall work efficiency.

[0178] Alternatively, in another embodiment of this application, such as Figure 4 As shown, the construction method of each aggregation table includes steps S401-S405:

[0179] S401, determine the statistically significant dimensions corresponding to the original data recorded in the pre-stored fact table, and use them as the root data space;

[0180] For example, if the raw data recorded in the fact table includes order ID, product category ID, sales location ID, and sales amount, then the statistical dimensions include the product category dimension and the sales location dimension. If the raw data also includes a sales time ID, then the statistical dimensions also include the sales time dimension. If the raw data includes data with three statistical dimensions: {product category, sales time, and sales location}, then the root data space is the data space constructed according to the three dimensions of {product category, sales time, and sales location}.

[0181] S402, for multiple first sub-data spaces of the root data space, determine the processing cost of the first sub-data space based on the data complexity of the projection of the original data into the first sub-data space; where the processing cost is positively correlated with the data complexity;

[0182] It is understandable that the first sub-data space of the root data space refers to any one of the data spaces among all sub-data spaces, including the root data space itself and the empty set. For example, if the root data space is {product category, sales time, sales location}, then the first sub-data space of the root data space can be any one of the following: data spaces with dimensions {product category, sales time, sales location}, data spaces with dimensions {product category, sales time}, data spaces with dimensions {sales time, sales location}, data spaces with dimensions {product category}, data spaces with dimensions {sales time}, data spaces with dimensions {sales location}, and data spaces with dimensions {empty set}.

[0183] For example, determining multiple first sub-data spaces of the root data space can be achieved by constructing a data cube tree, where the data cube represents the relationships between the sub-data spaces. Each data space is considered a node in the data cube tree, and the relationships between data spaces are considered branches of the data cube tree. The number of nodes can be determined by calculating the number of possible combinations of each dimension of the root data space based on known original data. For example, if the data space {A, B, C} is a three-dimensional data space, and this data space {A, B, C} is the root data space, the possible combinations of each dimension of the root data space can be calculated as follows: data space {A, B, C}, data space {A, B}, data space {A, C}, data space {B, C}, data space {A}, data space {B}, data space {C}, and data space {None}. Here, {None} represents the empty set. If a node is used to represent a data space, it can be done as follows: Figure 5 The data cube tree shown represents the correlation between different data spaces. Figure 5 In the middle, nodes ABC represent the data space {A, B, C}, nodes AB represent the data space {A, B}, nodes AC represent the data space {A, C}, nodes BC represent the data space {B, C}, nodes A represent the data space {A}, nodes B represent the data space {B}, nodes C represent the data space {C}, and the node "empty" (also known as the node None) represents the data space {None}.

[0184] For multiple first sub-data spaces of the root data space, the processing cost of each first sub-data space can be determined for each of all first sub-data spaces of the root data space; or the processing cost of each first sub-data space in a subset of all first sub-data spaces of the root data space can be determined.

[0185] In one possible embodiment, the first sub-data space can be randomly selected. In another possible embodiment, it can be based on prior knowledge or actual needs to exclude some first sub-data spaces from all first sub-data spaces in the root data space, and determine the processing cost of each first sub-data space in the remaining part of the first sub-data space. For example, taking the root data space as {product category, sales time, sales location}, if prior knowledge determines that the processing cost of the first sub-data spaces {product category}, {sales time}, and {sales location} has no impact on determining the materialized revenue, then these three first data spaces can be excluded, and only the processing costs of the first sub-data spaces {product category, sales time, sales location}, {product category, sales time}, {product category, sales location}, and {sales time, sales location} can be determined.

[0186] The data complexity of the projection of the original data into the first sub-data space can refer to the algorithmic complexity of obtaining the target data (statistical data obtained from the original data statistics) from the preprocessed data of the original data within the first sub-data space. Since the preprocessed data of the original data within the first sub-data space is obtained based on the projection, the data complexity can be estimated based on the projection. It is understood that different types of data have different data complexities; for example, the data complexity of floating-point data is higher than that of integer data. Therefore, in one possible embodiment, the data complexity of the projection can be determined based on the data type of the projection of the original data into the first sub-data space. For example, if the data type of the projection is floating-point, the data complexity is determined to be a preset first complexity; if the data type of the projection is integer, the data complexity is determined to be a preset second algorithmic complexity, where the first complexity is higher than the second complexity.

[0187] Understandably, the more data to process, the higher the algorithm complexity tends to be. Therefore, in one possible embodiment, the data complexity of the projection can be determined based on the cardinality of the original data in the first sub-data space, such as using the cardinality of the projection as the data complexity. Another example is to normalize the cardinality of the projection and use the normalized result as the data complexity. In yet another possible embodiment, the data complexity of the projection can also be determined by combining the cardinality of the original data in the first sub-data space and the data type.

[0188] S403, for a first sub-data space that is not a materialized sub-data space, determine the materialization benefit of the first sub-data space based on the processing cost of the first sub-data space and the processing cost of each materialized sub-data space; wherein, the materialized sub-data space initially includes the root data space, and the materialization benefit is negatively correlated with the processing cost of the first sub-data space and positively correlated with the processing cost of each materialized sub-data space;

[0189] In this embodiment, a materialized sub-data space refers to the data space that has been determined to be the one that needs to build the corresponding aggregation table, and a non-materialized sub-data space means that the data space does not currently belong to the materialized sub-data space.

[0190] For a first sub-data space that is not a materialized sub-data space, it can be determined for each first sub-data space in the first sub-data space of all the first sub-data spaces of the root data space that is not a materialized sub-data space, and the materialization benefit of each first sub-data space can be determined. Alternatively, it can be determined for each first sub-data space in the partial first sub-data spaces of all the first sub-data spaces of the root data space that is not a materialized sub-data space, and the materialization benefit of each first sub-data space in the partial first sub-data spaces can be determined.

[0191] In one possible embodiment, the first sub-data space can be randomly selected. In another possible embodiment, it can be based on prior knowledge or actual needs to exclude some first sub-data spaces from those that are not all materialized sub-data spaces, and determine the materialization benefits of each first sub-data space in the remaining part of the first sub-data space. For example, taking the first sub-data spaces that are not materialized sub-data spaces as including: {product category, sales time, sales location}, {product category, sales time}, {product category, sales location}, {sales time, sales location}, {product category}, {sales time}, {sales location} as an example, if it is determined based on prior knowledge that the materialization benefits of the first sub-data spaces {product category}, {sales time}, and {sales location} cannot meet the preset materialization benefit conditions, then these three first sub-data spaces can be excluded, and only the materialization benefits of the first sub-data spaces {product category, sales time, sales location}, {product category, sales time}, {product category, sales location}, and {sales time, sales location} can be determined.

[0192] The materialization benefit of the first sub-data space can be reflected in the computational cost of obtaining the target data based on other materialized sub-data spaces when the first sub-data space is not materialized, versus the computational cost of obtaining the target data based on the materialized first sub-data space when the first sub-data space is materialized.

[0193] For example, if the first sub-data space of the root data space includes: root data space {A, B, C}, data space {A, B}, data space {B, C}, data space {A, C}, and data space {A}, and the materialized data spaces include: root data space {A, B, C} and data space {A, B}. To determine whether data space {A} is worth materializing, we need to determine the materialization benefit of data space {A}. Since in this example, the preprocessed data within data space {A} can only be processed to obtain the target data within data space {A}, the materialization benefit of data space {A} is:

[0194] The materialized benefit of data space {A} = the computational cost of obtaining the target data within data space {A} based on data space {A, B} - the computational cost of obtaining the target data within data space {A} based on data space {A}. That is, the materialized benefit of data space {A} = the processing cost of data space {A, B} - the processing cost of data space {A}. It is evident that in this example, the lower the processing cost of the first sub-data space, the higher the corresponding materialized benefit. The same logic applies to other examples; the lower the processing cost of the first sub-data space, the higher the corresponding materialized benefit. In other words, the materialized benefit of the first sub-data space is negatively correlated with its processing cost.

[0195] Because the first materialized sub-data spaces are correlated, for example, the data space of {product category} can be regarded as a sub-data space of the data space of {product category, sales time}. The processing cost of a first sub-data space that is not a materialized sub-data space is affected by the processing cost of other materialized first sub-data spaces. Specifically, the processing cost of a first sub-data space that is not a materialized sub-data space is positively correlated with the processing cost of other materialized first sub-data spaces. For example, if the processing cost of the data space {product category, sales time} is low, then the processing difficulty of determining {product category} or {sales time} based on {product category, sales time} is also correspondingly low, that is, the processing cost is correspondingly low.

[0196] For example, based on the processing cost of each first sub-data space, a greedy algorithm can be used to determine the materialization benefit of each first sub-data space. Then, the first sub-data space with the largest materialization benefit is materialized, thereby obtaining the optimal data cube tree.

[0197] The execution logic of the greedy algorithm can be summarized as follows:

[0198] S = {top view}

[0199] For i = 1 to k, do begin

[0200] Select that view v not in S such that B(v,S) is maximized;

[0201] S = S union {v}

[0202] End

[0203] Where S is the set of sub-data spaces that need to be materialized in the final construction, v is each first sub-data space, and B(v, S) is the materialization benefit of constructing the first sub-data space v.

[0204] S404, determine the first sub-data space that satisfies the preset materialized revenue conditions, and use it as the new materialized sub-data space;

[0205] The first sub-data space whose materialized revenue satisfies the preset materialized revenue conditions is determined as the new materialized sub-data space. This can be achieved by sorting the materialized revenue of each first sub-data space in descending order, and selecting the first sub-data spaces with the highest materialized revenue from each sorted sub-data space as the new materialized sub-data space. Alternatively, the materialized revenue can be sorted in ascending order, and the first sub-data spaces with the lowest materialized revenue from each sorted sub-data space are determined as the new materialized sub-data space.

[0206] S405: For each materialized sub-data space, aggregate the projection of the original data onto the materialized sub-data space to obtain the aggregated table corresponding to the materialized sub-data space.

[0207] After determining the materialized sub-data space, the projection of the original data onto the materialized sub-data space is aggregated. That is, the real original data is projected onto the materialized sub-data space according to each dimension of the materialized sub-data space. Then, the projected data is aggregated using statistical methods such as summation and averaging to obtain the aggregated table corresponding to each materialized sub-data space.

[0208] For example, if each materialized sub-data space includes {product category} and {sales location}, the raw data recorded in the fact table can be projected onto the data space {product category} to obtain data such as sales volume and sales revenue for each product category. Then, this raw data can be projected onto the data space {sales location} to obtain data such as sales volume and sales revenue for each sales location. Finally, the sales volume for each product category can be summed to obtain an aggregate table, which records the total sales volume corresponding to each product category.

[0209] Understandably, by defining the first sub-data space whose materialization yield meets the preset materialization yield condition as the materialized sub-data space, the generation of aggregation tables corresponding to materialized sub-data spaces with lower materialization yield can be reduced, thereby saving system resources. Furthermore, since the materialization yield of the materialized sub-data space meets the preset materialization yield condition, the projection of the original data onto the materialized sub-data space is aggregated to obtain the aggregation table corresponding to the materialized sub-data space. Subsequent queries using the data stored in this aggregation table can minimize the processing cost during the query, thus significantly improving the efficiency of obtaining the queried data from the data in the aggregation table.

[0210] As can be seen, this solution can further improve data query efficiency.

[0211] Corresponding to the above method embodiments, this application provides a data query system, such as... Figure 6 As shown, the system includes an intermediate query engine 610 and a backend query engine 620;

[0212] The intermediate query engine 610 is used to execute any of the data query methods described above.

[0213] The backend query engine 620 is used to receive the first statistical query statement sent by the intermediate query engine 610, and to perform queries and statistics on the raw data it manages according to the specified statistical range, specified statistical method and specified metric indicated by the first statistical query statement, so as to obtain the query results.

[0214] Optionally, the shared storage space of the intermediate query engine and the backend query engine stores various aggregation tables;

[0215] The backend query engine is also used to receive a second statistical query statement sent by the intermediate query engine, and execute the received second statistical query statement based on the data stored in the shared storage space.

[0216] It should be noted that the functions implemented by the intermediate query engine and the backend query engine in the system have been described in the above method embodiments, and will not be repeated here.

[0217] Corresponding to the above method embodiments, this application also provides a data query device, such as... Figure 7 As shown, the device includes:

[0218] The first determining module 710 is used to determine the specified statistical range, specified statistical method and specified metric indicated by the first statistical query statement in response to receiving a first statistical query statement for the data to be queried;

[0219] The second determining module 720 is used to determine, based on the hierarchical structure between the pre-stored aggregation tables, whether there exists a target aggregation table in each aggregation table whose statistical range belongs to the specified statistical range, whose statistical measure is the specified measure, and whose statistical method is the specified statistical method, if no aggregation table containing the data to be queried exists in the pre-stored aggregation tables; wherein, the hierarchical structure between the dimension tables represents the relationship between the statistical ranges recorded in the dimension tables; an aggregation table records the measure values ​​obtained by statistically analyzing at least one measure in the fact table in different statistical ranges according to a statistical method;

[0220] The first query module 730 is used to determine, based on the hierarchical structure, each statistical range recorded in the target aggregation table that belongs to the specified statistical range, if the target aggregation table exists in each aggregation table, obtain the metric value of the specified metric corresponding to each determined statistical range from the target aggregation table, and obtain the query result based on the obtained metric value and the specified statistical method.

[0221] Optionally, the device is applied to an intermediate query engine, which is communicatively connected to each backend query engine;

[0222] The device further includes:

[0223] The detection module is used to detect whether the first statistical query statement carries a preset keyword in response to receiving the first statistical query statement;

[0224] The second determining module 720 is specifically used for:

[0225] If the first statistical query statement carries the preset keyword, and if there is no aggregate table containing the data to be queried in the pre-stored aggregate tables, then based on the hierarchical structure between the pre-stored dimension tables, it is determined whether there is a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, whose statistical metric is the specified metric, and whose statistical method is the specified statistical method.

[0226] The device further includes:

[0227] The third determining module is used to determine, based on the metadata tables of each backend query engine recorded, a target backend query engine that stores a fact table containing the specified metric, when the first statistical query statement does not carry the preset keyword; wherein, the metadata table of each backend query engine is used to describe the raw data managed by that backend query engine.

[0228] The second query module is used to send the first statistical query statement to the target backend query engine, so that the target backend query engine can perform queries and statistics on the raw data it manages according to the specified statistical range, the specified statistical method and the specified metric, and obtain query results.

[0229] Optionally, the device further includes:

[0230] The fourth determining module is used to determine, based on the metadata tables of each backend query engine recorded, the target backend query engine that stores the fact table containing the specified metric if the target aggregation table does not exist in any of the aggregation tables; wherein, the metadata table of each backend query engine is used to describe the raw data managed by that backend query engine.

[0231] The third query module is used to send the first statistical query statement to the target backend query engine, so that the target backend query engine can perform queries and statistics on the raw data it manages according to the specified statistical range, the specified statistical method and the specified metric, and obtain query results.

[0232] Optionally, each aggregation table is stored in the shared storage space of the intermediate query engine and each backend query engine;

[0233] The second determining module 720 includes:

[0234] The rewrite submodule is used to rewrite the first statistical query statement based on the hierarchical structure to obtain a second statistical query statement; wherein, the second statistical query statement is used to instruct: determine each statistical range belonging to the specified statistical range recorded in the target aggregation table stored in the shared storage space, obtain the metric value of the specified metric corresponding to each determined statistical range from the target aggregation table, and obtain the query result based on the obtained metric value and the specified statistical method;

[0235] An execution submodule is used to execute the second statistical query statement; or, to send the second statistical query statement to a specified backend query engine so that the specified backend query engine executes the second statistical query statement; wherein the specified backend query engine is selected from each backend query engine according to a predetermined selection rule.

[0236] Optionally, the construction methods for each aggregate table include:

[0237] Identify the statistically significant dimensions corresponding to the original data recorded in the pre-stored fact table, and use them as the root data space;

[0238] For multiple first sub-data spaces of the root data space, the processing cost of the first sub-data space is determined based on the data complexity of the projection of the original data into the first sub-data space; wherein the processing cost is positively correlated with the data complexity.

[0239] For a first sub-data space that is not a materialized sub-data space, the materialization benefit of the first sub-data space is determined based on the processing cost of the first sub-data space and the processing cost of each materialized sub-data space; wherein, the materialized sub-data space initially includes the root data space, and the materialization benefit is negatively correlated with the processing cost of the first sub-data space and positively correlated with the processing cost of each materialized sub-data space;

[0240] The first sub-data space that satisfies the preset materialized benefit conditions is determined as the new materialized sub-data space;

[0241] For each of the materialized sub-data spaces, the projection of the original data into the materialized sub-data space is aggregated to obtain an aggregated table corresponding to the materialized sub-data space.

[0242] In the technical solution of this application, the acquisition, storage, use, processing, transmission, provision and disclosure of data such as fact tables and aggregation tables are all carried out with the user's authorization.

[0243] This application also provides an electronic device, such as... Figure 8 As shown, it includes:

[0244] Memory 801 is used to store computer programs;

[0245] The processor 802, when executing the program stored in the memory 801, implements the steps of any of the above data query methods.

[0246] Furthermore, the aforementioned electronic device may also include a communication bus and / or a communication interface, with the processor 802, the communication interface, and the memory 801 communicating with each other via the communication bus.

[0247] The communication bus mentioned in the above electronic devices can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. This communication bus can be divided into address bus, data bus, control bus, etc. For ease of illustration, only one thick line is used to represent it in the diagram, but this does not mean that there is only one bus or one type of bus.

[0248] The communication interface is used for communication between the aforementioned electronic devices and other devices.

[0249] The memory may include random access memory (RAM) or non-volatile memory (NVM), such as at least one disk storage device. Optionally, the memory may also be at least one storage device located remotely from the aforementioned processor.

[0250] The processors mentioned above can be general-purpose processors, including central processing units (CPUs), network processors (NPs), etc.; they can also be digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components.

[0251] In another embodiment provided in this application, a computer-readable storage medium is also provided, which stores a computer program that, when executed by a processor, implements the steps of any of the above-described data query methods.

[0252] In another embodiment provided in this application, a computer program product containing instructions is also provided, which, when run on a computer, causes the computer to execute any of the data query methods described in the above embodiments.

[0253] In the above embodiments, implementation can be achieved entirely or partially through software, hardware, firmware, or any combination thereof. When implemented using software, it can be implemented entirely or partially in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, all or part of the processes or functions described in the embodiments of this application are generated. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium or transmitted from one computer-readable storage medium to another. For example, the computer instructions can be transmitted from one website, computer, server, or data center to another website, computer, server, or data center via wired (e.g., coaxial cable, fiber optic, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) means. The computer-readable storage medium can be any available medium that a computer can access or a data storage device such as a server or data center that integrates one or more available media. The available medium can be a magnetic medium (e.g., floppy disk, hard disk, magnetic tape), an optical medium (e.g., DVD), or a solid-state drive (SSD), etc.

[0254] It should be noted that, in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0255] The various embodiments in this specification are described in a related manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the system, apparatus, electronic device, and computer-readable storage medium embodiments are basically similar to the method embodiments, so the descriptions are relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0256] The above description is merely a preferred embodiment of this application and is not intended to limit the scope of protection of this application. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application are included within the scope of protection of this application.

Claims

1. A data query method, characterized by, The method comprises: in response to receiving a first statistical query statement for to-be-queried data, determining a specified statistical range, a specified statistical manner and a specified metric indicated by the first statistical query statement; wherein the specified statistical range is indicated by an aggregation dimension list obtained by parsing the first statistical query statement; the specified statistical manner is indicated by an aggregation function obtained by parsing the first statistical query statement; if there is no aggregation table containing the to-be-queried data in each pre-stored aggregation table, determining whether there is a target aggregation table in each aggregation table, which has a statistical range belonging to the specified statistical range, a statistical metric being the specified metric and a statistical manner being the specified statistical manner, based on a hierarchical structure between each pre-stored dimension table; wherein the hierarchical structure between each dimension table indicates a belonging relationship between statistical ranges recorded in each dimension table, and the hierarchical structure between each dimension table is determined according to a foreign key relationship between dimension tables; in two dimension tables having a foreign key relationship, a statistical range represented by data of one field value of a foreign key field in a dimension table with a smaller statistical range belongs to a statistical range represented by data of a primary key field having the field value in a dimension table with a larger statistical range; and each aggregation table records metric values obtained by at least one metric in a fact table being statistically processed in different statistical ranges according to one statistical manner; the determining whether there is the target aggregation table in each aggregation table based on the hierarchical structure between each pre-stored dimension table comprises: finding a dimension column and an aggregation function of each aggregation table; judging whether the aggregation dimension column depends on columns in the dimension column of each aggregation table; wherein if data of the aggregation dimension column needs to be obtained by being statistically processed on data in the dimension column of an aggregation table, the aggregation dimension column depends on the columns in the dimension column of the aggregation table; if the aggregation dimension column depends on the columns in the dimension column of the aggregation table, determining an aggregation table having the same aggregation function as the aggregation function obtained by parsing the first statistical query statement as the target aggregation table; based on the hierarchical structure, finding an identifier indicating the specified statistical range from each dimension table; determining identifiers corresponding to sub-ranges belonging to the specified statistical range from each dimension table according to the found identifiers; obtaining metric values of the specified metric corresponding to the determined identifiers from the target aggregation table; and statistically processing the metric values of the specified metric corresponding to each statistical range indicated by the determined identifiers according to the specified statistical manner to obtain a query result.

2. The method of claim 1, wherein, The method is applied to an intermediate query engine, which is in communication connection with each backend query engine; the method further comprises: in response to receiving the first statistical query statement, detecting whether a preset keyword is carried in the first statistical query statement. If none of the pre-stored aggregate tables contains the data to be queried, based on the hierarchy among the pre-stored dimension tables, it is determined whether there is a target aggregate table in the aggregate tables, which has a statistical range belonging to the specified statistical range, a statistical metric being the specified metric, and a statistical manner being the specified statistical manner, including: If none of the pre-stored aggregate tables contains the data to be queried, based on the hierarchy among the pre-stored dimension tables, it is determined whether there is a target aggregate table in the aggregate tables, which has a statistical range belonging to the specified statistical range, a statistical metric being the specified metric, and a statistical manner being the specified statistical manner; The method further includes: If the first statistical query statement does not carry the preset keyword, based on the metadata table of each recorded backend query engine, a target backend query engine storing a fact table containing the specified metric is determined from each backend query engine; wherein the metadata table of each backend query engine is used to describe the original data managed by the backend query engine; The first statistical query statement is sent to the target backend query engine, so that the target backend query engine queries and counts the original data managed by itself according to the specified statistical range, the specified statistical manner, and the specified metric, to obtain a query result.

3. The method of claim 2, wherein, The method further includes: If none of the aggregate tables contains the target aggregate table, based on the metadata table of each recorded backend query engine, a target backend query engine storing a fact table containing the specified metric is determined from each backend query engine; wherein the metadata table of each backend query engine is used to describe the original data managed by the backend query engine; The first statistical query statement is sent to the target backend query engine, so that the target backend query engine queries and counts the original data managed by itself according to the specified statistical range, the specified statistical manner, and the specified metric, to obtain a query result.

4. The method of claim 2, wherein, Each aggregate table is stored in a shared storage space of the intermediate query engine and each backend query engine; Based on the hierarchy, it is determined that each statistical range belonging to the specified statistical range is recorded in the target aggregate table, the metric value of the specified metric corresponding to each determined statistical range is obtained from the target aggregate table, and based on the obtained metric value and the specified statistical manner, a query result is obtained, including: Based on the hierarchy, the first statistical query statement is rewritten to obtain a second statistical query statement; wherein the second statistical query statement is used to instruct that each statistical range belonging to the specified statistical range is determined to be recorded in the target aggregate table stored in the shared storage space, the metric value of the specified metric corresponding to each determined statistical range is obtained from the target aggregate table, and based on the obtained metric value and the specified statistical manner, a query result is obtained; execute the second statistical query statement; or, send the second statistical query statement to a specified backend query engine to make the specified backend query engine execute the second statistical query statement; wherein the specified backend query engine is selected from each backend query engine according to a predetermined selection rule.

5. The method according to any one of claims 1 to 4, characterized in that, The construction of each aggregated table comprises: determining each statistical dimension corresponding to the original data of the record in the pre-stored fact table as a root data space; for a plurality of first sub-data spaces of the root data space, determining a processing cost of the first sub-data space according to a data complexity of a projection of the original data on the first sub-data space; wherein the processing cost is positively correlated with the data complexity; for a plurality of first sub-data spaces that are not materialized sub-data spaces, determining a materialization benefit of the first sub-data space according to the processing cost of the first sub-data space and a processing cost of each materialized sub-data space; wherein the materialized sub-data space initially comprises the root data space, the materialization benefit is negatively correlated with the processing cost of the first sub-data space, and is positively correlated with the processing cost of each materialized sub-data space; determining the first sub-data space whose materialization benefit meets a preset materialization benefit condition as a new materialized sub-data space; for each materialized sub-data space, performing an aggregation processing on a projection of the original data on the materialized sub-data space to obtain an aggregated table corresponding to the materialized sub-data space.

6. A data query system, characterized by, comprise an intermediate query engine and a backend query engine; the intermediate query engine is configured to execute the method in any one of claims 1-5; the backend query engine is configured to receive a first statistical query statement sent by the intermediate query engine, and perform a query and statistics on original data managed by itself according to a specified statistical range, a specified statistical manner and a specified metric indicated by the first statistical query statement to obtain a query result.

7. The system of claim 6, wherein, each aggregated table is stored in a shared storage space of the intermediate query engine and the backend query engine; the backend query engine is further configured to receive a second statistical query statement sent by the intermediate query engine, and execute the received second statistical query statement based on data stored in the shared storage space.

8. A data query apparatus, characterized by comprising: The apparatus comprises: a first determination module configured to, in response to receiving a first statistical query statement for to-be-queried data, determine a specified statistical range, a specified statistical manner and a specified metric indicated by the first statistical query statement; wherein the specified statistical range is indicated by an aggregated dimension list obtained by parsing the first statistical query statement; and the specified statistical manner is indicated by an aggregation function obtained by parsing the first statistical query statement; The second determining module is configured to, if there is no aggregation table containing the to-be-queried data in the pre-stored aggregation tables, determine, based on a hierarchy among the pre-stored dimension tables, whether there is a target aggregation table in the aggregation tables, which has a statistical range belonging to the specified statistical range, a statistical metric being the specified metric, and a statistical manner being the specified statistical manner; wherein the hierarchy among the dimension tables represents a belonging relationship among statistical ranges recorded in the dimension tables, and the hierarchy among the dimension tables is determined according to a foreign key relationship among the dimension tables; in two dimension tables having the foreign key relationship, a statistical range represented by data corresponding to a field value of a foreign key field in a dimension table with a smaller statistical range belongs to a statistical range represented by data corresponding to a primary key field having the field value in a dimension table with a larger statistical range; and the aggregation table records metric values obtained by performing statistics on at least one metric in the fact table in different statistical ranges according to one statistical manner. The determining, based on the hierarchy among the pre-stored dimension tables, whether there is the target aggregation table in the aggregation tables, which has the statistical range belonging to the specified statistical range, the statistical metric being the specified metric, and the statistical manner being the specified statistical manner, includes: finding a dimension column and an aggregation function of each aggregation table; judging whether the aggregation dimension column depends on a column in the dimension column of each aggregation table; wherein the aggregation dimension column depends on the column in the dimension column of the aggregation table if data of the aggregation dimension column needs to be obtained by performing statistics on data in the dimension column of the aggregation table; if the aggregation dimension column depends on the column in the dimension column of the aggregation table, determining, as the target aggregation table, an aggregation table having the same aggregation function as the aggregation function obtained by analyzing the first statistical query statement; The first query module is configured to find, based on the hierarchy, an identifier representing the specified statistical range from the dimension tables; determine, from the dimension tables, identifiers corresponding to sub-ranges belonging to the specified statistical range according to the found identifiers; acquire, from the target aggregation table, metric values of the specified metric corresponding to the determined identifiers; and perform statistics on the metric values of the specified metric corresponding to each statistical range represented by the determined identifiers according to the specified statistical manner, to obtain a query result.

9. An electronic device, comprising: The memory is configured to store a computer program. The processor is configured to execute the program stored in the memory to implement the method in any one of claims 1-5. The computer program stored in the computer-readable storage medium is configured to be executed by the processor to implement the method in any one of claims 1-5.

10. A computer-readable storage medium, characterized in that, ​

Images