System and method for realizing synchronous distribution to heterogeneous database based on kafka

Abstract

The invention discloses a system and method for realizing synchronous distribution to a heterogeneous database based on kafka, and the system comprises a message middleware module which is used for receiving synchronous data sent by each synchronous source at a source end through employing a distributed publishing and subscribing message system, and sending the received data to each copy of a specified theme, each copy is locally persistent in a log file form; the synchronization rule configuration management module is used for configuring a synchronization rule of each synchronization source of the source end and each synchronization target end of the standby end, and generating a corresponding configuration file according to the configured synchronization rule; and the data synchronization processing module is used for consuming the data received and stored in the local by the message middleware module according to the configuration file content of the synchronization rule corresponding to the synchronization request when the synchronization request is received, and processing, converting and sending the data to a corresponding synchronization target end of the standby end according to the corresponding synchronization rule.

Description

Technical Field

[0001] The present invention relates to the technical field of computer data conversion and synchronization, and in particular to a system and method for realizing synchronous distribution to heterogeneous databases based on Kafka. Background Art

[0002] Due to business development needs, there may be a need for heterogeneous data migration and real-time synchronization in many cases. For the synchronization of heterogeneous databases, due to database differences, such as data types and lengths, a series of corresponding conversions are required to meet the needs. Common heterogeneous database synchronization is mainly for the cold standby stage, and cannot be used for incremental data synchronization, has no additional custom content, and cannot synchronize one-to-many (one source database, multiple standby databases) data at the same time.

[0003] Message Oriented Middleware (MOM) is a middleware technology composed of a message transmission mechanism or a message queue mode. It uses an efficient and reliable message transmission mechanism to exchange data independently of the platform, and integrates distributed systems based on data communication, by providing a variety of clustering methods and message transmission models. MOM can realize two-way data communication and asynchronous message reception in this communication mode. The message sender does not need to wait for the receiver's response, reducing the coupling degree of software multi-system integration and ensuring reliable, flexible and efficient operation of the system. At present, message middleware technology has been widely used in data synchronization.

[0004] Kafka is a high-throughput distributed publish-subscribe messaging system. As a mainstream messaging middleware, it can process all action stream data of consumers on the website. Introducing a Kafka cluster as an intermediate station for data synchronization, temporarily storing data on Kafka, and through Kafka's publish and subscribe mode, data distribution and reuse can be achieved.

[0005] Kafka solves the problem of temporary data storage and data usage, but for complex business needs, it is not enough to rely solely on the database-kafka-database model. It is also necessary to use the synchronization between Kafka clusters, relational databases and non-relational databases to meet business needs. If multiple data synchronization needs are developed and maintained separately, the development cost and operation and maintenance cost will definitely increase. Therefore, how to conveniently and quickly distribute the same or several database data to various types of other databases and message clusters for downstream use without too much manual intervention has become an urgent problem to be solved. Summary of the invention

[0006] In order to overcome the shortcomings of the above-mentioned prior art, the purpose of the present invention is to provide a system and method for realizing synchronous distribution to heterogeneous databases based on Kafka, so as to realize the synchronization of multiple heterogeneous and homogeneous databases and the distribution, aggregation and logical mirroring of messages between Kafka clusters.

[0007] To achieve the above object, the present invention provides a system for implementing synchronous distribution to heterogeneous databases based on Kafka, comprising: The message middleware module is used to adopt a distributed publish-subscribe message system to receive synchronization data sent by each synchronization source on the source side, and send the received data to each replica of the specified topic. Each replica is persisted locally in the form of a log file. The synchronization rule configuration management module is used to configure the synchronization rules of each synchronization source on the source end and each synchronization target end on the backup end, and generate corresponding configuration files according to the configured synchronization rules; The data synchronization processing module is used to consume the data received and stored locally by the message middleware module according to the configuration file content of the synchronization rule corresponding to the synchronization request when a synchronization request is received, and process, transform and send the data to the corresponding synchronization target end of the backup end according to the corresponding synchronization rule.

[0008] Preferably, the synchronization rules include at least the subject of the synchronization data and the location information of the synchronization data. Each synchronization rule generates a corresponding unique parameter uuid, and the parameter uuid is a system global parameter. The state of each synchronization rule serves as a condition for each thread under the rule to maintain the running state, and the rule state is globally shared.

[0009] Preferably, the synchronization rule configuration management module includes: A synchronization rule configuration unit is used to check whether there is a related thread group according to the uuid in the synchronization rule parameter content when receiving a synchronization rule addition / modification request, and if so, stop the related thread, combine the obtained configuration parameter content with the local corresponding configuration file to generate a new configuration file corresponding to the uuid, and store it locally; when receiving a synchronization rule deletion request, mark the rule status, query whether there is a surviving rule-related thread locally according to the uuid in the synchronization rule parameter content, and if so, notify the interruption until all threads are stopped, and clear the rule status and rule configuration file; The synchronization rule startup unit is used to query whether there are any surviving rule-related threads locally when receiving a synchronization rule startup request. If there are, it notifies to stop the related threads until all related threads are stopped, and then sends a synchronization request to the data synchronization processing module to start the corresponding uuid thread group according to the uuid and mark the running status of the rule.

[0010] Preferably, the data synchronization processing module further includes: The Kafka consumer thread startup unit is used to start the Kafka consumer thread when a synchronization request is received, consume the data of the corresponding topic in the Kafka cluster according to the configuration file content and put it into the rule queue, and format each consumed message into the corresponding object according to the data type supported by the synchronization target end during heterogeneous synchronization, and then put it into the rule queue; The data processing thread startup unit is used to create or start the data processing thread, obtain data from the rule queue, and format it into corresponding objects according to the data type supported by the synchronization target end. It determines whether to submit the batch synchronization task based on the previous and subsequent data operation types and whether the fields have changed.

[0011] Preferably, the naming convention of the created thread name is uuid_function name_task name_Thread.

