Database table synchronization method and device based on log data, equipment and medium

Abstract

The invention relates to the technical field of big data and the field of financial science and technology, and discloses a database table synchronization method based on log data, which comprises the following steps: recording operation information of a business database table to log data, writing the log data into a message queue, obtaining the log data through a streaming data processing framework, and deserializing the log data into structured data; and extracting operation records, classifying the operation records according to operation type fields, distributing the operation records to processing channels corresponding to the table names, and updating or adjusting the table structure of the target table according to classification, or executing data change operation. Through the log data-based synchronization method, quasi-real-time synchronization from the business database table to the operation data storage module is realized, and the table structure is adaptively adjusted when the business table structure is changed, so that the data consistency problem and the system interruption risk are reduced, the data synchronization efficiency and stability are effectively improved, and the user experience is improved. And the efficient and stable data synchronization requirements of the service are met.

Description

Technical Field

[0001] The present invention relates to the fields of big data technology and financial technology, and in particular to a database table synchronization method, device, equipment and storage medium based on log data. Background Art

[0002] In the financial sector, with the rapid growth of data volume and business complexity, various financial institutions have increasing demands for real-time data processing and synchronization. Especially in the case of multiple business systems running in parallel, ensuring data consistency and timeliness between the business database and the operational data store (ODS) has become a key issue. The traditional T+1 data synchronization solution has certain limitations and is difficult to meet the current financial business's demand for efficient and real-time data synchronization.

[0003] First, the traditional T+1 data synchronization solution usually continuously pulls the database change log (Binlog) on ​​T day, parses it and stores it in a temporary table, and merges the incremental data in the early morning of T+1 day. This method causes data updates to lag and cannot meet business needs in real time. Especially when facing complex multi-table synchronization tasks, multiple task processes need to be established, which not only increases system resource consumption, but also easily causes data consistency problems.

[0004] Secondly, when the business database structure changes (such as adding, updating or deleting fields), the traditional solution requires redefining the target table and re-extracting data, which increases maintenance costs. At the same time, when the database table structure changes frequently, the traditional synchronization method is difficult to effectively respond. This synchronization method that relies on temporary tables and batch merges has gradually become difficult to adapt to the requirements of quasi-real-time data processing in financial services.

[0005] At present, the most commonly used change data capture (CDC) solution in the industry is mainly based on Flink CDC technology to achieve real-time data synchronization. Flink CDC combines streaming data processing tools such as Debezium and reads database Binlog logs in the form of Slave Server, which can support high real-time performance and multi-table synchronization. However, in actual applications, the Flink CDC solution also has certain problems:

[0006] The Flink CDC solution requires direct access to the Binlog files of the production database. However, in financial systems, data security and compliance requirements are high, and direct access to the production database is usually strictly restricted, which poses a great challenge to the implementation of the data synchronization solution.

[0007] Debezium Reader is prone to thread leakage and untimely release of database connections in long-term operation, which may cause performance fluctuations or delays in the synchronization process. This is particularly critical in financial business, which has high requirements for data timeliness.

[0008] When the business table structure changes (such as adding, deleting, or updating attributes), the downstream ODS structure needs to be adjusted dynamically. However, as a streaming job, Flink CDC is difficult to implement adaptive schema changes without interrupting tasks. This makes it more difficult to maintain the financial system in a dynamic environment with multiple tables synchronized, affecting the stability of the system. Summary of the invention

[0009] The main purpose of the present invention is to provide a database table synchronization method, device, equipment and storage medium based on log data, aiming to solve the technical problems that the prior art is difficult to achieve quasi-real-time synchronization of business database tables to operational data storage, and cannot adaptively adjust when the business table structure changes, resulting in insufficient data consistency and system stability.

[0010] To achieve the above object, the present invention provides a database table synchronization method based on log data, comprising:

[0011] Recording the operation information of the business database table into log data, and writing the log data into the message queue;

[0012] Obtain the log data from the message queue through a streaming data processing framework, and deserialize each log data to obtain corresponding structured data;

[0013] Extracting an operation record from the structured data, the operation record including a table name, an operation type field, and a change content;

[0014] Based on the operation type field, classify the operation record into a data definition language operation record and a data operation language operation record;

[0015] Distributing the data definition language operation record and the data manipulation language operation record to the processing channel corresponding to the table name;

[0016] According to the change content in the data definition language operation record, updating or adjusting the table structure of the target table corresponding to the table name in the operation data storage module;

[0017] According to the change content in the data operation language operation record, a corresponding data change operation is performed on the target table corresponding to the table name in the operation data storage module.

[0018] Furthermore, to achieve the above object, the present invention provides a database table synchronization device based on log data, comprising:

[0019] A log recording module, used to record the operation information of the business database table into log data, and write the log data into a message queue;

[0020] A data consumption module, used to obtain the log data from the message queue through a streaming data processing framework, and deserialize each log data to obtain corresponding structured data;

[0021] An operation record parsing module, used to extract operation records from the structured data, wherein the operation records include a table name, an operation type field, and a change content;

[0022] An operation type classification module, used for classifying the operation record into a data definition language operation record and a data operation language operation record based on the operation type field;

[0023] An operation record distribution module, used for distributing the data definition language operation record and the data operation language operation record to the processing channel corresponding to the table name;

[0024] A table structure updating module, used to update or adjust the table structure of the target table corresponding to the table name in the operation data storage module according to the change content in the data definition language operation record;

[0025] The data change module is used to perform corresponding data change operations on the target table corresponding to the table name in the operation data storage module according to the change content in the data operation language operation record.

[0026] Furthermore, to achieve the above-mentioned purpose, the present invention also provides a computer device, which includes a memory, a processor, and a database table synchronization program based on log data stored in the memory and executable on the processor, wherein the database table synchronization program based on log data, when executed by the processor, implements the steps of the database table synchronization method based on log data as described above.

[0027] Furthermore, to achieve the above objectives, the present invention also provides a computer-readable storage medium, on which a database table synchronization program based on log data is stored, and when the database table synchronization program based on log data is executed by a processor, the steps of the database table synchronization method based on log data as described above are implemented.

[0028] Beneficial effects: The present invention relates to the fields of big data technology and financial technology, and discloses a database table synchronization method based on log data, which records the operation information of the business database table into log data, writes the log data into a message queue, obtains the log data through a streaming data processing framework and deserializes it into structured data; extracts the operation records and classifies them according to the operation type field, distributes them to the processing channel corresponding to the table name, updates or adjusts the table structure of the target table according to the classification, or performs data change operations. The present invention realizes quasi-real-time synchronization from the business database table to the operation data storage module through a synchronization method based on log data, and adaptively adjusts the table structure when the business table structure changes, reduces data consistency issues and system interruption risks, effectively improves the efficiency and stability of data synchronization, and meets the business's demand for efficient and stable data synchronization. BRIEF DESCRIPTION OF THE DRAWINGS

[0029] The present invention will be further described below with reference to the accompanying drawings and embodiments, in which:

[0030] Figure 1 A schematic diagram of an application environment of a database table synchronization method based on log data in an embodiment of the present invention;

