Data query method, device, computer equipment and storage medium

Abstract

The embodiment of the present application discloses a data query method, device, computer equipment and storage medium. The method includes: obtaining a DSL query request sent by a user terminal, the DSL query request carries an indicator set and a dimension set; performing metadata parsing processing on each query indicator, determining the metric and indicator filtering condition corresponding to each query indicator; grouping metrics with the same indicator filtering condition into one group to obtain at least one metric grouping; generating a logical wide table corresponding to each metric grouping according to the mapping relationship between the metric and the physical table; generating an SQL query request for each metric grouping according to the preset SQL conversion logic, the indicator filtering condition corresponding to the metric grouping and the query dimension; executing the corresponding SQL query request on each logical wide table, obtaining a data query result, and sending the data query result to the user terminal. By implementing the method of the embodiment of the present application, the performance and usability of the data query service can be improved.

Description

Technical Field

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

[0002] When providing data analysis services online, in order to ensure the convenience and high performance of queries, different application layer data tables are built for query services of different business types. Query services of different business types often involve queries on the same data in the same physical table, resulting in the same data in the same physical table being copied to multiple different application layer data tables. This will lead to repeated copying of data in the physical table, large consumption of data processing resources, and difficulty in physical table management, which affects data query performance. In addition, existing data queries require users to query using Structured Query Language (SQL) statements, resulting in poor usability of data query services.

[0003] There is an urgent need for a method to improve the performance and usability of data query services. Summary of the invention

[0004] The embodiments of the present application provide a data query method, apparatus, computer equipment, and storage medium, which can improve the performance and usability of data query services.

[0005] In a first aspect, an embodiment of the present application provides a data query method, which includes:

[0006] Acquire a DSL query request sent by a user terminal, where the DSL query request carries an indicator set and a dimension set, where the indicator set includes at least one query indicator, and the dimension set includes at least one query dimension;

[0007] Perform metadata parsing on each of the query indicators to determine the metrics and indicator filtering conditions corresponding to each query indicator;

[0008] Grouping the metrics with the same indicator filtering condition into one group to obtain at least one metric group;

[0009] Generate a logical wide table corresponding to each of the metric groups according to a preset mapping relationship between the metric and the physical table;

[0010] For each of the metric groups, a SQL query request is generated according to a preset SQL conversion logic, an indicator filtering condition corresponding to the metric group, and the query dimension;

[0011] Execute the corresponding SQL query request on each of the logical wide tables respectively, obtain data query results, and send the data query results to the user terminal;

[0012] In a second aspect, the embodiment of the present application further provides a data query device, which includes:

[0013] A transceiver unit, configured to obtain a DSL query request sent by a user terminal, wherein the DSL query request carries an indicator set and a dimension set, wherein the indicator set includes at least one query indicator, and the dimension set includes at least one query dimension;

[0014] A processing unit is used to perform metadata parsing processing on each of the query indicators, determine the metrics and indicator filtering conditions corresponding to each query indicator; group the metrics with the same indicator filtering conditions into one group to obtain at least one metric group; generate a logical wide table corresponding to each of the metric groups according to a preset mapping relationship between the metrics and the physical table; generate an SQL query request for each of the metric groups according to a preset SQL conversion logic, the indicator filtering conditions corresponding to the metric group, and the query dimension; execute the corresponding SQL query request on each of the logical wide tables to obtain a data query result, and send the data query result to the user terminal through the transceiver unit;

[0015] In a third aspect, an embodiment of the present application further provides a computer device, which includes a memory and a processor, wherein a computer program is stored in the memory, and the processor implements the above method when executing the computer program;

[0016] In a fourth aspect, an embodiment of the present application further provides a computer-readable storage medium, wherein the storage medium stores a computer program, wherein the computer program includes program instructions, and wherein the program instructions can implement the above method when executed by a processor.

[0017] The embodiment of the present application provides a data query method, device, computer equipment and storage medium. The method includes: obtaining a DSL query request sent by a user terminal, the DSL query request carries an indicator set and a dimension set, the indicator set includes at least one query indicator, and the dimension set includes at least one query dimension; performing metadata parsing processing on each query indicator to determine the metric and indicator filtering condition corresponding to each query indicator; grouping the metrics with the same indicator filtering condition into one group to obtain at least one metric group; generating a logical wide table corresponding to each metric group according to a preset mapping relationship between the metric and the physical table; for each metric group, generating an SQL query request according to a preset SQL conversion logic, the indicator filtering condition corresponding to the metric group and the query dimension; executing the corresponding SQL query request on each logical wide table to obtain a data query result, and sending the data query result to the user terminal. The embodiment of the present application improves the performance of the data query service by constructing a logical wide table for data query without repeatedly copying the physical table; in addition, the user of the embodiment of the present application can directly query data through DSL, which improves the ease of use of the data query service compared to querying using SQL. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

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

[0020] Figure 2 A schematic diagram of a sub-process of the data query method provided in an embodiment of the present application;

[0021] Figure 3 Another sub-process diagram of the data query method provided in an embodiment of the present application;

[0022] Figure 4 A schematic block diagram of a data query device provided in an embodiment of the present application;

