Method for recording latest historical data change based on multiple different data sources

Abstract

The invention discloses a method for recording latest historical data change based on multiple different data sources, which comprises the following steps of: S1, when any data in any physical table changes, reading a whole row of records where the changed data in the physical table is located into a data transfer library; s2, in the data transfer library, reading and judging whether all fields in the record exist in a summary table or not, if not, adding a summary table column, on this basis, if no data exists in the fields of the same keyword in the record, directly inserting data in the fields, and if no data exists in the fields of the same keyword in the record, adding the summary table column; if data exists in the field of the same main keyword in the record, updating the data in the field; s3, when any data in the summary sheet changes, the whole line of records where the changed data are located are read into the data public consumption library, and integration obtaining and reading of data at multiple positions are achieved; according to the invention, a plurality of different data are integrated into one place, and a plurality of changes are converted into one change, so that the acquisition and reading of the data at the plurality of places are simplified.

Description

Technical Field

[0001] The present invention relates to the field of information processing, and in particular to a method for recording the latest historical data changes based on multiple remote data sources. Background Art

[0002] In the field of real-time data processing, it is often necessary to pay attention to the changes in the attribute information of a certain type of entity in real time, and to take relevant actions in real time based on these information changes. For the flexible employment field, relevant business data exists in multiple physical tables. Since the data in each physical table changes frequently, in actual data applications, it is necessary to combine the historical data snapshots with the data changes in the current data table. Based on the above scenario, there are three problems: 1. No matter which table is used as the basis for observing data changes, the information of other physical tables will be missing; 2. If the changes in a certain physical table are used as the basis for associating the remaining physical tables, the changes in other physical tables cannot be obtained in time, and multiple associations will lead to association performance problems; 3. Traditional databases do not have the ability to actively monitor changes, and can only be queried in a polling manner, which results in inconsistency between the query time and the data change time. The data may have changed multiple times during the query. Summary of the invention

[0003] The purpose of the present invention is to provide a method for recording the latest historical data changes based on multiple data sources in different places. The present invention integrates multiple data in different places into one place and converts multiple changes into one change, thereby simplifying the acquisition and reading of multiple data.

[0004] The technical solution of the present invention is a method for recording the latest historical data changes based on multiple remote data sources, which is carried out according to the following steps:

[0005] Step S1: when any data in any physical table changes, the entire row of records where the changed data is located in the physical table is read into the data transfer library;

[0006] Step S2: In the data transfer library, read and determine whether all the fields in the record exist in the summary table. If not, add a summary table column first. On this basis, if there is no data under the field with the same keyword in the record, directly insert data under the field. If there is data under the field with the same primary keyword in the record, update the data under the field.

[0007] Step S3: When any data in the summary table changes, the entire row of records containing the changed data is read into the data public consumption library to achieve integrated acquisition and reading of data from multiple locations.

[0008] In the above method of recording the latest historical data changes based on multiple remote data sources, the data changes in the physical table are obtained through MySQL binlog.

[0009] In the aforementioned method of recording the latest historical data changes based on multiple remote data sources, the entire row record where the changed data is located is obtained through Row-based recording.

[0010] In the aforementioned method for recording the latest historical data changes based on multiple remote data sources, the summary table uses the holo summary table and obtains data changes through the holo binlog.

[0011] In the aforementioned method of recording the latest historical data changes based on multiple remote data sources, the operation process of the holo binlog is carried out according to the following steps:

[0012] Step A1: Enable the logical replication function in the source database, and configure and enable related parameters;

[0013] Step A2: Create a logical replication slot in the source database and obtain the logical replication stream through the slot. The slot will track the WAL log position of the source database.

[0014] Step A3: Set up a logical replication connection in the target database, subscribe to the logical replication stream of the source database, and convert the data operations in the logical replication stream into actual data change operations in the target database through the logical decoder;

[0015] Step A4: The target database performs corresponding data change operations according to the change operations received in the logical replication stream.

[0016] In the aforementioned method of recording the latest historical data changes based on multiple remote data sources, the flink consumer program consumes the judgment records and changes the summary table accordingly.

[0017] In the aforementioned method of recording the latest historical data changes based on multiple remote data sources, the change records are read through Flink CDC to the data transfer library and the data public consumption library.

