Dimension table data storage method, device, computer equipment and storage medium

Abstract

The present invention discloses a dimension table data storage method, device, computer equipment and storage medium, which relates to the field of big data technology. The dimension table data storage method includes: comparing and processing the dimension table data of full partitions of two adjacent days to obtain incremental data; using preset rules to determine the full partitions to be retained, and deleting the full partitions that are not determined to be retained in the preset rules and have obtained incremental data; when restoring the dimension table data of the full partition of the target date, determining the target full partition; performing merging and deduplication processing based on the dimension table data of the target full partition, as well as the target date, the generation date corresponding to the target full partition, and the incremental data between the two dates, to obtain the dimension table data of the full partition of the target date. The use of this dimension table data storage method can significantly improve the rationality of storage resource utilization while ensuring that the dimension table data can be restored at any time.

Description

Technical field

[0001] The present invention relates to the field of big data technology in artificial intelligence, and in particular to a dimension table data storage method, device, computer equipment, and storage medium. [Background Technology]

[0002] In the information-exploding internet age, the importance of data is self-evident. Historical data tracing is essential for major internet companies, but currently, most companies store a daily full snapshot of changing dimension table data.

[0003] The current problem is that as the number of full partitions increases, the amount of data in the dimension tables of the full partitions will skyrocket, occupying a large amount of disk space and placing high demands on the device storage capacity. [Summary of the invention]

[0004] In view of this, an embodiment of the present invention provides a dimension table data storage method, apparatus, computer equipment and storage medium to solve the current problem of dimension table data occupying too much storage resources. This application not only belongs to the field of artificial intelligence, but also involves blockchain-related technologies.

[0005] In a first aspect, an embodiment of the present invention provides a dimension table data storage method, including:

[0006] Compare dimension table data of full partitions from two consecutive days to obtain incremental data.

[0007] Determine the full partitions to be retained using a preset rule, and delete the full partitions that are not retained according to the preset rule and for which the incremental data has been obtained;

[0008] When restoring dimension table data of a full partition on a target date, determining a target full partition, wherein the target full partition refers to a full partition closest to the target date and time;

[0009] Merge and deduplication processing is performed based on the dimension table data of the target full partition, the target date, the generation date corresponding to the target full partition, and the incremental data of the date between the target date and the generation date to obtain the dimension table data of the full partition of the target date.

[0010] According to the above aspects and any possible implementation, an implementation is further provided, wherein the incremental data includes deleted data, newly added data, and updated data, and the incremental data is obtained by comparing dimension table data of full partitions of two consecutive days, including:

[0011] Calculate the information summary value of the logical primary key of the dimension table data of the full partition for the two adjacent days respectively;

[0012] Performing a full outer join of the dimension table data of the full partitions of the two adjacent days according to the logical primary key;

[0013] Comparing the information summary values of the logical primary keys to obtain a comparison result;

[0014] If the comparison result shows that the information summary value of the second day of two consecutive days is empty, it is concluded that the deleted data is the dimension table data corresponding to the logical primary key;

[0015] If the comparison result shows that the information summary value of the previous day between two consecutive days is empty, it is concluded that the newly added data is the dimension table data corresponding to the logical primary key;

[0016] If the comparison result is that the information summary values of two consecutive days are different and not empty, it is concluded that the updated data is the dimension table data corresponding to the logical primary key;

[0017] According to the above aspects and any possible implementation, an implementation is further provided, wherein the incremental data includes deleted data, newly added data, and updated data, and the updated data includes dimension table data before the update and dimension table data after the update. When the generation date is before the target date, the dimension table data of the target full partition, the target date, the generation date corresponding to the target full partition, and the incremental data of the dates between the target date and the generation date are merged and deduplicated to obtain the dimension table data of the full partition of the target date, including:

[0018] Obtaining incremental data for dates between the target date and the generated date, as well as incremental data for the target date;

[0019] Remove the incremental data of the dates between the target date and the generation date, and the dimension table data before the update in the incremental data of the target date;

[0020] Merging the dimension table data of the target full partition and the incremental data after removing the dimension table data before the update, and deduplicating them based on the logical primary key to retain the dimension table data with the latest date;

[0021] The deleted data in the dimension table data of the latest date is removed to obtain the dimension table data of the full partition of the target date.

[0022] According to the above aspects and any possible implementation, an implementation is further provided, wherein the incremental data includes deleted data, newly added data, and updated data, and the updated data includes dimension table data before the update and dimension table data after the update. When the generation date is after the target date, the dimension table data of the target full partition, the target date, the generation date corresponding to the target full partition, and the incremental data of the dates between the target date and the generation date are merged and deduplicated to obtain the dimension table data of the full partition of the target date, including:

[0023] Obtaining incremental data for dates between the target date and the generation date, incremental data for the target date, and incremental data for the generation date;

[0024] Remove dimension table data before update from the incremental data of the target date;

[0025] Remove the newly added data and updated dimension table data in the incremental data of the generation date;

[0026] removing updated dimension table data from incremental data other than the incremental data on the target date and the incremental data on the generation date to obtain processed incremental data;

[0027] Merge the dimension table data of the target full partition and the processed incremental data, and deduplicate based on the logical primary key to retain the dimension table data with the oldest date;

