Whole library synchronization method and device based on Flinksql and storage medium

Abstract

The invention discloses a whole library synchronization method and device based on Flinksql and a storage medium. The method comprises the steps that a binlog file of a source data end is obtained; partitioning output data of the source data end according to data table information recorded in the binlog file, and determining at least one side output stream of the output data according to a partitioning result; and sending the output data to a target data end based on at least one side output stream. According to the method, the data connection is established through the whole database synchronization source end, the binlog is read, so that the database operation instruction is read into the stream according to the name of the database table, the stream is distributed, converted and output to the sink end, the method is suitable for most of connectors based on flink source, and in the database synchronization process, the data synchronization efficiency is greatly improved. For conversion and cleaning of data synchronization, a single-side output stream mode is used as a public process for data input, task nodes accessed by a database are reduced, and the technical effect of saving server system resources is achieved.

Description

Technical Field

[0001] The present application relates to the technical field of data synchronization, and in particular to a whole-database synchronization method, device, and storage medium based on Flinksql. Background Art

[0002] Existing database synchronization applications generally use the official pipeline connector of apache flink to connect to the database, and write the execution plan of the task based on YAML, so as to assist users in automatically generating customized Flink operators and submitting Flink jobs for synchronization. In data synchronization based on the flink cdc pipeline connector, there are not many official sources and sinks that can be used. The latest version of the existing flink cdc is v3.3, and the only source is the mysql database (or a database using mysql technology such as MariaDB), and the sink is only Apache Doris, Kafka, Paimon, StarRocks, etc., which makes the range of database objects that can be synchronized through this data synchronization technology quite limited.

[0003] If you want to expand the scope of database objects that can be synchronized, you can use the data synchronization technology based on the flink source connector. Although this synchronization technology can expand the scope of synchronized database objects, when it is applied, each table in the database needs to be compiled into a separate synchronization task to achieve data synchronization. During this data synchronization process, the task compilation process of defining the flink DDL and INSERT statements for each table in the database is extremely inconvenient. In addition, each data table needs to define an independent source end, establish a connection with the source database separately, and repeatedly read the binlog. This reading process puts a lot of pressure on the source database and wastes the system resources of the server.

[0004] The above contents are only used to assist in understanding the technical solution of the present application and do not constitute an admission that the above contents are prior art. Summary of the invention

[0005] The main purpose of this application is to provide a whole-database synchronization method, device and storage medium based on Flinksql, aiming to solve the technical problem of waste of system resources of the server during existing database synchronization.

[0006] To achieve the above purpose, this application proposes a whole database synchronization method based on Flinksql, and the method includes: Get the binlog log file of the source data end; Partition the output data of the source data end according to the data table information recorded in the binlog log file, and determine at least one side output stream of the output data according to the partition result; Based on at least one side output stream, the output data is sent to a target data end.

[0007] In one embodiment, the step of sending the output data to the target data end based on at least one side output stream comprises: In the side output flow, converting the output data into data rows of different operation types; A temporary view of the data row is created, and the data row is captured from the side output stream through the temporary view and written to the target data end.

[0008] In one embodiment, the step of creating a temporary view of the data row, and fetching the data row from the side output stream through the temporary view and writing it to the target data end includes: Determine the operation type to which the temporary view belongs, and call the real-time data integration framework to fetch data rows of the operation type; The captured data row is written to the target data end.

[0009] In one embodiment, the step of determining the operation type to which the temporary view belongs and calling the real-time data integration framework to fetch data rows of the operation type includes: Determine metadata corresponding to the data row, and create an SQL statement based on the metadata; The SQL statement is executed in the real-time data integration framework to fetch data rows of the operation type.

[0010] In one embodiment, the step of determining metadata corresponding to the data row and creating an SQL statement based on the metadata includes: Determine the data table where the metadata is located; The SQL statement is created according to the business attributes of the data table.

[0011] In one embodiment, the step of partitioning the output data of the source data end according to the data table information recorded in the binlog log file, and determining at least one side output stream of the output data according to the partitioning result includes: Creating a main data output stream of the data table information, and partitioning the output data in the main data output stream; According to the partitioning result, the main data output stream is split into at least one side output stream.

[0012] In one embodiment, before the step of obtaining the binlog log file in the source data end, the step further includes: Capturing data change events of the source data end through a source end connector, and converting the data change events into binlog logs; Write the binlog log into the binlog log file of the source data end.

[0013] In one embodiment, before the step of capturing the data change event of the source data end through the source end connector and converting the data change event into a binlog log, the step further includes: Setting event capture rules according to the configuration parameters of the source data terminal; The event capture rule is deployed to the source-end connector to capture data change events of the source data end.

[0014] In addition, to achieve the above-mentioned purpose, the present application also proposes a whole-database synchronization device based on Flinksql, the device comprising: a memory, a processor, and a computer program stored in the memory and executable on the processor, the computer program being configured to implement the steps of the whole-database synchronization method based on Flinksql as described above.

[0015] In addition, to achieve the above-mentioned purpose, the present application also proposes a storage medium, which is a computer-readable storage medium, and a computer program is stored on the storage medium. When the computer program is executed by the processor, the steps of the whole database synchronization method based on Flinksql as described above are implemented.