[0023] Figure 5 A schematic block diagram of a computer device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0024] 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 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 without creative work are within the scope of protection of this application.

[0025] It should be understood that when used in this specification and the appended claims, the terms "include" and "comprises" indicate the presence of described features, integers, steps, operations, elements and / or components, but do not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or combinations thereof.

[0026] It should also be understood that the terms used in this application specification are only for the purpose of describing specific embodiments and are not intended to limit the application. As used in this application specification and the appended claims, unless the context clearly indicates otherwise, the singular forms "a", "an" and "the" are intended to include plural forms.

[0027] It should be further understood that the term “and / or” used in the specification and appended claims refers to any and all possible combinations of one or more of the associated listed items, and includes these combinations.

[0028] Embodiments of the present application provide a data query method, apparatus, computer equipment, and storage medium.

[0029] The executor of the data query method may be the data query device provided in the embodiment of the present application, or a data query system integrating the data query device, wherein the data query system is integrated in a computer device, wherein the data query device may be implemented in hardware or software, the computer device may be a terminal or a server, and the terminal may be a smart phone, a tablet computer, a PDA, or a laptop computer, etc.

[0030] The data query method provided in the embodiment of the present application is described in detail below with the data query system as the execution subject.

[0031] First, you need to build a metadata wide table, which contains the global public dimensions and global public metrics of the data query system, and multiple physical tables (physical tables used to provide data query) are mapped to the corresponding public dimensions and corresponding public metrics, that is, the metadata wide table maintains the mapping relationship between dimensions and physical tables, as well as the mapping relationship between metrics and physical tables. For example, the metadata wide table contains public metrics M1, M2, and M3 (such as payment amount, independent visitors, and number of sales), and public dimensions D1, D2, and D3 (such as time, store logo, and payment channel); physical table A contains m1, d1, and d2, then m1, d1, and d2 in physical table A are mapped to M1, D1, and D2 respectively; physical table B contains m1, d1, d2, and d3, then m1, d1, d2, and d3 in physical table B are mapped to M1, D1, D2, and D3 respectively.

[0032] In addition, it is necessary to define indicators in advance, wherein the indicators are defined based on common dimensions and common metrics, specifically, a correspondence between preset indicators and indicator calibers, in which, for atomic indicators, the set indicator caliber includes corresponding metrics; for derived indicators, the set indicator caliber includes metrics and indicator filtering conditions; for composite indicators, the composite indicators include atomic indicators and / or derived indicators, and the set indicator caliber includes the indicator caliber corresponding to the atomic indicators and / or the derived indicators and the indicator calculation expression, wherein the above metrics belong to common metrics, and the indicator filtering conditions belong to common dimensions.

[0033] For example, configure the measurement of the atomic indicator pay_amt (payment amount) as pay_amount (payment amount), and configure the operator corresponding to the measurement as sum (sum); configure the atomic indicators referenced by the derived indicators, and configure the filtering conditions of the public dimensions, such as defining the derived indicator ali_pay_amt (Alipay channel payment amount) to reference the atomic indicator pay_amt, configuring the public dimension channel (channel), and the indicator filtering condition is channel='ali'; configure the atomic indicators and / or derived indicators referenced by the composite indicators, as well as the indicator calculation expression; for example, define the composite indicator ali_pay_rate (Alipay channel payment amount share), and its indicator calculation expression is ali_pay_amt / pay_amt.

[0034] In the embodiment of the present application, the indicator definition is independent of the specific physical table, and the indicator definition is globally unified through logical indicator definition.

[0035] In order to improve the query efficiency of the system, in this embodiment, for the materialized view of the physical table that has been entered into the table, the mapping relationship of the physical table points to the corresponding fogged view; specifically, when mapping the physical table, first determine whether the physical table is a materialized view of the table that has been entered into the system. If so, configure the materialized view relationship of the physical table. For example, if there is a physical table log table and the log table has been entered into the system, log_view is the materialized view of the log of the physical table, and there is a materialized view relationship: select if(event='display', 1,0) as display_pv, if(event='click', 1,0) asclick_pv from log, and enter the logic into the system; if the physical table is not a materialized view of the table that has been entered into the system, map the physical table and the public fields (public dimensions and public metrics);

[0036] In addition, it is necessary to determine whether the physical table contains only partial data; if it contains only partial data, use SQL fragments to configure the data range of the physical table. For example, the log_display table only contains data with event='display', and event='display' is entered into the system;

[0037] In addition, it is also necessary to determine whether the physical table has necessary dimensions; if it contains necessary dimensions, configure the mandatory public dimensions of the physical table. For example, in the ump_activity table, the dimensions are shop_id, activity_id and the public dimensions are shop_id, activity_id respectively. The necessary dimensions are the public dimensions shop_id, activity_id.

[0038] Among them, the physical table can still be selected when the general dimension query does not include the dimension, and the necessary dimension means that the physical table can only be selected when the query includes the dimension; for example, physical table A has dimensions: d1 and d2 are mapped to public dimensions D1 and D2 respectively, and d2 is the necessary dimension of the physical table. When the query dimensions are D1 and D2, physical table A can be selected; when the query dimension is D2, physical table A can be selected; and when the query dimension is D1, physical table A cannot be selected.