[0031] Figure 2 It is a flow chart of an embodiment of a method for synchronizing a database table based on log data of the present invention;

[0032] Figure 3 It is a functional module diagram of a preferred embodiment of a database table synchronization device based on log data of the present invention;

[0033] Figure 4 A schematic diagram of the structure of a computer device in one embodiment of the present invention;

[0034] Figure 5 FIG. 4 is another schematic diagram of the structure of a computer device in one embodiment of the present invention. DETAILED DESCRIPTION

[0035] It should be understood that the specific embodiments described herein are only used to explain the present invention, and are not used to limit the present invention.

[0036] The database table synchronization method based on log data provided by the embodiment of the present invention can be applied in Figure 1In an application environment, the user end communicates with the server end through a network. The server end can record the operation information of the business database table to log data through the user end, write the log data into a message queue, obtain the log data through a streaming data processing framework and deserialize it into structured data; extract the operation record and classify it according to the operation type field, distribute it to the processing channel corresponding to the table name, update or adjust the table structure of the target table according to the classification, or perform data change operations. The present invention realizes quasi-real-time synchronization from the business database table to the operation data storage module through a synchronization method based on log data, and adaptively adjusts the table structure when the business table structure changes, reduces data consistency problems and system interruption risks, effectively improves the efficiency and stability of data synchronization, and meets the business's demand for efficient and stable data synchronization. Among them, the user end can be but is not limited to various personal computers, laptops, smart phones, tablet computers and portable wearable devices. The server end can be implemented with an independent server or a server cluster consisting of multiple servers. The present invention is described in detail below through specific embodiments.

[0037] See also Figure 2 , Figure 2 This is a flow chart of an embodiment of a database table synchronization method based on log data provided by the present invention. It should be noted that although a logical sequence is shown in the flow chart, in some cases, the steps shown or described may be performed in a different order than that shown here.

[0038] like Figure 2 As shown, the database table synchronization method based on log data proposed by the present invention includes the following steps:

[0039] S10, recording the operation information of the business database table into log data, and writing the log data into a message queue;

[0040] In this embodiment, the operational behavior of the business database table is continuously monitored to capture various important operational events. This includes operations such as data insertion, updating, and deletion, as well as the creation, modification, and deletion of table structures. This monitoring method can quickly capture events after data operations occur to ensure that data changes can be recorded in the log. Monitoring can be achieved by configuring triggers or using change data capture (CDC) technology. For example, the changes are captured using the database log file (such as Binlog or WAL log), and the captured changes are converted into operational events for subsequent processing and recording.

[0041] After capturing the operation behavior, you need to further determine its specific type to clarify whether the event is an operation on the data content (such as insert, update, delete) or a change in the table structure (such as create, modify, delete). The operation event type is key information in the log record and will be used for subsequent classification and processing. Extract and parse the operation type field from the operation log. Most database log formats contain this information field, which can be used to quickly identify the operation type.

[0042] Extract information from the operation event, including the operation type, the name of the operation table, the fields involved, the operation time, and the executing user, to form a complete operation record, which is convenient for subsequent log classification and synchronization. You can use a parser to extract structured information from the log and store the extracted information in a standardized format of operation records, using structured formats such as JSON or Avro for easy storage and subsequent transmission.

[0043] Convert the extracted operation information into log data in a standardized format to ensure data consistency during transmission, storage, and processing. Use common structured formats such as JSON or Avro to format the extracted information into log data for subsequent message queue transmission and consumption processing.

[0044] Example: In the bank's operational data store (ODS), the table structure of the loan application may be updated as policy changes occur. When the structure of the loan application form changes (such as adding new fields or modifying field types), the system can automatically identify and adjust the corresponding table structure in the ODS to ensure synchronization without interrupting business operations. In addition, transaction data such as increases and decreases in customer accounts are captured and updated to the ODS immediately after the business database table is changed. The financial system can obtain the latest data status in a timely manner, thereby improving response efficiency and reducing the need for manual intervention.

[0045] By continuously monitoring the operation behavior of the business database table, data changes are recorded in the log data, providing basic support for subsequent data synchronization. By accurately capturing and standardizing operation events, the risk of data loss is reduced, and while ensuring data consistency and update timeliness, the reliability and response speed of the system are improved.

[0046] Establish a connection between the log data generation module and the message queue to ensure that the log data can be reliably transmitted to the message queue for subsequent processing modules to subscribe and consume. Use the API provided by the message queue (such as KafkaProducerAPI) to initialize the connection at the application layer and configure the authentication parameters to ensure the stability and security of the connection.

[0047] Through the sending function of the message queue, the structured log data is pushed to the specified topic of the message queue one by one. As a middleware, the message queue realizes the decoupling and storage of log data for subsequent modules to consume and process. Configure the topic (Topic) in the message queue as the storage channel for log data, and use the producer API to push the log data to the topic one by one. This operation is usually performed in chronological order in the message queue so that subsequent modules can consume in order.

[0048] Ensure the order and integrity of log data during the push process to avoid loss or disorder. Since log data often needs to be processed in chronological order, it is important to maintain the order of data during the sending process. You can add sequence marks or timestamps to the log data and use the partitioning mechanism of the message queue to ensure the order of data. The partitioning and replication mechanism built into the message queue can further ensure the integrity of the data.

[0049] During the data writing process, if an abnormal situation such as sending failure occurs, the retry mechanism is started to ensure that the data can be successfully written in the end. Implement a sending module with retry logic. When data writing fails, it will automatically retry or add the data to the pending queue until the data is successfully written. The fault tolerance mechanism of the message queue can further ensure the reliability of log data.

[0050] By writing log data into the message queue, data decoupling and transmission are effectively achieved, providing an efficient data channel for subsequent streaming data processing and data synchronization. It can ensure that log data is reliably transmitted in chronological order, reduce the pressure of direct access to the database, improve the system's data processing capabilities and stability, and reduce the risk of log data loss, providing a complete and stable data source for subsequent modules.

[0051] S20, obtaining the log data from the message queue through a streaming data processing framework, and deserializing each log data to obtain corresponding structured data;

[0052] In this embodiment, a streaming data processing framework (such as Flink or Spark Streaming) establishes a connection with a message queue and subscribes to a log data stream to consume the log data in the queue in real time. This ensures that the data processing framework can obtain and process the data as soon as possible after it is generated. The connection is configured through the consumer API of the message queue, and a specified topic (such as Kafka's Topic or RabbitMQ's Queue) is subscribed, which contains all the log data. After the connection is established, the streaming data processing framework can automatically receive and process the newly pushed log data.

[0053] The streaming data processing framework consumes log data from the message queue one by one, ensuring that each piece of data is passed in and processed in order. Consuming one by one can ensure the orderliness and integrity of the data. Set the consumption strategy so that the streaming data processing framework pulls or receives log data from the message queue one by one and processes it according to time or sequence. While consuming, the framework maintains the consumption position (offset) to ensure that the data is processed in order.

