Methods, apparatus, equipment, and storage media for reducing computational load in data warehouses

Abstract

This application proposes a method, apparatus, device, and storage medium for reducing computational load in a data warehouse. The method includes: initiating the current round of offline computation tasks based on a detected trigger event; determining the computation method for each source table in the current round of offline computation tasks based on the changed business data in the source tables, all business data, and computation method determination rules; extracting the changed business data from the source tables determined to be for incremental computation and saving it as a corresponding new table; generating offline computation logic for offline computation based on the full computation logic, the target table, and task configuration information, wherein the target table includes the source table with the full computation method and the generated new table; and using the offline computation logic to perform computation on the data to be computed in the target table to obtain the computation result. This application reduces the amount of data computation in the data warehouse, reduces the waste of computing resources, speeds up computation, and saves storage resources.

Description

Technical Field

[0001] This application relates to the field of data warehouse technology, and in particular to a method, apparatus, device and storage medium for reducing computational load in a data warehouse. Background Technology

[0002] Offline computing is a big data computing technology that typically performs batch parallel computation based on a distributed file system. The data to be computed is determined before the computation begins and remains unchanged during the process. Offline computing usually involves large datasets, consuming additional storage space and taking a considerable amount of time—making it a space- and time-intensive computing method. However, offline computing can perform complex calculations with high stability and does not have strict time requirements, making it widely used in fields such as data warehouses with large datasets and high computational complexity.

[0003] In existing technologies, offline computing involves pulling all historical data daily for full computation, but over 95% of this full computation is repetitive. Furthermore, pulling full data at the source layer is slow and time-consuming. Another offline computing method is incremental extraction, but this also merges incremental and existing data into a full dataset before computation, which also consumes considerable time. In fact, the changed incremental data in each offline computation only represents a small portion of the total full dataset. Calculating unchanged data amplifies unnecessary computation, wasting significant computing and storage resources. Moreover, offline computation is typically concentrated in a specific time period, requiring a large amount of computing resources to be allocated during that period to meet computational demands; while during other idle times, a large amount of resources remain idle, leading to an overabundance of computing resources. Summary of the Invention

[0004] To address the technical problems of time-consuming offline computation, high computational and storage resource consumption, and low resource utilization in existing technologies, this application provides a method, apparatus, device, and storage medium for reducing computational load in a data warehouse. Its main objective is to reduce the amount of data computation in the data warehouse, minimize waste of computational resources, ensure full utilization of computational resources, and conserve storage resources.

[0005] To achieve the above objectives, this application provides a method for reducing computational load in a data warehouse, the method comprising:

[0006] Start the current round of offline computing tasks based on the detected trigger events;

[0007] The calculation method for each source table in the current round of offline computing task is determined based on the changed business data in the source table, all business data, and the calculation method determination rules. The changed business data refers to the changed data extracted from the start of the previous round of offline computing task to the start of the current round of offline computing task, and the calculation method is either full calculation or incremental calculation.

[0008] Extract the changed business data from the source table that is determined to be used for incremental calculation and save it as a corresponding new table;

[0009] Based on the full calculation logic, the target table, and the task configuration information, an offline calculation logic for offline calculation is generated. The target table includes the source table with the calculation method of full calculation and the generated new table.

[0010] The offline calculation logic is used to calculate the data to be calculated in the target table to obtain the calculation results.

[0011] Furthermore, to achieve the above objectives, this application also provides an apparatus for reducing computational load in a data warehouse, the apparatus comprising:

[0012] The task startup module is used to start the current round of offline computing tasks based on the detected trigger events;

[0013] The calculation method determination module is used to determine the calculation method of each source table in the current round of offline calculation task based on the changed business data and all business data in the source table and the calculation method determination rules. The changed business data refers to the changed data extracted from the start of the previous round of offline calculation task to the start of the current round of offline calculation task, and the calculation method is full calculation or incremental calculation.

[0014] The first data extraction module is used to extract changed business data from the source table determined to be for incremental calculation and save it as a corresponding new table;

[0015] The logic generation module is used to generate offline computing logic for offline computing based on the full computing logic, the target table, and the task configuration information. The target table includes the source table with full computing as the computing method and the generated new table.

[0016] The first calculation module is used to perform calculations on the data to be calculated in the target table using offline calculation logic, and obtain the calculation results.

[0017] To achieve the above objectives, this application also provides a computer device, including a memory, a processor, and computer-readable instructions stored in the memory and executable on the processor, wherein the processor executes the computer-readable instructions to perform the steps of the method for reducing computational load in the data warehouse as described in any of the preceding claims.

[0018] To achieve the above objectives, this application also provides a computer-readable storage medium storing computer-readable instructions that, when executed by a processor, cause the processor to perform the steps of the method for reducing computational load in the data warehouse as described in any of the preceding claims.

[0019] The method, apparatus, equipment, and storage medium for reducing computational load in a data warehouse proposed in this application determine the data to be computed for full and incremental computation by defining the computation method of the source tables. Then, offline computation logic is generated based on the data to be computed and the full computation logic, and the offline computation logic is used to perform computations on the data to be computed. Because the source tables for full and incremental computations are distinguished, and incremental data from some source tables and full data from some original tables are extracted, full computation of all source tables can be reduced. This reduces unnecessary data computation in the data warehouse, lowers the computational load, speeds up computation, reduces waste of computing resources, makes full use of computing resources, reduces resource overload, improves resource utilization, and saves storage resources. Attached Figure Description

[0020] Figure 1 This is a flowchart illustrating a method for reducing computational load in a data warehouse according to an embodiment of this application;

[0021] Figure 2 This is a structural block diagram of a device for reducing computational load in a data warehouse according to an embodiment of this application;

[0022] Figure 3 This is a block diagram of the internal structure of a computer device according to an embodiment of this application.