[0039] Figure 1 Schematic diagram of the data query method provided in the embodiment of the present application. Figure 1 As shown, the method includes the following steps S110-S160.

[0040] S110: Acquire a DSL query request sent by a user terminal, where the DSL query request carries an indicator set and a dimension set, where the indicator set includes at least one query indicator, and the dimension set includes at least one query dimension.

[0041] In this embodiment, when a user needs to perform a data query, the user accesses the data query system through a user terminal. The data query system provides a unified query interface, which provides multiple query indicators and multiple query dimensions for selection. The user only needs to select the query indicator and query dimension currently required to be queried, and click the "Query" control. The system can obtain a domain-specific language (DSL) query request sent by the user terminal, wherein the DSL query request carries an indicator set and a dimension set, wherein the indicator set includes at least one query indicator, and the dimension set includes at least one query dimension; the query indicator in the indicator set is the query indicator selected by the user based on the query interface, and the query dimension in the dimension set is the query dimension selected by the user based on the query interface.

[0042] In some embodiments, the query interface further provides multiple query filter conditions based on the common dimension. If the user selects a query filter condition, the DSL query request also carries the query filter condition selected by the user.

[0043] It can be seen that the query input parameter of this application is the indicator granularity, which can ensure the consistency of indicator caliber and the unified reuse and readability of service entrances.

[0044] S120: Perform metadata parsing on each of the query indicators to determine the metrics and indicator filtering conditions corresponding to each query indicator.

[0045] Specifically, for each of the query indicators, the target indicator caliber corresponding to the query indicator is determined according to the correspondence between the preset indicators and the indicator caliber; wherein, when the query indicator is an atomic indicator, the target indicator caliber includes a metric; when the query indicator is a derived indicator, the target indicator caliber includes a metric and an indicator filtering condition; when the query indicator is a composite indicator, the composite indicator includes an atomic indicator and / or a derived indicator, and the target indicator caliber includes the indicator caliber corresponding to the atomic indicator and / or the derived indicator.

[0046] Since the present embodiment predefines the indicators, the present embodiment can perform metadata parsing processing on the query indicators based on the pre-set indicator definitions, thereby parsing out the metrics and indicator filtering conditions of each query indicator, wherein, for atomic indicators, the corresponding indicator filtering conditions are empty.

[0047] The metric corresponding to the query indicator is the public metric in the metadata wide table.

[0048] S130: Group the metrics with the same indicator filtering condition into one group to obtain at least one metric group.

[0049] In this embodiment, after metadata parsing is performed on the query indicator in the DSL query request to obtain multiple metrics, metrics with the same indicator filtering conditions are grouped together according to the indicator filtering conditions corresponding to each metric, and at least one metric grouping is obtained. When there are multiple indicator filtering conditions, there are also multiple metric groupings, and when there is only one indicator filtering condition, there is also only one metric grouping.

[0050] For example, the query indicators in the DSL query request include pay_amount, ali_pay_rate, and uv (unique visitors). After parsing pay_amount, the metric pay_amt is obtained, and there is no indicator filtering condition. After parsing ali_pay_rate, the metric pay_amt is obtained, and the corresponding indicator filtering condition is channel='ali'. After parsing uv, the metric uv is obtained, and there is no indicator filtering condition.

[0051] At this point, the metrics are divided into Group A and Group B, where:

[0052] Group A:

[0053] Metrics: uv, pay_amt;

[0054] Dimension: None;

[0055] Filter conditions: None;

[0056] Group B:

[0057] Metric: pay_amt;

[0058] Dimension: None;

[0059] Metric filter condition: channel='ali'.

[0060] S140: Generate a logical wide table corresponding to each of the metric groups according to a preset mapping relationship between the metric and the physical table.

[0061] See also Figure 2 Specifically, in some embodiments, step S140 includes:

[0062] S1401. For each of the metric groups, add the conditional dimension corresponding to the indicator filtering condition to the dimension set.

[0063] For example, if the dimension set of the DSL query request contains the query filter condition: shop_id='123'; the indicator filter condition of the above group B is: channel='ali', so for group B, the updated dimension set is: shop_id='123' and channel='ali'; and for group A, since there is no corresponding indicator filter condition, the updated dimension set is still shop_id='123'.

[0064] S1402: For each metric in the metric group, determine multiple candidate physical tables corresponding to the metric according to a preset mapping relationship between the metric and the physical table, and obtain a candidate physical table set.

[0065] For example, for the measurement uv in group A, physical table B: shop_id, uv and physical table C: shop_id, pay_amount, uv are screened out. At this time, the candidate physical table set corresponding to the measurement uv includes physical table B and physical table C.

[0066] S1403: Eliminate candidate physical tables that do not contain data corresponding to the index filtering condition in the candidate physical table set, and candidate physical tables whose necessary dimensions are not included in the dimension set.

[0067] For example, if the indicator filtering condition is channel='ali', and a candidate physical table in the candidate physical table set does not include this dimension, then the candidate physical table is eliminated; if the physical table necessary dimension of a candidate physical table is channel, and this necessary dimension is not included in the queried dimension set, then the candidate physical table is eliminated.