[0016] One or more technical solutions proposed in this application have at least the following technical effects: The technical solution of the present application is to obtain the binlog log file of the source data end; partition the output data of the source data end according to the data table information recorded in the binlog log file, and determine at least one side output stream of the output data through the partitioning result; based on at least one side output stream, send the output data to the target data end. This technical content establishes a data connection by synchronizing many tables on the source end of the entire library, and then reads the binlog log to read the database operation instructions into streams according to the library table names and distributes them to the sink end. It can adapt to most of the connectors based on flinksource, so that in the process of database synchronization, the conversion and cleaning of data synchronization are output as a public process in the form of a single output stream, which reduces the task nodes of database access, thereby achieving the technical effect of saving server system resources. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0018] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0019] Figure 1 A flowchart of the first embodiment of the whole database synchronization method based on Flinksql of this application is provided; Figure 2 This is a schematic diagram of the data flow for synchronization of the entire database; Figure 3 This is a schematic diagram of the device structure of the hardware operating environment involved in the whole database synchronization method based on Flinksql in the embodiment of the present application.

[0020] The purpose, features and advantages of this application will be further described in conjunction with the embodiments and with reference to the accompanying drawings. DETAILED DESCRIPTION

[0021] It should be understood that the specific embodiments described herein are only used to explain the technical solutions of the present application and are not used to limit the present application.

[0022] In order to better understand the technical solution of the present application, a detailed description will be given below in conjunction with the accompanying drawings and specific implementation methods.

[0023] The main solution of the embodiment of the present application is: obtain the binlog log file of the source data end; partition the output data of the source data end according to the data table information recorded in the binlog log file, and determine at least one side output stream of the output data through the partitioning result; based on at least one side output stream, send the output data to the target data end.

[0024] Due to the existing data synchronization technology based on the flink source connector, each table in the database needs to be compiled into a separate synchronization task to achieve data synchronization. In this data synchronization task, the task compilation process of defining the flink DDL and INSERT statements of each table in the database is extremely inconvenient. In addition, each data table needs to define an independent source end, establish a connection with the source database separately, and repeatedly read the binlog. This reading process puts a lot of pressure on the source database and wastes the system resources of the server.

[0025] The present application provides a solution, which establishes a data connection by synchronizing multiple tables on the source side of the entire database, and then reads the binlog log to read the database operation instructions into streams and distribute them to the sink side according to the library table names. It can adapt to most of the connectors based on flink source, so that in the process of database synchronization, the conversion and cleaning of data synchronization are output in the form of a single output stream as a public process, reducing the task nodes for database access, thereby achieving the technical effect of saving server system resources.

[0026] Based on this, the embodiment of the present application provides a whole database synchronization method based on Flinksql. Figure 1 , Figure 1 This is a flow chart of the first embodiment of the whole database synchronization method based on Flinksql of this application. In this embodiment, the whole database synchronization method based on Flinksql includes steps S10 to S30: Step S10, obtaining the binlog log file of the source data end; Step S20, partitioning the output data of the source data end according to the data table information recorded in the binlog log file, and determining at least one side output stream of the output data according to the partition result; Step S30: sending the output data to a target data terminal based on at least one side output stream.

[0027] In this embodiment, based on the data synchronization requirements of the source data end, the binlog log file of the source data end is obtained, the content of the binlog log file is read to determine the data table information of the source data end, the data table information includes the table name and the primary key, and the output data of the source data end is partitioned according to the data table information, that is, the data synchronization job based on the source data end has been started when the binlog log file is read, and the performance of the data synchronization job is to create a main data output stream based on the data table information in the source data end, so that the data table information is outputted and processed via the main data output stream, that is, the output data of the source data end is partitioned according to the data table information recorded in the binlog log file, and the step of determining at least one side output stream of the output data through the partition result includes: Creating a main data output stream of the data table information, and partitioning the output data in the main data output stream; According to the partitioning result, the main data output stream is split into at least one side output stream.

[0028] According to the database objects that currently need to be synchronized, that is, the source data end and the target data end, in order to achieve data synchronization of the source data end, the source data end data transmission connection is created to synchronize the data items in the source data end to the receiving end database through the database transmission connection, wherein the data synchronization based on the source data end is essentially a process of transmitting the data table information of the source data end to the target data end, and for this purpose, a main data output stream based on the data table information of the source data end is created to transmit the data table information. In actual applications, when creating the main data transmission connection, it is usually achieved by setting the connection parameters of the source data end, that is, the data transmission connection is generated by the connection parameters of the source data end, so as to access the source data end to output the data table information. When configuring the connection parameters, the specific configuration process is different from the database system to which the source data end belongs, but the basic application steps may include the following: 1) Confirm the types of the source database and the target database (such as Oracle, MySQL, SQL Server, etc.); collect connection information, including the IP address, port number, service name / SID of the source database and the target database. The user name and password of the target database.

[0029] 2) Create a database link and connect to the source database using SQL*Plus, PL / SQL Developer, or other Oracle client tools; 3) Execute the SQL statement to create a database link. For example, you can use the Create Database Link statement to create a link. The SQL code can be defined as: Create Database link dbl_totarget connect to target_useridentified by target_password using 'tns_target', where dbl_totarget is the name of the database link, target_user and target_password are the user name and password of the target data end, and tns_target is the configured tns entry name.

