Data query method, system, device and equipment and storage medium

Abstract

The embodiment of the invention provides a data query method, system and device, equipment and a storage medium, and relates to the technical field of data processing. According to the specific implementation scheme, a first statistical query statement is received, and a specified statistical range, a specified statistical mode and a specified metric indicated by the first statistical query statement are determined; if the aggregation table containing the to-be-queried data does not exist in each pre-stored aggregation table, determining whether a target aggregation table of which the statistical range belongs to a specified statistical range, the statistical metric is a specified metric and the statistical mode is a specified statistical mode exists in each aggregation table based on a hierarchical structure between each pre-stored dimension table; and if yes, based on the hierarchical structure, obtaining a metric value of a specified metric corresponding to each statistical range belonging to the specified statistical range from the target aggregation table, and based on the obtained metric value and a specified statistical mode, obtaining a query result. Visibly, through the scheme, the query efficiency can be improved.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a data query method, system, device, equipment and storage medium. Background Art

[0002] With the continuous development of information technology, data has become an important factor driving the development of the industry. The growth of massive data leads to slow query and analysis of data. In order to speed up the query, the query engine usually caches pre-built aggregation tables to reduce query time and resource consumption. Among them, the aggregation table is a table of measurement data under the statistical dimension obtained by pre-statisticing the original data under the statistical dimension that the user is interested in.

[0003] When querying business data using a query engine, the query engine will construct the metric data under the statistical dimension with a high query frequency into an aggregate table for cache. Different query engines have their own built aggregate tables cached. If a query engine is used to query data, and the query statement does not hit the aggregate table cached in the query engine, it is necessary to query the original data in the fact table from the data warehouse and perform statistics, resulting in low query efficiency.

[0004] Therefore, how to improve query efficiency has become a technical problem that needs to be solved urgently. Summary of the invention

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

[0006] In a first aspect, an embodiment of the present application provides a data query method, the method comprising:

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

[0008] 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, the statistical metric is the specified metric, and the statistical method is the specified statistical method; wherein the hierarchical structure between the dimension tables represents the belonging relationship between the statistical ranges recorded in the dimension tables; an aggregate table records at least one metric in the fact table obtained by performing statistics in different statistical ranges according to a statistical method;

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

[0010] Optionally, the method is applied to an intermediate query engine, and the intermediate query engine is communicatively connected with each back-end query engine; the method further includes:

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

[0012] 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, determining whether there is a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, the statistical metric is the specified metric, and the statistical method is the specified statistical method, including:

[0013] In the case where the preset keyword is carried in the first statistical query statement, 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 metric is the specified metric, and whose statistical method is the specified statistical method;

[0014] The method further includes: in the case where the first statistical query statement does not carry the preset keyword, based on the recorded metadata tables of each backend query engine, determining a target backend query engine storing 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 original data managed by the backend query engine;

[0015] The first statistical query statement is sent to the target backend query engine, so that the target backend query engine queries and performs statistics on the original data managed by itself according to the specified statistical range, the specified statistical method and the specified metric to obtain a query result.

[0016] Optionally, the method further comprises: if the target aggregate table does not exist in each aggregate table, determining, based on the recorded metadata tables of each backend query engine, a target backend query engine storing 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 original data managed by the backend query engine;

[0017] The first statistical query statement is sent to the target backend query engine, so that the target backend query engine queries and performs statistics on the original data managed by itself according to the specified statistical range, the specified statistical method and the specified metric to obtain a query result.

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

[0019] The step of determining each statistical range recorded in the target aggregate table and belonging to the specified statistical range based on the hierarchical structure, obtaining the metric value of the specified metric corresponding to each determined statistical range from the target aggregate table, and obtaining the query result based on the obtained metric value and the specified statistical method, includes:

[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 recorded in the target aggregation table stored in the shared storage space and belonging to the specified statistical range, 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 designated backend query engine so that the designated backend query engine executes the second statistical query statement; wherein the designated backend query engine is selected from the backend query engines according to a predetermined selection rule.

[0022] Optionally, each aggregate table is constructed in the following ways:

[0023] Determine each statistically identifiable dimension corresponding to the original data recorded in the pre-stored fact table as the root data space;

[0024] 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 in the first sub-data space; wherein the processing cost is positively correlated with the data complexity;

[0025] For a plurality of the first sub-data spaces that are not materialized sub-data spaces, determining the materialized benefit of the first sub-data space according to the processing cost of the first sub-data space and the processing costs of each materialized sub-data space; wherein the materialized sub-data space initially includes the root data space, and the materialized 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] Determine the first sub-data space whose materialized benefit satisfies a preset materialized benefit condition as a new materialized sub-data space;

[0027] For each of the materialized sub-data spaces, aggregation processing is performed on the projection of the original data in the materialized sub-data space to obtain an aggregation table corresponding to the materialized sub-data space.

[0028] In a second aspect, an embodiment of the present application provides 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 above data query methods;

[0030] The backend query engine is used to receive the first statistical query statement sent by the intermediate query engine, and query and count the original data managed by itself according to the specified statistical range, specified statistical method and specified measurement indicated by the first statistical query statement to obtain query results.

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

[0032] The back-end 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 the data stored in the shared storage space.

[0033] In a third aspect, an embodiment of the present application provides a data query device, the device comprising:

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

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

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

[0037] Optionally, the device is applied to an intermediate query engine, and the intermediate query engine is communicatively connected with each back-end query engine;

[0038] The device also includes:

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

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

[0041] In the case where the preset keyword is carried in the first statistical query statement, 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 metric is the specified metric, and whose statistical method is the specified statistical method;

[0042] The device also includes:

[0043] A third determination module is used to determine, when the first statistical query statement does not carry the preset keyword, a target backend query engine that stores a fact table containing the specified metric from each backend query engine based on the recorded metadata tables of 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;