[0023] The realization of the purpose, functional features and advantages of this application will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0024] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application. It should be understood that the specific embodiments described herein are merely illustrative of this application and are not intended to limit this application.

[0025] Figure 1 This is a flowchart illustrating a method for reducing computational load in a data warehouse according to one embodiment of this application. (Reference) Figure 1The methods for reducing computational load in this data warehouse include the following steps S100-S500.

[0026] S100: Start the current round of offline computing tasks based on the detected trigger event.

[0027] Specifically, the triggering event can be a scheduled event or a start command from the developer for the offline computing task. If the offline computing task is started automatically at a first preset interval, each round of offline computing will take a certain amount of time. The first preset interval is longer than the total duration of the offline computing task. The task framework for the offline computing task can be, for example, MapReduce, Spark batch processing, etc., and is not limited to these.

[0028] In one specific embodiment, if the currently running offline computing task is the first or first run after its creation, then all data is extracted from the source table and full computation is performed on the entire data. If the currently running offline computing task is not the first round of offline computing tasks, then steps S200-S500 are executed.

[0029] S200: Determine the calculation method for each source table in the current round of offline computing task based on the changed business data and all business data in the source table and the calculation method determination rules. The changed business data refers to the changed data extracted from the start of the previous round of offline computing task to the start of the current round of offline computing task. The calculation method is either full calculation or incremental calculation.

[0030] Specifically, the source table is the data table in the source layer of the data warehouse. The source layer is the first layer from the business database to the data warehouse. The table structure of the source layer is consistent with that of the business database. The raw data that has changed is directly extracted from the business database and saved to the source table without any task processing.

[0031] The changed business data in the source table includes data that has changed between two adjacent rounds of offline computing tasks. If the current round of offline computing tasks is started based on a scheduled task, then the changed business data in the source table includes the changed data extracted within the first preset time period before the start time of the current round of offline computing tasks. The changed business data includes newly added incremental data and modified data.

[0032] A data warehouse typically contains multiple source tables, each containing different amounts of changing business data. One source table can be either the primary table or a join table with the primary table. The calculation method for each source table is determined, specifying which tables use full calculations and which use incremental calculations. The calculation method for each source table is determined by comparing its changing business data with all the business data it contains. Generally, the more data in a source table, the more likely it is to be used for incremental calculations; when the data volume is small, full calculations may not take too long, so source tables with small data volumes can temporarily avoid using incremental calculations. Specifically, the calculation method can be determined by the ratio of changing business data to all business data across certain dimensions; alternatively, it can be determined directly based on the amount of business data contained in the source table.

[0033] The purpose of determining the calculation method is to determine whether a table is to be calculated in full or incrementally based on the changed business data of each table (main table and related tables) in the source table and all the business data of that table. If it is to be calculated incrementally, the changed data in that table is extracted and stored in a new table. If it is to be calculated in full, that table is used directly during the calculation.

[0034] S300: Extract the changed business data from the source table that is determined to be used for incremental calculation and save it as a corresponding new table.

[0035] Specifically, if the source table is determined to be used for full calculation, then all business data in the source table is treated as data to be calculated during the data calculation process; if the source table is determined to be used for incremental calculation, then the changed data in the source table is extracted as data to be calculated and saved as a new table. Each source table determined to be used for incremental calculation corresponds to a new table.

[0036] For example, if a source table has 50 fields, the changing business data corresponds to 10 fields involved in the calculation, and there is one field that is a join field, then these 11 fields and their corresponding values ​​are extracted to generate a new table. In this way, the data to be calculated changes from a large table with 50 joined fields to a small table with 11 joined fields. Converting a large table into a small table reduces the amount of calculation and speeds up the calculation process.

[0037] S400: Generates offline computing logic for offline computing based on the full computing logic, the target table, and the task configuration information. The target table includes the source table with full computing as the computing method and the newly generated table.

[0038] Specifically, the full-scale calculation logic used for full-scale calculation is parsed to obtain relevant calculation information, including the main table and its corresponding related tables, query fields, related fields, and aggregation grouping fields. Based on this relevant calculation information, task configuration information, the source table for full-scale calculation, and the newly generated table, offline calculation logic for offline calculation is generated. The task configuration information includes rules for determining the calculation method.

[0039] The primary table refers to the fact table that needs to be calculated, while the related table generally refers to a dimension table or other fact table. For example, in the SQL query `SELECT ta.field_a, tb.field_b FROM table_a ta LEFT JOIN table_b tb ON ta.join_field = tb.join_field`, the primary table is `table_a`, and the related table is `table_b`. The primary table is generally the master data, and the primary table and related tables are joined through the join fields. The query fields are the fields needed for this calculation, i.e., the fields corresponding to the data to be calculated, and need to be retrieved. The aggregation and grouping fields are the fields after `GROUP BY` in the SQL statement, which divide the data into multiple groups for calculations such as summation and counting within each group.

[0040] The full-scale computation logic is the original computational logic code used by the data warehouse itself to perform full computations on all business data. This embodiment generates offline computation logic based on the full-scale computation logic to perform calculations on the data in the target table. This allows computation to be performed only on a portion of the data in the source table, rather than on all data, thereby reducing unnecessary computations and lowering the computational load.

[0041] Relevant computational information can be saved as prerequisite relational data in the computational metadata, which also stores task configuration information and target tables.

[0042] S500: Utilizes offline calculation logic to perform calculations on the data to be calculated in the target table and obtains the calculation results.

[0043] Specifically, the generated offline computing logic is used to perform calculations on the business data in the source table (which is not used for full calculation) and the newly generated table, and the calculation results are obtained.

[0044] Once the calculations are complete, the new table can be deleted, which reduces the storage space it occupies.