[0030] 4) Use the created database link to query the table in the target data end to verify whether the link is correct; and if there is a connection problem, check the TNS configuration, network connection, user name and password, and other SQL statements to adjust the database link.

[0031] 5) Users who create database links usually need to have corresponding permissions; ensure the secure storage and transmission of user names and passwords; consider using encrypted connections and authentication mechanisms; performance, monitor the performance of database links and optimize as needed; maintenance, regularly maintain database links to ensure their availability and accuracy.

[0032] The steps and syntax for creating the database connection shown above may differ based on different types of databases. The specific steps are related to the database type and will not be repeated here.

[0033] According to the data transmission link between the source data end and the target data end created above, the binlog log based on the source data end is read to generate the main data output stream of the source data end. It should be clarified that the main data output stream of the source data end is generated based on the data table information about the source data end recorded in the binlog log, so that the data table information stored in the source data end is output to the target data end through the main data output stream via the data transmission connection.

[0034] Among them, the log is a log file used to record database operation information, and different databases have different implementation methods and names. In this embodiment, the binlog log file, that is, the file generated by the binlog information of the source data end, is defined as Binary Log (the Binary Log is the definition name of the MySQL operation information log file), which is a very important log file in the MySQL database, also known as the change log (Update Log), that is, the binlog log file records the relevant information based on the data item in the source data end, that is, before the above step of obtaining the binlog log file in the source data end, it also includes: Capturing data change events of the source data end through a source end connector, and converting the data change events into binlog logs; Write the binlog log into the binlog log file of the source data end.

[0035] In this embodiment, the Binlog log file records all database execution statements based on data items in the source data end, and the database execution statements include data modification statements and database structure change statements, etc., but do not include statements that do not modify any data. This content is characterized as the binlog log of the source data end in practical applications. Specifically, the content recorded in the binlog log file mainly includes: data definition language (DDL) operations and database operation language (DML) operations. The data definition language includes Create, Alter, Drop and other statements, which are used to define or modify the data structure in the database. The data operation language operations include Insert, Update, Delete and other statements, which are used to add, delete and modify data in the database.

[0036] The binlog records store data operation information of the source data end in binary format and save it to disk in record form. Each record is saved in the form of an event to describe the changes in the data in the source data. In addition, the recorded event also includes the time consumed by the statement execution, which can provide a benchmark for subsequent data recovery and replication operations.