[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 queries and counts the original data managed by itself according to the specified statistical range, the specified statistical method and the specified measurement to obtain the query result.

[0045] Optionally, the device further comprises:

[0046] A fourth determination module is used to determine, if the target aggregate table does not exist in each aggregate table, a target backend query engine storing a 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 original data managed by the 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 queries and counts the original data managed by itself according to the specified statistical range, the specified statistical method and the specified measurement to obtain the query result.

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

[0049] The second determining module includes:

[0050] A rewriting 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 indicate: determining each statistical range recorded in the target aggregation table stored in the shared storage space and belonging to the specified statistical range, 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;

[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 various backend query engines according to a predetermined selection rule.

[0052] Optionally, each aggregate table is constructed in the following ways:

[0053] Determine each statistically identifiable dimension corresponding to the original data recorded in the pre-stored fact table as the root data space;

[0054] 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 in the first sub-data space; wherein the processing cost is positively correlated with the data complexity;

[0055] For a plurality of the first sub-data spaces that are not materialized sub-data spaces, determining the materialized benefit of the first sub-data space according to the processing cost of the first sub-data space and the processing costs of each materialized sub-data space; wherein the materialized sub-data space initially includes the root data space, and the materialized 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] Determine the first sub-data space whose materialized benefit satisfies a preset materialized benefit condition as a new materialized sub-data space;

[0057] For each of the materialized sub-data spaces, aggregation processing is performed on the projection of the original data in the materialized sub-data space to obtain an aggregation table corresponding to the materialized sub-data space.

[0058] In a fourth aspect, an embodiment of the present application provides an electronic device, including:

[0059] Memory, used to store computer programs;

[0060] The processor is used to implement any of the above-mentioned data query methods when executing the program stored in the memory.

[0061] In a fifth aspect, an embodiment of the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the data query method described in any one of the above items is implemented.

[0062] In a sixth aspect, an embodiment of the present application provides a computer program product, which includes executable instructions. When the executable instructions are executed on a computer, the computer executes any of the data query methods described above.

[0063] Beneficial effects of the embodiments of the present application:

[0064] The solution provided by the embodiment of the present 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 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, determines whether there is a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, the statistical metric is the specified metric, and the statistical method is the specified statistical method. When the target aggregate table exists, the query is implemented using the data in the target aggregate table, that is, the metric values ​​of the specified metric corresponding to 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, so as to obtain the query result. Since the target aggregate table is an aggregate table whose statistical range belongs to the specified statistical range, the statistical metric is the specified metric, and the statistical method is the specified statistical method, that is, the data stored in the target aggregate table is the data obtained by pre-statisticing the original data within the statistical range belonging to the specified statistical range, then when there is no direct record of the data to be queried in each aggregate table, the query is implemented using the data in the target aggregate table, that is, query statistics are performed on the basis of the target aggregate table obtained by certain statistics on the original data. Compared with directly querying and counting from the original data, this can reduce query costs and improve query efficiency.

[0065] Of course, implementing any product or method of the present application does not necessarily require achieving all of the advantages described above at the same time. BRIEF DESCRIPTION OF THE DRAWINGS

[0066] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application, and for ordinary technicians in this field, other embodiments can also be obtained based on these drawings.

[0067] Figure 1 A flowchart of a data query method provided in an embodiment of the present application;

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

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

[0070] Figure 4 A flowchart of a method for constructing an aggregate table provided in an embodiment of the present application;

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

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

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

[0074] Figure 8 A block diagram of an electronic device for implementing the data query method provided in an embodiment of the present application. DETAILED DESCRIPTION

[0075] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field based on the present application belong to the scope of protection of the present application.

[0076] The data query method provided in the embodiment of the present application can be applied to query engines in various electronic devices. Among them, various electronic devices can be personal computers, servers, and other devices with data processing capabilities, and the query engine can be a back-end query engine such as Presto (an open source distributed SQL query engine), Impala (a large-scale parallel processing SQL query engine), Spark (a query engine for processing structured data), or an intermediate query engine OneSQL (a query engine for processing structured and semi-structured data) that communicates with each back-end query engine, and so on. It is understandable that the OneSQL query engine can parse and rewrite SQL (Structured Query Language) statements, and send the rewritten SQL statements to the back-end query engine so that the back-end query engine performs the actual query work. In addition, it is understandable that the data query method provided in the embodiment of the present application can be implemented by software, hardware, or a combination of software and hardware.

[0077] The data query method provided in the embodiment of the present application may include the following steps:

[0078] In response to receiving a first statistical query statement for the data to be queried, determining a specified statistical range, a specified statistical method, and a 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, determine whether there is a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, the statistical metric is the specified metric, and the statistical method is the specified statistical method; wherein the hierarchical structure between the dimension tables represents the belonging relationship between the statistical ranges recorded in the dimension tables; an aggregate table records the measurement value of at least one measurement in different statistical ranges;

[0080] If there is a target aggregate table in each aggregate table, then based on the hierarchical structure, the statistical ranges recorded in the target aggregate table that belong to the specified statistical range are determined, and the metric values ​​of the specified metrics corresponding to the determined statistical ranges are obtained from the target aggregate table, and the query results are obtained based on the obtained metric values ​​and the specified statistical method.

[0081] The solution provided by the embodiment of the present 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 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, determines whether there is a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, the statistical metric is the specified metric, and the statistical method is the specified statistical method. When the target aggregate table exists, the query is implemented using the data in the target aggregate table, that is, the metric values ​​of the specified metric corresponding to 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, so as to obtain the query result. Since the target aggregate table is an aggregate table whose statistical range belongs to the specified statistical range, the statistical metric is the specified metric, and the statistical method is the specified statistical method, that is, the data stored in the target aggregate table is the data obtained by pre-statisticing the original data within the statistical range belonging to the specified statistical range, then when there is no direct record of the data to be queried in each aggregate table, the query is implemented using the data in the target aggregate table, that is, query statistics are performed on the basis of the target aggregate table obtained by certain statistics on the original data. Compared with directly querying and counting from the original data, this can reduce query costs and improve query efficiency.

[0082] The data query method provided by the embodiment of the present application is introduced below in conjunction with the accompanying drawings. Figure 1 As shown, steps S101-S103 are included:

[0083] S101, in response to receiving a first statistical query statement for data to be queried, determining a specified statistical range, a specified statistical method, and a 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 the specified statistical method indicated by the first statistical query statement are 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 used to obtain the data to be queried belongs. For example, if the data to be queried is the total sales of commodity A in 2020, the original data used to obtain the data to be queried is the sales of each order of commodity A in 2020, and the metric to which the original data belongs is sales, then the specified metric indicated by the first statistical query statement is sales.

[0085] Exemplarily, the specified statistical scope may be a statistical scope of a time dimension, a statistical scope of a location dimension, or a statistical scope of a combined dimension of time and location, etc. For example, if the specified statistical scope is a statistical scope of a time dimension, the specified statistical scope may be 2018-2024; if the specified statistical scope is a statistical scope of a location dimension, the specified statistical scope may be all areas within the scope of City X; if the specified statistical scope is a statistical scope of a combined dimension of time and location, the specified statistical scope may be all areas within Province Y from 2020 to 2021.

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

[0087] For example, in actual applications, if the data to be queried is the total sales of commodity A in 2020, it is necessary to query the sales of all orders of commodity A in 2020, and calculate the sum to obtain the total sales of commodity A in 2020. Then, relevant technical personnel can write a first statistical query statement and input it into the client for data query, so that the client calls the query engine to parse the first statistical query statement, and obtains the specified statistical range, specified statistical method and specified metric indicated by the first statistical query statement, that is, it is determined that the specified metric is sales, the specified statistical method is the method of calculating the sum, and the specified statistical range is the whole 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, the statistical metric is the specified metric, and the statistical method is the specified statistical method; wherein the hierarchical structure between the dimension tables represents the belonging relationship between the statistical ranges recorded in the dimension tables; an aggregate table records at least one metric in the fact table obtained by performing statistics in different statistical ranges according to a statistical method;

[0089] It is understandable that in order to speed up the query, the query engine usually caches pre-built aggregate tables to reduce query time and resource consumption. An aggregate table records at least one metric in the fact table that is statistically obtained in different statistical ranges according to a statistical method. For example, aggregate table A is a table obtained by summing the sales volume and sales of commodity A every day in 2020. The aggregate table A records the measurement value of the total sales volume and the measurement value of the total sales of commodity A every day in 2020. If aggregate table A is as shown in Table 1 below, then if the data to be queried is the total sales volume of commodity A in the first quarter of 2020, there is no data to be queried in aggregate table A.

[0090] Table 1

[0091]

[0092] In one implementation, when there is no aggregate table containing the data to be queried in the pre-stored aggregate tables, the query engine will query and count from the fact table stored in the data warehouse, that is, query the sales volume of commodity A in each order in the first quarter of 2020 from the fact table and sum it up, resulting in a high query time and the need to consume more computing resources. It is understandable that the fact table records data or indicators in the specific business process, such as sales order number, sales product ID (Identity), sales, sales volume, etc., and the data in the fact table will continue to grow over time.

[0093] In order to solve this problem, the present application determines whether there is a target aggregate table in each aggregate table according to the hierarchical structure between the pre-stored dimension tables. The target aggregate table records data whose statistical range belongs to the specified statistical range, the statistical measurement is the specified measurement, and the statistical method is the specified statistical method. By using the data in the target aggregate table to implement the query when the target aggregate table exists, since the data in the target aggregate table is data that has been counted in advance, when the first statistical query statement does not directly hit each aggregate table, that is, when the data to be queried is not directly recorded in each aggregate table, the query is implemented using the data in the target aggregate table, which can reduce the query cost and improve the query efficiency.

[0094] Exemplarily, if the data to be queried is the total sales volume of commodity A in the first quarter of 2020, the specified statistical range indicated by the first statistical query statement is the first quarter of 2020, the specified statistical measure is sales volume, and the specified statistical method is the summation method. Since Table 1 records the total sales volume of commodity A every day in 2020, the statistical range from January 1, 2020 to March 31, 2020 belongs to the statistical range of the first quarter of 2020, and Table 1 includes data on the sales volume of commodity A every day in 2020 in a summation manner, that is, there are statistical data in Table 1 whose statistical measure is sales volume and whose statistical method is the summation method, then Table 1 is the target aggregation table.

[0095] Exemplarily, in actual applications, each statistical range recorded in each aggregate table is data under each statistical dimension. For example, the statistical ranges such as "T001", "T002" recorded in Table 1 are data under the daily statistical dimension. Since each aggregate table is obtained by pre-statistical analysis of the data in the fact table, and the data under each statistical dimension in the fact table actually stores identifiers, which reference the data stored in each dimension table, then the field values ​​of the fields about the statistical range recorded in the aggregate table are also stored as identifiers. Therefore, when determining whether there is an aggregate table whose statistical range belongs to the specified statistical range in each aggregate table, it is necessary to use the hierarchical structure between the pre-stored dimension tables to determine the identifiers of the statistical ranges belonging to the specified statistical range from each dimension table. If there is an aggregate table containing the determined identifiers in each aggregate table, it is determined that there is an aggregate table whose statistical range belongs to the specified statistical range in each aggregate table.

[0096] It can be understood that, since the hierarchical structure between the dimension tables represents the belonging relationship between the statistical ranges recorded in the dimension tables, the statistical ranges belonging to the specified statistical range can be determined according to the hierarchical structure.

[0097] For example, for each dimension table of the statistical dimension of time, the data in each dimension table may have a hierarchical structure of "year-quarter-month-day", and for each dimension table of the statistical dimension of location, the data in each dimension table may have a hierarchical structure of "province-city-district-township". It can be understood that the hierarchical structure between the dimension tables can be determined according to the foreign key relationship between the tables.

[0098] For example, if dimension table B is as shown in Table 2, dimension table C is as 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 are in a foreign key relationship, that is, the specific data corresponding to each field value in the "FK" field in Table 2 is the data corresponding to the "PK" field with the corresponding field value in Table 3, Table 2 has a "month-day" hierarchy, and Table 3 has a "year-quarter" hierarchy, then the hierarchy between Table 2 and Table 3 is "year-quarter-month-day". Then the belonging relationship between the statistical ranges represented by the hierarchy between Table 2 and Table 3 includes: the statistical range corresponding to the day belongs to the statistical range corresponding to the month, the statistical range corresponding to the month belongs to the statistical range corresponding to the quarter, and the statistical range corresponding to the quarter belongs to the statistical range corresponding to the year. According to this hierarchy, if you want to query the total sales volume of commodity A in the first quarter of 2020, you can determine that "F01" is the field corresponding to the specified statistical range, then you 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, T003" in Table 2 are all sub-ranges belonging to the "F01" field. Since there is data corresponding to "T001, T002, T003" in Table 1, that is, there is data in Table 1 whose statistical range belongs to the specified statistical range, and there is data in Table 1 whose statistical measurement is the specified measurement and whose statistical method is the specified statistical method, Table 1 is the target aggregation table.

[0104] In addition, it should be noted that if there is only one query engine in the query scenario, the pre-stored aggregation tables may be aggregation tables stored in the query engine that executes the data query method provided in the embodiment of the present application; if there are multiple query engines in the query scenario, the pre-stored aggregation tables may 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 there is a target aggregate table in each aggregate table, then based on the hierarchical structure, determine the statistical ranges recorded in the target aggregate table that belong to the specified statistical range, obtain the metric values ​​of the specified metrics corresponding to the determined statistical ranges from the target aggregate table, and obtain the query results based on the obtained metric values ​​and the specified statistical method.

[0106] In this embodiment, if there is a target aggregation table in each aggregation table, each statistical range recorded in the target aggregation table that belongs to the specified statistical range is determined based on the hierarchical structure. It can be understood that, according to the hierarchical structure between each dimension table, it is possible to find an identifier representing the specified statistical range from each dimension table, and then determine each identifier corresponding to the sub-range belonging to the specified statistical range from each dimension table based on the identifier, and obtain the metric value of the specified metric corresponding to each determined identifier 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 each statistical range recorded in the target aggregation table that belongs to the specified statistical range, the metric value of the specified metric corresponding to each statistical range is counted according to the specified statistical method to obtain the query result.

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

[0108] The solution provided by the embodiment of the present 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 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, determines whether there is a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, the statistical metric is the specified metric, and the statistical method is the specified statistical method. When the target aggregate table exists, the query is implemented using the data in the target aggregate table, that is, the metric values ​​of the specified metric corresponding to 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, so as to obtain the query result. Since the target aggregate table is an aggregate table whose statistical range belongs to the specified statistical range, the statistical metric is the specified metric, and the statistical method is the specified statistical method, that is, the data stored in the target aggregate table is the data obtained by pre-statisticing the original data within the statistical range belonging to the specified statistical range, then when there is no direct record of the data to be queried in each aggregate table, the query is implemented using the data in the target aggregate table, that is, query statistics are performed on the basis of the target aggregate table obtained by certain statistics on the original data. Compared with directly querying and counting from the original data, this can reduce query costs and improve query efficiency.

[0109] Optionally, in another embodiment of the present application, the above data query method is applied to an intermediate query engine, and the intermediate query engine is connected to each back-end query engine in communication; Figure 1 Based on the embodiment shown, it also includes:

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

[0111] In this embodiment, the intermediate query engine may be a OneSQL query engine, which is connected to a backend query engine such as Presto, Impala, or Spark. Exemplarily, in actual applications, the preset keyword may be set in advance by relevant technical personnel, so that when the first statistical query statement is received, it may be detected whether the first statistical query statement carries the preset keyword, so that different response processes may be executed according to different detection results. For example, the preset keyword may 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 the above 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 the specified statistical range, the statistical metric is the specified metric, and the statistical method is the specified statistical method, including:

[0113] In the case where the first statistical query statement carries a preset keyword, 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, the statistical metric is the specified metric, and the statistical method is the specified statistical method;

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

[0115] A1, when the first statistical query statement does not carry a preset keyword, based on the recorded metadata tables of each backend query engine, determine a target backend query engine that stores a fact table containing a specified metric 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;

[0116] A2, sending a first statistical query statement to the target backend query engine, so that the target backend query engine queries and performs statistics on the original data managed by itself according to the specified statistical range, specified statistical method and specified metric to obtain the query result.

[0117] In this embodiment, the metadata table of each backend query engine is recorded in the intermediate query engine, and the metadata table of each backend query engine is used to describe the original data managed by the backend query engine. For example, the original data managed by backend query engine A includes the number of views and click-through rate of commodity A, and the original data managed by backend query engine B includes the sales amount and sales volume of commodity A. Then, different metadata can be used to describe the various metrics included in the original data managed in backend query engine A, and metadata table A describing the original data managed by backend query engine A is obtained; and different metadata can be used to describe the various metrics included in the original data managed in backend query engine B, and metadata table B describing the original data managed by backend query engine B is obtained.

[0118] If the first statistical query statement does not carry a preset keyword, the intermediate query engine determines the target backend query engine that stores the fact table of the specified metric from each backend query engine according to the metadata table of each backend query engine recorded. For example, if the specified metric is sales, the metadata table B managing the data related to the specified metric is queried from the metadata table of each backend query engine, and the metadata table B is used to describe the original data managed by the backend query engine B, then the backend query engine B can be determined as the target backend query engine.

[0119] After determining the target backend query engine, send a first statistical query statement to the target backend query engine, so that the target backend query engine queries the metric value of the specified metric within the specified statistical range from the managed raw data, and performs statistics on the queried metric value according to the specified statistical method to obtain the query result. For example, if the specified statistical range indicated by the first statistical query statement is the first quarter of 2020, the specified statistical metric is the sales volume, and the specified statistical method is the summation method, then after receiving the first statistical query statement, the target backend query engine queries the sales volume of commodity A in each order in the first quarter of 2020 from the managed raw data and sums them up to obtain the query result.

[0120] It is understandable that in actual applications, when the user clearly knows that the required target aggregate table does not exist in the pre-stored aggregate tables, the user can write the first statistical query statement without adding preset keywords, so that after the intermediate query engine receives the first statistical query statement, it can directly call the back-end query engine to query from the fact table without the need for the intermediate query engine to determine whether the target aggregate table exists through the hierarchy. This speeds up the query compared to the intermediate query engine calling the back-end query engine to query from the fact table when it determines that the target aggregate table does not exist based on the hierarchy. It can be seen that by executing different response processes based on the detection result of whether the query statement carries the preset keyword when the first statistical query statement is received, the query efficiency can be further improved.

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

[0122] If the target aggregate table does not exist in each aggregate table, based on the recorded metadata tables 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 original data managed by the backend query engine;

[0123] The first statistical query statement is sent to the target backend query engine, so that the target backend query engine queries and performs statistics on the original data managed by itself according to the specified statistical range, the specified statistical method and the specified metric to obtain the query result.

[0124] In this implementation, when the intermediate query engine determines that the target aggregate table does not exist in each aggregate table based on the hierarchical structure between the pre-stored dimension tables, it is necessary to query and count from the fact table. That is, the intermediate query engine determines the target backend query engine that stores the fact table of the specified metric from each backend query engine based on the metadata table of each backend query engine recorded. After determining the target backend query engine, a first statistical query statement is sent to the target backend query engine, so that the target backend query engine queries the metric value of the specified metric within the specified statistical range from the managed original data, and counts the queried metric value according to the specified statistical method to obtain the query result.

[0125] It can be seen that through this solution, when the target aggregate table does not exist in each aggregate table, it can be guaranteed that the query result can be obtained.

[0126] Optionally, in one implementation, each aggregate table is stored in a shared storage space of the intermediate query engine and each backend query engine;

[0127] In the above step S103, based on the hierarchical structure, each statistical range recorded in the target aggregation table belonging to the specified statistical range is determined, the metric value of the specified metric corresponding to each determined statistical range is obtained from the target aggregation table, and the query result is obtained based on the obtained metric value and the specified statistical method, including steps B1-B2:

[0128] B1, based on the hierarchical structure, rewrite the first statistical query statement to obtain a second statistical query statement; wherein the second statistical query statement is used to indicate: determine each statistical range recorded in the target aggregation table stored in the shared storage space and belonging to the specified statistical range, 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;

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

[0130] In this implementation, the shared storage space of 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 data analysis scenarios), etc. Each aggregation table is stored in the data lake in an open storage format for each query engine to call.

