A data synchronization method and device based on table structure relationship

Abstract

The present invention discloses a data synchronization method and device based on table structure relationships, including: a first system obtains a log record of a second system for a second database; for any log record, based on an association relationship configuration, determines whether a first data table in the log record is a first child table or a first parent table, and obtains a second data table that has an association relationship with the first child table or the first parent table; wherein the association relationship configuration includes data tables with an association relationship in the second database and associated field names in the data tables with an association relationship, synchronizes data on the first data table based on the log record; and synchronizes data on the second data table based on the associated field name information in the log record. In this way, the second data table that has an association relationship with the first data table in the log record can be determined through a log record, thereby improving the comprehensiveness of the second data table to be modified by the first system, reflecting the table structure relationship, and improving the efficiency of data synchronization.

Description

Technical Field

[0001] The present invention relates to the field of financial technology (Fintech), and in particular to a data synchronization method and device based on table structure relationships. Background Art

[0002] With the development of computer technology, more and more technologies (such as blockchain, cloud computing or big data) are being applied in the financial field. The traditional financial industry is gradually transforming into financial technology, and big data technology is no exception. However, due to the security and real-time requirements of the financial and payment industries, higher requirements are also placed on the indexing methods in big data technology.

[0003] At present, the index generated by the first system corresponds to the data table in the second system, that is, one data table corresponds to one index. In the second database corresponding to the second system, when the data in the data table changes, the second database will generate a log record, such as a binary log, that is, a binlog log. In other words, one log record is equivalent to a binlog log. In order to keep the data in the first database of the first system and the second database of the second system consistent, the second system will send the binlog log to the first system so that the first system can make corresponding changes to the data table of the first database based on the binlog log.

[0004] However, because one data table corresponds to one index, a binlog can only modify one data table in the first database. It is not possible to modify all data tables in the first database that are structurally associated with the data table recorded in the binlog through a single binlog. Therefore, it is not possible to reflect the table structure relationship in the database. For example, a binlog includes data table information a (such as table name), field name b (such as "user name"), and statement instruction c (change the user name "Zhang*" to "Li*"). If the first system includes two data tables A and B, both of which include field name b, then a binlog cannot be used to modify the field value c of field name b in the two tables A and B. Only the table to which the binlog corresponds to the index (such as the table to which the binlog corresponds to the index is A) can be modified.

[0005] To sum up, the existing technology fails to reflect the table structure relationship in the database. The first system is not comprehensive enough when indexing, and multiple binlog logs are required to complete the modification of the indexed table. When ensuring the data consistency of the databases corresponding to the first system and the second system, the efficiency is low and the resource consumption is large. Therefore, there is a need for an indexing method that can realize the modification of the data table in the first database that has an associated relationship with the log record based on a binlog log, thereby improving the efficiency of maintaining the data consistency of the databases corresponding to the first system and the second system. Summary of the Invention

[0006] An embodiment of the present invention provides a data synchronization method and device based on table structure relationships, which is used to modify a data table in a first database that has an associated relationship with a log record based on a log record, reflect the table structure relationship, and improve the efficiency of data synchronization.

[0007] In a first aspect, an embodiment of the present invention provides a data synchronization method based on table structure relationships, comprising:

[0008] The first system obtains log records of the second system for the second database; the first database in the first system and the second database are associated with data tables through indexes;

[0009] For any log record, the first system determines whether the data tables with associated relationships in the associated relationship configuration include the first data table in the log record; the associated relationship configuration includes the data tables with associated relationships in the second database and the associated field names in the data tables with associated relationships;

[0010] If included, the first system determines, according to the association relationship configuration, that the first data table in the log record is the first child table or the first parent table, and obtains a second data table that has an association relationship with the first child table or the first parent table;

[0011] The first system synchronizes data on a first data table in the first database based on the log record; and synchronizes data on a second data table in the first database based on information about associated field names in the log record.

[0012] The present invention determines the second data table of the data to be changed through an association relationship configuration including a second database table structure relationship. Specifically, according to the data table with an association relationship in the second database included in the association relationship configuration and the associated field name in the data table with an association relationship, where the association relationship is a parent-child table relationship, it can be determined whether the identifier (such as the table name) of the first data table in the log record exists in the association relationship configuration, as well as the role of the first data table (the first child table or the first parent table), and then obtain the second data table of the data to be changed that has an association relationship with the first child table or the first parent table, and then synchronize the data of the second data table, so as to achieve through a log record, in the first system database, determining one or more second data tables to be changed that have an association relationship with the first data table of the log record, thereby improving the comprehensiveness of the second data tables to be changed in the first system and reflecting the table structure relationship. After determining the second data table to be changed, data synchronization is performed on the second data table to improve the efficiency of data synchronization.

[0013] Optionally, for any log record, if included, the first system determines, based on the association relationship configuration, that the first data table in the log record is the first child table or the first parent table, and obtains a second data table associated with the first child table or the first parent table, including:

[0014] If the first system determines that the associated data table includes the first data table in the log record, then the first system determines that the field name in the log record is included in the associated field name in the associated data table;

[0015] The first system determines, based on the field name and the associated field name in the associated data table, that the first data table is the first child table or the first parent table;

