A Flink method, system, device, and storage medium for implementing delayed association of CDC data dimension tables

Abstract

The present invention discloses a Flink method, system, device, and storage medium for implementing delayed association of CDC data dimension tables. Both fact table and dimension table data are synchronized from a database, and dimension table data updates are implemented. When the dimension table synchronization speed is slower than the fact table, data association can be successfully implemented within a certain timeframe. This method can also address the issue of data disorder when multiple dimension tables with the same primary key are associated with each other, and if the first association fails while the subsequent ones succeed, the data is associated with the fact table synchronized via CDC. This method primarily addresses business scenarios where a database is used for data storage and CDC technology is used to collect data for association. The method reduces the delay in dimension table data updates from hours to seconds. It can ensure the accuracy of data association within a specific timeframe when dimension table data is later than fact table data. When data association fails within a specific timeframe, it can collect abnormal data to ensure data integrity.

Description

Technical Field

[0001] The present invention relates to the field of real-time computing, and in particular to a Flink method, system, device, and storage medium for implementing delay association of CDC data dimension tables. Background Art

[0002] CDC (Change Data Capture) is the process of identifying and capturing changes to data in a database (including inserts, updates, and deletes of data or tables), fully recording these changes in the order they occur, and transmitting them to downstream processes or systems in real time via messaging middleware. In this way, CDC provides efficient, low-latency data transmission to data warehouses, allowing information to be converted and delivered to analytical applications in a timely manner. Sequence is crucial; misordering the same primary key can affect the accuracy of the final data.

[0003] Fact Table: Fact tables are used to record and organize data for analysis and reporting. They primarily contain numerical data and are used to answer business questions such as sales, volume, and profit. They are typically large tables because they contain a large amount of numerical data.

[0004] Dimension tables: A dimension table is a table in a data warehouse that stores dimensional information related to the business. Dimension tables are typically small because they contain descriptive data. They are used to analyze and filter data in fact tables, such as time, location, and product.

[0005] Wide Data Tables: Fact tables are often linked to multiple dimension tables because business problems often require analysis from different perspectives. Using the dimension table primary key information recorded in the fact table, the dimension content for other analyses is linked and redundantly integrated into the data, facilitating direct data analysis later and reducing linking time.

[0006] In common dimension table association scenarios, fact table data, primarily for log types, is stored in append-only mode in Kafka message queues. Dimension table information is selected based on data volume, with small data volumes stored in a database and large data volumes stored in HBase. To process data into wide tables, Kafka data is consumed, and dimension table information is then retrieved based on the associated primary key. This information is then added to the current data and passed downstream. However, this solution typically updates dimension table data offline through scheduled updates, primarily for scenarios with infrequent dimension updates.

[0007] Chinese patent application No. 202211457131.3 discloses a FlinkSQL dimension table join implementation method, device, and medium. This method primarily monitors the middleware for new data. If so, it obtains an SQL source table from the middleware; generates enterprise service bus (ESB) interface information based on the SQL source table; queries and obtains dimension table data based on the enterprise service bus (ESB) interface information; and performs preset operations on the dimension table data, obtains the processing results, and stores them in a preset location to implement a dimension table join. Although this method improves the timeliness of dimension table data for general dimension table association scenarios, it still cannot meet the requirements for scenarios where both fact tables and dimension table data are generated in the database and linked via CDC synchronization. Furthermore, during the synchronization process, dimension table data may lag behind fact table data, resulting in the dimension table data not being fully prepared when the fact table is linked. Currently, there is still no good solution for the problem of unlinked data statistics. Summary of the Invention

[0008] The present invention aims to address the shortcomings of the existing technology by proposing a Flink method, system, device, and storage medium for implementing delayed association of CDC data dimension tables. The method addresses the issue of synchronizing both fact table and dimension table data from a database and slow dimension table data updates. The method also addresses the issue of data association failures within a certain timeframe when dimension table synchronization is slower than fact table synchronization. The method also addresses the issue of data disorder caused by the failure of the first association to join multiple dimension tables with the same primary key when the first association fails, while subsequent associations succeed. The method also addresses the issue of counting association failures for unconventional data types.

