Data synchronization method, device, storage medium and computer equipment

Abstract

The present application relates to a data synchronization method, device, storage medium and computer equipment, the method comprising: obtaining log data of a source database, determining data change information of a source database table of a current batch according to the log data; obtaining a data warehouse table identifier corresponding to the source database table identifier in the data change information in a data warehouse, determining the incremental data corresponding to the data warehouse table identifier according to the data change content corresponding to the source database table identifier; determining the incremental data of a data warehouse of a current batch according to the incremental data corresponding to the data warehouse table identifier; obtaining the full data of a data warehouse of a previous batch, generating the full data of a data warehouse of a current batch according to the full data of the data warehouse of the previous batch and the incremental data of the data warehouse of the current batch. The scheme provided by the present application improves the accuracy of data synchronization.

Description

Technical Field

[0001] The present application relates to the field of computer Internet technology, and in particular to a data synchronization method, device, storage medium and computer equipment. Background Art

[0002] With the promotion and popularization of the Internet and big data technologies, data warehouses have gradually become mainstream. Since data warehouses have a large storage capacity and can process structured data, data in the database can be synchronized to the data warehouse for processing. In the traditional data synchronization method, data extraction tools such as sqoop can be used to import all data in the database into the data warehouse; distributed log collection systems such as flume can also be used to import newly added data in the database into the data warehouse. However, the traditional data synchronization method does not support data update, deletion, etc., resulting in inaccurate data synchronization. Summary of the invention

[0003] Based on this, it is necessary to provide a data synchronization method, apparatus, storage medium and computer equipment to address the technical problem of inaccurate traditional data synchronization methods.

[0004] A data synchronization method, comprising:

[0005] Obtaining log data of the source database, and determining data change information of the source database table of the current batch according to the log data;

[0006] Obtaining a data warehouse table identifier corresponding to the source database table identifier in the data change information in the data warehouse, and determining incremental data corresponding to the data warehouse table identifier according to the data change content corresponding to the source database table identifier;

[0007] Determine the incremental data of the data warehouse of the current batch according to the incremental data corresponding to the data warehouse table identifier;

[0008] The full amount of data of the data warehouse of the previous batch is obtained, and the full amount of data of the data warehouse of the current batch is generated according to the full amount of data of the data warehouse of the previous batch and the incremental data of the data warehouse of the current batch.

[0009] A data synchronization device, comprising:

[0010] An acquisition module is used to acquire log data of a source database and determine data change information of a source database table of a current batch according to the log data;

[0011] A determination module, used to obtain a data warehouse table identifier corresponding to the source database table identifier in the data change information in the data warehouse, and determine the incremental data corresponding to the data warehouse table identifier according to the data change content corresponding to the source database table identifier;

[0012] The determination module is further used to determine the incremental data of the data warehouse of the current batch according to the incremental data corresponding to the data warehouse table identifier;

[0013] The generation module is used to obtain the full data of the data warehouse of the previous batch, and generate the full data of the data warehouse of the current batch based on the full data of the data warehouse of the previous batch and the incremental data of the data warehouse of the current batch.

[0014] A storage medium stores a computer program, and when the computer program is executed by a processor, the processor executes the steps of the data synchronization method.

[0015] A computer device comprises a memory and a processor, wherein the memory stores a computer program, and when the computer program is executed by the processor, the processor executes the steps of the data synchronization method.

[0016] The above-mentioned data synchronization method, device, storage medium and computer equipment obtain the log data of the source database, determine the data change information of the source database table of the current batch according to the log data, obtain the data warehouse table identifier corresponding to the source database table identifier in the data change information in the data warehouse, determine the incremental data corresponding to the data warehouse table identifier according to the data change content corresponding to the source database table identifier, determine the incremental data of the data warehouse of the current batch according to the incremental data corresponding to the data warehouse table identifier, obtain the full data of the data warehouse of the previous batch, and generate the full data of the data warehouse of the current batch according to the full data of the data warehouse of the previous batch and the incremental data of the data warehouse of the current batch. Since all changes of the data of the source database are recorded in the log data, data synchronization between the source database and the data warehouse is realized through the log data, so that the data of the data warehouse corresponds to the changes of the data of the source database, and the accuracy of data synchronization is improved. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] Figure 1 An application environment diagram for implementing a data synchronization method in one embodiment;

[0018] Figure 2 A schematic diagram of a flow chart of a data synchronization method in an embodiment;

[0019] Figure 3 An application environment diagram of a data synchronization method in an embodiment;

[0020] Figure 4 is an application environment diagram of a data synchronization method in another embodiment;

[0021] Figure 5is a flowchart of a data synchronization method in another embodiment;

[0022] Figure 6 is a flowchart of a data synchronization method in yet another embodiment;

[0023] Figure 7 is a structural block diagram of a data synchronization device in another embodiment;

[0024] Figure 8 FIG. 4 is a structural block diagram of a computer device in one embodiment. DETAILED DESCRIPTION

[0025] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0026] Figure 1 FIG. 1 is an application environment diagram of a data synchronization method in an embodiment. Figure 1 The data synchronization method is applied to a data synchronization system. The data synchronization system may include a source database 102 and a data warehouse 104. The source database 102 and the data warehouse 104 may be different servers.

