Data Association Method, Apparatus, Device, and Storage Medium
By storing version information in the dimensional database and using the associated primary key for matching, the problem of inaccurate association between fact data and dimensional data in real-time data processing is solved, real-time and accurate data association is achieved, and the real-time and accuracy of the system is improved.
Patent Information
- Application Number
- CN202411505747.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-10-25
- Publication Date
- 2025-07-22
- Estimated Expiration
- 2044-10-25
AI Technical Summary
In real-time data processing, the correlation between fact data and dimensional data is inaccurate, resulting in a reduction in real-time performance in traditional methods and the inability to achieve real-time accurate correlation.
By storing the version information of the dimension data in the dimension database, using the associated primary key and version information to match, we directly find the matching version of the dimension data in the dimension database for association, and record the association time after the association is successful for verification to ensure the accuracy and real-timeness of the data.
It realizes real-time and accurate correlation of fact data with dimensional data when data is stored, improves the accuracy and real-timeness of data association, and solves the problem of real-time reduction caused by association errors in traditional methods.
Smart Images
Figure CN119474168B_ABST
Abstract
Description
Technical Field
[0001] The present disclosure relates to the technical field of real-time data processing, and in particular, to a method, apparatus, device, and storage medium for data association. Background Art
[0002] When constructing a data warehouse for big data, in order to reduce the complexity of data processing, fact data and dimension data are often associated, complemented, and widened. In the field of real-time data processing, when fact data and dimension data are associated, there may be cases of incorrect association due to inaccurate association between fact data and dimension data. In response to this situation, the traditional data processing method is to create a dimension zipper table through the offline data warehouse the next day to accurately associate the fact data and dimension data and then perform data backfilling. The traditional processing method greatly reduces the real-time performance of the real-time data warehouse. Therefore, how to accurately associate fact data and dimension data in real time during data storage has become an urgent problem for those skilled in the art. Summary of the Invention
[0003] In view of this, the present disclosure provides a data association method, apparatus, device, and storage medium, which can realize accurate association of fact data and dimension data in real time during data storage.
[0004] According to a first aspect of the present disclosure, there is provided a data association method for associating fact data with dimension data, including:
[0005] Obtaining current fact data to be associated from source data;
[0006] Searching for corresponding matching versions of dimension data in a dimension database according to the current fact data; wherein, the dimension database stores a dimension data table, and the dimension data table records the dimension data and version information of the dimension data;
[0007] Associating the current fact data with the corresponding matching versions of dimension data.
[0008] In a possible implementation, when searching for corresponding matching versions of dimension data in a dimension database according to the current fact data, it includes:
[0009] Extracting an association primary key from the current fact data and obtaining a concatenated query condition according to the association primary key;
[0010] Searching for a matching dimension version from the dimension data table recorded in the dimension database according to the concatenated query condition.
[0011] In a possible implementation, the concatenated query condition includes: the update time of the dimension version is less than or equal to the occurrence time of the current fact data, and the update time of the dimension version is the closest to the occurrence time of the current fact data.
[0012] In a possible implementation, the concatenated query condition further includes: the associated primary key matches the primary key of the dimension data.
[0013] In a possible implementation, when finding the dimension data of the corresponding matching version from the dimension database according to the fact data, it further includes:
[0014] Determine whether the update time of the dimension version is later than the association time of the previous fact data of the current fact data;
[0015] When it is determined that the update time of the dimension version is later than the association time of the previous fact data of the current fact data, determine that the dimension version is the latest version, and perform the operation of writing the dimension data into the database.
[0016] In a possible implementation, when it is determined that the update time of the dimension version is earlier than the association time of the previous fact data of the current fact data, perform a rebrushing operation on the fact data associated within a preset time period before the association time of the current fact data, and re-associate the fact data associated within the preset time period before.
[0017] According to a second aspect of the present disclosure, there is provided a data association device for associating fact data and dimension data, including:
[0018] A data acquisition module for acquiring the current fact data to be associated from the source data;
[0019] A dimension data search module for finding the dimension data of the corresponding matching version from the dimension database according to the current fact data; wherein, the dimension database stores a dimension data table, and the dimension data table records the dimension data and the version information of the dimension data;
[0020] A data association module for associating the current fact data with the dimension data of the corresponding matching version.
[0021] In a possible implementation, when finding the dimension data of the corresponding matching version from the dimension database according to the fact data, the dimension data search module includes:
[0022] A dimension data update time comparison module for determining whether the update time of the dimension version is later than the association time of the previous fact data of the current fact data;
[0023] A dimension data writing module, configured to determine that the dimension version is the latest version when it is determined that the update time of the dimension version is later than the association time of the previous fact data of the current fact data, and perform an operation of writing the dimension data into a database.
[0024] According to a third aspect of the present disclosure, there is provided a data association device, including: a processor; a memory for storing processor-executable instructions; wherein, the processor is configured to execute the method described in the first aspect of the present disclosure.
[0025] According to a fourth aspect of the present disclosure, there is provided a non-volatile computer-readable storage medium, on which computer program instructions are stored, characterized in that when the computer program instructions are executed by a processor, the method described in any one of the first aspects of the present disclosure is implemented.
[0026] The present disclosure provides a data association method, including: obtaining current fact data to be associated from source data; searching for dimension data of a corresponding matching version in a dimension database according to the current fact data; wherein, the dimension database stores a dimension data table, and the dimension data table records dimension data and version information of the dimension data; associating the current fact data with the dimension data of the corresponding matching version. The above data association method records the version information of the dimension data in the dimension data table stored in the dimension database. When performing association, directly search for the dimension data of the version matching the current fact data in the dimension database, which can realize accurate association of fact data and dimension data in real time during data storage, and solve the problem that in the traditional processing method, when an association error occurs, a zipper table is created through an offline warehouse the next day for accurate data association and then backfilled, which is not real-time, and effectively improves the accuracy of the system for real-time association of fact data and dimension data.
[0027] According to the following detailed description of exemplary embodiments with reference to the accompanying drawings, other features and aspects of the present disclosure will become clear. BRIEF DESCRIPTION OF THE DRAWINGS
[0028] The drawings included in and constituting a part of this specification, together with the specification, illustrate exemplary embodiments, features, and aspects of the present disclosure and are used to explain the principles of the present disclosure.
[0029] Figure 1 A flowchart showing a data association method according to an embodiment of the present disclosure;
[0030] Figure 2 An architecture diagram of a real-time data warehouse showing an embodiment of the present disclosure;
[0031] Figure 3Shows a flowchart of a water level mechanism according to an embodiment of the present disclosure;
[0032] Figure 4 Shows a schematic block diagram of a data association device according to an embodiment of the present disclosure;
[0033] Figure 5 Shows a schematic block diagram of a data association device according to an embodiment of the present disclosure. Detailed implementation manners
[0034] Various exemplary embodiments, features, and aspects of the present disclosure will be described in detail below with reference to the accompanying drawings. The same reference numerals in the drawings denote elements having the same or similar functions. Although various aspects of the embodiments are shown in the drawings, the drawings are not necessarily drawn to scale unless otherwise specified.
[0035] The special term "exemplary" here means "serving as an example, embodiment, or illustration". Any embodiment described as "exemplary" here is not necessarily to be construed as superior to or better than other embodiments.
[0036] In addition, for a better description of the present disclosure, numerous specific details are given in the following detailed implementation manners. Those skilled in the art should understand that the present disclosure can also be implemented without some specific details. In some instances, methods, means, elements, and circuits well known to those skilled in the art are not described in detail so as to highlight the gist of the present disclosure.
[0037] <Method embodiment>
[0038] Figure 1 Shows a data association method according to an embodiment of the present disclosure (that is, Figure 2 Shows the specific process of generating wide table detail data in the real-time data warehouse architecture). As Figure 1 shown, the method includes the steps of: S1100, obtaining the current fact data to be associated from the source data; S1200, looking up the corresponding matching version of the dimension data in the dimension database according to the current fact data; wherein, the dimension database stores a dimension data table, and the dimension data table records the dimension data and the version information of the dimension data; S1300, associating the current fact data with the corresponding matching version of the dimension data.
[0039] Therefore, the data association method provided by the present disclosure records the version information of dimension data in the dimension data table stored in the dimension database. When performing association, it directly searches for the dimension data of the version that matches the current fact data in the dimension database, which can achieve that the current fact data can be associated with the correct version of the dimension data in real time, solving the problem that in the traditional processing method, when an association error occurs, a zipper table is created through the offline warehouse the next day for accurate data association and then backfilled, which is not real-time, and effectively improving the accuracy of the system to perform real-time association between fact data and dimension data.
[0040] It should be noted that the data association method in an embodiment of the present disclosure is performed in a real-time data warehouse built based on the Kafka platform. Further, it should be noted that when processing data, the real-time data warehouse generally uses Flink for data processing. Among them, Flink is a unified computing framework and distributed processing engine that combines batch processing and stream processing.
[0041] Based on the constructed fact data warehouse architecture, when performing a data association task, it is necessary to first obtain fact data. Fact data refers to the specific numerical information generated in business activities. Refer to Figure 2 As shown, in the source data stored in the real-time data warehouse built based on Kafka, the fact data is obtained by performing data ingestion on the stored source data. When storing, the system will obtain the fact data according to the own attributes of the fact data and store the fact data. It should be noted that before storing the fact data, the obtained fact data will be cleaned in the Flink-CDC layer of the real-time data warehouse to remove noise data, and then the cleaned fact data will be stored. Among them, the Flink-DWD layer of the real-time data warehouse is a link in the real-time data warehouse architecture, mainly used for data cleaning and integration. At the same time, the method of removing noise data from data is common knowledge in the art and will not be elaborated here.
[0042] After obtaining the fact data, search for the dimension data of the corresponding version that matches in the dimension database according to the obtained fact data. Among them, dimension data refers to the characteristics or attributes of fact data, providing different perspectives for observing data. Before performing the query, it is necessary to first store the dimension data of each version in the dimension database. Specifically, when storing the dimension data, the dimension data of each version is stored in the dimension database in the form of a dimension data table. At the same time, the stored dimension data table adds a dbTime column to record the update time of the dimension data of each version, and constructs a reverse index by sorting the dimension data of each version in the dimension database in reverse order according to the update time recorded in the dbTime column. Among them, dbTime is the data business time, which is used here to record the version update time of the dimension data.
[0043] It should be noted that when storing dimension data, an external database is imported as a dimension database to store dimension data of each version. Among them, the selected dimension database can be flexibly selected according to the selection principle, and no specific limitation is made. The selection principle is to support index query and data table sorting, and have good writing and query speeds. Further, based on the dimension data of each version stored in the dimension database, the system will set corresponding thresholds to regularly clean the dimension data of the dimension versions exceeding the thresholds, avoiding the problem of decreased query performance caused by excessive storage of dimension data. Specifically, the threshold can be set as at least one of the quantity threshold of the primary key or the time threshold. Among them, the quantity threshold and time threshold of the primary key can be flexibly determined according to the selected database, and no specific limitation is made.
[0044] Furthermore, based on the obtained current fact data and the dimension data of each version stored in the dimension database, the system will define the associated primary key of the current fact data, the logical primary key of the current dimension data, and the association relationship between the associated primary key and the logical primary key, and store them. Among them, when defining the associated primary key of the current fact data, the logical primary key of the current dimension data, and the association relationship between the associated primary key and the logical primary key, it can be flexibly determined, and no specific limitation is made.
[0045] According to the above, after storing the dimension data, data association is performed based on the obtained fact data and the dimension data of each version stored in the dimension database. When performing data association, first find the dimension data of the corresponding matching version from the dimension database, that is Figure 2 the dimension query steps (Dememsion query) shown. After querying the dimension data of the corresponding matching version, then associate the current fact data with the queried dimension data of the corresponding matching version. Specifically, refer to Figure 2 shown. When performing data association, based on the obtained current fact data, find the corresponding logical primary key from the dimension database according to the defined associated primary key of the current fact data and the association relationship between the associated primary key and the logical primary key, and then confirm the corresponding dimension data table through the found logical primary key. When there is only a single found dimension data table, associate the current fact data with the dimension data stored in the found dimension data table.
[0046] Further, when associating dimension data with the obtained current fact data, the corresponding logical primary key is searched in the dimension database according to the association primary key of the current fact data and the association relationship between the association primary key and the logical primary key. Then, the corresponding dimension data table is searched according to the found logical primary key. When multiple dimension data tables are found, the final dimension data table is determined according to the concatenated query conditions. Finally, the current fact data is associated with the dimension data stored in the found final dimension data table. Among them, the concatenated query conditions include: the association primary key of the current fact data matches the logical primary key of the current dimension data; the update time of the queried dimension data is less than or equal to the occurrence time of the current fact data, and only the dimension data with the dimension version update time closest to the occurrence time of the current fact data (that is, the reverse index is 1) is associated.
[0047] Specifically, when querying the final dimension data table according to the concatenated conditions, first, the corresponding logical primary key is searched according to the association primary key of the current fact data and the defined association relationship between the association primary key and the logical primary key, and then the corresponding dimension data table is confirmed according to the found logical primary key. Then, the final dimension data table for association is screened according to the concatenated query conditions. First, it is confirmed that the found dimension data table is the correct dimension data table based on the matching condition between the association primary key and the logical primary key in the concatenated conditions. The correct dimension data table refers to the dimension data that matches the association primary key of the current fact data found according to the defined association relationship between the association primary key of the current fact data and the logical primary key of the current dimension data. Here, those skilled in the art can understand that the found correct dimension data can be multiple versions. After finding the correct dimension data, the version of the dimension data for the final association is determined from the correct dimension data, and the dimension data of the determined version for the final association is used as the associated dimension data.
[0048] Specifically, according to the defined association relationship between the association primary key and the logical primary key, it is judged whether the logical primary key of the found dimension data table matches the association primary key of the fact data to determine whether the found dimension data table is correct. If not, the corresponding dimension data table is re-query according to the current fact data; if it matches, the next condition is screened.
[0049] When performing the screening of the next condition, it is based on the correct dimensional data table queried by satisfying the matching condition of the associated primary key and the logical primary key for further screening. Specifically, according to the condition that the update time of the dimensional data queried in the concatenated query condition is less than or equal to the current fact data occurrence time, the update time of the records in the correct dimensional data table is compared with the current fact data time. If the dimensional data update time is later than the current fact data occurrence time, it is discarded, and the remaining dimensional data table is the dimensional data table that the current fact data should be associated with. Finally, based on the condition in the concatenated condition that only the dimensional data with the closest update time of the dimensional version to the current fact data occurrence time (that is, the reverse index in the dimensional database is 1) is taken, a final dimensional data table for association is confirmed. According to the dimensional data stored in the obtained final dimensional data table, the current real-time data and the stored dimensional data are associated to obtain the associated structured data (that is, Figure 2 the wide table details shown). Among them, generating structured data is carried out in the Flink-DWM layer of the real-time data warehouse. The Flink-DWM layer mainly performs aggregation operations on the data based on Flink-DWD. At the same time, the associated structured data is output (Sideoutput) to the Doris database for storing the detailed wide table.
[0050] After obtaining the structured data (that is, the current fact data is successfully associated), the system will record the association time of the current fact data as the watermark (that is, Figure 3 the watermark shown) and record it under the key in redis. After each successful association of the fact data and the dimensional data, the watermark will be updated under the corresponding key. As the fact data and the dimensional data are continuously associated successfully, the corresponding watermark will also be continuously updated. Among them, the key refers to the unique identifier of the data stored in redis, and the value refers to a collection of multiple data types.
[0051] Furthermore, as shown in Figure 3 when performing data association, dimensional data writing will be synchronized, that is, Figure 2 the change data acquisition (CDC config load) shown. Dimensional data writing means that the dimensional data has been updated at this time (that is, a new version of the dimensional data appears). At this time, based on the recorded watermark, the data association correctness verification will be performed on the fact data that has been successfully associated. Specifically, when performing the data association correctness verification, based on the current dimensional version update, it is judged whether the current dimensional version update time is later than the time recorded by the watermark (that is, the previous fact data association time of the current fact data). When it is judged that the dimensional version update time is later than the previous fact data association time of the current fact data, it is determined that the current dimensional version is the latest version. At this time, the dimensional data of the current dimensional version is written into the dimensional data for storage, that is,Figure 2 Shown Dimension Save
[0052] When it is determined that the dimension version update time is earlier than the previous fact data association time of the current fact data, it is determined that there is an association error between the fact data associated during the period from the current water level line record time to the dimension data update time and the dimension data. At this time, the system will send a rollback notification and asynchronously query the fact data that has been associated during the period from the dimension data update time to the previous fact data association time of the current fact data, and perform re - association. At the same time, update the latest time associated with the fact data whose water level line in redis is for rollback. It should be noted that the above - mentioned method for associating fact data and dimension data is the same as the method for associating fact data and dimension data in the previous text, and will not be elaborated here. At the same time, when performing data association, the status of the fact data with successful association will be cached for a first preset time. After receiving the rollback notification, the corresponding fact data within the corresponding time period will be searched in the cache for re - association. In a possible implementation, the value of the first pre - review time can be set to 15 min to 60 min, preferably 30 min. That is, after the current fact data is successfully associated, the fact data is cached, and the cache time is set to 15 min to 60 min, so that when it is determined that data needs to be rolled back, the data can be directly obtained from the cached fact data for re - association, without having to extract the fact data from the database again, which speeds up the data association rate.
[0053] Furthermore, it should be noted that in order to improve the processing performance of the system, the data association method provided in this disclosure establishes an asynchronous thread pool when performing data association, which is used to process the task of associating fact data and dimension data, and returns the combined data after the association of fact data and dimension data. The asynchronous thread pool contains multiple threads and can be reused, avoiding the system from frequently creating threads multiple times after receiving fact data. At the same time, in order to maintain the order of fact data, an ordered queue is maintained when associating fact data. Only when the previous fact data in the queue is associated and returned can the next data be returned. For example, there are threads numbered 1 - 10 in the ordered queue for processing fact data association tasks. When performing data association, only after thread 1 is associated and returned will thread 2 be returned.
[0054] Accordingly, the present disclosure provides a data association method, including: obtaining current fact data to be associated from source data; searching for corresponding and matching version of dimension data in a dimension database according to the current fact data, where the dimension database stores a dimension data table that records dimension data and version information of the dimension data; and associating the current fact data with the corresponding and matching version of the dimension data. The above data association method records the version information of the dimension data in the dimension data table stored in the dimension database. When performing association, it directly searches for the dimension data with a version matching the current fact data in the dimension database according to the concatenated query conditions. At the same time, after successful association, it will also record the association time of successful association. Through the recorded association time, when updating the dimension data, it further performs data association correctness verification on the fact data and dimension data that have been successfully associated, ensuring the accuracy of data association. In this way, it can achieve accurate real-time association of fact data and dimension data during data storage, improving the accuracy and real-time performance of data association.
[0055] <Apparatus Embodiment>
[0056] Figure 4 FIG. shows a schematic block diagram of a data association apparatus according to an embodiment of the present disclosure. As Figure 4 shown, the apparatus 100 is used for associating fact data and dimension data, including: a data acquisition module 110, a dimension data search module 120, and a data association module 130. Among them, the data acquisition module 110 is configured to obtain current fact data to be associated from source data; the dimension data search module 120 is configured to search for corresponding and matching version of dimension data in a dimension database according to the current fact data, where the dimension database stores a dimension data table that records dimension data and version information of the dimension data; and the data association module 130 is configured to associate the current fact data with the corresponding and matching version of the dimension data.
[0057] <Device Embodiment>
[0058] Figure 5 FIG. shows a schematic block diagram of a data association device according to an embodiment of the present disclosure. As Figure 5 shown, the data association device 200 includes: a processor 210 and a memory 220 for storing executable instructions executable by the processor 210. Among them, the processor 210 is configured to implement the data association method described in any one of the foregoing when executing the executable instructions.
[0059] Here, it should be noted that the number of processors 210 can be one or more. At the same time, in the data association device 200 of the embodiments of the present disclosure, an input device 230 and an output device 240 may also be included. Among them, the processor 210, the memory 220, the input device 230, and the output device 240 may be connected through a bus or in other ways, which is not specifically limited here.
[0060] As a computer-readable storage medium, the memory 220 can be used to store software programs, computer-executable programs, and various modules, such as the programs or modules corresponding to the data association method of the embodiments of the present disclosure. The processor 210 executes various functional applications and data processing of the data association device 200 by running the software programs or modules stored in the memory 220.
[0061] The input device 230 can be used to receive input numbers or signals. Among them, the signal can be a key signal related to the user settings and function control of the device / terminal / server. The output device 240 may include a display device such as a display screen.
[0062] <Embodiment of the storage medium>
[0063] According to the fourth aspect of the present disclosure, a non-volatile computer-readable storage medium is further provided, on which computer program instructions are stored, and when the computer program instructions are executed by the processor 210, the data association method described in any one of the foregoing is implemented.
[0064] The above has described the embodiments of the present disclosure. The above description is exemplary and not exhaustive, and is not limited to the disclosed embodiments. Many modifications and variations are obvious to those of ordinary skill in the art in the technical field without departing from the scope and spirit of the described embodiments. The selection of the terms used herein is intended to best explain the principles of the embodiments, practical applications, or improvements to the technology in the market, or to enable other ordinary skill in the art in the technical field to understand the disclosed embodiments.
Claims
1. A method for data association, characterized in that, For performing the association between factual data and dimensional data, including: Obtaining the current factual data to be associated from the source data; Searching in the dimensional database for the dimension data of the corresponding matching version according to the current factual data; wherein, the dimensional database stores a dimensional data table, and the dimensional data table records the dimensional data and the version information of the dimensional data; Associating the current factual data with the dimension data of the corresponding matching version; After obtaining the structured data, the system records the association time of the current factual data as the water level line; the structured data refers to the data after the successful association of the current factual data; When writing dimensional data, if it is determined that the update time of the version of the dimensional data is earlier than the water level line, perform a rescrubbing operation on the factual data associated during the time period from the update time of the dimensional data to the time recorded by the water level line, and re-associate the factual data associated during the time period; 2. The method according to claim 1, wherein When searching in the dimensional database for the dimension data of the corresponding matching version according to the current factual data, including: Extracting the association primary key from the current factual data, and obtaining the concatenated query condition according to the association primary key; According to the concatenated query condition, searching in the dimensional data table recorded in the dimensional database for the version of the matching dimension data; 3. The method according to claim 2, characterized in that, The concatenated query condition includes: the update time of the version of the dimensional data is less than or equal to the occurrence time of the current factual data, and the update time of the version of the dimensional data is the closest to the occurrence time of the current factual data; 4. The method according to claim 3, characterized in that, The concatenated query condition further includes: the association primary key matches the primary key of the dimensional data; 5. The method according to any one of claims 1 to 4, characterized in that, When searching in the dimensional database for the dimension data of the corresponding matching version according to the factual data, further including: Judging whether the update time of the version of the dimensional data is later than the water level line; When it is determined that the update time of the version of the dimensional data is later than the water level line, determining that the version of the dimensional data is the latest version, and performing the operation of writing the dimensional data into the database; 6. A data association device, characterized in that, For performing the association between factual data and dimensional data, including: A data acquisition module, for obtaining the current factual data to be associated from the source data; A dimension data search module, for searching in the dimensional database for the dimension data of the corresponding matching version according to the current factual data; wherein, the dimensional database stores a dimensional data table, and the dimensional data table records the dimensional data and the version information of the dimensional data; A data association module, for associating the current factual data with the dimension data of the corresponding matching version; After obtaining the structured data, the system will record the association time of the current factual data as the water level line; wherein, the structured data refers to the data after the successful association of the current factual data; When writing dimensional data, if it is determined that the update time of the version of the dimensional data is earlier than the water level line, perform a rescrubbing operation on the factual data associated during the time period from the update time of the dimensional data to the time recorded by the water level line, and re-associate the factual data associated during the time period; 7. The device according to claim 6, characterized in that, When looking up the dimension data of the corresponding matching version in the dimension database according to the said factual data, the dimension data lookup module includes: A dimension data update time comparison module, configured to determine whether the update time of the version of the dimension data is later than the water level line; A dimension data writing module, configured to, when it is determined that the update time of the version of the dimension data is later than the water level line, determine that the version of the dimension data is the latest version, and perform the operation of writing the dimension data into the database.
8. A data association device, characterized in that, including: a processor; a memory for storing processor-executable instructions; Wherein, the processor is configured to implement the method according to any one of claims 1 to 5 when executing the executable instructions.
9. A non-volatile computer-readable storage medium having computer program instructions stored thereon, characterized in that, The computer program instructions implement the method according to any one of claims 1 to 5 when executed by the processor.