[0009] The objective of the present invention is achieved through the following technical solution: a Flink method for implementing delayed association of CDC data dimension tables, the method comprising the following steps:

[0010] (1) Generate and store fact table and dimension table data in the business database;

[0011] (2) Synchronizing business data to the Kafka queue: Streaming data synchronization is achieved through the Flink framework and CDC technology, and the change logs of dimension tables and fact tables are collected and stored in different Kafka topics.

[0012] (3) Dimension table data synchronization: Configure Flink jobs to consume dimension table data in Kafka and output it to the dimension table database.

[0013] (4) Consuming real-time table data: By configuring Flink jobs, consume data from the topic of the fact table and the topic of the delay queue respectively;

[0014] (5) Associating dimension tables based on conditions: After receiving the data in step 4, the dimension information is associated with the dimension table database based on the dimension table primary key, and the associated conditions are processed and added to the data as the associated primary key;

[0015] (6) Success judgment of the association dimension table: Determine whether the association is successful based on the query results. If the association is successful, the data continues to be processed. If the association fails, a retry operation is required to store the data in the cache system.

[0016] (7) The associated cache mark exists: After the dimension table is successfully associated, the cache system is checked based on the associated primary key to see if the cache primary key exists. If not, it means that the data has been successfully associated and is directly output to the wide table queue. If it exists, it means that there is data that has failed to be associated. In order to ensure the order of data with the same primary key is consistent, further judgment processing is required. When there is no cache system sequence number in the data, it means that the sequence of this data is later than the data in the cache system and is output to the cache system. If there is a cache system sequence number in the data, it is determined whether the sequence number is less than the current sequence number of the cache system. If it is less, it means that the sequence is earlier than the cache system data and the data is directly output to the wide table queue. If it is greater, it needs to continue to be output to the cache system to ensure that the previous data are successfully associated and output.

[0017] (8) Wide table Kafka queue: The final data after the fact table data is associated with the dimension table information is output to the Kafka Topic.

[0018] Furthermore, in step (3), the Flink job keeps running. As long as there is a change in the dimension table data in the business table, the changed data can be synchronized to the dimension table database of the analysis system in real time.

[0019] Furthermore, in step (6), the cache system data structure consists of a primary key list and a sorted queue, where the primary key information includes the minimum time in the same primary key queue. Each primary key corresponds to an ordered queue, and the order is sorted by time; when the minimum time data is removed, the time in the primary key is also refreshed to take the minimum value in the queue.

[0020] Furthermore, in step (6), the expiration primary key is obtained from the cache system through the timing program, and the expiration data is obtained based on the primary key. The number of retries in the data is compared with the maximum number of retries in the configuration. If the data has not reached the maximum number of retries, the next expiration time in the data needs to be updated according to the configured retry time interval, and the data is output to the delay queue; if it is determined that the data has reached the maximum number of retries, the data is output to the timeout queue.

[0021] In a second aspect, the present invention also provides a Flink system for implementing delayed association of CDC data dimension tables, which includes a business data synchronization module, a dimension table data synchronization module, a consumption real-time table data module, a conditional dimension table association module, a dimension table association success judgment module, a module for judging whether an association cache tag exists, and a wide table Kafka queue module.

[0022] The business data synchronization module is used to generate and store fact table and dimension table data in the business database, implement streaming data synchronization through the Flink framework and CDC technology, and collect dimension table and fact table change logs into different Kafka topics respectively;

[0023] The dimension table data synchronization module is used to configure Flink jobs to consume dimension table data in Kafka and output it to the dimension table database.

[0024] The real-time table data consumption module is used to consume data from the topic of the fact table and the topic of the delay queue by configuring Flink jobs;

