A Flink-based Oracle data completion method and apparatus

Abstract

This invention discloses an Oracle data completion method and apparatus based on Flink. The method includes: acquiring original changed data; parsing the change operation type, primary key field data, and change field data from the original changed data; if the change operation type is an insert operation, storing the original changed data as a full-field record with the primary key field data as the key in the Flink state backend, and simultaneously writing the full-field record into the complete data topic of the message queue; if the change operation type is an update operation, searching for the corresponding target full-field record from the Flink state backend with the primary key field data as the key, merging the change field data and the target full-field record into a new full-field record and replacing the target full-field record, and simultaneously writing the new full-field record into the complete data topic of the message queue. This invention achieves real-time and accurate completion of Oracle database change data with only primary key appending logs enabled.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to an Oracle data completion method and apparatus based on Flink. Background Technology

[0002] In enterprise-level real-time data synchronization scenarios, Change Data Capture (CDC) is widely used to collect changed data from Oracle databases and synchronize it to message queue systems such as Kafka. The implementation of CDC relies on Oracle database's supplemental logging feature, which records complete information before and after data changes. Oracle database supplemental logging primarily has three configurations: minimal supplemental logging, primary key supplemental logging, and full-field supplemental logging. Due to database performance and resource management considerations, many enterprise database management standards only allow primary key supplemental logging. Primary key supplemental logging only records information about the table's primary key fields and the fields that have changed. This results in incomplete field information in the data synchronized to the message queue via CDC, severely impacting the business processing of downstream data consumption systems.

[0003] Therefore, how to solve the technical problem of incomplete fields when real-time data is synchronized to the message queue in an Oracle database with only primary key append logs enabled has become a technical problem that urgently needs to be solved by those skilled in the art. Summary of the Invention

[0004] The purpose of this invention is to provide an Oracle data completion method and apparatus based on Flink, which realizes real-time and accurate completion of Oracle database change data with only primary key append logs enabled, thereby ensuring that downstream systems obtain complete and orderly latest data.

[0005] According to one aspect of the present invention, a Flink-based Oracle data completion method is provided, the method comprising:

[0006] The raw change data is obtained from the raw data topic of the message queue; wherein the raw change data is obtained by the change data capture tool from the redo log of the Oracle database with only primary key append logging enabled; The change operation type, primary key field data, and change field data are parsed from the original change data, and the original change data is allocated to the Flink parallel processing instance corresponding to the primary key field data; If the change operation type is an insert operation, the original changed data is stored as a full-field record through the Flink parallel processing instance, with the primary key field data as the key of the full-field record, in the Flink state backend. At the same time, the full-field record is written into the complete data topic of the message queue. If the change operation type is an update operation, then the Flink parallel processing instance uses the primary key field data as the key to find the corresponding target full-field record from the Flink state backend, merges the changed field data and the target full-field record into a new full-field record and replaces the target full-field record, and writes the new full-field record into the complete data topic of the message queue.

[0007] Optionally, after parsing the change operation type, primary key field data, and change field data from the original change data, and allocating the original change data to the Flink parallel processing instance corresponding to the primary key field data, the method further includes: If the change operation type is a deletion operation, the Flink parallel processing instance uses the primary key field data as the key to find the corresponding full-field record from the Flink state backend and deletes it, while writing the original change data into the complete data topic of the message queue.

[0008] Optionally, if the change operation type is an update operation, then by using the Flink parallel processing instance to find the corresponding target full-field record from the Flink state backend with the primary key field data as the key, merging the changed field data and the target full-field record into a new full-field record and replacing the target full-field record, and simultaneously writing the new full-field record into the complete data topic of the message queue, the method further includes: If the target full-field record with the primary key field data as the key is not found in the Flink state backend, an asynchronous query is initiated to the Oracle database to retrieve the original complete record based on the primary key field data; The full-field record obtained by merging the original complete record and the changed field data is stored in the complete data topic of the Flink state backend and the message queue, respectively.

[0009] Optionally, the method further includes: Delete all field records in the Flink state backend that have been stored for longer than a preset time threshold; or... When the total number of full-field records stored in the Flink state backend exceeds a preset threshold, some full-field records are deleted according to the least-used strategy.

[0010] Optionally, the method further includes: Flink's Checkpoint mechanism is used to periodically persist all fields of the records stored in the Flink state backend to a distributed storage object.

[0011] Optionally, the method further includes: When the changed field data contains a large field, the content corresponding to the large field is stored in an external storage object and the external storage index is obtained; When the changed field data is stored in the full field record, the external storage index and the large field associated with it are stored in the full field record.

