Data association method and device, equipment and storage medium

Abstract

The invention provides a data association method and device, equipment and a storage medium. The method comprises the following steps: acquiring current fact data needing to be associated from source data; searching dimension data corresponding to the matched version from a dimension database according to the current fact data; wherein a dimension data table is stored in the dimension database, and dimension data and version information of the dimension data are recorded in the dimension data table; and associating the current fact data with the dimension data of the corresponding matched version. According to the data association method, the version information of the dimension data is recorded in the dimension data table stored in the dimension database, and during association, the dimension data of the version matched with the current fact data is directly searched in the dimension database; the fact data and the dimension data can be accurately associated in real time during data storage, and the accuracy and the real-time performance of data association are effectively improved.

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 building a data warehouse for big data, in order to reduce the complexity of data processing, fact data and dimension data are often associated, supplemented and widened. In the field of real-time data processing, when associating fact data and dimension data, there may be association errors 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 of the next day to accurately associate fact data with dimension data and then backfill the data. 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 when storing data has become a problem that needs to be solved urgently by technicians in this field. Summary of the invention

[0003] In view of this, the present disclosure proposes a data association method, apparatus, device and storage medium, which can accurately associate fact data and dimensional data in real time when data is stored.

[0004] According to a first aspect of the present disclosure, a data association method is provided, for associating fact data with dimension data, comprising:

[0005] Get the current fact data that needs to be associated from the source data;

[0006] According to the current fact data, the dimension database is searched for the dimension data of the corresponding matching version; wherein the dimension database stores a dimension data table, and the dimension data and the version information of the dimension data are recorded in the dimension data table;

[0007] The current fact data is associated with the corresponding matching version of the dimension data.

[0008] In a possible implementation, searching the dimension database for the corresponding matching version of dimension data according to the current fact data includes:

[0009] Extracting an associated primary key from the current fact data, and obtaining a concatenation query condition according to the associated primary key;

[0010] According to the concatenation query condition, a matching dimension version is searched from the dimension data table recorded in the dimension database.

[0011] In a possible implementation, the concatenation 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 closest to the occurrence time of the current fact data.

[0012] In a possible implementation, the concatenation query condition further includes: the associated primary key matches the primary key of the dimension data.

[0013] In a possible implementation, when searching for the dimension data of the corresponding matching version in the dimension database according to the fact data, the method 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, the dimension version is determined to be the latest version, and an operation of writing the dimension data into a database is performed.

[0016] In one 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, a flush operation is performed on the fact data that has been associated within a preset time period before the association time of the current fact data, and the fact data that has been associated within the preset time period is re-associated.

[0017] According to a second aspect of the present disclosure, a data association device is provided, which is used to associate fact data with dimension data, including:

[0018] A data acquisition module is used to obtain the current fact data that needs to be associated from the source data;

[0019] A dimension data search module is used to search 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 and version information of the dimension data are recorded in the dimension data table;

[0020] The data association module is used to associate the current fact data with the corresponding matching version of the dimension data.

[0021] In a possible implementation, when searching for a corresponding matching version of dimension data in a dimension database according to the fact data, the dimension data search module includes:

[0022] A dimension data update time comparison module, used to 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;

[0023] The dimension data writing module is used to determine 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 execute the operation of writing the dimension data into the database.

[0024] According to a third aspect of the present disclosure, a data association device is provided, comprising: a processor; and 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, a non-volatile computer-readable storage medium is provided, 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 the current fact data that needs to be associated from the source data; searching for 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; and associating the current fact data with the corresponding matching version of the dimension data. The above-mentioned data association method records the version information of the dimension data in the dimension data table stored in the dimension database, and directly searches for the dimension data of the version matching the current fact data in the dimension database when performing the association, so that the fact data and the dimension data can be accurately associated in real time when the data is stored, which solves the problem of the traditional processing method that when an association error occurs, the zipper table is created through the offline storage of the next day to accurately associate the data and then backfill, which is not real-time, and effectively improves the accuracy of the system in real-time association of fact data and dimension data.