[0018] In the aforementioned method of recording the latest historical data changes based on multiple remote data sources, the operation process of the flink cdc is performed according to the following steps:

[0019] Step B1: Enable the data transfer library and the data public consumption library;

[0020] Step B2: Flink monitors the change records of the physical table and summary table and parses the change events;

[0021] Step B3: Convert the change event into a Flink data stream;

[0022] Step B4: Aggregate, filter and connect the Flink data stream to the corresponding data transfer library and data public consumption library.

[0023] In the aforementioned method for recording the latest historical data changes based on multiple remote data sources, the data transfer library and the data public consumption library are kafka topics.

[0024] In the aforementioned method for recording the latest historical data changes based on multiple remote data sources, the writing process of the data transfer library and the data public consumption library is that when flink cdc sends a message to a topic, it first selects a partition according to the key of the message or the polling algorithm, and then flink cdc appends the message to the current active segment of the leader copy of the partition; the reading process of the data transfer library and the data public consumption library is to read the message from the specified partition, and choose to read the message directly from the leader copy or from the follower copy when reading.

[0025] Compared with the prior art, the present invention obtains the entire row record of the changed data in the physical table through mysql binlog, reads it to the data transfer public kafka topic record through the flink cdc method, and then the flink consumer program determines whether all the field names in the record exist in the holo summary table, performs corresponding operations of adding fields, inserting data or updating data, and integrates multiple different data and data changes into the same holo summary table; the entire row record holo binlog generated by the changed data in the holo summary table is read by the flink cdc method to the data public consumption kafka topic, which solves the problem of simultaneously observing multiple data changes in the real-time data processing field and failing to obtain all changes in time, and the changed data with the same primary key field will be stored in the same row of the summary table, so that when the data changes, all the data corresponding to all the fields that have changed historically corresponding to the same primary key field can be carried out for use. BRIEF DESCRIPTION OF THE DRAWINGS

[0026] Figure 1 It is a schematic diagram of the process of the present invention;

[0027] Figure 2 It is a flowchart of the flink consumer program judgment of the present invention. DETAILED DESCRIPTION

[0028] The present invention is further described below in conjunction with the accompanying drawings and embodiments, but they are not intended to limit the present invention.

[0029] Embodiment 1: A method for recording the latest historical data changes based on multiple different data sources, as shown in the attached Figure 1As shown in the figure, the mysql binlog generated by the physical table data change is read into the data transfer public kafka topic record through the flink cdc method, and then the flink consumer program determines whether all the field names in the record exist in the holo summary table, and performs the corresponding operations of adding fields, inserting data, or updating data; the whole row record holo binlog generated by the changed data in the holo summary table is read into the data public consumption kafka topic by the flink cdc method. Each record in the data public consumption kafka topic is all the column data of each row in the holo summary table as of the latest data change.

[0030] As attached Figure 2 As shown, the process of the flink consumer program judging the record and the holo summary table performing corresponding operations is carried out in the following steps:

[0031] Step S1: The flink consumer program determines whether the record field name of the data transfer public kafka topic exists in the holo summary table. If it exists, proceed to the next step; if it does not exist, add a holo summary table column and insert this field into the holo summary table, and proceed to the next step;

[0032] Step S2: Determine whether the data under a certain field of the record to be modified already exists. If it does, update the data under the field. If it does not exist, insert the data under the field.

[0033] mysql binlog method: mysql binlog (binary log) is an important log file in the mysql database, which records all insert, delete, update and other modification operations of the database. It is very important for scenarios such as data recovery, master-slave replication, and data auditing; description of the working process of mysql binlog: first, set the parameter log_bin=mysql-bin in the mysql configuration file to enable binlog. This method relies on row-based logging. Any data change will bring out the entire row record and record the event. When a data modification operation (such as INSERT, UPDATE, DELETE) is executed on the database, mysql will generate the corresponding binlog event and record it in the binlog file. Each binlog file has a unique file name, and inside the file, each record has a unique log position. This information helps mysql determine where to start applying binlog events to achieve data recovery or replication.

[0034] Flink method: Apache Flink is an open source stream processing framework used for real-time data stream processing and batch processing tasks, including the following basic principles and core components:

[0035] 1. Stream Processing Model

[0036] Flink adopts an event-driven stream processing model. Its core concept is to regard data streams as continuous event streams. In this model, data is generated, processed and transmitted in real time, rather than processed in batches. Key concepts include: Data stream: Data stream is the core data structure in Flink, representing a continuous event stream. Data stream can be unbounded (real-time generated event stream) or bounded (a collection of data over a period of time); State: Flink supports maintaining and accessing state during stream processing, which allows tracking and managing state information when processing complex events. State can be keyed state or operator state; Time semantics (Event Time): Flink supports event time processing, allowing data to be processed based on the time when the event actually occurs, rather than relying on the time when the system processes the data.

[0037] 2. Core components and principles

[0038] Job Graph: Flink jobs are converted into job graphs, which describe the data flow and operation process of the job, including data sources, data conversion operators and data sinks; Source and Sink: The data source is used to read the data stream, while the data sink is used to write the processed data. Flink provides a variety of connectors, such as Kafka, HDFS, JDBC, etc., to facilitate integration with external systems; Transformation: The transformation operator is the core component for operating and transforming data streams, including map, filter, reduce, join and other operations, allowing developers to perform complex logical processing on data streams; Execution Engine and Task Scheduling: Flink uses a distributed execution engine to map the job graph to tasks in the physical cluster, which is responsible for the scheduling and execution of tasks. Tasks exchange and communicate data through the network; Fault Tolerance: Flink's fault tolerance mechanism is based on Checkpoint and replay to ensure that jobs can be restored to a consistent state in the event of node failure or other abnormal conditions.

[0039] flink cdc method: flink cdc (Change Data Capture) refers to the use of Apache Flink to implement the data change capture method in real-time data stream processing, capturing data changes in the database (such as INSERT, UPDATE, DELETE and other operations) in real time, and processing and analyzing these changes as data streams. Specific instructions:

[0040] Flink cdc is used in conjunction with mysql binlog and holo binlo to achieve change capture of real-time data streams. The main steps are as follows:

[0041] Step B1: Enable binlog of MySQL and Holo databases so that Flink can access these logs.

[0042] Step B2: Flink monitors the binlog of the database and parses change events (such as INSERT, UPDATE, DELETE, etc.);

[0043] Step B3: Convert the captured database change events into Flink’s data stream, usually by mapping the records into the data format in the Flink data stream (such as JSON, Avro, etc.);

[0044] Step B4: Aggregate, filter and connect the Flink data stream to the corresponding data transfer public Kafka topic or data public consumption Kafka topic.

[0045] Kafka method: Kafka is a distributed stream processing platform, in which the basic unit is the topic, which is responsible for storing and organizing message data. The principle of Kafka topic recording data involves the following key concepts and components:

[0046] 1. Topic and partition

[0047] In Kafka, a topic is a logical container for messages, which is used to classify and organize messages. Each topic can be divided into one or more partitions. A partition is a physical storage unit of a topic. Each partition is an ordered, immutable sequence of messages. Each message is assigned to a specific partition, and the insertion order of the messages is maintained in the partition.

[0048] 2. Storage and replication of messages

[0049] Each partition consists of one or more segments. Segment is the basic unit of message persistence in Kafka. Each segment is a file, usually in the form of a log segment. Kafka's message storage adopts a log-structured structure, that is, the messages in each partition are written to the segment in the order of appending, and message update or deletion operations are not supported, ensuring high-performance sequential writing and appending operations.

[0050] 3. Writing and reading process

[0051] The writing process is when Flink CDC sends a message to a topic. It first selects a partition according to the key of the message or the polling algorithm, and then Flink CDC appends the message to the currently active segment of the leader copy of the partition. The reading process is when the Flink consumer program or consumer reads the message from the specified partition. When reading, it chooses to read the message directly from the leader copy or from the follower copy (usually used for load balancing and fault tolerance). Kafka ensures that the consumer can maintain a connection with the Kafka Broker during the consumption process to achieve efficient message push.

[0052] 4. Application scenarios

[0053] Kafka's message recording principle is applicable to various real-time data processing and transmission scenarios, including: real-time log collection and analysis, asynchronous communication between distributed systems, data pipeline construction and data integration, streaming data processing and real-time push.