[0028] In addition to the newly added data on the target date, the newly added data in the oldest dimension table data on the target date is removed to obtain the dimension table data of the full partition on the target date.

[0029] According to the above aspects and any possible implementation manner, a further implementation manner is provided, wherein after performing merging and deduplication processing on the dimension table data of the target full partition, the target date, the generation date corresponding to the target full partition, and the incremental data of the date between the target date and the generation date to obtain the dimension table data of the full partition of the target date, the method further includes:

[0030] Obtain the target dimension table data for two consecutive days, where the target dimension table data for two consecutive days is stored in a full partitioning manner, and the user's private information is stored in the blockchain;

[0031] When the target two adjacent days change, based on the dimension table data of the target two adjacent days, the dimension table data of the changed target two adjacent days is stored in a full partitioning manner, and a removal operation is performed on the full partitions except the full partitions determined to be retained using the preset rules.

[0032] In a second aspect, an embodiment of the present invention provides a dimension table data storage device, comprising:

[0033] The incremental data acquisition module is used to compare the dimension table data of the full partitions of two consecutive days to obtain incremental data;

[0034] A deletion module is used to determine the full partitions to be retained using a preset rule, and to delete the full partitions that are not retained according to the preset rule and for which the incremental data has been obtained;

[0035] a target full partition determination module, configured to determine a target full partition when restoring dimension table data of a full partition on a target date, wherein the target full partition refers to a full partition that is closest to the target date;

[0036] The target date data acquisition module is used to perform merging and deduplication processing based on the dimension table data of the target full partition, the target date, the generation date corresponding to the target full partition, and the incremental data of the date between the target date and the generation date, to obtain the dimension table data of the full partition of the target date.

[0037] In a third aspect, a computer device includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the steps of the above-mentioned dimension table data storage method when executing the computer program.

[0038] In a fourth aspect, an embodiment of the present invention provides a computer-readable storage medium, comprising: a computer program, wherein when the computer program is executed by a processor, the steps of the above-mentioned dimension table data storage method are implemented.

[0039] In an embodiment of the present invention, by comparing and processing the dimension table data of the full partitions of two adjacent days, incremental data is obtained, which provides a prerequisite for realizing appropriate deletion of full partitions and optimized storage of dimension table data; after obtaining incremental data, the full partitions to be retained can be determined using preset rules, and the full partitions that are not determined to be retained in the preset rules and for which incremental data has been obtained can be deleted, thereby clearing a large amount of storage space; when it is necessary to restore the dimension table data of the full partition of the target date, merging and deduplication processing can be performed based on the dimension table data of the target full partition, as well as the incremental data of the target date, the generation date corresponding to the target full partition, and the date between the target date and the generation date, to realize rapid recovery of dimension table data for any day. In an embodiment of the present invention, by optimizing the storage of dimension table data, the rationality of storage resource utilization can be significantly improved while ensuring that the dimension table data can be restored at any time.

Brief Description of the Drawings

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

[0041] Figure 1 This is a flow chart of a dimension table data storage method according to an embodiment of the present invention;

[0042] Figure 2 is a schematic diagram of a dimension table data storage device according to an embodiment of the present invention;

[0043] Figure 3 FIG. 1 is a schematic diagram of a computer device according to an embodiment of the present invention. [Specific implementation method]

[0044] In order to better understand the technical solution of the present invention, the embodiments of the present invention are described in detail below with reference to the accompanying drawings.

[0045] It should be understood that the embodiments described are only a portion of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by persons of ordinary skill in the art without creative work are within the scope of protection of the present invention.

[0046] The terms used in the embodiments of the present invention are only for the purpose of describing specific embodiments and are not intended to limit the present invention. The singular forms "a", "an", "the" and "the" used in the embodiments of the present invention and the appended claims are also intended to include plural forms unless the context clearly indicates otherwise.

[0047] It should be understood that the term "and / or" as used herein is simply a term used to describe the existence of three possible relationships between related objects. For example, "A and / or B" can represent the existence of A alone, the existence of both A and B, and the existence of B alone. Furthermore, the character " / " in this document generally indicates that the related objects are in an "or" relationship.

[0048] It should be understood that although the terms "first," "second," and "third" may be used to describe preset ranges in embodiments of the present invention, these preset ranges should not be limited to these terms. These terms are merely used to distinguish one preset range from another. For example, without departing from the scope of embodiments of the present invention, the first preset range may also be referred to as the second preset range, and similarly, the second preset range may also be referred to as the first preset range.

[0049] The word "if," as used herein, may be interpreted as "at the time of" or "when" or "in response to determining" or "in response to detecting," depending on the context. Similarly, the phrases "if it is determined" or "if (stated condition or event) is detected" may be interpreted as "when it is determined" or "in response to the determination" or "when detecting (stated condition or event)" or "in response to detecting (stated condition or event)," depending on the context.

[0050] Figure 1 A flow chart of the dimension table data storage method in this embodiment is shown. The dimension table data storage method can be applied to a computer device, wherein the computer device is a device that can perform human-computer interaction with a user, including but not limited to wearable devices, computers, smart phones, tablets and other devices. Figure 1 As shown, the dimension table data storage method includes the following steps:

[0051] S10: Compare dimension table data of full partitions of two consecutive days to obtain incremental data.

[0052] Among them, two adjacent days refer to two consecutive natural days, such as January 5th and January 6th, or February 3rd and February 4th, etc., which can all be called adjacent two days.

[0053] The dimension table data stored daily by the device can be used as a partition. When all dimension data tables for a day are stored, the partition can be called a full partition.

[0054] Among them, the dimension table can be regarded as a window for users to analyze data. The dimension table contains the characteristics of the fact records in the fact table. Some characteristics provide descriptive information, and some characteristics specify how to summarize the fact table data to provide useful information for analysts. The dimension table contains a hierarchy of characteristics that help summarize data.

[0055] Furthermore, incremental data includes deleted data, added data, and updated data. Updated data may also include data before and after the update. Specifically, incremental data may use markers to record deleted data, added data, and updated data. When searching incremental data, deleted data, added data, and updated data may be determined based on pre-set markers.

[0056] In one embodiment, a data comparison can be performed on the dimension table data of the full partitions of two consecutive natural days, and changes such as data changes, additions, or deletions can be identified from the dimension table data of the partitions of the two natural days, thereby obtaining incremental data. It can be understood that when a partition used for storage stores full snapshot data, the difference in dimension table data of the full partitions on two consecutive days is very small relative to the amount of data stored in the full partition. It can be understood that only part of the dimension table data of the full partitions on two consecutive days has changed. Therefore, in this embodiment, this changed data is extracted as incremental data, providing the technical prerequisite for the subsequent implementation of optimized storage of dimension table data.

[0057] Furthermore, in step S10, the dimension table data of the full partitions of two consecutive days are compared to obtain incremental data, specifically including:

[0058] S11: Calculate the information summary values of the logical primary keys of the dimension table data of the full partition for two consecutive days.

[0059] Among them, the unique key composed of certain business fields is called the logical primary key.

[0060] A message digest is a data processing method that converts a message of any length into a short message of a fixed length. For example, it converts plaintext of any length into ciphertext of a fixed length. The ciphertext has a fixed length. Moreover, different plaintexts are always digested into different ciphertexts using a message digest algorithm. However, the digests of the same plaintext must be consistent. In this embodiment, this method is specifically used to verify the consistency of dimension table data.

[0061] In one embodiment, the computer device uses the logical primary key of the dimension table data as a reference standard for dimension table data comparison, and adopts the message digest algorithm MD5 (Message-Digest Algorithm) to calculate the message digest value of the dimension table data of the full partition for two consecutive days to determine whether the dimension table data has changed based on the message digest value.

[0062] S12: Perform a full outer join of the dimension table data of the full partitions for two consecutive days based on the logical primary key.

[0063] The full outer join can be understood as a data processing operation of listing all tuples in the dimension table corresponding to the full partitions of two adjacent days (each tuple appears only once).

[0064] It can be understood that performing a full outer join on the dimension table can enable data comparison based on the logical primary key in the same dimension table.

[0065] S13: Compare the information summary value of the logical primary key to obtain a comparison result.

[0066] As you can understand, since the logical primary key is a unique key composed of fields, whether the dimension table data of the full partition has changed between two consecutive days is actually compared with the logical primary key as a reference standard. The information digest value is compared based on the logical primary key, and the corresponding comparison result is obtained based on the information digest value. Specifically, the comparison result includes whether the dimension table data corresponding to the logical primary key is deleted, added, updated, or unchanged.

[0067] S14: If the comparison result shows that the information summary value of the second day of two consecutive days is empty, it is determined that the deleted data is the dimension table data corresponding to the logical primary key.

[0068] It can be understood that, for example, in the dimension table data of the full partition on January 1 and January 2, the comparison result on January 2 is determined by calculation to be an empty information summary value, which indicates that the dimension table data that appeared on January 1 has been cleared on January 2. At this time, it can be determined that the dimension table data corresponding to the logical primary key is deleted data.

[0069] S15: If the comparison result shows that the information summary value of the previous day between two consecutive days is empty, it is determined that the newly added data is dimension table data corresponding to the logical primary key.

[0070] It can be understood that, for example, in the dimension table data of the full partitions on January 1 and January 2, the comparison result on January 1 is determined by calculation to be an empty information summary value, which indicates that the dimension table data that appeared on January 2 did not appear on January 1. At this time, it can be determined that the dimension table data corresponding to the logical primary key is new data.

[0071] S16: If the comparison result shows that the information summary values of two consecutive days are different and not empty, it is determined that the updated data is the dimension table data corresponding to the logical primary key.

[0072] It can be understood that, for example, in the dimension table data of the full partition on January 1 and January 2, the comparison result on January 1 is determined by calculation to be that the information summary value is different and not empty, which indicates that there is dimension table data corresponding to the logical primary key on January 1 and January 2, but the information summary values are different, which means that the dimension table data corresponding to the logical primary key has changed. At this time, it can be determined that the dimension table data corresponding to the logical primary key is updated data.

[0073] S17: If the comparison result shows that the information summary values of two consecutive days are the same, it is concluded that the dimension table data corresponding to the logical primary key has not changed.

