A method for collecting data changes that is compatible with multiple heterogeneous databases

Abstract

This invention relates to the field of data processing technology, and in particular to a method for collecting data changes adapted to various heterogeneous databases. The method includes the following steps: configuring monitoring information for a business service on a change monitoring platform; injecting a component into the business service to request the monitoring information from the monitoring platform; and initializing an asynchronous collection task. The component, through a plugin in a JAR file, monitors database changes. When changes occur in the database's log data, the plugin reports the log data through the message middleware RabbitMQ. The change monitoring platform consumes messages in a pull mode to write the changed data to the corresponding ElasticSearch and Hadoop HDFS indexes. The data undergoes a cold / hot conversion. This invention enables seamless data collection and transmission, ensuring the real-time performance, reliability, and idempotency of change collection, while also saving on data storage and maintenance costs.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to a method for collecting data changes that is compatible with various heterogeneous databases. Background Technology

[0002] With the continuous development of information technology, the water conservancy industry is gradually transforming towards intelligent and information-based systems. During this transformation, water conservancy information systems need to handle massive amounts of data from different sources, in different formats, and with different structures. This data is often stored in various heterogeneous databases, such as Oracle, MySQL, SQL Server, Kingbase, and DM, each with its unique data storage and access mechanisms. Therefore, how to effectively collect data change information from these heterogeneous databases has become a crucial issue in the construction of water conservancy information systems. The current pain points are mainly reflected in the following aspects:

[0003] 1. Differences in data format and structure: There are significant differences in data format, table structure, field types, etc. between heterogeneous databases. This leads to complex conversion and mapping during the data collection process, increasing the complexity and difficulty of data collection.

[0004] 2. Incompatible technology systems: Different database systems often use different technology systems, such as stored procedures, triggers, and logging mechanisms. These technology systems may not be directly compatible during data acquisition and require the development of special adapters or middleware for bridging.

[0005] 3. Real-time and accuracy issues: Water conservancy information systems have high requirements for the real-time performance and accuracy of data, but existing data acquisition schemes often fail to meet both requirements simultaneously. For example, while log-based data acquisition schemes offer good real-time performance, data accuracy may decrease due to lost or corrupted logs; conversely, while periodic polling-based data acquisition schemes offer high accuracy, their real-time performance is poor.

[0006] 4. Insufficient scalability and flexibility: With the continuous development of water conservancy information systems and the integration of new and old reservoir projects, new data sources and data types are constantly emerging. Existing data acquisition solutions often struggle to adapt quickly to these changes, requiring extensive custom development work, resulting in insufficient system scalability and flexibility.

[0007] 5. Massive Data Storage Challenges: The volume of water conservancy-related data is enormous, and traditional storage systems struggle to meet the ever-increasing capacity demands. Furthermore, single-node performance issues make it difficult to support high-concurrency, high-throughput data access requirements. Large-scale data storage presents not only technical challenges but also significant costs for hardware procurement, maintenance, and energy consumption. Summary of the Invention

[0008] To address the aforementioned issues, this invention provides a data change collection method adaptable to various heterogeneous databases, enabling seamless data collection and transmission while ensuring the real-time performance, reliability, and idempotency of change collection, and saving on data storage and maintenance costs.

[0009] To achieve the above objectives, the technical solution adopted by the present invention is as follows:

[0010] A method for collecting data changes that is compatible with multiple heterogeneous databases includes the following steps:

[0011] S1. Modify the monitoring information of the business service in the monitoring platform configuration, inject components into the business service to request the configuration of the monitoring information from the monitoring platform, and initialize the asynchronous collection task;

[0012] S2. The component uses a plugin in a JAR file and collects data from the database according to the configuration of the listening information to monitor database changes. When the log data of the database changes, the plugin parses the changed log data and reports the log data through the message middleware RabbitMQ.

[0013] S3. The change monitoring platform consumes messages in a pull mode to write change data into the corresponding ElasticSearch and the corresponding Hadoop HDFS indexes to store the change data, and the change monitoring platform returns a success ACK after successful consumption.

[0014] S4. Schedule a hot / cold data conversion monitoring task to retrieve data access records from the access record database and perform hot / cold data conversion based on the data access activity, so as to write highly active hot data to ElasticSearch.

[0015] Further, in step S1, the monitoring information includes service name, database, data table, table fields, encrypted fields and encryption methods, reporting strategy, and retry strategy; in step S2, the data collection content of the database includes the target table, collection fields, and field encryption strategy.

[0016] Further, in step S2, the plugin parses the changed log data, and after obtaining the corresponding monitoring change fields according to the configuration of the listening information, it encrypts the corresponding fields according to the encryption strategy to generate a key / value list of columns and values.