[0054] Kafka achieves efficient message storage, transmission, and consumption through the organization of topics and partitions.

[0055] Holo binlog method: Holo is a cloud-native analytical data warehouse solution provided by Alibaba Cloud, and Holo's binlog method refers to the change data capture (CDC) function it supports. It has replication and streaming replication mechanisms and can be used to implement a binlog-like function called logical replication. This function allows real-time capture of Holo data changes to enable real-time data analysis and query. The main reason why this method uses Holo summary tables and Holobinlog is that compared with traditional MySQL databases, Holo supports large-scale data analysis and processing and provides high availability, data consistency assurance, flexibility and scalability, real-time data integration, data distribution, and load balancing.

[0056] 1. Basic principles of logical replication

[0057] Logical replication refers to the process of converting data change operations (such as INSERT, UPDATE, DELETE) in the source database into logical insert, update, and delete operations, and streaming these operations to one or more target databases; using WAL logs to ensure data persistence and transaction atomicity, WAL logs record all modification operations performed by the database engine on disk; the core of logical replication is to decode logical data change operations from WAL logs through logical decoders. The logical decoder parses WAL records into a series of database operations (such as INSERT, UPDATE, DELETE) and generates the corresponding logical replication stream; publisher and subscriber: In logical replication, the source database acts as a publisher, responsible for generating the logical replication stream; and the target database acts as a subscriber, responsible for receiving and applying the change operations in the logical replication stream.

[0058] 2. Steps to implement holo binlog function

[0059] Step A1: Enable the logical replication function in the source Holo database by configuring the `wal_level` parameter to `logical` and enabling the related parameters `max_replication_slots` and `wal_sender_timeout`;

[0060] Step A2: Create a logical replication slot in the source database. Flink CDC obtains the logical replication stream through this slot. The slot tracks the WAL log position of the source database to ensure that Flink CDC recovers to the correct position.

[0061] Step A3: Set up a logical replication connection in the target database, subscribe to the logical replication stream of the source database, and convert the data operations in the logical replication stream into actual data change operations in the target database through the logical decoder;

[0062] Step A4: The target database performs corresponding data change operations (such as INSERT, UPDATE, and DELETE) according to the change operations received in the logical replication stream.

[0063] Example 2: Application of a method for recording the latest historical data changes based on multiple different data sources. Now assume that the holo table has been created and has only one column for job_id; assume that the existing job information table is as follows: Table 1

[0064] job_id title town class 123 Campus Part-time Job Hangzhou Waiter

[0065] Table 1

[0066] job_id title town class 123 Campus Part-time Job Shanghai Waiter

[0067] Table 2

[0068] As shown in Table 2, the town field in the job information table changes from Hangzhou to Shanghai. The generated mysqlbinlog is read by flink cdc to the data transfer public kafka topic and recorded as follows: {"job_id":"123","title":"Part-time job on campus","town":"Shanghai","class":"Waiter"}. The holo summary table adds new fields and inserts data. "123" in "job_id" is the primary key field, and the record is shown in Table 3 below.

[0069] job_id title town class 123 Campus Part-time Job Shanghai Waiter

[0070] Table 3

[0071] Then the binlog generated by holo is read by flink cdc to the data public consumption kafka topic, and the data is {"job_id":"123","title":"Part-time job on campus","town":"Shanghai","class":"Waiter"};

[0072] Assume that the existing job display table changes as shown in Table 4 and Table 5

[0073] job_id iS_show show_platform 123 1 Android, IOS

[0074] Table 4

[0075] job_id is_show show_platform 123 1 Android

[0076] Table 5

[0077] The show_platform field changes from "Android, IOS" to "Android", and the generated MySQL binlog is read by Flink CDC to the public Kafka topic for data transfer, and the records are as follows: {"job_id":"123","is_show":"1","show_platform":"Android"}, and the holo summary table adds new fields and inserts data, as shown in Table 6 below.

[0078] job_id title town class is_show show_platform 123 Campus Part-time Job Shanghai Waiter 1 Android

[0079] Table 6

[0080] The binlog generated by holo binlog is read by flink cdc to the data public consumption kafka topic data is