[0074] It can be understood that, for example, in the dimension table data of the full partition on January 1 and January 2, if the comparison result on January 1 is determined by calculation to be the same information summary value, it means that the dimension table data corresponding to the logical primary key on January 1 and January 2 may both be null values or may both be the same non-null value, but the dimension table data corresponding to the logical primary key does not change in either case.

[0075] In one embodiment, if the dimension table is specifically a personnel information table, in which the combination of the personnel ID (emp_id) and the enterprise ID (enterprise_id) is its logical primary key, the dimension table data of the full partition for two consecutive days is taken as an example:

[0076] 2020-01-05

[0077] emp_id enterprise_id

[0078] zhangsan E1

[0079] lisi E1

[0080] 2020-01-06

[0081] emp_id enterprise_id

[0082] zhangsan E2

[0083] wangwu E1

[0084] Specifically, by taking the md5 operation of emp_id+enterprise_id and comparing the corresponding md5 values, it is found that the md5 values corresponding to zhangsan+E2 and zhangsan+E1 are different, so zhangsan+E2 is updated data and can be marked as state 3; the md5 value of lisi+E1 on that day is empty, which can be marked as state 2; the md5 value of wangwu+E1 corresponding to the previous day is empty, which can be marked as state 1. Furthermore, the corresponding incremental number can also be written into the incremental partition table.

[0085] In steps S11-S17, a specific embodiment of obtaining incremental data is provided, which uses the logical primary key as a reference standard for comparison and adopts the information summary value to determine whether the dimension table data of the full partition of two adjacent days has changed, thereby achieving accurate and purposeful acquisition of incremental data.

[0086] S20: The full partitions to be retained are determined using a preset rule, and the full partitions that are not determined to be retained according to the preset rule and for which incremental data has been obtained are deleted.

[0087] Specifically, the preset rule can be to retain full partitions on the 1st, 15th, and end of each month. This means that according to the preset rule, only three days' worth of data needs to be retained each month. Compared to the original practice of retaining a full partition every day, this method only uses about one-tenth the original storage space, significantly improving the rationality of storage space usage.

[0088] S30: When restoring dimension table data of a full partition of a target date, a target full partition is determined, wherein the target full partition refers to a full partition that is closest to the target date.

[0089] Understandably, the implementation of optimized storage of dimension table data requires the restoration of dimension table data to demonstrate its value. If only a portion of the full partitions is deleted in step S20, but the dimension table data for any day's full partitions cannot be restored, then some dimension table data in the non-retained full partitions is effectively lost. However, in this embodiment, the dimension table data for any day's full partition can also be restored, thereby achieving more effective optimized storage of dimension table data.

[0090] Specifically, when restoring dimension table data of a full partition on a target date, you first need to determine the target full partition. This target full partition belongs to the full partitions determined to be retained according to the preset rules, and the one closest to the target date is used as the target full partition. For example, if a user wants to restore dimension table data of a full partition on the 18th (target date), since the full partition closest to the target date is the one determined to be retained according to the preset rules, the full partition on the 15th is used as the target full partition for the sake of simplicity in data recovery. This full partition and incremental data are used to restore the dimension data table for the full analysis on the 18th.

[0091] S40: Perform merging and deduplication processing based on the dimension table data of the target full partition, the target date, the generation date corresponding to the target full partition, and the incremental data of the date between the target date and the generation date to obtain the dimension table data of the full partition of the target date.

[0092] As you can understand, in addition to the incremental data for the target date, the incremental data for the target full partition's generation date and dates between the target date and generation date are also key to recovering dimension table data for the full partition. The incremental data for dates between the target date and generation date serves as a bridge for data recovery. Through merging and deduplication, the dimension table data for the full partition of the target date can be recovered. This allows users to recover dimension table data for any full partition at any time, based on the full partitions retained according to pre-set rules.

[0093] Furthermore, if the generation date is before the target date, that is, in step S40, a merge and deduplication process is performed based on the dimension table data of the target full partition, the target date, the generation date corresponding to the target full partition, and the incremental data of the dates between the target date and the generation date to obtain the dimension table data of the full partition of the target date, specifically including:

[0094] Obtain incremental data for dates between the target date and the generation date, and the incremental data for the target date; remove the incremental data for dates between the target date and the generation date, and the dimension table data before the update in the incremental data for the target date; merge the dimension table data for the target full partition and the incremental data after removing the dimension table data before the update, and deduplicate by logical primary key to retain the dimension table data with the latest date; remove deleted data from the dimension table data with the latest date to obtain the dimension table data for the full partition of the target date.

[0095] Furthermore, if the generation date is after the target date, then in step S40, a merge and deduplication process is performed based on the dimension table data of the target full partition, the target date, the generation date corresponding to the target full partition, and the incremental data of the dates between the target date and the generation date to obtain the dimension table data of the full partition of the target date, specifically including:

[0096] Obtain incremental data for dates between a target date and a generation date, incremental data for the target date, and incremental data for the generation date; remove dimension table data before updates in the incremental data for the target date; remove newly added data and updated dimension table data in the incremental data for the generation date; remove updated dimension table data from incremental data other than the incremental data for the target date and the incremental data for the generation date to obtain processed incremental data; merge the dimension table data for the target full partition and the processed incremental data, and deduplicate by logical primary key to retain the dimension table data with the oldest date; remove newly added data from the dimension table data with the oldest date, excluding (except) the newly added data for the target date, to obtain dimension table data for the full partition for the target date.

[0097] It can be understood that there is a certain difference between the generation date before and after the target date. If the generation date is before the target date, the strategy implemented in the embodiment is mainly to retain the updated dimension table data to achieve data recovery. If the generation date is after the target date, the strategy implemented in the embodiment is mainly to restore the deleted data to achieve data recovery.

[0098] Furthermore, after step S40, that is, after performing merging and deduplication processing based on the dimension table data of the target full partition, the target date, the generation date corresponding to the target full partition, and the incremental data of the date between the target date and the generation date to obtain the dimension table data of the full partition of the target date, the following further specifically is included:

[0099] S41: Obtain dimension table data of the target for two consecutive days, wherein the dimension table data of the target for two consecutive days is stored in a full partitioning manner, and the user's private information is stored in the blockchain.

[0100] It can be understood that, compared with the dimension table data of the full partition of two adjacent days in S10, the target dimension table data of two adjacent days in this embodiment refers to the dimension table data of two adjacent days obtained after deleting part of the full partition data, and the dimension table data of the full partition of two adjacent days in S10 can be the dimension table data of the full partition of two adjacent days obtained after not deleting part of the full partition data, or can be obtained after deleting part of the dimension table data of the full partition. S10 represents a general case. This embodiment emphasizes the dimension table data of two adjacent days obtained after deleting part of the full partition data.

[0101] In one embodiment, the dimension table data for two adjacent target days may specifically refer to the dimension table data for the current day and yesterday. Understandably, when calculating incremental data each day, since the full interval is only retained according to preset rules and the number is relatively small, when the device calculates the incremental data for that day on the 22nd, it is necessary to calculate the dimension table data for the full interval on the 21st according to the method in the embodiment. Only after comparing and processing the dimension table data for the 22nd can the incremental data be obtained, resulting in relatively low computational efficiency. Given that incremental data needs to be calculated daily, two additional dynamic full intervals can be set in addition to the full interval retained according to preset rules to achieve rapid calculation of incremental data.

[0102] The blockchain mentioned in this application is a new application model for computer technologies such as distributed data storage, peer-to-peer transmission, consensus mechanisms, and encryption algorithms. Blockchain is essentially a decentralized database, a series of data blocks generated using cryptographic methods. Each data block contains information about a batch of network transactions, which is used to verify the validity of the information (to prevent counterfeiting) and generate the next block. Blockchain can include the blockchain underlying platform, the platform product service layer, and the application service layer.

[0103] The underlying blockchain platform can include processing modules such as user management, basic services, smart contracts, and operation monitoring. Among them, the user management module is responsible for the identity information management of all blockchain participants, including maintaining public and private key generation (account management), key management, and maintaining the corresponding relationship between the user's real identity and the blockchain address (authority management), etc., and under authorization, it supervises and audits the transactions of certain real identities and provides risk control rule configuration (risk control audit); the basic service module is deployed on all blockchain node devices to verify the validity of business requests, and records the valid requests to the storage after consensus is reached. For a new business request, the basic service first adapts the interface to parse and authenticate the request (interface adaptation), and then encrypts the business information through the consensus algorithm (consensus management). The smart contract module is responsible for the registration, issuance, triggering and execution of contracts. Developers can define the contract logic in a programming language and publish it to the blockchain (contract registration). According to the logic of the contract terms, the contract logic is triggered by calling keys or other events to trigger execution. The contract logic is completed, and the contract upgrade and cancellation functions are also provided. The operation monitoring module is mainly responsible for the deployment, configuration modification, contract setting, cloud adaptation and real-time status visualization output of the product during the product release process, such as alarms, network status monitoring, and node device health monitoring.

[0104] S42: When the target two adjacent days change, based on the dimension table data of the target two adjacent days, the dimension table data of the changed target two adjacent days is stored in a full partitioning manner, and a removal operation is performed on the full partitions except the full partitions determined to be retained using the preset rules.

[0105] In one embodiment, in addition to the full intervals retained according to preset rules, the device retains dimension data tables for the current day and yesterday's full partitions. When the target changes between two adjacent days, that is, when a day has passed, the original full partition for yesterday is deleted, and the original full partition for today and the dimension table data for the immediately preceding day are retained. It is understood that when the target two adjacent days overlap with the number of days retained according to preset rules, it is not necessary to repeatedly store the dimension table data for the full partition.

[0106] In steps S41-S42, a specific implementation method for quickly calculating incremental data every day is provided. By retaining the dimension table data of the full partition of the target two adjacent days, the data processing efficiency is improved as much as possible without occupying a large amount of storage space.