[0068] S1404: Sort the candidate physical tables in the candidate physical table set according to a preset physical table sorting logic, and use the candidate physical table ranked first as the query physical table corresponding to the metric.

[0069] If after step S1403, the candidate physical table set still includes multiple candidate physical tables, the candidate physical tables in the candidate physical table set are sorted according to a preset physical table sorting logic, and the candidate physical table ranked first is used as the query physical table corresponding to the metric.

[0070] Specifically, the physical table sorting logic includes priority sorting, true subset sorting and data range sorting in order. Specifically, if priority configuration is performed, sorting is performed according to the priority. For example, if the manually configured priority of physical table A is higher than that of physical table B, then the priority of physical table A is higher than that of physical table B, and physical table A is ranked before physical table B. If no manual priority configuration is performed, or the priorities are the same, true subset sorting is performed. For example, if the dimension of physical table A is a true subset of the dimension of physical table B, then the priority of physical table A is higher than that of physical table B. If physical table A and physical table B do not have a true subset relationship, data range sorting is performed. For example, if the data range of physical table A is less than that of physical table B, then the priority of physical table A is higher than that of physical table B. Finally, the candidate physical table ranked first is obtained as the query physical table corresponding to the metric, until the query physical tables corresponding to all metrics in the metric group are obtained. The query physical tables corresponding to all metrics in the group are the query physical tables corresponding to the metric group.

[0071] S1405: Construct a logical wide table of the metric grouping according to the query physical tables corresponding to the metrics in the metric grouping.

[0072] Specifically, the metrics and metadata of the physical table are mapped into a logical wide table. The metadata of the logical wide table is the metric and dimension set of the query. The mapping logic is to uniquely map the metric to the selected query physical table, and map the dimensions of each physical table to the dimensions of the logical wide table. The name of the logical wide table is logic_wide_table.

[0073] For example, the physical tables selected for query in group A include: physical table A, shop_id, pay_amount and physical table B: shop_id, uv, which are assembled into logical wide tables: shop_id (corresponding to physical table A: shop_id, physical table B: shop_id), pay_amount (corresponding to physical table A: pay_amount), uv (corresponding to physical table B: uv). The physical tables selected for query in group B include physical table A: shop_id, pay_amount; which are assembled into logical wide tables: shop_id (corresponding to physical table A: shop_id), pay_amount (corresponding to physical table A: pay_amount).

[0074] It can be seen that through this embodiment, the DSL query request can be parsed, and the physical table can be automatically selected based on the common metrics and common dimensions, so that the physical table selection is automated, the logical wide table assembly is automated, the reusability of the physical table is improved, and the complexity of query implementation is reduced.

[0075] S150: For each of the metric groups, generate an SQL query request according to a preset SQL conversion logic, an indicator filtering condition corresponding to the metric group, and the query dimension.

[0076] Specifically, in some embodiments, step S150 includes: obtaining the operators corresponding to each metric in the metric group according to the corresponding relationship between the preset metrics and the operators; generating the SQL query request according to the SQL conversion logic, the operators corresponding to each metric, the indicator filtering conditions and the query dimension.

[0077] Furthermore, the DSL query request also carries a query filtering condition; step S150 includes: generating the SQL query request according to the SQL conversion logic, the operators corresponding to each metric, the indicator filtering condition, the query filtering condition and the query dimension.

[0078] Furthermore, the DSL query request also carries paging information; step S150 includes: generating the SQL query request according to the SQL conversion logic, the operators corresponding to each metric, the indicator filtering condition, the query filtering condition, the paging information and the query dimension.

[0079] The paging information includes at least one of a sorting index, a sorting method, a paging size, and a paging page number.

[0080] Specifically, the specific method of generating a SQL query request is:

[0081] Combine the grouped metrics with operators to form query fields and add them to the select of the query SQL; add the dimensions in DSL to the select; set the query SQL table to logic_wide_table; combine the indicator filter conditions in the group with the query filter conditions in DSL and add them to where; add the dimensions in DSL to group by; determine whether the group is a paging query; if so, add the sorting and paging information to the query SQL to generate an SQL query request.

[0082] For example, the SQL query request generated for the above group A is: select sum(pay_amount),count(distinct uv) from logic_table where shop_id='123'; the SQL query request generated for the above group B is: select sum(pay_amount) from logic_table where shop_id='123' and channel='ali'.

[0083] S160: Execute the corresponding SQL query request on each of the logical wide tables to obtain a data query result, and send the data query result to the user terminal.

[0084] Wherein, when the query indicator is a composite indicator, the target indicator scope also includes the indicator calculation expression; please refer to Figure 3 At this time, the step of executing the corresponding SQL query request on each of the logical wide tables in step S160 to obtain the data query result includes:

[0085] S1601, executing the corresponding SQL query request on each of the logical wide tables respectively, to obtain the group query results corresponding to each of the metric groups respectively;

[0086] S1602, performing dimension merging processing on the plurality of grouped query results according to the query dimension to obtain a dimension merging result;