[0016] The first system determines a second data table that has an association relationship with the first child table or the first parent table according to data tables that have an association relationship in the second database.

[0017] In the above technical solution, the association relationship is that of a parent-child table, such as Table A being the child or parent of Table B. First, the first data table is determined to be the first child table or the first parent table. Then, based on the data table identifier, the second data table associated with the first child table or the first parent table is determined. In other words, the parent table of the first child table or the child table of the first parent table is determined to be the second data table. This demonstrates the table structure relationship and improves the comprehensiveness of the second data table to be modified in the first system.

[0018] Optionally, the first system determines, based on data tables in the second database that have an associated relationship, a second data table associated with the first child table or the first parent table, including:

[0019] Based on the field names in the log record, when the first system determines that the first data table is the first child table, the first system determines a second parent table of the first child table according to data tables in the second database that have an associated relationship, and determines the second parent table as the second data table;

[0020] The first system determines, based on the data tables in the second database that have an associated relationship, whether there is another data table associated with the second parent table; if so, determines the other data table associated with the second parent table as the second data table;

[0021] When the first system determines that the first data table is the first parent table, it determines a second child table of the first parent table based on the data tables in the second database that have an associated relationship, and determines the second child table as the second data table;

[0022] The first system determines whether there is another data table associated with the second subtable based on the associated data tables in the second database; if so, the other data table associated with the second subtable is determined as the second data table.

[0023] In the above technical solution, for any first data table, the existing association relationship may include many layers. For example, if there is a parent table in the first data table, the parent table of the first data table also includes a parent table, that is, the parent table of the parent table of the first data table, which is similar to the relationship between three generations of grandparents and grandchildren. Therefore, after determining the parent table or child table of the first data table, the parent table or child table of the parent table or child table is determined according to its parent table or child table, until all second data tables with associated relationships are determined, thereby improving the comprehensiveness of the second data tables to be changed in the first system.

[0024] Optionally, the association relationship configuration is obtained in the following manner, including:

[0025] The first system receives a configuration file sent by the second system; the configuration file is determined by the second system based on associations between data tables in the second database and associated field names of the data tables in the second database; if a primary key field of a third data table in the second database is a field of a fourth data table in the second database, then determining that an association exists between the third data table and the fourth data table, and determining the primary key field as the associated field name of the fourth data table;

[0026] The first system determines the association relationship configuration according to the configuration file.

[0027] In the above technical solution, the association relationship configuration is determined according to the configuration file, which reflects the table structure relationship of the second database. Specifically, whether an association relationship exists is determined according to the field, and the associated field is determined according to the primary key, thereby reflecting the table structure relationship.

[0028] Optionally, the configuration file is a relationship diagram of each data table in the second database determined by the second system through the first modeling tool; or a relationship code of each data table in the second database determined by the second system through the second modeling tool; or a relationship table determined by the second system based on the relationship between the data tables, the associated field names of the data tables and the file generation tool.

[0029] In the above technical solution, three forms of configuration files are given. For the relationship diagram, the table structure relationship is vividly reflected. For the relationship code, it is convenient for the first system to analyze. For the relationship table, the table structure relationship is intuitively reflected. The first system can determine the association relationship configuration for any of the above forms of configuration files, thereby increasing the flexibility of determining the association relationship configuration.

[0030] Optionally, before the first system synchronizes data on the first data table in the first database based on the log record, the system further includes:

[0031] Storing the log record in a cache queue;

[0032] Before synchronizing data on the second data table in the first database based on the information of the associated field name in the log record, the method further includes:

[0033] A preset number of the log records are obtained from the cache queue.

[0034] In the above technical solution, log records are obtained in the cache queue. By obtaining a preset number of log records, the first system can avoid requesting log records from the cache queue multiple times, reducing the number of interactions between the first system and the cache queue and reducing resource consumption.

[0035] Optionally, if the first system fails to synchronize data with the first data table in the first database, the log record is written into the third database;

[0036] The first system determines whether the log record is successfully written into the third database, and if so, executes the log record in the third database according to a preset time;

[0037] If not, the first system rolls back the offset in the cache queue to the log record.

[0038] In the above technical solution, in the event of abnormal situations such as write failure, when writing log records to the first database fails, the log records are stored in the third database to prevent the loss of log records, and then the log records are retried according to the preset time to increase the success rate of data synchronization. If writing log records to the third database fails, it proves that the third database is abnormal, and the offset in the cache queue is rolled back to the log record to prevent the loss of log records and abnormal data synchronization.

[0039] In a second aspect, an embodiment of the present invention provides a data synchronization device based on table structure relationships, comprising:

[0040] An acquisition module is used to acquire log records of the second system for the second database; the first database in the first system and the second database are associated with data tables by indexing;

[0041] A processing module is configured to determine, for any log record, whether the data tables with associated relationships in the associated relationship configuration include the first data table in the log record; the associated relationship configuration includes the data tables with associated relationships in the second database and associated field names in the data tables with associated relationships;

[0042] If included, determining, according to the association relationship configuration, that the first data table in the log record is the first child table or the first parent table, and obtaining a second data table associated with the first child table or the first parent table;

[0043] Data synchronization is performed on the first data table in the first database based on the log record; and data synchronization is performed on the second data table in the first database based on information about associated field names in the log record.