[0012] Preferably, the rule link of each synchronization rule has an independent processing process, an independent data processing thread set, and a file system unit. The synchronization rule configuration unit can independently operate any synchronization rule through an instruction set.

[0013] Preferably, if the synchronization target end type is a relational database, the JSON data is converted into an object and then the corresponding type conversion is performed; if the synchronization target end type is a non-relational database, it is customized and developed according to the characteristics of the corresponding database; if the synchronization target end is a hive database, the JSON data is converted into an object and then processed into a file in orc / parquet format, and stored in hdfs in orc / parquet format.

[0014] Preferably, the Kafka consumer thread starting unit consumes the specified topic according to the configuration file content of the synchronization rule, and consumes by partition, and starts a Kafka consumer thread for each partition to ensure the orderliness of the data.

[0015] Preferably, when a certain number of data synchronizations are successful or the rules are stopped, the data synchronization processing module refreshes the subject, address information and other information of the synchronization rules and stores them on the disk for breakpoint resumption.

[0016] To achieve the above object, the present invention also provides a method for realizing synchronous distribution to heterogeneous databases based on Kafka, comprising the following steps: Step S1, receiving the synchronization data sent by each synchronization source on the source end through the message middleware module, and sending the received data to each copy of the specified topic, each copy is persisted locally in the form of a log file; Step S2, using the synchronization rule management module to configure the synchronization rules of each synchronization source on the source end and each synchronization target end on the backup end, and generating a corresponding configuration file according to the configured synchronization rules; Step S3, using the synchronization rule management module to receive the synchronization rule start request, query whether there are any surviving rule-related threads locally, and if so, notify to stop the related threads until all related threads are stopped, and then send a synchronization request to the data synchronization processing module to start the corresponding uuid thread group according to the uuid and mark the running status of the rule; Step S4, when receiving a synchronization request, the data received and stored locally by the message middleware module is consumed according to the configuration file content of the synchronization rule corresponding to the synchronization request, and is processed, converted and sent to the corresponding synchronization target end of the backup end according to the corresponding synchronization rule.

[0017] Compared with the prior art, the system and method for realizing synchronous distribution to heterogeneous databases based on Kafka of the present invention receives the synchronization data sent by each synchronization source of the source end by using the message middleware module, and sends the received data to each copy of the specified topic, each copy is persisted locally in the form of a log file, and uses the synchronization rule configuration management module to configure the synchronization rules of each synchronization source of the source end and each synchronization target end of the backup end, generates a corresponding configuration file according to the configured synchronization rules, and when receiving a synchronization request, consumes the data received and stored locally by the message middleware module according to the configuration file content of the synchronization rule corresponding to the synchronization request, processes, transforms and sends it to the corresponding synchronization target end of the backup end according to the corresponding synchronization rule. Through the present invention, the synchronization of multiple heterogeneous and homogeneous databases and the distribution, aggregation and logical mirroring of messages between Kafka clusters can be realized. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] Figure 1 This is a system architecture diagram of a system for implementing synchronous distribution to heterogeneous databases based on Kafka in the present invention; Figure 2-Figure 4 This is a schematic diagram of Web configuration in an embodiment of the present invention; Figure 5 A schematic diagram of a rule link of synchronization rules in a specific embodiment of the present invention; Figure 6 The present invention is a flowchart of the steps of a method for realizing synchronous distribution to heterogeneous databases based on Kafka. DETAILED DESCRIPTION

[0019] The following describes the implementation of the present invention through specific examples and in conjunction with the accompanying drawings. Those skilled in the art can easily understand other advantages and effects of the present invention from the contents disclosed in this specification. The present invention can also be implemented or applied through other different specific examples, and the details in this specification can also be modified and changed in various ways based on different viewpoints and applications without departing from the spirit of the present invention.

[0020] Figure 1This is an architecture diagram of a system for implementing synchronous distribution to heterogeneous databases based on Kafka in the present invention. Figure 1 As shown, the present invention provides a system for implementing synchronous distribution to heterogeneous databases based on Kafka, including: The message middleware module 10 is used to adopt a distributed publish-subscribe message system to receive synchronization data sent by each synchronization source on the source end, and send the received data to each copy of a specified topic. Each copy is persisted locally in the form of a log file.

[0021] In a specific embodiment of the present invention, the source end has several synchronization sources, including but not limited to mysql, oracle, hive, hbase and other databases. Since the data format of the source end data is not necessarily the data format supported by the message middleware module 10, when each synchronization source on the source end wants to synchronize, it is necessary to convert the data format of its synchronization data into the data format supported by the message middleware module 10, and then send it to the message middleware module 10 according to the synchronization rules.

[0022] In the present invention, the message middleware module 10 adopts the mainstream middleware kafka. Specifically, the message middleware module 10 includes several kafka clusters, such as kafka cluster a, kafka cluster b, kafka cluster c... Since the message middleware kafka supports input in JSON (JavaScript Object Notation) format, in a specific embodiment of the present invention, the source end converts the extracted data to be synchronized into JSON data in JSON format and sends it to the corresponding kafka cluster of the message middleware module 10 according to the synchronization rules. After receiving the synchronization data, the corresponding kafka cluster sends the received data to each copy of the specified topic according to its topic. Each copy is persisted locally in the form of a log file. During the persistence time, data of the same topic can be consumed repeatedly.

[0023] In the present invention, before synchronization, the synchronization source database of each source end and the database of each target end will be configured with synchronization rules in advance through the Web interface. Each synchronization rule can only correspond to a kafak cluster of the message middleware. Taking oracle-kafka-mysql as an example, only one kafka cluster is involved. In the oracle-kafka stage, a cluster such as kafka cluster a will be specified through the synchronization rule configuration, and data will be transferred to the specified topic of kafka cluster a. In the kafka-mysql stage, the parameters of kafka cluster a and the corresponding kafka topic parameters will be configured in the corresponding synchronization rules to consume the topic data corresponding to the cluster, such as Figure 2 The figure shows a schematic diagram of the Web interface configuration of kafka-oracle synchronization in a specific embodiment of the present invention.