[0054] Convert the acquired log data from binary or serialized format to a structured data format to facilitate subsequent data parsing and manipulation. Use deserialization tools such as JSON or Avro to convert log data from byte streams or string streams to structured data objects. Through deserialization operations, complex data structures can be mapped to readable formats such as key-value pairs, which facilitates subsequent field extraction and processing.

[0055] The structured data obtained after deserialization contains various fields of the log data (such as operation type, table name, change content, etc.), which will serve as the basic data for subsequent steps. After the deserialization operation is completed, the streaming data processing framework converts the data into a data structure (such as a JSON object or key-value pair) to support subsequent data extraction and classification operations.

[0056] Example: When a customer initiates a transfer operation of 1,000 yuan, the system will record and generate log data. The stream data processing framework obtains the log in real time, deserializes it and generates structured data, which contains detailed information such as operation type (transfer), table name (account transaction table), and change content (account balance decreases by 1,000 yuan). These structured data are synchronized to ODS through the stream processing framework, and the risk control system and financial system can receive the updated information in real time for subsequent processing.

[0057] The streaming data processing framework connects the message queue and deserializes the log data in real time, ensuring that the log data can be structured and processed in the order in which it is generated. This can reduce the burden of direct access to the database and ensure the timeliness and integrity of data synchronization. Through deserialization, the log data is standardized into a structured format, which is convenient for subsequent processing, classification, and synchronization, thereby effectively improving the data stream processing efficiency and the system's response speed, and providing a stable data foundation for real-time data applications.

[0058] S30, extracting an operation record from the structured data, wherein the operation record includes a table name, an operation type field, and a change content;

[0059] In this embodiment, fields related to the operation record are found in the structured data to ensure that the key information of the operation record is correctly extracted. These fields usually include table name, operation type, and change content. The parser scans the field name and position in the structured data to identify the table name, operation type field, and change content field required for the operation record. For example, for data in JSON or Avro format, the field path is used to extract specific content.

[0060] Extract the table name field from the structured data to identify the specific database table involved in this operation. The table name is a basic attribute of the log data, which helps with subsequent operation classification and table structure processing. Use a field parsing tool (such as a JSON parser) to locate the table name field and extract its value. The extracted table name will be used to distinguish it from other operation records so that data can be distributed by table name.

[0061] Get the operation type field in the structured data to determine the category of the operation record (such as data definition language DDL operation or data manipulation language DML operation). The operation type field is the basis for log data classification and subsequent processing. Through string matching or keyword parsing methods, extract the value of the operation type field, such as "insert", "update", "delete" and other operations. This information will be used to classify the operation record.

[0062] Get the changes from the structured data, including specific data changes or table structure adjustment information. This step ensures that detailed data of the record changes can be obtained to support data synchronization and table structure updates. Parse the change content fields in the structured data and extract the relevant content into a processable format. The changes may include changes to the values ​​of specific fields (such as changes in account balances) or adjustments to the attributes of fields (such as adding fields). The extracted changes will be used for table structure updates or data change operations in subsequent steps.

[0063] By extracting operation records from structured data, the system can accurately identify the table name, operation type, and specific change content of each operation. This ensures the accuracy of data classification, distribution, and synchronization operations in subsequent steps, allowing the system to accurately process different types of data changes, thereby reducing data errors and delays.

[0064] S40, classifying the operation record into a data definition language operation record and a data operation language operation record based on the operation type field;

[0065] In this embodiment, the specific value of the operation type field is obtained from the extracted operation record to determine the category of the operation record. The operation type field usually contains keywords (such as "create table", "insert record", etc.) to indicate the specific operation type. According to the format of structured data (such as JSON or Avro), the value of the operation type field is located and read through the field path. The extracted value will be used to further determine the type of the operation record.

[0066] Based on the content of the operation type field, the operation record is classified as a data definition language DDL operation or a data manipulation language DML operation. Data definition language operations (such as creating a table and modifying the table structure) are mainly used to change the database structure, while data manipulation language operations (such as inserting, updating, and deleting records) are used to change specific data content. The operation type is determined based on the content of the operation type field through string matching or keyword search methods. If the field value contains keywords such as "create table", "delete table", or "modify table structure", it is classified as a DDL operation record; if it contains keywords such as "insert", "update", or "delete record", it is classified as a DML operation record.

[0067] Add a classification tag to each operation record to make it clear whether the record belongs to a DDL operation or a DML operation, which is convenient for subsequent classification processing. Add a classification tag or identification field in the object structure of the operation record, such as "operationCategory", marked as "DDL" or "DML". This tag can be used for data routing and processing judgment in subsequent steps.

[0068] The classified operation records are stored in the classified operation record list so that the data definition and data operation records can be processed separately by type in the subsequent steps. Two operation record lists are created to store DDL operation records and DML operation records respectively. After classification, the operation records are placed in the corresponding lists to ensure the orderliness of subsequent processing.

[0069] Example: Assume that there is an "Account Information Table" in the banking system, which records the account balance and transaction information of each customer. When the business department requires adding a new field "Account Type" to distinguish customer types, this operation belongs to the Data Definition Language (DDL) operation because it affects the table structure. This DDL operation record will be marked in the log and automatically classified as a structure change operation by the system, so that the structure adjustment can be synchronized in the Operational Data Storage Module (ODS) to ensure the consistency of the table structure.

[0070] On the other hand, when a customer deposits 1,000 yuan at an ATM, the system records the transaction and processes it as a data manipulation language (DML) operation. The record contains the operation type ("insert"), the table name ("account information table"), and the content of the change (such as "balance increased by 1,000 yuan"). This DML operation record is classified as a content update operation and synchronized to the ODS to ensure that the bank's customer service system can display the latest account balance in real time.

[0071] By classifying operation records based on the operation type field, the system can accurately distinguish between structure change (DDL) operations and data content change (DML) operations. This enables the system to execute specific synchronization processes for different types of operations, thereby achieving separate management of structure adjustment and data update. Through this classification, the efficiency of data synchronization is improved, the complexity of data flow processing is reduced, and the system's response speed and accuracy when facing multi-type data changes are further improved.

[0072] S50, distributing the data definition language operation record and the data operation language operation record to the processing channel corresponding to the table name;

[0073] In this embodiment, dedicated processing channels are established for different table names so as to manage the operation records of different tables separately and ensure that the data change processing of each table is independent and orderly. A mapping table is configured in the system to associate each table name with a corresponding processing channel. This can be achieved through a data structure (such as a hash table) or a mapping configuration file so that the operation record of each table can automatically match the corresponding processing channel.

[0074] According to the table name in the operation record, the data definition language (DDL) operation record and the data manipulation language (DML) operation record are sent to the corresponding processing channel respectively. This ensures that different types of operations are processed in their own channels and maintains the independence of data flow. Using the mapping configuration from table name to channel, each operation record is sent to the matching processing channel through the routing mechanism. The processing channel can be a channel instance of a thread, process, or message queue to independently process the operation record.