[0131] Exemplarily, the system architecture corresponding to this implementation is as follows: Figure 2 As shown, the client communicates with the intermediate query engine OneSQL, and the intermediate query engine OneSQL communicates with each backend query engine. When the user performs data query, he enters the query statement in the client. After the intermediate query engine OneSQL receives the query statement, it can use the intelligent scheduling module to obtain the unified stored aggregate tables from the data lake to query using the aggregate tables stored in the data lake, or send the query statement to the backend query engine so that the backend query engine can query from the fact table managed by itself.

[0132] It is understandable that after receiving the first statistical query statement, the intermediate query engine can parse and rewrite the first statistical query statement according to the hierarchical structure to obtain a second statistical query statement in order to speed up the query efficiency. The rewritten second statistical query statement is used to indicate: determine the statistical ranges that belong to the specified statistical range 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.

[0133] The logic for rewriting the first statistical query statement is: combining each dimension table with the target aggregation table according to the hierarchical structure to achieve cross-table aggregation analysis.

[0134] After obtaining the second statistical query statement, the intermediate query engine can directly execute the second statistical query statement. Alternatively, in order to reduce the load of the intermediate query engine, the second statistical query statement can also be sent to the designated backend query engine so that the designated backend query engine executes the second statistical query statement. Among them, the designated backend query engine is selected from each backend query engine according to a predetermined selection rule. Exemplarily, the predetermined selection rule can be to determine the backend query engine with the smallest load among each backend query engine as the designated backend query engine, or to determine the backend query engine with the strongest computing performance among each backend query engine as the designated backend query engine, which is reasonable. It can be understood that by determining the backend query engine with the smallest load or the strongest computing performance as the designated backend query engine, the query efficiency can be improved.