[0027] In one embodiment, the data warehouse 104 obtains log data of the source database 102, determines data change information of the source database 102 table of the current batch based on the log data, obtains the data warehouse 104 table identifier corresponding to the source database 102 table identifier in the data change information in the data warehouse 104, determines the incremental data corresponding to the data warehouse 104 table identifier based on the data change content corresponding to the source database 102 table identifier, determines the incremental data of the data warehouse 104 of the current batch based on the incremental data corresponding to the data warehouse 104 table identifier, obtains the full data of the data warehouse 104 of the previous batch, and generates the full data of the data warehouse 104 of the current batch based on the full data of the data warehouse 104 of the previous batch and the incremental data of the data warehouse 104 of the current batch.

[0028] Those skilled in the art will understand that Figure 1 The structure shown in the figure is only a block diagram of a partial structure related to the scheme of the present application, and does not constitute a limitation on the terminal to which the scheme of the present application is applied. The specific terminal may include more or fewer components than shown in the figure, or combine certain components, or have a different arrangement of components.

[0029] like Figure 2 As shown, in one embodiment, a data synchronization method is provided, and the data synchronization method specifically includes the following steps:

[0030] Step 202: Obtain log data of the source database, and determine data change information of the source database table of the current batch according to the log data.

[0031] The source database refers to the party from which data is extracted during the data synchronization process. The source database can be a relational database, which is a database that uses a relational model to organize data and stores data in the form of rows and columns (i.e., tables), such as MySQL database, mariaDB database, Oracle database, etc.

[0032] The source database table refers to a table in the source database used to store data. In the source database, if there is a large number and high read and write concurrency, it is easy to exceed the performance limit of a single database instance. For this situation, sharding can be used in the source database. Sharding is to divide a database into multiple parts and split a main table into multiple sub-tables, thereby effectively alleviating the read and write pressure of a single database or a single table. Therefore, the source database table in this embodiment may be a sub-table obtained after the sharding operation.

[0033] Log data records the data or table structure changes of the source database, such as Binlog of MySQL database and LogMiner of Oracle database.

[0034] Data change information refers to the information about the changes made by users to the data stored in the source database table. Taking Binlog as an example, Binlog is a binary file that records the SQL statements that users use to change data in the MySQL database. Binlog stores DML (Data Manipulation Language), which records the changes made by users to the data stored in the source database table. That is, in the MySQL database, data change information can be DML.

[0035] The data change information includes at least: the source database table identifier and the data change content. The data change content includes: the data change type, the data before the data change, the data after the data change, and the data change timestamp, etc. The data change types include at least: addition, modification, and deletion. When the data change type is addition, the data before the data change is empty, and the data after the data change is the added data; when the data change type is modification, the data before the data change is the data before the modification, and the data after the data change is the modified data; when the data change type is deletion, the data before the data change is the data before the deletion, and the data after the data change is empty.

[0036] Specifically, the time interval of the migration batch can be set according to the actual application, such as performing a batch migration operation every 24 hours. The batches of data synchronization can be marked by the migration batch information to ensure the orderliness of data synchronization. The migration batch information can be set according to the actual application, such as numbers, letters, etc.

[0037] Step 204: Obtain a data warehouse table identifier corresponding to the source database table identifier in the data change information in the data warehouse, and determine incremental data corresponding to the data warehouse table identifier according to the data change content corresponding to the source database table identifier.

[0038] The source database table identifier is used to represent the identity information of the source database table, and the source database table identifier may be the name of the source database table. The data warehouse table identifier is used to represent the identity information used to represent the data warehouse table, and the data warehouse table identifier may be the name of the data warehouse table.

[0039] The data warehouse refers to the database into which the extracted data is imported during the data synchronization process. The data warehouse stores data in the form of rows and columns (i.e., in the form of tables), and the data warehouse table refers to a table in the data warehouse used to store data. In one embodiment, the data warehouse can be Hive, which has a relatively large storage capacity and can be used to process structured data.

[0040] Since data warehouses have large storage capacity and can process structured data, they are generally not divided into different databases or tables. However, when the source database is divided into different databases and tables, but the data warehouse is not divided into different databases and tables, the synchronization of data from the source database table to the data warehouse table may cause data confusion. Based on this, it is necessary to know the mapping relationship between the source database table and the data warehouse table, and then determine the data warehouse table corresponding to the source database table of the current batch. Figure 3 As shown, a main table in the source database is split into sub-table 1, sub-table 2 and sub-table 3. For each sub-table with data changes, there is a corresponding data change content. The data warehouse tables corresponding to sub-table 1, sub-table 2 and sub-table 3 in the data warehouse are determined, and the data in the data warehouse table is updated according to the data change content of sub-table 1, sub-table 2 and sub-table 3.

[0041] Specifically, if the source database table represented by the source database table identifier is a sub-table, detect whether the main table identifier corresponding to the source database table identifier is in the data warehouse. When the main table identifier corresponding to the source database table identifier is in the data warehouse, obtain the first sub-table list corresponding to the source database table from the configuration file, and determine the data warehouse table identifier corresponding to the source database table identifier according to the first sub-table list. When the main table identifier corresponding to the source database table identifier is not in the data warehouse, calculate the second sub-table list corresponding to the source database table, and determine the data warehouse table identifier corresponding to the source database table identifier according to the second sub-table list. Among them, if a source database table (sub-table) has performed a synchronization operation with the data warehouse, or other sub-tables belonging to the same main table as the source database table (sub-table) have performed a synchronization operation with the data warehouse, the main table identifier corresponding to the source database table (sub-table) identifier will be stored in the data warehouse. The sub-table list records the identifiers of each source data table (sub-table) belonging to the same main table identifier.

