Data change content collection method adaptive to various heterogeneous databases

Abstract

The invention relates to the technical field of data processing, in particular to a data change content acquisition method adaptive to various heterogeneous databases, which comprises the following steps of: configuring monitoring information of a business service on a change monitoring platform, injecting a component into the business service so as to request to pull the configuration of the monitoring information from the monitoring platform, the asynchronous acquisition task is initialized; the component monitors database change through a plug-in of a JAR package file, and when log data of the database is changed, the plug-in reports the log data through message middleware Rabbit MQ; the change monitoring platform carries out message consumption through a pull mode so as to write change data in a corresponding ElasticSearch and a corresponding Hadoop HDFS (Hadoop Distributed File System) index; and carrying out cold-hot conversion on the data. According to the invention, seamless acquisition and transmission of data can be carried out, the real-time performance, reliability and idempotence of change acquisition are ensured, and the operation and maintenance cost of data storage is saved.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a method for collecting data change content adapted to multiple heterogeneous databases. Background Art

[0002] With the continuous development of information technology, the water conservancy industry is gradually transforming towards intelligence and information technology. During this transformation process, the water conservancy information system needs to process massive data from different sources, formats, and structures. These data are often stored in a variety of heterogeneous databases, such as Oracle, MySQL, SQL Server, King Base, and DAMO, each of which has its own unique data storage and access mechanism. Therefore, how to effectively collect data changes in these heterogeneous databases has become an important 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 type, etc. between heterogeneous databases, which requires complex conversion and mapping during the data collection process, increasing the complexity and difficulty of data collection.

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

[0005] 3. Real-time and accuracy issues: The water conservancy information system has high requirements for the real-time and accuracy of data, but the existing data collection solutions often cannot meet these two requirements at the same time. For example, although the data collection solution based on logs has good real-time performance, it may cause data accuracy to decrease due to log loss or damage; while the data collection solution based on periodic polling has high accuracy but poor real-time performance.

[0006] 4. Insufficient scalability and flexibility: With the continuous development of water conservancy information systems and the access of new and old reservoir projects, new data sources and data types continue to emerge. Existing data collection solutions are often difficult to quickly adapt to these changes and require a lot of customized development work, resulting in insufficient system scalability and flexibility.

[0007] 5. Massive data storage issues: The amount of water conservancy-related data is very large, and traditional storage systems are difficult to meet the growing capacity requirements. In addition, there are single-node performance issues that make it difficult to support high-concurrency, high-throughput data access requirements. Large-scale data storage brings not only technical challenges, but also high hardware purchase, operation and maintenance, and energy consumption costs. Summary of the invention

[0008] In order to solve the above problems, the present invention provides a data change content collection method that is adaptable to multiple heterogeneous databases, which can perform seamless data collection and transmission, while ensuring the real-time, reliability, and idempotence of change collection, and saving the operation and maintenance costs of data storage.

[0009] In order to achieve the above object, the technical solution adopted by the present invention is:

[0010] A data change content collection method adapted to multiple heterogeneous databases includes the following steps:

[0011] S1. Change the monitoring platform configuration business service monitoring information, inject components into the business service to request the monitoring platform to pull the configuration of the monitoring information, and initialize the asynchronous acquisition task;

[0012] S2. The component collects data from the database through a plug-in of a JAR package file and according to the configuration of the monitoring information to monitor database changes, and when the log data of the database changes, the plug-in parses the changed log data and reports the log data through the message middleware Rabbit MQ;

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

[0014] S4. Scheduled start of the hot-cold conversion monitoring task, obtain data access records by accessing the record database, and perform hot-cold conversion of data according to the access activity of the data, so as to write highly active hot data to ElasticSearch.

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

[0016] Further, in step S2, the plug-in parses the changed log data, and after obtaining the corresponding monitoring change field according to the configuration of the monitoring information, encrypts the corresponding field according to the encryption strategy to generate a key / value list of columns and values;