[0017] The plugin serializes the key / value list into a JSON string and then puts it into the reporting queue through the message middleware RabbitMQ.

[0018] Furthermore, the component reports the log data according to the reporting strategy, and the component performs trigger capture and periodic polling according to the retry strategy to re-report failed data.

[0019] Furthermore, in step S3, after the change monitoring platform pulls the log data through the message middleware RabbitMQ, it puts the data into an asynchronous thread. The asynchronous thread writes the data into the corresponding ElasticSearch and Hadoop HDFS indexes according to the configuration information corresponding to the change.

[0020] If the write operation is successful, an ACK indicating successful consumption is returned. If the write operation fails, the system will retry the write operation locally a set number of times, with each retry having an interval. After each local retry fails, the system will attempt to roll back. Once the set number of local retry attempts is reached, an ACK indicating failed consumption is returned, and the message will be delivered again.

[0021] Furthermore, in step S4, the data hot / cold conversion includes the following steps:

[0022] S4.1 The user's data access is sent to the record table data, and the record table data writes the data access record to the access record database;

[0023] S4.2 uses the LRU and LFU algorithms to filter and obtain hot and cold data;

[0024] S4.3 removes cold data and data not written within the most recent preset time from Elasticsearch;

[0025] S4.4 writes frequently accessed data that does not exist in Elasticsearch to Elasticsearch.

[0026] Further, in step S4.2, the LRU algorithm is used to filter out the N most recently accessed data items in the access records within the most recent preset time period to obtain the most recently accessed data items; the LFU algorithm is used to filter out the M most recently accessed data items with the highest access frequency within the most recently accessed time period to obtain hot data; and cold data is obtained by filtering the hot data in the record database.

[0027] The beneficial effects of this invention are:

[0028] 1. The plugin of this invention implements log monitoring and parsing for multi-source heterogeneous databases, provides a unified standard data format, and enables hot-swapping via JAR package references, reducing the intrusion of database change collection into business code. Furthermore, through message middleware access and change notification combined with polling scanning, it ensures the real-time performance, reliability, and idempotency of change collection, providing a solution for future integration with more systems and more types of databases. Moreover, the monitoring and parsing plugin is distributed as a JAR package, enabling rapid development and application when adding new database types; simultaneously, it stores data based on the content reported by the business-side monitoring and parsing plugin received from the message middleware, reducing the intrusion of data collection into business-side code and achieving decoupling between business and collection code.

[0029] 2. A hot / cold data separation storage approach is adopted. Recently collected hot data is stored using Elasticsearch to improve data query efficiency. Cold data, which is not recently collected, is stored and processed in parallel using a distributed storage system such as Hadoop HDFS. This hot / cold data separation scheme effectively improves storage capacity and access speed, while saving on data storage maintenance costs. Furthermore, by dividing data into hot and cold data and storing them on storage media with different performance and costs, it avoids storing all data on high-performance, high-cost storage devices. Cold data can be stored on lower-cost storage media, such as cloud storage or low-speed hard drives, thus significantly reducing storage costs. Meanwhile, storing hot data on high-performance storage devices provides faster read and write speeds, accelerating data processing and response times. It also avoids read / write conflicts for hot data, improving the overall performance of the storage system. Cold data, due to its lower access frequency, does not place excessive load on the storage system. Attached Figure Description

[0030] Figure 1 This is a flowchart of a preferred embodiment of a method for collecting data changes from various heterogeneous databases.

[0031] Figure 2 This is a data flow diagram of a preferred embodiment of a method for collecting data changes from various heterogeneous databases. Detailed Implementation

[0032] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0033] It should be noted that when a component is described as "fixed to" another component, it can be directly on the other component or may have a component in between. When a component is considered "connected to" another component, it can be directly connected to the other component or may have a component in between. When a component is considered "set on" another component, it can be directly set on the other component or may have a component in between. The terms "vertical," "horizontal," "left," "right," and similar expressions used in this document are for illustrative purposes only.

[0034] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention pertains. The terminology used herein in the description of the invention is for the purpose of describing particular embodiments only and is not intended to be limiting of the invention. The term "and / or" as used herein includes any and all combinations of one or more of the associated listed items.

[0035] Please also see Figure 1 and Figure 2 A preferred embodiment of the present invention provides a method for collecting data changes from various heterogeneous databases, comprising the following steps:

[0036] S1. Modify the monitoring information of the business service in the monitoring platform configuration, inject components into the business service to request the configuration of the monitoring information from the monitoring platform, and initialize the asynchronous collection task. In step S1, the monitoring information includes service name, database, data table, table fields, encryption fields and encryption method, reporting strategy, and retry strategy.