[0027] Further features and aspects of the present disclosure will become apparent from the following detailed description of exemplary embodiments with reference to the attached drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] The accompanying drawings, which are incorporated in and constitute a part of the specification, illustrate exemplary embodiments, features, and aspects of the disclosure and, together with the description, serve to explain the principles of the disclosure.

[0029] Figure 1 A flow chart showing a data association method according to an embodiment of the present disclosure;

[0030] Figure 2 A real-time data warehouse architecture diagram according to an embodiment of the present disclosure is shown;

[0031] Figure 3A flow chart showing a water level mechanism according to an embodiment of the present disclosure;

[0032] Figure 4 A schematic block diagram showing a data association device according to an embodiment of the present disclosure;

[0033] Figure 5 A schematic block diagram of a data association device according to an embodiment of the present disclosure is shown. DETAILED DESCRIPTION

[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 accompanying drawings represent elements with the same or similar functions. Although various aspects of the embodiments are shown in the accompanying drawings, the drawings are not necessarily drawn to scale unless otherwise specified.

[0035] The word “exemplary” is used exclusively herein to mean “serving as an example, example, or illustration.” Any embodiment described herein as “exemplary” is not necessarily to be construed as preferred or advantageous over other embodiments.

[0036] In addition, in order to better illustrate the present disclosure, numerous specific details are given in the following specific embodiments. It should be understood by those skilled in the art that the present disclosure can also be implemented without certain specific details. In some examples, methods, means, components and circuits well known to those skilled in the art are not described in detail in order to highlight the subject matter of the present disclosure.

[0037] <Method Example>

[0038] Figure 1 A data association method according to an embodiment of the present disclosure is shown (ie, Figure 2 Specific process of generating wide table detailed data in the real-time data warehouse architecture shown in FIG. Figure 1 As shown, the method includes the steps of: S1100, obtaining the current fact data that needs to be associated from the source data; S1200, searching the dimension data of the corresponding matching version 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 dimension data of the corresponding matching version.

[0039] Therefore, the data association method provided by the present invention records the version information of the dimension data in the dimension data table stored in the dimension database, and directly searches the dimension data of the version matching the current fact data in the dimension database when performing the association, so that the current fact data can be associated with the correct version of the dimension data in real time. This solves the problem of the traditional processing method that when an association error occurs, the zipper table is created through the offline storage of the next day to accurately associate the data and then backfill, which is not real-time. This effectively improves the accuracy of the system in real-time association of fact data and dimension data.

[0040] It should be noted that the data association method performed in an embodiment of the present disclosure is performed in a real-time data warehouse built on the Kafka platform. It should be further noted that the real-time data warehouse generally uses Flink for data processing when processing data. 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 data association tasks, you need to obtain fact data first. Fact data refers to the specific numerical information generated in business activities. Figure 2 As shown in the figure, the source data stored in the real-time data warehouse built based on Kafka, the fact data is obtained by data ingestion (Dataingestion) in the source data to be stored. When storing, the system will obtain the fact data according to the attributes of the fact data itself and store the fact data. It should be noted that before storing the fact data, the acquired 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 field, and will not be elaborated here.

[0042] After obtaining the fact data, search the dimension database for the corresponding version of the matching dimension data based on the obtained fact data. Dimension data refers to the characteristics or attributes of fact data, which provides different angles for observing data. Before querying, it is necessary to store the dimension data of each version in the dimension database. Specifically, when storing the dimension data, each version of the dimension data 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 each version of the dimension data, and the dimension data of each version in the dimension database is arranged in reverse order according to the update time recorded in the dbTime column, and a reverse index is constructed. Among them, dbTime is the data business time, which is used here to record the update time of the dimension data version.

[0043] It should be noted that when storing dimension data, an external database is imported as a dimension database to store various versions of dimension data. Among them, the dimension database to be selected can be flexibly selected according to the selection principle, and there is no specific limitation. The selection principle is to support index query and data table sorting, and have good writing and query speed. Furthermore, based on the various versions of dimension data stored in the dimension database, the system will set corresponding thresholds to regularly clean up dimension data of dimension versions that exceed the threshold, avoiding the problem of query performance degradation caused by excessive storage of dimension data. Specifically, the threshold can be set to set at least one of the primary key quantity threshold or the time threshold. Among them, the primary key quantity threshold and the time threshold can be flexibly determined according to the selected database, and there is no specific limitation.

[0044] Furthermore, based on the acquired current fact data and the various versions of dimension data 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 associated relationship between the associated primary key and the logical primary key, and store them. In particular, when defining the associated primary key of the current fact data, the logical primary key of the current dimension data, and the associated relationship between the associated primary key and the logical primary key, it can be determined flexibly without specific limitation.

[0045] According to the above, after the dimension data is stored, data association is performed based on the acquired fact data and the dimension data of each version stored in the dimension database. When performing data association, the corresponding matching version of dimension data is first searched from the dimension database, that is, Figure 2 The dimension query step (Dememsion query) shown in the figure. After the dimension data of the corresponding matching version is queried, the current fact data is associated with the dimension data of the corresponding matching version. For details, see Figure 2 As shown, when performing data association, based on the current fact data obtained, the corresponding logical primary key is searched 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 the corresponding dimension data table is confirmed by the searched logical primary key. When there is only a single dimension data table to be searched, the current fact data is associated with the dimension data stored in the searched dimension data table.

[0046] Furthermore, when the dimension data is associated with the current fact data, the corresponding logical primary key is searched from the dimension database according to the associated primary key of the current fact data and the association relationship between the associated primary key and the logical primary key, and the corresponding dimension data table is searched according to the searched logical primary key. When there are multiple dimension data tables found at this time, the final dimension data table is determined according to the splicing query conditions, and finally the current fact data is associated with the dimension data stored in the final dimension data table. Among them, the splicing query conditions include: the associated 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 whose dimension version update time is closest to the occurrence time of the current fact data (that is, the flashback index is 1) is associated.

[0047] Specifically, when the final dimension data table is queried according to the splicing condition, the corresponding logical primary key is first searched according to the associated primary key of the current fact data and the defined association relationship between the associated primary key and the logical primary key, and then the corresponding dimension data table is confirmed according to the searched logical primary key. Then the dimension data table to be finally associated is screened according to the splicing query condition. First, based on the matching condition of the associated primary key and the logical primary key in the splicing condition, it is confirmed that the dimension data table found is the correct dimension data table. The correct dimension data table refers to the dimension data that matches the associated primary key of the current fact data according to the defined association relationship between the associated primary key of the current fact data and the logical primary key of the current dimension data. Here, it can be understood by those skilled in the art that the correct dimension data found can be multiple versions. After the correct dimension data is found, the version of the dimension data to be finally associated is determined from the correct dimension data, and the determined version of the dimension data to be finally associated is used as the associated dimension data.

[0048] Specifically, according to the defined association relationship between the associated primary key and the logical primary key, it is determined whether the logical primary key of the dimension data table found matches the associated primary key of the fact data to determine whether the dimension data table found is correct. If they do not match, the corresponding dimension data table is re-queried based on the current fact data; if they match, the next condition screening is performed.

[0049] When filtering for the next condition, further screening is performed based on the correct dimension data table that meets the matching condition of the associated primary key and the logical primary key. Specifically, according to the condition that the update time of the dimension data queried in the splicing query condition is less than or equal to the occurrence time of the current fact data, the update time of the correct dimension data table record is compared with the current fact data time. If the dimension data update time is later than the current fact data occurrence time, it is discarded, and the remaining dimension data table is the dimension data table that should be associated with the current fact data. Finally, based on the condition in the splicing condition that only the dimension data version update time is closest to the current fact data occurrence time (that is, the reverse index in the dimension database is 1), a final dimension data table for association is confirmed. Based on the dimension data stored in the final dimension data table, the current real-time data and the stored dimension data are associated to obtain the associated structured data (that is, Figure 2 The structured data is generated in the Flink-DWM layer of the real-time data warehouse. The Flink-DWM layer mainly aggregates the data based on Flink-DWD. At the same time, the associated structured data is output (Sideoutput) to the Doris database for detailed wide table storage.

[0050] After obtaining 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 (i.e. Figure 3 The watermark is recorded in the key of redis. After each successful association between fact data and dimension data, the watermark is updated under the corresponding key. As fact data and dimension data are continuously successfully associated, the corresponding watermark is also continuously updated. Among them, key refers to the unique identifier of data stored in redis, and value refers to a collection of multiple data types.

[0051] Further, refer to Figure 3 As shown, when data association is performed, dimension data will be written synchronously, that is, Figure 2 The change data acquisition (CDC config load) shown. The dimension data is written, which means that the dimension data at this time has been updated (that is, a new version of the dimension data has appeared). At this time, based on the recorded watermark, the fact data that has been successfully associated will be checked for data association correctness. Specifically, when checking the correctness of data association, based on the current dimension version update, determine whether the current dimension version update time is later than the time recorded in the watermark (that is, the previous fact data association time of the current fact data). When it is determined that the dimension version update time is later than the previous fact data association time of the current fact data, it is determined that the current dimension version is the latest version. At this time, the dimension data of the current dimension version is written into the dimension data for storage, that is, Figure 2 The dimension save (Dememsion Save) shown.

[0052] When it is determined that the dimension version update time is earlier than the last fact data association time of the current fact data, it is determined that the fact data associated with the dimension data in the time period from the current watermark record time to the dimension data update time has an association error. At this time, the system will send a refresh notification, and asynchronously query the fact data that has been associated in the time period from the dimension data update time to the last fact data association time of the current fact data, and re-associate. At the same time, the watermark in redis is updated to the latest time of the refreshed fact data association. It should be noted that the above-mentioned method of associating fact data with dimension data is consistent with the method of associating fact data with dimension data in the above text, and will not be repeated here. At the same time, when performing data association, the state cache of the successfully associated fact data will be cached for the first preset time. After receiving the refresh notification, the fact data in the corresponding time period will be searched in the cache for re-association. In a possible implementation, the value of the first pre-trial time can be set to 15min~60min, preferably 30min. That is, after the current fact data is successfully associated, the fact data is cached, and the cache time is set to 15 minutes to 60 minutes, so that when it is determined that the data needs to be refreshed, the data can be directly obtained from the cached fact data for re-association, and there is no need to extract the fact data from the database, which speeds up the data association rate.

[0053] It should be further explained that, in order to improve the processing performance of the system, the data association method provided by the present disclosure establishes an asynchronous thread pool when performing data association, which is used to process the task of associating fact data with dimension data, and returns the combined data after the fact data and dimension data are associated. Among them, the asynchronous thread pool contains multiple threads that can be used repeatedly, avoiding the system from frequently creating threads after receiving the fact data. At the same time, in order to maintain the orderliness of the fact data, an ordered queue is maintained when the fact data is associated, and the next data can only be returned when the association of the previous fact data in the queue is completed. For example, there are threads numbered 1-10 in the ordered queue for processing fact data association tasks. When performing data association, thread No. 2 will only be returned after thread No. 1 completes the association and returns.

[0054] Therefore, the present disclosure provides a data association method, including: obtaining the current fact data that needs to be associated from the source data; searching for 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; and associating the current fact data with the corresponding matching version of the dimension data. The above-mentioned data association method records the version information of the dimension data in the dimension data table stored in the dimension database, and when associating, directly searches for the dimension data of the version matching the current fact data in the dimension database according to the splicing query conditions, and also records the association time of the successful association after the association is successful. Through the recorded association time, the fact data and the dimension data that have been successfully associated are further checked for the correctness of the data association when the dimension data is updated, thereby ensuring the accuracy of the data association. In this way, it is possible to achieve accurate association of the fact data and the dimension data in real time when the data is stored, thereby improving the accuracy and real-time performance of the data association.

[0055] <Device Example>

[0056] Figure 4 FIG. 1 is a schematic block diagram of a data association device according to an embodiment of the present disclosure. Figure 4 As shown, the device 100 is used to associate fact data with dimension data, and includes: a data acquisition module 110, a dimension data search module 120, and a data association module 130. The data acquisition module 110 is used to acquire the current fact data to be associated from the source data; the dimension data search module 120 is used to search for the dimension data of the corresponding matching version in the dimension database according to the current fact data; 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; the data association module 130 is used to associate the current fact data with the dimension data of the corresponding matching version.

[0057] <Equipment Embodiment>

[0058] Figure 5 FIG. 1 is a schematic block diagram of a data association device according to an embodiment of the present disclosure. Figure 5 As shown, the data association device 200 includes: a processor 210 and a memory 220 for storing executable instructions of the processor 210. The processor 210 is configured to implement any of the above-mentioned data association methods when executing the executable instructions.

[0059] Here, it should be noted that the number of processors 210 may be one or more. Meanwhile, in the data association device 200 of the embodiment of the present disclosure, an input device 230 and an output device 240 may also be included. The processor 210, the memory 220, the input device 230 and the output device 240 may be connected via a bus or in other ways, which are not specifically limited here.

[0060] The memory 220 is a computer-readable storage medium that can be used to store software programs, computer executable programs, and various modules, such as the program or module corresponding to the data association method of the embodiment 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 may be used to receive input numbers or signals. The signals may be key signals related to user settings and function control of the device / terminal / server. The output device 240 may include display devices such as display screens.

[0062] <Storage Medium Embodiment>

[0063] According to a fourth aspect of the present disclosure, a non-volatile computer-readable storage medium is further provided, on which computer program instructions are stored. When the computer program instructions are executed by the processor 210, any of the above-mentioned data association methods is implemented.

[0064] The embodiments of the present disclosure have been described above, and the above description is exemplary, not exhaustive, and is not limited to the disclosed embodiments. Many modifications and changes will be apparent to those of ordinary skill in the art without departing from the scope and spirit of the described embodiments. The selection of terms used herein is intended to best explain the principles of the embodiments, practical applications, or technical improvements in the market, or to enable other persons of ordinary skill in the art to understand the embodiments disclosed herein.

Claims

1. A method for data association, characterized in that: Used to associate fact data with dimensional data, including: Get the current fact data that needs to be associated from the source data; According to the current fact data, a dimension database is searched for dimension data of a corresponding matching version; wherein the dimension database stores a dimension data table, and the dimension data and version information of the dimension data are recorded in the dimension data table; The current fact data is associated with the corresponding matching version of the dimension data.

2. The method according to claim 1, characterized in that When searching for the dimension data of the corresponding matching version in the dimension database according to the current fact data, it includes: Extracting an associated primary key from the current fact data, and obtaining a concatenation query condition according to the associated primary key; According to the concatenation query condition, a matching dimension version is searched from the dimension data table recorded in the dimension database.

3. The method according to claim 2, characterized in that The concatenation 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 closest to the occurrence time of the current fact data.

4. The method according to claim 3, characterized in that The concatenation query condition also includes: the associated primary key matches the primary key of the dimension data.

5. The method according to any one of claims 1 to 4, characterized in that: When searching the dimension database for the corresponding matching version of dimension data according to the fact data, the method further includes: 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; 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, the dimension version is determined to be the latest version, and an operation of writing the dimension data into a database is performed.

6. The method according to claim 5, characterized in that 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, a flush operation is performed on the fact data that has been associated within a preset time period before the association time of the current fact data, and the fact data that has been associated within the preset time period is re-associated.

7. A data association device, characterized in that: Used to associate fact data with dimensional data, including: A data acquisition module is used to obtain the current fact data that needs to be associated from the source data; A dimension data search module is used to search 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 and version information of the dimension data are recorded in the dimension data table; The data association module is used to associate the current fact data with the corresponding matching version of the dimension data.

8. The device according to claim 7, characterized in that When searching for the dimension data of the corresponding matching version in the dimension database according to the fact data, the dimension data search module includes: A dimension data update time comparison module, used to 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; The dimension data writing module is used to determine 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 execute the operation of writing the dimension data into the database.

9. A data association device, characterized in that: include: processor; a memory for storing processor-executable instructions; Wherein, the processor is configured to implement the method described in any one of claims 1 to 6 when executing the executable instructions.

10. A non-volatile computer-readable storage medium having computer program instructions stored thereon, characterized in that: When the computer program instructions are executed by a processor, the method according to any one of claims 1 to 6 is implemented.

Images