[0012] To achieve the above objectives, the present invention also provides an Oracle data completion device based on Flink, the device comprising: The acquisition module is used to acquire raw change data from the raw data topic of the message queue; wherein, the raw change data is acquired by the change data capture tool from the redo log of the Oracle database with only primary key append logging enabled; The parsing module is used to parse the change operation type, primary key field data and change field data from the original change data, and allocate the original change data to the Flink parallel processing instance corresponding to the primary key field data; The insertion module is used to store the original changed data as a full-field record and use the primary key field data as the key of the full-field record in the Flink state backend through the Flink parallel processing instance if the change operation type is an insertion operation. At the same time, the full-field record is written into the complete data topic of the message queue. The update module is used to, if the change operation type is an update operation, search for the corresponding target full-field record from the Flink state backend using the primary key field data as the key through the Flink parallel processing instance, merge the changed field data and the target full-field record into a new full-field record and replace the target full-field record, and write the new full-field record into the complete data topic of the message queue.

[0013] Optionally, the device further includes a deletion module for: If the change operation type is a deletion operation, the Flink parallel processing instance uses the primary key field data as the key to find the corresponding full-field record from the Flink state backend and deletes it, while writing the original change data into the complete data topic of the message queue.

[0014] To achieve the above objectives, the present invention also provides a computer device, which specifically includes: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the steps of the Flink-based Oracle data completion method described above.

[0015] To achieve the above objectives, the present invention also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the Flink-based Oracle data completion method described above.

[0016] The Oracle data completion method and apparatus based on Flink provided by this invention can achieve the following beneficial effects: (1) Effectively solve the problem of incomplete fields: Under the constraint that only the primary key append log is enabled in the Oracle database, the incomplete data of the UPDATE operation is completed into a complete record containing all fields through state storage and incremental merging mechanism, and the field integrity rate reaches 100%, which meets the data requirements of the downstream business system; (2) Avoid extra database load: The entire completion process is completed in Flink memory, without the need for additional queries to the Oracle database, without increasing the database load, without affecting the database performance, and fully complies with the enterprise database management specifications; (3) Ensure data real-time performance: The stream processing-based completion method can complete data completion and write it to the message queue in milliseconds, and the end-to-end latency is usually within 10 milliseconds, which meets the performance requirements of real-time data synchronization; (4) Improve the overall system efficiency: The downstream data consumption system does not need to perform additional data query and completion operations, which simplifies the data processing process, reduces the system complexity, and improves the overall data processing efficiency. Attached Figure Description

[0017] Various other advantages and benefits will become apparent to those skilled in the art upon reading the following detailed description of preferred embodiments. The accompanying drawings are for illustrative purposes only and are not intended to limit the invention. Furthermore, the same reference numerals denote the same parts throughout the drawings. In the drawings: Figure 1 This is a schematic diagram of an optional process for the Flink-based Oracle data completion method provided in Implementation Example 1. Figure 2 This is a schematic diagram of the data processing flow for the update operation provided in Example 1; Figure 3 This is a sequence diagram illustrating the Oracle data completion method using a real-time synchronization scenario of a customer information table as an example, provided in Implementation Example 1. Figure 4A complete flowchart of the Flink-based Oracle data completion method provided in Example 1; Figure 5 This is a schematic diagram of an optional component structure of the Flink-based Oracle data completion device provided in Embodiment 2. Figure 6 This is a schematic diagram of an optional hardware structure for the computer device provided in Embodiment 3. Detailed Implementation

[0018] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention. All other embodiments obtained by those skilled in the art based on the embodiments of this invention without inventive effort are within the scope of protection of this invention.

[0019] Example 1 This invention provides a Flink-based Oracle data completion method, applicable to real-time data synchronization scenarios where only primary key append logs are enabled in the Oracle database. This method is executed through the Flink stream processing engine, such as... Figure 1 As shown, the method specifically includes the following steps: Step S101: Obtain raw change data from the raw data topic of the message queue; wherein the raw change data is obtained by the change data capture tool from the redo log of the Oracle database with only primary key appending logs enabled.

[0020] It's important to note that Oracle database supplemental logging primarily has three configurations: minimal supplemental logging, primary key supplemental logging, and full-field supplemental logging. Full-field supplemental logging records changes to all fields in a table, ensuring data synchronization integrity. However, in real-world production environments, enabling full-field supplemental logging can significantly impact the database server, primarily in the following ways: first, it drastically increases redo log storage space consumption, typically increasing storage overhead by 30%-50%; second, it increases CPU resource consumption, affecting overall database performance; and third, it may impact database backup and recovery efficiency. Due to database performance and resource management considerations, many enterprise database management standards only allow primary key supplemental logging. Primary key supplemental logging only records information about the table's primary key fields and fields that have changed. For INSERT operations, it records information for all fields, but for UPDATE operations, it only records information for the primary key field and the updated field; for DELETE operations, it only records information for the primary key field. This results in incomplete fields in data synchronized to the message queue via CDC, particularly in UPDATE operations where information about fields not yet updated is missing, severely impacting the business processing of downstream data consumption systems. This embodiment aims to resolve the technical issue of incomplete fields when real-time data is synchronized to the message queue in an Oracle database scenario where only primary key append logging is enabled.