[0087] S1603. Calculate the dimension merging result based on the indicator calculation expression to obtain the data query result.

[0088] In this embodiment, if there is no indicator calculation expression (that is, when the query indicator is an atomic indicator or a derived indicator), there is no need to execute step S1603, and the data query results can be output by executing steps S1601 and S1602. If there is an indicator calculation expression (that is, when the query indicator is a composite indicator), S1603 must be executed and further calculations must be performed to obtain the data query results.

[0089] Among them, when performing metadata parsing processing on the query indicator, if the indicator filtering conditions of the indicator referenced by the composite indicator are different, a memory calculation expression is generated for the calculation expression of the composite indicator, an alias is generated for the referenced indicator, and the memory calculation expression is changed to a reference to the alias.

[0090] For example, for the composite indicator ali_pay_rate, the indicator calculation expression is ali_pay_amt / pay_amt. The filter condition of ali_pay_amt is channel='ali', while pay_amt has no filter condition. Therefore, ali_pay_amt is divided into the group with the filter condition of channel='ali' and generates the alias tmp_index_01. Pay_amt is divided into the group without the filter condition and generates the alias tmp_index_02. At this time, the memory calculation expression is changed to: tmp_index_01 / tmp_index_02;

[0091] For example, the query indicators in the DSL query request are ali_pay_rate and pay_amt, and the query dimension is date. According to the above logic, data query will be divided into two groups, each group corresponding to a logical wide table.

[0092] Group A: does not contain the indicator filter condition, and the query metric is sum(pay_amount) as tmp_index_02; Group B: the indicator filter condition is channel='ali', and the query metric is sum(pay_amount) as tmp_index_01

[0093] The query results for group A are:

[0094] date:20241001,tmp_index_02:100;

[0095] date:20241002,tmp_index_02:100;

[0096] The query results for group B are:

[0097] date:20241001,tmp_index_01:10;

[0098] date:20241002,tmp_index_01:20;

[0099] Merge data by date:

[0100] The combined result is:

[0101] date:20241001,tmp_index_01:10,tmp_index_02:100;

[0102] date:20241002,tmp_index_01:20,tmp_index_02:100;

[0103] For composite indicators, the expression tmp_index_01 / tmp_index_02 is executed after merging to output the final query result.

[0104] In summary, the present embodiment obtains a DSL query request sent by a user terminal, the DSL query request carries an indicator set and a dimension set, the indicator set includes at least one query indicator, and the dimension set includes at least one query dimension; performs metadata parsing on each query indicator to determine the metrics and indicator filtering conditions corresponding to each query indicator; groups metrics with the same indicator filtering conditions into one group to obtain at least one metric group; generates a logical wide table corresponding to each metric group according to a preset mapping relationship between the metric and the physical table; generates an SQL query request for each metric group according to a preset SQL conversion logic, the indicator filtering conditions corresponding to the metric group, and the query dimension; executes the corresponding SQL query request on each logical wide table to obtain a data query result, and sends the data query result to the user terminal. The present embodiment of the application improves the performance of the data query service by constructing a logical wide table for data query without repeatedly copying the physical table; in addition, the user of the present embodiment of the application can directly query data through DSL, which improves the ease of use of the data query service compared to querying using SQL.

[0105] Figure 4 is a schematic block diagram of a data query device provided in an embodiment of the present application. Figure 4 As shown, corresponding to the above data query method, the present application also provides a data query device 400. The data query device 400 includes a unit for executing the above data query method, and the data query device 400 can be configured in a desktop computer, a tablet computer, a laptop computer, etc. Specifically, please refer to Figure 4 The data query device 400 includes a transceiver unit 401 and a processing unit 402 .

[0106] The transceiver unit 401 is configured to obtain a DSL query request sent by a user terminal, wherein the DSL query request carries an indicator set and a dimension set, wherein the indicator set includes at least one query indicator, and the dimension set includes at least one query dimension;

[0107] The processing unit 402 is used to perform metadata parsing on each of the query indicators to determine the metrics and indicator filtering conditions corresponding to each query indicator; group the metrics with the same indicator filtering conditions into one group to obtain at least one metric grouping; generate logical wide tables corresponding to each of the metric groups according to a preset mapping relationship between the metrics and the physical table; generate an SQL query request for each of the metric groups according to a preset SQL conversion logic, the indicator filtering conditions corresponding to the metric grouping, and the query dimension; execute the corresponding SQL query request on each of the logical wide tables to obtain a data query result, and send the data query result to the user terminal through the transceiver unit 401.

[0108] In some embodiments, when the processing unit 402 performs the step of performing metadata parsing processing on each query indicator to determine the metric and indicator filtering condition corresponding to each query indicator, it is specifically used to:

[0109] For each of the query indicators, according to the correspondence between the preset indicators and the indicator calibers, determine the target indicator caliber corresponding to the query indicator;

[0110] Among them, when the query indicator is an atomic indicator, the target indicator caliber includes measurement; when the query indicator is a derived indicator, the target indicator caliber includes measurement and indicator filtering conditions; when the query indicator is a composite indicator, the composite indicator includes atomic indicators and / or derived indicators, and the target indicator caliber includes the indicator caliber corresponding to the atomic indicator and / or the derived indicator.