[0042] The incremental data corresponding to the data warehouse table identifier refers to: for a data warehouse table, the data to be synchronized corresponding to the current batch of data synchronization process.

[0043] Step 206, determining the incremental data of the data warehouse of the current batch according to the incremental data corresponding to the data warehouse table identifier.

[0044] The incremental data of the current batch of data warehouse refers to: for the data warehouse, the data to be synchronized corresponding to the data synchronization process of the current batch. That is, the incremental data of the current batch of data warehouse refers to the difference between the full data of the data warehouse after the synchronization of the current batch and the full data of the data warehouse after the synchronization of the previous batch. The full data refers to all the data stored in the data warehouse.

[0045] Specifically, the data change information is converted into corresponding execution statements in the data warehouse, and the execution statements are executed in the data warehouse to obtain incremental data.

[0046] Step 208, obtain the full data of the data warehouse of the previous batch, and generate the full data of the data warehouse of the current batch according to the full data of the data warehouse of the previous batch and the incremental data of the data warehouse of the current batch.

[0047] like Figure 4As shown, when the source database synchronizes data to the data warehouse for the first time, a data extraction tool (such as sqoop, etc.) can be used to synchronize the full amount of data from the source database to the data warehouse. In the subsequent synchronization process, a log extraction tool (such as canal, etc.) can be used to send the log data of the source database to the data warehouse in real time or regularly, and the data warehouse is updated according to the log data of the source database table, thereby realizing data synchronization between the two. Specifically, using the incremental formula, the full amount of data of the data warehouse of the current batch (T1 batch) is generated according to the full amount of data of the data warehouse of the previous batch (T0 batch) and the incremental data of the data warehouse table (T1 batch). The incremental formula can be: the full amount of data of the data warehouse of the T1 batch = the full amount of data of the data warehouse of the T0 batch + the incremental data of the data warehouse table of the T1 batch. The full amount of data of the data warehouse of the previous batch is updated based on the incremental data of the data warehouse of the current batch, that is, the full amount of data of the data warehouse of the current batch is obtained.

[0048] The data synchronization method provided in this embodiment obtains the log data of the source database, determines the data change information of the source database table of the current batch based on the log data, obtains the data warehouse table identifier corresponding to the source database table identifier in the data change information in the data warehouse, determines the incremental data corresponding to the data warehouse table identifier based on the data change content corresponding to the source database table identifier, determines the incremental data of the data warehouse of the current batch based on the incremental data corresponding to the data warehouse table identifier, obtains the full data of the data warehouse of the previous batch, and generates the full data of the data warehouse of the current batch based on the full data of the data warehouse of the previous batch and the incremental data of the data warehouse of the current batch. Since all changes of the data of the source database are recorded in the log data, data synchronization between the source database and the data warehouse is realized through the log data, so that the data of the data warehouse corresponds to the changes of the data of the source database, and the accuracy of data synchronization is improved.

[0049] In one embodiment, before determining the data change information of the source database tables of the current batch according to the log data, it also includes: determining the table structure change information of the source database tables of the current batch according to the log data; obtaining the data warehouse table identifier corresponding to the source database table identifier in the table structure change information in the data warehouse, and updating the table structure of the data warehouse table corresponding to the data warehouse table identifier according to the table structure change content corresponding to the source database table identifier.

[0050] Table structure change information refers to the change information of the table structure of the source database table by the user. Taking Binlog as an example, Binlog is a binary file that records the SQL statements that users use to change data in the MySQL database. Binlog also stores DDL (Data Definition Language), which records the changes made by users to the table structure of the source database table. That is, in the MySQL database, the table structure change information is DDL.

[0051] The table structure change information at least includes: the source database table identifier and the table structure change content. The table structure change content includes: the table structure change type, the table structure change data, and the table structure change timestamp, etc. The table structure change type at least includes: the addition and structure change of source database objects (such as libraries and tables). The table structure change data at least includes the changed field name and field data type.

[0052] In actual applications, the table structure of the source database table may be changed. If the table structure of the data warehouse table is not changed in time after the table structure of the source database table is changed, it is easy to cause data synchronization confusion. Before data synchronization, you can first check whether the table structure of the source database table and the data warehouse table is consistent. If they are consistent, perform data synchronization; if not, adjust the table structure of the data warehouse table to be consistent with the table structure of the source database table, and then perform data synchronization.

[0053] If there is structural change information in the log data, it is determined that the table structures of the source database table and the data warehouse table are inconsistent, and the data warehouse table identifier corresponding to the source database identifier in the table structure change information is obtained, and the table structure of the data warehouse table corresponding to the data warehouse table identifier is updated according to the table structure change content. Specifically, the structural change information is converted into the corresponding execution statement in the data warehouse, and the table structure change statement is executed in the data warehouse, that is, the synchronization of the table structure of the source database table and the data warehouse table is completed.

[0054] If the current batch of log data only contains table structure change information, obtain the data warehouse table identifier corresponding to the source database table identifier in the table structure change information in the data warehouse, and update the table structure of the data warehouse table corresponding to the data warehouse table identifier according to the table structure change content corresponding to the source database table identifier.