[0024] Of course, the present invention can also realize the mirroring and aggregation of clusters in the message middleware, that is, Kafka cluster-Kafka cluster. In this case, the synchronization rules of the two Kafka clusters can be configured through the Web interface. The synchronization rules here will involve two Kafka clusters, which belong to the Kafka cluster mirroring content. The parameters of Kafka cluster a and Kafka cluster b are configured through the web interface. It has two modes: one is to send the topics data of consumption cluster a to the topics of Kafka cluster b. This mode can realize one-to-one mirroring between topics; it can also realize sending multiple topics of cluster a to a topic of cluster b, that is, aggregation mode, such as Figure 3 The figure shows a schematic diagram of the Web interface configuration of Kafka-kafka synchronization in an embodiment of the present invention. Kafka-kafka synchronization supports mirroring and aggregation, which are performed according to the configured topics. For mirroring, if the number of topics configured on the source end is consistent with the number of target ends, the data of the topics configured on the source end will be synchronized to the corresponding topics on the target end in the order of topic configuration. The final effect is that the data in each partition of each topic on the target end is consistent with the data in each partition of the corresponding topic on the source end (including key, value, and timestamp information are completely consistent). For aggregation, when the source end is configured with multiple topics and the target end is configured with only one topic, the data of each topic on the source end will be synchronized to the target end in the original order of partitions in the topic. When the number of topics configured on the source end is inconsistent with that on the target end and the number of topics on the target end is not 1, all data in the multiple topics configured on the source end will be synchronized to the multiple topics on the target end according to the balancing strategy.

[0025] Kafka-kafka synchronization can be performed between two clusters or between different topics in the same cluster. However, two database node information needs to be configured. For each Kafka configuration, refer to the following: Figure 4 shown.

[0026] When the remote network environment is not accessible, you can combine the above process, that is, oracle-kafka a- kafka b –mysql.

[0027] Before synchronization, create a topic through background commands. For example, use background commands to generate the following command: . / bin / kafka-topics.sh --create --zookeeper 192.168.86.63:2181,192.168.86.64:2181, 192.168.86.65:2181 --partitions 3 --replication-factor 3 --topic mysqltest To create a topic, specify that the topic has three partitions and three replicas; After creating the topic, the producer specifies the topic of the cluster and then transfers data to the topic.

[0028] In the present invention, in order to improve the system performance, the message middleware module 10 adopts a cluster solution, that is, a kafka cluster is used as the message middleware. A cluster is similar to a domain. When the queue managers in the cluster communicate with each other, there is no need to establish message channels between each other. Instead, cluster channels are used to communicate with other members, thereby greatly simplifying the system configuration. Moreover, the queue managers in the cluster can automatically perform load balancing. When a queue manager fails, other queue managers can take over its work, thereby greatly improving the high reliability of the system.

[0029] The synchronization rule configuration management module 20 is used to configure the synchronization rules of each synchronization source on the source end and each synchronization target end on the backup end, and generate corresponding configuration files according to the configured synchronization rules.

[0030] In the present invention, the synchronization rule configuration management module 20 configures corresponding synchronization rules (such as Figure 2 , Figure 3 and Figure 4), the configured synchronization rules can customize the content, and the rule content has strong flexibility, including but not limited to the topic of the synchronized data, the location of the synchronized data, and whether message authentication is required. Specifically, the synchronization rule configuration management module 20 can configure the synchronization rules through the Web configuration page. Taking Oracle as the source end as an example, each rule on the source end interface will configure which tables under which user need to be synchronized; if MySQL is used as the source end as an example, it may be configured to synchronize by library or by table. If it is synchronized by library, a topic is configured, and if it is synchronized by table, a topic can be configured for each table, or multiple tables can be configured as the same topic. The standby end (that is, kafka-other databases) is generally configured with the same information. In a specific embodiment of the present invention, the standby end used for data synchronization may include several synchronization target ends, and the synchronization target end may include databases that are isomorphic or heterogeneous with the synchronization sources (databases) of the source end, such as MySQL, Oracle, Hive, Hbase, etc.

[0031] In the present invention, the synchronization rule configuration management module 20 is also used to configure the synchronization between message middlewares. Taking kafka a-kafka b as an example, each rule will involve two kafka clusters, which belong to the kafka cluster mirror content. The parameters of kafka cluster a and kafka cluster b will be configured on the Web configuration interface. By consuming the topics data of cluster a, the data is sent to the topics of kafka cluster b, which can realize one-to-one mirroring between topics, and can also realize sending multiple topics of cluster a to one topic of cluster b.

[0032] In a specific embodiment of the present invention, the synchronization rules are configured and issued through a client such as a web page, that is, the user can set each synchronization rule through the client (web page) according to the actual situation, and issue it to the synchronization rule configuration management module 20, and the synchronization rule configuration management module 20 independently generates a corresponding configuration file for each synchronization rule according to the configured synchronization rules. In the present invention, each synchronization rule can be freely added, stopped, modified, continued, and deleted. Specifically, the synchronization rule configuration management module 20 further includes: The synchronization rule configuration unit 201 is used for, when receiving a synchronization rule adding / modifying request, the synchronization rule adding / modifying request at least includes the parameter content of the synchronization rule, checking whether there is a related thread group according to the uuid in the synchronization rule parameter content, and if so, stopping the related thread; combining the obtained configuration parameter content with the corresponding local configuration file to generate a new uuid-related configuration file, and storing it locally; when receiving a synchronization rule deletion request, marking the rule status, querying whether there is a surviving rule-related thread locally according to the uuid in the synchronization rule parameter content, if so, notifying the interruption until all threads stop, and clearing the rule status and rule configuration file.