[0037] In practical applications, the data change events of the source data end are captured through the source connector (i.e., flink cdc connector, which is a set of source connectors of Apache Flink, used to capture Change Data Capture in the database, i.e., CDC, and the source connector works based on the database log and Flink's stream processing engine), and the captured change events are used as binlog records to form the binlog log. First, a suitable source connector is selected according to the data type and related characteristics of the source data end, and related parameters based on the source connector are configured, such as database connection information, capture scope (such as table name, field, etc.), and capture mode (such as log-based, trigger-based, or SQL script-based, etc.). A stable connection between the source database and the source connector is configured, so that data changes can be accessed and monitored in real time or on a scheduled basis. Afterwards, the source connector captures the data change events of the source data end according to the configured capture mode, and the data change events may include operations such as insert, update, and delete.

[0038] Furthermore, based on different capture modes of the binlog log, the source-end connector can also parse out data change events based on the transaction log or change log read from the source data end.

[0039] Alternatively, the source-end connector may also capture the data change event by relying on a trigger set by the source data end; Alternatively, when a pre-set SQL script is used as the capture mode, the source-end connector may periodically execute the SQL script to query the data change event and obtain the data change event.

[0040] According to the above, the source connector captures the data change event based on the source data end, converts the data change event into structured event data, the structured event data is represented by the binlog log, and the structured event data contains key information such as event type, table name, data values ​​before and after the operation, timestamp, etc. The structured data is encapsulated into data in a specific format (such as JSON) for storage and transmission.

[0041] In addition, before the source connector transmits the encapsulated binlog log to the corresponding storage area through a message queue or a data stream platform, a binlog log file is pre-set in the storage area. Therefore, when the encapsulated binlog log is transmitted to the storage area, it can also be defined as writing the binlog log to the binlog log file. In the transmission process, the event data needs to be serialized, compressed, etc. to improve the transmission efficiency and reliability.

[0042] Further, when the data change event of the source data end is captured by the source end connector, the data change event of the source data end can also be filtered by setting an event capture rule, that is, before the step of capturing the data change event of the source data end by the source end connector and converting the data change event into a binlog log, it also includes: Setting event capture rules according to the configuration parameters of the source data terminal; The event capture rule is deployed to the source-end connector to capture data change events of the source data end.

[0043] In this embodiment, when the source connector captures the data change event of the source data end, the data change event of the source data end is filtered by setting the event capture rule, so as to locate the valid data change event of the source data end to avoid duplicate or invalid data information. The event capture rule includes capture scope, capture mode, event type, data consistency, real-time requirements, performance and resource consumption, error handling and logging, and security considerations. The specific content of the event capture rule can be as follows: The capture scope refers to the specific capture data range specified by the source connector for the source data end to capture data change events, including change events of the database, data table or data field, etc. This setting is related to the security settings of the database, thereby ensuring that only relevant and important data changes are captured, avoiding the capture of unnecessary data change events.

[0044] The capture mode includes log capture mode, trigger capture mode and SQL script capture mode, and the corresponding capture mode can be selected for execution according to the characteristics of the source data end and business requirements. Specifically, the log capture mode has higher real-time performance and integrity, and the trigger capture mode will affect the performance of the source data end, so it can be used to capture large batches of data change events.

[0045] The event type refers to the data type of the data change event of the captured object set by the source connector, and the data type includes operations such as insert, update, and delete.

[0046] The data consistency means that when capturing the data change event, the source connector ensures that the captured data change event can accurately reflect the state of the source data end, and can correctly handle the order and dependencies of the data change event under concurrent operations.

[0047] The real-time requirement means that the source connector provides real-time or quasi-real-time data change capture capabilities according to business needs, so as to ensure that the data change events can be synchronized to the target system or data storage in a timely manner.

[0048] The performance and resource consumption refer to the performance and resource consumption that the source connector needs to consider when capturing the data change event. The setting benchmark specifically includes reducing the impact on the performance of the source data end, optimizing data transmission, processing efficiency, and maintaining the stability and reliability of capture in high-concurrency scenarios.

[0049] The error handling and logging refers to the perfect error handling and logging mechanism of the source connector. Specifically, it can be defined as that when an error or abnormality occurs during the capture of the data change event, the source connector promptly records the relevant information and performs corresponding processing solutions for subsequent troubleshooting and fault recovery.

[0050] The security considerations refer to the security issues that the source connector considers when capturing the data change events, including ensuring encryption and security during data transmission, preventing unauthorized access and operations, etc. This setting can comply with relevant data protection regulations and standards.

[0051] According to the above, based on the capture of the data change event of the source data end by the source end connector, after converting the captured data change event into a binlog log, it is written into the binlog log file of the source data end.

[0052] The above clearly indicates the relevant data content stored in the binlog file. Therefore, the data table information in the source data end can be determined by reading the binlog file. Since the source data end is represented by multiple data tables, and multiple data items expressed as different information are stored in units of data tables, in order to avoid synchronization anomalies caused by errors in the data output process, in this embodiment, the generated main data output stream is split into one or more output streams in units of data tables for output respectively, that is, the data in the output data stream is partitioned based on the data table information.

[0053] Wherein, when the output data in the output data stream is partitioned based on the data table information, it is actually a split of the main data output stream created based on the data table information of the source data end. The partitioning basis is based on the specific data structure represented by the data table information, and the data table information is represented by the table name and the primary key (wherein, the table name is the identifier of the table in the database, which is used to uniquely identify a table in the database, usually composed of letters, numbers and underscores, and usually follows a certain naming convention; the primary key is a combination of one or more columns in the data table, whose value is unique in the table and is not allowed to be empty (NULL), that is, the primary key is used to uniquely identify each row record in the data table). Therefore, the output data of the source data end is partitioned according to the data table information, so that the main data output stream is split into one or more output streams based on the partitioning result, and the data table information of each partition result is output through the split one or more output streams.

[0054] It can be understood that the side output stream is a sub-data output stream after the main output data stream of the source data end is partitioned, and there are one or more of them. Specifically, the number of the side output streams is related to the number of data partitions of the output data; or, according to the data output amount of the source data end, the main data output stream is split into one or more output streams, and after the output data is partitioned, the output data in the partition is sent to the target data end through a certain output stream according to the partition result.

[0055] As shown above, the main output data stream is the only data output thread of the source data end, and it can also be determined as a common process for data synchronization based on the source data end. Even if the main output data stream is split into one or more output streams, the split side output stream still uses the data output thread of the main output data stream. In addition, the process of dividing the main data output stream into multiple output streams based on the data table information in the source data end can be characterized based on the form of calling related methods, that is, calling the data method of DataStreamSource to split the main data output stream based on the table name and primary key, thereby splitting the main data output stream into multiple output streams identified by the table name and primary key. In specific operations, partitions can be performed in sequence according to the actual data volume of the data tables in the source data end, which can be defined as preferentially partitioning and dividing the data tables with more or fewer data items into output streams, and outputting the data items in the data table through the side output stream, wherein the actual partitioning rule of the side output stream can be set based on the data output rule of the pre-set output stream.

[0056] As described above, in the operation of splitting the main data output stream of the source data end into one or more output streams for data output through table name partitioning, it can be represented by calling the corresponding data method in DataStreamSource. In practical applications, the DataStreamSource is part of the DataStream API in Apache Flink, which is used to represent the source of the data stream, that is, represented as the source data end. As for the specific data methods possessed by the DataStreamSource, it actually refers to the method class inherited from SingleOutputStreamOperator, which serves as the starting point for representing the data stream.

[0057] In Flink applications, the DataStreamSource can be created by calling the addSource() method of the execution environment StreamExecutionEnvironment, or methods related to addSource(), such as fromCollection() and readTextFile(). In this embodiment, the methods of the DataStreamSource used include partitionCustom method, process method and flatMap method.

[0058] The partitionCustom method of DataStreamSource is called to partition the data items in the data output stream by table name; then, the process method of DataStreamSource is called to split the main data output stream into one or more output streams by table name according to the partition operation of the table name.

[0059] According to the data stream processing scheme shown above, since the unit of the side output stream is a data table, and the data table has one or more data items indicated by a data table header, in order to avoid confusion of the data items during data synchronization, when the data items are output to the target data end through the side output stream, the data items of the side output stream are inserted into the corresponding data table of the target data end in the form of a view.

[0060] In this embodiment, the flatMap method of DataStream is used to convert the data stream into a tabular form. The DataStream is a core concept in Apache Flink, representing a distributed data set, used to represent an infinite or finite stream of data, and used to process the abstract concept of unbounded stream data. In practical applications, the DataStream represents a series of continuous, infinite data record streams, including event data generated in real time or data received through a data source (such as Kafka, Socket, etc.). The real-time generated event data includes sensor data, log data, etc., and the data received through the data source includes data received through the data source (such as Kafka, Socket, etc.). When the DataStream is applied, a method for creating the DataStream can be used based on the data read from the data source. In this embodiment, the data source based on the creation of the DataStream method is the data information in the source data end. When the data information is output in the form of splitting the main data output stream into one or more output streams, the data source can also be identified as the data item information represented in the split side output stream. In addition, the DataStream has a data conversion element, and different data sources can convert the data source into a data set of corresponding format when called. That is, the step of sending the output data to the target data end based on at least one side output stream includes: In the side output flow, converting the output data into data rows of different operation types; A temporary view of the data row is created, and the data row is captured from the side output stream through the temporary view and written to the target data end.

[0061] Based on the data synchronization operation of the source data end and the form represented by the processed side output stream during synchronization, the flatMap method of the DataStream is called to convert the data information in the side output stream into data rows of different operation types. In practical applications, the data row is defined as the Row type of flink. In Flink, "Row" is a data structure representing a row of data. Each column of the Row type contains data of different operation types (for example, string, integer, floating point number, etc.). As shown above, when the side output stream is split by partition based on the table name, the data information output based on the side output stream is essentially based on the data items stored in the data table. Therefore, when converting the data information into a data row of the Row type, it is essentially a process of splitting the data items in units of rows.

[0062] In database applications, the data items used for storage can be reflected in different operation types, including addition, deletion and modification, which are used to specifically characterize the data operation form reflected by the data items. In actual applications, by calling the flatMap method of the DataStream, the data items in the side output stream are converted into data rows of the row type of Flink according to the operation type.

[0063] According to the definition of the operation types shown above, when distinguishing the operation types of adding, deleting and changing, the data items of different operation types can be distinguished by assigning different data identifiers to the operation types, that is, a unique data identifier of the operation type is pre-set to distinguish the data items of different operation types. The specific data identification form can be determined based on the content defined for different operation types in the database, and the details will not be repeated here.

[0064] Therefore, according to the data identifiers that have been set to characterize different operation types, the flatMap method of DataStream is called to convert the data items into data rows of the row type of Flink according to the operation type. The Row is an important data structure in Apache Flink, mainly used to represent a row of data, and is also one of the basic data types in the Flink Table API and Flink DataSet API. Based on the expression of the Row type, it is at the bottom level in the design of the Flink layered API, which can unify the data structure design so that the operators of the entire link of Source, Transform and Sink use a unified data standard.

[0065] In actual applications, the Row type has four attributes, namely kind, fieldByPosition, fieldByName and positionByName. The specific analysis is as follows: The kind indicates the operation type of the current Row, that is, the data type based on the data item, and is also defined as a RowKind enumeration class value, including Insert (inserted data), Update_Before (data before update), Update_After (data after update) and Delete (deleted data), which respectively represent the three operations of adding, deleting and modifying data in CDC (Change Data Capture).

[0066] The fieldByPosition is a fixed-length one-dimensional array of Objects, representing a row of data. Each Object in the array represents the value of a field. The array is headed by 0, and the data of each field is quickly accessed by the position of the field in the row. After the array is initialized, the value of each field defaults to null; The fieldByName is a Map<String, Object> , key is the field name, value is the value of the field, and the field data is accessed through the field name. Before the data item is serialized, the data length of the fieldByName is variable; during the serialization process, Flink reorders all fields according to the order of the fields, and the missing fields are defaulted to null, thereby determining the length of fieldByName; The positionByName is LinkedHashMap<String, Integer> , key is the field name, value is the position index of the field. In the application, its properties need to be used in conjunction with fieldByPosition. Access positionByName through the field name to obtain the position index of the field data, and access the fieldByPosition property through the index to quickly locate the field data.

[0067] As described above, after the output data in the data stream is converted into a Row type data row of different operation types, the data row can be directly written to the target data end, thereby achieving the purpose of synchronizing the source data end to the target data end. To this end, in order to improve the writing efficiency of the Row type data row, the Row type data row is temporarily stored by creating a temporary view so that multiple data rows are written to the target data end in an orderly manner. The temporary view is a session or transaction with a certain life cycle, which can be used for temporary query, data analysis or temporary data processing. For example, when performing temporary data analysis or query, a temporary view can be used to simplify the query operation; when performing temporary data processing or conversion, a temporary view can be used as an intermediate result; and in a multi-user environment, the use of a temporary view can isolate data operations of different sessions to avoid data conflicts. This limitation is applied to this embodiment, that is, according to the different operation types of the data row, corresponding multiple temporary views are created, so that data rows of different operation types are isolated. Among them, the step of creating a temporary view of the data row and grabbing the data row from the side output stream through the temporary view and writing it to the target data end includes: Determine the operation type to which the temporary view belongs, and call the real-time data integration framework to fetch data rows of the operation type; The captured data row is written to the target data end.

[0068] Based on the above, when the output data in the side output stream is converted into a data row of the Row type, and the corresponding data row is captured by creating a temporary view of different operation types, the corresponding data row can be captured from the data stream and a temporary view can be created by calling the real-time data integration framework, and then the application of the temporary view writes the data row to the target data end. Wherein, in this embodiment, the real-time data integration framework is defined as a data method of DataStream, and the data method of DataStream can convert the data representation form into a Row type data item in a unified format, and convert the Row type data item into a view data item so that it can be quickly inserted into the target data end. In actual applications, the view item data is a virtual table defined by a result set, which does not actually store actual data but is dynamically generated. Therefore, when converting the data in the side output stream into a Row type data row according to the data type, it is essentially a process of generating the data form of different data items into a data set with a unified format, and based on the Row type data item in the unified format, when converted into a view data item, the following steps are specifically included: 1) Locate the data row of the Row type and determine the combined business requirements of the data row; 2) Use SQL language to write one or more queries to extract the data rows according to data requirements; 3) Use Create view statement to convert the data row into the view data item, and use the written SQL query as part of the view data item, and define the name of the view data item for reference; 4) Execute the Select statement to test the newly created view.