[0037] S2. The component uses a plugin in a JAR file and, based on the configured listening information, collects data from the database to monitor database changes. When the database log data changes, the plugin parses the changed log data and reports it through the message middleware RabbitMQ. In step S2, the database data collection content includes the target table, the fields to be collected, and the field encryption strategy.

[0038] This embodiment, based on current business requirements and implementation methods, provides data collection tools in the form of JAR file plugins to ensure flexibility and convenience of access. Different plugins are implemented based on the log update rules of each database. The business side injects components into Spring according to the database currently used by the business. Corresponding monitoring platform configurations are also configured for interaction with the monitoring platform.

[0039] In step S2, the plugin parses the changed log data, obtains the corresponding monitored change fields according to the configuration of the listening information, and encrypts the corresponding fields according to the encryption policy to generate a key / value list of columns and values.

[0040] After serializing the key / value list into a JSON string, the plugin puts it into the reporting queue through the message middleware RabbitMQ.

[0041] The component reports log data according to the reporting strategy, and the component captures triggers and polls periodically according to the retry strategy to re-report failed data.

[0042] After the business service injection component starts, it interacts with the change monitoring platform to obtain the corresponding configurations of the data tables and fields that need to be collected on the platform side, and initializes the asynchronous collection task. The component collects data according to the configured content and reports it according to the configured collection and reporting strategy, such as real-time reporting, batch reporting, and scheduled reporting. Simultaneously, it needs to periodically poll according to the configured failure policy to re-report failed data. Policies include: discarding, issuing an alarm after 3 retries, and infinite retries. To ensure the reliability and real-time nature of data reporting, a trigger capture and scheduled polling strategy is adopted.

[0043] Log data is reported according to the configured reporting strategy. To minimize the impact on business service performance, both data collection and reporting are processed asynchronously. Reporting is performed using the RabbitMQ message middleware, which ensures message reliability, real-time performance, and idempotency.

[0044] S3. The change monitoring platform consumes messages in a pull mode to write the changed data into the corresponding ElasticSearch and Hadoop HDFS indexes for storage. The change monitoring platform returns a success ACK after successful consumption.

[0045] In step S3, after the change monitoring platform pulls the log data through the message middleware RabbitMQ, it puts the data into an asynchronous thread. The asynchronous thread writes the data into the corresponding ElasticSearch and Hadoop HDFS indexes according to the configuration information corresponding to the change.

[0046] If the write operation is successful, an ACK indicating successful consumption is returned. If the write operation fails, the system will retry the write operation locally a set number of times, with each retry having an interval. After each local retry fails, the system will attempt to roll back. Once the set number of local retry attempts is reached, an ACK indicating failed consumption is returned, and the message will be delivered again.

[0047] In this embodiment, after the server retrieves data through RabbitMQ, it puts the data into an asynchronous thread. This thread will write the data into the corresponding HDFS and ES indexes according to the changed configuration information. If the writing is successful, it returns an ACK for successful consumption. If the writing fails, it will retry 3 times locally, with an interval of 15 seconds between each of the 3 attempts. After each failure, it will attempt to roll back. After 3 failures, it will return an ACK for consumption failure, and the message will be delivered again.

[0048] This embodiment establishes a unified data acquisition standard, including data format, data interface, and data transmission protocol, to reduce data conversion and mapping work between heterogeneous databases; based on log parsing rules of Java, RabbitMQ, and various databases, a dedicated multi-source heterogeneous database log listening and parsing plugin is developed to parse database logs in different business services, achieving seamless data acquisition and transmission.

[0049] S4. Schedule a hot / cold data conversion monitoring task to retrieve data access records from the access record database and perform hot / cold data conversion based on the data access activity, so as to write highly active hot data to ElasticSearch.

[0050] In step S4, the data hot / cold conversion includes the following steps:

[0051] S4.1 User data access is sent to the record table data, and the record table data writes the data access record to the access record database.

[0052] S4.2 uses the LRU and LFU algorithms to filter hot and cold data.

[0053] In step S4.2, the LRU algorithm is used to filter out the N most recently accessed data items within the most recent preset time period in the access records to obtain the most recently accessed data items; the LFU algorithm is used to filter out the M most recently accessed data items with the highest access frequency within the most recently preset time period to obtain hot data; and cold data is obtained by filtering the hot data in the record database.

[0054] S4.3 removes cold data and data not written within the most recent preset time from Elasticsearch.

[0055] S4.4 writes frequently accessed data that does not exist in Elasticsearch to Elasticsearch. It does not operate on frequently accessed data that already exists in Elasticsearch, thus maintaining highly active frequently accessed data in Elasticsearch.