[0107] In an embodiment of the present invention, by comparing and processing the dimension table data of the full partitions of two adjacent days, incremental data is obtained, which provides a prerequisite for realizing appropriate deletion of full partitions and optimized storage of dimension table data; after obtaining incremental data, the full partitions to be retained can be determined using preset rules, and the full partitions that are not determined to be retained in the preset rules and for which incremental data has been obtained can be deleted, thereby clearing a large amount of storage space; when it is necessary to restore the dimension table data of the full partition of the target date, merging and deduplication processing can be performed based on the dimension table data of the target full partition, as well as the incremental data of the target date, the generation date corresponding to the target full partition, and the date between the target date and the generation date, to realize rapid recovery of dimension table data for any day. In an embodiment of the present invention, by optimizing the storage of dimension table data, the rationality of storage resource utilization can be significantly improved while ensuring that the dimension table data can be restored at any time.

[0108] It should be understood that the size of the serial numbers of the steps in the above embodiments does not mean 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 the present invention.

[0109] Based on the dimension table data storage method provided in the embodiment, the embodiment of the present invention further provides an embodiment of a device for implementing each step and method in the above method embodiment.

[0110] Figure 2 The principle block diagram of the dimension table data storage device corresponding to the dimension table data storage method in the embodiment is shown. Figure 2 As shown, the dimension table data storage device includes an incremental data acquisition module 10, a deletion module 20, a target full partition determination module 30, and a target date data acquisition module 40. The functions implemented by the incremental data acquisition module 10, the deletion module 20, the target full partition determination module 30, and the target date data acquisition module 40 correspond to the corresponding steps of the dimension table data storage method in the embodiment. To avoid redundancy, this embodiment does not describe them in detail.

[0111] The incremental data acquisition module 10 is used to compare the dimension table data of the full partitions of two consecutive days to obtain incremental data.

[0112] The deletion module 20 is configured to determine the full partitions to be retained using a preset rule, and delete the full partitions that are not retained according to the preset rule and for which incremental data has been obtained.

[0113] The target full partition determination module 30 is used to determine the target full partition when restoring dimension table data of the full partition of the target date, wherein the target full partition refers to the full partition closest to the target date.

[0114] The target date data acquisition module 40 is used to perform merging and deduplication processing based on the dimension table data of the target full partition, as well as the target date, the generation date corresponding to the target full partition, and the incremental data of the date between the target date and the generation date, to obtain the dimension table data of the full partition of the target date.

[0115] Optionally, the incremental data includes deleted data, newly added data and updated data, and the updated data includes dimension table data before the update and dimension table data after the update.

[0116] Optionally, the incremental data acquisition module is specifically used to:

[0117] Calculate the summary values of the logical primary keys of dimension table data for the full partitions on two consecutive days.

[0118] Perform a full outer join of dimension table data of full partitions for two consecutive days based on the logical primary key;

[0119] Compare the information summary value of the logical primary key to obtain the comparison result;

[0120] If the comparison result shows that the information summary value of the second day of two consecutive days is empty, it is concluded that the deleted data is the dimension table data corresponding to the logical primary key;

[0121] If the comparison result shows that the information summary value of the previous day in two consecutive days is empty, it is concluded that the newly added data is the dimension table data corresponding to the logical primary key;

[0122] If the comparison result shows that the information summary values of two consecutive days are different and not empty, then the updated data is the dimension table data corresponding to the logical primary key;

[0123] Optionally, the target date data acquisition module is specifically used to:

[0124] Get the incremental data of the dates between the target date and the generated date, as well as the incremental data of the target date;

[0125] Remove the incremental data between the target date and the generated date, and the dimension table data before the update in the incremental data of the target date;

[0126] Merge the dimension table data of the target full partition and the incremental data without the dimension table data before the update, and deduplicate them by logical primary key to retain the latest dimension table data.

[0127] Remove the deleted data from the dimension table data with the latest date to obtain the dimension table data of the full partition on the target date.

[0128] Optionally, the target date data acquisition module is further specifically configured to:

[0129] Get the incremental data of the dates between the target date and the generated date, the incremental data of the target date, and the incremental data of the generated date;

[0130] Remove dimension table data before update from the incremental data of the target date;

[0131] Remove the newly added data and updated dimension table data from the incremental data of the generated date;

[0132] Remove updated dimension table data from the incremental data other than the incremental data of the target date and the incremental data of the generation date to obtain processed incremental data;

[0133] Merge the dimension table data of the target full partition and the processed incremental data, and deduplicate them by logical primary key to retain the oldest dimension table data.

[0134] In addition to the new data on the target date, the new data in the dimension table data with the oldest date is removed to obtain the dimension table data of the full partition on the target date.

[0135] Optionally, the dimension table data storage device is further specifically used for:

[0136] Obtain the dimension table data for two consecutive days of the target. The dimension table data for two consecutive days of the target is stored in a full partitioning manner.

[0137] When the target two adjacent days change, based on the dimension table data of the target two adjacent days, the dimension table data of the changed target two adjacent days is stored in a full partitioning manner, and a removal operation is performed on the full partitions except for the full partitions determined to be retained using the preset rules.