[0069] In addition, based on the converted view data item, when inserting the view data item into the target data end, the location information of the source data to which the view data item belongs needs to be taken into consideration, so as to ensure that the location of the view data item inserted into the target data end is consistent with the location of its metadata in the source data end, that is, the step of determining the operation type to which the temporary view belongs and calling the real-time data integration framework to capture the data row of the operation type includes: Determine metadata corresponding to the data row, and create an SQL statement based on the metadata; The SQL statement is executed in the real-time data integration framework to fetch data rows of the operation type.

[0070] According to the view data item currently to be inserted into the target data end, the metadata corresponding to the view data item is determined, and the metadata is the data item recorded in the data table to which the output stream corresponding to the view data item belongs. By constructing the cdc sql of the link cdc sink end and executing the cdc sql of the link cdc sink end, the view data item is inserted into the corresponding position in the target data end, thereby realizing data synchronization between the source data end and the target data end. Among them, the application definition of the CDC (Change Data Capture) is that the insert type SQL statement of the sink end is used to process the data change captured from the source end and marked as the insert operation, that is, the view data item is inserted into the target data end as the data captured by the source end. In actual application, according to the metadata corresponding to the view data item, the cdc sql based on the insert event of the view data item is constructed, that is, the view data item is used as the data table for executing the insert event, and the insert parameters of the insert event (such as the location of the data table, data type and other database attributes) are set according to the belonging information of the source data. Afterwards, when the CDC tool (such as Debezium) captures the insert event, the cdc sql of the insert event is executed, and the relevant change data is sent to the sink end, and the sink end persists the view data item to the target data end. The above is based on the synchronization process of synchronizing the data from the source data end to the target data end. For the specific synchronization process, you can also refer to Figure 2 , Figure 2 This is a schematic diagram of the data flow for synchronization of the entire database.