[0056] Currently, hot data is defined as data generated within the last month and frequently accessed data within the last month. For data generated with time as the table name, Elasticsearch's own index expiration policy can be relied upon; the index will be automatically deleted when the corresponding date expires. Therefore, this embodiment uses Elasticsearch as the preferred platform for storage and querying highly active hot data within the last month (preset timeframe). Its superior search performance and flexible indexing mechanism can quickly respond to complex query needs, significantly improving data retrieval efficiency. For cold data with low access frequency and a recent preset timeframe, the Hadoop HDFS distributed storage system is used for storage. Hadoop HDFS, with its high scalability, fault tolerance, and cost-effectiveness, provides an ideal storage environment for massive amounts of cold data. Through the use of parallel processing technology, even when processing large-scale datasets, efficient data access and processing capabilities are maintained, laying a solid foundation for data analysis and long-term archiving.

[0057] By using LRU and LFU algorithms to filter and identify hot and cold data, the core basis for calculating data access activity is established. This approach cleverly avoids potential interference from historically extreme high-frequency data on the evaluation of recently pre-defined hot data, thus significantly improving the accuracy of data activity calculation. Active data filtered by this algorithm will be preferentially retained in Elasticsearch for rapid response to query requests. Data deemed inactive and not recently written will automatically undergo a "coldening" process, migrating it from Elasticsearch to optimize storage resources and improve overall system performance. This achieves intelligent data management while ensuring efficient data access and maximized utilization of system resources.

Claims

1. A method for collecting data change information adapted to multiple heterogeneous databases, characterized in that, Includes the following steps: S1. Change the monitoring information of the business service in the monitoring platform, inject components into the business service to request the configuration of the monitoring information from the monitoring platform, and initialize the asynchronous collection task; In step S1, the monitoring information includes service name, database, data table, table fields, encryption fields and encryption method, reporting strategy, and retry strategy; S2. The component collects data from the database through a plugin in a JAR file and according to the configuration of the listening information to monitor database changes. When the log data of the database changes, the plugin parses the changed log data and reports the log data through the message middleware RabbitMQ. In step S2, the data collection content of the database includes the target table, the fields to be collected, and the field encryption strategy. S3. The change monitoring platform consumes messages in a pull mode to write change data into the corresponding ElasticSearch and Hadoop HDFS indexes for storage. The change monitoring platform returns a successful ACK after successful consumption. In step S3, after the change monitoring platform pulls the log data through the message middleware RabbitMQ, it puts the data into an asynchronous thread. The asynchronous thread writes the data into the corresponding ElasticSearch and Hadoop HDFS indexes according to the configuration information corresponding to the change. If the write operation is successful, an ACK indicating successful consumption is returned. If the write operation fails, the local system retryes the write operation a set number of times, with an interval set between each retry. After each local retry fails, a rollback is attempted. Once the set number of local retry attempts is reached, an ACK indicating failed consumption is returned, and the message is delivered again. S4. Schedule a hot / cold data conversion monitoring task to retrieve data access records from the access record database and perform hot / cold data conversion based on the data access activity, so as to write highly active hot data to ElasticSearch.

2. The method for collecting data changes adapted to multiple heterogeneous databases according to claim 1, characterized in that: In step S2, the plugin parses the changed log data, obtains the corresponding monitoring change fields according to the configuration of the listening information, and encrypts the corresponding fields according to the encryption strategy to generate a key / value list of columns and values. The plugin serializes the key / value list into a JSON string and then puts it into the reporting queue through the message middleware RabbitMQ.

3. The method for collecting data changes adapted to multiple heterogeneous databases according to claim 1, characterized in that: The component reports the log data according to the reporting strategy, and the component performs trigger capture and periodic polling according to the retry strategy to re-report failed data.

4. The method for collecting data changes adapted to multiple heterogeneous databases according to claim 1, characterized in that: In step S4, the data hot / cold conversion includes the following steps: S4.1 The user's data access is sent to the record table data, and the record table data writes the data access record to the access record database; S4.2 Hot and cold data are obtained by filtering using the LRU and LFU algorithms; S4.3 Removes cold data and data not written within the most recent preset time from Elasticsearch; S4.4 writes hot data that does not exist in Elasticsearch to Elasticsearch.

5. A method for collecting data changes adapted to multiple heterogeneous databases according to claim 4, characterized in that: In step S4.2, the LRU algorithm is used to filter out the N most recently accessed data items within the most recent preset time period from the access records to obtain the most recently accessed data items; the LFU algorithm is used to filter out the M most recently accessed data items with the highest access frequency within the most recently preset time period to obtain hot data; and cold data is obtained by filtering the hot data in the record database.

Images