[0044] Optionally, the processing module is specifically configured to:

[0045] If it is determined that the data table with the associated relationship includes the first data table in the log record, then determining that the field name in the log record is included in the associated field name in the data table with the associated relationship;

[0046] Determine whether the first data table is a first child table or a first parent table according to the field name and the associated field name in the associated data table;

[0047] A second data table having an association relationship with the first child table or the first parent table is determined according to the data tables having an association relationship in the second database.

[0048] Optionally, the processing module is specifically configured to:

[0049] Based on the field names in the log record, when the first system determines that the first data table is the first child table, the first system determines a second parent table of the first child table according to data tables in the second database that have an associated relationship, and determines the second parent table as the second data table;

[0050] The first system determines, based on the data tables in the second database that have an associated relationship, whether there is another data table associated with the second parent table; if so, determines the other data table associated with the second parent table as the second data table;

[0051] When the first system determines that the first data table is the first parent table, it determines a second child table of the first parent table based on the data tables in the second database that have an associated relationship, and determines the second child table as the second data table;

[0052] The first system determines whether there is another data table associated with the second subtable based on the associated data tables in the second database; if so, the other data table associated with the second subtable is determined as the second data table.

[0053] Optionally, the processing module is specifically configured to:

[0054] receiving a configuration file sent by the second system; the configuration file is determined by the second system based on associations between data tables in the second database and associated field names of the data tables in the second database; wherein, if a primary key field of a third data table in the second database is a field of a fourth data table in the second database, determining that an association exists between the third data table and the fourth data table, and determining the primary key field as the associated field name of the fourth data table;

[0055] The association relationship configuration is determined according to the configuration file.

[0056] Optionally, the configuration file is a relationship diagram of each data table in the second database determined by the second system through the first modeling tool; or a relationship code of each data table in the second database determined by the second system through the second modeling tool; or a relationship table determined by the second system based on the relationship between the data tables, the associated field names of the data tables and the file generation tool.

[0057] Optionally, the processing module is further configured to:

[0058] Before synchronizing data on the first data table in the first database based on the log record, storing the log record in a cache queue;

[0059] Based on the information of the associated field name in the log record, before synchronizing data on the second data table in the first database, a preset number of the log records are obtained from the cache queue.

[0060] In a third aspect, an embodiment of the present invention further provides a computer device, including:

[0061] a memory for storing program instructions;

[0062] The processor is used to call the program instructions stored in the memory and execute the above-mentioned data synchronization method based on table structure relationship according to the obtained program.

[0063] In a fourth aspect, an embodiment of the present invention further provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and the computer-executable instructions are used to enable a computer to execute the above-mentioned data synchronization method based on table structure relationship. BRIEF DESCRIPTION OF THE DRAWINGS

[0064] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0065] Figure 1 A schematic diagram of a system architecture provided by an embodiment of the present invention;

[0066] Figure 2 A flowchart of a data synchronization method based on table structure relationships provided by an embodiment of the present invention;

[0067] Figure 3 A schematic diagram of a data table provided in an embodiment of the present invention;

[0068] Figure 4 A schematic diagram of a relationship diagram provided by an embodiment of the present invention;

[0069] Figure 5 A schematic diagram of a relationship code provided by an embodiment of the present invention;

[0070] Figure 6 A schematic diagram of a relationship table provided in an embodiment of the present invention;

[0071] Figure 7 A schematic diagram of an association relationship configuration provided by an embodiment of the present invention;

[0072] Figure 8 A flowchart of a data synchronization method based on table structure relationships provided by an embodiment of the present invention;

[0073] Figure 9 A schematic diagram of the structure of a data synchronization device based on table structure relationships provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0074] To make the objectives, technical solutions, and advantages of the present invention more apparent, the present invention will be further described in detail below with reference to the accompanying drawings. It is apparent that the embodiments described are only some, not all, of the present invention. All other embodiments derived by persons of ordinary skill in the art based on the embodiments of the present invention without creative effort are intended to fall within the scope of protection of the present invention.

[0075] In a distributed system architecture, the data in the tables of databases corresponding to each system is consistent. When a table in one database changes, a log record is generated to indicate the corresponding change in tables in other databases. The tables in each database are linked by indexes. Log records include binary logs, or binlogs.

[0076] For example, a log record includes the table name, field name, key name, and new data where data has changed. The table name is used to locate the data table where data has changed, and the field name and key name are used to locate the cell position where data has changed in the data table. For example, if the table name is A, the field name is a1, and the key name is a`1, then the cell position is in the A data table, the column with the field name a1, and the row with the key name a`1.

[0077] In existing data, a table name in a log record can only index one corresponding table. For example, in the first database A and the second database B, when the data table A in the second database B changes, the data table A in the first database A is changed through the index, and other data tables cannot be changed. For example, if data table A has an associated table B, that is, data table A and data table B include the same field name, then only data table A will be changed according to the log record, and data table B will not be changed. This can neither reflect the structural relationship of the data tables nor fully index other data tables that have an associated relationship with the data table in the log record.

[0078] Therefore, a data synchronization method is now needed to reflect the table structure relationship, realize the modification of the data table in the first database that has an associated relationship with the log record according to a log record, and improve the efficiency of data synchronization.