[0025] The conditional dimension table association module is used to associate dimension information from the dimension table database according to the dimension table primary key after receiving the data from the consumption real-time table data module, and add the associated conditions as the associated primary key to the data;

[0026] The association dimension table success judgment module is used to judge whether the association is successful according to the query result. If the association is successful, the data continues to perform subsequent processing. If the association fails, a retry operation needs to be performed and the data is stored in the cache system.

[0027] The module for judging whether the associated cache mark exists is used to check the cache system according to the associated primary key after the dimension table is successfully associated. If the cache primary key does not exist, it means that the data has been successfully associated and is directly output to the wide table queue; if it exists, it means that there is data that has failed to be associated. In order to ensure the consistency of the order of data with the same primary key, further judgment processing is required. When there is no cache system sequence number in the data, it means that the sequence of this data is later than the data in the cache system and is output to the cache system; if the cache system sequence number exists in the data, it is judged whether the sequence number is less than the current sequence number of the cache system. If it is less, it means that the sequence is earlier than the cache system data and the data is directly output to the wide table queue. If it is greater, it needs to continue to be output to the cache system to ensure that the previous data are successfully associated and output;

[0028] The wide table Kafka queue module is used to output the final data after associating the fact table data with the dimension table information to the Topic of Kafka.

[0029] In a third aspect, the present invention further provides a device for implementing delay association of CDC data dimension tables in Flink, comprising a memory and one or more processors. The memory stores executable code, and when the processor executes the executable code, it implements the method for implementing delay association of CDC data dimension tables in Flink.

[0030] In a fourth aspect, the present invention further provides a computer-readable storage medium having a program stored thereon. When the program is executed by a processor, the program implements the Flink method for implementing delay association of CDC data dimension tables.

[0031] The beneficial effects of the present invention are as follows: the present invention mainly applies to business scenarios where a database is used for data storage and CDC technology is used to collect data for association. The update delay of dimension table data is reduced from hours to seconds. The present invention can ensure the accuracy of data association when dimension table data is later than fact table data within a specific time range. When data association fails within a specific time range, the present invention can collect abnormal data to ensure data integrity. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. 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 creative work.

[0033] Figure 1 This is a flow chart of a Flink method for implementing delayed association of CDC data dimension tables in the present invention;

[0034] Figure 2 It is a schematic diagram of the data structure of the cache system of the present invention;

[0035] Figure 3 This is a structural diagram of a Flink device for implementing CDC data dimension table delay association according to the present invention. DETAILED DESCRIPTION

[0036] The specific embodiments of the present invention are further described in detail below with reference to the accompanying drawings.

[0037] like Figure 1 As shown, the present invention provides a Flink method for implementing delayed association of CDC data dimension tables, and the specific steps are as follows:

[0038] 1. Business Objective: Fact table and dimension table data is generated and stored in the business database. This data needs to be synchronized from the business system to the analysis system and processed. After data processing, the dimension table data is linked to form a wide table. The analysis system (ClickHouse, Presto, Doris) then writes SQL based on the wide table data to perform ad hoc queries and business analysis.

[0039] 2. Synchronize business data to the Kafka queue: Streaming data synchronization is achieved through the Flink framework (real-time stream processing engine) and CDC technology (data changes generate operation logs and data recovery is based on operation logs). Change logs for dimension tables and fact tables are collected separately and stored in different Kafka topics. CDC technology minimizes the impact on the business database system while maximizing data synchronization time. Synchronizing data to Kafka buffers fluctuations in data volume and provides subscriptions for multiple business parties, enabling multiple users to synchronize a single piece of data, avoiding duplication of development and reducing system pressure. Dimension tables and fact tables are stored in separate topics to reduce dimension table data synchronization time. Because dimension table data volumes are generally small and fact table data volumes are large, a trade-off must be made between latency and throughput. Prioritizing reducing latency for dimension table data and increasing system throughput for fact tables is crucial.