[0045] This embodiment determines the data to be calculated for full and incremental calculations by defining the calculation method of the source table. Then, it generates offline calculation logic based on the data to be calculated and the full calculation logic, and uses the offline calculation logic to perform calculations on the data to be calculated. Because the source tables for full and incremental calculations are distinguished, and incremental data from some source tables and full data from some original tables are extracted, full calculations on all source tables can be reduced. This reduces unnecessary data calculations in the data warehouse, lowers the amount of data calculation, speeds up the calculation, reduces the waste of computing resources, makes full use of computing resources, reduces resource stacking, improves resource utilization, and saves storage resources.

[0046] In one embodiment, step S200 specifically includes:

[0047] For each source table, calculate the first statistical data of the changed business data in at least one statistical dimension, and the second statistical data of all business data in the corresponding source table in the same dimension. The source table is the main table or the related table.

[0048] Calculate the ratio of the first statistical data point to the second statistical data point of the same dimension in the same source table for each statistical dimension;

[0049] The calculation method is determined based on the calculation rules, and the calculation method of the corresponding source table in the current round of offline calculation task is determined according to the ratio and the corresponding first threshold.

[0050] Specifically, for any source table, the first statistical data of the changed business data in the source table is calculated in at least one statistical dimension, and the second statistical data of all business data in the source table in the same statistical dimension is also calculated. For the same source table, the ratio of the first statistical data to the second statistical data in the same statistical dimension is calculated. Each statistical dimension corresponds to a first threshold. The ratio of the source table in the same statistical dimension is compared with the first threshold to obtain the comparison result. Based on all the comparison results of the source table in all statistical dimensions, the calculation method of the source table in the current round of offline computing tasks is determined. The smaller the ratio, the more likely incremental calculation is needed, because a smaller ratio indicates a larger amount of business data in the source table. This embodiment combines the comparison results of at least one statistical dimension to comprehensively evaluate the calculation method of the source table, resulting in a more comprehensive and accurate judgment.

[0051] Other statistical dimensions may include one or more of the following: the number of data entries, the number of bytes occupied by the data, the number of fields contained, and the number of bytes occupied by the fields. This application is not limited to these.

[0052] Alternatively, you can directly determine whether to perform incremental or full calculation based on the statistical data of all business data in each source table, i.e., the data volume statistics. If the statistical data exceeds the corresponding data volume threshold, it is determined to be incremental calculation.

[0053] In one embodiment, the first statistical data of the changed business data in each source table is calculated in at least one statistical dimension, and the second statistical data of all business data in the corresponding source table is calculated in the same dimension, including statistics in at least one of the following dimensions:

[0054] The number of changed business data records in each source table is counted as the first statistical data of the corresponding source table in the first dimension, and the total number of all business data records contained in the corresponding source table is counted as the second statistical data of the corresponding source table in the first dimension.

[0055] The number of bytes occupied by the changed business data in each source table is counted as the first statistical data of the corresponding source table in the second dimension. The total number of bytes occupied by all business data contained in the corresponding source table is counted as the second statistical data of the corresponding source table in the second dimension.

[0056] The number of calculated fields contained in the changed business data of each source table is counted as the first statistical data of the corresponding source table in the third dimension. The total number of fields contained in all the business data of the corresponding source table is counted as the second statistical data of the corresponding source table in the third dimension.

[0057] The number of bytes occupied by the fields involved in the calculation in each source table is counted as the first statistical data of the corresponding source table in the fourth dimension, and the number of bytes occupied by all fields contained in the corresponding source table is counted as the second statistical data in the fourth dimension.

[0058] Specifically, the first dimension is the number of data entries, the second dimension is the number of bytes occupied by the data, the third dimension is the number of fields included, and the fourth dimension is the number of bytes occupied by each field. At least one of these three statistical dimensions can be selected for data analysis.

[0059] The calculation of the ratio of the first statistical data for each statistical dimension to the second statistical data for the same dimension in the same source table, as described above, specifically includes at least one of the following:

[0060] The ratio of the first statistical data in the first dimension to the second statistical data in the first dimension of the same source table is used as the first ratio of the corresponding source table.

[0061] The ratio of the first statistical data in the second dimension to the second statistical data in the second dimension of the same source table is used as the second ratio of the corresponding source table.

[0062] Calculate the ratio of the first statistical data in the third dimension to the second statistical data in the third dimension of the same source table as the third ratio of the corresponding source table;

[0063] The ratio of the first statistical data in the fourth dimension to the second statistical data in the fourth dimension of the same source table is used as the fourth ratio of the corresponding source table.

[0064] In addition, to avoid duplicate statistics in the next round of offline computing tasks, the second statistical data can be saved to the computing metadata. The next round of offline computing tasks only needs to collect the first statistical data of the changed business data, and then merge the first statistical data of this round with the second statistical data obtained in the previous round to obtain the second statistical data of this round. This can effectively avoid duplicate statistics and reduce the amount of computation.

[0065] In one embodiment, the rule is determined based on the calculation method, and the calculation method of the corresponding source table in the current round of offline calculation task is determined according to the ratio and the corresponding first threshold, including:

[0066] If a preset number of ratios corresponding to the same source table are less than the corresponding first threshold, then the corresponding source table is determined to be calculated as incrementally in the current round of offline computing tasks, wherein the preset number does not exceed the number of statistical dimensions.

[0067] Specifically, the preset quantity is configured according to the actual application scenario, and the preset quantity is any number between 1 and the total number of statistical dimensions.

[0068] If the preset quantity is the total number of statistical dimensions, then for a source table, all ratios must be less than the corresponding first threshold to determine that the source table meets the incremental computation execution condition in the current round of offline computation tasks. More specifically, if the ratios of all dimensions are less than the corresponding first threshold, then the computation method of the source table in the current round of offline computation tasks is determined to be incremental computation; if the ratio of any dimension is not less than the corresponding first threshold, then the computation method of the source table in the current round of offline computation tasks is determined to be full computation.