[0033] Specifically, when adding or modifying synchronization rules through the client (web page), the client (web page) notifies the synchronization rule configuration unit 201 through TCP. The synchronization rule configuration unit 201 checks whether the thread group exists in the thread group according to the uuid in the rule, and stops if it exists. The copied and transmitted json configuration parameter content is combined with the local basic configuration file to generate a new uuid-related configuration file and stored locally; when deleting rules through the client (web page), the client (web page) notifies the synchronization rule configuration unit 201 through TCP. After receiving the notification, the synchronization rule configuration unit 201 marks the rule status and queries whether there are any surviving rule-related threads locally. If so, the notification is interrupted until all threads stop, and the rule status and rule configuration file are cleared. In the present invention, when configuring synchronization rules, each synchronization rule will generate a corresponding unique parameter uuid. For example, the synchronization rule from the configured kafka cluster to the backup kafka cluster has a unique parameter uuid. The parameter uuid is a system global parameter. The creation of threads for all data synchronization tasks under this synchronization rule is related to the parameter uuid. When synchronizing data, data processing threads and Kafka consumer threads will be created. When consuming data from Kafka, since the efficiency of grabbing Kafka data according to Kafka partitions is much higher than the subscription method, each partition corresponds to a consumer thread. For example, if there are 2 topics and each topic has three partitions, 6 Kafka consumer threads will be created. The main responsibilities of each data processing thread are to filter out supported operation types, process DDL part data, and collect DML data. In the specific embodiment of the present invention, the data processing thread adopts the table distribution and single table concurrency method (table distribution will trigger single table concurrency). The data processing thread is the same as the consumer thread. It will also be created according to the topic-partition. The table distribution thread processes the DML data in the queue of each table. The naming specification of each created thread name is "uuid_function name_task name_Thread". The state of each synchronization rule is used as the condition for each thread under the rule to maintain the running state, and the rule state is globally shared. In the present invention, each rule link of a synchronization rule (for example, a synchronization rule for synchronizing a kafka cluster to a mysql database, the process of synchronizing a kafka cluster to a mysql database according to the rule is called the rule link of the synchronization rule, that is, corresponding to the synchronization rule, synchronizing the data of the kafka cluster to the mysql database) has an independent processing process, such as Figure 5 The figure shows the life cycle of each rule. Each rule link has an independent processing process, an independent data processing thread set (multiple data processing threads. For example, if a topic has three partitions, three data processing threads will be created to process the data of each partition respectively), and a file system unit (each rule has an independent file directory. Each rule directory will record the temporary storage data of each table and the consumption records of each topic). Any rule can be operated independently through the instruction set.

[0034] In a specific embodiment of the present invention, for a newly added type of database or a change in the Kafka message format, hot deployment can be performed using a plug-in without stopping the service.

[0035] The synchronization rule starting unit 202 is used to query whether there are any surviving rule-related threads locally when receiving a synchronization rule starting request. If there are, it notifies to stop the related threads until all related threads are stopped, and then sends a synchronization request to the data synchronization processing module 30 to start the corresponding uuid thread group according to the uuid and mark the running status of the rule.

[0036] Specifically, when the client (web page) issues a synchronization rule to start (for example, when it is necessary to synchronize one or some topic data of Kafka to a database on the backup side, click to run the corresponding synchronization rule), after the synchronization rule start unit 202 receives the notification request, it queries whether there are any surviving rule-related threads locally according to the uuid in the request. If there are, the notification stops until all related threads stop, and then sends a synchronization request to the data synchronization processing module to start the corresponding uuid thread group and mark the running status of the rule. In the present invention, each thread is prefixed with uuid in its name to facilitate thread management. After the rule is started, as the synchronization rule proceeds, multiple directory files will be generated under each rule directory, such as configuration files related to the rule, files related to each topic and partition information, temporarily stored data files, table primary key identification, external notifications, and even derby content related to hbase rowkey, etc.

[0037] Preferably, the synchronization rule configuration management module 20 also includes: a synchronization rule status query unit 203, which is used to record the real-time status of each rule operation when receiving a rule status query request, including the number of message processing, the current state of the rule, the peak message processing speed and other information, and return the recorded information to the client. In other words, when the user wants to query the real-time status of the synchronization rule operation, the rule status query request can be sent to the synchronization rule status query unit 203 through the client (web page), and the synchronization rule status query unit 203 records the real-time status of each synchronization rule operation, including the number of message processing, the current state of the rule, the peak message processing speed and other information, and returns the recorded value to the client.

[0038] The data synchronization processing module 30 is used to consume the data received and stored locally by the message middleware module according to the configuration file content of the synchronization rule corresponding to the synchronization request when a synchronization request is received, and process, transform and send the data to the corresponding synchronization target end of the backup end according to the corresponding synchronization rule, such as MySQL, Oracle, Hive, Hbase and other databases or other Kafka cluster environments.

[0039] Specifically, the data synchronization processing module 30 further includes: The Kafka consumer thread starting unit 301 is used to start the Kafka consumer thread when a synchronization request is received, consume the data of the corresponding topic in the Kafka cluster according to the configuration file content and put it into the rule queue, and format each consumed message into a corresponding object according to the data type supported by the synchronization target end during heterogeneous synchronization and then put it into the rule queue.

[0040] In a specific embodiment of the present invention, the Kafka cluster of the message middleware module 10 can write the received data into the Kafka log system according to specific rules (referring to synchronization methods, such as synchronization by table, synchronization by user, synchronization by library, and a specific topic can be specified in each synchronization method). For example, in a rule such as synchronization by table, three tables need to be synchronized, table1, table2, and table3, among which table1 and table2 have less data volume and can be put into one topic, and table3 has a larger data volume and can be put into another topic. The data of the table is ordered in a single partition.

[0041] When it is necessary to synchronize one or some Kafka topic data to the standby synchronization target end (database or Kafka cluster), click to run the corresponding synchronization rule (that is, all synchronizations need to be configured in advance on the interface, and the main content of the configuration is to synchronize the specified topic data of the Kafka cluster to a specific database (mysql / oracle, etc.). After adding the rules, you can select and start the rules that need to be synchronized from the rule list for automatic synchronization). After processing, the synchronization rule startup unit sends the synchronization request to the data synchronization processing module 30. The Kafka consumer thread startup unit 301 consumes the specified topic according to the configuration file content of the synchronization rule, and consumes by partition. A Kafka consumer thread is started for each partition to ensure the orderliness of the data.