[0040] 3. Dimension table data synchronization: Configure a Flink job to consume dimension table data from Kafka and output it to the dimension table database. The Flink job runs continuously, and any changes to dimension table data in the business table are synchronized to the analysis system's dimension table database in real time.

[0041] 4. Consuming real-time table data: By configuring Flink jobs, data is consumed from the topic of the fact table and the topic of the delay queue. In the stream processing framework, the collected data is passed to downstream operators in real time. The delay queue is a Kafka message queue used to store delayed data. Because CDC-type log data is processed, there are strict requirements for the order of data with the same primary key. For example, adding a piece of data generates a piece of data marked as I. Updating a piece of data is equivalent to deleting the original data and adding a new piece of data, resulting in two pieces of data, marked as D and I respectively. Deleting a piece of data generates a piece of data marked as D. Similarly, data output to the dimension table database is also processed according to the marking process for addition, deletion, and modification operations. Therefore, if the order is disrupted, the results will be inaccurate.

[0042] 5. Associate the dimension table based on the conditions: After receiving the data in step 4, associate the dimension information from the dimension table database based on the dimension table primary key, and add the associated conditions as the associated primary key to the data after processing.

[0043] 6. Success judgment of associating dimension tables: In the program, determine whether the association is successful based on the query results. If the association is successful, the data continues to be processed. If the association fails, a retry operation is required to store the data in the cache system.

[0044] Cache system data structure such as Figure 2 As shown, it consists of a primary key list and a sorted queue. The primary key information includes the minimum time in the queue with the same primary key. Each primary key corresponds to an ordered queue, which is sorted by time. When the minimum time data is removed, the time in the primary key is refreshed to take the minimum value in the queue.

[0045] 7. Scheduled task data retrieval: Through the timing program, obtain the expiration primary key from the cache system, and obtain the expiration data based on the primary key.

[0046] 8. Reaching the maximum number of retries: After obtaining the expiration data in step 7, compare the number of retries in the data with the maximum number of retries in the configuration. If the data has not reached the maximum number of retries, the next expiration time in the data needs to be updated according to the configured retry interval, and the data is output to the delay queue; if it is determined that the data has reached the maximum number of retries, the data is output to the timeout queue.

[0047] 9. Timeout Kafka queue: Each piece of data is associated with the number of retries and the retry interval. If the conditions are met, the failed data is collected for analysis.

[0048] 10. Kafka delay queue: When data meets the retry conditions, it is stored in this queue for data processing jobs to continue consuming and processing.

[0049] 11. Association cache mark exists: After the dimension table is successfully associated, the cache system is checked based on the associated primary key to see if the cache primary key exists. If not, it means that the data has been successfully associated and is directly output to the wide table queue. If it exists, it means that there is data that failed to be associated. To ensure the consistency of the order of data with the same primary key, it is necessary to further perform step 12 for judgment processing.

[0050] 12. Data has a cache mark: When the data does not contain a cache system sequence number, it indicates that the data is later than the data in the cache system and is output to the cache system. If the data contains a cache system sequence number, determine whether the sequence number is less than the current sequence number of the cache system. If so, it indicates that the sequence is earlier than the cache system data and the data is directly output to the wide table queue. If so, it needs to continue to be output to the cache system to ensure that the previous data is successfully associated and output.

[0051] 13. Wide table Kafka queue: The final data after the fact table data is associated with the dimension table information is output to the Kafka Topic.

[0052] The cache system ensures the consistency of data association order. Furthermore, the number of retries and retry intervals ensure that even if dimension table data arrives late in the fact table, it can still be associated with the data. The timeout queue collects data and details specific to failed associations, facilitating data governance.

[0053] Corresponding to the aforementioned embodiment of a method for implementing delay association of CDC data dimension tables in Flink, the present invention also provides an embodiment of a device for implementing delay association of CDC data dimension tables in Flink.

