Database real-time zipper generation method and system

Abstract

The invention relates to the technical field of data real-time processing, and provides a database real-time zipper generation method and system, and the method comprises the steps: carrying out the parallel query of total historical data from a business database table, obtaining incremental log data, and connecting the total historical data with the incremental log data; uniformly converting the total historical data and the incremental log data into data change events with consistent formats, carrying out shunting according to metadata information of a source table, and converting the data change events into data streams which can be processed by a stream calculation engine to obtain data stream of the data change events; adding zipper information to the data change event data stream, and converting the data change event data stream into a data change event data stream with the zipper information; and converting the data change event data stream with the zipper information into a data operation statement of a downstream analysis library so as to push the data operation statement to the downstream analysis library for execution. The method has the characteristics of low cost, real-time performance and high-efficiency generation of the zipper table.

Description

Technical Field

[0001] The invention belongs to the technical field of real-time data processing, and in particular relates to a database real-time zipper generation method and system. Background Art

[0002] The statements in this section merely provide background information related to the present invention and do not necessarily constitute prior art.

[0003] In the field of modern information technology, the real-time and accuracy of data are crucial to decision support systems. In order to maintain the historical and latest status of data, a common technical means is to use zipper tables, which are a special data structure that can record the trajectory of data changes over time, thus facilitating data analysis and historical queries.

[0004] The existing zipper table technology has the following problems:

[0005] First, the generation of zipper tables usually relies on daily task scheduling and has daily delays, which means that it inherently has at least one day of data delay. In many application scenarios, this delay is unacceptable because it is impossible to obtain the latest data status and make instant decisions in real time.

[0006] Secondly, as the amount of data in a single table increases, the time required to generate a zipper table will also increase significantly. This not only affects the efficiency of data processing, but also increases the consumption of system resources. When it is necessary to maintain zipper tables for multiple data tables, the development cost of the task, resource consumption, and overall data latency will increase exponentially, which is a major challenge for systems with limited resources. Summary of the invention

[0007] In order to solve the technical problems existing in the above-mentioned background technology, the present invention provides a database real-time zipper generation method and system, which synchronizes the business database table through the full-increment whole database, then performs data diversion according to metadata information, and then converts the data stream into a zipper data stream, which is pushed to the downstream analysis library for execution. It can reduce data delay to seconds, avoid additional CPU and memory overhead for the business library, reduce storage occupancy, and has the characteristics of low cost, real-time, and high-efficiency generation of zipper tables.

[0008] In order to achieve the above object, the present invention adopts the following technical solution:

[0009] A first aspect of the present invention provides a database real-time zipper generation method, comprising:

[0010] Query the full historical data from the business database table in parallel, obtain the incremental log data, and connect the full historical data with the incremental log data;

[0011] Convert the full historical data and incremental log data into data change events with the same format, and split them according to the metadata information of the source table. Convert the data change events into data streams that can be processed by the stream computing engine to obtain data change event data streams.

[0012] Add zipper information to the data change event data stream and convert it into the data change event data stream with zipper information;

[0013] Convert the data change event data stream with zipper information into data operation statements of the downstream analysis library, and push them to the downstream analysis library for execution.

[0014] Furthermore, the incremental log data is obtained by performing single-thread log parsing from the database log.

[0015] Furthermore, the step of querying the full amount of historical data from the business database table in parallel includes: using the incremental snapshot algorithm capability provided by FlinkCDC, evenly dividing the data of the business database table into multiple snapshot shards, and then the snapshot shards are read concurrently by multiple parallel Reader instances.

[0016] Furthermore, the data change event includes metadata information of a source table where the data change occurs, a data state before the change, and a data state after the change.

[0017] Furthermore, the start date and end date of the zipper information are generated according to an update timestamp in the data.

[0018] Furthermore, the conversion of the data change event includes:

[0019] The data change event data of historical data will be converted into a data change event data stream with new semantics and zipper information;

[0020] The data change event data stream with the added operation is converted into the data change event data stream with the added semantics and zipper information;

[0021] The data change event data stream with modification operation is converted into the data change event data stream with modification and addition semantics and zipper information;

[0022] The data change event data stream with deletion operations is converted into a data change event data stream with modification semantics and zipper information.

[0023] Furthermore, the Flink Sink operator converts the data change event data stream with zipper information into data operation statements of the downstream analysis library and pushes it to the downstream analysis library for execution.

[0024] A second aspect of the present invention provides a database real-time zipper generation system, comprising:

[0025] The collection module is configured to: query the full amount of historical data from the business database table in parallel, obtain the incremental log data, and connect the full amount of historical data with the incremental log data;