[0071] In addition, for the implementation steps shown above, the whole database synchronization SQL based on the source data end can also be generated by setting relevant whole database synchronization algorithm rules, and the whole database synchronization SQL is executed to synchronize the data of the source data end to the target data end. Specifically, it can be indicated that when database synchronization is available, the whole database synchronization SQL based on database synchronization is obtained, and the whole database synchronization SQL is a SQL task, which includes parameter information for indicating the database object to be synchronized, and SQL statements for executing data synchronization, etc. The whole database synchronization SQL can be generated by pre-built synchronization algorithm rules; or, according to the currently synchronized data object, the preset synchronization algorithm rules are called to generate the whole database synchronization SQL. In actual applications, when generating the whole database synchronization SQL, the required data objects should meet the requirements of the preset synchronization algorithm rules in order to generate the whole database synchronization SQL.

[0072] According to the above implementation, based on the content of synchronizing the data in the source data end to the target data end, before implementation, it is necessary to generate the whole database synchronization SQL based on the source data end based on the synchronization SQL generation rules. As a data synchronization task, the whole database synchronization SQL can synchronize the data in the source data end to the target data when executed. Therefore, the whole database synchronization SQL includes the parameter information of the database object for data synchronization, that is, the parameter information of the source data end and the target data end, which is used to establish a data transmission connection and call related data methods to achieve data synchronization. Since the source data end and the target data end have multiple data types, the whole database synchronization SQL of the source data end is generated by the synchronization SQL generation rules. The preset whole database synchronization algorithm is a rule framework for generating data synchronization SQL. By filling in the relevant parameter information of the source data end and the target data end to be synchronized in the rule framework, the whole database synchronization SQL based on the source data end is automatically generated.

[0073] In addition, according to the current data synchronization requirements, a synchronization SQL generation rule is constructed to generate synchronization SQL statements in a specified format during database synchronization. Among them, all current official data end parameters are captured. The data end parameters are the official source and sink ends of the flink cdc pipeline connector. The Source end is the sending end, the starting point of the stream, which can be intuitively understood as the data item producer, that is, the source data end, which is responsible for reading the information of the file or network stream. In software development, the Source end is the source of data and is responsible for sending data to the network or transmitting it to other processing units. The Sink end is the receiving end, the end point of the stream, which can be understood as a consumer. Literally translated as "sink", it figuratively represents the end point of the data stream. The Sink end is responsible for receiving and processing the data stream from the Source end. In software development, the Sink end is represented as a function or module for processing received data.