[0042] The data processing thread startup unit 302 is used to create or start a data processing thread, obtain data from the rule queue, and format it into a corresponding object according to the data type supported by the synchronization target end. It determines whether to submit a batch task according to the previous and next data operation types (dml / ddl) and whether the field has changed. After the task is submitted, the data reaches the corresponding synchronization target source on the backup end, thereby realizing data synchronization. It should be noted that in the present invention, the data processing thread is created when the rule is started, and will continue to exist until the rule is stopped or deleted, and the data processing thread will die. For each Kafka message, it will record what action the message is taking, whether it is a DDL type (such as creating a table, modifying column attributes, adding or subtracting columns) or a DML (add, delete, and modify) operation type. For relational databases such as Oracle / MySQL, in order to improve processing efficiency, the present invention will use batch processing to process data. For example, data in the same partition may be multiple insertions, deletions, or updates of the same table. At this time, they will be included in a batch processing task in sequence. When the inserted message is converted to a deletion, the previous insertion batch processing task will be submitted, and the next deletion action will be included in the deletion batch processing task. When DDL operations such as modifying columns occur, a batch processing task will be submitted. In other words, the DML part will have a batch processing process (of course, the maximum batch processing time and the maximum number of messages processed in each batch will be considered during batch processing. Messages exceeding this number will be processed in the next batch).

[0043] Specifically, in the present invention, when starting synchronization, if the current data synchronization is heterogeneous data synchronization, such as mysql-kafka-oracle synchronization, the fields of the mysql synchronized table may contain text, tinytext and other fields, which do not exist in oracle, but oracle has clob type fields to replace them. Therefore, the data processing thread starting unit 302 first needs to format the consumed messages according to the data type supported by the synchronization target end.

[0044] For heterogeneous data synchronization, the data processing thread starting unit 302 can implement corresponding data type conversion and data content conversion according to the data type supported by the synchronization target of the backup end. Specifically, if the synchronization target end type is a relational database, the JSON data is converted into an object and then the corresponding type conversion is performed; if the synchronization target end is a non-relational database, it is necessary to customize the development and implementation according to the characteristics of the corresponding database. Taking the target end as a hive database as an example, the JSON data will be converted into an object and then processed into a file in orc / parquet format, and stored in HDFS using the orc / parquet format. The implementation steps include formatting the Kafka JSON data into specific JSON content format data, uploading it to HDFS through Spark, etc. Specifically, for a database such as Hive, the data in the queue is formatted into a JSON file and stored locally. The file generation method will be generated according to the specific configuration, such as generating one file every five minutes for each table. When the amount of data within 5 minutes is large, the maximum size of each file will be set, such as 500M per file. After each file is generated, a file with the same name and the suffix ok will be generated. Subsequent scheduled tasks will scan each local directory table every 10 seconds and upload the corresponding files to HDFS. In the present invention, for a relational database as the target end, a batch processing process based on JDBC can be implemented, and table distribution and single-table concurrency are used to implement efficient data processing logic. Among them, the process of processing multiple tables in batches is called table distribution, and single-table concurrency refers to putting the same operations (such as all insert actions) together and starting multiple threads to execute together is called single-table concurrency. The logic of table distribution and single-table concurrency is suitable for all relational databases, which can be either isomorphic or heterogeneous relational databases. Specifically, the data processing thread startup unit 302 detects the operation type in the data obtained from the queue. If it is a DDL operation type, it is necessary to wait until all dmls of the table are executed before executing the DDL. If it is dml data, the data is added to the queue of the corresponding table. For the single-table concurrency part, within the specified time (generally, the maximum time for processing a table each time is set to 5s, and it will be called after 5s). Exit the thread and process the table data next time the table is distributed) The data of continuous same operation is processed concurrently according to the batch size. For example, there are 10,000 data in the queue of the table, and a batch is set to 1,000. Every 1,000 data will be submitted to the thread pool for processing as a data processing unit (the thread pool is generally set with 20 core threads, that is, when the single table concurrency is 20, it can process up to 20,000 data at the same time). Each small data processing unit (each processing thread under single table concurrency is a small data processing unit) will convert data according to the data type corresponding to the field; for non-relational databases such as Hive on the target end, use the orc / parquet format to store it on HDFS. The implementation steps include Kafka JSON data is formatted into specific JSON content format data and uploaded to HDFS through Spark. It is also necessary to configure whether to create partitions, temporary tables, and data deduplication. Taking Hive as an example, a JSON file is generated every 5 minutes. Each JSON file may contain many messages. The maximum size of each JSON file is set to 500M. The process of formatting local JSON files into Parquet format and uploading them to HDFS is actually a batch process. Since Hive itself does not support DML for update and delete, it may convert updates and deletes into inserts and mark what operation this message is. After that, it will periodically perform deduplication by using the primary key or unique identifier field to indirectly implement the update and delete functions. In this process, a temporary table is used as a temporary data storage directory. After deduplication is completed, the temporary table data is transferred to the real table. Partitions are created in Hive, generally based on customer needs, for the purpose of efficient query;For synchronization between Kafka clusters, it can be divided into message aggregation (multiple topics of cluster A are aggregated to cluster B), message dispersion (one or more topics of cluster A are published to multiple topics of cluster B according to the message content), and logical mirroring (each topic of cluster A is published to the partition of the corresponding topic of cluster B according to the partition where the message is located). The aggregation and mirroring mentioned here are operations on topics, which are also specified in the rules of the interface. For example, when the Kafka version is upgraded, the old version of the Kafka cluster is no longer in use, but the historical data may be used for future big data analysis and needs to be migrated to the new Kafka cluster. In other cases, for example, the Kafka cluster needs another Kafka cluster as a springboard to obtain data due to network problems. Preferably, when a certain number of data synchronizations are successful or the rule is stopped, the data synchronization processing module 30 will refresh the topic (topic), address information (offset) and other information of the synchronization rule and store them on the disk for breakpoint resumption.