[0079] The index may be implemented by ES (Elasticsearch, a search and data analysis engine), that is, the first database may be an Elasticsearch database. The embodiment of the present invention only provides an implementable method and does not specifically limit this.

[0080] Figure 1 A system architecture applicable to an embodiment of the present invention is exemplarily shown. The system architecture includes a server 100 . The server 100 may include a processor 110 , a communication interface 120 , and a memory 130 .

[0081] The communication interface 120 is used to enable each system to transmit log records.

[0082] The processor 110 is the control center of the server 100. It connects various parts of the server 100 using various interfaces and routes. It executes various functions of the server 100 and processes data by running or executing software programs and / or modules stored in the memory 130 and calling data stored in the memory 130. Optionally, the processor 110 may include one or more processing units.

[0083] Memory 130 can be used to store software programs and modules. Processor 110 executes various functional applications and data processing by running the software programs and modules stored in memory 130. Memory 130 may primarily include a program storage area and a data storage area. The program storage area may store an operating system, at least one application required for a function, and the data storage area may store data generated based on business processing. Memory 130 may also include high-speed random access memory and non-volatile memory, such as at least one disk storage device, flash memory device, or other volatile solid-state memory device.

[0084] It should be noted that the above Figure 1 The structure shown is only an example and is not limited in the embodiment of the present invention.

[0085] Based on the above description, Figure 2 A flowchart of a data synchronization method based on table structure relationships provided by an embodiment of the present invention is exemplarily shown. The flowchart can be executed by a data synchronization device based on table structure relationships.

[0086] like Figure 2 As shown in the figure, the process specifically includes:

[0087] Step 210: The first system obtains the log records of the second system for the second database.

[0088] In an embodiment of the present invention, the first database in the first system associates data tables with the second database through indexes. The first system may first store the log records sent by the second system in a cache queue, and then the first system loads the log records in the cache queue.

[0089] Step 220: For any log record, the first system determines whether the data tables with associated relationships in the association relationship configuration include the first data table in the log record.

[0090] In an embodiment of the present invention, the association relationship configuration includes the data tables with association relationships in the second database and the associated field names in the data tables with association relationships. The association relationship configuration is determined by the first system based on the configuration file sent by the second system, and the configuration file includes the table structure relationship in the second database.

[0091] Step 230: If included, the first system determines that the first data table in the log record is the first child table or the first parent table according to the association relationship configuration, and obtains a second data table that has an association relationship with the first child table or the first parent table.

[0092] In an embodiment of the present invention, among data tables with an association relationship, the association relationship refers to the relationship between a parent table and a corresponding child table. Thus, if the first data table is determined to be the first child table, the parent table of the first child table is determined to be the second data table. If the first data table is determined to be the first parent table, the child table of the first parent table is determined to be the second data table.

[0093] In step 240 , the first system synchronizes data on the first data table in the first database based on the log record; and synchronizes data on the second data table in the first database based on the associated field name information in the log record.

[0094] In the embodiment of the present invention, after determining the second data table, the first system synchronizes data on the second data table based on the content of the log record to ensure data consistency between the first database and the second database.

[0095] In step 210 , for obtaining log records, one practicable approach is that the first system requests log records from the second system one by one, for example, requests the first log record, and after the first log record is processed, requests the second log record from the second system.

[0096] Another feasible method is to store the log records sent by the second system in the cache log records, and then the first system requests log records from the cache log records one by one. For example, the second system generates two log records and sends them to the first system. The first system stores the two log records in the cache log records, and then requests log records from the cache log records one by one.

[0097] In an embodiment of the present invention, before synchronizing data of a first data table in a first database based on log records, the first system stores the log records in a cache queue. This is equivalent to storing the log records in the cache queue before retrieving the log records, and then retrieving a preset number of the log records from the cache queue. This reduces the number of requests to the cache queue for log records and reduces resource consumption.

[0098] In step 220, the association configuration is determined based on a configuration file, which is determined by the second system. Specifically, the first system receives the configuration file from the second system and then determines the association configuration based on the configuration file. The configuration file is determined by the second system based on the associations between the data tables in the second database and the associated field names of the data tables in the second database.

[0099] Furthermore, if the primary key field of the third data table in the second database is a field of the fourth data table in the second database, it is determined that there is an association relationship between the third data table and the fourth data table, and the primary key field is determined as the associated field name of the fourth data table.

[0100] For example, Figure 3 A schematic diagram of a data table is shown as an example. Figure 3 As shown, the second database includes three data tables, namely the author table, the book table and the chapter table. Among them, the field name (author code) in the book table (equivalent to the fourth data table) is the primary key field (author code) of the author table (equivalent to the third data table). Therefore, there is an association relationship between the book table and the author table, and for the book table, its field name "author code" is the associated field name.

[0101] For example, for the author table, the field name (book code) in the author table (equivalent to the fourth data table) is the primary key field (book code) of the book table (equivalent to the third data table), then there is an association relationship between the book table and the author table, and for the author table, its field name "book code" is the associated field name.

[0102] For example, for the chapter table, the field name (book code) in the chapter table (equivalent to the fourth data table) is the primary key field (book code) of the book table (equivalent to the third data table), then there is an association relationship between the book table and the chapter table, and for the chapter table, its field name "book code" is the associated field name.