[0111] In some embodiments, when the query indicator is a composite indicator, the target indicator scope also includes the indicator calculation expression; when the processing unit 402 executes the step of executing the corresponding SQL query request on each of the logical wide tables to obtain the data query result, it is specifically used to:

[0112] Execute the corresponding SQL query request on each of the logical wide tables to obtain the group query results corresponding to each of the metric groups; perform dimension merging processing on multiple group query results according to the query dimension to obtain the dimension merging result; calculate the dimension merging result based on the indicator calculation expression to obtain the data query result.

[0113] In some embodiments, when the processing unit 402 executes the step of generating the logical wide table corresponding to each of the metric groups according to the preset mapping relationship between the metric and the physical table, it is specifically used to:

[0114] For each of the metric groups, the conditional dimension corresponding to the indicator filtering condition is added to the dimension set; for each metric in the metric group, multiple candidate physical tables corresponding to the metric are determined according to a preset mapping relationship between the metric and the physical table to obtain a candidate physical table set; the candidate physical tables in the candidate physical table set that do not contain data corresponding to the indicator filtering condition and the candidate physical tables whose necessary dimensions of the physical table are not included in the dimension set are eliminated; the candidate physical tables in the candidate physical table set are sorted according to a preset physical table sorting logic, and the candidate physical table ranked first is used as the query physical table corresponding to the metric; and a logical wide table of the metric group is constructed according to the query physical tables corresponding to each metric in the metric group.

[0115] In some embodiments, when the processing unit 402 executes the step of generating an SQL query request according to the preset SQL conversion logic, the indicator filtering condition corresponding to the metric grouping, and the query dimension, it is specifically used to:

[0116] According to the correspondence between the preset metrics and operators, the operators corresponding to the metrics in the metric group are obtained; and the SQL query request is generated according to the SQL conversion logic, the operators corresponding to the metrics, the indicator filtering conditions and the query dimension.

[0117] In some embodiments, the DSL query request further carries a query filtering condition; when the processing unit 402 executes the step of generating the SQL query request according to the SQL conversion logic, the operators corresponding to each metric, the indicator filtering condition and the query dimension, it is specifically used to:

[0118] The SQL query request is generated according to the SQL conversion logic, the operators corresponding to the metrics, the indicator filtering condition, the query filtering condition, and the query dimension.

[0119] In some embodiments, the DSL query request also carries paging information; when the processing unit 402 executes the step of generating the SQL query request according to the SQL conversion logic, the operators corresponding to each metric, the indicator filtering condition, the query filtering condition and the query dimension, it is specifically used to:

[0120] The SQL query request is generated according to the SQL conversion logic, the operators corresponding to the metrics, the indicator filtering conditions, the query filtering conditions, the paging information, and the query dimension.

[0121] In summary, the data query device 400 provided in the embodiment of the present application performs data query by constructing a logical wide table, without the need to repeatedly copy the physical table, thereby improving the performance of the data query service; in addition, the user of the embodiment of the present application can directly perform data query through DSL, which improves the ease of use of the data query service compared to using SQL for querying.

[0122] It should be noted that those skilled in the art can clearly understand that the specific implementation process of the above-mentioned data query device and each unit can refer to the corresponding description in the aforementioned method embodiment, and for the convenience and brevity of description, it will not be repeated here.

[0123] The above data query device can be implemented in the form of a computer program. The computer program can be Figure 5 Runs on the computer device shown.

[0124] See also Figure 5 , Figure 5 5 is a schematic block diagram of a computer device provided in an embodiment of the present application. The computer device 500 may be a terminal or a server, wherein the terminal may be an electronic device with communication functions such as a smart phone, a tablet computer, a laptop computer, a desktop computer, a personal digital assistant, and a wearable device. The server may be an independent server or a server cluster composed of multiple servers.

[0125] See also Figure 5 The computer device 500 includes a processor 502 , a memory and a network interface 505 connected via a system bus 501 , wherein the memory may include a non-volatile storage medium 503 and an internal memory 504 .

[0126] The non-volatile storage medium 503 can store an operating system 5031 and a computer program 5032. The computer program 5032 includes program instructions, and when the program instructions are executed, the processor 502 can execute a data query method.

[0127] The processor 502 is used to provide computing and control capabilities to support the operation of the entire computer device 500 .

[0128] The internal memory 504 provides an environment for the operation of the computer program 5032 in the non-volatile storage medium 503. When the computer program 5032 is executed by the processor 502, the processor 502 can execute a data query method.

[0129] The network interface 505 is used to communicate with other devices over the network. Figure 5The structure shown in the figure is merely a block diagram of a portion of the structure related to the solution of the present application, and does not constitute a limitation on the computer device 500 to which the solution of the present application is applied. The specific computer device 500 may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0130] The processor 502 is used to run the computer program 5032 stored in the memory to implement the following steps:

[0131] Acquire a DSL query request sent by a user terminal, where the DSL query request carries an indicator set and a dimension set, where the indicator set includes at least one query indicator, and the dimension set includes at least one query dimension;