[0017] The plug-in serializes the key / value list into a JSON string and puts it into the reporting queue through the message middleware Rabbit MQ.

[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 the failed reporting data.

[0019] Further, in step S3, after the change monitoring platform pulls the log data through the message middleware Rabbit MQ, the data is put into the asynchronous thread, and the asynchronous thread writes the data into the corresponding ElasticSearch and Hadoop HDFS indexes according to the configuration information corresponding to the change;

[0020] When the write is successful, an ACK of successful consumption is returned; when the write fails, the local retry write is set a number of times, and each retry is set with an interval time. After each local retry fails, an attempt is made to roll back. After the local retry write number reaches the set number of times, an ACK of failed consumption is returned, and the message is 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 obtains hot data and cold data by filtering through LRU algorithm and LFU algorithm;

[0024] S4.3 removes cold data in ElasticSearch and data that was not written within the recent preset time;

[0025] S4.4 writes hot data that does not exist in ElasticSearch to ElasticSearch.

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

[0027] The beneficial effects of the present invention are:

[0028] 1. The plug-in of the present invention realizes log monitoring and parsing for multi-source heterogeneous databases, provides a unified standard data format, and realizes hot plugging through JAR package reference, reducing the intrusion of database change collection on business code. And through the access of message middleware and change reach plus polling scanning, the real-time, reliability and idempotence of change collection are guaranteed, providing solutions for the access of more systems and more types of databases in the future. Moreover, the monitoring and parsing plug-in is issued in the form of jar package, which can achieve rapid development and application when adding new database types; at the same time, based on the content reported by the business-side monitoring and parsing plug-in received by the message middleware for data storage, the intrusion of data collection on the business-side code is reduced, and the decoupling of business and collection code is realized.

[0029] 2. Adopt the hot and cold separation storage method, and use ElasticSearch to store the hot data collected recently to improve the efficiency of data query. For the cold data collected not recently, a distributed storage system such as Hadoop HDFS is used to realize the distributed storage and parallel processing of data. The hot and cold data separation solution effectively improves the storage capacity and access speed, and saves the operation and maintenance cost of data storage. Moreover, by dividing the data into hot data and cold data, and storing them on storage media with different performance and cost respectively, it can avoid storing all data on high-performance and high-cost storage devices. Cold data can be stored on low-cost storage media, such as cloud storage or low-speed hard disks, thereby significantly reducing storage costs. At the same time, hot data is stored on high-performance storage devices, which can provide faster read and write speeds to speed up data processing speed and response time. In addition, it can avoid read and write conflicts of hot data and improve the overall performance of the storage system. Since cold data has a low access frequency, it will not cause excessive load pressure on the storage system. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] Figure 1 It is a flowchart of a data change content collection method adapted to multiple heterogeneous databases according to a preferred embodiment of the invention.

[0031] Figure 2 It is a data flow diagram of a data change content collection method that is adaptable to a variety of heterogeneous databases according to a preferred embodiment of the invention. DETAILED DESCRIPTION

[0032] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0033] It should be noted that when a component is referred to as being "fixed to" another component, it may be directly on the other component or there may also be a component centered. When a component is considered to be "connected to" another component, it may be directly connected to the other component or there may also be a component centered. When a component is considered to be "set on" another component, it may be directly set on the other component or there may also be a component centered. The terms "vertical", "horizontal", "left", "right" and similar expressions used herein are for illustrative purposes only.

[0034] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as those commonly understood by those skilled in the art of the present invention. The terms used herein in the specification of the present invention are only for the purpose of describing specific embodiments and are not intended to limit the present invention. The term "and / or" used herein includes any and all combinations of one or more related listed items.

[0035] Please also see Figure 1 and Figure 2 A data change content collection method adapted to multiple heterogeneous databases according to a preferred embodiment of the present invention comprises the following steps:

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