[0103] In one practicable manner, if the fields of the third data table in the second database are the same as the fields of the fourth data table in the second database, it is determined that there is an association relationship between the third data table and the fourth data table, and the same fields are determined as the associated field names of the data tables.

[0104] The configuration file can be in different formats, such as diagrams, tables, and codes.

[0105] Furthermore, the configuration file is a relationship diagram of each data table in the second database determined by the second system through the first modeling tool; or a relationship code of each data table in the second database determined by the second system through the second modeling tool; or a relationship table determined by the second system based on the association relationship between each data table, the associated field name of each data table and the file generation tool.

[0106] The first modeling tool may be PowerDesigner (data modeling), Figure 4 A schematic diagram of a relationship diagram is shown as an example. Figure 4 As shown, there is an association between the author table and the book table, and there is an association between the book table and the chapter table.

[0107] The second modeling tool can be Enterprise Architect (modeling software), Figure 5 A schematic diagram of a relationship code is shown as an example. Figure 5 As shown in the figure, the chapter table includes the non-associated fields chapter code and chapter name, and the associated field book code. Therefore, the chapter table is associated with the data table. The book table includes the non-associated fields book code and book name, and the associated field author code. Therefore, the book table is associated with the author table. The author table includes the non-associated fields author code and author name, and the associated field book code. Therefore, the author table is associated with the book table.

[0108] Figure 6 A schematic diagram of a relationship table is shown as an example. Figure 6 As shown in the figure, it includes the author table, book table, and chapter table. The author table is associated with the book table, and the associated field of the author table is called book code. The book table is associated with the author table, and the associated field of the book table is called author code. The chapter table is associated with the book table, and the associated field of the chapter table is called book code.

[0109] In an embodiment of the present invention, the first system determines the association relationship configuration after preprocessing the configuration file. Specifically, after receiving the configuration file, the first system performs preprocessing methods such as program identification, reading, processing, refining the association relationship, and refining the association field name to determine the association relationship configuration, wherein the preprocessing method depends on the form of the configuration file.

[0110] For example, if the configuration file is a relationship graph, the relationship and associated field names can be determined by extracting the relationship and associated field names. If the configuration file is a relationship code, the relationship and associated field names can be determined through program recognition. If the configuration file is a relationship table, the relationship and associated field names can be determined by reading, processing, table segmentation, extracting the relationship, and extracting the associated field names.

[0111] For the association relationship configuration, in the embodiment of the present invention, the association relationship is determined as the relationship between the parent and child tables. Figures 4 to 6 For example, Figure 7 An exemplary diagram of an association relationship configuration is shown, as shown in FIG. Figure 7 As shown, for the book table, in the association relationship between the book table and the chapter table, the book table is the parent table, the chapter table is the child table, and the association field is named "book code". In the association relationship between the book table and the author table, the book table is the parent table, the author table is the child table, and the association field is named "book code".

[0112] For the author table, in the association relationship between the author table and the book table, the author table is the parent table, the book table is the child table, and the association field is named "author code".

[0113] In one feasible method, the association relationship configuration can also be the index corresponding to the data table. For example, in the association relationship between the author table and the book table, the index of the author table is used as the parent table, the index of the book table is used as the child table, and the associated field is named "author code". Other tables will not be described here, and no specific limitations are given.

[0114] In one practicable manner, programmers can customize comments for the associated field names of any data table. Figure 5 For example, according to the table structure relationship code in the second database, the programmer makes a comment description "$$BOOK; BOOK_CODE" in the author table code "COMMENT" to indicate that there is an association relationship between the author table and the book table, and the association field is named "Book Code".

[0115] According to the above Figure 6 For example, after generating a table according to the table structure in the second database, the programmer makes a note in the rows and columns of the chapter table describing "Book table: Book code" to indicate that there is an association relationship between the chapter table and the book table, and the association field is named "Book code".

[0116] In the embodiment of the present invention, a technical solution is provided for determining association relationships and associated field names through primary keys. In another feasible manner, association relationships and associated field names may also be determined based on foreign keys and other associated keys, which is not specifically limited here.

[0117] After determining the association relationship configuration, the first system can directly determine, based on the identifier (such as the table name) of the first data table, whether the data tables with association relationships in the association relationship configuration include the first data table in the log record.

[0118] In the embodiment of the present invention, Figure 7 It can be seen that the association relationship configuration includes the data tables with association relationships and the associated field names. Therefore, it can be determined based on the table name of the first data table whether the field name of the log is included in the association relationship configuration. In an implementable method, it can also be determined directly based on the field name whether the first data table is included in the association relationship configuration.

[0119] In step 230, if it is determined that the data tables with an associated relationship include the first data table, then it is determined that the field name in the log record is included in the associated field name in the data table with an associated relationship, and then the specific role of the first data table in the associated relationship is determined based on the field name, and then other data tables with an associated relationship are determined.

[0120] Furthermore, the first system determines that the first data table is the first child table or the first parent table based on the field name and the associated field name in the data table with the associated relationship, and determines the second data table with an associated relationship with the first child table or the first parent table based on the data table with the associated relationship in the second database.