[0045] Figure 6 This is a flow chart of the steps of a method for implementing synchronous distribution to heterogeneous databases based on Kafka in the present invention. Figure 6 As shown, the present invention provides a method for realizing synchronous distribution to heterogeneous databases based on Kafka, comprising the following steps: Step S1, receiving the synchronization data sent by each synchronization source of the source end through the message middleware module, and sending the received data to each copy of the specified topic (topic), and each copy is persisted locally in the form of a log file. In an embodiment of the present invention, the message middleware module adopts the mainstream middleware kafka cluster, and the source end converts the extracted data to be synchronized into JSON data in the format of JSON and sends it to the kafka cluster of the message middleware module. After receiving the synchronization data, the kafka cluster sends the received data to each copy of the specified topic (topic) according to its topic, and each copy is persisted locally in the form of a log file. During the persistence time, the data of the same topic (topic) can be consumed repeatedly.

[0046] Step S2: Use the synchronization rule management module to configure the synchronization rules of each synchronization source on the source end and each synchronization target end on the backup end, and generate a corresponding configuration file according to the configured synchronization rules.

[0047] In the present invention, it is necessary to configure corresponding synchronization rules for data synchronization between each synchronization source on the source end and each synchronization target end on the backup end. The configured synchronization rules can customize the content, and the rule content has strong flexibility, including but not limited to the topic of the synchronization data, the location of the synchronization data, and whether message authentication is required. In a specific embodiment of the present invention, the backup end used for data synchronization includes several synchronization target ends, and the synchronization target end may include a database that is isomorphic or heterogeneous with each synchronization source (each database) on the source end, such as mysql, oracle, hive, hbase, etc., or other message middleware, such as other kafka cluster environments. In a specific embodiment of the present invention, the synchronization rules are configured and sent to the system through a client such as a web page, that is, the user can set each synchronization rule through the client (web page) according to the actual situation, and send it to the system. The system independently generates a corresponding configuration file for each synchronization rule according to the configured synchronization rules. In the present invention, each synchronization rule can be freely added, stopped, modified, continued, and deleted.

[0048] Specifically, in step S2, when a synchronization rule adding / modifying request is received, the synchronization rule adding / modifying request includes at least the parameter content of the synchronization rule, and checks whether there is a related thread group according to the uuid in the synchronization rule parameter content. If so, stops the related thread; combines the obtained configuration parameter content with the corresponding local configuration file to generate a new configuration file corresponding to the uuid, and stores it locally; when a synchronization rule deletion request is received, marks the rule status, and queries whether there are any surviving rule-related threads locally according to the uuid in the synchronization rule parameter content. If so, notifies the interruption until all threads stop, and clears the rule status and rule configuration file.

[0049] In the present invention, when configuring synchronization rules, each synchronization rule will generate a corresponding unique parameter uuid. For example, the synchronization rule from the configured kafka cluster to the standby kafka cluster has a unique parameter uuid. The parameter uuid is a system global parameter. The creation of threads for all data synchronization tasks under the synchronization rule is related to uuid. That is to say, when performing data synchronization, a data processing thread will be created, and the naming convention of each created thread name is "uuid_function name_task name_Thread". The state of each synchronization rule serves as a condition for each thread under the rule to maintain the running state, and the rule state is globally shared.

[0050] Step S3, use the synchronization rule management module to receive the synchronization rule start request, query whether there are any surviving rule-related threads locally, and if so, notify to stop the related threads. After all related threads are stopped, send a synchronization request to the data synchronization processing module to start the corresponding uuid thread group according to the uuid and mark the running status of the rule.

[0051] Specifically, when the client (web page) issues a synchronization rule to start (for example, when it is necessary to synchronize one or some topic data of Kafka to a database on the backup side, click to run the corresponding synchronization rule), query whether there is a surviving rule-related thread locally based on the uuid in the request. If so, notify to stop until all related threads stop, send a synchronization request to the data synchronization processing module to start the corresponding uuid thread group and mark the running status of the rule. In the present invention, each thread is named with uuid as the prefix for thread management. After the rule is started, as the synchronization rule proceeds, multiple directory files will be generated under each rule directory, such as configuration files related to the rule, files related to each topic and partition information, temporarily stored data files, table primary key identification, external notifications, and even derby content related to hbase rowkey, etc.

[0052] Step S4, when receiving a synchronization request, consumes the data received and stored locally by the message middleware module according to the configuration file content of the synchronization rule corresponding to the synchronization request, processes, transforms and sends it to the corresponding synchronization target end of the backup end according to the corresponding synchronization rule, such as MySQL, Oracle, Hive, HBase and other databases or other Kafka cluster environments.

[0053] Specifically, step S4 further includes: Step S401, when a synchronization request is received, the Kafka consumption thread is started, and the data of the corresponding topic in the Kafka cluster is consumed according to the content of the configuration file and put into the rule queue. During heterogeneous synchronization, each consumed message is formatted into a corresponding object according to the data type supported by the synchronization target end and then put into the rule queue.

[0054] In a specific embodiment of the present invention, the Kafka cluster of the message middleware module can write the received data into the Kafka log system according to specific rules (referring to the synchronization method, such as synchronization by table, synchronization by user, synchronization by library, and a specific topic can be specified in each synchronization method). For example, some tables are written to a partition of the corresponding topic, and other tables are written to another partition of the topic. The data of the table is ordered in a single partition.

[0055] When you need to synchronize one or some topics of Kafka to the standby synchronization target (database or Kafka cluster), click to run the corresponding synchronization rule, consume the specified topic according to the configuration file content of the synchronization rule, and consume by partition. Start a Kafka consumption thread for each partition to ensure the orderliness of the data. At the same time, if the current data synchronization is heterogeneous data synchronization, for example, taking mysql-kafka-oracle synchronization as an example, the fields of the mysql synchronized table may contain text, tinytext and other fields, which do not exist in oracle, but there are clob type fields in oracle to replace them, and the consumed messages also need to be formatted according to the data types supported by the synchronization target.