[0026] The event conversion module is configured to: uniformly convert the full historical data and incremental log data into data change events with consistent formats, and perform diversion according to the metadata information of the source table, convert the data change events into data streams that can be processed by the stream computing engine, and obtain data change event data streams;

[0027] A zipper generation module is configured to: add zipper information to the data change event data stream, and convert it into a data change event data stream with the zipper information;

[0028] The output module is configured to convert the data change event data stream with zipper information into data operation statements of the downstream analysis library so as to push them to the downstream analysis library for execution.

[0029] A third aspect of the present invention provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps in the database real-time zipper generation method as described above.

[0030] The fourth aspect of the present invention provides a computer device, including a computer-readable storage medium, a processor, and a computer program stored on the computer-readable storage medium and executable on the processor, wherein when the processor executes the program, the steps in the database real-time zipper generation method as described above are implemented.

[0031] Compared with the prior art, the present invention has the following beneficial effects:

[0032] In terms of resource utilization, the present invention only creates one data source link for the source business library to perform log parsing, thereby avoiding additional CPU and memory overhead for the business library.

[0033] The present invention stores data changes by adding zipper information, thereby greatly reducing storage occupancy.

[0034] In terms of data delay, the present invention implements real-time log parsing and processes the logs and writes them into an analysis library through FlinkCDC+Flink, reducing the data delay to seconds. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] The accompanying drawings in the specification, which constitute a part of the present invention, are used to provide a further understanding of the present invention. The exemplary embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute improper limitations on the present invention.

[0036] Figure 1 is a flow chart of a method for generating a real-time zipper from a database according to a first embodiment of the present invention;

[0037] Figure 2 This is a structural diagram of a database real-time zipper generation system according to the second embodiment of the present invention;

[0038] Figure 3 It is a structural diagram of a computer device according to a fourth embodiment of the present invention. DETAILED DESCRIPTION

[0039] To make the purpose, technical solution and advantages of the embodiments of the present invention more clear, the technical solution in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention.

[0040] It should be noted that the following detailed descriptions are all illustrative and intended to provide further explanation of the present invention. Unless otherwise specified, all technical and scientific terms used herein have the same meanings as those commonly understood by those skilled in the art to which the present invention belongs.

[0041] Terminology explanation:

[0042] CDC: The abbreviation of Change Data Capture technology, which can synchronize the incremental change records of the source database (Source) to one or more data destinations (Sink).

[0043] FlinkSink: One of the three major logical structures of Flink, its function is to output the data processed by Flink to the external system.

[0044] Embodiment 1

[0045] This embodiment provides a method for generating a real-time zipper in a database.

[0046] This embodiment provides a database real-time zipper generation method, such as Figure 1 As shown, the following steps are included:

[0047] Step 1: Collection. Use the incremental snapshot algorithm provided by Flink CDC to collect full incremental data from business database tables (such as MySQL data tables) and database logs (such as MySQL Binlog logs). That is, query the full historical data from the business database table in parallel, and perform single-threaded log parsing to collect incremental log data from the database log, and correctly connect the full historical data of the database with the incremental log data.

[0048] Specifically, the incremental snapshot algorithm provided by Flink CDC is used to divide the data of the business database table into multiple snapshot shards as evenly as possible. For example, MySQL adopts a sampling strategy to evaluate the size of each row of data and evenly generates shards. These shards are then read concurrently by multiple parallel Reader instances of Flink to achieve the function of correctly completing the consistent snapshot without locking the table and multi-concurrency, correctly connecting the full historical data of the database with the incremental log data, and completing the full incremental collection of the business database table and database log.

[0049] Step 2: Event conversion. The collected full historical data and incremental log data are uniformly converted into data change event data streams. Specifically: The collected full historical data and incremental log data are uniformly converted into data change events with consistent formats. Data change events include four categories: historical data import events, data addition events, data deletion events, and data update events (data update events include pre-update events and post-update events). Events include metadata information of the source table where the data change occurred, the time and transaction information of the change, the data status before the change, and the data status after the change. Then, the data is diverted according to the table name in the metadata information of the source table. During the diversion process, each data change event will be divided into the data stream of each table according to the table name, and the data change event is converted into a data stream that can be processed by the stream computing engine, that is, the data change event data stream.

[0050] Specifically, the collected full historical data and incremental log data are uniformly converted into data change events with consistent formats. The events contain metadata information of the source table where the data change occurs, the data status before the change, and the data status after the change. Data is split according to the db and table attribute values ​​of the source table metadata information payload.source, and then converted into a data change event data stream that can be processed by the stream computing engine through the before and after of the data change event payload.