[0121] According to the above Figure 7 For example, if the log record is: "Book table, book code, code number 3, xx", it means that in the second database, the book code column of the book table, the cell information corresponding to the row with code number 3 is changed to "xx".

[0122] According to the table name "Book Table", it can be determined that the "Book Table" is both a parent table and a child table in the association relationship configuration, and then the "Author Table" and "Chapter Table" that have an association relationship with the "Book Table" can be determined.

[0123] For the table structure, it may include many layers of association relationships, such as data table A is the parent table of data table B, data table B is the parent table of data table C, data table C is the parent table of data table D, and so on. It may include many layers of association relationships. Therefore, in an embodiment of the present invention, based on the field name of the log record, it is necessary to determine it again through the determined child table or parent table until all data tables with association relationships are determined.

[0124] Specifically, based on the field names in the log records, when the first system determines that the first data table is the first child table, it determines the second parent table of the first child table based on the data tables in the second database that have an associated relationship, determines the second parent table as the second data table, and then determines whether there are other data tables that have an associated relationship with the second parent table based on the data tables in the second database that have an associated relationship. If so, the other data tables that have an associated relationship with the second parent table are determined as the second data table.

[0125] When the first system determines that the first data table is the first parent table, it determines the second child table of the first parent table based on the data tables with an associated relationship in the second database, determines the second child table as the second data table, and then determines whether there are other data tables with an associated relationship with the second child table based on the data tables with an associated relationship in the second database. If so, the other data tables with an associated relationship with the second child table are determined as the second data table.

[0126] For example, the field name of the log record is s. If the first data table x of the log record is determined to be the parent table, the child table y of the first data table x is determined in the association relationship configuration, and then the child table of table y is determined to be table z in the association relationship configuration based on table y. It should be noted that the associated field names of tables x, y and z are all s. Therefore, based on the field name of the log, all data tables that have an association relationship with the data table corresponding to the field name are determined.

[0127] In step 240 , after determining one or more second data tables, the first system synchronizes data on the second data tables according to the content in the log record.

[0128] If the data is synchronized, the log record needs to be handled exceptionally. Specifically, if the first system fails to synchronize the data of the first data table in the first database, the log record is written to the third database, and it is determined whether the log record is successfully written to the third database. If so, the log record in the third database is executed according to the preset time; if not, the offset in the cache queue is rolled back to the log record.

[0129] In an embodiment of the present invention, the third database can be any database with storage function, such as a TiDB database, which is used to prevent the loss of log records.

[0130] For example, the cache queue includes a1, a2, ... a10, a total of ten log records. If data synchronization fails due to ES downtime or other conditions when executing log record a7, that is, the log record fails to be written to the first database, then the first system stores log record a7 in the third database, and then executes the next log record (a8), and after 10 minutes (preset time), re-attempts data synchronization with the log records (a7, etc.) in the third database, that is, writes log record a7 to the first database again.

[0131] Taking the above example again, when the storage of log record a7 to the third database fails due to a situation such as the third database crashing, the offset of the cache queue is rolled back to log record a7 (when it is determined that the a7 data synchronization fails, the offset is the next log record a8) to prevent the loss of log records.

[0132] In one possible scenario, if the offset rollback fails due to a cache queue crash, an alert message is issued to notify staff and prevent log loss. The alert message can be in the form of text, voice, or other formats, which are not specifically limited here.

[0133] In order to better explain the above technical solution, Figure 8 The following is a flow chart showing a data synchronization method based on table structure relationship. Figure 8 As shown, the process includes:

[0134] Step 810: Obtain a configuration file and determine the association relationship configuration.

[0135] Receive a configuration file sent by the second system, such as a relationship diagram, a relationship code, or a relationship table, and pre-process the configuration file to obtain an association relationship configuration.

[0136] Step 820: Obtain the first data table of the log record.

[0137] Before obtaining the log records, the log records are stored in Kafka (cache queue). The first system sends a request to Kafka to obtain a preset number of log records, stores them in a storage unit, and then processes the log records one by one.

[0138] Step 830 , determine whether there is an association relationship, if so, execute step 840 , otherwise execute step 850 .

[0139] Log records are processed one by one. For any log record, the first system determines whether the associated field name is in the association relationship configuration through the associated field name in the log record. If so, it is determined that there are other data tables that have an association relationship with the first data table of the log record. Otherwise, it is determined that there are no other data tables that have an association relationship with the first data table of the log record.

[0140] Step 840: Synchronize the first data table.

[0141] The first system indexes the first data table according to the table name of the log record, and synchronizes data on the first data table based on the content in the log record.

[0142] Step 850: Synchronize the first data table.

[0143] Step 860 , determining whether there are other data tables associated with the first data table. If so, executing step 870 and / or step 880 ; otherwise, executing step 830 to process the next log record.

[0144] Step 870: Determine the parent table of the second data table and perform data synchronization.

[0145] The first system determines the parent table x1 of the first data table in the association relationship based on the table name and associated field names of the first data table, and determines the child table y1 of the parent table x1 based on the associated field names of the first data table, until it is determined that there is no data table that has an association relationship with the first data table based on the associated field names of the first data table, and then synchronizes data on the parent tables x1, y1 and other data tables.

[0146] Step 880: Determine the sub-table of the second data table and perform data synchronization.