[0135] It should be noted that the embodiments of the present application do not limit the predetermined selection rules. For example, the predetermined selection rules may 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, backend query engine A can be selected as the designated backend query engine, which is reasonable.

[0136] In order to better understand the solution provided by the embodiment of the present application, Figure 3 A specific example of the data query method provided in an embodiment of the present application is introduced.

[0137] This example includes the following steps:

[0138] S301, receiving a query statement;

[0139] That is, an SQL statement (corresponding to the first statistical query statement mentioned above) is received. The SQL statement is a query statement input by a user through a client when querying data. The intermediate query engine receives the SQL statement.

[0140] S302, determine whether there is a preset keyword in the query statement; if so, execute S303, if not, execute S309;

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

[0142] S303, parsing out the aggregation dimension column a and the aggregation 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 in the above text) and the aggregate function (corresponding to the specified statistical method in the above text).

[0144] For example, if you need to query the sales volume of each quarter, the specified statistical range indicated by the SQL statement is the statistical range with quarter as the statistical dimension, the specified measure indicated by the SQL statement is the sales volume, and the specified statistical method indicated by the SQL statement is the summation method. If the field representing the quarter in the dimension table is Dim3, then the aggregate dimension column a obtained by parsing is the column where the Dim3 field is located, and the aggregate function obtained by parsing is the sum function.