[0138] In an embodiment of the present invention, by comparing and processing the dimension table data of the full partitions of two adjacent days, incremental data is obtained, which provides a prerequisite for realizing appropriate deletion of full partitions and optimized storage of dimension table data; after obtaining incremental data, the full partitions to be retained can be determined using preset rules, and the full partitions that are not determined to be retained in the preset rules and for which incremental data has been obtained can be deleted, thereby clearing a large amount of storage space; when it is necessary to restore the dimension table data of the full partition of the target date, merging and deduplication processing can be performed based on the dimension table data of the target full partition, as well as the incremental data of the target date, the generation date corresponding to the target full partition, and the date between the target date and the generation date, to realize rapid recovery of dimension table data for any day. In an embodiment of the present invention, by optimizing the storage of dimension table data, the rationality of storage resource utilization can be significantly improved while ensuring that the dimension table data can be restored at any time.

[0139] This embodiment provides a computer-readable storage medium storing a computer program. When executed by a processor, the computer program implements the dimension table data storage method of the embodiment. To avoid repetition, this computer program is not described in detail here. Alternatively, when executed by a processor, the computer program implements the functions of each module / unit in the dimension table data storage device of the embodiment. To avoid repetition, this computer program is not described in detail here.

[0140] Figure 3 FIG. 1 is a schematic diagram of a computer device provided by an embodiment of the present invention. Figure 3 As shown, the computer device 50 of this embodiment includes: a processor 51, a memory 52, and a computer program 53 stored in the memory 52 and executable by the processor 51. When executed by the processor 51, the computer program 53 implements the dimension table data storage method of the embodiment. To avoid repetition, a detailed description is omitted here. Alternatively, when executed by the processor 51, the computer program 53 implements the functions of each model / unit in the dimension table data storage device of the embodiment. To avoid repetition, a detailed description is omitted here.

[0141] The computer device 50 may be a desktop computer, a notebook computer, a PDA, a cloud server, or other computing devices. The computer device 50 may include, but is not limited to, a processor 51 and a memory 52. Those skilled in the art will appreciate that Figure 3 This is merely an example of the computer device 50 and does not constitute a limitation of the computer device 50 . The computer device 50 may include more or fewer components than shown in the figure, or a combination of certain components, or different components. For example, the computer device may also include input and output devices, network access devices, buses, etc.

[0142] The processor 51 may be a central processing unit (CPU), or 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. The general-purpose processor may be a microprocessor or any conventional processor.

[0143] The memory 52 can be an internal storage unit of the computer device 50, such as a hard disk or memory of the computer device 50. The memory 52 can also be an external storage device of the computer device 50, such as a plug-in hard disk, a Smart Media Card (SMC), a Secure Digital (SD) card, a flash memory card, etc. equipped on the computer device 50. Furthermore, the memory 52 can include both an internal storage unit of the computer device 50 and an external storage device. The memory 52 is used to store computer programs and other programs required by the computer device, as well as dimension table data. The memory 52 can also be used to temporarily store dimension table data that has been output or is about to be output.

[0144] Those skilled in the art will clearly understand that for the sake of convenience and brevity of description, only the division of the above-mentioned functional units and modules is used as an example. In actual applications, the above-mentioned functions can be distributed and completed by different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above.

[0145] The above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit the same. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. These modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the various embodiments of the present invention, and should all be included in the scope of protection of the present invention.

Claims

1. A dimension table data storage method, characterized in that: The method comprises: Compare dimension table data of full partitions from two consecutive days to obtain incremental data. Determine the full partitions to be retained using a preset rule, and delete the full partitions that are not retained according to the preset rule and have obtained the incremental data, wherein the preset rule includes retaining the full partitions according to a preset period; When restoring dimension table data of a full partition on a target date, determining a target full partition, wherein the target full partition refers to the full partition closest to the target date and time among the full partitions determined to be retained using the preset rule; Performing merging and deduplication processing based on the dimension table data of the target full partition, the target date, the generation date corresponding to the target full partition, and the incremental data of the dates between the target date and the generation date, to obtain the dimension table data of the full partition of the target date; After obtaining the dimension table data of all partitions for the target date, the following steps are also included: Obtain the target dimension table data for two consecutive days, where the target dimension table data for two consecutive days is stored in a full partitioning manner, and the user's private information is stored in the blockchain. The target dimension table data for two consecutive days refers to the dimension table data for two consecutive days obtained after deleting some full partition data; When the target two adjacent days change, based on the dimension table data of the target two adjacent days, the dimension table data of the changed target two adjacent days is stored in a full partitioning manner, and a removal operation is performed on the full partitions except the full partitions determined to be retained by the preset rules. When the target two adjacent days overlap with the number of days determined to be retained by the preset rules, the dimension table data of the duplicated stored full partitions is removed.

2. The method according to claim 1, characterized in that The incremental data includes deleted data, newly added data, and updated data. The incremental data obtained by comparing the dimension table data of the full partitions of two consecutive days includes: Calculate the information summary value of the logical primary key of the dimension table data of the full partition for the two adjacent days respectively; Performing a full outer join of the dimension table data of the full partitions of the two adjacent days according to the logical primary key; Comparing the information summary values of the logical primary keys to obtain a comparison result; If the comparison result shows that the information summary value of the second day of two consecutive days is empty, it is concluded that the deleted data is the dimension table data corresponding to the logical primary key; If the comparison result shows that the information summary value of the previous day between two consecutive days is empty, it is concluded that the newly added data is the dimension table data corresponding to the logical primary key; If the comparison result is that the information summary values of two adjacent days are different and not empty, it is concluded that the updated data is the dimension table data corresponding to the logical primary key.