[0055] If the current batch of log data contains data change information and table structure change information, first obtain the data warehouse table identifier corresponding to the source database table identifier in the table structure change information in the data warehouse, and update the table structure of the data warehouse table corresponding to the data warehouse table identifier according to the table structure change content corresponding to the source database table identifier; then obtain the data warehouse table identifier corresponding to the source database table identifier in the data change information in the data warehouse, and determine the incremental data corresponding to the data warehouse table identifier according to the data change content corresponding to the source database table identifier.

[0056] The data synchronization method provided in this embodiment realizes table structure synchronization between the source database and the data warehouse through structure change information, thereby avoiding data synchronization confusion caused by table structure changes.

[0057] In one embodiment, obtaining a data warehouse table identifier corresponding to a source database table identifier in the data change information in a data warehouse includes: if the source database table represented by the source database table identifier is a sub-table, detecting whether a main table identifier corresponding to the source database table identifier is in the data warehouse; when the main table identifier corresponding to the source database table identifier is in the data warehouse, obtaining a first sub-table list corresponding to the source database table identifier from a configuration file; and determining, according to the first sub-table list, a data warehouse table identifier corresponding to the source database table identifier.

[0058] The main table identifier is used to determine whether there is a data warehouse table identifier corresponding to a source database table identifier in the data warehouse. The sub-table list records the identifiers of each source data table (sub-table) belonging to the same main table. The first sub-table list refers to the sub-table list stored in the configuration file.

[0059] If a source data table (sub-table) or a source database table (sub-table) that belongs to the same master table as the source data table (sub-table) has been synchronized to the data warehouse, the data warehouse will record the master table identifier corresponding to the source data table (sub-table) identifier, the first sub-table list, and the data warehouse table. If the master table identifier corresponding to the source database table (sub-table) identifier exists in the data warehouse, the first sub-table list is obtained from the configuration file, and the data warehouse table identifier corresponding to the source database table (sub-table) identifier is determined according to the first sub-table list.

[0060] The data synchronization method provided in this embodiment can accurately identify sub-tables belonging to the same main table, and synchronize the sub-tables belonging to the same main table in the source database to the same data warehouse table in the data warehouse.

[0061] In one embodiment, determining the data warehouse table identifier corresponding to the source database table identifier based on the first sub-table list includes: obtaining the sub-library and table rules of the source database table represented by the source database table identifier; if the sub-library and table rules of the source database table are fixed rules, then using the data warehouse table identifier corresponding to the first sub-table list as the data warehouse table identifier corresponding to the source database table identifier.

[0062] The rules for splitting databases and tables include fixed rules and time rules. Fixed rules refer to splitting a main table into a fixed number of sub-tables; time rules refer to splitting a main table according to time. As time goes by, the number of sub-tables will gradually increase.

[0063] If the sub-library and sub-table rules of the source database table are fixed rules, then the source database table (sub-table) identifier on the first sub-table list will not change, and the data warehouse table identifier corresponding to the first sub-table list obtained from the configuration file will be used as the data warehouse table identifier corresponding to the source database table (sub-table) identifier.

[0064] The data synchronization method provided in this embodiment can accurately identify sub-tables belonging to the same main table, and synchronize the sub-tables belonging to the same main table in the source database to the same data warehouse table in the data warehouse.

[0065] In one embodiment, the method also includes: if the library and table sub-division rule of the source database table is a time rule, obtaining the comparison table identifier in the first sub-table list; determining whether the table structure of the source database table is consistent with the table structure of the comparison table represented by the comparison table identifier; if consistent, using the data warehouse table identifier corresponding to the first sub-table list as the data warehouse table identifier corresponding to the source database table identifier.

[0066] The comparison table represented by the comparison table identifier is used as a standard table structure to be compared with the table structure of the newly generated source database table (sub-table). The selection rules of the comparison table identifier can be divided into static selection and dynamic selection. Static selection means that after the first sub-table list is established, any source database table identifier in the first sub-table list is selected as the comparison table identifier; dynamic selection means that the source database table identifier closest to the current time in the first sub-table list is selected as the comparison table identifier.

[0067] If the sub-library and sub-table rule of the source database table is a time rule, the table structure of the newly generated source database table (sub-table) needs to be proofread. If they are consistent, the data warehouse table identifier corresponding to the newly generated source database table (sub-table) identifier is the data warehouse table identifier corresponding to the first sub-table list. If they are inconsistent, an alarm message is output to allow business personnel to check for errors in sub-library and sub-table.

[0068] The data synchronization method provided in this embodiment can accurately identify sub-tables belonging to the same main table, and synchronize the sub-tables belonging to the same main table in the source database to the same data warehouse table in the data warehouse.

[0069] In one embodiment, after determining whether the table structure of the source database table is consistent with the table structure of the comparison table represented by the comparison table identifier, the method further includes: if they are consistent, updating the first sub-table list according to the source database table identifier.

[0070] If the sub-library and sub-table rule of the source database table is a time rule, it is necessary to proofread the table structure of the newly generated source database table (sub-table). If they are consistent, the data warehouse table identifier corresponding to the newly generated source database table (sub-table) identifier is the data warehouse table identifier corresponding to the first sub-table list, and the newly generated source database table (sub-table) identifier is added to the first sub-table list.