[0021] In this embodiment, the Oracle database is configured with a primary key appended log level to record data change information occurring in each table of the Oracle database in the redo log. The Change Data Capture (CDC) tool is based on Oracle LogMiner or Debezium. The CDC tool parses the Oracle database's redo log in real time to obtain the raw change data and sends this raw change data to the raw data topic of the message queue. In this embodiment, Kafka is used as an example of the message queue; that is, the raw change data collected by the CDC tool is stored in the raw data topic of Kafka.

[0022] Step S102: Parse the change operation type, primary key field data, and change field data from the original change data, and allocate the original change data to the Flink parallel processing instance corresponding to the primary key field data.

[0023] Specifically, each original change data includes: the change operation type (op), primary key field data including the primary key field and its value (keys), and change field data including all changed fields and their values ​​(data). Additionally, each original change data also includes: the table name representing the table to which the change data belongs (table). The change operation type includes: INSERT, UPDATE, and DELETE operations. When the operation type is INSERT, the change field data includes all fields and their values; when the operation type is UPDATE, the change field data only includes the updated field and its value; when the operation type is DELETE, the change field data only includes the primary key field and its value or is null.

[0024] In this embodiment, multiple Flink parallel processing instances are configured. The original changed data undergoes a keyBy operation based on the primary key field value to ensure that original changed data with the same primary key field is assigned to the same Flink parallel processing instance. Furthermore, for multi-table synchronization scenarios, Flink parallel processing instances can be dynamically allocated based on the data volume and update frequency within each table. For low-frequency updated tables, multiple tables can share the same Flink parallel processing instance, while for high-frequency updated tables, a separate Flink parallel processing instance can be configured.

[0025] Step S103: If the change operation type is an insert operation, the original change data is stored as a full-field record through the Flink parallel processing instance, and the primary key field data is used as the key of the full-field record to the Flink state backend. At the same time, the full-field record is written into the complete data topic of the message queue.

[0026] In this implementation, since the insert operation in primary key appended log mode includes information for all fields, the changed field data itself is already complete. Therefore, the changed field data can be directly stored as a full-field record containing all field information in Flink's ValueState, with the primary key field value used as the state key. The Flink state backend stores the complete record data for INSERT operations and supports data querying and merging during UPDATE operations. In this embodiment, the KeyedState mechanism is used to classify and store each full-field record according to the primary key field value, and each primary key field value has only one unique corresponding full-field record containing all field information in the Flink state backend.

[0027] In addition, the complete data topic of the message queue is used to store the complete field data (i.e., full field records) after being completed by Flink, for use by downstream data consumption systems. In this embodiment, the message queue is illustrated using Kafka as an example. That is, the downstream data consumption system obtains the full field records from the complete data topic of Kafka and performs the corresponding business processing.

[0028] Step S104: If the change operation type is an update operation, then the Flink parallel processing instance uses the primary key field data as the key to find the corresponding target full field record from the Flink state backend, merges the changed field data and the target full field record into a new full field record and replaces the target full field record, and writes the new full field record into the complete data topic of the message queue.

[0029] In the primary key appended log mode, update operations only include information about the primary key field and the field being updated, but not information about fields that were not updated. Therefore, it is necessary to first find the corresponding historical complete record (i.e., the target full-field record) from the Flink state backend based on the value of the primary key field, and then update the historical complete record by changing the field data to obtain a new complete record (i.e., a new full-field record). In addition, since each primary key field value can only store one complete record in the Flink state backend, the historical complete record needs to be replaced with a new complete record.

[0030] Specifically, such as Figure 2 As shown, fields can be merged to update all-field records in the following way: (1) Obtain the complete historical record in the Flink state backend, denoted as old_record; (2) Retrieve the changed field data from the UPDATE message, denoted as update_fields; (3) Execute the field merging algorithm: new_record = old_record.copy() for field in update_fields: new_record[field] = update_fields[field] (4) Update the merged complete record new_record back to the Flink state backend; (5) Write new_record into the Kafka full data topic.

[0031] In this embodiment, when there are multiple version conflicts between the changed field data and the same field in the target full field record (such as multiple updates of the same field in a short period of time), the "timestamp priority" rule is applied: the generation timestamp of the changed data is used as the standard, and only the field value corresponding to the latest timestamp is retained.