[0069] If the preset quantity is 1, then if any ratio of all ratios corresponding to the same source table is less than the corresponding first threshold, the source table is determined to meet the incremental calculation execution condition in the current round of offline computing tasks. More specifically, if the ratio of any dimension is less than the corresponding first threshold, the calculation method of the source table in the current round of offline computing tasks is determined to be incremental calculation; if the ratios of all dimensions are not less than the corresponding first threshold, the calculation method of the source table in the current round of offline computing tasks is determined to be full calculation.

[0070] The ratios of statistical data are more intuitive. Saving statistical data allows you to see the trend of data changes, and you can adjust the number of statistical dimensions and the first threshold based on the statistical data.

[0071] The first threshold and the preset quantity can both be obtained from the task configuration information.

[0072] In one embodiment, step S500 specifically includes:

[0073] Based on the grouping type and grouping order contained in the offline calculation logic, the data to be calculated in the target table is grouped to obtain multiple grouped data.

[0074] Calculations are performed on each group of data to obtain the results.

[0075] Specifically, the offline computing logic includes multi-table join calculations and / or aggregation calculations. Multi-table join calculations are also known as relational calculations. Grouping types include relational grouping and aggregation grouping. The grouping order is obtained from the task configuration information. Developers determine the granularity of the relational and aggregation groupings based on the values ​​of the relational fields and the distribution of values ​​in the aggregation grouping fields in the data warehouse, respectively, and configure the grouping order accordingly. The more granular the data grouping, the more likely it is to be the preferred grouping type.

[0076] For example, if the grouping type includes both related grouping and aggregate grouping, and the grouping order (priority) of related grouping is higher than that of aggregate grouping, then the data to be calculated in the target table is firstly grouped according to the values ​​of the association fields between the main table and the related tables, resulting in multiple first-group data. Then, all the first-group data is secondly grouped according to the values ​​of the aggregate grouping fields, resulting in multiple second-group data. The calculation is then performed on the second-group data to obtain the calculation result.

[0077] For example, in the SQL code: SELECT ta.field_a,tb.field_b FROM table_a ta LEFT JOIN table_b tb ON ta.join_field=tb.join_field, join_field is the join condition or join field; the data to be calculated is grouped according to the value of the join field.

[0078] For example, in the SQL code: SELECT field_a, COUNT(field_b) FROM table GROUP BY field_a, field_a is the aggregation condition or aggregation grouping field; the data to be calculated is grouped according to the value of the aggregation grouping field.

[0079] In related grouping, the grouping method is determined based on the relationship fields between the main table and each related table. Grouping methods include combined grouping and / or non-combined grouping. That is, the grouping method may include at least one non-combined grouping, at least one combined grouping, or at least one combined and non-combined grouping. Combined grouping refers to grouping data based on a combination of at least two relationship fields, while non-combined grouping refers to grouping data based on a single relationship field.

[0080] If the main table is linked to at least one related table through at least two different join fields, then the data to be calculated is grouped either in combination or without combination, starting with the group of join fields that have the most join fields, in descending order of the number of join fields in each related table and the main table. For example, if main table A and related table B have 5 first join fields, main table A and related table C have 4 second join fields, and main table A and related table D have 2 third join fields, then the data to be calculated is first grouped based on the 5 first join fields. These 5 first join fields can be used for either combined or non-combined grouping. Then, the data from the first group is grouped again based on the 4 second join fields. Finally, the data from the second group is grouped again based on the 2 third join fields. If any of the first, second, and third join fields share a common join field, that common join field is used only in one grouping.

[0081] For example, main table A is related to related table B through related fields f1 and f2, and main table A is related to related table C through related field f2. The data to be calculated contained in tables A, B, and C is grouped first through related field f2, and then the data in the first group is grouped secondly based on related field f1.

[0082] For example, main table A is related to table B through association fields f1 and f3, and main table A is related to table C through association field f2. The data to be calculated contained in tables A, B, and C is grouped as follows: first, the data is grouped using association field f1; then, the data from the first group is grouped again using association field f3; or, the data is grouped again using association field f3; or, the data is combined using a combination of association fields f1 and f3; then, the data from the second group or the combined group is grouped again using association field f2.

[0083] An aggregation grouping field can include multiple different values. For example, if the aggregation grouping field is a region, the region values ​​could be Beijing, Shanghai, Guangzhou, and Shenzhen.

[0084] In addition, this application can also determine whether to perform fine-grained partitioning based on the amount of data to be calculated. If the amount of data exceeds the data volume threshold, fine-grained partitioning is performed based on associated grouping and / or aggregated grouping; if the amount of data does not exceed the data volume threshold, overly fine-grained partitioning is not performed or partitioning is performed only based on some of the grouping types.

[0085] The calculations are performed on each group of data to obtain the results, including:

[0086] If there is no aggregated grouping, each group of data is calculated independently to obtain the sub-calculation result of each group of data. All sub-calculation results are combined to form the calculation result.

[0087] If there are associated groups and aggregate groups, the data to be calculated is first divided into multiple associated group data by associated grouping, and then each associated group data is aggregated to obtain multiple aggregate group data corresponding to each associated group data.

[0088] For each associated group of data, the calculation for multiple aggregated groups begins with the first aggregated group at the finest granularity, yielding the result for each associated group. The grouped data corresponding to the aggregated group constitutes the aggregate calculation, which typically includes calculations such as summation, counting, mean, maximum, and minimum.

[0089] The methods for data grouping and data calculation are not limited to those described above, and this application makes no restrictions on them.

[0090] In one embodiment, step S500 specifically includes:

[0091] Based on the grouping types and order contained in the offline computing logic, the data to be calculated in the target table is grouped starting from the grouping type with the highest priority.

[0092] If at least one target group exists among the multiple grouped data obtained after grouping by the current grouping type, and the data volume of the target group exceeds the corresponding second threshold, then the next grouping type in the grouping order is used to continue grouping the target group data;

[0093] If the target group data is not found in any of the multiple groups obtained after grouping by the current grouping type, or if all grouping types have been enabled, then grouping is stopped;

[0094] Calculations are performed on each final group of data obtained after stopping the grouping, and the calculation results are obtained.

[0095] Specifically, each grouping type should group the data as much as possible. If the grouping type executed first groups the data in a very fine-grained manner, that is, there is no large amount of data in the grouped data, then there is no need to execute the grouping corresponding to the subsequent grouping type.

[0096] More specifically, the data to be calculated in the target table is first grouped according to the grouping type that is at the first priority level in the grouping order to obtain multiple first groups;

[0097] If at least one of the first groups has a data volume exceeding the corresponding second threshold, then the first groups exceeding the second threshold are further subdivided according to the next priority grouping type. Other first groups that do not exceed the threshold do not need to be subdivided. This process continues until no group has a data volume exceeding the second threshold or all grouping types have been used. The second threshold can be obtained from the task configuration information.

[0098] This embodiment uses fine-grained partitioning for large datasets and avoids overly fine-grained partitioning for small datasets, which reduces computational overhead from data grouping and improves computational speed.

[0099] In one embodiment, prior to step S100, the method further includes:

[0100] Extract changed data from the business database;

[0101] Add the changed data to the corresponding source table or replace the corresponding historical data in the source table with the changed data. Identify the changed data in the source table or record the operation timestamp or version number.

[0102] Specifically, the extraction of changed data from the business database can be done in real time or offline after the current round of offline computing tasks is started. Steps S100-S500 are executed only after the offline extraction is completed.

[0103] Preferably, taking offline extraction as an example, after each offline computing task is started, before extracting data, if the changed data is marked by a tagging method, all tags are cleared first, and then the data is extracted from the business database into the source table. If the extracted changed data is newly added data, it is stored in the corresponding source table. If the extracted changed data is modified data, it can replace the historical data in the corresponding source table by overwriting the historical data. In this case, a tag indicating the change can be added to the changed data in the source table. In step S200, the changed business data in each source table can be identified based on the tag.

[0104] Alternatively, modified data can be stored in the corresponding source table using a non-overwrite storage method. In this case, the version number or operation timestamp of the changed data in the source table is recorded, or an identifier indicating the change is added to the modified and newly added data. In step S200, the changed business data in each source table is identified based on the operation timestamp, version number, or identifier.

[0105] This embodiment greatly facilitates the identification of changed business data by using identifiers, operation timestamps, or version numbers, enabling the extraction task to accurately capture the changed business data, ensuring the accuracy of the extracted data, and accelerating the data calculation speed.

[0106] In one embodiment, after obtaining the calculation result, the method further includes:

[0107] Store the calculation results of the current round of offline computing tasks in the incremental table;

[0108] or,

[0109] The calculation results of the current round of offline computing tasks are merged with the existing data in the source table to obtain the full data corresponding to the current round of offline computing tasks.

[0110] Specifically, the initial offline computation task saves the results of full computation of all business data in the data warehouse. Subsequent offline computation tasks save the computation results of the data to be computed in the target table within the current round of offline computation.

[0111] Alternatively, the calculation results obtained from each offline calculation task can be merged with the existing data from the previous round of offline calculation tasks to obtain the full data for the current round of offline calculation tasks.

[0112] Since all calculations except the first one are of incremental or modified business data, a full calculation is not required. If full data is needed, the data obtained from the offline calculation task can be merged with the existing data to obtain the full data.

[0113] In one embodiment, after obtaining the calculation result, the method further includes:

[0114] Calculate the third statistical data on resource consumption in the current round of offline computing tasks;

[0115] The computational efficiency of the current round of offline computing tasks is evaluated based on the third statistical data.

[0116] The task configuration information is adjusted based on the evaluation results. The task configuration information includes at least one of the following: a first threshold, a second threshold, a statistical dimension, a grouping order, and a calculation method determination rule.

[0117] Specifically, the system records the third statistical information of computing resource consumption and systematically evaluates the computational efficiency of offline computing tasks. This evaluation is used to adjust computation methods, determine rules, and set thresholds related to data preparation. Automatic adjustment of task configuration information through feedback optimizes the execution of offline computing tasks, making them more effective. Computing resources include the number of CPU cores, memory, disk I / O, network I / O, and runtime. The evaluation of computational efficiency involves normalizing and weighting the consumption of various resources during computation to calculate an index. Comparing this index with the resource consumption index corresponding to full computation assesses whether the offline computing task is more efficient. Simultaneously, parameters in the offline computing configuration information are adjusted based on the evaluation results, and the effectiveness of these adjustments is evaluated to aid in parameter optimization.

[0118] Offline computing tasks typically only compute changed data. However, if the values ​​of association or aggregation conditions change, they will rely on historical data. Changed data from the historical data will be removed, and a portion of the historical data will be recalculated; if the data has not changed, no calculation will be performed.

[0119] Specifically, if the association conditions change, i.e., the value of the association field changes, the first historical stock business data corresponding to the association field whose value has changed is retrieved. The first target business data corresponding to the removed value is removed from the first historical stock business data (i.e., removed from the group). The remaining first historical stock business data after removing the data is recalculated. Since the business data corresponding to the newly added value has already been calculated in the offline calculation task, only the calculation of the historical stock data needs to be updated.