[0147] The first system determines sub-table x2 of the first data table in the association relationship based on the table name and associated field names of the first data table, and determines sub-table y2 of sub-table x2 based on the associated field names of the first data table, until it is determined that there is no data table that has an association relationship with the first data table based on the associated field names of the first data table, and then synchronizes data on sub-tables x2, y2 and other data tables.

[0148] In the embodiment of the present invention, if the first system determines that the content in the second data table to be changed is consistent with the log record content, the second data table will not be changed, because the log record will generate a log record when the data in any data table changes. Figure 3 Taking the data table as an example, changes in the book code data in the author table will generate log records for the author table, and changes in the book code data in the book table will generate log records for the book table. However, the first system will synchronize the "book code" data of the author table, book table, and chapter table for any log record, resulting in data synchronization for all data tables with associated relationships for the first processed log record. For the post-processed log record, data synchronization is no longer repeated, but the post-processed log record plays a verification role, preventing the first system from synchronizing all data for the data tables with associated relationships for the first processed log record.

[0149] Based on the same technical concept, Figure 9 The following is a schematic structural diagram of a data synchronization device based on table structure relationships provided by an embodiment of the present invention. The device can execute the above-mentioned data synchronization method based on table structure relationships.

[0150] like Figure 9 As shown, the device specifically includes:

[0151] The acquisition module 910 is configured to acquire log records of the second system for the second database; the first database in the first system and the second database are associated with data tables by indexing;

[0152] Processing module 920 is configured to determine, for any log record in the log record, a second data table associated with the first data table in the log record according to an association relationship configuration; the association relationship configuration includes the associated data table in the second database and the associated field name in the associated data table;

[0153] Data synchronization is performed on the first data table in the first database based on the log record; and data synchronization is performed on the second data table in the first database based on information about associated field names in the log record.

[0154] Optionally, the processing module 920 is specifically configured to:

[0155] Determine whether the data tables with association relationships in the association relationship configuration include the first data table in the log record;

[0156] If included, it is determined that the field name in the log record is included in the associated field name in the data table with the associated relationship.

[0157] Optionally, the processing module 920 is specifically configured to:

[0158] Determine whether the first data table is a first child table or a first parent table according to the field name and the associated field name in the associated data table;

[0159] A second data table having an association relationship with the first child table or the first parent table is determined according to the data tables having an association relationship in the second database.

[0160] Optionally, the processing module 920 is specifically configured to:

[0161] Based on the field names in the log record, when the first system determines that the first data table is the first child table, the first system determines a second parent table of the first child table according to data tables in the second database that have an associated relationship, and determines the second parent table as the second data table;

[0162] The first system determines, based on the data tables in the second database that have an associated relationship, whether there is another data table associated with the second parent table; if so, determines the other data table associated with the second parent table as the second data table;

[0163] When the first system determines that the first data table is the first parent table, it determines a second child table of the first parent table based on the data tables in the second database that have an associated relationship, and determines the second child table as the second data table;

[0164] The first system determines whether there is another data table associated with the second subtable based on the associated data tables in the second database; if so, the other data table associated with the second subtable is determined as the second data table.

[0165] Optionally, the processing module 920 is specifically configured to:

[0166] receiving a configuration file sent by the second system; the configuration file is determined by the second system based on associations between data tables in the second database and associated field names of the data tables in the second database; wherein, if a primary key field of a third data table in the second database is a field of a fourth data table in the second database, determining that an association exists between the third data table and the fourth data table, and determining the primary key field as the associated field name of the fourth data table;

[0167] The association relationship configuration is determined according to the configuration file.

[0168] Optionally, the configuration file is a relationship diagram of each data table in the second database determined by the second system through the first modeling tool; or a relationship code of each data table in the second database determined by the second system through the second modeling tool; or a relationship table determined by the second system based on the relationship between the data tables, the associated field names of the data tables and the file generation tool.

[0169] Optionally, the processing module 920 is further configured to:

[0170] Before synchronizing data on the first data table in the first database based on the log record, storing the log record in a cache queue;

[0171] Based on the information of the associated field name in the log record, before synchronizing data on the second data table in the first database, a preset number of the log records are obtained from the cache queue.

[0172] Based on the same technical concept, an embodiment of the present invention further provides a computer device, including:

[0173] a memory for storing program instructions;

[0174] The processor is used to call the program instructions stored in the memory and execute the above-mentioned data synchronization method based on table structure relationship according to the obtained program.

[0175] Based on the same technical concept, an embodiment of the present invention further provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and the computer-executable instructions are used to enable a computer to execute the above-mentioned data synchronization method based on table structure relationship.

[0176] Those skilled in the art will appreciate that the embodiments of the present application can be provided as methods, systems, or computer program products. Therefore, the present application can adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application can adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) that contain computer-usable program code.

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

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

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

[0180] Obviously, those skilled in the art may make various changes and modifications to this application without departing from the spirit and scope of this application. Thus, if these modifications and variations of this application fall within the scope of the claims of this application and their equivalents, this application is intended to include these modifications and variations.

Claims