[0032] Preferably, step S104 specifically includes: If the change operation type is an update operation, the Flink parallel processing instance uses the primary key field data as the key to search for the corresponding target full-field record from the Flink state backend. The target full-field record is then categorized by data type into fixed-length numeric, variable-length character, and large-field indexed fields, and stored uniformly in the Flink off-heap memory area. A fixed memory base address and offset are allocated to each type of field, thus constructing a structured mapping table between fields and memory addresses. Furthermore, a composite version stamp, composed of a microsecond-level database-generated timestamp and an auto-incrementing logical operation sequence number, is configured for each changed data and the full-field record in the Flink state backend. The composite version stamp of the changed data and the composite version stamp of the target full-field record are verified bit-by-bit by bit, including the timestamp and sequence number. Only when the timestamp value of the changed data is greater than that of the target full-field record, or when the timestamp values ​​are equal and the sequence number value is greater than that of the target full-field record, is the result verified via Java. The Unsafe class calls the CPU's underlying CAS atomic instruction to locate the off-heap memory base address and offset corresponding to the changed field data based on the structured mapping table between the fields and memory addresses. It then performs an atomic data overwrite operation on the memory region pointed to by the changed field. The memory region corresponding to the unchanged field retains its original value. After the overwrite is completed, the composite version stamp of the target full-field record is synchronously updated to the composite version stamp of the changed data. The target full-field record and the changed field data are then merged into a new full-field record and replaced with the target full-field record. At the same time, the new full-field record is written into the complete data topic of the message queue.

[0033] In this embodiment, targeted optimizations are made to address the technical problems of traditional processing methods for merging update operation fields when Oracle only enables primary key append logs. These problems include heap memory GC overhead, low processing efficiency due to synchronization lock contention, data distortion caused by reverse or duplicate updates due to single-dimensional version verification, high overhead of whole-row memory copying, and inaccurate field positioning. Specifically, off-heap memory storage combined with a structured mapping table of fields and memory addresses avoids the impact of heap memory GC on the processing flow, while achieving accurate memory positioning of changed fields and completely eliminating the memory overhead caused by whole-row copying. By calling the CPU's underlying CAS atomic instruction through the Java Unsafe class, lock-free atomic data overwriting is achieved, solving the efficiency problem of synchronization lock contention from the bottom layer. The timestamp and sequence number bits of the composite version stamp are checked bit by bit in a two-dimensional manner, which can accurately identify old versions and duplicate changed data, avoiding data distortion caused by invalid overwriting. Synchronously updating the version stamp after overwriting ensures version consistency of all field records. Ultimately, this achieves precise and efficient merging of fields in update operations, significantly reducing memory and operational overhead, greatly improving data processing efficiency in high-concurrency scenarios, ensuring the temporal consistency and accuracy of the completed full-field records and the data status of the Oracle database, and ensuring that the changed data of update operations is accurately completed into complete records without increasing the database load, thus meeting the complete data requirements of downstream systems.

[0034] It should also be noted that in practical applications, primary key field data may include multiple fields and their corresponding values. In this case, the values ​​of multiple primary key fields need to be combined into a composite key to serve as the key for state storage. In addition, when merging fields, it is necessary to ensure the integrity and consistency of all primary key fields.

[0035] Furthermore, after step S102, the method further includes: If the change operation type is a deletion operation, the Flink parallel processing instance uses the primary key field data as the key to find the corresponding full-field record from the Flink state backend and deletes it, while writing the original change data into the complete data topic of the message queue.

[0036] In this embodiment, when the change operation type is a delete operation, the full-field record corresponding to the value of the primary key field is deleted from the Flink state backend to free up storage space, and the DELETE message is directly written to the complete data topic of Kafka, thereby notifying the downstream data consumption system that the record has been deleted.

[0037] Furthermore, such as Figure 2 As shown, step S104 further includes: Step A1: When no target full-field record with the primary key field data is found in the Flink state backend, an asynchronous query is initiated to the Oracle database to retrieve the original complete record based on the primary key field data; For example, an asynchronous query can be initiated to an Oracle database through the JDBC (Java Database Connectivity) connector; Step A2: The full-field record obtained by merging the original complete record and the changed field data is stored in the complete data topic of the Flink state backend and the message queue, respectively.

[0038] In this embodiment, when the corresponding complete historical record cannot be found in the Flink state backend according to the primary key field data (usually occurring during the first system startup or after the state data expires), a cold start process needs to be performed in accordance with the above steps A1 to A2.

[0039] Alternatively, other methods of cold start processing can be used, such as... Figure 2 As shown, for example, the changed field data can be directly written into the complete data topic of the message queue and marked as "incomplete data" so that the downstream data consumption system can decide how to handle it. At the same time, the changed field data is stored in the Flink state backend and will be further completed by subsequent UPDATE operations. Another example is to use a preloading mechanism to query the existing data in the Oracle database in batches and load it into the Flink state backend when the Flink task starts, thereby avoiding the cold start problem at runtime.

[0040] Furthermore, after step S104, the method further includes: Delete all field records in the Flink state backend that have been stored for longer than a preset time threshold; or... When the total number of full-field records stored in the Flink state backend exceeds a preset threshold, some full-field records are deleted according to the least-used strategy; or, Flink's Checkpoint mechanism is used to periodically persist all fields of the records stored in the Flink state backend to a distributed storage object.