[0145] S304, searching the dimension column S1 and the aggregation function of each aggregation table;

[0146] If the aggregate table A records the total sales volume of commodity A every day, the aggregate function of the aggregate table is a sum function, and if the field representing the day in the dimension table is Dim1, the dimension column S1 in the aggregate table A is the column where Dim1 is located. The dimension column S1 can be one or more columns.

[0147] S305, determine whether a depends on the columns in S1; if so, execute S306, if not, execute S309;

[0148] If the data of the aggregate dimension column a needs to be obtained by statistics based on the data in the dimension column S1, then a depends on the columns in S1.

[0149] S306, determine whether there is the same aggregation function; if so, execute S307, if not, execute S309;

[0150] If the aggregate function parsed by the SQL statement is the same as the aggregate function in the found aggregate table, then they have the same aggregate function.

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

[0152] After S306 determines that there is an identical aggregation function, the aggregation table with the identical aggregation function is the target aggregation table. According to the hierarchical structure between the dimension tables, the target aggregation table and the dimension tables can be joined, and the SQL statement can be rewritten to implement cross-table aggregation query using the rewritten SQL statement (corresponding to the second statistical query statement in the above text).

[0153] For example, if you need to query the sales volume of each quarter, the specified statistical range indicated by the SQL statement is the statistical range with quarter as the statistical dimension, the specified measure indicated by the SQL statement is the sales volume, and the specified statistical method indicated by the SQL statement is the sum method. If the dimension table includes the fields Dim4 (indicating year), Dim3 (indicating quarter), Dim2 (indicating month), and Dim1 (indicating day), and the field representing sales volume in the fact table is M1, the SQL statement before rewriting can be as follows:

[0154] "select

[0155] Dim3,

[0156] M1

[0157] from

[0158] OneSQL

[0159] group by

[0160] Dim3".

[0161] The above SQL statement contains the keyword "OneSQL", which means to query the values ​​of the Dim3 field and the M1 field and group them according to the dimension of Dim3; the SQL statement does not reflect the specified statistical method. If the Dim3 field is recorded in table2 (dimension table 2), table1 (dimension table 1) is associated with table2 through the foreign key "FK" field, and the "FK" field points to the primary key "PK" field of table2. If the pre-stored aggregation tables have records of the M1 field and the Dim1 field in Aggregation1 (aggregation table 1), the rewritten SQL statement can be as follows:

[0162] "select

[0163] Table 2. 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 above SQL statement joins Aggregation1 and table1 through the "Join" field, and joins table2 based on the foreign key relationship between table1 and table2, thereby realizing cross-table aggregation analysis.

[0173] S308, executing the rewritten query statement;

[0174] The OneSQL query engine executes the rewritten SQL statement to obtain the query result.

[0175] S309, sending 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 executes the SQL statement to obtain the query result.

[0177] It can be seen that this solution associates tables by designing preset keywords and combining hierarchical structures. When the first statistical query statement does not directly hit the aggregate table, the data in the aggregate table can be used without rebuilding the aggregate table, thereby greatly improving the query efficiency. It only needs to improve the function of the intermediate query engine so that it can rewrite the received query statement according to the hierarchical structure. The rewritten query statement can be executed by the intermediate query engine or sent to the back-end query engine for execution. The SQL statement can be parsed and rewritten by the intermediate query engine, unifying the SQL technology without improving the function of each back-end query engine and avoiding duplication of construction. , saving costs; avoiding building independent caches for each query engine, and achieving the purpose of saving resources by building a unified cache; in dimension tables with dependencies, by "joining" each dimension table, the aggregate table can be reused when the query statement does not directly hit the aggregate table, reducing the cost of rebuilding the aggregate table, avoiding directly dispatching the aggregate query statement to the back-end query engine, resulting in the high cost of the back-end query engine querying and counting from the fact table, reducing the overall system load; greatly reducing the complexity of writing SQL statements, reducing the risk of errors in writing SQL statements; reducing the difficulty of building a cache, improving the efficiency of building a cache, and improving overall work efficiency.

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