[0056] For heterogeneous data synchronization, the corresponding data type conversion and data content conversion can be implemented according to the data types supported by the synchronization target on the standby side. Specifically, if the synchronization target type is a relational database, the JSON data is converted into objects and then the corresponding type conversion is performed. For non-relational databases, such as Hive, the JSON data is converted into objects and then processed into orc / parquet format files, which are stored in HDFS using the orc / parquet format. The implementation steps include formatting the Kafka JSON data into specific JSON content format data and uploading it to HDFS through Spark, etc. Step S402, create or start a data processing thread, obtain data from the rule queue, and format it into corresponding objects according to the data type supported by the synchronization target end. Determine whether to submit the batch task based on the previous and subsequent data operation types (dml / ddl) and whether the field has changed. After the task is submitted, the data reaches the corresponding synchronization target source on the backup end, thereby realizing data synchronization.

[0057] Specifically, for homogeneous target ends that are heterogeneous and relational databases, a batch processing process based on JDBC can be implemented, and the table of continuous messages, operation type (DML, DDL such as insert / update / delete / alter / create), and update and deletion conditions are included in whether the continuous batch processing conditions are met to achieve efficient data processing logic; for homogeneous target ends that are heterogeneous non-relational databases such as Hive, orc / parquet formats are used to store them on HDFS. The implementation steps include formatting Kafka JSON data into specific JSON content format data, uploading it to HDFS through Spark, and configuring whether to create partitions, temporary tables, and data deduplication. For synchronization between Kafka clusters, it can be divided into message aggregation (multiple topics in cluster A are aggregated to cluster B), message dispersion (one or multiple topics in cluster A are published to multiple topics in cluster B according to the message content), and logical mirroring (each topic in cluster A is published to the partition of the corresponding topic in cluster B according to the partition where the message is located).

[0058] Preferably, when a certain number of data synchronizations are successful or the rule is stopped, the data synchronization processing module 30 will refresh the topic (topic), address information (offset) and other information of the synchronization rule and store them on the disk for breakpoint resumption.

[0059] Example Please continue to refer to Figure 1 , Figure 1 The embodiment of the present invention shows that the log data extracted from the source MySQL / Oracle database is converted and formatted into the specified JSON data and sent to the Kafka cluster of the message middleware module 10. The synchronization rule configuration management module 20 independently generates a corresponding configuration file according to the rules configured in the interface. The data synchronization processing module 30 consumes the Kafka data according to the content of the configuration file and converts and sends it to the backup MySQL / Oracle / Hive / HBase database or other Kafka cluster environment.

[0060] When the system architecture is built, the system of the present invention is used as a server to receive requests sent by the client, start the socket service, listen to the specific port and wait for communication with the agent. That is to say, in this embodiment, the system of the present invention does not directly interact with the client control machine. The client control machine sends a command to iahelper, and iahelper processes the information and sends it to the system of the present invention. The system of the present invention takes corresponding actions based on the received information, such as creating new rules, deleting old synchronization rules, and sending software running status information.

[0061] The following describes the rule creation and operation process of this embodiment: Step 1: The client (web page) sends the add / modify rule and notifies the system through TCP. The system checks whether the thread group exists in the thread group according to the uuid in the rule. If it exists, it stops. Copy the transmitted json configuration parameter content and combine it with the local basic configuration file to generate a new uuid related configuration file and store it locally Step 2: The client (web page) sends the startup rule. After receiving the notification request, the system queries whether there are any surviving rule-related threads locally. If so, the notification stops until all related threads stop, then the uuid thread group is started and the running status of the rule is marked. Each thread is named with uuid as the prefix for thread management. After the rule is started, as the operation progresses, multiple directory files will be generated under each rule directory, such as configuration files related to the rule, files related to each topic and partition information, temporarily stored data files, table primary key identification, external notifications, and even derby content related to hbaserowkey. Step 3: The client (web page) sends a query rule status. The system records the real-time status of each rule, including the number of message processing, the current status of the rule, the peak message processing speed and other information.

[0062] Step 4: The client (web page) sends a stop / delete rule. After receiving the notification request, the system marks the rule status and queries whether there are any surviving rule-related threads locally. If so, the notification is interrupted until all threads stop, and the rule status and rule configuration file are cleared.

[0063] The following is the data synchronization process of this embodiment: Step 1: According to the configuration file content corresponding to the synchronization rule, start the Kafka consumption thread and data processing thread, and store the data consumed by Kafka in the rule queue management. In this embodiment, the data to be synchronized by the source end is written into the Kafka log system according to specific rules, such as some tables are written to a partition of a topic, and other tables are written to another partition of the topic, and the data of the table is ordered in a single partition. This system consumes the specified topic, consumes by partition, and starts a consumption thread for each partition, which can ensure the orderliness of the data, and formats each message consumed into objects one by one according to the type of the synchronized backup database and puts it into the queue.

[0064] Step 2: According to the started data processing logic thread, the data is formatted into the specified format, or processed and added to the required batch processing process. The data processing thread obtains data from the rule queue, and determines whether to submit the batch processing task based on the previous and subsequent data operation types (dml / ddl) and whether the fields have changed. After the task is submitted, the data arrives at the corresponding database on the standby end.

[0065] Step 3: When a certain amount of data is successfully processed or the stop rule is met, refresh the topic offset and other information to disk for breakpoint resuming.

[0066] In summary, the present invention provides a system and method for realizing synchronous distribution to heterogeneous databases based on Kafka, which receives the synchronization data sent by each synchronization source on the source end by utilizing a message middleware module, and sends the received data to each copy of the specified topic, each copy is persisted locally in the form of a log file, and configures the synchronization rules of each synchronization source on the source end and each synchronization target end on the backup end by utilizing a synchronization rule configuration management module, generates a corresponding configuration file according to the configured synchronization rules, and when a synchronization request is received, consumes the data received and stored locally by the message middleware module according to the content of the configuration file of the synchronization rule corresponding to the synchronization request, processes, transforms and sends the data to the corresponding synchronization target end on the backup end according to the corresponding synchronization rule, and realizes the synchronization of multiple heterogeneous and homogeneous databases and the distribution, aggregation and logical mirroring of messages between Kafka clusters.