[0120] If the aggregation conditions change, i.e., the value of the aggregation grouping field changes, then the second historical stock business data corresponding to the aggregation grouping field whose value has changed is retrieved. The second target business data corresponding to the removed value is then removed from the second historical stock business data (i.e., removed from the group). The remaining second historical stock business data after removing the data is recalculated. Since the business data corresponding to the newly added value has already been calculated in the offline calculation task, only the calculation of the historical stock data needs to be updated.

[0121] This application employs an offline computation optimization method to perform fine-grained data partitioning: partitioning based on incremental and existing data, grouping based on join conditions, and grouping based on aggregation conditions. This allows for the computation of only changing business data, avoiding the resource waste caused by full-scale computation, and particularly reducing the computational amplification waste resulting from multi-table joins. Furthermore, calculating only changing business data requires less time, thus improving data timeliness and mitigating the need for T+1 computations to some extent. When calculating changing business data, most tables only need to store incremental data, effectively saving storage resources.

[0122] It should be understood that the sequence number of each step in the above embodiments does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of this application.

[0123] Figure 2 This is a structural block diagram of a device for reducing computational load in a data warehouse according to an embodiment of this application. (Reference) Figure 2 The device includes:

[0124] The task initiation module 100 is used to start the current round of offline computing tasks based on the detected trigger events;

[0125] The calculation method determination module 200 is used to determine the calculation method of each source table in the current round of offline calculation task based on the changed business data and all business data in the source table and the calculation method determination rules. The changed business data is the changed data extracted from the start of the previous round of offline calculation task to the start of the current round of offline calculation task, and the calculation method is full calculation or incremental calculation.

[0126] The first data extraction module 300 is used to extract changed business data from the source table determined to be for incremental calculation and save it as a corresponding new table;

[0127] The logic generation module 400 is used to generate offline computing logic for offline computing based on the full computing logic, the target table and the task configuration information. The target table includes the source table with full computing as the computing method and the generated new table.

[0128] The first calculation module 500 is used to perform calculations on the data to be calculated in the target table using offline calculation logic, and obtain the calculation results.

[0129] In one embodiment, the calculation method determination module 200 specifically includes:

[0130] The statistics module is used to calculate the first statistical data of the changed business data in each source table in at least one statistical dimension, and the second statistical data of all business data in the corresponding source table in the same dimension. The source table is the main table or the related table.

[0131] The second calculation module is used to calculate the ratio of the first statistical data of each statistical dimension to the second statistical data of the same dimension in the same source table.

[0132] The determination module is used to determine the rules based on the calculation method, and to determine the calculation method of the corresponding source table in the current round of offline calculation task according to the ratio and the corresponding first threshold.

[0133] In one embodiment, the statistics module includes at least one of the following modules:

[0134] The first statistics module is used to count the number of changed business data records in each source table as the first statistical data of the corresponding source table in the first dimension, and to count the total number of all business data records contained in the corresponding source table as the second statistical data of the corresponding source table in the first dimension.

[0135] The second statistics module is used to count the number of bytes occupied by the changed business data in each source table as the first statistical data of the corresponding source table in the second dimension, and to count the total number of bytes occupied by all business data contained in the corresponding source table as the second statistical data of the corresponding source table in the second dimension.

[0136] The third statistics module is used to count the number of calculated fields contained in the changed business data of each source table as the first statistical data of the corresponding source table in the third dimension, and to count the total number of fields contained in all fields of all business data contained in the corresponding source table as the second statistical data of the corresponding source table in the third dimension.

[0137] The fourth statistics module is used to count the number of bytes occupied by the fields involved in the calculation in each source table as the first statistical data of the corresponding source table in the fourth dimension, and to count the number of bytes occupied by all fields contained in the corresponding source table as the second statistical data in the fourth dimension.

[0138] In one embodiment, the determination module is specifically used to determine that if a preset number of ratios among all ratios corresponding to the same source table are less than the corresponding first threshold, the calculation method of the corresponding source table in the current round of offline calculation task is incremental calculation, wherein the preset number does not exceed the number of statistical dimensions.

[0139] In one embodiment, the first computing module 500 specifically includes:

[0140] The first grouping module is used to group the data to be calculated in the target table according to the grouping type and grouping order contained in the offline calculation logic, so as to obtain multiple grouped data.

[0141] The first sub-calculation module is used to perform calculations on each group of data and obtain the calculation results.

[0142] In one embodiment, the first computing module 500 specifically includes:

[0143] The second grouping module is used to group the data to be calculated in the target table based on the grouping type and grouping order contained in the offline computing logic, starting from the first priority grouping type.

[0144] The continue grouping module is used to continue grouping the target group data if there is at least one target group data among the multiple group data obtained after grouping by the current grouping type, wherein the data volume of the target group data exceeds the corresponding second threshold.

[0145] The Stop Grouping module is used to stop grouping if the target group data is not found in the multiple groups of data obtained after grouping by the current grouping type, or if all grouping types have been enabled.

[0146] The second sub-calculation module is used to perform calculations on each final group of data obtained after stopping the grouping, and to obtain the calculation results.

[0147] In one embodiment, the device further includes:

[0148] The second data extraction module is used to extract changed data from the business database.

[0149] The first storage module is used to add changed data to the corresponding source table or replace the corresponding historical data in the corresponding source table with changed data, and to identify or record the operation timestamp or version number of the changed data in the source table.

[0150] In one embodiment, the device further includes:

[0151] The second storage module is used to save the calculation results of the current round of offline computing tasks in the incremental table;

[0152] or,

[0153] The data merging and storage module is used to merge the calculation results of the current round of offline computing tasks with the existing data in the source table to obtain the full data corresponding to the current round of offline computing tasks.

[0154] In one embodiment, the device further includes:

[0155] The third calculation module is used to calculate the third statistical data of resource consumption in the current round of offline calculation tasks;

