Data synchronization method and device, electronic equipment and storage medium
Through binary log files and regular matching technology, efficient synchronization of MySQL data sets is achieved, solving the problems of long synchronization time of existing tools and high system pressure, and improving the reusability and automation of data synchronization.
Patent Information
- Application Number
- CN202411125190.X
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-15
- Publication Date
- 2024-11-26
AI Technical Summary
Existing data synchronization tools such as Apache Sqoop have a long extraction time during the data synchronization process, data is not reusable, and it has a high pressure on MySQL, so it cannot automatically switch to other methods for data synchronization, resulting in increased synchronization time and system pressure. Enlarge.
By detecting changes in database data sets, using binary log files to transfer data to the change data channel, parsing and partitioning the data, using regular matching shunt data, synchronizing to the data warehouse with preset path templates, and synchronizing with the initial data set, realizing data Efficient synchronization.
It realizes efficient synchronization of data sets, reduces the pressure on MySQL, improves the reusability and scalability of data synchronization, automates the synchronization process, and reduces manual intervention.
Smart Images

Figure CN119025595A_ABST
Abstract
Description
Technical Field
[0001] The present disclosure relates to the field of data processing, and in particular to a data synchronization method, device, electronic device and storage medium. Background Art
[0002] In data synchronization, it is necessary to synchronize the structured query language (MySQL) data to the data warehouse. In related technologies, tools such as data synchronization tools (Apache Sqoop) are usually used for full data extraction.
[0003] However, using Apache Sqoop to extract data takes a long time, and the data is not reusable, and it is impossible to switch from Apache Sqoop to another method for data synchronization. In addition, Apache Sqoop will cause greater pressure on MySQL and increase the time for data synchronization. Summary of the invention
[0004] In order to overcome the problems existing in the related art, the present disclosure provides a data synchronization method, device, electronic device and storage medium.
[0005] According to a first aspect of an embodiment of the present disclosure, a data synchronization method is provided, comprising: in response to detecting that a data set in a database has changed, transmitting the data set in the database in the form of a binary log file to a change data channel; parsing the data set based on the change data channel to obtain a first data set in a preset format, wherein the preset format includes a primary key corresponding to the first data set; partitioning the first data set according to the primary key of each data set in the first data set to obtain a second data set, and storing the second data set in a layer cluster of a system cluster, wherein the second data set includes multiple partitions, wherein the multiple partitions include multiple data sets with the same primary key, and the system cluster includes different layers of clusters for storing the changed data sets, wherein different layers of clusters have different corresponding functions; performing regular matching on the second data set to obtain a third data set, and storing the third data set in a layer cluster of the system cluster, wherein the regular matching is used to divert data in the second data set, and the layer cluster is used to store the second data set after the regular matching; synchronizing the third data set to a data warehouse based on the third data set and a preset path template; loading the third data set in the data warehouse, synchronizing the third data set with the initial data set, and obtaining a synchronized target data set.
[0006] In one embodiment, partitioning the first data set according to the primary key of each data set in the first data set to obtain the second data set includes: splicing the data including the primary key in the first data set to obtain first spliced data; determining a hash value of the first spliced data, and taking a modulus of a preset partition number based on the hash value, and using the modulo value as a partition number; storing the first spliced data in a storage area corresponding to the partition number to obtain a second data set stored based on the partition number.
[0007] In one embodiment, the regular matching of the second data set to obtain a third data set, and storing the third data set in the second-layer cluster of the system cluster, includes: matching the second data set based on a regular expression in the regular matching; in response to a successful match between the regular expression and a data set in the second data set, diverting the data set based on the regular expression matched by the data set to obtain different data sets corresponding to different regular expressions, storing the data sets in the second data set according to a storage area corresponding to the regular expression to obtain a third data set; in response to an unsuccessful match between the regular expression and the data set in the second data set, naming the data set according to a preset naming rule to obtain a third data set; and storing the third data set in the second-layer cluster.
[0008] In one embodiment, synchronizing the third data set to the data warehouse based on the third data set and a preset path template includes: obtaining the third data set in the second-layer cluster, splicing the data in the third data set according to the preset path template to obtain second spliced data, wherein the second spliced data includes a database name, a table name, and a timestamp; using the database name, table name, and timestamp in the second spliced data as an index to create a data warehouse corresponding to the second spliced data; and writing the third data set into the data warehouse based on the index.
[0009] In one embodiment, the initial data set includes an initial log data set and an initial stock data set, the initial log data set includes data before the data set is changed within a preset time and data after the data set is changed within the preset time, and the initial stock data set is a stock data set obtained by synchronizing the data before the preset time; synchronizing the third data set with the initial data set to obtain a synchronized target data set includes: obtaining the last updated data in the initial log data set; creating a merged data set based on the last updated data; left-joining and right-joining the initial stock data set with the merged data set to compare the initial stock data set with the merged data set; obtaining data that is different between the merged data set and the initial stock data set, and storing the different data in the merged data set to obtain the target data set.
[0010] In one embodiment, in response to detecting that a data set in a database has changed, the data set is synchronized based on a data synchronization tool; after obtaining the target data set, the method further includes: in response to the target data set being the same as the data obtained after data synchronization with the data synchronization tool, suspending the use of the data synchronization tool for data synchronization and switching to data synchronization using the binary log file.
[0011] In one embodiment, the method further includes: in response to detecting a table structure change event, reporting the table structure change event or parsing the table structure change event based on preset fields; in response to a data anomaly that has occurred in a current database, relocating to the location of the abnormal data and repairing the data; in response to the current database being unable to use data synchronization in the form of a binary log file, switching to the data synchronization tool for data synchronization.
[0012] According to a second aspect of an embodiment of the present disclosure, a data synchronization device is provided, including a transmission unit, for transmitting the data set in the database in the form of a binary log file to a change data channel in response to detecting a change in the data set in the database; a processing unit, for parsing the data set based on the change data channel to obtain a first data set in a preset format, wherein the preset format includes a primary key corresponding to the first data set; a determination unit, for partitioning the first data set according to the primary key of each data set in the first data set to obtain a second data set, and storing the second data set in a layer of a system cluster, wherein the second data set includes multiple partitions, A partition includes multiple data sets with the same primary key, and the system cluster includes different layers of clusters for storing changed data sets, wherein different layers of clusters have different corresponding functions; the second data set is regularly matched to obtain a third data set, and the third data set is stored in the second layer cluster of the system cluster, the regular matching is used to divert the data in the second data set, and the second layer cluster is used to store the second data set after regular matching; a synchronization unit synchronizes the third data set to a data warehouse based on the third data set and a preset path template; the third data set in the data warehouse is loaded, and the third data set is synchronized with the initial data set to obtain a synchronized target data set.
[0013] In one embodiment, the determination unit partitions the first data set according to the primary key of each data set in the first data set to obtain a second data set in the following manner: splicing the data including the primary key in the first data set to obtain first spliced data; determining a hash value of the first spliced data, and taking a modulus of a preset number of partitions based on the hash value, and using the modulo value as a partition number; storing the first spliced data in a storage area corresponding to the partition number to obtain a second data set stored based on the partition number.
[0014] In one embodiment, the determination unit performs regular matching on the second data set to obtain a third data set, and stores the third data set in the second-layer cluster of the system cluster in the following manner: matching the second data set based on the regular expression in the regular matching; in response to the regular expression successfully matching the data set in the second data set, diverting the data set based on the regular expression matched by the data set to obtain different data sets corresponding to different regular expressions, storing the data sets in the second data set according to the storage area corresponding to the regular expression to obtain the third data set; in response to the regular expression not successfully matching the data set in the second data set, naming the data set according to a preset naming rule to obtain the third data set; and storing the third data set in the second-layer cluster.
[0015] In one embodiment, the synchronization unit synchronizes the third data set to the data warehouse based on the third data set and a preset path template in the following manner: obtain the third data set in the second-layer cluster, splice the data in the third data set according to the preset path template to obtain second spliced data, wherein the second spliced data includes a database name, a table name, and a timestamp; use the database name, table name, and timestamp in the second spliced data as an index to create a data warehouse corresponding to the second spliced data; and write the third data set into the data warehouse based on the index.
[0016] In one embodiment, the initial data set includes an initial log data set and an initial stock data set, the initial log data set includes data before the data set is changed within a preset time and data after the data set is changed within the preset time, and the initial stock data set is a stock data set obtained by synchronizing the data before the preset time; the synchronization unit synchronizes the third data set with the initial data set in the following manner to obtain a synchronized target data set: obtain the last updated data in the initial log data set; create a merged data set based on the last updated data; left-connect and right-connect the initial stock data set with the merged data set to compare the initial stock data set with the merged data set; obtain data that is different between the merged data set and the initial stock data set, and store the different data in the merged data set to obtain the target data set.
[0017] In one embodiment, in response to detecting that a data set in a database has changed, the data set is synchronized based on a data synchronization tool; after obtaining the target data set, the synchronization unit is further used to: in response to the target data set being the same as the data obtained after data synchronization with the data synchronization tool, suspend the use of the data synchronization tool for data synchronization, and switch to data synchronization using the binary log file.
[0018] In one embodiment, the processing unit is also used to: in response to detecting a table structure change event, report the table structure change event or parse the table structure change event based on preset fields; in response to a data anomaly that has occurred in a current database, relocate to the location of the abnormal data and repair the data; in response to the current database being unable to use data synchronization in the form of a binary log file, switch to the data synchronization tool for data synchronization.
[0019] According to a third aspect of an embodiment of the present disclosure, an electronic device is provided, comprising: a processor; and a memory for storing processor executable instructions; wherein the processor is configured to: execute the data synchronization method described in the first aspect or any one of the implementations of the first aspect.
[0020] According to a fourth aspect of an embodiment of the present disclosure, a storage medium is provided, in which instructions are stored. When the instructions in the storage medium are executed by a processor, the processor is enabled to execute the data synchronization method described in the first aspect or any one of the embodiments of the first aspect.
[0021] The technical solution provided by the embodiments of the present disclosure may include the following beneficial effects: in response to detecting a change in a data set in a database, the changed data set is transmitted to a change data channel in the form of a binary log file. Based on the change data channel, the data set is parsed, partitioned, and regularly matched, and the data set is allocated to different clusters to achieve hierarchical design of the data set. The allocated data set is synchronized to the data warehouse, and the data set in the data warehouse is compared with the initial data set to obtain the updated data set and obtain the latest synchronized data set. Efficient data synchronization is achieved through hierarchical design of data sets.
[0022] It is to be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the present disclosure. BRIEF DESCRIPTION OF THE DRAWINGS
[0023] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present disclosure and, together with the description, serve to explain the principles of the present disclosure.
[0024] Figure 1 The present invention is a flowchart of a data synchronization method according to an exemplary embodiment.
[0025] Figure 2A The figure is a flowchart showing a method for determining a second data set according to an exemplary embodiment.
[0026] Figure 2B The diagram is a schematic diagram showing a method for determining a data set according to an exemplary embodiment.
[0027] Figure 3 The figure is a flowchart showing a method for determining a third data set according to an exemplary embodiment.
[0028] Figure 4 The figure is a flowchart showing a method for determining a data set according to an exemplary embodiment.
[0029] Figure 5The present invention is a flowchart of a method for synchronizing to a data warehouse according to an exemplary embodiment.
[0030] Figure 6 The figure is a flow chart of a method for writing into a data warehouse according to an exemplary embodiment.
[0031] Figure 7 The figure is a flowchart of a method for synchronizing a target data set according to an exemplary embodiment.
[0032] Figure 8 It is a schematic diagram showing a data synchronization method according to an exemplary embodiment.
[0033] Fig. 9 It is a schematic diagram of data according to an exemplary embodiment.
[0034] Fig.10 The figure is a schematic diagram showing appearance data according to an exemplary embodiment.
[0035] Fig.11 The diagram is a schematic diagram showing a method of creating a view according to an exemplary embodiment.
[0036] Fig.12 The diagram is a schematic diagram showing a method of creating a view according to an exemplary embodiment.
[0037] Fig.13 The figure is a schematic diagram showing a method of rewriting an original inventory table according to an exemplary embodiment.
[0038] Fig.14A The figure is a schematic diagram showing a method of rewriting an original inventory table according to an exemplary embodiment.
[0039] Fig. 14B It is a schematic diagram of a data synchronization method according to an exemplary embodiment.
[0040] Fig.15 The invention is a block diagram of a data synchronization device according to an exemplary embodiment.
[0041] Fig.16 It is a schematic diagram of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION
[0042] Here, exemplary embodiments will be described in detail, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present disclosure.
[0043] Synchronizing MySQL data to a data warehouse (Hive) is an important scenario for data synchronization. In related technologies, tools such as data synchronization tools (Apache Sqoop) are usually used for full data extraction. However, using Apache Sqoop to extract data takes a long time and puts a lot of pressure on MySQL.
[0044] In the related art, the whole process of synchronizing MySQL multi-database and multi-table to Hive and switching from Apache Sqoop to another method is not automated, and the problems of MySQL table structure change, data orderliness, Hive view restoration to MySQL, check of data readiness for the day, fault recovery and verification of the correctness of data results cannot be solved. At the same time, the reusability and scalability of the MySQL multi-database and multi-table synchronization to Hive link still need to be further improved. In view of this, the present disclosure proposes a data synchronization method, which is applied to the scenarios using binary log file (Binarylog, Binlog) subscription, Hive and change data channel (Canal) and other components.
[0045] For ease of understanding, the present disclosure explains the above components. Among them, Binlog is used for database data synchronization and change capture. Canal is a middleware for incremental subscription and consumption of MySQL database. It simulates MySQL's Binlog mechanism and parses MySQL's Binlog to achieve real-time data synchronization, subscription and consumption. Apache Kafka is a distributed stream processing platform for building real-time data pipelines and streaming applications. Apache Hive is a general data warehouse framework in big data scenarios, designed to store, query and manage large-scale data sets.
[0046] In some embodiments, the terms "data set", "Binlog data", "Binlog data", etc. can be used interchangeably.
[0047] Figure 1 FIG. 1 is a flow chart of a data synchronization method according to an exemplary embodiment. Figure 1 As shown, the method includes steps S11 to S16.
[0048] In step S11 , in response to detecting that a data set in the database is changed, the data set in the database is transmitted to a change data channel in the form of a binary log file.
[0049] In the disclosed embodiment, the subscription to Binlog is pre-set to be based on the granularity of the MySQL database, and one Canal collects data from one database. In response to detecting a change in a data set in the database, the changed data set is recorded in Binlog and transmitted to Canal, so that Canal collects data from Binlog.
[0050] In step S12, the data set is parsed based on the changed data channel to obtain a first data set in a preset format, where the preset format includes a primary key corresponding to the first data set.
[0051] In the embodiment of the present disclosure, the data of Binlog collected by Canal is parsed according to a preset format to obtain a first data set. It can be understood that the first data set is a data set obtained according to a preset format. The preset format is described in conjunction with Table 1:
[0052]
[0053]
[0054] Table 1
[0055] In step S13, the first data set is partitioned according to the primary key of each data set in the first data set to obtain a second data set, and the second data set is stored in a layer of clusters of the system cluster.
[0056] In the embodiment of the present disclosure, the second data set includes multiple partitions, and the multiple partitions include multiple data sets with the same primary key. The system cluster includes different layer clusters for storing changed data sets. It can be understood that different layer clusters have different functions and store different data sets.
[0057] In the embodiment of the present disclosure, in the first data set including the preset format, the first data set is partitioned based on the primary key of the unique identifier of the event. It can be understood that after the partitioning, the data sets with the same primary key are stored in one partition. The partitioned data set, i.e., the second data set, is stored in a layer of clusters in the system cluster, so as to facilitate the operation of the second data set directly from the layer of clusters.
[0058] In step S14, regular expression matching is performed on the second data set to obtain a third data set, and the third data set is stored in the second-layer cluster of the system cluster.
[0059] In the embodiment of the present disclosure, regular matching is used to shunt the data in the second data set in the first-layer cluster, and the data set obtained after shunting is determined as the third data set, and the third data set is stored in the second-layer cluster. It can be understood that the second-layer cluster is different from the first-layer cluster, and the data set stored in the first-layer cluster can be the first data set after being split and stored in the first-layer cluster. The second-layer cluster can be the third data set obtained after the data in the first-layer cluster is integrated together through regular matching.
[0060] In step S15, based on the third data set and the preset path template, the third data set is synchronized to the data warehouse.
[0061] In the disclosed embodiment, after completing Binlog collection, a third data set is obtained. The integrated third data set is synchronized to the data warehouse using the third data set and a pre-set path template. It is understandable that the third data set at this time is not the data set containing the last updated data.
[0062] In step S16, the third data set in the data warehouse is loaded, and the third data set is synchronized with the initial data set to obtain a synchronized target data set.
[0063] In the disclosed embodiment, the data warehouse containing the third data set is loaded so that the data warehouse can be queried. The third data set obtained by real-time updated data is compared with the initial data set, and different data are synchronized to obtain a synchronized target data set.
[0064] In the disclosed embodiment, the data set is parsed based on Canal, partitioned and regular matched, and the data set is allocated to different clusters to achieve hierarchical design of the data set. The allocated data set is synchronized to the data warehouse, and the data set in the data warehouse is compared with the initial data set to obtain the updated data set and obtain the latest synchronized data set, thus realizing the synchronization of MySQL multi-database and multi-table to the data warehouse and the automation of the synchronization process.
[0065] In the embodiment of the present disclosure, the first data set is partitioned according to the primary key of each data set in the first data set in the following manner to obtain the second data set. Figure 2A FIG. 1 is a flow chart showing a method for determining a second data set according to an exemplary embodiment. Figure 2A As shown, the method includes steps S21 to S23.
[0066] In step S21, the data including the primary key in the first data set is concatenated to obtain first concatenated data.
[0067] In the disclosed embodiment, a Canal instance collects data from a MySQL database. In order to ensure the orderliness of data writing, the schema, table and primaryId of the Binlog event message are concatenated as the index of the Kafka message, which can be understood as the key in the key-value pair. It can be understood that the data after the schema, table and primaryId is the first concatenated data.
[0068] For example, the schema and table field values in the database change message field are concatenated, and then concatenated with each value in the primary key list in the primaryKeys field in the field, and each concatenated unit is connected with a "-" character. For example, the unique primary key identifier is user-userinfo-1-23. This example is only an exemplary description, and there is no specific limitation on how to concatenate.
[0069] In step S22, a hash value of the first concatenated data is determined, and a modulus of a preset partition number is taken based on the hash value, and a value obtained by the modulus is used as a partition number.
[0070] In the disclosed embodiment, a hash value of the first spliced data is determined, and a hash calculation is performed, and a value obtained by taking the hash value modulo the preset number of partitions is used as a partition number, wherein the partition number is used to determine a target partition corresponding to the first spliced data.
[0071] In step S23, the first spliced data is stored in a storage area corresponding to the partition number, and a second data set stored based on the partition number is obtained.
[0072] In the disclosed embodiment, based on the partition number corresponding to the first spliced data, the first spliced data is stored in the storage area corresponding to the partition number. The data sets in the first data set are all distributed to different partitions based on the above calculation, and the second data sets are stored in different partitions in the first-layer cluster after being distributed based on the primary key. It can be understood that the first data set can be the same as the second data set, only the storage method is different. Exemplarily, for the database data collection scenario, the collected database data needs to restore the database change message in the heterogeneous data synchronization and real-time data warehouse scenarios of the database data. In order to ensure that the synchronized data is consistent with the original database data, the collection of database data needs to ensure that the data is orderly in the overall read and write link. Therefore, by collecting database data, writing to the first-level kafka Topic (first-layer cluster), full synchronization of data, second-level Kafka Topic (second-layer cluster), data diversion, splitting of diversion, merging of data, and writing to the third-level Kafka Topic are all orderly, which can meet the use of downstream orderly scenarios while also taking into account read and write efficiency.
[0073] Exemplarily, a unique primary key identifier is generated, a hash value is calculated, and a modulo operation is performed with the preset number of partitions, and the calculated partition number is written as the partition number of the target KafkaTopic as a message. This method can write data with the same unique primary key identifier into the same KafkaTopic partition. That is, data with the same primary key value in the same library table will be divided into the same partition. On the reading side, the program reads in the order of the KafkaTopic partitions and writes the data with the same unique primary key identifier in the previous step into the same partition in order, so the read data can also be guaranteed to be ordered by the unique primary key identifier.
[0074] Exemplary, combined Figure 2B The method of determining the data set is described. Figure 2B FIG. 1 is a schematic diagram showing a method for determining a data set according to an exemplary embodiment. Figure 2B As shown in the figure, it includes id, data before change (before-value), data after change (after-value), event type, database name and table name. Figure 2BAs shown in the figure, the database user, table userinfo generates 5 database messages, where the id field is the primary key. id=4, the corresponding partition number is hash(user-userinfo-4)%3=1, so it is assigned to partition 1 (partition1). id=1, the corresponding partition number is hash(user-userinfo-1)%3=1, so it is assigned to partition1. id=2, the corresponding partition number is hash(user-userinfo-2)%3=2, so it is assigned to partition 2 (partition2). id=1, the corresponding partition number is hash(user-userinfo-1)%3=1, so it is assigned to partition1. id=5, the corresponding partition number is hash(user-userinfo-5)%3=2, so it is assigned to partition2. Take the two database change messages generated by id=1, the second and fourth messages, as examples. The unique message identifier of message 2 concatenated with the unique primary key identifier is user-userinfo-1. The hash operation modulo is hash(user-userinfo-1)%3=1, and the message is written to the KafkaTopic partition partition1. The unique message identifier of message 4 concatenated with the unique primary key identifier is user-userinfo-1. The hash operation modulo is hash(user-userinfo-1)%3=1, and the message is written to the KafkaTopic partition of partition1. Finally, the two messages with id=1 are written to partition 1 in order. Thus, data order is achieved.
[0075] In the disclosed embodiment, the partition number corresponding to the current data set is determined based on the first spliced data and stored in the storage area corresponding to the partition number, so that the data with the same primary key under the same library table will be allocated to the same partition, thereby ensuring that the data sets are stored in the order of the primary keys, and realizing the conversion of the first data set into the second data set based on the primary key ordered strategy and writing it into a layer of cluster.
[0076] In the disclosed embodiment, the second data set is split according to regular matching to obtain a third data set. Figure 3 FIG. 1 is a flow chart showing a method for determining a third data set according to an exemplary embodiment. Figure 3 As shown, the method includes steps S31 to S32b.
[0077] In step S31, the second data set is matched based on the regular expression in the regular matching.
[0078] In the embodiment of the present disclosure, in order to support the scenario of sub-library and sub-table and improve the use efficiency, reusability and reading performance of data, the second data set is matched based on regular matching, wherein the regular matching includes regular expressions, so as to realize the data sets in the second data set to be divided according to different regular expressions.
[0079] In step S32a, in response to the regular expression successfully matching the data set in the second data set, the data set is split based on the regular expression matched by the data set.
[0080] In the disclosed embodiment, the regular expression matches the library table field corresponding to the data set in the second data set. If the match is successful, the diversion task can identify the data set that needs to be aggregated, and write the Binlog data of the matched library table field into the second-layer cluster of the "target Topic name". The target Topic name is obtained by presetting. The second-layer cluster can be a secondary Topic.
[0081] In step S32b, in response to the regular expression not successfully matching the data set in the second data set, the data set is named according to a preset naming rule.
[0082] In the embodiment of the present disclosure, if the regular expression does not match the data set in the second data set, it can be understood that the Binlog data is not matched by the regular expression, and the default rule is used for naming. Among them, the default rule can be that the Binlog data of a table is written to the same Topic, and the Topic name is named "binlog_database name_table name".
[0083] Exemplary, combined Figure 4 Determining the second data set and determining the third data set are described. Figure 4 FIG. 1 is a flow chart showing a method for determining a data set according to an exemplary embodiment. Figure 4 As shown, MySQL includes use.user_info1, use.user_info2, and db1.table1. After Canal collection, the above Binlog data is partitioned based on the hash value of each table, and user.user_info1 and user.user_info2 are written to the first-level Topic of "binlog_db1". You can assign db1.table1 to binlog_user. At this time, read the data in the Topic and assign the data in the first-level Topic based on regular matching. If the regular expression in the regular matching is a table ending with user, db1.user1 and db1.user2 can be aggregated into the second-level Topic of the same binlog_user_info. Figure 4The binlog_db1_table2 in the table can be the table name obtained based on the default rule.
[0084] For example, Figure 4 Take the rule name of the table in the right half as an example. In the user database, the regular expression is user.user_info*. If a table related to user.user_info* is matched in the first-level Topic, it is input into binlog_user_info in the second-level Topic. If there is a table in the first-level Topic that does not match user.user_info*, the above table is named "binlog_table name" according to the default rule and stored as one table.
[0085] In the embodiment of the present disclosure, both the data set that successfully matches the regular expression and the data set that fails to match the regular expression are stored in the second-layer cluster. The data set stored in the second-layer cluster is determined as the third data set. It can be understood that the third data set has the same situation as the second data set, but the difference is that it is stored in different clusters in different storage methods.
[0086] In the disclosed embodiment, the design of the first-tier cluster and the second-tier cluster realizes orderly data storage, meets the needs of library and table subscription and supports different scenarios. It implements a data diversion strategy based on regular matching, provides support for MySQL library and table sharding scenarios, and improves the efficiency, performance and data reusability of Binlog data subscription.
[0087] In the disclosed embodiment, the third data set is synchronized to the data warehouse based on the third data set and the preset path template. Figure 5 FIG. 1 is a flow chart showing a method for synchronizing to a data warehouse according to an exemplary embodiment. Figure 5 As shown, the method includes steps S41 to S43.
[0088] In step S41, a third data set in the second-layer cluster is obtained, and the data in the third data set is spliced according to a preset path template to obtain second spliced data.
[0089] In the disclosed embodiment, Kafka data is written to HDFS in real time through a big data component (Flink). Through the Flink interface (BucketAssigner), the data set is assigned to different files or directories. Among them, it can be implemented by means of a preset template path. The preset template path can be a path set by the developer based on demand. Exemplarily, the es, schema, and table fields in the Binlog data are parsed and processed to obtain a preset template path. That is, the second spliced data includes the database name, table name, and timestamp. That is, es, schema, and table fields, for example: ${base_hive_dir} / dt=${dt} / database=${schema} / table=${table}. Wherein ${dt} is the value of the timestamp field value of the Binlog data formatted according to the time string of 'yymmdd'. ${schema}, schema field value of Binlog data; table is the table field value of Binlog data. ${base_hive_dir} is a fixed value / user / hive / warehouse / original_binlog.
[0090] In some embodiments and drawings, “ / user / hive / warehouse / original_binlog” and “binlog” in “original_binlog” are in the storage path, and “b” is lowercase.
[0091] In step S42, the database name, table name and timestamp in the second spliced data are used as indexes to create a data warehouse corresponding to the second spliced data.
[0092] In the disclosed embodiment, a data storage path of the data warehouse external table (original_binlog) is created, and its data storage path points to / user / hive / warehouse / original_binlog, and also points to dt, database, and table as the partition fields of the Binlog table original_binlog, so as to complete the writing from Kafka to Hive.
[0093] In step S43, the third data set is written into the data warehouse based on the index.
[0094] Exemplary, combined Figure 6 Describes the method of writing into the data warehouse. Figure 6 FIG. 1 is a flow chart showing a method for writing into a data warehouse according to an exemplary embodiment. Figure 6As shown, the outer table points to / user / hive / warehouse. If there is a database named user, the corresponding Binlog data is stored in the Hive table of original_binlog. The Binlog of each MySQL table is organized into a partition of the Hive table of original_binlog. For example, the Binlog data of the userinfo table under the MySQLuser database is stored in the datatable_name=user, table=user_info partition of the Hive table original_binlog. Under each table_name secondary partition, the third-level partition is organized by dt, for example, dt=20240710, dt=20240711, dt=20240712. Figure 6 The part-xx-inprogress file is the data that is still being processed, and the part-xx file is the data that has been processed.
[0095] In the disclosed embodiments, the reusability and scalability of synchronizing multiple databases and tables from MySQL to Hive are improved to support more usage scenarios.
[0096] In the embodiment of the present disclosure, after the third data set is synchronized to the data warehouse, it is also necessary to compare the third data set with the initial data set to store the latest data in the data warehouse.
[0097] In the disclosed embodiment, before storing the latest data in the data warehouse, it is also necessary to check whether the Binlog data generated on the day is ready. Among them, the Binlog ready task detection logic is whether there is data for the next day in the Binlog data. Since the writing process from the first-layer cluster, the second-layer cluster and synchronization to the data warehouse is orderly, if the Binlog data of the next day appears in the HDFS path corresponding to a certain table of data, it means that the maximum timestamp of the Binlog of a certain table has exceeded 0 o'clock. Based on the fact that there are no files with the suffix part-xx-inprogress in the HDFS directory of the Binlog data of the day, and the Binlog data of the next day appears in the HDFS path, it is considered that all the data of the day are written into HDFS, and the data ready task has completed the detection.
[0098] For example, based on the HDFS path of writing original_binlog to the Hive table in Kafka is / user / hive / ware house / original_binlog / database=user / table=user_info / dt=20240710, the path of writing the Binlog data of the user_info table in MySQL to HDFS on the same day is / user / hive / warehouse / original_binlog / database=user / table=user_info / dt=20240711. The Binlog data of the user_info table in the user library of MySQL on that day has been written to HDFS, and the Binlog data can be updated.
[0099] Figure 7 FIG. 1 is a flow chart showing a method for synchronizing a target data set according to an exemplary embodiment. Figure 7 As shown, the method includes steps S51 to S54.
[0100] In step S51, the last updated data in the initial log data set is obtained.
[0101] In the embodiment of the present disclosure, the initial log data set includes data before the data set changes within a preset time and data after the data set changes within a preset time. In conjunction with Table 2, the acquisition of the last updated data based on the initial log data set is described. The initial log data set can be stored in the form of a table or in other ways.
[0102] Identification (id) Data before change Changed data Event Type 4 400 insert 1 100 110 renew 2 200 210 renew 1 110 120 renew 5 500 delete
[0103] Table 2
[0104] Exemplarily, Table 2 is the initial log data set. The logic of sorting is to take the first entry of the same primary key Binlog data in reverse order of logFileName, logFileOffset, and execute_time. When id=4, there is no data before the change. Through the insert event, the changed data 400 is obtained. When id=1, the data before the change is 100. Based on the update event, the changed data 110 is obtained. When id=2, the data before the change is 200. Based on the update event, the changed data 210 is obtained. When id=1, the data before the change is 110. Based on the update event, the changed data 120 is obtained. When id=5, the data before the change is 500. Based on the delete event, the changed data is empty.
[0105] In step S52, a merged data set is created based on the last updated data.
[0106] In the disclosed embodiment, based on the last updated data in the initial log data set, a merged data set is created for the last updated data. The merged data set is illustrated by combining Table 3 and Table 2. Among them, the Binlog data generated on the day is made into an initial log data set based on the primary key. When a piece of data changes multiple times in one day, only the data after the last change is stored in the merged data set.
[0107]
[0108]
[0109] Table 3
[0110] For example, when id=1 in Table 2, the data after the last change is 120, and the event type is update, so the above data is stored in a merged data center. When id=2 in Table 2, the data after the last change is 210, and the event type is update, so the above data is stored in a merged data center. When id=4 in Table 2, the data after the last change is 400, and the event type is insert, so the above data is stored in a merged data center. When id=5 in Table 2, the data after the last change is 500, and the event type is delete, so the above data is stored in a merged data center. A merged data set is obtained based on the above data.
[0111] In step S53, the initial stock data set is left-joined and right-joined with the merged data set to compare the initial stock data set with the merged data set.
[0112] In the disclosed embodiment, the merged data set is left-joined and right-joined with the initial stock data set based on the primary key of the Binlog data. The initial stock data set is the stock data set obtained after synchronizing the data before the preset time. It is understandable that after the data synchronization of the first day is completed, the merged data set is updated to the initial stock data set of the second day.
[0113] In step S54, the data that is different from the merged data set and the initial stock data set is obtained, and the different data is stored in the merged data set to obtain the target data set.
[0114] In the embodiment of the present disclosure, if the same piece of data appears in both the initial stock data set and the merged data set, it means that the data has been updated, and the data of the merged data set is selected as the final result. Exemplarily, the initial stock data set can be right-connected with the merged data set to obtain the final update result of the merged data set. Otherwise, it means that no changes have occurred, and the data in the original initial stock data set is retained as the final result. Exemplarily, the initial stock data set can be left-connected with the merged data set to retain the data that has not changed. Through the above operations, the target data set is obtained.
[0115] Exemplarily, the synchronized target data set is described in conjunction with Tables 4 to 6.
[0116] id data Logo data Event Type 1 100 1 120 renew 2 200 2 210 renew 3 300 null null null 5 500 5 delete
[0117] Table 4
[0118] id data Logo data Event Type 1 100 1 120 renew 2 200 2 210 renew 5 500 5 delete null null 4 400 insert
[0119] Table 5
[0120] id data 3 300 1 120 2 210
[0121] Table 6
[0122] For example, in Tables 4 and 5, the data before the empty column is the initial stock data set, and the data after the empty column is the merged data set. Based on Table 4, it can be seen that when id=3, the value corresponding to id=3 has not changed in both the initial stock data set and the merged data set, so based on the left connection record, the corresponding value when id=3 is 300, that is, in Table 6, id=3, the data is 300. Based on Table 5, it can be seen that when id=1 and id=2, the data corresponding to the initial stock data set or the merged data set are different. Based on the right connection method, the latest data corresponding to id=1 and id=2 are obtained and stored in Table 6, that is, in Table 6, when id=1, the data is 120, and when id=2, the data is 210.
[0123] In the disclosed embodiment, the target data set obtained above is used as the last updated data set and stored in the data warehouse. The initial stock data set on the second day is replaced by the merged data set after the first synchronization on the second day, ensuring the accuracy of data synchronization. This achieves data synchronization that takes less time.
[0124] In the disclosed embodiment, in response to detecting a change in a data set in a database, in addition to data synchronization through Binlog subscription, data synchronization is also performed based on a data synchronization tool (Apache Sqoop). In response to the target data set being the same as the data obtained after data synchronization based on Apache Sqoop, data synchronization using the data synchronization tool is suspended, and data synchronization is switched to binary log files.
[0125] Exemplarily, the description is given in conjunction with Tables 7 to 9.
[0126]
[0127] Table 7
[0128] id data Update time 1 100 2024-7-10 02:03:06 2 200 2024-7-10 02:03:06 3 300 2024-7-10 02:03:06
[0129] Table 8
[0130] id data Update time 1 100 2024-7-10 02:03:06 2 200 2024-7-10 02:03:06 3 700 2024-7-11 00:01:06
[0131] Table 9
[0132] Exemplarily, if the event change in Table 7 occurs, the target data set obtained based on Binlog subscription is obtained. And the data set obtained based on Apache Sqoop is obtained for comparison. Based on Tables 8 and 9, it can be seen that when id=3, the corresponding data and update time are different, and the data update occurs after 0 o'clock the next day. It is necessary to remove the data corresponding to id=3 in Tables 8 and 9 to avoid verification anomalies due to the data content and number of data in the two tables. After removing the interference factors as mentioned above, compare the number of data in the two tables, and check the two tables by using JOIN to determine whether the data field values are consistent. As can be seen from Tables 8 and 9, the corresponding data when id=1 and id=2 are consistent, then the Apache Sqoop data synchronization can be switched to data synchronization based on Binlog subscription.
[0133] In the disclosed embodiment, based on the simultaneous execution of the Apache Sqoop full synchronization and the Binlog incremental synchronization processes and the verification of the result data after removing interference factors, an efficient and accurate verification process is completed. After the data verification is passed, the Apache Sqoop full synchronization process is migrated to the Binlog incremental synchronization process, and finally the launch is completed. The entire synchronization process steps are connected in series through scheduling and systematization, which improves the overall data processing efficiency and reduces the labor cost through automation.
[0134] In the disclosed embodiment, in response to detecting a table structure change event, the table structure change event is reported or parsed based on a preset field. For example, through monitoring alarm strategies, if a table structure change event is detected, a relevant notification is sent to a human to implement manual intervention. Alternatively, based on the isDdl and sql fields of the Binlog data, the sql statement is parsed and executed on the target Hive table to perform the table structure change, which is automatically performed without manual intervention.
[0135] In the disclosed embodiment, in response to data anomalies that have changed in the current database, the data is relocated to the location of the abnormal data and the data is repaired. For example, if a processing anomaly occurs in the data processing link, the data needs to be repaired. Use the "show slave status\G;" or "show master status\G;" command to find the GTID of a relatively early Binlog data from the time period when the problem occurred, reconfigure the canal.instance.master.gtid field of the Canal, replace the new GTID, restart the service, and then you can trace back the data from the Binlog at the GTID position to complete the data repair.
[0136] In the embodiment of the present disclosure, in response to the current database being unable to use the binary log file for data synchronization, the data synchronization tool is switched to perform data synchronization. For example, by configuring the incremental field and incremental condition, such as configuring the incremental field as the update creation time ctime or update time utime field of the MySQL table, ds is the current execution time minus one day, so that the incremental changes of today's data update and data insertion can be obtained through batch query through the condition of ctime>='{{ds}}' or utime>='{{ds}}'.
[0137] In the present disclosure, in combination Figure 8 The data synchronization method is described. Figure 8 FIG. 1 is a schematic diagram showing a data synchronization method according to an exemplary embodiment. Figure 8 As shown, Figure 8 It includes the normal process of Binlog incremental synchronization with MySQL and the fault recovery process of Binlog incremental synchronization with MySQL.
[0138] In the embodiment of the present disclosure, the conventional process of incremental synchronization of Binlog with MySQL includes steps 1 to 9. Step 1 includes configuring Canal to collect Binlog. In step 1, the parsed Binlog data can be Fig. 9 The data included in the code is shown. Fig. 9is a schematic diagram of data according to an exemplary embodiment. Step 2 includes writing and Kafka topic. Step 3 includes data diversion. Step 4 includes synchronizing Kafka's Binlog data to hive. In step 4, create a Hive external representation such as Fig.10 As shown, Fig.10 is a schematic diagram of a table data according to an exemplary embodiment. Step 5 includes determining whether the current Binlog change data is ready. Step 6, i.e., the sixth step, includes adding a partition. Step 7, i.e., the seventh step, includes creating a merge view. The creation of the merge view in step 7 can be performed by Fig.11 Create it as shown. Fig.11 is a schematic diagram showing a method of creating a view according to an exemplary embodiment. Step 8 is the eighth step, restoring the latest data based on the merge view and the stock table. In step 8, Fig.12 The code shown is used to restore the data to obtain the target data set, where Fig.12 The figure is a schematic diagram showing a method of creating a view according to an exemplary embodiment. Step nine, namely the ninth step, includes inserting an overwrite into the original inventory table. Fig.13 FIG. 1 is a schematic diagram showing a method of rewriting an original inventory table according to an exemplary embodiment. Fig.13 As shown, in the Hive table original_binlog of Binlog data and the partition database=user, table=user_info, dt=20240710, the partition data is ready and the data is restored. Based on the stock table origindb.user_user_info and the user_user_info_merge_view in the merge view, and based on the query condition dt=20240710, the data is restored to obtain a new stock table origindb.user_user_info, and the original stock table is overwritten based on the new stock table. In the disclosed embodiment, the stock table can be the initial stock data set, and the merge view can be the merged data set.
[0139] In the disclosed embodiment, the fault recovery process of Binlog incremental synchronization of MySQL includes steps 10 to 12. Step 10 includes table structure change processing, step 11 includes data repair, and step 12 includes one-key switching of data synchronization mode.
[0140] In the disclosed embodiment, Fig.14A FIG. 1 is a schematic diagram showing a method of rewriting an original inventory table according to an exemplary embodiment. Fig.14AAs shown in the figure, the original process is to extract the full amount of data in MySQL through Apache Sqoop to obtain the Hive inventory table. In this disclosure, before the Binlog incremental extraction is launched, the dual-write test process is used to determine whether Binlog incremental extraction can be used. In the dual-write test process, Apache Sqoop and Binlog incremental extraction are executed for MySQL at the same time (corresponding to Figure 8 Steps 1 to 9 in the above steps), change the target table of Insert OverWrite in step 9 to the temporary table tmp, and synchronize it to the Hive stock temporary table. Judge the Hive stock table and Hive stock temporary table obtained based on Apache Sqoop. If the data is inconsistent, find out the cause and re-verify. If the data in the two tables are consistent, the Binlog incremental extraction process is launched. Based on the Binlog incremental extraction, the Apache Sqoop extraction process is taken out. For MySQL, the Hive stock table containing the target data set is obtained through Binlog incremental extraction.
[0141] In the disclosed embodiment, Fig. 14B FIG. 1 is a schematic diagram showing a data synchronization method according to an exemplary embodiment. Fig. 14B As shown, the related art usually adopts Fig. 14B The process on the left directly links to the database through open source stream processing framework (Flink ChangeData Capture, Flink CDC) data synchronization and other tools, reads database change data in real time, and writes to other databases. If there are many consumers, database resources will be consumed, which will put pressure on the database, and each consumer will need to go through the consumption process again. In this disclosure, the data in the database is collected by the CDC tool and stored in the Kafka cluster, and the data is split and merged through the data synchronization program and written to the lower-level Kafka cluster. Other databases and other usage scenarios can be synchronized, so that other consumers can consume data repeatedly, improve data reusability and data processing efficiency, and reduce database pressure. At the same time, the orderliness of the overall link of the database change message based on the primary key meets the needs of more data usage scenarios more flexibly, such as support for database sub-library and sub-table.
[0142] In some embodiments and drawings, MySQL and MYSQL can be replaced with each other.
[0143] In the disclosed embodiment, key methods for synchronizing multiple MySQL databases and tables to Hive tables are implemented, such as data orderliness, Hive view restoration of MySQL, and checking the readiness of the data for the day, and finally the synchronization process is realized. The design of the first-level Kafkatopic and the second-level Kafka topic and the orderliness of the data meet the Binlog subscription requirements for the database and table granularity and support for different scenarios. A data diversion strategy based on rule matching is implemented, which provides support for MySQL sub-library and sub-table scenarios, and also improves the efficiency, performance and data reusability of Binlog data subscriptions. The processing process is simplified, and the readability is greatly improved.
[0144] In the disclosed embodiment, a data verification method for synchronizing MySQL multi-database and multi-table to Hive table is implemented. The Apache Sqoop full synchronization and Binlog incremental synchronization processes are performed simultaneously through a double-write method, and the result data is verified after removing interference factors, completing an efficient and accurate verification process. After the data verification is passed, the Apache Sqoop full synchronization process is migrated to the Binlog incremental synchronization process, and finally the launch is completed. The entire synchronization process steps are connected in series through scheduling and systematization, which improves the overall data processing efficiency and reduces the labor cost through automation. In the long-term practice of data synchronization, some abnormal situations encountered are summarized and precipitated, and a method for data repair and fault rapid recovery based on MySQL GTID backtracking data and one-click switching of synchronization methods is designed, which greatly improves the availability of data. In the case of changes in the MySQL table structure, it is also possible to quickly discover and automatically process compatibility with changes in the synchronization process table based on multiple strategies, further reducing the situation of manual intervention, improving efficiency and reducing labor costs. Finally, through a long-term data comparison after the launch, the time of using the Binlog incremental synchronization method is faster than the overall synchronization time of the Apache Sqoop full synchronization, reducing the pressure of the data synchronization process on MySQL.
[0145] Based on the same concept, the embodiment of the present disclosure also provides a data synchronization device 100 .
[0146] It is understandable that the data synchronization device 100 provided in the embodiment of the present disclosure includes hardware structures and / or software modules corresponding to the execution of each function in order to realize the above functions. In combination with the units and algorithm steps of each example disclosed in the embodiment of the present disclosure, the embodiment of the present disclosure can be implemented in the form of hardware or a combination of hardware and computer software. Whether a function is executed in the form of hardware or computer software driving hardware depends on the specific application and design constraints of the technical solution. Those skilled in the art may use different methods to implement the described functions for each specific application, but such implementation should not be considered to exceed the scope of the technical solution of the embodiment of the present disclosure.
[0147] Fig.15 1 is a block diagram of a data synchronization device 100 according to an exemplary embodiment. Fig.15 The device includes a transmission unit 101, a processing unit 102, a determination unit 103 and a synchronization unit 104.
[0148] The transmission unit 101 is configured to transmit the data set in the database to a change data channel in the form of a binary log file in response to detecting that a data set in the database has been changed.
[0149] The processing unit 102 is configured to parse the data set based on the changed data channel to obtain a first data set in a preset format, where the preset format includes a primary key corresponding to the first data set.
[0150] The determination unit 103 partitions the first data set according to the primary key of each data set in the first data set to obtain a second data set, and stores the second data set in a layer cluster of the system cluster, wherein the second data set includes multiple partitions, and the multiple partitions include multiple data sets with the same primary key. The system cluster includes different layers of clusters for storing changed data sets, wherein different layers of clusters have different corresponding functions; performs regular matching on the second data set to obtain a third data set, and stores the third data set in a second layer cluster of the system cluster, wherein the regular matching is used to divert the data in the second data set, and the second layer cluster is used to store the second data set after the regular matching.
[0151] The synchronization unit 104 synchronizes the third data set to the data warehouse based on the third data set and the preset path template; loads the third data set in the data warehouse, synchronizes the third data set with the initial data set, and obtains a synchronized target data set.
[0152] In one implementation, the determination unit 103 partitions the first data set according to the primary key of each data set in the first data set to obtain a second data set in the following manner: splicing the data including the primary key in the first data set to obtain first spliced data; determining a hash value of the first spliced data, and taking a modulus of a preset number of partitions based on the hash value, and using the modulo value as a partition number; storing the first spliced data in a storage area corresponding to the partition number to obtain a second data set stored based on the partition number.
[0153] In one embodiment, the determination unit 103 performs regular matching on the second data set to obtain a third data set, and stores the third data set in the second-layer cluster of the system cluster in the following manner: matching the second data set based on a regular expression in the regular matching; in response to a successful match between the regular expression and a data set in the second data set, diverting the data set based on the regular expression matched by the data set to obtain different data sets corresponding to different regular expressions, storing the data sets in the second data set according to a storage area corresponding to the regular expression to obtain a third data set; in response to an unsuccessful match between the regular expression and the data set in the second data set, naming the data set according to a preset naming rule to obtain a third data set; and storing the third data set in the second-layer cluster.
[0154] In one embodiment, the synchronization unit 104 synchronizes the third data set to the data warehouse based on the third data set and a preset path template in the following manner: obtain the third data set in the second-layer cluster, splice the data in the third data set according to the preset path template to obtain second spliced data, and the second spliced data includes a database name, a table name, and a timestamp; use the database name, table name, and timestamp in the second spliced data as an index to create a data warehouse corresponding to the second spliced data; and write the third data set into the data warehouse based on the index.
[0155] In one embodiment, the initial data set includes an initial log data set and an initial stock data set. The initial log data set includes data before the data set is changed within a preset time and data after the data set is changed within the preset time. The initial stock data set is a stock data set obtained by synchronizing the data before the preset time. The synchronization unit 104 synchronizes the third data set with the initial data set in the following manner to obtain a synchronized target data set: obtain the last updated data in the initial log data set; create a merged data set based on the last updated data; left-connect and right-connect the initial stock data set with the merged data set to compare the initial stock data set with the merged data set; obtain data that is different between the merged data set and the initial stock data set, and store the different data in the merged data set to obtain the target data set.
[0156] In one embodiment, in response to detecting that a data set in a database has changed, the data set is synchronized based on a data synchronization tool; after obtaining the target data set, the synchronization unit 104 is also used for: in response to the target data set being the same as the data obtained after data synchronization with the data synchronization tool, suspending the use of the data synchronization tool for data synchronization and switching to data synchronization using a binary log file.
[0157] In one embodiment, the processing unit 102 is also used to: in response to detecting a table structure change event, report the table structure change event or parse the table structure change event based on preset fields; in response to a data anomaly that has occurred in a change in the current database, relocate to the location of the abnormal data and repair the data; in response to the current database being unable to use data synchronization in the form of a binary log file, switch to a data synchronization tool for data synchronization.
[0158] Regarding the device in the above embodiment, the specific manner in which each module performs operations has been described in detail in the embodiment of the method, and will not be elaborated here.
[0159] like Fig.16 As shown, Fig.16 : is a schematic diagram of an electronic device provided by an embodiment of the present invention. One embodiment of the present invention provides an electronic device 200. Among them, the electronic device 200 includes a memory 201, a processor 202, and an input / output (I / O) interface 203. Among them, the memory 201 is used to store instructions. The processor 202 is used to call the instructions stored in the memory 201 to execute the file merging method of the embodiment of the present invention. Among them, the processor 202 is connected to the memory 201 and the I / O interface 203 respectively, for example, it can be connected through a bus system and / or other forms of connection mechanisms (not shown). The memory 201 can be used to store programs and data, including the program of the file merging method involved in the embodiment of the present invention, and the processor 202 executes various functional applications and data processing of the electronic device 200 by running the program stored in the memory 201.
[0160] In the embodiment of the present invention, the processor 202 can be implemented in at least one hardware form of a digital signal processor (Digital Signal Processing, DSP), a field programmable gate array (Field-Programmable Gate Array, FPGA), and a programmable logic array (Programmable Logic Array, PLA). The processor 202 can be a central processing unit (Central Processing Unit, CPU) or one or a combination of other forms of processing units with data processing capabilities and / or instruction execution capabilities.
[0161] The memory 201 in the embodiment of the present invention may include one or more computer program products, and the computer program product may include various forms of computer-readable storage media, such as volatile memory and / or non-volatile memory. The volatile memory may include, for example, random access memory (RAM) and / or cache memory. The non-volatile memory may include, for example, read-only memory (ROM), flash memory, hard disk drive (HDD) or solid-state drive (SSD).
[0162] In the embodiment of the present invention, the I / O interface 203 may be used to receive input instructions (such as digital or character information, and key signal input related to user settings and function control of the electronic device 200), and may also output various information to the outside (such as images or sounds). In the embodiment of the present invention, the I / O interface 203 may include one or more of a physical keyboard, function keys (such as volume control keys, switch keys, etc.), a mouse, a joystick, a trackball, a microphone, a speaker, and a touch panel.
[0163] It is to be understood that, although the operations are described in a specific order in the drawings in the embodiments of the present invention, it should not be understood as requiring to perform these operations in the specific order shown or in a serial order, or requiring to perform all the operations shown to obtain the desired results. In certain environments, multitasking and parallel processing may be advantageous.
[0164] The methods and devices involved in the embodiments of the present invention can be implemented using standard programming techniques, using rule-based logic or other logic to implement various method steps. It should also be noted that the words "device" and "module" used herein and in the claims are intended to include implementations using one or more lines of software code and / or hardware implementations and / or devices for receiving input.
[0165] Any steps, operations or procedures described herein may be performed or implemented using one or more hardware or software modules, either alone or in combination with other devices. In one embodiment, the software module is implemented using a computer program product including a computer-readable medium containing computer program code, which can be executed by a computer processor to perform any or all of the described steps, operations or procedures.
[0166] The foregoing description of the implementation of the present invention has been given for purposes of illustration and description. The foregoing description is not intended to be exhaustive nor to limit the invention to the exact form disclosed, and various variations and modifications are possible in accordance with the above teachings or may be obtained from the practice of the invention. These embodiments are selected and described in order to illustrate the principles of the invention and its practical application, so that those skilled in the art can utilize the invention in various embodiments and with various modifications suitable for the particular use contemplated.
Claims
1. A data synchronization method, It is characterized in that include: In response to detecting that a data set in a database has been changed, transmitting the data set in the database to a change data channel in the form of a binary log file; Parsing the data set based on the changed data channel to obtain a first data set in a preset format, wherein the preset format includes a primary key corresponding to the first data set; Partitioning the first data set according to the primary key of each data set in the first data set to obtain a second data set, and storing the second data set in a layer of clusters of a system cluster, wherein the second data set includes multiple partitions, wherein the multiple partitions include multiple data sets with the same primary key, and the system cluster includes different layers of clusters for storing changed data sets, wherein different layers of clusters have different corresponding functions; Perform regular matching on the second data set to obtain a third data set, and store the third data set in a second-layer cluster of the system cluster, wherein the regular matching is used to shunt the data in the second data set, and the second-layer cluster is used to store the second data set after regular matching; Based on the third data set and the preset path template, synchronizing the third data set to the data warehouse; A third data set in the data warehouse is loaded, and the third data set is synchronized with the initial data set to obtain a synchronized target data set.
2. The method according to claim 1, It is characterized in that Partitioning the first data set according to the primary key of each data set in the first data set to obtain a second data set includes: splicing the data including the primary key in the first data set to obtain first spliced data; Determine a hash value of the first spliced data, and take a modulus of a preset partition number based on the hash value, and use a value obtained by taking the modulus as a partition number; The first spliced data is stored in a storage area corresponding to the partition number to obtain a second data set stored based on the partition number.
3. The method according to claim 1, It is characterized in that The performing regular expression matching on the second data set to obtain a third data set, and storing the third data set in the second-layer cluster of the system cluster, includes: Matching the second data set based on the regular expression in the regular matching; In response to the regular expression successfully matching the data set in the second data set, the data set is split based on the regular expression matched by the data set to obtain different data sets corresponding to different regular expressions, and the data sets in the second data set are stored in the storage area corresponding to the regular expression to obtain a third data set; In response to the regular expression not successfully matching the data set in the second data set, naming the data set according to a preset naming rule to obtain a third data set; The third data set is stored in the second-layer cluster.
4. The method according to claim 1, It is characterized in that The step of synchronizing the third data set to a data warehouse based on the third data set and a preset path template includes: Acquire a third data set in the second-layer cluster, and splice the data in the third data set according to a preset path template to obtain second spliced data, where the second spliced data includes a database name, a table name, and a timestamp; Using the database name, table name and timestamp in the second spliced data as indexes, creating a data warehouse corresponding to the second spliced data; The third data set is written into the data warehouse based on the index.
5. The method according to claim 1, It is characterized in that The initial data set includes an initial log data set and an initial stock data set. The initial log data set includes data before the data set is changed within a preset time and data after the data set is changed within a preset time. The initial stock data set is a stock data set obtained by synchronizing the data before the preset time. The step of synchronizing the third data set with the initial data set to obtain a synchronized target data set includes: Obtain the last updated data in the initial log data set; Creating a merged data set based on the last updated data; The initial stock data set is left-connected and right-connected with the merged data set to compare the initial stock data set with the merged data set; different data between the merged data set and the initial stock data set are obtained, and the different data are stored in the merged data set to obtain a target data set.
6. The method according to claim 1, It is characterized in that In response to detecting that a data set in the database has been changed, the data set is synchronized based on a data synchronization tool; After obtaining the target data set, the method further includes: In response to the target data set being identical to data obtained after data synchronization using the data synchronization tool, suspending data synchronization using the data synchronization tool and switching to data synchronization using the binary log file.
7. The method according to claim 1 or 6, It is characterized in that The method further comprises: In response to detecting a table structure change event, reporting the table structure change event or parsing the table structure change event based on preset fields; In response to abnormal data changes in the current database, relocate to the location of the abnormal data and repair the data; In response to the current database being unable to use the binary log file for data synchronization, switching to the data synchronization tool for data synchronization.
8. A data synchronization device, It is characterized in that include: a transmission unit, configured to transmit the data set in the database to a change data channel in the form of a binary log file in response to detecting that a data set in the database has changed; A processing unit, configured to parse the data set based on the changed data channel to obtain a first data set in a preset format, wherein the preset format includes a primary key corresponding to the first data set; The determination unit partitions the first data set according to the primary key of each data set in the first data set to obtain a second data set, and stores the second data set in a layer cluster of a system cluster, wherein the second data set includes multiple partitions, wherein the multiple partitions include multiple data sets with the same primary key, and the system cluster includes different layers of clusters for storing changed data sets, wherein different layers of clusters have different corresponding functions; performs regular matching on the second data set to obtain a third data set, and stores the third data set in a layer cluster of the system cluster, wherein the regular matching is used to divert data in the second data set, and the layer cluster is used to store the second data set after regular matching; The synchronization unit synchronizes the third data set to the data warehouse based on the third data set and the preset path template; loads the third data set in the data warehouse, synchronizes the third data set with the initial data set, and obtains a synchronized target data set.
9. An electronic device, It is characterized in that include: processor: a memory for storing processor-executable instructions; Wherein, the processor is configured to: execute the data synchronization method described in any one of claims 1 to 7.
10. A storage medium, It is characterized in that The storage medium stores instructions, and when the instructions in the storage medium are executed by a processor, the processor is enabled to execute the data synchronization method according to any one of claims 1 to 7.