[0037] S2. The component collects data from the database through the plug-in of the JAR package file and according to the configuration of the monitoring information to monitor database changes. When there is a change in the log data of the database, the plug-in parses the changed log data and reports the log data through the message middleware Rabbit MQ. In step S2, the data collection content of the database includes the collection target table, collection fields, and field encryption strategy.

[0038] This embodiment is based on the current business demands and implementation methods. In order to ensure the flexibility and convenience of access, a data collection tool is provided in the form of a plug-in in a JAR package file. Different plug-ins are implemented based on the log update rules of each database. The business side injects components into Spring according to the database used by the current business. And configure the corresponding monitoring platform configuration for interaction with the monitoring platform.

[0039] In step S2, the plug-in parses the changed log data, obtains the corresponding monitoring change fields according to the configuration of the monitoring information, and then encrypts the corresponding fields according to the encryption strategy to generate a key / value list of columns and values;

[0040] The plug-in serializes the key / value list into a JSON string and puts it into the reporting queue through the message middleware Rabbit MQ.

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

[0042] After the business service injection component is started, it interacts with the change monitoring platform to obtain the corresponding configuration of the data tables and fields that need to be collected on the change monitoring platform side, and initializes the asynchronous collection task. The component collects according to the configured content and reports according to the configured collection reporting strategy, such as real-time reporting, batch reporting, and scheduled reporting. At the same time, it is necessary to conduct regular polling according to the configured failure strategy to re-report the failed data. The strategies include: abandonment, alarm notification after 3 retries, unlimited retries, etc. In order to ensure the reliability and real-time nature of data reporting, trigger capture and scheduled polling strategies are adopted.

[0043] The reporting of log data is carried out according to the configured reporting strategy. In order to reduce the impact on business service performance, the collection and reporting are processed asynchronously, and the reporting is carried out using the message middleware Rabbit MQ, which can ensure the reliability, real-time and idempotence of the message.

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

[0045] In step S3, the change monitoring platform pulls the log data through the message middleware Rabbit MQ, and then puts the data into the 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] When the write is successful, an ACK of successful consumption is returned; when the write fails, the local retry write is set a number of times, and each retry is set with an interval time. After each local retry fails, an attempt is made to roll back. After the local retry write number reaches the set number of times, an ACK of failed consumption is returned, and the message is delivered again.

[0047] In this embodiment, after the server pulls the data through Rabbit MQ, it puts the data into the asynchronous thread. The thread will write the data in the corresponding HDFS and ES indexes according to the configuration information corresponding to the change. If the write is successful, an ACK of successful consumption is returned. If the write fails, the local retry is performed 3 times, and the interval between each processing is 15s. After each failure, an attempt is made to roll back. After 3 failures, a consumption failure ACK is returned, and the message will be delivered again.

[0048] This embodiment formulates a unified data collection standard, including data format, data interface, data transmission protocol, etc., to reduce the data conversion and mapping work between heterogeneous databases; based on the log parsing rules of Java, Rabbit MQ and various databases, a special multi-source heterogeneous database log monitoring and parsing plug-in is developed to parse database logs in different business services to achieve seamless data collection and transmission.

[0049] S4. Scheduled start of the hot-cold conversion monitoring task, obtain data access records by accessing the record database, and perform hot-cold conversion of data according to the access activity of the data, 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 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.

[0052] S4.2 obtains hot data and cold data through LRU algorithm and LFU algorithm.

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

[0054] S4.3 removes cold data in ElasticSearch and data that was not written within the most recent preset time.

[0055] S4.4 writes hot data that does not exist in ElasticSearch to ElasticSearch. No operation is performed on hot data that already exists in ElasticSearch, so as to keep highly active hot data in ElasticSearch.