[0132] Perform metadata parsing on each of the query indicators to determine the metrics and indicator filtering conditions corresponding to each query indicator;

[0133] Grouping the metrics with the same indicator filtering condition into one group to obtain at least one metric group;

[0134] Generate a logical wide table corresponding to each of the metric groups according to a preset mapping relationship between the metric and the physical table;

[0135] For each of the metric groups, a SQL query request is generated according to a preset SQL conversion logic, an indicator filtering condition corresponding to the metric group, and the query dimension;

[0136] The corresponding SQL query request is executed on each of the logical wide tables to obtain a data query result, and the data query result is sent to the user terminal.

[0137] It should be understood that in the embodiment of the present application, the processor 502 may be a central processing unit (CPU), and the processor 502 may also be other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. Among them, the general-purpose processor may be a microprocessor or the processor may also be any conventional processor, etc.

[0138] Those of ordinary skill in the art can understand that all or part of the processes in the methods of implementing the above embodiments can be completed by instructing relevant hardware through a computer program. This computer program includes program instructions, and the computer program can be stored in a storage medium, which is a computer-readable storage medium. The program instructions are executed by at least one processor in the computer system to implement the process steps of the above method embodiments.

[0139] Therefore, the present application also provides a storage medium. The storage medium can be a computer-readable storage medium. The storage medium stores a computer program, where the computer program includes program instructions. When the program instructions are executed by a processor, the processor performs the following steps:

[0140] Obtain a DSL query request sent by a user terminal, where the DSL query request carries an index set and a dimension set, the index set includes at least one query index, and the dimension set includes at least one query dimension;

[0141] Perform metadata parsing processing on each of the query indexes to determine the metric and index filtering conditions respectively corresponding to each query index;

[0142] Group the metrics with the same index filtering conditions into one group to obtain at least one metric group;

[0143] Generate a logical wide table respectively corresponding to each of the metric groups according to a preset mapping relationship between metrics and physical tables;

[0144] For each of the metric groups, generate an SQL query request according to a preset SQL conversion logic, the index filtering conditions corresponding to the metric group, and the query dimension;

[0145] Execute the corresponding SQL query request on each of the logical wide tables to obtain a data query result, and send the data query result to the user terminal.

[0146] The storage medium can be various computer-readable storage media such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a magnetic disk, or an optical disc that can store program codes.

[0147] Those of ordinary skill in the art can realize that the units and algorithm steps of the examples described in combination with the embodiments disclosed herein can be implemented by electronic hardware, computer software, or a combination of the two. To clearly illustrate the interchangeability of hardware and software, the composition and steps of the examples have been generally described according to functions in the above description. Whether these functions are executed in a hardware or software manner depends on the specific application and design constraints of the technical solution. Professional technicians can use different methods to implement the described functions for each specific application, but such implementation should not be considered to exceed the scope of this application.

[0148] In several embodiments provided by this application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are merely illustrative. For example, the division of each unit is only a logical function division, and there may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed.

[0149] The steps in the method embodiments of this application can be adjusted, combined, and deleted according to actual needs. The units in the device embodiments of this application can be combined, divided, and deleted according to actual needs. In addition, the functional units in each embodiment of this application can be integrated into a processing unit, or each unit can exist physically alone, or two or more units can be integrated into one unit.

[0150] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to enable a computer device (which can be a personal computer, a terminal, or a network device, etc.) to execute all or part of the steps of the methods described in each embodiment of this application.

[0151] As described above, the above is only the specific implementation manner of this application, but the protection scope of this application is not limited thereto. Any person skilled in the art within the technical scope disclosed by this application can easily think of various equivalent modifications or substitutions, and these modifications or substitutions should all be covered within the protection scope of this application. Therefore, the protection scope of this application should be subject to the protection scope of the claims.

Claims

1. A data query method, characterized in that: include: Acquire a DSL query request sent by a user terminal, where the DSL query request carries an indicator set and a dimension set, where the indicator set includes at least one query indicator, and the dimension set includes at least one query dimension; Performing metadata parsing processing on each of the query indicators respectively, determining the metrics and indicator filtering conditions corresponding to each query indicator respectively, wherein the metrics corresponding to the query indicators are public metrics in the metadata wide table, and the metadata wide table contains the global public dimensions and global public metrics of the data query system, and maintains the mapping relationship between the dimensions and the physical table, and the mapping relationship between the metrics and the physical table; Grouping the metrics with the same indicator filtering condition into one group to obtain at least one metric group; Generate a logical wide table corresponding to each of the metric groups according to a preset mapping relationship between the metric and the physical table; For each of the metric groups, a SQL query request is generated according to a preset SQL conversion logic, an indicator filtering condition corresponding to the metric group, and the query dimension; Execute the corresponding SQL query request on each of the logical wide tables respectively, obtain data query results, and send the data query results to the user terminal; The step of generating a logical wide table corresponding to each of the metric groups according to a mapping relationship between the preset metric and the physical table includes: For each of the metric groups, adding the conditional dimension corresponding to the indicator filtering condition to the dimension set; For each metric in the metric group, determine a plurality of candidate physical tables corresponding to the metric according to a preset mapping relationship between the metric and the physical table, and obtain a set of candidate physical tables; Eliminate candidate physical tables that do not contain data corresponding to the index filtering condition in the candidate physical table set, and candidate physical tables whose necessary dimensions are not included in the dimension set; Sorting the candidate physical tables in the candidate physical table set according to a preset physical table sorting logic, and taking the candidate physical table ranked first as the query physical table corresponding to the metric; A logical wide table of the metric grouping is constructed according to the query physical tables respectively corresponding to the metrics in the metric grouping.