1. A data synchronization method based on table structure relationship, characterized in that: include: The first system obtains the log record of the second system for the second database; The first database in the first system and the second database associate data tables by indexing; For any log record, the first system determines whether the data tables with association relationships in the association relationship configuration include the first data table in the log record; The association relationship configuration includes the data table in the second database that has the association relationship and the associated field name in the data table that has the association relationship; If included, the first system determines, according to the association relationship configuration, that the first data table in the log record is the first child table or the first parent table, and obtains a second data table that has an association relationship with the first child table or the first parent table; The first system synchronizes data on the first data table in the first database based on the log record; and synchronizing data on the second data table in the first database based on the information of the associated field name in the log record; If included, the first system determines, according to the association relationship configuration, that the first data table in the log record is the first child table or the first parent table, and obtains a second data table associated with the first child table or the first parent table, including: If the first system determines that the associated data table includes the first data table in the log record, then the first system determines that the field name in the log record is included in the associated field name in the associated data table; The first system determines, based on the field name and the associated field name in the associated data table, that the first data table is the first child table or the first parent table; The first system determines, based on the data tables in the second database that have an association relationship, a second data table that has an association relationship with the first child table or the first parent table; The first system determines, based on the data tables in the second database that have an association relationship, a second data table that has an association relationship with the first child table or the first parent table, including: Based on the field names in the log record, when the first system determines that the first data table is the first child table, the first system determines a second parent table of the first child table according to data tables in the second database that have an associated relationship, and determines the second parent table as the second data table; The first system determines, based on the data tables in the second database that have an associated relationship, whether there is another data table associated with the second parent table; if so, determines the other data table associated with the second parent table as the second data table; When the first system determines that the first data table is the first parent table, it determines a second child table of the first parent table based on the data tables in the second database that have an associated relationship, and determines the second child table as the second data table; The first system determines whether there is another data table associated with the second subtable based on the associated data tables in the second database; if so, the other data table associated with the second subtable is determined as the second data table.

2. The method according to claim 1, wherein The association relationship configuration is obtained by the following methods, including: The first system receives a configuration file sent by the second system; the configuration file is determined by the second system based on associations between data tables in the second database and associated field names of the data tables in the second database; if a primary key field of a third data table in the second database is a field of a fourth data table in the second database, then determining that an association exists between the third data table and the fourth data table, and determining the primary key field as the associated field name of the fourth data table; The first system determines the association relationship configuration according to the configuration file.

3. The method according to claim 2, wherein The configuration file is a relationship diagram of the data tables in the second database determined by the second system using the first modeling tool; or The relational codes of the data tables in the second database determined by the second system using a second modeling tool; or The second system determines a relationship table based on the relationship between the data tables, the associated field names of the data tables, and a file generation tool.

4. The method according to claim 1, wherein Before the first system synchronizes data on the first data table in the first database based on the log record, the method further includes: Storing the log record in a cache queue; Before synchronizing data on the second data table in the first database based on the information of the associated field name in the log record, the method further includes: A preset number of the log records are obtained from the cache queue.

5. The method according to any one of claims 1 to 4, characterized in that Also includes: If the first system fails to synchronize data with the first data table in the first database, the log record is written into the third database; The first system determines whether the log record is successfully written into the third database, and if so, executes the log record in the third database according to a preset time; If not, the first system rolls back the offset in the cache queue to the log record.

6. A data synchronization device based on table structure relationship, characterized in that: include: An acquisition module is used to acquire log records of the second system for the second database; the first database in the first system and the second database are associated with data tables by indexing; A processing module is configured to determine, for any log record, whether the data tables with associated relationships in the associated relationship configuration include the first data table in the log record; the associated relationship configuration includes the data tables with associated relationships in the second database and associated field names in the data tables with associated relationships; If included, determining, according to the association relationship configuration, that the first data table in the log record is the first child table or the first parent table, and obtaining a second data table associated with the first child table or the first parent table; Synchronize data on the first data table in the first database based on the log record; and synchronize data on the second data table in the first database based on information about associated field names in the log record; The processing module is specifically configured to: if it is determined that the data table with the associated relationship includes the first data table in the log record, determine that the field name in the log record is included in the associated field name in the data table with the associated relationship; Determine, based on the field name and the associated field name in the associated data table, that the first data table is a first child table or a first parent table; and determine, based on the associated data table in the second database, a second data table associated with the first child table or the first parent table. The processing module is specifically configured to: based on the field name in the log record, when the first system determines that the first data table is the first sub-table, determine the second parent table of the first sub-table according to the data tables in the second database that have an associated relationship, and determine the second parent table as the second data table; determining, based on the data tables in the second database that have an associated relationship, whether there are other data tables associated with the second parent table; and if so, determining the other data tables associated with the second parent table as the second data table; and when determining that the first data table is the first parent table, determining, based on the data tables in the second database that have an associated relationship, a second child table of the first parent table, and determining the second child table as the second data table; Determine whether there is another data table associated with the second sub-table based on the associated data tables in the second database; if so, determine the other data table associated with the second sub-table as the second data table.

7. A computer device, characterized in that: include: a memory for storing program instructions; A processor is configured to call the program instructions stored in the memory and execute the method according to any one of claims 1 to 5 according to the obtained program.

8. A computer-readable storage medium, characterized in that The computer-readable storage medium stores computer-executable instructions, and the computer-executable instructions are used to enable a computer to execute the method according to any one of claims 1 to 5.

Images