[0041] In this embodiment, to avoid memory overflow caused by the infinite growth of all field records in the Flink state backend, the following state management mechanism is implemented: (1) TTL (Time to Live) configuration: Set TTL for state data, such as 24 hours; State data exceeding TTL is automatically cleaned up to balance memory usage and completion capability; (2) State cleanup triggering conditions: Periodic check: Scan the state every certain time period (such as 1 hour) and clean up expired data; Capacity limit: When the state size exceeds the threshold, the least accessed data is eliminated according to the LRU policy; DELETE trigger: Clean up the corresponding data immediately when a DELETE operation is received; (3) State persistence: Use Flink's Checkpoint mechanism to periodically persist the state to distributed storage objects (such as HDFS, S3) to ensure data reliability and fault recovery capability.

[0042] Furthermore, the method also includes: Step B1: When the changed field data contains a large field, store the content corresponding to the large field to an external storage object and obtain the external storage index; Step B2: When storing the changed field data in the full field record, store the external storage index and the large field associated with it in the full field record.

[0043] In this embodiment, for data tables with large fields such as BLOB and CLOB, it is possible to configure the storage of only the primary key field data and small field data in the changed field data in the Flink state backend, while storing the content corresponding to the large field (the value of the large field) in the external storage object. Only the large field and the external storage index associated with it are stored in the Flink state backend, thereby reducing memory usage and improving processing efficiency.

[0044] This embodiment is applicable to the following typical application scenarios: (1) Real-time risk control of financial transaction system: Real-time synchronization of account information, transaction records, etc. of Oracle core transaction database to big data platform for risk control analysis. Data integrity needs to be guaranteed, but the database does not allow full field logs to be enabled; (2) Real-time analysis of e-commerce order data: Real-time synchronization of order table, product table, etc. to data warehouse for real-time report analysis. Order status is frequently updated, but only primary key append logs can be enabled; (3) Synchronization of IoT device status: Real-time synchronization of device status table to message queue for use by downstream alarm system. Device status is frequently updated. Complete device information is required, but database resources are limited.

[0045] The following, such as Figure 3 As shown, the Oracle data completion method based on Flink proposed in this embodiment will be explained exemplarily using the real-time synchronization scenario of the customer information table as an example: Suppose there is a customer information table called customer in an Oracle database, which includes the following fields: id (primary key field), name, age, address, and phone, and the Oracle database only has primary key append logging enabled.

[0046] Sequence 1: INSERT operation At time T1, the Oracle database executes the following INSERT statement: INSERT INTO customer VALUES (1, 'Zhang San', 30, 'Beijing', '1234567890'); The CDC tool captures the complete INSERT message (containing all fields); After receiving the record, Flink will store the complete record in the state: {id:1 ->{name:'Zhang San', age:30,address:'Beijing', phone:'1234567890'}}; Flink writes the complete record to a complete data topic in Kafka.

[0047] Time Sequence 2: UPDATE Operation At time T2, the Oracle database executes the following UPDATE statement: UPDATE customer SET age=31 WHERE id=1; The CDC tool only captured the primary key appended log: {op:'U', keys:{id:1}, data:{age:31}} (missing the name, address, and phone fields). After receiving the data, Flink retrieves the complete historical record from the status based on id=1; Flink performs field merging: it updates age to 31 while keeping other fields unchanged; The complete record is: {id:1, name:'Zhang San', age:31, address:'Beijing', phone:'1234567890'}; Flink updates the state and writes the complete record to the full data topic in Kafka.

[0048] Sequence 3: Another UPDATE operation At time T3, the Oracle database executes the following UPDATE statement: UPDATE customer SET phone='1111111111'WHERE id=1; CDC capture: {op:'U', keys:{id:1}, data:{phone:'1111111111'}}; Flink queries the state and merges them to get: {id:1, name:'Zhang San', age:31, address:'Beijing', phone:'11111111111'}; Flink updates the state and writes it to the full data topic in Kafka.

[0049] Sequence 4: DELETE operation; At time T4, the Oracle database executes the DELETE statement: DELETE FROM customer WHERE id=1; CDC capture: {op:'D', keys:{id:1}}; After Flink receives the record, it removes the complete record with id=1 from the state. Flink writes DELETE messages to a full data topic in Kafka.