[0075] During the distribution process, the distribution status of each operation record is monitored to ensure that the operation record can be successfully transmitted to the corresponding processing channel to avoid data loss due to network or system failure. A status monitoring mechanism is added to the distribution module to record the distribution results of each operation record. In case of distribution failure, an alarm is triggered or the record is recorded in the processing queue to ensure that the problem record is not ignored.

[0076] If an operation record encounters an error during the distribution process, the system will re-add the unsuccessfully distributed operation record to the pending queue and retry until the distribution is successful. Design an error handling and retry mechanism to regularly check the operation records in the pending queue and redistribute them. You can set a retry limit or delay strategy to reduce the system pressure of repeated distribution.

[0077] Example: In a bank's account management system, customer account information and transaction records are stored in different database tables. When a customer makes a deposit, withdraws money, or updates account information, data in different tables will change. These operation records need to be distributed to the corresponding channels in a timely manner for synchronization and processing to ensure that the account information and transaction records in the database are kept up to date and to avoid errors in customer fund information due to information lag.

[0078] Assume that the banking system contains a "customer information table" and a "transaction record table". When a customer updates personal information (for example, changes the contact address), this operation generates a data definition language (DDL) operation record that points to the "customer information table". When a customer makes a deposit or withdrawal transaction, a data manipulation language (DML) operation record is generated that points to the "transaction record table". The system automatically distributes each record to different processing channels based on the table names of these operation records, thereby achieving parallel processing and avoiding mutual interference in data processing.

[0079] For example, the DDL operation records of the "customer information table" are distributed to a dedicated channel to update the table structure, while the DML operation records of the "transaction record table" are distributed to another channel to ensure that transaction data is written quickly without affecting other data synchronization tasks. In addition, the system monitors the distribution status of each record in real time. If a distribution fails, the failed record will be redistributed to ensure that no data is missed.

[0080] By distributing DDL and DML operation records to the processing channels corresponding to the table names, refined data distribution management is achieved, so that operations on different tables can be processed independently and mutual interference between channels can be avoided. The distributed status monitoring and exception handling mechanism further improves the stability of the system and ensures efficient processing of operation records. It effectively reduces the risk of data distribution delays, improves the accuracy and response speed of data synchronization, and provides flexible and efficient processing capabilities for complex multi-table data synchronization scenarios.

[0081] S60, updating or adjusting the table structure of the target table corresponding to the table name in the operation data storage module according to the change content in the data definition language operation record;

[0082] In this embodiment, the current structure of the target table corresponding to the table name in the data definition language (DDL) operation record is obtained from the operation data storage module to ensure that the update or adjustment is based on the latest table structure. The current schema structure (such as field name, data type, primary key, etc.) of the target table is obtained through the database metadata query interface and used as the basis for adjustment.

[0083] In the change content of the DDL operation record, if the operation type is found to be "add field", check whether the target table already contains the newly added field. Parse the change content of the DDL operation record, locate the name of the newly added field, and check whether the field already exists in the current table structure. If not, continue to perform the add field operation.

[0084] After confirming that the target table does not contain the newly added field, perform a table structure update operation to add the field to the target table. This step ensures that the target table contains the latest field definition and maintains consistency with business requirements. Call the database structure update API (such as the ALTER TABLE command) to add the newly added field to the table structure and specify parameters such as field type, length, and constraints to meet data storage requirements.

[0085] When the change content is a "delete field" operation, the system needs to determine whether deleting the field will affect other dependencies (such as foreign key constraints, indexes, triggers, etc.) to avoid data integrity issues. Through the database metadata query interface, check whether the field has a dependency relationship. If there is no impact, perform the field deletion operation, otherwise record the dependency conflict and terminate the operation.

[0086] If the change includes the "Modify Field Properties" operation, keep the original field unchanged and add a new field with the modified properties to ensure data consistency and table structure flexibility. Add the modified field as a new field to the target table, call the database update API, and configure the required data type, length, default value and other properties for the new field.

[0087] By updating or adjusting the target table structure according to the changes recorded in the DDL operation, the system can achieve real-time synchronization and automatic adaptation of the table structure without affecting business operations. The database structure can flexibly respond to changes in business needs, improving the accuracy and real-time performance of data synchronization.

[0088] S70, performing corresponding data change operations on the target table corresponding to the table name in the operation data storage module according to the change content in the data operation language operation record.

[0089] In this embodiment, the operation type is read from the data manipulation language (DML) operation record to determine the specific operation of the data change, such as insert, update or delete. The operation type field of the operation record is parsed, and the corresponding operation type is identified through keyword matching (such as "INSERT", "UPDATE" or "DELETE"), providing a basis for subsequent steps.

[0090] Extract changes from operation records, including specific data record content, such as field names and their corresponding values. Changes will be used to insert, update, or delete data records. Use data parsing tools to extract field names and values ​​from structured data (such as JSON or Avro) to form complete data records. Ensure that data content is complete so that operations can be performed accurately.

[0091] According to the operation specified in the Operation Type field, perform insert, update, or delete operations to ensure that the target table in the data storage module is consistent with the business system.

[0092] Insert operation: insert the extracted data records into the target table. Use the database's "INSERT INTO" command or API to complete the record insertion.

[0093] Update operation: Find the corresponding data record in the target table and update the field with the new value in the change content. You can use the "UPDATE" command or API to update the specified field.

[0094] Delete operation: locate the target record based on the primary key or unique identifier in the change content and delete this record from the target table. Use the "DELETE FROM" command or API to perform the delete operation.

[0095] After executing the data change operation, confirm the execution status of each operation to ensure the integrity and consistency of data synchronization. If the operation fails or encounters an error, record the error and try again to avoid data loss. After the data change operation is completed, check the operation status and record the execution results. The integrity of the operation can be guaranteed through the database transaction mechanism. If an exception is encountered, roll back the transaction to ensure the stability and consistency of the database.

[0096] By parsing the data manipulation language (DML) operation records and performing corresponding data change operations according to the operation type, the system can efficiently and accurately synchronize data changes in the business database to the operational data storage module.

[0097] The present invention relates to the fields of big data technology and financial technology, and discloses a database table synchronization method based on log data, which records the operation information of a business database table into log data, writes the log data into a message queue, obtains the log data through a streaming data processing framework and deserializes it into structured data; extracts the operation record and classifies it according to the operation type field, distributes it to the processing channel corresponding to the table name, updates or adjusts the table structure of the target table according to the classification, or performs a data change operation. The present invention realizes quasi-real-time synchronization from a business database table to an operation data storage module through a synchronization method based on log data, and adaptively adjusts the table structure when the business table structure changes, reduces data consistency problems and system interruption risks, effectively improves the efficiency and stability of data synchronization, and meets the business's demand for efficient and stable data synchronization.

[0098] In one embodiment, in the above S10, recording the operation information of the business database table into the log data includes:

[0099] S101, monitoring operation behaviors in a business database table, and identifying operation events including data insertion, data update, and data deletion, as well as table creation, table modification, and table deletion;

[0100] S102, extracting operation information including operation type, table name and field information from the operation event;

[0101] S103, converting the operation information into a standardized log format to generate log data;

[0102] S104, storing the log data in a log data storage module.