[0067] The above embodiments are only intended to illustrate the principles and effects of the present invention, and are not intended to limit the present invention. Any person skilled in the art may modify and change the above embodiments without violating the spirit and scope of the present invention. Therefore, the scope of protection of the present invention shall be as set forth in the claims.

Claims

1. A system for implementing synchronous distribution to heterogeneous databases based on Kafka, comprising: The message middleware module is used to adopt a distributed publish-subscribe message system to receive synchronization data sent by each synchronization source on the source side, and send the received data to each replica of the specified topic. Each replica is persisted locally in the form of a log file. The synchronization rule configuration management module is used to configure the synchronization rules of each synchronization source on the source end and each synchronization target end on the backup end, and generate corresponding configuration files according to the configured synchronization rules; The data synchronization processing module is used to consume the data received and stored locally by the message middleware module according to the configuration file content of the synchronization rule corresponding to the synchronization request when a synchronization request is received, and process, transform and send the data to the corresponding synchronization target end of the backup end according to the corresponding synchronization rule.

2. A system for implementing synchronous distribution to heterogeneous databases based on Kafka as claimed in claim 1, characterized in that: The synchronization rule includes at least the subject of the synchronization data and the location information of the synchronization data. Each synchronization rule generates a corresponding unique parameter uuid, and the parameter uuid is a system global parameter. The state of each synchronization rule serves as a condition for each thread under the rule to maintain the running state, and the rule state is globally shared.

3. A system for implementing synchronous distribution to heterogeneous databases based on Kafka as claimed in claim 2, characterized in that: The synchronization rule configuration management module includes: A synchronization rule configuration unit is used to check whether there is a related thread group according to the uuid in the synchronization rule parameter content when receiving a synchronization rule addition / modification request, and if so, stop the related thread, combine the obtained configuration parameter content with the local corresponding configuration file to generate a new configuration file corresponding to the uuid, and store it locally; when receiving a synchronization rule deletion request, mark the rule status, query whether there is a surviving rule-related thread locally according to the uuid in the synchronization rule parameter content, and if so, notify the interruption until all threads are stopped, and clear the rule status and rule configuration file; The synchronization rule startup unit is used to query whether there are any surviving rule-related threads locally when receiving a synchronization rule startup request. If there are, it notifies to stop the related threads until all related threads are stopped, and then sends a synchronization request to the data synchronization processing module to start the corresponding uuid thread group according to the uuid and mark the running status of the rule.

4. A system for implementing synchronous distribution to heterogeneous databases based on Kafka as claimed in claim 3, characterized in that: The data synchronization processing module further includes: The Kafka consumer thread startup unit is used to start the Kafka consumer thread when a synchronization request is received, consume the data of the corresponding topic in the Kafka cluster according to the configuration file content and put it into the rule queue, and format each consumed message into the corresponding object according to the data type supported by the synchronization target end during heterogeneous synchronization, and then put it into the rule queue; The data processing thread startup unit is used to create or start the data processing thread, obtain data from the rule queue, and format it into corresponding objects according to the data type supported by the synchronization target end. It determines whether to submit the batch synchronization task based on the previous and subsequent data operation types and whether the fields have changed.

5. A system for implementing synchronous distribution to heterogeneous databases based on Kafka as claimed in claim 4, characterized in that: The naming convention for the created thread name is uuid_function name_task name_Thread.

6. A system for implementing synchronous distribution to heterogeneous databases based on Kafka as claimed in claim 5, characterized in that: The rule link of each synchronization rule has an independent processing process, an independent data processing thread set, and a file system unit. The synchronization rule configuration unit can independently operate any synchronization rule through an instruction set.

7. A system for implementing synchronous distribution to heterogeneous databases based on Kafka as claimed in claim 4, characterized in that: If the synchronization target is a relational database, the JSON data will be converted into objects and then the corresponding type conversion will be performed. If the synchronization target is a non-relational database, the development and implementation will be customized according to the characteristics of the corresponding database. If the synchronization target is a hive database, the JSON data will be converted into objects and then processed into orc / parquet format files, which will be stored in HDFS in orc / parquet format.

8. A system for implementing synchronous distribution to heterogeneous databases based on Kafka as claimed in claim 7, characterized in that: The Kafka consumer thread startup unit consumes the specified topic according to the configuration file content of the synchronization rule, and consumes by partition, and starts a Kafka consumer thread for each partition to ensure the orderliness of the data.

9. A system for implementing synchronous distribution to heterogeneous databases based on Kafka as claimed in claim 8, characterized in that: When a certain number of data synchronizations are successful or the rules are stopped, the data synchronization processing module refreshes the subject, address information and other information of the synchronization rules and stores them on the disk for breakpoint resumption.

10. A method for synchronous distribution to heterogeneous databases based on Kafka, comprising the following steps: Step S1, receiving the synchronization data sent by each synchronization source on the source end through the message middleware module, and sending the received data to each copy of the specified topic, each copy is persisted locally in the form of a log file; Step S2, using the synchronization rule management module to configure the synchronization rules of each synchronization source on the source end and each synchronization target end on the backup end, and generating a corresponding configuration file according to the configured synchronization rules; Step S3, using the synchronization rule management module to receive the synchronization rule start request, query whether there are any surviving rule-related threads locally, and if so, notify to stop the related threads until all related threads are stopped, and then send a synchronization request to the data synchronization processing module to start the corresponding uuid thread group according to the uuid and mark the running status of the rule; Step S4, when receiving a synchronization request, the data received and stored locally by the message middleware module is consumed according to the configuration file content of the synchronization rule corresponding to the synchronization request, and is processed, converted and sent to the corresponding synchronization target end of the backup end according to the corresponding synchronization rule.

Images