[0050] like Figure 4The diagram shows the complete process of the Oracle data completion method based on Flink provided in this embodiment. This embodiment aims to solve the technical problem of incomplete fields when real-time data is synchronized to the message queue in the scenario where only primary key append logs are enabled in the Oracle database. Specifically, it includes: (1) solving the problem of incomplete data fields in UPDATE operations: when only primary key append logs are enabled in the Oracle database, the UPDATE operation can only capture the information of the primary key field and the modified field, resulting in the data synchronized to the message queue lacking the information of the unmodified fields, and the downstream data consumption system cannot obtain complete record information; (2) solving the problem of being unable to break through database management restrictions: in enterprise-level production environments, due to performance and resource control requirements, full field append logs cannot be enabled, and the existing solution cannot achieve complete data synchronization without violating database management specifications; (3) solving the problem of balancing real-time performance and integrity: the existing solution increases the database load and reduces real-time performance by querying the database through the downstream data consumption system to complete the fields, which cannot meet the millisecond-level data synchronization requirements; (4) solving the problem of ensuring data consistency: in high-concurrency scenarios, how to ensure that the completed data is consistent with the actual database state and avoid business errors caused by data inconsistency. To address the aforementioned issues, this embodiment incorporates a state storage mechanism within the Flink stream processing engine. During real-time data synchronization with an Oracle database that has primary key appending logs enabled, intelligent completion processing is performed. Based on the change operation type, the data stream is divided into three branches for processing: an INSERT branch for adding new data, an UPDATE branch for updating data, and a DELETE branch for deleting data. In the INSERT branch, leveraging Flink's full data caching mechanism in the state backend, Flink's KeyedState is used to store the original change data corresponding to the INSERT operation as a full-field record. A state index is then created using the primary key field value as the key, enabling fast retrieval of full-field records. In the UPDATE branch, considering that the UPDATE operation only contains primary key and change field information, the corresponding full-field record in the Flink state is found using the primary key. The change field is then intelligently merged with the found full-field record to generate a complete record containing all fields, replacing the found full-field record. In the DELETE branch, upon receiving a DELETE operation message, the corresponding record in the Flink state is synchronously cleaned up to ensure consistency between the state data and the actual database data. In addition, this embodiment also adopts a message queue-based data replay mechanism, which rewrites the completed field data into the complete data topic of the message queue to ensure the integrity of the data obtained by the downstream data consumption system, while maintaining the order of the data stream.This embodiment employs lifecycle management of state data, using the TTL (Time To Live) mechanism and LRU (Least Recently Used) strategy to automatically clean up and evict data in the Flink state, balancing memory usage and data completion capabilities.

[0051] This embodiment achieves the following technical effects by intelligently completing incomplete data in the primary key append log at the Flink stream processing layer: (1) Effectively solves the problem of incomplete fields: Under the constraint that only the primary key append log is enabled in the Oracle database, the incomplete data of the UPDATE operation is completed into a complete record containing all fields through state storage and incremental merging mechanism, and the field integrity rate reaches 100%, which meets the data requirements of the downstream business system; (2) Avoids additional database load: The entire completion process is completed in Flink memory, without the need for additional queries to the Oracle database, without increasing the database load, without affecting the database performance, and fully complies with the enterprise database management specifications; (3) Ensures data real-time performance: Based on stream processing The completion method can complete data completion and write it to the message queue in milliseconds. The end-to-end latency is usually within 10 milliseconds, which meets the performance requirements of real-time data synchronization. (4) Improve the overall efficiency of the system: The downstream data consumption system does not need to perform additional data query and completion operations, which simplifies the data processing process, reduces the system complexity, and improves the overall data processing efficiency. (5) Enhance data consistency guarantee: Through state management and life cycle control, ensure the consistency between the completed data and the actual state of the database, and avoid business errors caused by incomplete or inconsistent data. (6) Have good scalability: Based on Flink's distributed processing capability, it can cope with large-scale data synchronization scenarios by increasing parallelism and resources, and supports the processing capability of tens of thousands of records per second.

[0052] Example 2 This invention provides an Oracle data completion device based on Flink, such as... Figure 5 As shown, the device specifically includes the following components: The acquisition module 501 is used to acquire raw change data from the raw data topic of the message queue; wherein the raw change data is acquired by the change data capture tool from the redo log of the Oracle database with only primary key append logging enabled; Parsing module 502 is used to parse the change operation type, primary key field data and change field data from the original change data, and allocate the original change data to the Flink parallel processing instance corresponding to the primary key field data; The insertion module 503 is used to, if the change operation type is an insertion operation, store the original change data as a full-field record through the Flink parallel processing instance and store the full-field record with the primary key field data as the key to the Flink state backend, and write the full-field record into the complete data topic of the message queue. The update module 504 is used to, if the change operation type is an update operation, search for the corresponding target full-field record from the Flink state backend using the primary key field data as the key through the Flink parallel processing instance, merge the changed field data and the target full-field record into a new full-field record and replace the target full-field record, and write the new full-field record into the complete data topic of the message queue.

[0053] Specifically, the device further includes a deletion module for: If the change operation type is a deletion operation, the Flink parallel processing instance uses the primary key field data as the key to find the corresponding full-field record from the Flink state backend and deletes it, while writing the original change data into the complete data topic of the message queue.

[0054] Furthermore, the update module 504 is also used for: If the target full-field record with the primary key field data as the key is not found in the Flink state backend, an asynchronous query is initiated to the Oracle database to retrieve the original complete record based on the primary key field data; The full-field record obtained by merging the original complete record and the changed field data is stored in the complete data topic of the Flink state backend and the message queue, respectively.