2. The method according to claim 1, characterized in that The metadata parsing process is performed on each query indicator to determine the metric and indicator filtering condition corresponding to each query indicator, including: For each of the query indicators, according to the correspondence between the preset indicators and the indicator calibers, determine the target indicator caliber corresponding to the query indicator; Among them, when the query indicator is an atomic indicator, the target indicator caliber includes measurement; when the query indicator is a derived indicator, the target indicator caliber includes measurement and indicator filtering conditions; when the query indicator is a composite indicator, the composite indicator includes atomic indicators and / or derived indicators, and the target indicator caliber includes the indicator caliber corresponding to the atomic indicator and / or the derived indicator.

3. The method according to claim 2, characterized in that When the query indicator is a composite indicator, the target indicator scope further includes an indicator calculation expression; the corresponding SQL query request is executed on each of the logical wide tables to obtain a data query result, including: Execute the corresponding SQL query request on each of the logical wide tables to obtain the group query results corresponding to each of the metric groups; Performing dimension merging processing on the plurality of grouped query results according to the query dimension to obtain a dimension merging result; The dimension merging result is calculated based on the indicator calculation expression to obtain the data query result.

4. The method according to claim 1, characterized in that: The generating of the SQL query request according to the preset SQL conversion logic, the indicator filtering condition corresponding to the metric grouping, and the query dimension includes: According to the preset correspondence relationship between the metric and the operator, the operator corresponding to each metric in the metric group is obtained; The SQL query request is generated according to the SQL conversion logic, the operators corresponding to the metrics, the indicator filtering conditions, and the query dimensions.

5. The method according to claim 4, characterized in that The DSL query request also carries a query filtering condition; the generating the SQL query request according to the SQL conversion logic, the operators corresponding to each metric, the indicator filtering condition and the query dimension includes: The SQL query request is generated according to the SQL conversion logic, the operators corresponding to the metrics, the indicator filtering condition, the query filtering condition, and the query dimension.

6. The method according to claim 5, characterized in that The DSL query request also carries paging information; the generating the SQL query request according to the SQL conversion logic, the operators corresponding to each metric, the indicator filtering condition, the query filtering condition and the query dimension includes: The SQL query request is generated according to the SQL conversion logic, the operators corresponding to the metrics, the indicator filtering conditions, the query filtering conditions, the paging information, and the query dimension.

7. A data query device, characterized in that: include: A transceiver unit, configured to obtain a DSL query request sent by a user terminal, wherein the DSL query request carries an indicator set and a dimension set, wherein the indicator set includes at least one query indicator, and the dimension set includes at least one query dimension; A processing unit is used to perform metadata parsing processing on each of the query indicators, determine the metrics and indicator filtering conditions corresponding to each query indicator, wherein the metrics corresponding to the query indicators are public metrics in the metadata wide table, and the metadata wide table contains the global public dimensions and global public metrics of the data query system, and maintains the mapping relationship between the dimensions and the physical table, and the mapping relationship between the metrics and the physical table; grouping the metrics with the same indicator filtering conditions into one group to obtain at least one metric group; generating a logical wide table corresponding to each of the metric groups according to a preset mapping relationship between the metrics and the physical table; generating an SQL query request for each of the metric groups according to a preset SQL conversion logic, the indicator filtering condition corresponding to the metric group, and the query dimension; executing the corresponding SQL query request on each of the logical wide tables to obtain a data query result, and sending the data query result to the user terminal through the transceiver unit; When the processing unit executes the step of generating the logical wide table corresponding to each of the metric groups according to the preset mapping relationship between the metric and the physical table, it is specifically used to: For each of the metric groups, the conditional dimension corresponding to the indicator filtering condition is added to the dimension set; for each metric in the metric group, multiple candidate physical tables corresponding to the metric are determined according to a preset mapping relationship between the metric and the physical table to obtain a candidate physical table set; the candidate physical tables in the candidate physical table set that do not contain data corresponding to the indicator filtering condition and the candidate physical tables whose necessary dimensions of the physical table are not included in the dimension set are eliminated; the candidate physical tables in the candidate physical table set are sorted according to a preset physical table sorting logic, and the candidate physical table ranked first is used as the query physical table corresponding to the metric; and a logical wide table of the metric group is constructed according to the query physical tables corresponding to each metric in the metric group.

8. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein: When the processor executes the computer program, the data query method according to any one of claims 1 to 6 is implemented.

9. A storage medium, characterized in that: The storage medium stores a computer program, wherein the computer program includes program instructions, and when the program instructions are executed by a processor, the processor executes the data query method according to any one of claims 1 to 6.

Images