3. The method according to claim 1, characterized in that The incremental data includes deleted data, newly added data and updated data, and the updated data includes dimension table data before and after the update; When the generation date is before the target date, the dimension table data of the target full partition, the target date, the generation date corresponding to the target full partition, and the incremental data between the target date and the generation date are merged and deduplicated to obtain the dimension table data of the full partition of the target date, including: Obtaining incremental data for dates between the target date and the generated date, as well as incremental data for the target date; Remove the incremental data of the dates between the target date and the generation date, and the dimension table data before the update in the incremental data of the target date; Merging the dimension table data of the target full partition and the incremental data after removing the dimension table data before the update, and deduplicating them based on the logical primary key to retain the dimension table data with the latest date; The deleted data in the dimension table data of the latest date is removed to obtain the dimension table data of the full partition of the target date.

4. The method according to claim 1, wherein The incremental data includes deleted data, newly added data and updated data, and the updated data includes dimension table data before and after the update; When the generation date is after the target date, the dimension table data of the target full partition, the target date, the generation date corresponding to the target full partition, and the incremental data between the target date and the generation date are merged and deduplicated to obtain the dimension table data of the full partition of the target date, including: Obtaining incremental data for dates between the target date and the generation date, incremental data for the target date, and incremental data for the generation date; Remove dimension table data before update from the incremental data of the target date; Remove the newly added data and updated dimension table data in the incremental data of the generation date; removing updated dimension table data from incremental data other than the incremental data on the target date and the incremental data on the generation date to obtain processed incremental data; Merge the dimension table data of the target full partition and the processed incremental data, and deduplicate based on the logical primary key to retain the dimension table data with the oldest date; In addition to the newly added data on the target date, the newly added data in the oldest dimension table data on the target date is removed to obtain the dimension table data of the full partition on the target date.

5. A dimension table data storage device, characterized in that: The device comprises: The incremental data acquisition module is used to compare the dimension table data of the full partitions of two consecutive days to obtain incremental data; a deletion module, configured to determine the full partitions to be retained using a preset rule, and to delete the full partitions that are not retained according to the preset rule and for which the incremental data has been obtained, wherein the preset rule includes retaining the full partitions according to a preset period; a target full partition determination module, configured to determine a target full partition when restoring dimension table data of a full partition on a target date, wherein the target full partition refers to the full partition closest to the target date and time among the full partitions determined to be retained using a preset rule; A target date data acquisition module is configured to perform merging and deduplication processing based on the dimension table data of the target full partition, the target date, the generation date corresponding to the target full partition, and the incremental data of the dates between the target date and the generation date, to obtain the dimension table data of the full partition of the target date; After obtaining the dimension table data of the full partition of the target date, the device is further used to obtain the dimension table data of the two adjacent target days, wherein the dimension table data of the two adjacent target days is stored in a full partition manner, and the dimension table data of the two adjacent target days refers to the dimension table data of the two adjacent target days obtained after deleting part of the full partition data; when the two adjacent target days change, based on the dimension table data of the two adjacent target days, the dimension table data of the changed two adjacent target days is stored in a full partition manner, and a removal operation is performed on the full partitions except the full partitions determined to be retained by the preset rules, wherein when the two adjacent target days overlap with the number of days determined to be retained by the preset rules, the dimension table data of the duplicated full partitions are removed.

6. The device according to claim 5, characterized in that The incremental data includes deleted data, newly added data and updated data. The incremental data acquisition module is specifically used to: Calculate the information summary value of the logical primary key of the dimension table data of the full partition for the two adjacent days respectively; Performing a full outer join of the dimension table data of the full partitions of the two adjacent days according to the logical primary key; Comparing the information summary values of the logical primary keys to obtain a comparison result; If the comparison result shows that the information summary value of the second day of two consecutive days is empty, it is concluded that the deleted data is the dimension table data corresponding to the logical primary key; If the comparison result shows that the information summary value of the previous day between two consecutive days is empty, it is concluded that the newly added data is the dimension table data corresponding to the logical primary key; If the comparison result is that the information summary values of two adjacent days are different and not empty, it is concluded that the updated data is dimension table data corresponding to the logical primary key.

7. The device according to claim 5, characterized in that The incremental data includes deleted data, newly added data and updated data. The updated data includes dimension table data before and after the update. The target date data acquisition module is specifically used to: Obtaining incremental data for dates between the target date and the generated date, as well as incremental data for the target date; Remove the incremental data of the dates between the target date and the generation date, and the dimension table data before the update in the incremental data of the target date; Merging the dimension table data of the target full partition and the incremental data after removing the dimension table data before the update, and deduplicating them based on the logical primary key to retain the dimension table data with the latest date; The deleted data in the dimension table data of the latest date is removed to obtain the dimension table data of the full partition of the target date.

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 steps of the dimension table data storage method according to any one of claims 1 to 4 are implemented.

9. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the steps of the dimension table data storage method according to any one of claims 1 to 4 are implemented.

Images