[0071] The data synchronization method provided in this embodiment ensures the accuracy of the records in the first sub-table list.

[0072] In one embodiment, obtaining the comparison table identifier in the first sub-table list includes: obtaining the source database identifier closest to the current time in the first sub-table list, and using the source database identifier closest to the current time as the comparison table identifier.

[0073] The data synchronization method provided in this embodiment dynamically selects a comparison table to ensure the accuracy of the table structure of the comparison table.

[0074] In one embodiment, if the source database table represented by the source database table identifier is a sub-table, after detecting whether the main table identifier corresponding to the source database table identifier is in the data warehouse, the method further includes: when the main table identifier to which the source database table identifier belongs is not in the data warehouse, obtaining the sub-library and sub-table naming rules of the source database table represented by the source database table identifier; according to the sub-library and sub-table naming rules of the source database table, calculating a second sub-table list corresponding to the source database table identifier; and determining the data warehouse table identifier corresponding to the source database table identifier according to the second sub-table list.

[0075] The naming rules for sub-libraries and tables refer to the naming method used for the sub-libraries and tables of the source database. When sub-libraries and tables are divided into sub-libraries and tables of the source database, name identifiers can be added to the names of the source database tables (sub-tables). Through the name identifiers, sub-tables belonging to the same main table can be identified.

[0076] The name identifier may include: a fixed identifier (such as a number or letter), a date identifier, a month identifier, etc. A fixed identifier may include: [0-9]{1} or [0-9]{2} or [0-9]{3}, etc. A date identifier may include:

[12] \d{3}(0[1-9]|1[0-2])(0[1-9]|

[12] \d|3

[01] ), etc. A month identifier may include:

[12] \d{3}(0[1-9]|1[0-2]) or \d{2}(0[1-9]|1[0-2]), etc.

[0077] The mode of name identification can be: adding name identification to the library name and / or table name of the sub-table. For example, name identification can be added to the library name and / or table name of the sub-table. For sub-table a,b (library name is a, table name is b), name identification can be added to the library name of the sub-table: a_0,b, a_1,b, a_2,b, etc., or to the table name of the sub-table: a,b_0, a,b_1, a,b_2, etc., or to both the library name and the table name of the sub-table: a_0,b_0, a_1,b_1, a_2,b_2, etc.

[0078] Different sub-library and sub-table naming rules are used for sub-library and sub-table operations of different main tables, that is, different name identification contents and / or different name identification patterns are used. According to the sub-library and sub-table naming rules, each source database table (sub-table) identifier that belongs to the same main table identifier as a source database table (sub-table) identifier can be obtained in the source database, and then a sub-table list corresponding to the source database table (sub-table) identifier can be obtained.

[0079] In actual application, a certain number of source database table (sub-table) identifiers can be selected for testing to detect whether the source database table (sub-table) identifiers belonging to the same main table identifier in the source database can be determined as the same data warehouse table identifier through the sub-library and sub-table naming rules.

[0080] The second sub-table list refers to the sub-table list calculated according to the sub-database and sub-table naming rules. If the main table identifier corresponding to the source database table (sub-table) identifier does not exist in the data warehouse, the second sub-table list corresponding to the source database table (sub-table) identifier is calculated according to the sub-database and sub-table naming rules, and the data warehouse table identifier corresponding to the source database table identifier is determined according to the second sub-table list.

[0081] The data synchronization method provided in this embodiment calculates the sub-table list corresponding to the source database table (sub-table) identifier according to the sub-library and sub-table naming rules, so as to accurately identify the sub-tables belonging to the same main table.

[0082] In one embodiment, determining the data warehouse table identifier corresponding to the source database table identifier according to the second sub-table list includes: judging whether the table structures of the source database tables represented by the respective source database table identifiers in the second sub-table list are consistent; if they are consistent, creating the data warehouse table identifier corresponding to the source database table identifier in the data warehouse.

[0083] After calculating the second sub-table list, determine whether the table structures of the source database tables (sub-tables) in the sub-table list are consistent. If they are consistent, create a data warehouse table identifier corresponding to the source database table (sub-table) identifier in the data warehouse, and associate and store the main table identifier corresponding to the source database table (sub-table) identifier, the second sub-table list, and the data warehouse table identifier; if they are inconsistent, output an alarm message for business personnel to check sub-database and sub-table errors.

[0084] The data synchronization method provided in this embodiment realizes synchronization of source database tables (sub-tables) belonging to the same main table in the source database to the same data warehouse table in the data warehouse.

[0085] like Figure 5 As shown, in a specific embodiment, the data synchronization method includes:

[0086] (a1) If the source database table represented by the source database table identifier is a sub-table, it is detected whether the main table identifier corresponding to the source database table identifier is in the data warehouse.

[0087] (a2), when the main table identifier corresponding to the source database table identifier is in the data warehouse, obtain the first sub-table list corresponding to the source database table identifier from the configuration file; obtain the sub-table and sub-library rules of the source database table represented by the source database table identifier, if the sub-table and sub-library rules of the source database table are fixed rules, use the data warehouse table identifier corresponding to the first sub-table list as the data warehouse table identifier corresponding to the source database table identifier; if the sub-table and sub-library rules of the source database table are time rules, obtain the comparison table identifier in the first sub-table list, if the table structure of the source database table is consistent with the table structure of the comparison table represented by the comparison table identifier, use the data warehouse table identifier corresponding to the first sub-table list as the data warehouse table identifier corresponding to the source database table identifier, and update the first sub-table list according to the source database table identifier.