[0054] See also Figure 3 An embodiment of the present invention provides a Flink device for implementing delay association of CDC data dimension tables, including a memory and one or more processors. The memory stores executable code, and when the processor executes the executable code, it is used to implement a Flink method for implementing delay association of CDC data dimension tables in the above embodiment.

[0055] The embodiment of the Flink CDC data dimension table delay association implementation device provided by the present invention can be applied to any device with data processing capabilities, and the device with data processing capabilities can be a device or apparatus such as a computer. The device embodiment can be implemented through software, or through hardware or a combination of software and hardware. Taking software implementation as an example, as a device in a logical sense, it is formed by the processor of any device with data processing capabilities in which it is located reading the corresponding computer program instructions in the non-volatile memory into the memory for execution. From the hardware level, if Figure 3 As shown in the figure, a hardware structure diagram of a device with data processing capability where a Flink CDC data dimension table delay association implementation device is located, except Figure 3 In addition to the processor, memory, network interface, and non-volatile memory shown, any device with data processing capabilities in which the apparatus in the embodiment is located may also include other hardware, generally based on the actual functions of the device with data processing capabilities, which will not be described in detail.

[0056] The implementation process of the functions and effects of each unit in the above-mentioned device is specifically described in the implementation process of the corresponding steps in the above-mentioned method, and will not be repeated here.

[0057] For the device embodiments, since they basically correspond to the method embodiments, the relevant parts can be referred to the partial description of the method embodiments. The device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the present invention. A person of ordinary skill in the art can understand and implement the present invention without inventive work.

[0058] An embodiment of the present invention further provides a computer-readable storage medium having a program stored thereon. When the program is executed by a processor, the program implements a Flink method for implementing delay association of CDC data dimension tables in the above embodiment.

[0059] The computer-readable storage medium may be an internal storage unit of any device with data processing capabilities described in any of the aforementioned embodiments, such as a hard disk or memory. The computer-readable storage medium may also be an external storage device of any device with data processing capabilities, such as a plug-in hard disk, a smart media card (SMC), an SD card, a flash card, etc. equipped on the device. Furthermore, the computer-readable storage medium may also include both an internal storage unit and an external storage device of any device with data processing capabilities. The computer-readable storage medium is used to store the computer program and other programs and data required by any device with data processing capabilities, and may also be used to temporarily store data that has been output or is to be output.

[0060] The above embodiments are used to illustrate the present invention rather than to limit the present invention. Any modifications and changes made to the present invention within the spirit of the present invention and the protection scope of the claims shall fall within the protection scope of the present invention.

Claims

1. A Flink method for implementing delayed association of CDC data dimension tables, characterized in that: The method comprises the following steps: (1) Generate and store fact table and dimension table data in the business database; (2) Synchronizing business data to the Kafka queue: Streaming data synchronization is achieved through the Flink framework and CDC technology, and the change logs of dimension tables and fact tables are collected and stored in different Kafka topics. (3) Dimension table data synchronization: Configure Flink jobs to consume dimension table data in Kafka and output it to the dimension table database; (4) Consuming real-time table data: By configuring Flink jobs, consume data from the topic of the fact table and the topic of the delay queue respectively; (5) Associating dimension tables based on conditions: After receiving the data in step (4), the dimension information is associated with the dimension table database based on the dimension table primary key, and the associated conditions are processed and added to the data as the associated primary key; (6) Success judgment of association dimension table: Determine whether the association is successful based on the query results. If the association is successful, the data will continue to be processed. If the association fails, a retry operation is required and the data is stored in the cache system. (7) The associated cache mark exists: After the dimension table is successfully associated, the cache system is checked based on the associated primary key to see if the cache primary key exists. If not, it means that the data has been successfully associated and is directly output to the wide table queue. If it exists, it means that there is data that has failed to be associated. In order to ensure the order of data with the same primary key is consistent, further judgment processing is required. When there is no cache system sequence number in the data, it means that the sequence of this data is later than the data in the cache system and is output to the cache system. If there is a cache system sequence number in the data, it is determined whether the sequence number is less than the current sequence number of the cache system. If it is less, it means that the sequence is earlier than the cache system data and the data is directly output to the wide table queue. If it is greater, it needs to continue to be output to the cache system to ensure that the previous data is successfully associated and output. (8) Wide table Kafka queue: The final data after the fact table data is associated with the dimension table information is output to the Kafka Topic.