[0081]

[0082] Now assume that the job statistics table changes as shown in Table 7 and Table 8

[0083] job_id view_cnt sign_cnt 123 20 15

[0084] Table 7

[0085] job_id view_cnt sign_cnt 123 25 18

[0086] Table 8

[0087] The view_cnt and sign_cnt fields change. View_cnt changes from 20 to 25, and sign_cnt changes from 15 to 18. The generated MySQL binlog is read by Flink CDC to the public Kafka topic for data transfer and recorded as follows:

[0088] {"job_id":"123","view_cnt":"25","sign_cnt":"18"}, add new fields and insert data into the holo summary table, and the records are shown in Table 9 below

[0089] job_id title town class is_show show_platform view_cnt sign_cnt 123 Campus Part-time Job Shanghai Waiter 1 Android 25 18

[0090] Table 9

[0091] The binlog generated by holo binlog is read by flink cdc to the data public consumption kafka topic data is

[0092]

[0093] Now assume that a row of data is added to the position information table as shown in Table 10 and Table 11

[0094] job_id title town class 123 Campus Part-time Job Shanghai Waiter

[0095] Table 10

[0096] job_id title town class 123 Campus Part-time Job Shanghai Waiter 456 Barista recruitment Beijing Barista

[0097] Table 11

[0098] The job_id is 456, the title is Barista Recruitment, the town is Beijing, and the class is Barista. The generated MySQL binlog is read by the fl ink cdc to the data transfer public kafka topic and the record is as follows: {"job_id":"456","title":"Barista Recruitment","town":"Beijing","class":"Barista"}, and the data is inserted into the holo summary table, as shown in Table 12 below

[0099]

[0100] Table 12

[0101] The binlog generated by holo binlog is read by flink cdc to the data public consumption kafka topic data is

[0102]

[0103] Now assume that the data of position 123 in the position information table changes as shown in Table 13 and Table 14

[0104] job_id title town class 123 Campus Part-time Job Shanghai Waiter 456 Barista recruitment Beijing Barista

[0105] Table 13

[0106] job_id title town class 123 Campus Part-time Job Shanghai Cashier 456 Barista recruitment Beijing Barista

[0107] Table 14

[0108] The class field of the position with job_id 123 changes from waiter to cashier. The generated mysqlbinlog is read by flink cdc to the public kafka topic for data transfer and recorded as follows: {"job_id":"123","title":"Part-time job on campus","town":"Shanghai","class":"Cashier"}. The data is updated in the holo summary table, as shown in Table 15 below.

[0109]

[0110] Table 15

[0111] The binlog generated by holo binlog is read by flink cdc to the data public consumption kafka topic data is

[0112]

[0113] Now assume that the new records in the job display table are as shown in Table 16 and Table 17

[0114] job_id is_show show_platform 123 1 Android

[0115] Table 16

[0116] job_id is_show show_platform 123 1 Android 456 0 iOS

[0117] Table 17

[0118] When new data is added, the generated MySQL binlog is read by Flink CDC to the data transfer public Kafka topic and recorded as follows:

[0119] {"job_id":"456","is_show":"0","show_platform":"IOS"}, update data in the holo summary table, and record as shown in Table 18 below

[0120]

[0121] Table 18