[0088] (a3) When the main table identifier to which the source database table identifier belongs is not in the data warehouse, obtain the sub-library and sub-table naming rules of the source database table represented by the source database table identifier, and calculate the second sub-table list corresponding to the source database table identifier according to the sub-library and sub-table naming rules of the source database table. If the table structures of the source database tables represented by the source database table identifiers in the second sub-table list are consistent, create a data warehouse table identifier corresponding to the source database table identifier in the data warehouse.

[0089] The data synchronization method provided in this embodiment realizes synchronization of sub-tables belonging to the same main table in the source database to the same data warehouse table in the data warehouse.

[0090] like Figure 6 As shown, in a specific embodiment, the data synchronization method includes the following steps:

[0091] S602, obtaining the Binlog of MySQL, and determining the table structure change information of the current batch of MySQL tables according to the Binlog;

[0092] S604, obtaining a Hive table identifier corresponding to the MySQL table identifier in the table structure change information in the Hive, and updating the table structure of the Hive table corresponding to the Hive table identifier according to the table structure change content corresponding to the MySQL table identifier;

[0093] S606, determining data change information of the current batch of MySQL tables according to the Binlog;

[0094] S608, obtaining a Hive table identifier corresponding to the MySQL table identifier in the data change information in Hive, and determining incremental data corresponding to the Hive table identifier according to the data change content corresponding to the MySQL table identifier;

[0095] S610, determining the incremental data of the current batch of Hive according to the incremental data corresponding to the Hive table identifier;

[0096] S612, obtaining the full data of the previous batch of Hive, and generating the full data of the current batch of Hive according to the full data of the previous batch of Hive and the incremental data of the current batch of Hive.

[0097] The data synchronization method provided in this embodiment records all changes of MySQL data and table structure in Binlog, and implements data synchronization and structure synchronization between MySQL and Hive through Binlog, so that Hive data corresponds to changes of MySQL data, and Hive table structure corresponds to changes of MySQL table structure, thereby improving the accuracy of data synchronization and table structure synchronization.

[0098] In a specific embodiment, the data synchronization method includes the following steps:

[0099] (c1) If the MySQL table represented by the MySQL table identifier is a sub-table, detect whether the main table identifier corresponding to the MySQL table identifier is in the Hive.

[0100] (c2), when the main table identifier corresponding to the MySQL table identifier is in the Hive, obtain the first sub-table list corresponding to the MySQL table identifier from the configuration file; obtain the library and table sub-rules of the MySQL table represented by the MySQL table identifier, if the library and table sub-rules of the MySQL table are fixed rules, use the Hive table identifier corresponding to the first sub-table list as the Hive table identifier corresponding to the MySQL table identifier; if the library and table sub-rules of the MySQL table are time rules, obtain the comparison table identifier in the first sub-table list, if the table structure of the MySQL table is consistent with the table structure of the comparison table represented by the comparison table identifier, use the Hive table identifier corresponding to the first sub-table list as the Hive table identifier corresponding to the MySQL table identifier, and update the first sub-table list according to the MySQL table identifier.

[0101] (c3) When the main table identifier to which the MySQL table identifier belongs is not in the Hive, obtain the sub-library and sub-table naming rules of the MySQL table represented by the MySQL table identifier, and calculate the second sub-table list corresponding to the MySQL table identifier according to the sub-library and sub-table naming rules of the MySQL table. If the table structures of the MySQL tables represented by the various MySQL table identifiers in the second sub-table list are consistent, create a Hive table identifier corresponding to the MySQL table identifier in the Hive.

[0102] The data synchronization method provided in this embodiment avoids data synchronization confusion caused by MySQL database and table sharding, and improves the accuracy of data synchronization and table structure synchronization.

[0103] Figure 2 and Figure 6 FIG. 1 is a flow chart of a data synchronization method in an embodiment. It should be understood that although Figure 2 and Figure 6 The steps in the flowchart are shown in sequence as indicated by the arrows, but these steps are not necessarily executed in the order indicated by the arrows. Unless otherwise specified in this document, there is no strict order restriction for the execution of these steps, and these steps can be executed in other orders. Moreover, Figure 2 and Figure 6 At least part of the steps may include multiple sub-steps or multiple stages. These sub-steps or stages are not necessarily executed at the same time, but can be executed at different times. The execution order of these sub-steps or stages is not necessarily sequential, but can be executed in turn or alternately with other steps or at least part of the sub-steps or stages of other steps.

[0104] In one embodiment, Figure 7 As shown, a data synchronization device 700 is provided, including: an acquisition module 702, a determination module 704 and a generation module 706, wherein:

[0105] The acquisition module 702 is used to: acquire log data of the source database, and determine data change information of the source database table of the current batch according to the log data;

[0106] The determination module 704 is used to: obtain the data warehouse table identifier corresponding to the source database table identifier in the data change information in the data warehouse, and determine the incremental data corresponding to the data warehouse table identifier according to the data change content corresponding to the source database table identifier;

[0107] The determination module 704 is further used to: determine the incremental data of the data warehouse of the current batch according to the incremental data corresponding to the data warehouse table identifier;