[0055] Furthermore, the device also includes a cleaning module for: Delete all field records in the Flink state backend that have been stored for longer than a preset time threshold; or... When the total number of full-field records stored in the Flink state backend exceeds a preset threshold, some full-field records are deleted according to the least-used strategy.

[0056] Furthermore, the device also includes a backup module for: Flink's Checkpoint mechanism is used to periodically persist all fields of the records stored in the Flink state backend to a distributed storage object.

[0057] Furthermore, the insertion module 503 and the update module 504 in the device are also used for: When the changed field data contains a large field, the content corresponding to the large field is stored in an external storage object and the external storage index is obtained; When the changed field data is stored in the full field record, the external storage index and the large field associated with it are stored in the full field record.

[0058] Example 3 This embodiment also provides a computer device, such as a smartphone, tablet computer, laptop computer, desktop computer, rack server, blade server, tower server, or cabinet server (including a standalone server or a server cluster composed of multiple servers), etc., capable of executing programs. Figure 6 As shown, the computer device 60 in this embodiment includes, but is not limited to, a memory 601 and a processor 602 that are communicatively connected to each other via a system bus. It should be noted that... Figure 6 Only a computer device 60 with components 601-602 is shown; however, it should be understood that it is not required to implement all of the components shown, and more or fewer components may be implemented instead.

[0059] In this embodiment, the memory 601 (i.e., the readable storage medium) includes flash memory, hard disk, multimedia card, card-type memory (e.g., SD or DX memory), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, magnetic disk, optical disk, etc. In some embodiments, the memory 601 may be an internal storage unit of the computer device 60, such as the hard disk or memory of the computer device 60. In other embodiments, the memory 601 may also be an external storage device of the computer device 60, such as a plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, etc., equipped on the computer device 60. Of course, the memory 601 may include both the internal storage unit and the external storage device of the computer device 60. In this embodiment, the memory 601 is typically used to store the operating system and various application software installed on the computer device 60. In addition, the memory 601 may also be used to temporarily store various types of data that have been output or will be output.

[0060] In some embodiments, processor 602 may be a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chip. This processor 602 is typically used to control the overall operation of the computer device 60.

[0061] Specifically, in this embodiment, the processor 602 is used to execute the program of the Flink-based Oracle data completion method stored in the memory 601. When the program of the Flink-based Oracle data completion method is executed, it performs the following steps: The raw change data is obtained from the raw data topic of the message queue; wherein the raw change data is obtained by the change data capture tool from the redo log of the Oracle database with only primary key append logging enabled; The change operation type, primary key field data, and change field data are parsed from the original change data, and the original change data is allocated to the Flink parallel processing instance corresponding to the primary key field data; If the change operation type is an insert operation, the original changed data is stored as a full-field record through the Flink parallel processing instance, with the primary key field data as the key of the full-field record, in the Flink state backend. At the same time, the full-field record is written into the complete data topic of the message queue. If the change operation type is an update operation, then the Flink parallel processing instance uses the primary key field data as the key to find the corresponding target full-field record from the Flink state backend, merges the changed field data and the target full-field record into a new full-field record and replaces the target full-field record, and writes the new full-field record into the complete data topic of the message queue.

[0062] For a detailed description of the above method steps, please refer to Example 1. This example will not be repeated here.

[0063] Example 4 This embodiment also provides a computer-readable storage medium, such as flash memory, hard disk, multimedia card, card-type memory (e.g., SD or DX memory), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, disk, optical disk, server, app store, etc., which stores a computer program. When the computer program is executed by a processor, it implements the following method steps: The raw change data is obtained from the raw data topic of the message queue; wherein the raw change data is obtained by the change data capture tool from the redo log of the Oracle database with only primary key append logging enabled; The change operation type, primary key field data, and change field data are parsed from the original change data, and the original change data is allocated to the Flink parallel processing instance corresponding to the primary key field data; If the change operation type is an insert operation, the original changed data is stored as a full-field record through the Flink parallel processing instance, with the primary key field data as the key of the full-field record, in the Flink state backend. At the same time, the full-field record is written into the complete data topic of the message queue. If the change operation type is an update operation, then the Flink parallel processing instance uses the primary key field data as the key to find the corresponding target full-field record from the Flink state backend, merges the changed field data and the target full-field record into a new full-field record and replaces the target full-field record, and writes the new full-field record into the complete data topic of the message queue.

[0064] For a detailed description of the above method steps, please refer to the first embodiment. This embodiment will not repeat the details here.

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

[0066] The sequence numbers of the above embodiments of the present invention are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0067] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method.

[0068] The above are merely preferred embodiments of the present invention and do not limit the scope of the patent. Any equivalent structural or procedural transformations made based on the description and drawings of the present invention, or direct or indirect applications in other related technical fields, are similarly included within the scope of patent protection of the present invention.

Claims