[0074] The source and sink ends are used as database-side parameters, and synchronization SQL generation rules are created. The synchronization SQL generation rules are understood to be SQL statements that can generate data from the source end to the sink end. According to the captured data-end parameter information of the source and sink ends, the whole database synchronization algorithm rules are constructed in combination with the synchronization SQL generation rules. The whole database synchronization algorithm rules can be represented as a data framework, in which SQL statement generation rules and execution rules and execution nodes for realizing data synchronization are deployed.

[0075] In this embodiment, a data connection is established by synchronizing multiple tables on the source side of the entire database, and then the binlog log is read to read the database operation instructions into streams according to the library table names and distributed to the sink side. This can adapt to most of the connectors based on flink source. Therefore, in the process of database synchronization, data synchronization conversion and cleaning are carried out in the form of a single output stream as a public process for data output, which reduces the task nodes for database access, thereby achieving the technical effect of saving server system resources. It should be noted that the above examples are only used to understand the present application and do not constitute a limitation on the whole database synchronization method based on Flinksql of the present application. More forms of simple transformations based on this technical concept are all within the scope of protection of the present application.

[0076] The present application provides a whole-database synchronization device based on Flinksql, and the whole-database synchronization device based on Flinksql includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the whole-database synchronization method based on Flinksql in the above-mentioned embodiment one.

[0077] Reference below Figure 3 , which shows a schematic diagram of the structure of a whole-database synchronization device based on Flinksql suitable for implementing the embodiment of the present application. The whole-database synchronization device based on Flinksql in the embodiment of the present application may include but is not limited to mobile terminals such as mobile phones, laptops, digital broadcast receivers, PDAs (Personal Digital Assistants), PADs (Portable Application Descriptions: tablet computers), etc., and fixed terminals such as digital TVs, desktop computers, etc. Figure 3 The Flinksql-based whole-database synchronization device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present application.

[0078] like Figure 3As shown, the whole database synchronization device based on Flinksql may include a processing device 1001 (such as a central processing unit, a graphics processing unit, etc.), which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM: Read Only Memory) 1002 or a program loaded from a storage device 1003 to a random access memory (RAM: Random Access Memory) 1004. In the random access memory (RAM: Random Access Memory) 1004, various programs and data required for the operation of the whole database synchronization device based on Flinksql are also stored. The processing device 1001, the read-only memory (ROM: Read Only Memory) 1002 and the random access memory (RAM: Random Access Memory) 1004 are connected to each other through a bus 1005. The input / output (I / O) interface 1006 is also connected to the bus. Typically, the following systems can be connected to the input / output (I / O) interface 1006: input devices 1007 including, for example, a touch screen, a touchpad, a keyboard, a mouse, an image sensor, a microphone, an accelerometer, a gyroscope, etc.; output devices 1008 including, for example, a liquid crystal display (LCD: Liquid Crystal Display), a speaker, a vibrator, etc.; storage devices 1003 including, for example, a magnetic tape, a hard disk, etc.; and communication devices 1009. The communication device 1009 can allow the Flinksql-based whole-library synchronization device to communicate wirelessly or wired with other devices to exchange data. Although the figure shows a Flinksql-based whole-library synchronization device with various systems, it should be understood that it is not required to implement or have all the systems shown. More or fewer systems may be implemented or provided alternatively.

[0079] In particular, according to the embodiments disclosed in the present application, the process described above with reference to the flowchart can be implemented as a computer software program. For example, the embodiments disclosed in the present application include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program contains program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network through a communication device, or installed from a storage device 1003, or installed from a read-only memory (ROM: Read Only Memory) 1002. When the computer program is executed by the processing device 1001, the above-mentioned functions defined in the method of the embodiment disclosed in the present application are executed.

[0080] The whole database synchronization device based on Flinksql provided by the present application adopts the whole database synchronization method based on Flinksql in the above embodiment, which can solve the technical problem of waste of system resources of the server during synchronization of the existing database. Compared with the prior art, the beneficial effects of the whole database synchronization device based on Flinksql provided by the present application are the same as the beneficial effects of the whole database synchronization method based on Flinksql provided by the above embodiment, and the other technical features in the whole database synchronization device based on Flinksql are the same as the features disclosed in the method of the previous embodiment, which will not be repeated here.

[0081] It should be understood that the various parts disclosed in this application can be implemented by hardware, software, firmware or a combination thereof. In the description of the above embodiments, specific features, structures, materials or characteristics can be combined in any one or more embodiments or examples in a suitable manner.

[0082] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any person skilled in the art who is familiar with the present technical field can easily think of changes or substitutions within the technical scope disclosed in the present application, which should be included in the protection scope of the present application. Therefore, the protection scope of the present application should be based on the protection scope of the claims.

[0083] The present application provides a storage medium, which is a computer-readable storage medium having computer-readable program instructions (i.e., a computer program) stored thereon, and the computer-readable program instructions are used to execute the whole-database synchronization method based on Flinksql in the above-mentioned embodiment.