[0156] The evaluation module is used to evaluate the computational efficiency of the current round of offline computing tasks based on third statistical data.

[0157] The adjustment module is used to adjust the task configuration information based on the obtained evaluation results. The task configuration information includes at least one of the following: a first threshold, a second threshold, a statistical dimension, a grouping order, and a calculation method determination rule.

[0158] The terms "first" and "second" in the above-mentioned modules / units are only used to distinguish different modules / units and are not intended to specify which module / unit has a higher priority or any other limiting meaning. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion. For example, a process, method, system, product, or device that includes a series of steps or modules is not necessarily limited to those steps or modules explicitly listed, but may include other steps or modules not explicitly listed or inherent to these processes, methods, products, or devices. The module divisions appearing in this application are merely logical divisions; in actual applications, different division methods may be used.

[0159] Specific limitations regarding the devices for reducing computational load in a data warehouse can be found in the limitations on methods for reducing computational load in a data warehouse described above, and will not be repeated here. The modules in the aforementioned devices for reducing computational load in a data warehouse can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in a computer device, or stored in the memory of a computer device as software, so that the processor can call and execute the operations corresponding to each module.

[0160] Figure 3 This is a block diagram of the internal structure of a computer device according to an embodiment of this application. Figure 3As shown, the computer device includes a processor, memory, network interface, input device, and display screen connected via a system bus. The processor provides computational and control capabilities. The memory includes storage media and internal memory. The storage media can be non-volatile or volatile. The storage media stores an operating system and may also store computer-readable instructions. When executed by the processor, these instructions enable the processor to implement methods for reducing computational load in a data warehouse. The internal memory provides an environment for the operation of the operating system and computer-readable instructions stored in the storage media. The internal memory may also store computer-readable instructions, which, when executed by the processor, enable the processor to execute methods for reducing computational load in a data warehouse. The network interface of the computer device is used for communication with an external server via a network connection. The display screen can be an LCD screen or an e-ink screen. The input device can be a touch layer covering the display screen, buttons, a trackball, or a touchpad located on the computer device's casing, or an external keyboard, touchpad, or mouse.

[0161] In one embodiment, a computer device is provided, including a memory, a processor, and computer-readable instructions (e.g., a computer program) stored in the memory and executable on the processor. When the processor executes the computer-readable instructions, it implements the steps of the method for reducing computational load in the data warehouse described in the above embodiments, for example... Figure 1 The steps S100 to S500 shown, as well as other extensions and related steps of the method, are examples. Alternatively, when the processor executes computer-readable instructions, it implements the functions of each module / unit of the device for reducing computational load in the data warehouse in the above embodiments, for example... Figure 2 The functions of modules 100 to 500 are shown. To avoid repetition, they will not be described again here.

[0162] A processor can be a Central Processing Unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. A general-purpose processor can be a microprocessor or any conventional processor. The processor is the control center of a computer device, connecting all parts of the computer device through various interfaces and lines.

[0163] Memory can be used to store computer-readable instructions and / or modules. The processor implements various functions of the computer device by running or executing the computer-readable instructions and / or modules stored in memory, and by accessing data stored in memory. Memory can mainly include a program storage area and a data storage area. The program storage area can store the operating system, application programs required for at least one function (such as sound playback function, image playback function, etc.), etc.; the data storage area can store data created based on the use of the mobile phone (such as audio data, video data, etc.).

[0164] The memory can be integrated into the processor or set up separately from the processor.

[0165] Those skilled in the art will understand that Figure 3 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0166] In one embodiment, a computer-readable storage medium is provided, on which computer-readable instructions are stored. When executed by a processor, these computer-readable instructions implement the steps of the method for reducing computational load in the data warehouse described in the above embodiments, for example... Figure 1 The steps S100 to S500 shown, as well as other extensions and related steps of the method, are examples. Alternatively, when executed by a processor, computer-readable instructions implement the functions of various modules / units of the computation-reducing device in the data warehouse of the above embodiments, for example... Figure 2 The functions of modules 100 to 500 are shown. To avoid repetition, they will not be described again here.

[0167] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by instructing related hardware through computer-readable instructions. These computer-readable instructions can be stored in a computer-readable storage medium, and when executed, they can include the processes of the embodiments of the above methods. Any references to memory, storage, databases, or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in various forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double-rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link DRAM (SLDRAM), RAMbus direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and RAMbus dynamic RAM (RDRAM), etc.

[0168] It should be noted that, in this document, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, apparatus, article, or method that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, apparatus, article, or method. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, apparatus, article, or method that includes that element.

[0169] The sequence numbers of the embodiments in this application are merely for description and do not represent the superiority or inferiority of the embodiments. Through the above description of the embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases, the former is a better implementation method. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) as described above, and includes several instructions to cause a terminal device (which may be a mobile phone, computer, server, or network device, etc.) to execute the methods described in the various embodiments of this application.

[0170] The above are merely preferred embodiments of this application and do not limit the patent scope of this application. Any equivalent structural or procedural transformations made using the content of this application's specification and drawings, or direct or indirect applications in other related technical fields, are similarly included within the patent protection scope of this application.

Claims