[0108] The generation module 706 is used to obtain the full data of the data warehouse of the previous batch, and generate the full data of the data warehouse of the current batch according to the full data of the data warehouse of the previous batch and the incremental data of the data warehouse of the current batch.

[0109] The data synchronization device 700 provided in this embodiment obtains the log data of the source database, determines the data change information of the source database table of the current batch according to the log data, obtains the data warehouse table identifier corresponding to the source database table identifier in the data change information in the data warehouse, determines the incremental data corresponding to the data warehouse table identifier according to the data change content corresponding to the source database table identifier, determines the incremental data of the data warehouse of the current batch according to the incremental data corresponding to the data warehouse table identifier, obtains the full data of the data warehouse of the previous batch, and generates the full data of the data warehouse of the current batch according to the full data of the data warehouse of the previous batch and the incremental data of the data warehouse of the current batch. Since all changes of the data of the source database are recorded in the log data, data synchronization between the source database and the data warehouse is realized through the log data, so that the data of the data warehouse corresponds to the changes of the data of the source database, thereby improving the accuracy of data synchronization.

[0110] In one embodiment, the data synchronization device 700 also includes an update module 708, and the determination module 704 is further used to: determine the table structure change information of the source database table of the current batch based on the log data; the update module 708 is used to: obtain the data warehouse table identifier corresponding to the source database table identifier in the table structure change information in the data warehouse, and update the table structure of the data warehouse table corresponding to the data warehouse table identifier according to the table structure change content corresponding to the source database table identifier.

[0111] In one embodiment, the determination module 704 is specifically used to: if the source database table represented by the source database table identifier is a sub-table, detect whether the main table identifier corresponding to the source database table identifier is in the data warehouse; when the main table identifier corresponding to the source database table identifier is in the data warehouse, obtain a first sub-table list corresponding to the source database table identifier from a configuration file; and determine the data warehouse table identifier corresponding to the source database table identifier based on the first sub-table list.

[0112] In one embodiment, the determination module 704 is specifically used to: obtain the library and table sub-division rules of the source database table represented by the source database table identifier; if the library and table sub-division rules of the source database table are fixed rules, then use the data warehouse table identifier corresponding to the first sub-table list as the data warehouse table identifier corresponding to the source database table identifier.

[0113] In one embodiment, the determination module 704 is specifically used to: if the library and table sub-division rule of the source database table is a time rule, obtain the comparison table identifier in the first sub-table list; determine whether the table structure of the source database table is consistent with the table structure of the comparison table represented by the comparison table identifier; if consistent, use the data warehouse table identifier corresponding to the first sub-table list as the data warehouse table identifier corresponding to the source database table identifier.

[0114] In one embodiment, the determination module 704 is specifically configured to: if they are consistent, update the first sub-table list according to the source database table identifier.

[0115] In one embodiment, the determination module 704 is specifically used to: obtain the source database identifier closest to the current time in the first sub-table list, and use the source database identifier closest to the current time as the comparison table identifier.

[0116] In one embodiment, the determination module 704 is specifically used to: when the main table identifier to which the source database table identifier belongs is not in the data warehouse, obtain the sub-library and sub-table naming rules of the source database table represented by the source database table identifier; calculate the second sub-table list corresponding to the source database table identifier according to the sub-library and sub-table naming rules of the source database table; and determine the data warehouse table identifier corresponding to the source database table identifier according to the second sub-table list.

[0117] In one embodiment, the determination module 704 is specifically used to: determine whether the table structures of the source database tables represented by the source database table identifiers in the second sub-table list are consistent; if consistent, create a data warehouse table identifier corresponding to the source database table identifier in the data warehouse.

[0118] Figure 8 The internal structure diagram of a computer device in one embodiment is shown. The computer device may specifically be Figure 1 The terminal in. Figure 8 As shown, the computer device includes a processor, a memory and a network interface connected through a system bus. Among them, the memory includes a non-volatile storage medium and an internal memory. The non-volatile storage medium of the computer device stores an operating system and may also store a computer program, which, when executed by the processor, enables the processor to implement a data synchronization method. The internal memory may also store a computer program, which, when executed by the processor, enables the processor to execute a data synchronization method.

[0119] Those skilled in the art will understand that Figure 8The structure shown in the figure is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation on the computer device to which the solution of the present application is applied. The specific computer device may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0120] In one embodiment, the data synchronization device provided by the present application can be implemented in the form of a computer program. The computer program can be implemented in the form of a computer program. Figure 8 The computer device memory may store various program modules constituting the data synchronization device, for example, Figure 7 The acquisition module 702, the determination module 704 and the generation module 706 are shown. The computer program composed of various program modules enables the processor to execute the steps of the data synchronization method of each embodiment of the present application described in this specification.

[0121] In one embodiment, a computer device is provided, including a memory and a processor, wherein the memory stores a computer program, and when the computer program is executed by the processor, the processor executes the steps of the above-mentioned data synchronization method. The steps of the data synchronization method here may be the steps of the data synchronization method in the above-mentioned various embodiments.

[0122] In one embodiment, a storage medium is provided, which stores a computer program, and when the computer program is executed by a processor, the processor executes the steps of the above data synchronization method. The steps of the data synchronization method here can be the steps in the data synchronization method of each embodiment above.