2. A Flink method for implementing delayed association of CDC data dimension tables according to claim 1, characterized in that: In step (3), the Flink job keeps running. As long as the dimension table data in the business table changes, the changed data can be synchronized to the dimension table database of the analysis system in real time.

3. A Flink method for implementing delayed association of CDC data dimension tables according to claim 1, characterized in that: In step (6), the cache system data structure consists of a primary key list and a sorted queue, where the primary key information includes the minimum time in the same primary key queue. Each primary key corresponds to an ordered queue, and the order is sorted by time; when the minimum time data is removed, the time in the primary key is also refreshed to take the minimum value in the queue.

4. A Flink method for implementing delayed association of CDC data dimension tables according to claim 3, characterized in that: In step (6), the expiration primary key is obtained from the cache system through the timing program, and the expiration data is obtained based on the primary key. The retry count in the data is compared with the maximum retry count in the configuration. If the data does not reach the maximum retry count, the next expiration time in the data needs to be updated according to the configured retry time interval, and the data is output to the delay queue; If it is determined that the data has reached the maximum number of retries, the data is output to the timeout queue.

5. A Flink system for implementing delayed association of CDC data dimension tables, characterized by: The system includes a business data synchronization module, a dimension table data synchronization module, a consumption real-time table data module, a conditional dimension table association module, a dimension table association success judgment module, a module for judging whether an association cache tag exists, and a wide table Kafka queue module; The business data synchronization module is used to generate and store fact table and dimension table data in the business database, implement streaming data synchronization through the Flink framework and CDC technology, and collect dimension table and fact table change logs into different Kafka topics respectively; The dimension table data synchronization module is used to configure Flink jobs to consume dimension table data in Kafka and output it to the dimension table database; The real-time table data consumption module is used to consume data from the topic of the fact table and the topic of the delay queue by configuring Flink jobs; The conditional dimension table association module is used to associate dimension information from the dimension table database according to the dimension table primary key after receiving the data from the consumption real-time table data module, and add the associated conditions as the associated primary key to the data; The association dimension table success judgment module is used to judge whether the association is successful based on the query result. If the association is successful, the data continues to be processed. If the association fails, a retry operation needs to be performed and the data is stored in the cache system. The module for judging whether the associated cache mark exists is used to check the cache system according to the associated primary key after the dimension table is successfully associated. If the cache primary key does not exist, it means that the data has been successfully associated and is directly output to the wide table queue; if it exists, it means that there is data that has failed to be associated. In order to ensure the consistency of the order of data with the same primary key, further judgment processing is required. When there is no cache system sequence number in the data, it means that the sequence of this data is later than the data in the cache system and is output to the cache system; if the cache system sequence number exists in the data, it is judged whether the sequence number is less than the current sequence number of the cache system. If it is less, it means that the sequence is earlier than the cache system data and the data is directly output to the wide table queue. If it is greater, it needs to continue to be output to the cache system to ensure that the previous data are successfully associated and output; The wide table Kafka queue module is used to output the final data after associating the fact table data with the dimension table information to the Topic of Kafka.

6. A Flink device for implementing CDC data dimension table delay association, comprising a memory and one or more processors, wherein the memory stores executable code, characterized in that: When the processor executes the executable code, a Flink method for implementing delay association of CDC data dimension tables according to any one of claims 1 to 4 is implemented.

7. A computer-readable storage medium having a program stored thereon, characterized in that: When the program is executed by a processor, a Flink method for implementing delay association of CDC data dimension tables according to any one of claims 1 to 4 is implemented.

Images