CN117349377A - Main external key table data synchronization method and system - Google Patents

Main external key table data synchronization method and system Download PDF

Info

Publication number
CN117349377A
CN117349377A CN202311293590.7A CN202311293590A CN117349377A CN 117349377 A CN117349377 A CN 117349377A CN 202311293590 A CN202311293590 A CN 202311293590A CN 117349377 A CN117349377 A CN 117349377A
Authority
CN
China
Prior art keywords
source data
data
master
data table
source
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Granted
Application number
CN202311293590.7A
Other languages
Chinese (zh)
Other versions
CN117349377B (en
Inventor
彭壮
戴博文
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Zhongdian Cloud Computing Technology Co ltd
Original Assignee
Zhongdian Cloud Computing Technology Co ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Zhongdian Cloud Computing Technology Co ltd filed Critical Zhongdian Cloud Computing Technology Co ltd
Priority to CN202311293590.7A priority Critical patent/CN117349377B/en
Publication of CN117349377A publication Critical patent/CN117349377A/en
Application granted granted Critical
Publication of CN117349377B publication Critical patent/CN117349377B/en
Active legal-status Critical Current
Anticipated expiration legal-status Critical

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/22—Indexing; Data structures therefor; Storage structures
    • G06F16/2282—Tablespace storage structures; Management thereof

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Databases & Information Systems (AREA)
  • Data Mining & Analysis (AREA)
  • Physics & Mathematics (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Computing Systems (AREA)
  • Software Systems (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

The invention relates to the technical field of data synchronization, and provides a method and a system for synchronizing data of a main external key table, wherein the method comprises the following steps: inquiring metadata of a data table in a database, and acquiring a master-slave relationship of a source data table according to the inquired metadata; configuring data synchronization tasks according to the master-slave relation of the acquired source data table, and performing priority operation arrangement on the configured data synchronization tasks; and carrying out issuing control and operation on the data synchronous tasks which are subjected to priority operation arrangement. The method can realize the visualization and automation of the data synchronization of the main external key table of the heterogeneous data source, and can remarkably improve the task configuration efficiency and the accuracy of the data synchronization in the data synchronization process of the main external key table.

Description

Main external key table data synchronization method and system
Technical Field
The invention relates to the technical field of data synchronization, in particular to a method and a system for synchronizing data of a main external key table.
Background
Along with the continuous development of informatization construction and the continuous improvement of the importance of users on the data value, more and more users begin to realize the great difficulty of mining, analyzing and utilizing data due to the phenomenon of data island caused by non-centralized business, non-intercommunication of flows and non-sharing of data. The first step of solving the problem of "data island" is to collect the data of each service system or each department, which are separated from each other, together, and establish a unified data acquisition, processing and sharing center, and one of the data processing methods used in the process is the data synchronization technology.
In order to ensure data integrity, a primary key and an external key are added to a data table in a database when the data table is designed. A primary key is one or more fields in a table that are used as unique identifiers in the table. If a field is not a primary key field in the current table, but is a primary key field in other tables than the current table, then the field is referred to as a foreign key field. The table where the primary key is located is called the primary table, and the table where the foreign key is located is called the secondary table. The main key can ensure the integrity of the entity and quicken the inquiry of the database, thereby facilitating the retrieval. By setting the external key of the data table, the consistency and the integrity of the data can be ensured at the database level. However, excessive use of the primary/foreign keys may result in increased development difficulty, resulting in an excessive number of tables; after the external key is set, related checking operation is needed to be carried out, so that the performance is affected; after the external key exists, because of the constraint of the external key, great difficulty can be generated for the synchronization of the data, and the sequence of the data addition and deletion needs to be ensured. When data is newly added in the master-slave table, the master table is required to be inserted first, and then the slave table is required to be inserted; when data deletion is performed in the master-slave table, the slave table needs to be deleted first, and then the master table needs to be deleted. In addition, the master-slave table has many-to-one, one-to-many and multi-level master-slave relation, and higher requirements are put on the data synchronization process. When data synchronization is performed on the main external key table, the following problems mainly exist: 1. the master-slave relationship in the source end and target end data table is required to be identified manually, the master-slave relationship is complex, and the work is complex; 2. after the master-slave relationship is combed, the upper and lower data synchronous tasks are required to be established respectively, the complicated task arrangement is carried out, the writing sequence is ensured artificially, the efficiency is low, and the error rate is high; 3. once the target end has changed the master-slave relationship, the data synchronization task needs to be reconfigured or task arrangement needs to be carried out again.
Therefore, how to provide a more efficient data synchronization method for the primary external key table becomes a technical problem to be solved.
Disclosure of Invention
In view of the above, the present invention aims to overcome the shortcomings of the prior art, and to provide a method for synchronizing data of a primary external key table.
According to a first aspect of the present invention, there is provided a primary foreign key table data synchronization method, including:
inquiring metadata of a data table in a database, and acquiring a master-slave relationship of a source data table according to the inquired metadata;
configuring data synchronization tasks according to the master-slave relation of the acquired source data table, and performing priority operation arrangement on the configured data synchronization tasks;
and carrying out issuing control and operation on the data synchronous tasks which are subjected to priority operation arrangement.
Preferably, in the method for synchronizing data of a primary external key table of the present invention, obtaining a master-slave relationship of a source data table according to queried metadata includes: and acquiring external key information in the queried metadata, acquiring the relation between the external key of the slave table and the main key of the main table according to the acquired external key information, and determining the relation between the main table and the slave table in the source data table according to the acquired relation between the external key of the slave table and the main key of the main table.
Preferably, in the method for synchronizing data of a master external key table of the present invention, the data synchronizing task is configured according to a master-slave relationship of an acquired source data table, including:
acquiring all source data tables in a database, and constructing a source data table column and a source data table selection column according to the acquired source data tables;
according to the master-slave relation of the source data table, matching a corresponding direct association master table for the source data table in the source data table column, and constructing an association master table column and a master table selection operation column according to the matched direct association master table;
and selecting the corresponding source data table and the directly-related main table through the source data table selection column and the main table selection operation column.
Preferably, in the method for synchronizing data of a master external key table of the present invention, a data synchronization task is configured according to a master-slave relationship of an acquired source data table, and the method further includes: when the target end data table has the main external key constraint, a master-slave relation of the target end data table is set according to the master-slave relation of the source end data table, the master table of the source end data table corresponds to the master table of the target end data table, the slave table of the source end data table corresponds to the slave table of the target end data table, and the corresponding target end data table is selected according to the master-slave relation of the source end data table.
Preferably, in the method for synchronizing data in a primary external key table of the present invention, priority operation arrangement is performed on a configured data synchronization task, including:
respectively constructing a source data table selection column and a direct association main table selection column according to the selected source data table and the direct association main table;
setting the initial priority of a source data table in a source data table selection column to be 1, and circularly traversing whether other source data tables exist in the source data table selection column of each source data table according to the sequence of the source data table in the source data table selection column, wherein the main table of the source data table which is circularly traversed currently;
when other source data tables exist and are the main tables of the source data tables traversed by the current cycle, updating the priority of the other source data tables to be the priority of the source data tables traversed by the current cycle by adding 1;
after the first cycle traversal of all the source data tables in the source data table selection column is completed, repeatedly carrying out the cycle traversal of all the source data tables in the source data table selection column in sequence according to the number of the source data tables in the source data table selection column.
Preferably, in the primary external key table data synchronization method of the present invention, after completing a first cycle of traversing all source data tables in a source data table selection column, repeatedly performing a cycle of traversing all source data tables in the source data table selection column in sequence according to the number of source data tables in the source data table selection column, including: and taking the value obtained by subtracting 1 from the total number of the source data tables in the source data table selection column as the repetition number of the cyclic traversal of all the source data tables in the source data table selection column.
Preferably, in the primary external key table data synchronization method of the present invention, after the cyclic traversal is repeatedly performed on all the source data tables in the source data table selection column in sequence for a certain time, no priority update occurs, and the cyclic traversal on the source data table selection column is ended.
Preferably, in the method for synchronizing data of a master external key table of the present invention, the issuing control and operation of the data synchronization task scheduled by priority operation include: and performing issuing control on the data synchronization task according to the priority, and writing the data of the slave table after the data of the master table is completely written.
Preferably, in the method for synchronizing data of a master external key table of the present invention, the issuing control and operation of the data synchronization task scheduled by priority operation include:
acquiring a data synchronization task to be issued according to priority scanning, and judging whether a main table exists in a source data table corresponding to the acquired data synchronization task to be issued;
when the source data table corresponding to the acquired data synchronization task to be issued does not have a main table, issuing and operating the data synchronization task;
judging the running state of the main table when the main table exists in the source data table corresponding to the data synchronization task to be issued, and issuing and running the data synchronization task corresponding to the main table when the running state of the main table is successful; and when the running state of the main table is not successful, canceling the issuing and running of the data synchronization task corresponding to the main table.
According to a second aspect of the present invention, there is provided a data synchronization system for a primary external key table, the system comprising a data synchronization server for querying metadata of a data table in a database, and obtaining a master-slave relationship of a source data table according to the queried metadata; configuring data synchronization tasks according to the master-slave relation of the acquired source data table, and performing priority operation arrangement on the configured data synchronization tasks; and carrying out issuing control and operation on the data synchronous tasks which are subjected to priority operation arrangement.
According to a third aspect of the present invention there is provided a computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, the processor implementing the method of the first aspect of the present invention when executing the program.
The main external key table data synchronization method and system have the following beneficial technical effects:
1. the master-slave relationship among the data tables is identified by inquiring the metadata of the data tables through the program, so that the efficiency of carding the master-slave relationship of the data tables is improved, the master-slave relationship of the data tables is displayed through a visual interface, the complex multi-level master-slave relationship among the data tables can be prompted, and the configuration of the data synchronization task is simplified;
2. the method supports the data synchronization task configuration of a plurality of master-slave tables in one data synchronization task, and the configured master-slave tables are set with different operation priorities through an algorithm, so that the task arrangement is automatically performed, the number of configuration tasks is reduced, the task arrangement is avoided, and the possibility of errors is reduced;
3. when the master-slave relation of the target end data table is changed, the data synchronization task does not need to be reconfigured or task arrangement is performed again, and the scheme for task arrangement according to the master-slave relation of the source end data table can be compatible with the scene;
4. the method can realize visualization and automation of data synchronization of the main external key table of the heterogeneous data source, and can remarkably improve task configuration efficiency and accuracy of data synchronization in the data synchronization process of the main external key table.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings that are needed in the embodiments will be briefly described below, and it is obvious that the drawings in the following description are only some embodiments of the present invention, and that other drawings can be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 is a schematic diagram of a system for a primary foreign key table data synchronization method according to an embodiment of the present invention;
fig. 2 is a diagram illustrating a structure of a data synchronization server in a data synchronization system of a primary external key table according to an embodiment of the present invention;
FIG. 3 is a flowchart illustrating a method for synchronizing data of a primary foreign key table according to an embodiment of the present invention;
FIG. 4 is a flowchart illustrating a method for performing issue control and operation on a priority operation scheduled data synchronization task according to an embodiment of the present invention;
fig. 5 is a schematic structural diagram of the apparatus provided by the present invention.
Detailed Description
Embodiments of the present invention will be described in detail below with reference to the accompanying drawings.
It should be noted that, without conflict, the following embodiments and features in the embodiments may be combined with each other; and, based on the embodiments in this disclosure, all other embodiments that may be made by one of ordinary skill in the art without inventive effort are within the scope of the present disclosure.
It is noted that various aspects of the embodiments are described below within the scope of the following claims. It should be apparent that the aspects described herein may be embodied in a wide variety of forms and that any specific structure and/or function described herein is merely illustrative. Based on the present disclosure, one skilled in the art will appreciate that one aspect described herein may be implemented independently of any other aspect, and that two or more of these aspects may be combined in various ways. For example, an apparatus may be implemented and/or a method practiced using any number of the aspects set forth herein. In addition, such apparatus may be implemented and/or such methods practiced using other structure and/or functionality in addition to one or more of the aspects set forth herein.
The invention provides a data synchronization method and a data synchronization system for a master key table and a slave key table, which are used for solving the problems of master-slave relation recognition and automatic task arrangement processing in the data synchronization process of heterogeneous data sources when master-slave relation tables exist. The technical principle of the invention is as follows:
1. analyzing the master-slave relationship of the data table by inquiring metadata information of the data table of the database, providing visual interface display, and displaying the master-slave relationship of the source table for selection by operators;
2. supporting batch data synchronization of data tables, and when master-slave relationships exist in a plurality of selected tables, setting the operation priority of each table through an algorithm, and automatically carrying out task arrangement;
3. task arrangement is carried out according to the selected main external key relation of the source end table, decoupling is carried out on the main external key relation and the main external key relation of the target end data table, and whether the main external key relation exists in the target end data table does not influence the operation sequence of data synchronization;
4. and controlling the data synchronization sequence of the data table according to the set operation priority, and ensuring that the slave table can start to operate after the operation of the master table is finished through reasonable task issuing design.
FIG. 1 illustrates an exemplary system suitable for use in the primary foreign key table data synchronization method of an embodiment of the invention. As shown in fig. 1, the system may include a data synchronization server 101, a communication network 102, and/or one or more data synchronization clients 103, which are illustrated in fig. 1 as a plurality of data synchronization clients 103.
The data synchronization server 101 may be any suitable server for storing information, data, programs, and/or any other suitable type of content. In some embodiments, the data synchronization server 101 may perform appropriate functions. For example, in some embodiments, the data synchronization server 101 may be used for primary foreign key table data synchronization. As an alternative example, in some embodiments, the data synchronization server 101 may be configured to obtain a master-slave relationship of a source data table according to metadata queried by querying metadata of the data table in the database; configuring data synchronization tasks according to the master-slave relation of the acquired source data table, and performing priority operation arrangement on the configured data synchronization tasks; and carrying out issuing control and operation on the data synchronous tasks which are subjected to priority operation arrangement.
As another example, in some embodiments, the data synchronization server 101 may send the primary foreign key table data synchronization method to the data synchronization client 103 for use by the user upon request of the data synchronization client 103.
Fig. 2 is a diagram illustrating a structure of a data synchronization server in a primary external key table data synchronization system according to an embodiment of the present invention, as shown in fig. 2, where the data synchronization server in this embodiment includes:
the metadata query module is used for querying metadata of the data table in the database;
the source end data table master-slave relation acquisition module is used for acquiring the master-slave relation of the source end data table according to the inquired metadata;
the data synchronization task configuration module is used for configuring data synchronization tasks according to the master-slave relation of the acquired source data table;
the data synchronization task arrangement and operation module is used for carrying out priority operation arrangement on the configured data synchronization task; and carrying out issuing control and operation on the data synchronous tasks which are subjected to priority operation arrangement.
As an alternative example, in some embodiments, the data synchronization client 103 is configured to provide a visual synchronization interface, where the visual synchronization interface is configured to receive a selection input operation for synchronizing data of the primary external key table of the user, and is configured to obtain, in response to the selection input operation, a synchronization interface corresponding to an option selected by the selection input operation from the data synchronization server 101 and display the synchronization interface, where at least information of synchronization of data of the primary external key table and operation options of information of synchronization of data of the primary external key table are displayed in the synchronization interface.
In some embodiments, communication network 102 may be any suitable combination of one or more wired and/or wireless networks. For example, the communication network 102 can include any one or more of the following: the internet, an intranet, a Wide Area Network (WAN), a Local Area Network (LAN), a wireless network, a Digital Subscriber Line (DSL) network, a frame relay network, an Asynchronous Transfer Mode (ATM) network, a Virtual Private Network (VPN), and/or any other suitable communication network. The data synchronization client 103 can be connected to the communication network 102 via one or more communication links (e.g., communication link 104), and the communication network 102 can be linked to the data synchronization server 101 via one or more communication links (e.g., communication link 105). The communication link may be any communication link suitable for transferring data between the data synchronization client 103 and the data synchronization server 101, such as a network link, a dial-up link, a wireless link, a hardwired link, any other suitable communication link, or any suitable combination of such links.
The data synchronization client 103 may include any one or more clients that present interfaces related to master foreign key table data synchronization in a suitable form for use and operation by a user. In some embodiments, the data synchronization client 103 may comprise any suitable type of device. For example, in some embodiments, the data synchronization client 103 may include a mobile device, a tablet computer, a laptop computer, a desktop computer, and/or any other suitable type of client device.
Although the data synchronization service 101 is illustrated as one device, in some embodiments any suitable number of devices may be used to perform the functions performed by the data synchronization service 101. For example, in some embodiments, multiple devices may be used to implement the functions performed by the data synchronization server 101. Alternatively, the functions of the data synchronization service 101 may be implemented using a cloud service.
Based on the above system, the embodiments of the present application provide a method for accessing an ORC external table at high speed, which is described in the following embodiments.
Referring to fig. 3, a flowchart of steps of a primary foreign key table data synchronization method according to an embodiment of the present invention is shown.
The data synchronization method of the primary external key table of the embodiment can be executed at a data synchronization server, and the data synchronization method of the primary external key table comprises the following steps:
step S201: inquiring metadata of a data table in the database, and acquiring a master-slave relationship of a source data table according to the inquired metadata.
In the method of the embodiment, in order to implement priority arrangement of the data synchronization task according to the master-slave relationship of the source data table, the premise is that the master-slave relationship of the source data table needs to be acquired.
As an optional example, the method of this embodiment obtains foreign key information in the queried metadata, obtains a relationship between a foreign key of the slave table and a primary key of the primary table according to the obtained foreign key information, and determines a relationship between the primary table and the slave table in the source data table according to the obtained relationship between the foreign key of the slave table and the primary key of the primary table.
The method of the embodiment can also display the relation between the master table and the slave table in the source data table through the master-slave relation acquisition module of the source data table. In the method of this embodiment, there is a foreign key field in the secondary table, the foreign key points to the primary key of its primary table, and for various relational databases, there is a corresponding metadata table to describe information about the foreign key.
For example, in practical applications, the REFERENTIAL _structures table of information_schema in MYSQL database provides INFORMATION about foreign keys. Table1 shows the REFERENTIAL _structures table fields and meanings in the MYSQL database.
TABLE1
Depending on the definition of foreign keys, one master table may have one or more slave tables, one slave table may have one or more master tables, and slave tables may be master tables of other tables, i.e., there is a multi-level master-slave relationship. Therefore, the method of the embodiment can fully present the master-slave relationship in various scenes by displaying the direct upper-lower relationship of the master-slave table. For example, the method of this embodiment may show all the data tables and the main tables thereof in the selected source database in the form of table 2.
TABLE2
Source end table Association master table
Table1 Table2
Table2 Table3,Table4
Table3 Table4
Table4
Table5
In Table2, the source data Table2 has two associated main tables, and the source data Table4 and Table5 have no associated main tables.
Step S202: and configuring data synchronization tasks according to the master-slave relation of the acquired source data table, and performing priority operation arrangement on the configured data synchronization tasks.
In the method of the embodiment, the data synchronization task needs to be configured according to the master-slave relation of the acquired source data table. As an optional example, the method of this embodiment needs to acquire all source data tables in the database, and constructs a source data table column and a source data table selection column according to the acquired source data tables; according to the master-slave relation of the source data table, matching a corresponding direct association master table for the source data table in the source data table column, and constructing an association master table column and a master table selection operation column according to the matched direct association master table; and selecting the corresponding source data table and the directly-related main table through the source data table selection column and the main table selection operation column.
In practical application, the number of tables in the source database of the user is huge, and it is a very complex and tedious matter to clear the master-slave relationship of each table from a large number of tables. After the master-slave relation of the source data table is obtained, the method designs a selection mode of the source data table according to the master-slave relation of the source data table.
The following describes in detail the data synchronization task configured according to the master-slave relationship of the acquired source data table in the method of this embodiment in a specific scenario.
A source data table selection list as shown in table3 was constructed.
TABLE3 Table3
As shown in table3, all source data tables in the database are shown in a source data table column, a direct upper-level associated main table corresponding to the source data table is shown in an associated main table column, and when one source data table is provided with a plurality of direct upper-level associated main tables, the associated main tables are shown in a comma separated mode; after the user selects the corresponding source data table in the 'selection' list, if the source data table has the associated main table, the user prompts whether to select the main table simultaneously in the 'operation' list, and after the 'simultaneous selection main table' is selected, the selection of the main table corresponding to the source data table in the 'source data table' list is completed.
In practical application, the method of the embodiment can guide operators to perform configuration of data synchronization tasks through a visual interface, and rapidly and efficiently select the source end table and the associated main table with the master-slave relationship, thereby avoiding spending a great deal of effort to manually comb the master-slave relationship among various data tables and avoiding omission of the data table with the master-slave relationship.
It should be noted that, in the method of this embodiment, when the target end data table has a primary external key constraint, a primary-secondary relationship of the target end data table is set according to a primary-secondary relationship of the source end data table, the primary table of the source end data table corresponds to the primary table of the target end data table, the secondary table of the source end data table corresponds to the secondary table of the target end data table, and the corresponding target end data table is selected according to the primary-secondary relationship of the source end data table.
When the target end list has a master-slave relationship, the selection of the target end data list is required according to the master-slave relationship of the source end data list, namely, the master list of the source end is required to correspond to the master list of the target end, and the slave list of the source end is required to correspond to the slave list of the target end. In this way, the writing sequence of the data table with the master-slave relationship at the target end is set according to the master-slave relationship of the data table at the source end. When the target end table has no master-slave relation, the external key constraint is not existed when the data is written into the target end table, namely, the priority order of the data synchronization of the data table is not required to be set, but in order to keep uniform logically, after the corresponding target end table is selected for the source end table, the actual writing order is set according to the master-slave relation of the source end data table.
For example, when the target table has a primary foreign key constraint, appropriate target tables need to be mapped to the source data table according to the primary-secondary relationship of the source data table as shown in table 4.
TABLE4 Table4
As shown in TABLE4, mb_table1 is an mb_table2 main TABLE, mb_table2 is an mb_table3 main TABLE, and the master-slave relationship is set with reference to the master-slave relationship of the source TABLE. When the target table does not have a primary foreign key constraint, the mapping of the source table and the target table may also be organized according to table 4. By the method of the embodiment, when the foreign key constraint of the target end is changed, such as when the foreign key constraint fails or is effective, the task does not need to be reconfigured, and good compatibility is achieved.
After the data synchronization task is configured, the method of the embodiment needs to perform priority operation arrangement on the configured data synchronization task. As an optional example, the method of this embodiment needs to construct a source data table selection column and a direct association master table selection column according to the selected source data table and the direct association master table, respectively; setting the initial priority of a source data table in a source data table selection column to be 1, and circularly traversing whether other source data tables exist in the source data table selection column of each source data table according to the sequence of the source data table in the source data table selection column, wherein the main table of the source data table which is circularly traversed currently; when other source data tables exist and are the main tables of the source data tables traversed by the current cycle, updating the priority of the other source data tables to be the priority of the source data tables traversed by the current cycle by adding 1; after the first cycle traversal of all the source data tables in the source data table selection column is completed, repeatedly carrying out the cycle traversal of all the source data tables in the source data table selection column in sequence according to the number of the source data tables in the source data table selection column.
It should be noted that, in the method of this embodiment, the value obtained by subtracting 1 from the total number of source data tables in the selected column of the source data table is used as the repetition number of the cyclic traversal for all the source data tables in the selected column of the source data table. After the cyclic traversal is repeatedly performed on all the source data tables in the source data table selection column in sequence for a certain time, priority updating does not occur, and the cyclic traversal on the source data table selection column is ended.
In the method of the embodiment, the master-slave table is to realize task operation and arrangement according to the master-slave sequence, and the core is to set the operation priority between the master-slave tables. In the following, in a specific scenario, the operation priority of the master-slave table set in the method of this embodiment will be described in detail.
In the scenario of this example, the source data table for which priority needs to be set is shown in table 5.
TABLE5
Source end table Association master table
Table1 Table3
Table2 Table4
Table3 Table2
Table4
As shown in Table5, in the present scenario, the source data tables are Table1, table2, table3 and Table4, respectively, table3 is a Table1 main Table, table4 is a Table2 main Table, and Table2 is a Table3 main Table.
As shown in table 6, it is first assumed that all source data tables have priority of 1.
TABLE 6
Table1 Table2 Table3 Table4
1 1 1 1
And sequentially traversing each source data table in a circulating way, and adding 1 to the priority of the main table on the basis of the priority of the sub-table when the main table exists in the table.
As shown in Table 7, when Table1 is traversed circularly, the priority of Table1 is unchanged, and the priority of Table3 in Table1 needs to be added with 1 to the priority of Table1, that is, 1+1=2.
TABLE 7
Table1 Table2 Table3 Table4
1 1 2 1
As shown in Table 8, when Table2 is traversed circularly, the priority of Table2 is unchanged, and the priority of Table4 in the main Table of Table2 needs to be added with 1, that is, 1+1=2.
TABLE 8
Table1 Table2 Table3 Table4
1 1 2 2
As shown in Table 9, when Table3 is traversed circularly, the priority of Table3 is unchanged, and the priority of Table2 in the main Table of Table3 needs to be added with 1, that is, 2+1=3, to the priority of Table 3.
TABLE 9
Table1 Table2 Table3 Table4
1 3 2 2
As shown in Table 10, when Table4 is traversed circularly, table4 has no main Table, and the priorities of the other tables are unchanged.
Table 10
Table1 Table2 Table3 Table4
1 3 2 2
The second round of traversing the 4 source data tables is repeated, with the priorities of the tables shown in table 11.
TABLE 11
In this example, the total number of repetitions is the number of source data tables minus 1, and after the second repetition cycle traversal is completed, the present example repeatedly performs the third cycle traversal on the 4 source data tables. When no priority update occurs in a large cycle, the cycle can be ended in advance.
Step S203: and carrying out issuing control and operation on the data synchronous tasks which are subjected to priority operation arrangement.
When data of the master-slave table is written, because of external key constraint, the data writing sequence of the master-slave table needs to be strictly controlled, and the data of the slave table is written after the data of the master table is completely written, so that after the data synchronization sequence of each data table is obtained by calculation, the issuing control of the data synchronization task needs to be strictly carried out.
As an optional example, in the method of this embodiment, the data synchronization task is controlled according to the priority, and after the data of the master table is completely written, the data of the slave table is written.
In the method of this embodiment, the data synchronization of each single table is used as a separate data synchronization task, so the operation priority of each table, that is, the operation sequence of each data synchronization task.
FIG. 4 is a flowchart illustrating a method for controlling and running the data synchronization task scheduled by priority running according to an embodiment of the present invention. As shown in fig. 4, in the method of the embodiment, a data synchronization task to be issued is obtained according to priority scanning, and whether a main table exists in a source data table corresponding to the obtained data synchronization task to be issued is judged; when the source data table corresponding to the acquired data synchronization task to be issued does not have a main table, issuing and operating the data synchronization task; judging the running state of the main table when the main table exists in the source data table corresponding to the data synchronization task to be issued, and issuing and running the data synchronization task corresponding to the main table when the running state of the main table is successful; when the running state of the main table is not successful, canceling the issuing and running of the data synchronization task corresponding to the main table, for example, when the running state of the main table is to be run or in the running state, the data synchronization task corresponding to the main table is not run, and then the data synchronization task cannot be issued; when the running state of the main table is running failure, the data synchronization task corresponding to the main table is running failure, and the data is not written in, so that the data synchronization task is not issued.
The main external key data synchronization method and system of the embodiment have the following beneficial technical effects:
1. the master-slave relationship among the data tables is identified by inquiring the metadata of the data tables through the program, so that the efficiency of carding the master-slave relationship of the data tables is improved, the master-slave relationship of the data tables is displayed through a visual interface, the complex multi-level master-slave relationship among the data tables can be prompted, and the configuration of the data synchronization task is simplified;
2. the method supports the data synchronization task configuration of a plurality of master-slave tables in one data synchronization task, and the configured master-slave tables are set with different operation priorities through an algorithm, so that the task arrangement is automatically performed, the number of configuration tasks is reduced, the task arrangement is avoided, and the possibility of errors is reduced;
3. when the master-slave relation of the target end data table is changed, the data synchronization task does not need to be reconfigured or task arrangement is performed again, and the scheme for task arrangement according to the master-slave relation of the source end data table can be compatible with the scene;
4. the method can realize visualization and automation of data synchronization of the main external key table of the heterogeneous data source, and can remarkably improve task configuration efficiency and accuracy of data synchronization in the data synchronization process of the main external key table.
As shown in FIG. 5, the present invention also provides an apparatus comprising a processor 310, a communication interface 320, a memory 330 for storing a processor executable computer program, and a communication bus 340. Wherein the processor 310, the communication interface 320 and the memory 330 perform communication with each other through the communication bus 340. The processor 310 implements the master foreign key table data synchronization method described above by running an executable computer program.
The computer program in the memory 330 may be implemented in the form of software functional units and may be stored in a computer-readable storage medium when sold or used as a separate product. Based on such understanding, the technical solution of the present application may be embodied essentially or in a part contributing to the prior art or in a part of the technical solution, in the form of a software product stored in a storage medium, including several instructions for causing a computer device (which may be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the methods of the embodiments of the present application. And the aforementioned storage medium includes: a U-disk, a removable hard disk, a Read-Only Memory (ROM), a random access Memory (RAM, random Access Memory), a magnetic disk, or an optical disk, or other various media capable of storing program codes.
The system embodiments described above are merely illustrative, in which elements illustrated as separate elements may or may not be physically separate, and elements shown as elements may or may not be physical elements, may be located in one place, or may be distributed over a plurality of network elements. Some or all of the modules may be selected based on actual needs to achieve the purpose of the embodiment. Those of ordinary skill in the art will understand and implement the present invention without undue burden.
From the above description of the embodiments, it will be apparent to those skilled in the art that the embodiments may be implemented by means of software plus necessary general hardware platforms, or of course may be implemented by means of hardware. Based on such understanding, the foregoing technical solutions may be embodied essentially or in part in the form of a software product, which may be stored in a computer-readable storage medium, such as a ROM/RAM, a magnetic disk, an optical disk, etc., including several instructions to cause a computer device (which may be a personal computer, a server, or a network device, etc.) to perform the various embodiments or methods of some parts of the embodiments.
The foregoing is merely illustrative of the present invention, and the present invention is not limited thereto, and any changes or substitutions easily contemplated by those skilled in the art within the scope of the present invention should be included in the present invention. Therefore, the protection scope of the invention is subject to the protection scope of the claims.

Claims (10)

1. A method for synchronizing data of a primary foreign key table, the method comprising:
inquiring metadata of a data table in a database, and acquiring a master-slave relationship of a source data table according to the inquired metadata;
configuring data synchronization tasks according to the master-slave relation of the acquired source data table, and performing priority operation arrangement on the configured data synchronization tasks;
and carrying out issuing control and operation on the data synchronous tasks which are subjected to priority operation arrangement.
2. The method for synchronizing data of a master external key table according to claim 1, wherein obtaining a master-slave relationship of a source data table according to the queried metadata comprises: and acquiring external key information in the queried metadata, acquiring the relation between the external key of the slave table and the main key of the main table according to the acquired external key information, and determining the relation between the main table and the slave table in the source data table according to the acquired relation between the external key of the slave table and the main key of the main table.
3. The method for synchronizing data of a master external key table according to claim 1, wherein configuring a data synchronization task according to a master-slave relationship of an acquired source data table comprises:
acquiring all source data tables in a database, and constructing a source data table column and a source data table selection column according to the acquired source data tables;
according to the master-slave relation of the source data table, matching a corresponding direct association master table for the source data table in the source data table column, and constructing an association master table column and a master table selection operation column according to the matched direct association master table;
and selecting the corresponding source data table and the directly-related main table through the source data table selection column and the main table selection operation column.
4. The method for synchronizing data of a master external key table according to claim 1, wherein the data synchronizing task is configured according to a master-slave relationship of the acquired source data table, further comprising: when the target end data table has the main external key constraint, a master-slave relation of the target end data table is set according to the master-slave relation of the source end data table, the master table of the source end data table corresponds to the master table of the target end data table, the slave table of the source end data table corresponds to the slave table of the target end data table, and the corresponding target end data table is selected according to the master-slave relation of the source end data table.
5. The method for synchronizing data of a master foreign key table according to claim 1, wherein the performing of priority operation arrangement on the configured data synchronization task comprises:
respectively constructing a source data table selection column and a direct association main table selection column according to the selected source data table and the direct association main table;
setting the initial priority of a source data table in a source data table selection column to be 1, and circularly traversing whether other source data tables exist in the source data table selection column of each source data table according to the sequence of the source data table in the source data table selection column, wherein the main table of the source data table which is circularly traversed currently;
when other source data tables exist and are the main tables of the source data tables traversed by the current cycle, updating the priority of the other source data tables to be the priority of the source data tables traversed by the current cycle by adding 1;
after the first cycle traversal of all the source data tables in the source data table selection column is completed, repeatedly carrying out the cycle traversal of all the source data tables in the source data table selection column in sequence according to the number of the source data tables in the source data table selection column.
6. The method of claim 5, wherein after completing the first round of the round robin traversal of all source data tables in the source data table selection column, repeatedly and sequentially round robin traversal of all source data tables in the source data table selection column according to the number of source data tables in the source data table selection column, comprising: and taking the value obtained by subtracting 1 from the total number of the source data tables in the source data table selection column as the repetition number of the cyclic traversal of all the source data tables in the source data table selection column.
7. The method according to claim 5, wherein after performing the cyclic traversal of all the source data tables in the source data table selection column in the order repeatedly, no priority update occurs, and the cyclic traversal of the source data table selection column is ended.
8. The method for synchronizing data of a master foreign key table according to claim 1, wherein the step of performing the issuing control and the operation of the data synchronization task subjected to the priority operation arrangement comprises the steps of: and performing issuing control on the data synchronization task according to the priority, and writing the data of the slave table after the data of the master table is completely written.
9. The method for synchronizing data of a master foreign key table according to claim 1, wherein the step of performing the issuing control and the operation of the data synchronization task subjected to the priority operation arrangement comprises the steps of:
acquiring a data synchronization task to be issued according to priority scanning, and judging whether a main table exists in a source data table corresponding to the acquired data synchronization task to be issued;
when the source data table corresponding to the acquired data synchronization task to be issued does not have a main table, issuing and operating the data synchronization task;
judging the running state of the main table when the main table exists in the source data table corresponding to the data synchronization task to be issued, and issuing and running the data synchronization task corresponding to the main table when the running state of the main table is successful; and when the running state of the main table is not successful, canceling the issuing and running of the data synchronization task corresponding to the main table.
10. The data synchronization system of the main external key table is characterized by comprising a data synchronization service end, wherein the data synchronization service end is used for inquiring metadata of a data table in a database and acquiring a master-slave relationship of a source data table according to the inquired metadata; configuring data synchronization tasks according to the master-slave relation of the acquired source data table, and performing priority operation arrangement on the configured data synchronization tasks; and carrying out issuing control and operation on the data synchronous tasks which are subjected to priority operation arrangement.
CN202311293590.7A 2023-10-08 2023-10-08 Main external key table data synchronization method and system Active CN117349377B (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202311293590.7A CN117349377B (en) 2023-10-08 2023-10-08 Main external key table data synchronization method and system

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202311293590.7A CN117349377B (en) 2023-10-08 2023-10-08 Main external key table data synchronization method and system

Publications (2)

Publication Number Publication Date
CN117349377A true CN117349377A (en) 2024-01-05
CN117349377B CN117349377B (en) 2024-05-10

Family

ID=89368513

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202311293590.7A Active CN117349377B (en) 2023-10-08 2023-10-08 Main external key table data synchronization method and system

Country Status (1)

Country Link
CN (1) CN117349377B (en)

Citations (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
WO2022147908A1 (en) * 2021-01-05 2022-07-14 平安科技（深圳）有限公司 Table association-based lost data recovery method and apparatus, device, and medium
CN116450660A (en) * 2023-04-06 2023-07-18 中电云数智科技有限公司 Method and device for processing primary key conflict in data synchronization

Patent Citations (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
WO2022147908A1 (en) * 2021-01-05 2022-07-14 平安科技（深圳）有限公司 Table association-based lost data recovery method and apparatus, device, and medium
CN116450660A (en) * 2023-04-06 2023-07-18 中电云数智科技有限公司 Method and device for processing primary key conflict in data synchronization

Also Published As

Publication number Publication date
CN117349377B (en) 2024-05-10

Similar Documents

Publication Publication Date Title
US11567956B2 (en) Background format optimization for enhanced queries in a distributed computing cluster
US10963428B2 (en) Multi-range and runtime pruning
CN107710193B (en) Data placement control for distributed computing environments
US10810170B2 (en) Equi-joins between split tables
CN107710200B (en) System and method for parallelizing hash-based operators in SMP databases
US10296505B2 (en) Framework for joining datasets
EP3885929A1 (en) Data redistribution method, device, and system
US10394805B2 (en) Database management for mobile devices
CN107506464A (en) A kind of method that HBase secondary indexs are realized based on ES
EP3376403A1 (en) Method of accessing distributed database and device providing distributed data service
AU2017268599A1 (en) Method, device, server and storage medium of searching a group based on social network
US20230350934A1 (en) Method and apparatus for the conversion and display of data
US20220121632A1 (en) Dynamic inclusion of custom columns into a logical model
CN104298761A (en) Implementation method for master data matching between heterogeneous software systems
JP6329552B2 (en) Reference data segmentation from single table to multiple tables
CN113656384A (en) Data processing method, distributed database system, electronic device and storage medium
CN117349377B (en) Main external key table data synchronization method and system
US11914612B2 (en) Selective synchronization of linked records
CN106716400A (en) Partitioned management method and apparatus for data table
CN116303516A (en) Method, device and related equipment for updating knowledge graph
US10764204B2 (en) Cloud extensibility framework
CN114969046A (en) Hash connection processing method, storage medium and equipment
US20130103724A1 (en) Network and method for managing models
CN114003717A (en) Data processing method and device, electronic equipment and storage medium
CN108073584B (en) Data processing method and server

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination
GR01 Patent grant
GR01 Patent grant