[0103] In this embodiment, the operation behavior in the business database table is monitored to identify the operation events such as data insertion, update, deletion, and table structure creation, modification, and deletion, ensuring that each data operation can be recorded. Database triggers or change data capture (CDC) technology are used to capture all data and structure change operations in the business database. Through trigger configuration or CDC monitoring, all addition, deletion, and modification operations involved in the data table are automatically monitored.

[0104] Extract key information such as operation type (such as insert, update, delete), table name and field information from the operation event. This information is the basis for subsequent log recording and synchronization. Through the event parser, extract the operation type (such as INSERT, UPDATE), table name, related field names and field values ​​from the captured operation event to form a complete operation information record.

[0105] Convert the extracted operation information into a standardized log format (such as JSON, Avro) so that the log data can be transmitted and parsed in the message queue or subsequent modules. Use a standardized format conversion tool to map the operation information field to the specified format. JSON or Avro format is usually used so that subsequent modules can parse the log data while ensuring data compatibility.

[0106] Store standardized log data in the log data storage module to provide a basis for subsequent data synchronization and processing. The log data storage module is used to store and manage all captured log data to ensure the integrity and order of the data. Write standardized log data to the log data storage module through the log storage API. You can use the file system, database, or message queue as the log storage module to ensure the security and persistence of data storage.

[0107] This embodiment realizes the automatic capture and management of operation information by monitoring the operation behavior of the business database table, extracting operation information, standardizing format conversion and log storage. It can not only effectively track the change history of data, but also provide a structured data foundation for subsequent data synchronization and data analysis. It effectively reduces the need for manual recording operations, ensures the real-time consistency of the business database and the operation data storage module, improves the accuracy and stability of data synchronization, and provides reliable technical support for data consistency between multiple systems.

[0108] In one embodiment, the above S20 includes:

[0109] S201, establishing a connection between the streaming data processing framework and the message queue;

[0110] S202, monitoring the log data stream in the message queue through the streaming data processing framework;

[0111] S203, acquiring log data one by one from the message queue in the time sequence of data generation;

[0112] S204, deserialize each log data and convert it into structured data;

[0113] S205: Add or maintain timestamp information in the structured data.

[0114] In this embodiment, a streaming data processing framework (such as Flink or Spark Streaming) is initialized and a stable connection is established with the message queue to ensure real-time transmission of log data. The connection parameters of the streaming data processing framework (such as the brokers address and Topic name of Kafka) are configured, and a connection is established through the message queue client API or driver to provide a channel for obtaining subsequent data streams.

[0115] The streaming data processing framework continuously monitors the log data stream in the message queue so that new data can be received and processed immediately when it is generated. Set up a monitoring mechanism in the streaming data processing framework and specify the topic or channel of the message queue. Through the real-time monitoring function of the framework, when new log data is generated in the message queue, the data reception operation is automatically triggered.

[0116] Ensure that log data is obtained from the message queue one by one in order, so that data processing is carried out in chronological order to ensure the sequential consistency of the data stream. Use the offset mechanism of the message queue to obtain log data from the message queue one by one and process it in the chronological order of data generation to ensure the continuity and accuracy of the log data.

[0117] Convert each acquired log data from the serialized format of the message queue (such as JSON or Avro) to a structured data format to facilitate subsequent field parsing and operations. Use the deserialization tool to convert the log data into a structured data object (such as a JSON object or a key-value pair). The deserialization result will contain fields such as the table name, operation type, and specific change content of the log record, which is convenient for subsequent processing.

[0118] Ensure that each structured data contains timestamp information so that subsequent data processing can be sorted and analyzed in chronological order. Timestamp information is used to identify the generation time and synchronization time of log data. Add a timestamp field to the deserialized structured data, or update the value of an existing timestamp field. Timestamps can be obtained from the metadata of the message queue or automatically generated by the system during data processing.

[0119] This embodiment realizes the real-time acquisition and structured processing of log data by connecting the streaming data processing framework with the message queue, ensuring that each log data can be processed immediately after it is generated. Through the acquisition and deserialization operations one by one, the system can convert the log data into a structured format that is easy to parse, which is convenient for subsequent classification and processing. At the same time, the addition of timestamps enables the orderliness of the data stream to be maintained, providing an accurate time reference for data synchronization and analysis. It effectively improves the timeliness and accuracy of data synchronization and enhances the system's processing capabilities in real-time data scenarios.

[0120] In one embodiment, the above S40 includes:

[0121] S401, extracting an operation type field from each operation record;

[0122] S402, when the operation type field contains keywords such as create table, modify table or delete table, determining that the operation record is a data definition language operation record;

[0123] S403, when the operation type field contains keywords such as insert record, update record or delete record, determining that the operation record is a data operation language operation record;

[0124] S404, adding a classification status mark to each classified operation record.

[0125] In this embodiment, the value of the operation type field is obtained from the operation record to determine whether the record belongs to a data definition language (DDL) operation or a data manipulation language (DML) operation. The operation type field is the key to identifying the record type. A data parsing tool (such as a JSON or Avro parser) is used to locate and extract the value of the operation type field from the operation record. The value obtained after extraction is used to determine the operation category, which is convenient for subsequent classification.

[0126] When the Operation Type field contains keywords such as "Create Table", "Modify Table", or "Delete Table", the system classifies the record as a Data Definition Language operation record. DDL operation records are used to describe operations that change the database table structure. Through keyword matching or string search, check whether the Operation Type field contains "Create Table", "Modify Table", or "Delete Table". If the match is successful, the record is marked as a DDL operation record. Matching can use regular expressions or specific string parsing methods.

[0127] When the Operation Type field contains keywords such as "Insert Record", "Update Record" or "Delete Record", the system classifies the record as a Data Manipulation Language Operation Record. DML operation records are mainly used to record changes to data content. Similarly, keyword matching or string search is used to check whether the Operation Type field contains "Insert Record", "Update Record" or "Delete Record". If the match is successful, the record is marked as a DML operation record. The keyword set can be defined in the configuration file to dynamically update the operation type judgment rules.

[0128] Add or update a classification status tag in the operation record to identify whether the record belongs to a DDL operation record or a DML operation record. Classification tags facilitate subsequent data processing and distribution operations. Add a new status field, such as "operationCategory", to the structured data of the operation record and set the field to "DDL" or "DML". This tag facilitates quick identification and processing of classified data in subsequent steps.

[0129] In this embodiment, by classifying operation records into data definition language (DDL) operation records and data manipulation language (DML) operation records according to the operation type field, the system can efficiently identify and manage different change operations of database structure and data. Classification tags simplify the subsequent processing logic, allowing the system to perform different synchronization and distribution processes for different types of operations, ensuring that database structure updates and data content changes can be processed and synchronized separately. The accuracy and processing efficiency of operation record classification are improved, flexible support is provided for the synchronization operation of multi-table and multi-type data, and the data management capability and maintainability of the system are effectively improved.

[0130] In one embodiment, the above S50 includes:

[0131] S501, assigning a corresponding processing channel to each table name according to the channel mapping rule;