[0056] Since hot data is currently defined as data generated within one month and high-frequency access data within the past month. For data generated with the time dimension as the table name, you can rely on the index expiration policy provided by ElasticSearch itself, and it will be automatically deleted when the date corresponding to the index expires. Therefore, this embodiment uses ElasticSearch as the preferred platform for storage and query for highly active hot data within the last preset time of one month. Relying on its excellent search performance and flexible indexing mechanism, it can quickly respond to complex query requirements and significantly improve data retrieval efficiency. For cold data with a recent preset time and low access frequency, 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 cold data. Through the use of parallel processing technology, even when processing large-scale data sets, efficient data access and processing capabilities can be maintained, laying a solid foundation for data analysis and long-term archiving.

[0057] Hot data and cold data are obtained through the LRU algorithm and LFU algorithm, which are the core calculation basis for data access activity. It also cleverly avoids the potential interference of historical extremely high-frequency data on the evaluation of hot data in the recent preset time, thereby greatly improving the accuracy of data activity calculation. The active data screened by the algorithm of this embodiment will be retained in ElasticSearch first to quickly respond to query needs. For those data that are judged to be inactive and not recently written, the data conversion process will be automatically executed to migrate them from ElasticSearch to optimize storage resources and improve the overall performance of the system. Intelligent management of data is achieved, and the efficiency of data access and the maximum utilization of system resources are ensured.

Claims

1. A data change content collection method adapted to multiple heterogeneous databases, characterized in that: The steps include: S1. In the change monitoring platform configuration business service monitoring information, the business service injection component to the monitoring platform request to pull the configuration of the monitoring information, and initialize the asynchronous acquisition task; S2. The component collects data from the database through a JAR package file plug-in, and collects data based on the configuration of the monitoring information to monitor database changes, and when the database log data changes, the plug-in parses the log data of the change and reports the log data through the messaging middleware Rabbit MQ; S3. The change monitoring platform consumes messages in pull mode to write change data in the corresponding ElasticSearch and the corresponding Hadoop HDFS index to store the change data, and the change monitoring platform returns a successful ACK after successful consumption; S4. Start the hot-cold conversion monitoring task regularly, obtain the data access records by accessing the record database, and perform hot-cold conversion of data according to the access activity of the data to write the highly active hot data to ElasticSearch.

2. According to claim 1, a data change content collection method adapted to multiple heterogeneous databases is characterized by: In step S1, the monitoring information includes service name, database, data table, table field, encrypted field and encryption method, reporting strategy, and retry strategy; in step S2, the data collection content of the database includes collection target table, collection field, and field encryption strategy.

3. The data change content collection method adapted to multiple heterogeneous databases according to claim 2 is characterized by: In step S2, the plug-in parses the changed log data, obtains the corresponding monitoring change fields according to the configuration of the monitoring information, and then encrypts the corresponding fields according to the encryption strategy to generate a key / value list of columns and values; The plug-in serializes the key / value list into a JSON string and puts it into the reporting queue through the message middleware Rabbit MQ.

4. The method for collecting data changes adapted to multiple heterogeneous databases according to claim 2 is 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 the failed reporting data.

5. The data change content collection method adapted to multiple heterogeneous databases according to claim 1 is characterized by: In step S3, the change monitoring platform pulls the log data through the message middleware Rabbit MQ and 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. When the write is successful, an ACK of successful consumption is returned; when the write fails, the local retry write is set a number of times, and each retry is set with an interval time. After each local retry fails, an attempt is made to roll back. After the local retry write number reaches the set number of times, an ACK of failed consumption is returned, and the message is delivered again.

6. 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 obtain hot data and cold data through LRU algorithm and LFU algorithm; S4.3 Remove cold data in ElasticSearch and data that was not written within the recent preset time; S4.4 Write hot data that does not exist in ElasticSearch to ElasticSearch.

7. The data change content collection method adapted to multiple heterogeneous databases according to claim 6 is characterized by: In step S4.2, the N data most recently accessed within the most recent preset time in the access record are filtered out by the LRU algorithm to obtain the most frequently accessed data items; the M data with the highest access frequency within the most recent preset time in the most frequently accessed data items are filtered out by the LFU algorithm to obtain hot data; and cold data are obtained by filtering the hot data in the record database.

Images