1. An Oracle data completion method based on Flink, characterized in that, The method, applicable to real-time CDC data synchronization scenarios in Oracle databases where only primary key append logging is enabled, but full field append logging is not, includes: The raw change data is obtained from the raw data topic of the message queue; wherein the raw change data is obtained by the change data capture tool from the redo log of the Oracle database with only primary key append logging enabled; The change operation type, primary key field data, and change field data are parsed from the original change data, and the original change data is allocated to the Flink parallel processing instance corresponding to the primary key field data; If the change operation type is an insertion operation that represents the change field data including all fields and their values, then the original change data is stored as a full-field record by the Flink parallel processing instance, and the primary key field data is used as the key of the full-field record in the Flink state backend. At the same time, the full-field record is written into the complete data topic of the message queue. In the Flink state backend, each full-field record is stored according to the value of the primary key field, and each value of the primary key field has only one unique full-field record in the Flink state backend. If the change operation type is an update operation that represents that the changed field data only includes the updated field and its value, then the Flink parallel processing instance uses the primary key field data as the key to find the corresponding target full field record from the Flink state backend, merges the changed field data and the target full field record into a new full field record and replaces the target full field record, and writes the new full field record into the complete data topic of the message queue.

2. The Oracle data completion method based on Flink according to claim 1, characterized in that, After parsing the change operation type, primary key field data, and change field data from the original change data, and allocating the original change data to the Flink parallel processing instance corresponding to the primary key field data, the method further includes: If the change operation type is a deletion operation, the Flink parallel processing instance uses the primary key field data as the key to find the corresponding full-field record from the Flink state backend and deletes it, while writing the original change data into the complete data topic of the message queue.

3. The Oracle data completion method based on Flink according to claim 1, characterized in that, If the change operation type is an update operation that indicates the changed field data only includes the updated field and its value, then the Flink parallel processing instance uses the primary key field data as the key to find the corresponding target full-field record from the Flink state backend, merges the changed field data and the target full-field record into a new full-field record and replaces the target full-field record, and simultaneously writes the new full-field record into the complete data topic of the message queue, further including: If the target full-field record with the primary key field data as the key is not found in the Flink state backend, an asynchronous query is initiated to the Oracle database to retrieve the original complete record based on the primary key field data; The full-field record obtained by merging the original complete record and the changed field data is stored in the complete data topic of the Flink state backend and the message queue, respectively.

4. The Oracle data completion method based on Flink according to claim 1, characterized in that, The method further includes: Delete all field records in the Flink state backend that have been stored for longer than a preset time threshold; or... When the total number of full-field records stored in the Flink state backend exceeds a preset threshold, some full-field records are deleted according to the least-used strategy.

5. The Oracle data completion method based on Flink according to claim 1, characterized in that, The method further includes: Flink's Checkpoint mechanism is used to periodically persist all fields of the records stored in the Flink state backend to a distributed storage object.

6. The Oracle data completion method based on Flink according to claim 1, characterized in that, The method further includes: When the changed field data contains a large field, the content corresponding to the large field is stored in an external storage object and the external storage index is obtained; When the changed field data is stored in the full field record, the external storage index and the large field associated with it are stored in the full field record.

7. An Oracle data completion device based on Flink, characterized in that, The device, applicable to real-time CDC data synchronization scenarios in Oracle databases where only primary key append logging is enabled, but full field append logging is not, comprises: The acquisition module is used to acquire raw change data from the raw data topic of the message queue; wherein, the raw change data is acquired by the change data capture tool from the redo log of the Oracle database with only primary key append logging enabled; The parsing module is used to parse the change operation type, primary key field data and change field data from the original change data, and allocate the original change data to the Flink parallel processing instance corresponding to the primary key field data; The insertion module is used to, if the change operation type is an insertion operation that represents the change field data including all fields and their values, store the original change data as a full-field record through the Flink parallel processing instance, and store the full-field record with the primary key field data as the key in the Flink state backend. At the same time, the full-field record is written into the complete data topic of the message queue. In the Flink state backend, each full-field record is stored according to the value of the primary key field, and each value of the primary key field has only one unique full-field record in the Flink state backend. The update module is used to, if the change operation type is an update operation that indicates that the changed field data only includes the updated field and its value, use the Flink parallel processing instance to find the corresponding target full field record from the Flink state backend with the primary key field data as the key, merge the changed field data and the target full field record into a new full field record and replace the target full field record, and write the new full field record into the complete data topic of the message queue.

8. The Oracle data completion device based on Flink according to claim 7, characterized in that, The device further includes a deletion module for: If the change operation type is a deletion operation, the Flink parallel processing instance uses the primary key field data as the key to find the corresponding full-field record from the Flink state backend and deletes it, while writing the original change data into the complete data topic of the message queue.

9. A computer device, the computer device comprising: A memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that the processor, when executing the computer program, implements the steps of the method according to any one of claims 1 to 6.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.

Images