[0122] The binlog generated by holo binlog is read by flink cdc to the data public consumption kafka topic data is {

[0123]

[0124]

[0125] Now assume that the new data in the job statistics table is as shown in Table 19 and Table 20

[0126] job_id view_Cnt sig n_cnt 123 25 18

[0127] Table 19

[0128] job_id view_cnt sign_cnt 123 25 18 456 0 0

[0129] Table 20

[0130] When new data is added, MySQL binlog is generated and read by f1 ink cdc to the data transfer public kafka topic. The records are as follows:

[0131] {"job_id":"456","view_cnt":"0","sign_cnt":"0"}, update data in the holo summary table, and record as shown in Table 21 below

[0132]

[0133] Table 21

[0134] The binlog generated by holo binlog is read by flink cdc to the data public consumption kafka topic data is

[0135]

[0136] In actual applications, the above examples are not limited by the number of tables, the number of table fields, or the number of holo table fields. As long as the data changes are written to the public Kafka topic for data transfer, the data consumer program can automatically add holo table data columns and the holo table can automatically generate holo binlog for use by downstream programs.

[0137] In summary, the present invention integrates multiple data from different places into one place through the mysql binlog method, the flink method, the flink cdc method, the kafka method and the holo binlog method, solves the problem that the real-time data processing field simultaneously observes data changes in multiple places but cannot obtain all changes in time, converts multiple changes into one change, and each data change will carry out the data of the latest historical changes and record the latest historical changes. When the data changes, all the data of the same primary key field that has changed in history can be carried out for use; the snapshot information of the latest historical changes of the data source in different places can be recorded in one table, and all complete data will be recorded in this table after multiple data changes; the performance problem of multi-table association is solved, and multiple tables are changed into one table, which solves the problem that only partial information can be obtained each time the data changes in the real-time data processing field; it can be used as a data source for real-time data marking, simplifies the problem of obtaining data from multiple places, solves the problem that multiple data changes cannot be monitored in time, simplifies the problem of storing and reading multiple data in different places, and simplifies the real-time data development process.

Claims

1. A method for recording the latest historical data changes based on multiple remote data sources, characterized in that: Follow these steps: Step S1: when any data in any physical table changes, the entire row of records containing the changed data in the physical table is read into the data transfer library; Step S2: In the data transfer library, read and determine whether all the fields in the record exist in the summary table. If not, add a summary table column first. On this basis, if there is no data under the field with the same keyword in the record, directly insert data under the field. If there is data under the field with the same primary keyword in the record, update the data under the field. Step S3: When any data in the summary table changes, the entire row of records containing the changed data is read into the data public consumption library to achieve integrated acquisition and reading of data from multiple locations.

2. The method for recording the latest historical data changes based on multiple remote data sources according to claim 1, characterized in that: Get data changes in the physical table through mysql binlog.

3. The method for recording the latest historical data changes based on multiple remote data sources according to claim 2, characterized in that: Get the entire row of records where the changed data is located through Row-based logging.

4. The method for recording the latest historical data changes based on multiple remote data sources according to claim 1, characterized in that: The summary table uses the holo summary table and obtains data changes through the holo binlog.

5. The method for recording the latest historical data changes based on multiple remote data sources according to claim 4, characterized in that: The operation process of holo binlog is carried out in the following steps: Step A1: Enable the logical replication function in the source database, and configure and enable related parameters; Step A2: Create a logical replication slot in the source database and obtain the logical replication stream through the slot. The slot will track the WAL log position of the source database. Step A3: Set up a logical replication connection in the target database, subscribe to the logical replication stream of the source database, and convert the data operations in the logical replication stream into actual data change operations in the target database through the logical decoder; Step A4: The target database performs corresponding data change operations according to the change operations in the received logical replication stream.

6. The method for recording the latest historical data changes based on multiple remote data sources according to claim 1, characterized in that: The flink consumer program consumes the judgment records and changes the summary table accordingly.

7. The method for recording the latest historical data changes based on multiple remote data sources according to claim 1, characterized in that: Use flink cdc to read change records to the data transfer library and the data public consumption library.

8. The method for recording the latest historical data changes based on multiple remote data sources according to claim 7, characterized in that: The operation process of the flink cdc is carried out in the following steps: Step B1: Enable the data transfer library and the data public consumption library; Step B2: Flink monitors the change records of the physical table and summary table and parses the change events; Step B3: Convert the change event into a Flink data stream; Step B4: Aggregate, filter and connect the Flink data stream to the corresponding data transfer library and data public consumption library.

9. The method for recording the latest historical data changes based on multiple remote data sources according to claim 8, characterized in that: The data transfer library and the data public consumption library are kafka topics.

10. The method for recording the latest historical data changes based on multiple remote data sources according to claim 9, characterized in that: The writing process of the data transfer library and the data public consumption library is that when Flink CDC sends a message to a topic, it first selects a partition according to the key of the message or the polling algorithm, and then Flink CDC appends the message to the current active segment of the leader copy of the partition; the reading process of the data transfer library and the data public consumption library is to read the message from the specified partition, and when reading, it chooses to read the message directly from the leader copy or from the follower copy.

Images