[0179] S401, determining each statistically available dimension corresponding to the original data recorded in the pre-stored fact table as a root data space;

[0180] For example, if the original data recorded in the fact table includes order ID, product category ID, sales location ID, and sales amount, the statistical dimensions include product category dimension and sales location dimension. If the original data also includes sales time ID, the statistical dimensions also include sales time dimension. If the original data includes data of three statistical dimensions of {product category, sales time, and sales location}, 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 a plurality of first sub-data spaces of the root data space, determining a processing cost of the first sub-data space according to the data complexity of the projection of the original data in the first sub-data space; wherein the processing cost is positively correlated with the data complexity;

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

[0183] Exemplarily, a method for determining multiple first sub-data spaces of a root data space may be to construct a data cube tree, where the data cube is used to represent the correlation between each sub-data space. Each data space is regarded as a node in the data cube tree, and the correlation between data spaces is regarded as the branches of the data cube tree. The number of each node can be determined by calculating the number of various possible combinations of each dimension of the root data space based on known original data. Exemplarily, the data space {A, B, C} is a three-dimensional data space. With the data space {A, B, C} as the root data space, the possible combinations of each dimension of the root data space that can be calculated include: 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}. Among them, {None} represents an empty set. If a node is used to represent a data space, the following can be used: Figure 5 The data cube tree shown represents the correlation between the data spaces. Figure 5 The node ABC represents the data space {A, B, C}, the node AB represents the data space {A, B}, the node AC represents the data space {A, C}, the node BC represents the data space {B, C}, the node A represents the data space {A}, the node B represents the data space {B}, the node C represents 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 may be determined for each first sub-data space among all the first sub-data spaces of the root data space; or the processing cost of each first sub-data space among some of the first sub-data spaces of all the first sub-data spaces of the root data space may be determined.

[0185] In one possible embodiment, the part of the first sub-data space may be randomly selected. In another possible embodiment, part of the first sub-data space may be excluded from all the first sub-data spaces of the root data space based on prior knowledge or actual needs, and the processing cost of each first sub-data space in the remaining other part of the first sub-data space is determined. For example, taking the root data space as {commodity category, sales time, sales location} as an example, it is determined based on prior knowledge that the processing cost of the first sub-data space {commodity category}, {sales time}, {sales location} has no effect on the determination of materialized benefits, then the three first data spaces may be excluded, and only the processing cost of the first sub-data space {commodity category, sales time, sales location}, {commodity category, sales time}, {commodity category, sales location}, {sales time, sales location} is determined.

[0186] The data complexity of the projection of the original data in the first sub-data space may refer to the algorithm complexity of obtaining the target data (statistical data obtained based on the statistics of the original data) from the pre-processed data of the original data in the first sub-data space. Since the pre-processed data of the original data in the first sub-data space is obtained by pre-processing based on the projection, the data complexity can be estimated based on the projection. It can be understood that different types of data have different data complexities, such as the data complexity of floating-point type data is higher than the algorithm complexity of integer data. Therefore, in a possible embodiment, the data complexity of the projection of the original data in the first sub-data space can be determined according to the data type of the projection. If the data type of the projection is a floating-point type, the data complexity is determined to be a preset first complexity. If the data type of the projection is an integer type, the data complexity is determined to be a preset second algorithm complexity. The first complexity is higher than the second complexity.

[0187] It is understandable that the more data that needs to be processed, the higher the algorithm complexity tends to be. Therefore, in one possible embodiment, the data complexity of the projection may be determined based on the cardinality of the original data in the first sub-data space, such as taking the cardinality of the projection as the data complexity. For another example, the cardinality of the projection is normalized, and the normalized result is taken as the data complexity. In another possible embodiment, the data complexity of the projection may also be determined based on the cardinality of the original data in the first sub-data space and the data type.

[0188] S403, for a plurality of first sub-data spaces that are not materialized sub-data spaces, determining a materialized benefit of the first sub-data space according to a 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 includes a root data space, and the materialized 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, the materialized sub-data space indicates the data space that has been determined to be the data space that needs to construct the corresponding aggregate table, and the non-materialized sub-data space indicates that the data space does not currently belong to the materialized sub-data space.

[0190] For multiple first sub-data spaces that are not materialized sub-data spaces, the materialized benefits of each first sub-data space can be determined for each first sub-data space among all the first sub-data spaces that are not materialized sub-data spaces of the root data space, or the materialized benefits of each first sub-data space in the partial first sub-data spaces can be determined for each first sub-data space among all the partial first sub-data spaces that are not materialized sub-data spaces of the root data space.

[0191] In a possible embodiment, the part of the first sub-data space may be randomly selected. In another possible embodiment, part of the first sub-data space may be excluded from the first sub-data space that is not a materialized sub-data space based on prior knowledge or actual needs, and the materialized benefits of each first sub-data space in the remaining other part of the first sub-data space are determined. For example, taking the first sub-data space that is not a materialized sub-data space as an example, including: {commodity category, sales time, sales location}, {commodity category, sales time}, {commodity category, sales location}, {sales time, sales location}, {commodity category}, {sales time}, {sales location}, based on prior knowledge, it is determined that the materialized benefits of the first sub-data space {commodity category}, {sales time}, {sales location} cannot meet the preset materialized benefit conditions, then the three first data spaces may be excluded, and only the materialized benefits of the first sub-data space {commodity category, sales time, sales location}, {commodity category, sales time}, {commodity category, sales location}, {sales time, sales location} are determined.

[0192] The materialized benefit of the first sub-data space can be reflected as: the computational amount of the processing cost between the computational amount corresponding to obtaining the target data based on other materialized sub-data spaces when the first sub-data space is not materialized and the computational amount corresponding to obtaining the target data based on the materialized first sub-data space when the user has materialized the first sub-data space.

[0193] Exemplarily, 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}, the materialized data space includes: root data space {A, B, C}, data space {A, B}. At present, it is necessary to determine whether the data space {A} is worth materializing, and it is necessary to determine the materialization benefit of the data space {A}. Since in this example, the preprocessed data in the data space {A} can only be processed to obtain the target data in the data space {A}, the materialization benefit of the data space {A} is:

[0194] The materialized benefit of data space {A} = the amount of calculation to obtain the target data in data space {A} based on data space {A, B} - the amount of calculation to obtain the target data in 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 can be seen that the lower the processing cost of the first sub-data space in this example, the higher the corresponding materialized benefit. Similarly, in other examples, the lower the processing cost of the first sub-data space, the higher the corresponding materialized benefit. That is, the materialized benefit of the first sub-data space is negatively correlated with the processing cost of the first sub-data space.

[0195] Since the first materialized sub-data spaces are correlated with each other, illustratively, the data space of {commodity category} can be regarded as a sub-data space of the data space of {commodity category, sales time}. The processing cost of the first sub-data space that is not a materialized sub-data space will be affected by the processing cost of other materialized first sub-data spaces, wherein the processing cost of the 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 of {commodity category, sales time} is low, then the processing difficulty of determining the {commodity category} or {sales time} of its sub-data space based on {commodity category, sales time} is also correspondingly low, that is, the processing cost is correspondingly low.

[0196] Exemplarily, based on the processing cost of each first sub-data space, the materialized benefit of each first sub-data space can be determined in combination with a greedy algorithm, and then the first sub-data space with the largest materialized benefit is materialized to obtain the optimal data cube tree.

[0197] Among them, the execution logic of the greedy algorithm can be 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] Among them, S is the set of sub-data spaces that need to be materialized in the end, v is each first sub-data space, and B (v, S) is the materialized benefit of constructing the first sub-data space v.

[0204] S404, determining a first sub-data space whose materialized benefits meet preset materialized benefits conditions as a new materialized sub-data space;

[0205] Determining the first sub-data space whose materialized benefits meet the preset materialized benefits conditions as the new materialized sub-data space may be to sort the materialized benefits of the first sub-data spaces in descending order, and to select the first sub-data spaces with materialized benefits ranked higher by a preset number as the new materialized sub-data space. Alternatively, sorting the materialized benefits in descending order, and determining the first sub-data spaces with materialized benefits ranked lower by a preset number as the new materialized sub-data space.

[0206] S405 , for each materialized sub-data space, performing aggregation processing on the projection of the original data in the materialized sub-data space to obtain an aggregation table corresponding to the materialized sub-data space.

[0207] After the materialized sub-data space is determined, the projection of the original data in the materialized sub-data space is aggregated, that is, the real original data is projected into the materialized sub-data space according to each dimension of the materialized sub-data space, and then the projected data is aggregated according to statistical methods such as summation and averaging to obtain the aggregation table corresponding to each materialized sub-space.

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

[0209] It can be understood that by determining the first sub-data space whose materialized benefits meet the preset materialized benefits conditions as the materialized sub-data space, the generation of the aggregation table corresponding to the materialized sub-data space with lower materialized benefits can be reduced, thereby saving system resources. And because the materialized benefits of the materialized sub-data space meet the preset materialized benefits conditions, the projection of the original data in the materialized sub-data space is aggregated to obtain the aggregation table corresponding to the materialized sub-data space. When the data stored in the aggregation table is subsequently used for query, the processing cost of the query can be reduced as much as possible, that is, the efficiency of obtaining the data to be queried based on the data in the aggregation table is fully improved.

[0210] It can be seen that through this solution, the data query efficiency can be further improved.

[0211] Corresponding to the above method embodiment, the present application embodiment 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 above data query methods;

[0213] The backend query engine 620 is used to receive the first statistical query statement sent by the intermediate query engine 610, and query and count the original data managed by itself according to the specified statistical range, specified statistical method and specified measurement indicated by the first statistical query statement to obtain query results.

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

[0215] The back-end 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 the data stored in the shared storage space.

[0216] It should be noted that the functions implemented by the intermediate query engine and the back-end 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 embodiment, the present application embodiment also provides a data query device, such as Figure 7 As shown, the device comprises:

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

[0219] The second determination module 720 is used to determine whether there is a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, the statistical metric is the specified metric, and the statistical method is the specified statistical method, if there is no aggregate table containing the data to be queried in the pre-stored aggregate tables, based on the hierarchical structure between the pre-stored dimension tables; wherein the hierarchical structure between the dimension tables represents the belonging relationship between the statistical ranges recorded in the dimension tables; an aggregate table records the metric value obtained by performing statistics in different statistical ranges according to a statistical method on at least one metric in the fact table;

[0220] The first query module 730 is used to determine, based on the hierarchical structure, the statistical ranges recorded in the target aggregate table that belong to the specified statistical range if the target aggregate table exists in each aggregate table, obtain the metric values ​​of the specified metric corresponding to the determined statistical ranges from the target aggregate table, and obtain the query results based on the obtained metric values ​​and the specified statistical method.

[0221] Optionally, the device is applied to an intermediate query engine, and the intermediate query engine is communicatively connected with each back-end query engine;

[0222] The device also includes:

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

[0224] The second determining module 720 is specifically configured to:

[0225] In the case where the preset keyword is carried in the first statistical query statement, 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 metric is the specified metric, and whose statistical method is the specified statistical method;

[0226] The device also includes:

[0227] A third determination module is used to determine, when the first statistical query statement does not carry the preset keyword, a target backend query engine that stores a fact table containing the specified metric from each backend query engine based on the recorded metadata tables of 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;