[0123] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the program can be stored in a non-volatile computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory may include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory may include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Sync hour link) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).

[0124] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0125] The above-mentioned embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the present application. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the present application shall be subject to the attached claims.

Claims

1. A data synchronization method, comprising: Obtaining log data of the source database, and determining data change information of the source database table of the current batch according to the log data; If the source database table represented by the source database table identifier in the data change information is a sub-table, detecting whether the main table identifier corresponding to the source database table identifier is in the data warehouse; When the main table identifier corresponding to the source database table identifier is in the data warehouse, a first sub-table list corresponding to the source database table identifier is obtained from a configuration file; according to the first sub-table list, a data warehouse table identifier corresponding to the source database table identifier is determined; according to the data change content corresponding to the source database table identifier, incremental data corresponding to the data warehouse table identifier is determined; the first sub-table list refers to a sub-table list stored in the configuration file; the sub-table list records each source data table identifier belonging to the same main table identifier; Determine the incremental data of the data warehouse of the current batch according to the incremental data corresponding to the data warehouse table identifier; The full amount of data of the data warehouse of the previous batch is obtained, and the full amount of data of the data warehouse of the current batch is generated according to the full amount of data of the data warehouse of the previous batch and the incremental data of the data warehouse of the current batch.

2. The method according to claim 1, characterized in that Before determining the data change information of the source database table of the current batch according to the log data, the method further includes: Determine table structure change information of the source database table of the current batch according to the log data; The data warehouse table identifier corresponding to the source database table identifier in the table structure change information is obtained, and the table structure of the data warehouse table corresponding to the data warehouse table identifier is updated according to the table structure change content corresponding to the source database table identifier.

3. The method according to claim 1, characterized in that The step of determining the data warehouse table identifier corresponding to the source database table identifier according to the first sub-table list includes: Obtaining a database and table partitioning rule for the source database table represented by the source database table identifier; If the library and table partitioning rule of the source database table is a fixed rule, the data warehouse table identifier corresponding to the first table partitioning list is used as the data warehouse table identifier corresponding to the source database table identifier.

4. The method according to claim 3, characterized in that The method further comprises: If the database and table sharding rule of the source database table is a time rule, obtaining a comparison table identifier in the first table sharding list; Determine whether the table structure of the source database table is consistent with the table structure of the comparison table represented by the comparison table identifier; If they are consistent, the data warehouse table identifier corresponding to the first sub-table list is used as the data warehouse table identifier corresponding to the source database table identifier.

5. The method according to claim 4, characterized in that After determining whether the table structure of the source database table is consistent with the table structure of the comparison table represented by the comparison table identifier, the method further includes: If they are consistent, the first sub-table list is updated according to the source database table identifier.

6. The method according to claim 4, characterized in that The obtaining of the comparison table identifier in the first sub-table list includes: The source database identifier closest to the current time in the first sub-table list is obtained, and the source database identifier closest to the current time is used as the comparison table identifier.

7. The method according to claim 1, characterized in that If the source database table represented by the source database table identifier in the data change information is a sub-table, after detecting whether the main table identifier corresponding to the source database table identifier is in the data warehouse, the method further includes: When the main table identifier to which the source database table identifier belongs is not in the data warehouse, obtaining a sub-library and sub-table naming rule of the source database table represented by the source database table identifier; According to the sub-library and sub-table naming rules of the source database table, a second sub-table list corresponding to the source database table identifier is calculated; According to the second sub-table list, the data warehouse table identifier corresponding to the source database table identifier is determined.

8. The method according to claim 7, characterized in that The step of determining the data warehouse table identifier corresponding to the source database table identifier according to the second sub-table list includes: Determining whether the table structures of the source database tables represented by the source database table identifiers in the second sub-table list are consistent; If they are consistent, a data warehouse table identifier corresponding to the source database table identifier is created in the data warehouse.

9. A data synchronization device, characterized in that: The device comprises: An acquisition module is used to acquire log data of a source database and determine data change information of a source database table of a current batch according to the log data; A determination module, for, if the source database table represented by the source database table identifier in the data change information is a sub-table, detecting whether the main table identifier corresponding to the source database table identifier is in the data warehouse; when the main table identifier corresponding to the source database table identifier is in the data warehouse, obtaining a first sub-table list corresponding to the source database table identifier from a configuration file; determining the data warehouse table identifier corresponding to the source database table identifier according to the first sub-table list; determining the incremental data corresponding to the data warehouse table identifier according to the data change content corresponding to the source database table identifier; wherein the first sub-table list refers to the sub-table list stored in the configuration file; the sub-table list records the various source data table identifiers belonging to the same main table identifier; the determination module is further used to determine the incremental data of the current batch of the data warehouse according to the incremental data corresponding to the data warehouse table identifier; The generation module is used to obtain the full data of the data warehouse of the previous batch, and generate the full data of the data warehouse of the current batch based on the full data of the data warehouse of the previous batch and the incremental data of the data warehouse of the current batch.

10. A storage medium storing a computer program, wherein when the computer program is executed by a processor, the processor is caused to perform the steps of the method according to any one of claims 1 to 8.

11. A computer device comprising a memory and a processor, wherein the memory stores a computer program, and when the computer program is executed by the processor, the processor executes the steps of the method according to any one of claims 1 to 8.

Images