[0132] S502, according to the table name information in each operation record, distribute the data definition language operation record and the data operation language operation record to the processing channel corresponding to the table name;

[0133] S503, monitoring the distribution status of each operation record during the distribution process;

[0134] S504: If an error occurs during the distribution process, the operation records that were not successfully distributed are added to a queue to be processed, and the distribution operation is re-executed until the operation records are successfully distributed.

[0135] In this embodiment, a channel mapping rule is established to assign a unique processing channel to each table name so that the operation records of each table can be processed independently. The channel mapping rule can be dynamically configured according to the database table name to ensure that all tables can be accurately assigned to the corresponding processing channel. The channel mapping rule is defined in the system configuration, for example, using a hash map or a configuration file to bind each table name to an independent processing channel so that the system can quickly route to the corresponding channel when identifying the table name.

[0136] After determining the table name of the operation record, the record is routed to the processing channel corresponding to the table name so as to classify and process data change operations of different tables. Parse the table name field of the operation record, and distribute the data definition language (DDL) operation record and data manipulation language (DML) operation record to the corresponding channels according to the channel mapping rules. Distribution can be achieved through message queues (such as Kafka's Topic) or independent process channels.

[0137] During the distribution process, the distribution status of each operation record is monitored in real time to ensure that the operation record can be correctly and completely transmitted to the corresponding processing channel. A status monitoring mechanism is added to the distribution module to record the distribution status of each operation record. If the distribution is successful, the distribution status is updated to "completed"; if the distribution fails, the status is updated to "failed" or "pending retry". This process can be implemented by logging and status marking.

[0138] If an error occurs during the distribution process, the operation records that were not successfully distributed will be added to the pending queue and redistributed to ensure that all operation records can be successfully transmitted in the end. Set up a pending queue to store operation records that were not successfully distributed. The system will scan the pending queue regularly and redistribute the operation records according to the set retry strategy until the distribution is successful. The number of retries and interval time can be set in the system to avoid system overload due to frequent retries.

[0139] This embodiment distributes the data definition language (DDL) and data manipulation language (DML) operation records to the corresponding processing channels through channel mapping based on table names, ensuring that the operation records of each table are processed independently. Real-time monitoring of distribution status and error handling mechanisms improve the system's ability to respond to data distribution errors, avoid data loss or processing delays, and ensure the accuracy and integrity of data synchronization.

[0140] In one embodiment, the above S60 includes:

[0141] S601, obtaining the current schema structure of the target table in the operation data storage module;

[0142] S602, when the change content is an operation of adding a field, checking whether the current schema structure of the target table contains the name of the newly added field specified in the data definition language operation record, if not, adding the newly added field to the target table using a table structure update API according to the newly added field information in the data definition language operation record;

[0143] S603, when the change content is a field deletion operation, determine whether the target table has the field name specified in the data definition language operation record for deletion, if so, determine whether the field deletion will affect foreign key constraints, indexes or triggers, if not, remove the corresponding field from the target table;

[0144] S604, when the change content is an operation to modify field attributes, the existing field whose name is the same as the modified field name specified in the data definition language operation record is retained in the target table unchanged, and the modified field is added to the target table as a new field according to the modified field attributes in the data definition language operation record.

[0145] In this embodiment, the current table structure (schema structure) of the target table is obtained from the operation data storage module to provide a basis for subsequent field addition, deletion, and modification operations. The current structure information of the target table, including field names, data types, constraints, etc., is read through the database management interface or metadata query tool to ensure that the operation is based on the latest table structure.

[0146] If the change specified in the data definition language (DDL) operation record is "adding a field", the system first checks whether the field already exists in the current structure of the target table to avoid duplicate addition. The newly added field name in the DDL operation record is parsed and compared with the current schema structure of the target table. If the target table does not yet contain the field, the addition operation is prepared according to the field properties provided in the DDL record.

[0147] If the target table does not contain the specified new field, call the database table structure update API (such as the ALTER TABLE command) to add the field to the target table. Use the database API or command line (such as the SQL ALTER TABLEADDCOLUMN statement) to specify the name, data type, default value, and other constraint information of the new field to complete the field addition operation.

[0148] If the change content in the DDL operation record is "delete field", the system checks whether the specified deleted field exists in the target table and determines whether the deletion operation will affect dependencies such as foreign keys, indexes, or triggers. The name of the deleted field is found from the schema structure of the target table. If the field exists, further checks whether there is a dependency. Foreign key, index, and trigger information can be obtained through database metadata queries to ensure that the deletion operation does not damage data integrity.

[0149] After confirming that deleting a field will not affect the dependency relationship, the system removes the field from the target table to update the structure. Call the database API or use the SQL ALTER TABLE DROP COLUMN command to remove the specified field to ensure safe and standardized operations.

[0150] If the change involves "modifying field properties", the system will keep the existing fields in the target table with the same name as the specified field unchanged, and add the modified field to the table as a new field. This method avoids directly overwriting the original field and ensures data security and consistency. On the basis of retaining the original field, add a new field based on the modified properties. Specify the properties of the new field through the table structure update API, including the field name (usually adding a suffix to distinguish), data type, and other properties.

[0151] This embodiment updates or adjusts the target table structure based on the changes recorded in the data definition language (DDL) operation. The system can respond to business needs in real time, automatically synchronize the addition, deletion and change of the database table structure, and reduce the risk of manual intervention. Automatic detection of the operations of adding, deleting and modifying fields and judgment of dependencies ensure the security and consistency of data. In addition, the method of retaining the original field and adding the modified field as a new field effectively reduces the risk of direct overwriting and modification, and ensures the stability of database updates.

[0152] In one embodiment, the above S70 includes:

[0153] S701, determining the operation type of the data operation language operation record, where the operation type includes an insert operation, an update operation or a delete operation, and the change content in the data operation language operation record includes a specific data record in the business database table;

[0154] S702, when the operation type is an insert operation, inserting the specific data record into a target table;

[0155] S703, when the operation type is an update operation, updating the corresponding specific data record in the target table;

[0156] S704: When the operation type is a delete operation, the corresponding specific data record is deleted from the target table.

[0157] In this embodiment, the operation type is obtained from the data manipulation language (DML) operation record to distinguish whether the specific operation is insert, update or delete. The "operation type" field of the DML operation record is parsed to determine whether its value is "insert", "update" or "delete". The operation type is distinguished by keywords or predefined tags to provide a basis for subsequent operations.

[0158] Extract the content of the data record to be inserted, updated or deleted from the DML operation record, including the field name and value, so as to perform specific data change operations. Parse the "change content" part in the DML operation record, extract the field name, field value and other related information from it, and form a complete record data to be operated. Process it through tools such as JSON or key-value pair format parser.

[0159] When the operation type is an insert operation, the extracted specific data records are added to the target table to achieve data synchronization. Use the database insert command (such as SQL's "INSERT INTO" statement) or API to insert new records into the corresponding fields of the target table to ensure that the data records are completely inserted into the database.

[0160] When the operation type is an update operation, locate the corresponding data record in the target table and update its field value to the new change content to maintain data consistency. Use the database update command (such as SQL's "UPDATE SET" statement) or API to find the corresponding record in the target table according to the primary key or unique identifier and update the field. Ensure that only the relevant fields of the target record are updated to avoid misoperation.

[0161] When the operation type is a delete operation, the corresponding data records are deleted from the target table according to the specified conditions to synchronize the delete operation. Use the database delete command (such as SQL's "DELETE FROM" statement) or API to locate the record to be deleted by the primary key or other unique identifier and perform the delete operation. The delete operation ensures that the data records are accurately removed from the target table.

[0162] This embodiment synchronizes data based on the changes recorded in the data manipulation language (DML) operation, thus realizing the automatic addition, deletion and modification of database records and ensuring the consistency and real-time performance of data. The system can automatically update the data records in the target table based on the data change information in the business database, avoiding errors and delays caused by manual synchronization operations. In addition, by using transaction mechanisms and operation status monitoring, the integrity and accuracy of each data operation are ensured, effectively improving the data synchronization efficiency and security of the system, and is suitable for business scenarios with high-frequency data updates.

[0163] In one embodiment, a database table synchronization device based on log data is provided, and the database table synchronization device based on log data corresponds one-to-one to the database table synchronization method based on log data in the above embodiment. Figure 3 , Figure 3 This is a functional module diagram of a preferred embodiment of a database table synchronization device based on log data of the present invention. Log recording module 10, data consumption module 20, operation record parsing module 30, operation type classification module 40, operation record distribution module 50, table structure update module 60 and data change module 70. The functional modules are described in detail as follows:

[0164] The log recording module 10 is used to record the operation information of the business database table into log data and write the log data into a message queue;

[0165] The data consumption module 20 is used to obtain the log data from the message queue through the streaming data processing framework, and deserialize each log data to obtain corresponding structured data;

[0166] An operation record parsing module 30, used to extract operation records from the structured data, wherein the operation records include a table name, an operation type field, and a change content;

[0167] An operation type classification module 40, for classifying the operation record into a data definition language operation record and a data operation language operation record based on the operation type field;

[0168] An operation record distribution module 50, used to distribute the data definition language operation record and the data operation language operation record to the processing channel corresponding to the table name;

[0169] A table structure updating module 60, for updating or adjusting the table structure of the target table corresponding to the table name in the operation data storage module according to the change content in the data definition language operation record;

[0170] The data changing module 70 is used to perform corresponding data changing operations on the target table corresponding to the table name in the operation data storage module according to the changed content in the data operation language operation record.

[0171] In one embodiment, the log recording module 10 is specifically used for:

[0172] Monitor the operation behaviors in the business database tables and identify operation events including data insertion, data update and data deletion, as well as table creation, table modification and table deletion;

[0173] Extracting operation information including operation type, table name and field information from the operation event;

[0174] Convert the operation information into a standardized log format to generate log data;

[0175] The log data is stored in a log data storage module.

[0176] In one embodiment, the data consumption module 20 is specifically used for:

[0177] Establishing a connection between the streaming data processing framework and the message queue;

[0178] Monitor the log data stream in the message queue through the streaming data processing framework;

[0179] Obtain log data one by one from the message queue in the time sequence of data generation;

[0180] Deserialize each log data and convert it into structured data;

[0181] Timestamp information is added or maintained in the structured data.

[0182] In one embodiment, the operation type classification module 40 is specifically used to:

[0183] Extract the operation type field from each operation record;

[0184] When the operation type field contains keywords such as create table, modify table or delete table, the operation record is determined to be a data definition language operation record;

[0185] When the operation type field contains keywords such as insert record, update record or delete record, the operation record is determined to be a data operation language operation record;

[0186] Add a classification status mark to each classified operation record.

[0187] In one embodiment, the operation record distribution module 50 is specifically used to:

[0188] Through the channel mapping rules, a corresponding processing channel is assigned to each table name;

[0189] According to the table name information in each operation record, the data definition language operation record and the data operation language operation record are distributed to the processing channel corresponding to the table name;

[0190] Monitor the distribution status of each operation record during the distribution process;

[0191] If an error occurs during the distribution process, the operation records that were not successfully distributed will be added to the queue to be processed, and the distribution operation will be re-executed until the operation records are successfully distributed.

[0192] In one embodiment, the table structure updating module 60 is specifically used for:

[0193] Get the current schema structure of the target table in the operation data storage module;

[0194] When the change content is to add a field operation, check whether the current schema structure of the target table contains the name of the newly added field specified in the data definition language operation record. If not, add the newly added field to the target table using the table structure update API according to the newly added field information in the data definition language operation record.

[0195] When the change content is a field deletion operation, determine whether the target table has the field name specified in the data definition language operation record for deletion. If so, determine whether the field deletion will affect foreign key constraints, indexes, or triggers. If not, remove the corresponding field from the target table.

[0196] When the change content is an operation to modify field attributes, the existing field with the same name as the modified field name specified in the data definition language operation record is retained unchanged in the target table, and the modified field is added to the target table as a new field according to the modified field attributes in the data definition language operation record.

[0197] In one embodiment, the data modification module 70 is specifically configured to:

[0198] Determine the operation type of the data operation language operation record, the operation type includes an insert operation, an update operation or a delete operation, and the change content in the data operation language operation record includes a specific data record in the business database table;

[0199] When the operation type is an insert operation, inserting the specific data record into the target table;

[0200] When the operation type is an update operation, the corresponding specific data record in the target table is updated;

[0201] When the operation type is a delete operation, the corresponding specific data record is deleted from the target table.

[0202] In one embodiment, a computer device is provided. The computer device may be a server, and its internal structure diagram may be as follows: Figure 4 As shown. The computer device includes a processor, a memory, a network interface and a database connected via a system bus. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile and / or volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The network interface of the computer device is used to communicate with an external user terminal via a network connection. When the computer program is executed by the processor, it implements the functions or steps of a database table synchronization method based on log data on the server side.

[0203] In one embodiment, a computer device is provided. The computer device may be a user terminal, and its internal structure diagram may be as follows: Figure 5 As shown. The computer device includes a processor, a memory, a network interface, a display screen and an input device connected via a system bus. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The network interface of the computer device is used to communicate with an external server via a network connection. When the computer program is executed by the processor, it realizes the functions or steps on the user side of a database table synchronization method based on log data

[0204] In one embodiment, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the computer program, the following steps are implemented:

[0205] Recording the operation information of the business database table into log data, and writing the log data into the message queue;

[0206] Obtain the log data from the message queue through a streaming data processing framework, and deserialize each log data to obtain corresponding structured data;

[0207] Extracting an operation record from the structured data, the operation record including a table name, an operation type field, and a change content;

[0208] Based on the operation type field, classify the operation record into a data definition language operation record and a data operation language operation record;

[0209] Distributing the data definition language operation record and the data manipulation language operation record to the processing channel corresponding to the table name;

[0210] According to the change content in the data definition language operation record, updating or adjusting the table structure of the target table corresponding to the table name in the operation data storage module;

[0211] According to the change content in the data operation language operation record, a corresponding data change operation is performed on the target table corresponding to the table name in the operation data storage module.

[0212] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored, and when the computer program is executed by a processor, the following steps are implemented:

[0213] Recording the operation information of the business database table into log data, and writing the log data into the message queue;

[0214] Obtain the log data from the message queue through a streaming data processing framework, and deserialize each log data to obtain corresponding structured data;

[0215] Extracting an operation record from the structured data, the operation record including a table name, an operation type field, and a change content;

[0216] Based on the operation type field, classify the operation record into a data definition language operation record and a data operation language operation record;

[0217] Distributing the data definition language operation record and the data manipulation language operation record to the processing channel corresponding to the table name;

[0218] According to the change content in the data definition language operation record, updating or adjusting the table structure of the target table corresponding to the table name in the operation data storage module;

[0219] According to the change content in the data operation language operation record, a corresponding data change operation is performed on the target table corresponding to the table name in the operation data storage module.

[0220] It should be noted that the above functions or steps that can be implemented by the computer-readable storage medium or computer device can refer to the relevant descriptions on the server side and the user side in the aforementioned method embodiment. To avoid repetition, they will not be described one by one here.

[0221] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).

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

[0223] It should be noted that if software tools or components other than those of the Company appear in the embodiments of the present application, they are only used for illustration and do not represent actual use. The above-described embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit them; although the present invention has been described in detail with reference to the above-mentioned embodiments, a person of ordinary skill in the art should understand that the technical solutions described in the above-mentioned embodiments can still be modified, or some of the technical features thereof can be replaced by equivalents; and these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included in the protection scope of the present invention.

Claims

1. A database table synchronization method based on log data, characterized in that: The following steps are involved: Recording the operation information of the business database table into log data, and writing the log data into the message queue; Obtain the log data from the message queue through a streaming data processing framework, and deserialize each log data to obtain corresponding structured data; Extracting an operation record from the structured data, the operation record including a table name, an operation type field, and a change content; Based on the operation type field, classify the operation record into a data definition language operation record and a data operation language operation record; Distributing the data definition language operation record and the data manipulation language operation record to the processing channel corresponding to the table name; According to the change content in the data definition language operation record, updating or adjusting the table structure of the target table corresponding to the table name in the operation data storage module; According to the change content in the data operation language operation record, a corresponding data change operation is performed on the target table corresponding to the table name in the operation data storage module.

2. The method for synchronizing a database table based on log data according to claim 1, characterized in that: Record the operation information of the business database table into the log data, including: Monitor the operation behaviors in the business database tables and identify operation events including data insertion, data update and data deletion, as well as table creation, table modification and table deletion; Extracting operation information including operation type, table name and field information from the operation event; Convert the operation information into a standardized log format to generate log data; The log data is stored in a log data storage module.

3. The method for synchronizing a database table based on log data according to claim 1, characterized in that: The log data is obtained from the message queue through the streaming data processing framework, and each log data is deserialized to obtain corresponding structured data, including: Establishing a connection between the streaming data processing framework and the message queue; Monitor the log data stream in the message queue through the streaming data processing framework; Obtain log data one by one from the message queue in the time sequence of data generation; Deserialize each log data and convert it into structured data; Timestamp information is added or maintained in the structured data.

4. The method for synchronizing a database table based on log data as claimed in claim 1, characterized in that: Based on the operation type field, the operation record is classified into a data definition language operation record and a data operation language operation record, including: Extract the operation type field from each operation record; When the operation type field contains keywords such as create table, modify table or delete table, the operation record is determined to be a data definition language operation record; When the operation type field contains keywords such as insert record, update record or delete record, the operation record is determined to be a data operation language operation record; Add a classification status mark to each classified operation record.

5. The method for synchronizing a database table based on log data according to claim 1, characterized in that: Distributing the data definition language operation record and the data operation language operation record to the processing channel corresponding to the table name includes: Through the channel mapping rules, a corresponding processing channel is assigned to each table name; According to the table name information in each operation record, the data definition language operation record and the data operation language operation record are distributed to the processing channel corresponding to the table name; Monitor the distribution status of each operation record during the distribution process; If an error occurs during the distribution process, the operation records that were not successfully distributed will be added to the queue to be processed, and the distribution operation will be re-executed until the operation records are successfully distributed.

6. The method for synchronizing a database table based on log data according to claim 1, characterized in that: According to the change content in the data definition language operation record, updating or adjusting the table structure of the target table corresponding to the table name in the operation data storage module includes: Get the current schema structure of the target table in the operation data storage module; When the change content is to add a field operation, check whether the current schema structure of the target table contains the name of the newly added field specified in the data definition language operation record. If not, add the newly added field to the target table using the table structure update API according to the newly added field information in the data definition language operation record. When the change content is a field deletion operation, determine whether the target table has the field name specified in the data definition language operation record for deletion. If so, determine whether the field deletion will affect foreign key constraints, indexes, or triggers. If not, remove the corresponding field from the target table. When the change content is an operation to modify field attributes, the existing field with the same name as the modified field name specified in the data definition language operation record is retained unchanged in the target table, and the modified field is added to the target table as a new field according to the modified field attributes in the data definition language operation record.

7. The method for synchronizing a database table based on log data according to claim 1, characterized in that: According to the change content in the data operation language operation record, a corresponding data change operation is performed on the target table corresponding to the table name in the operation data storage module, including: Determine the operation type of the data operation language operation record, the operation type includes an insert operation, an update operation or a delete operation, and the change content in the data operation language operation record includes a specific data record in the business database table; When the operation type is an insert operation, inserting the specific data record into the target table; When the operation type is an update operation, the corresponding specific data record in the target table is updated; When the operation type is a delete operation, the corresponding specific data record is deleted from the target table.

8. A database table synchronization device based on log data, characterized in that: The database table synchronization device based on log data includes: A log recording module, used to record the operation information of the business database table into log data, and write the log data into a message queue; A data consumption module, used to obtain the log data from the message queue through a streaming data processing framework, and deserialize each log data to obtain corresponding structured data; An operation record parsing module, used to extract operation records from the structured data, wherein the operation records include a table name, an operation type field, and a change content; An operation type classification module, used for classifying the operation record into a data definition language operation record and a data operation language operation record based on the operation type field; An operation record distribution module, used for distributing the data definition language operation record and the data operation language operation record to the processing channel corresponding to the table name; A table structure updating module, used to update or adjust the table structure of the target table corresponding to the table name in the operation data storage module according to the change content in the data definition language operation record; The data change module is used to perform corresponding data change operations on the target table corresponding to the table name in the operation data storage module according to the change content in the data operation language operation record.

9. A computer device, characterized in that: The computer device includes a memory, a processor, and a database table synchronization program based on log data stored in the memory and executable on the processor. When the database table synchronization program based on log data is executed by the processor, the steps of the database table synchronization method based on log data are implemented as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: The storage medium stores a database table synchronization program based on log data, and when the database table synchronization program based on log data is executed by the processor, the steps of the database table synchronization method based on log data according to any one of claims 1 to 7 are implemented.

Images