[0084] The computer-readable storage medium provided in the present application may be, for example, a USB flash drive, but is not limited to electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, systems or devices, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM: Random Access Memory), a read-only memory (ROM: Read Only Memory), an erasable programmable read-only memory (EPROM: Erasable Programmable Read Only Memory or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM: CD-Read Only Memory), an optical storage device, a magnetic storage device, or any suitable combination of the above. In this embodiment, the computer-readable storage medium may be any tangible medium containing or storing a program, which may be used by or in combination with an instruction execution system, system or device. The program code contained on the computer-readable storage medium may be transmitted using any appropriate medium, including but not limited to: wires, optical cables, RF (Radio Frequency: Radio Frequency), etc., or any suitable combination of the above.

[0085] The above-mentioned computer-readable storage medium may be included in the whole-database synchronization device based on Flinksql; or may exist independently without being assembled into the whole-database synchronization device based on Flinksql.

[0086] The above-mentioned computer-readable storage medium carries one or more programs. When the above-mentioned one or more programs are executed by the whole database synchronization device based on Flinksql, the whole database synchronization device based on Flinksql implements the technical content of the whole database synchronization method embodiment based on Flinksql as shown above.

[0087] Computer program code for performing the operations of the present application may be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, C++, and conventional procedural programming languages ​​such as "C" or similar programming languages. The program code may be executed entirely on the user's computer, partially on the user's computer, as a separate software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the case of a remote computer, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0088] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present application. In this regard, each square box in the flow chart or block diagram can represent a module, a program segment or a part of a code, and the module, the program segment or a part of the code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the square box can also occur in a sequence different from that marked in the accompanying drawings. For example, two square boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each square box in the block diagram and / or flow chart, and the combination of the square boxes in the block diagram and / or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0089] The modules involved in the embodiments described in this application may be implemented by software or hardware, wherein the name of the module does not constitute a limitation on the unit itself in some cases.

[0090] The readable storage medium provided in this application is a computer-readable storage medium, which stores computer-readable program instructions (i.e., computer programs) for executing the above-mentioned whole-database synchronization method based on Flinksql, and can solve the technical problem of waste of system resources of the server during synchronization of the existing database. Compared with the prior art, the beneficial effects of the computer-readable storage medium provided in this application are the same as the beneficial effects of the whole-database synchronization method based on Flinksql provided in the above-mentioned embodiment, and will not be repeated here.

Claims

1. A whole database synchronization method based on Flinksql, characterized in that: The method includes: Get the binlog log file of the source data end; Partition the output data of the source data end according to the data table information recorded in the binlog log file, and determine at least one side output stream of the output data according to the partition result; Based on at least one side output stream, the output data is sent to a target data end.

2. The whole database synchronization method based on Flinksql according to claim 1, characterized in that: The step of sending the output data to the target data end based on at least one side output stream comprises: In the side output flow, converting the output data into data rows of different operation types; A temporary view of the data row is created, and the data row is captured from the side output stream through the temporary view and written to the target data end.

3. The whole database synchronization method based on Flinksql according to claim 2, characterized in that: The step of creating a temporary view of the data row, and grabbing the data row from the side output stream through the temporary view and writing it to the target data end includes: Determine the operation type to which the temporary view belongs, and call the real-time data integration framework to fetch data rows of the operation type; The captured data row is written to the target data end.

4. The whole database synchronization method based on Flinksql according to claim 3, characterized in that: The step of determining the operation type to which the temporary view belongs and calling the real-time data integration framework to capture the data row of the operation type includes: Determine metadata corresponding to the data row, and create an SQL statement based on the metadata; The SQL statement is executed in the real-time data integration framework to fetch data rows of the operation type.

5. The whole database synchronization method based on Flinksql according to claim 4, characterized in that: The step of determining metadata corresponding to the data row and creating an SQL statement based on the metadata includes: Determine the data table where the metadata is located; The SQL statement is created according to the business attributes of the data table.

6. The whole database synchronization method based on Flinksql according to claim 1, characterized in that: The step of partitioning the output data of the source data end according to the data table information recorded in the binlog log file, and determining at least one side output stream of the output data according to the partition result, comprises: Creating a main data output stream of the data table information, and partitioning the output data in the main data output stream; According to the partitioning result, the main data output stream is split into at least one side output stream.

7. The whole database synchronization method based on Flinksql according to claim 1, characterized in that: Before the step of obtaining the binlog log file in the source data end, it also includes: Capturing data change events of the source data end through a source end connector, and converting the data change events into binlog logs; Write the binlog log into the binlog log file of the source data end.

8. The whole database synchronization method based on Flinksql according to claim 7, characterized in that: Before the step of capturing the data change event of the source data end through the source end connector and converting the data change event into a binlog log, the step further includes: Setting event capture rules according to the configuration parameters of the source data terminal; The event capture rule is deployed to the source-end connector to capture data change events of the source data end.

9. A whole database synchronization device based on Flinksql, characterized in that: The device comprises: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the computer program is configured to implement the steps of the whole database synchronization method based on Flinksql as described in any one of claims 1 to 8.

10. A storage medium, characterized in that: The storage medium is a computer-readable storage medium, and a computer program is stored on the storage medium. When the computer program is executed by a processor, the steps of the whole database synchronization method based on Flinksql are implemented as described in any one of claims 1 to 8.

Images