[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 queries and counts the original data managed by itself according to the specified statistical range, the specified statistical method and the specified measurement to obtain the query result.

[0229] Optionally, the device further comprises:

[0230] A fourth determination module is used to determine, if the target aggregate table does not exist in each aggregate table, a target backend query engine storing a 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 original data managed by the 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 queries and counts the original data managed by itself according to the specified statistical range, the specified statistical method and the specified measurement to obtain the query result.

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

[0233] The second determining module 720 includes:

[0234] A rewriting 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 indicate: determining each statistical range recorded in the target aggregation table stored in the shared storage space and belonging to the specified statistical range, 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;

[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 various backend query engines according to a predetermined selection rule.

[0236] Optionally, each aggregate table is constructed in the following ways:

[0237] Determine each statistically identifiable dimension corresponding to the original data recorded in the pre-stored fact table as the root data space;

[0238] 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 in the first sub-data space; wherein the processing cost is positively correlated with the data complexity;

[0239] For a plurality of the first sub-data spaces that are not materialized sub-data spaces, determining the materialized benefit of the first sub-data space according to the processing cost of the first sub-data space and the processing costs of each materialized sub-data space; wherein the materialized sub-data space initially includes the root data space, and the materialized 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] Determine the first sub-data space whose materialized benefit satisfies a preset materialized benefit condition as a new materialized sub-data space;

[0241] For each of the materialized sub-data spaces, aggregation processing is performed on the projection of the original data in the materialized sub-data space to obtain an aggregation table corresponding to the materialized sub-data space.

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

[0243] The present application also provides an electronic device, such as Figure 8 As shown, including:

[0244] Memory 801, used for storing computer programs;

[0245] The processor 802 is used to implement the steps of any of the above data query methods when executing the program stored in the memory 801.

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

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

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

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

[0250] The above-mentioned processor can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field programmable gate array (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components.

[0251] In another embodiment provided in the present application, a computer-readable storage medium is provided, in which a computer program is stored. When the computer program is executed by a processor, the steps of any of the above-mentioned data query methods are implemented.

[0252] In another embodiment provided in the present application, a computer program product including instructions is also provided, which, when executed on a computer, enables the computer to execute any of the data query methods in the above embodiments.

[0253] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented using software, it can be implemented in whole or in part 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, the process or function described in the embodiment of the present application is generated in whole or in part. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions may be stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium. For example, the computer instructions may be transmitted from a website site, computer, server or data center to another website site, computer, server or data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) means. The computer-readable storage medium may be any available medium that a computer can access or a data storage device such as a server or data center that includes one or more available media integrated. The available medium may be a magnetic medium (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a solid-state hard disk (SSD), etc.

[0254] It should be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.

[0255] Each embodiment in this specification is described in a related manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the system, device, electronic device, and computer-readable storage medium embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiments.

[0256] The above description is only a preferred embodiment of the present application and is not intended to limit the protection scope of the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application are included in the protection scope of the present application.

Claims

1. A data query method, characterized in that: The method comprises: In response to receiving a first statistical query statement for the data to be queried, determining a specified statistical range, a specified statistical method, and a specified metric indicated by the first statistical query statement; 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, the statistical metric is the specified metric, and the statistical method is the specified statistical method; wherein the hierarchical structure between the dimension tables represents the belonging relationship between the statistical ranges recorded in the dimension tables; an aggregate table records at least one metric in the fact table obtained by performing statistics in different statistical ranges according to a statistical method; If the target aggregate table exists in each aggregate table, then based on the hierarchical structure, the statistical ranges recorded in the target aggregate table that belong to the specified statistical range are determined, and the metric values ​​of the specified metrics corresponding to the determined statistical ranges are obtained from the target aggregate table, and the query results are obtained based on the obtained metric values ​​and the specified statistical method.

2. The method according to claim 1, characterized in that The method is applied to an intermediate query engine, which is communicatively connected with each back-end query engine; The method further comprises: In response to receiving the first statistical query statement, detecting whether the first statistical query statement carries a preset keyword; 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, determining whether there is a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, the statistical metric is the specified metric, and the statistical method is the specified statistical method, including: In the case where the preset keyword is carried in the first statistical query statement, 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 metric is the specified metric, and whose statistical method is the specified statistical method; The method further comprises: In the case where the preset keyword is not carried in the first statistical query statement, based on the recorded metadata tables of each 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 performs statistics on the original data managed by itself according to the specified statistical range, the specified statistical method and the specified metric to obtain a query result.

3. The method according to claim 2, characterized in that The method further comprises: If the target aggregate table does not exist in each aggregate table, based on the recorded metadata tables 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 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 performs statistics on the original data managed by itself according to the specified statistical range, the specified statistical method and the specified metric to obtain a query result.

4. The method according to claim 2, characterized in that: Each aggregate table is stored in a shared storage space of the intermediate query engine and each back-end query engine; The step of determining each statistical range recorded in the target aggregate table and belonging to the specified statistical range based on the hierarchical structure, obtaining the metric value of the specified metric corresponding to each determined statistical range from the target aggregate table, and obtaining the query result based on the obtained metric value and the specified statistical method, includes: 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 recorded in the target aggregation table stored in the shared storage space and belonging to the specified statistical range, 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; Execute the second statistical query statement; or, send the second statistical query statement to a designated backend query engine so that the designated backend query engine executes the second statistical query statement; wherein the designated backend query engine is selected from the backend query engines according to a predetermined selection rule.

5. The method according to any one of claims 1 to 4, characterized in that: The construction methods of each aggregate table include: Determine each statistically identifiable dimension corresponding to the original data recorded in the pre-stored fact table as the 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 in the first sub-data space; wherein the processing cost is positively correlated with the data complexity; For a plurality of the first sub-data spaces that are not materialized sub-data spaces, determining the materialized benefit of the first sub-data space according to the processing cost of the first sub-data space and the processing costs of each materialized sub-data space; wherein the materialized sub-data space initially includes the root data space, and the materialized 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; Determine the first sub-data space whose materialized benefit satisfies a preset materialized benefit condition as a new materialized sub-data space; For each of the materialized sub-data spaces, aggregation processing is performed on the projection of the original data in the materialized sub-data space to obtain an aggregation table corresponding to the materialized sub-data space.

6. A data query system, characterized in that: Including intermediate query engine and backend query engine; The intermediate query engine is used to execute the method according to any one of claims 1 to 5; The backend query engine is used to receive the first statistical query statement sent by the intermediate query engine, and query and count the original data managed by itself according to the specified statistical range, specified statistical method and specified measurement indicated by the first statistical query statement to obtain query results.

7. The system according to claim 6, characterized in that The shared storage space of the intermediate query engine and the backend query engine stores various aggregation tables; The back-end 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 the data stored in the shared storage space.

8. A data query device, characterized in that: The device comprises: A first determining module, configured to determine, in response to receiving a first statistical query statement for the data to be queried, a specified statistical range, a specified statistical method, and a specified metric indicated by the first statistical query statement; The second determination module is used to determine whether there is a target aggregate table in each aggregate table whose statistical range belongs to the specified statistical range, the statistical metric is the specified metric, and the statistical method is the specified statistical method, if there is no aggregate table containing the data to be queried in the pre-stored aggregate tables, based on the hierarchical structure between the pre-stored dimension tables; wherein the hierarchical structure between the dimension tables represents the belonging relationship between the statistical ranges recorded in the dimension tables; an aggregate table records the metric value obtained by performing statistics in different statistical ranges according to a statistical method on at least one metric in the fact table; The first query module is used to determine, based on the hierarchical structure, the statistical ranges recorded in the target aggregate table that belong to the specified statistical range if the target aggregate table exists in each aggregate table, obtain the measurement values ​​of the specified measurement corresponding to the determined statistical ranges from the target aggregate table, and obtain the query results based on the obtained measurement values ​​and the specified statistical method.

9. An electronic device, characterized in that: include: Memory, used to store computer programs; A processor, for implementing any of the methods described in claims 1-5 when executing a program stored in a memory.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 5 is implemented.

Images