[0051] Step 3: Zipper generation. Add zipper information to the data change event data stream (zipper information is the start date and end date of the data added to the original data. The default end date of the current valid data is 2999-01-01), and convert it into a data change event data stream with zipper information. The start date and end date of the zipper information are generated according to the update timestamp in the original business data. The zipper granularity can be at the day level or even the second level.

[0052] Furthermore, the data flow conversion relationship from various business data operations to the final data change event with a zipper is as follows:

[0053] The data change event data stream of historical data will be converted into a data change event data stream with newly added semantics and zipper information. Specifically: the historical data is converted into a data change event with the op:r type; the data change event is shunted according to the metadata information and converted into a data change event data stream with RowKind:+I semantics; through the data stream conversion relationship, the data change event data stream with RowKind:+I semantics is mapped into a data change event data stream with RowKind:+I semantics and zipper information, where the data effective start date in the zipper information of RowKind:+I is generated by taking the update timestamp in the original business data, and the end date is 2999-01-01;

[0054] The data change event data stream with the newly added operation will be converted into a data change event data stream with the newly added semantics and zipper information. Specifically: the newly added data is converted into a data change event with the op:c type; the data change event is shunted according to the metadata information and converted into a data change event data stream with the RowKind:+I semantics; through the data stream conversion relationship, the data change event data stream with the RowKind:+I semantics is mapped into a data change event data stream with the RowKind:+I semantics and zipper information, wherein the data effective start date in the zipper information of RowKind:+I is generated by taking the update timestamp in the original business data, and the end date is 2999-01-01;

[0055] The data change event data stream with modification operations will be converted into a data change event data stream with modification and addition semantics and zipper information. Specifically: the modified data is converted into a data change event with the op:u type; the data change event is split according to the metadata information and converted into a data change event data stream with RowKind:-U and RowKind:+U semantics; through the data stream conversion relationship, the data change event data stream with RowKind:-U and RowKind:+U semantics is mapped to a data change event with RowKind:-U ,RowKind:+U and RowKind:+I semantics and data change event data stream with zipper information, where the effective start date of the zipper information of RowKind:-U does not need to be set, and the end date is generated by the update timestamp before deletion, the effective start date of the zipper information of RowKind:+U is generated by the update timestamp before update, and the end date is generated by the update timestamp after update, the effective start date of the zipper information of RowKind:+I is generated by the update timestamp after update, and the end date is 2999-01-01;

[0056] The data change event data stream with deletion operation will be converted into a data change event data stream with modification semantics and zipper information, specifically: the deleted data is converted into a data change event with op:d type; the data change event is diverted according to the metadata information and converted into a data change event data stream with RowKind:-D semantics; through the data stream conversion relationship, the data change event data stream with RowKind:-D semantics is mapped to a data change event data stream with RowKind:-U and RowKind:+U semantics and zipper information, wherein the data effective start date of the zipper information of RowKind:-U does not need to be set, and the end date is generated by the update timestamp before deletion, and the data effective start date of the zipper information of RowKind:+U is generated by the update timestamp before deletion, and the end date is generated by the current timestamp.

[0057] Step 4: Output. By using the Flink Sink operator, the data change event data stream with zipper information is converted into data operation statements corresponding to the downstream analysis library and output to the downstream analysis library for execution, such as the Doris analysis library. In this case, the existing data is updated through upsert semantics.

[0058] This embodiment provides a database real-time zipper generation method, which synchronizes the business database table through Flink CDC full-increment whole database, then performs data diversion according to metadata information, and then converts the data stream into a zipper data stream, which is pushed to the downstream analysis library for execution. It can reduce data delay to seconds, avoid additional CPU and memory overhead for the business library, reduce storage occupancy, and has the characteristics of low-cost, real-time, and high-efficiency zipper table generation.

[0059] In terms of resource utilization, when using FlinkCDC for full incremental data, the present invention only creates one data source link for the source business library for log parsing, and does not establish a data source connection for each table, thereby avoiding additional CPU and memory overhead for the business library.

[0060] In terms of data changes, the present invention stores data changes by adding zipper information, which greatly reduces storage occupancy.

[0061] In terms of data delay, the present invention implements real-time log parsing and processes the logs and writes them into the analysis library through FlinkCDC+Flink Sink, reducing the data delay to seconds.

[0062] Embodiment 2

[0063] This embodiment provides a database real-time zipper generation system.

[0064] This embodiment provides a database real-time zipper generation system, such as Figure 2 As shown, it specifically includes:

[0065] The collection module is configured to: query the full amount of historical data from the business database table in parallel, obtain the incremental log data, and connect the full amount of historical data with the incremental log data;

[0066] The event conversion module is configured to: uniformly convert the full historical data and incremental log data into data change events with consistent formats, and perform diversion according to the metadata information of the source table, convert the data change events into data streams that can be processed by the stream computing engine, and obtain data change event data streams;

[0067] A zipper generation module is configured to: add zipper information to the data change event data stream, and convert it into a data change event data stream with the zipper information;

[0068] The output module is configured to convert the data change event data stream with zipper information into data operation statements of the downstream analysis library so as to push them to the downstream analysis library for execution.

[0069] It should be noted here that each module in this embodiment corresponds to each step in Example 1 one by one, and the specific implementation process is the same, which will not be repeated here.

[0070] Embodiment 3

[0071] This embodiment provides a computer-readable storage medium on which a computer program is stored. When the program is executed by a processor, the steps in the database real-time zipper generation method described in the first embodiment are implemented.

[0072] Embodiment 4

[0073] This embodiment provides a computer device, such as Figure 3 As shown, it includes a computer-readable storage medium 1003, a processor 1001, a communication interface 1002, and a computer program stored on the computer-readable storage medium 1003 and executable on the processor 1001, wherein the processor 1001, the communication interface 1002, and the computer-readable storage medium 1003 can be connected via a bus or other means. The communication interface 1002 is used to receive and send data, and when the processor 1001 executes the program, the steps in the database real-time zipper generation method described in the first embodiment are implemented.

[0074] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0075] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0076] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0077] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, the present invention may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A database real-time zipper generation method, characterized in that: include: Query the full historical data from the business database table in parallel, obtain the incremental log data, and connect the full historical data with the incremental log data; Convert the full historical data and incremental log data into data change events with the same format, and split them according to the metadata information of the source table. Convert the data change events into data streams that can be processed by the stream computing engine to obtain data change event data streams. Add zipper information to the data change event data stream and convert it into the data change event data stream with zipper information; Convert the data change event data stream with zipper information into data operation statements of the downstream analysis library, and push them to the downstream analysis library for execution.

2. A database real-time zipper generation method as claimed in claim 1, characterized in that: The incremental log data is obtained by performing single-thread log parsing from the database log.

3. A database real-time zipper generation method as claimed in claim 1, characterized in that: The step of querying the full amount of historical data in parallel from the business database table includes: using the incremental snapshot algorithm capability provided by Flink CDC, evenly dividing the data of the business database table into multiple snapshot shards, and then the snapshot shards are concurrently read by multiple parallel Reader instances.

4. A database real-time zipper generation method as claimed in claim 1, characterized in that: The data change event includes metadata information of the source table where the data change occurs, the data state before the change, and the data state after the change.

5. A database real-time zipper generation method as claimed in claim 1, characterized in that: The start date and end date of the zipper information are generated according to the update timestamp in the data.

6. A database real-time zipper generation method as claimed in claim 1, characterized in that: The conversion of the data change event includes: The data change event data of historical data will be converted into a data change event data stream with new semantics and zipper information; The data change event data stream with the added operation is converted into the data change event data stream with the added semantics and zipper information; The data change event data stream with modification operation is converted into the data change event data stream with modification and addition semantics and zipper information; The data change event data stream with deletion operations is converted into a data change event data stream with modification semantics and zipper information.

7. A database real-time zipper generation method as claimed in claim 1, characterized in that: The Flink Sink operator is used to convert the data change event data stream with zipper information into data operation statements for the downstream analysis library and push them to the downstream analysis library for execution.

8. A database real-time zipper generation system, characterized in that: include: The collection module is configured to: query the full amount of historical data from the business database table in parallel, obtain the incremental log data, and connect the full amount of historical data with the incremental log data; The event conversion module is configured to: uniformly convert the full historical data and incremental log data into data change events with consistent formats, and perform diversion according to the metadata information of the source table, convert the data change events into data streams that can be processed by the stream computing engine, and obtain data change event data streams; A zipper generation module is configured to: add zipper information to the data change event data stream, and convert it into a data change event data stream with the zipper information; The output module is configured to convert the data change event data stream with zipper information into data operation statements of the downstream analysis library so as to push them to the downstream analysis library for execution.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the steps in a database real-time zipper generation method as described in any one of claims 1 to 7 are implemented.

10. A computer device comprising a computer-readable storage medium, a processor, and a computer program stored in the computer-readable storage medium and executable on the processor, characterized in that: When the processor executes the program, the steps in the real-time zipper generation method for a database as described in any one of claims 1 to 7 are implemented.

Images