1. A method for reducing computational load in a data warehouse, characterized in that, The method includes: Start the current round of offline computing tasks based on the detected trigger events; The calculation method for each source table in the current round of offline computing task is determined based on the changed business data and all business data in the source table and the calculation method determination rules. The changed business data refers to the changed data extracted from the start of the previous round of offline computing task to the start of the current round of offline computing task. The calculation method is either full calculation or incremental calculation. Extract the changed business data from the source table that is determined to be used for incremental calculation and save it as a corresponding new table; Based on the full calculation logic, the target table, and the task configuration information, an offline calculation logic for offline calculation is generated. The target table includes the source table with the calculation method of full calculation and the generated new table. The offline calculation logic is used to calculate the data to be calculated in the target table to obtain the calculation result; The step of determining the calculation method for each source table in the current round of offline calculation task based on the changed business data in the source table, all business data, and calculation method determination rules includes: Each source table is used to calculate the first statistical data of the changed business data in at least one statistical dimension, and the second statistical data of all business data in the corresponding source table in the same dimension. The source table is either the main table or the related table. Calculate the ratio of the first statistical data point to the second statistical data point of the same dimension in the same source table for each statistical dimension; The calculation method is determined based on the calculation rule, and the calculation method of the corresponding source table in the current round of offline calculation task is determined according to the ratio and the corresponding first threshold.

2. The method according to claim 1, characterized in that, The first statistical data, which calculates the changed business data in each source table in at least one statistical dimension, and the second statistical data, which calculates all business data in the corresponding source table in the same dimension, includes statistics in at least one of the following dimensions: The number of changed business data entries in each source table is counted as the first statistical data of the corresponding source table in the first dimension, and the total number of all business data entries contained in the corresponding source table is counted as the second statistical data of the corresponding source table in the first dimension. The number of bytes occupied by the changed business data in each source table is counted as the first statistical data of the corresponding source table in the second dimension. The total number of bytes occupied by all business data contained in the corresponding source table is counted as the second statistical data of the corresponding source table in the second dimension. The number of calculated fields contained in the changed business data of each source table is counted as the first statistical data of the corresponding source table in the third dimension. The total number of fields contained in all the business data of the corresponding source table is counted as the second statistical data of the corresponding source table in the third dimension. The number of bytes occupied by the fields involved in the calculation in each source table is counted as the first statistical data of the corresponding source table in the fourth dimension, and the number of bytes occupied by all fields contained in the corresponding source table is counted as the second statistical data in the fourth dimension.

3. The method according to claim 1, characterized in that, The rule for determining the calculation method, which determines the calculation method of the corresponding source table in the current round of offline computing tasks based on the ratio and the corresponding first threshold, includes: If a preset number of ratios corresponding to the same source table are less than the corresponding first threshold, then the calculation method of the corresponding source table in the current round of offline calculation task is determined to be incremental calculation, wherein the preset number does not exceed the number of statistical dimensions.

4. The method according to claim 1, characterized in that, The step of using the offline calculation logic to calculate the data to be calculated in the target table to obtain the calculation result includes: Based on the grouping type and grouping order contained in the offline calculation logic, the data to be calculated in the target table is grouped to obtain multiple grouped data. The calculation is performed on each of the grouped data to obtain the calculation result.

5. The method according to claim 1, characterized in that, The step of using the offline calculation logic to calculate the data to be calculated in the target table to obtain the calculation result includes: Based on the grouping types and grouping order contained in the offline computing logic, the data to be calculated in the target table is grouped starting from the grouping type with the first priority level; If at least one target group exists among the multiple grouped data obtained after grouping by the current grouping type, and the data volume of the target group exceeds the corresponding second threshold, then the next grouping type in the grouping order is used to continue grouping the target group data; If the target group data is not found in any of the multiple groups obtained after grouping by the current grouping type, or if all grouping types have been enabled, then grouping is stopped; Calculations are performed on each final group of data obtained after stopping the grouping, and the calculation results are obtained.

6. The method according to claim 1, characterized in that, Before initiating the current round of offline computing tasks based on the detected triggering event, the method further includes: Extract changed data from the business database; Add the changed data to the corresponding source table or replace the corresponding historical data in the corresponding source table with the changed data, and mark or record the operation timestamp or version number of the changed data in the source table.

7. The method according to claim 1, characterized in that, After obtaining the calculation result, the method further includes: The calculation results of the current round of offline calculation tasks are stored in the incremental table; or, The calculation results of the current round of offline computing tasks are merged with the existing data in the source table to obtain the full data corresponding to the current round of offline computing tasks.

8. The method according to claim 1, characterized in that, After obtaining the calculation result, the method further includes: Calculate the third statistical data on resource consumption in the current round of offline computing tasks; The computational efficiency of the current round of offline computing tasks is evaluated based on the third statistical data. The task configuration information is adjusted based on the obtained evaluation results. The task configuration information includes at least one of the following: a first threshold, a second threshold, a statistical dimension, a grouping order, and a calculation method determination rule.

9. An apparatus for reducing computational load in a data warehouse, used to implement the method for reducing computational load in a data warehouse as described in any one of claims 1-8, characterized in that, The device includes: The task startup module is used to start the current round of offline computing tasks based on the detected trigger events; The calculation method determination module is used to determine the calculation method of each source table in the current round of offline calculation task based on the changed business data and all business data in the source table and the calculation method determination rules. The changed business data is the changed data extracted from the start of the previous round of offline calculation task to the start of the current round of offline calculation task. The calculation method is full calculation or incremental calculation. The first data extraction module is used to extract changed business data from the source table determined to be for incremental calculation and save it as a corresponding new table; The logic generation module is used to generate offline computing logic for offline computing based on the full computing logic, the target table, and the task configuration information. The target table includes the source table with full computing as the computing method and the generated new table. The first calculation module is used to perform calculations on the data to be calculated in the target table using the offline calculation logic to obtain the calculation results.

10. A computer device comprising a memory, a processor, and computer-readable instructions stored in the memory and executable on the processor, characterized in that, When the processor executes the computer-readable instructions, it performs the steps of the method for reducing computation in a data warehouse as described in any one of claims 1-8.

11. A computer-readable storage medium storing computer-readable instructions, characterized in that, When the computer-readable instructions are executed by a processor, the processor performs the steps of the method for reducing computational load in a data warehouse as described in any one of claims 1-8.

Images