Method, system and terminal for cross-database replication of distributed database tables and storage medium

Abstract

The application discloses a method, system, terminal and storage medium for cross-database replication of a distributed database table, wherein the method comprises the following steps: acquiring data information of the distributed database; judging the distribution type of a target data table; when the target data table is in a hash distribution, acquiring a hash value list corresponding to each target data node; creating a first remote table on each target data node, and copying data in a source data table into each first remote table respectively; copying the data on the first remote table into a target database of the target data node where the first remote table is located; when the target data table is in an average distribution, dividing the data in each source data table into multiple interval segments averagely; creating a second remote table on each target data node, and copying the data in the source data table into the second remote table; and copying the data on the second remote table into a target database of the target data node where the second remote table is located.

Description

Technical Field

[0001] This invention relates to the field of distributed database management technology, specifically to a method, system, terminal, and storage medium for cross-database table replication in a distributed database. Background Technology

[0002] In the era of big data, single-node databases are increasingly insufficient for government and enterprise applications, leading to the widespread adoption of multi-node distributed structured databases (hereinafter referred to as distributed databases). In multi-node distributed databases, the data nodes used during database creation are typically selectable, resulting in different databases having different data nodes. When copying table data between two different databases, the different data nodes prevent direct execution of cross-database SQL statements on the data nodes, hindering successful table copying in cross-database scenarios.

[0003] In distributed databases, different databases may be distributed across different nodes. If you want to copy source data tables from the source database to the destination database, the traditional method for cross-node data transfer is to create a remote table on the target node that is located on the source node. However, this will bring the following problems: 1. Data distribution skew will occur after copying the table, wasting disk space and machine performance; 2. The data distribution of the target table will not match the original distribution rules after copying the table data. Summary of the Invention

[0004] The main objective of this invention is to provide a method for cross-database table replication in distributed databases, aiming to solve the problems of data distribution skew after table replication, wasted disk space and machine performance, and mismatch between the data distribution of the target table and the original distribution rules after data replication.

[0005] To achieve the above objectives, the first aspect of the present invention provides a method for cross-database table replication in a distributed database, comprising the following steps:

[0006] Obtain data information from a distributed database; the data information from the distributed database includes a source database and a destination database, wherein the source database is distributed in multiple source data nodes, the destination database is distributed in multiple destination data nodes, each source data node includes a source data table, and each destination data node includes a destination data table.

[0007] Determine the distribution type of the target data table;

[0008] When the target data table is a hash distribution, then obtain the list of hash values ​​corresponding to each target data node;

[0009] A first remote table is created on each of the destination data nodes, and the data in the source data table is copied to each of the first remote tables according to the hash value list corresponding to each of the destination data nodes.

[0010] Copy the data from the first remote table to the destination database of the destination data node where the first remote table is located, and delete the first remote table;

[0011] When the target data table is evenly distributed, the data in each source data table is evenly divided into multiple intervals, wherein the number of intervals is equal to the number of target data nodes;

[0012] Create a second remote table on each of the destination data nodes, and copy the data in the source data table to the second remote table according to the interval;

[0013] Copy the data from the second remote table to the destination database of the destination data node where the second remote table is located, and then delete the second remote table.

[0014] In an optional embodiment of the first aspect of the present invention, the step of creating a first remote table on each of the destination data nodes and copying the data in the source data table to each of the first remote tables according to the hash value list corresponding to each of the destination data nodes includes the following steps:

[0015] Multiple hash partition tables are established, wherein the number of hash partition tables is the same as the number of destination data nodes, and the hash value list of the hash partition tables is the same as the hash value list of the destination data nodes.

[0016] Based on the hash value list of each hash partition table, copy the data from all the source data tables to each hash partition table respectively;

[0017] A first remote table is created on each of the destination data nodes, and a connection is established between the first remote table and the hash partition table; wherein each of the first remote tables is connected to one of the hash partition tables.

[0018] The data in the hash partition table is copied to the first remote table connected to it, and the hash partition table is deleted.

[0019] In an optional embodiment of the first aspect of the present invention, the step of copying data from all the source data tables to each of the hash partition tables according to the hash value list of each of the hash partition tables includes the following steps:

[0020] Establish a connection between the hash partition table and the source data nodes, wherein each hash partition table establishes a connection with all the source data nodes respectively;

[0021] Based on the list of hash values ​​in the hash partition table, copy the data from all the source data tables to the respective hash partition tables.

[0022] In an optional embodiment of the first aspect of the present invention, the step of creating a first remote table on each of the destination data nodes and establishing a connection between the first remote table and the hash partition table; wherein each of the first remote tables corresponds to one of the hash partition tables in the step of establishing a connection;

[0023] The hash value list of the destination data node where the first remote table is located is consistent with the hash value list of the hash partition table that is connected to the first remote table.

[0024] In an optional embodiment of the first aspect of the present invention, when the destination data table is evenly distributed, dividing the data in each source data table into multiple intervals, wherein the number of intervals is equal to the number of destination data nodes, includes the following steps:

[0025] A temporary table is created on each of the source data nodes, and an auto-incrementing field is added to the temporary table. Then, the data in the source data table is copied to the corresponding temporary table.

[0026] The data in the temporary table is divided into multiple intervals based on the auto-increment field on the temporary table, wherein the number of intervals is equal to the number of the destination data nodes.

[0027] In an optional embodiment of the first aspect of the present invention, the step of dividing the data in the temporary table into multiple intervals based on an auto-incrementing field on the temporary table, wherein the number of intervals is equal to the number of the destination data nodes, includes the following steps:

[0028] Get the largest auto-incrementing field in the temporary table;

[0029] Calculate the length of each interval segment in the temporary table, including the auto-incrementing field, wherein the length of each interval segment including the auto-incrementing field is equal to the largest auto-incrementing field in the temporary table divided by the number of the destination data nodes;

[0030] The data in the temporary table is divided into different intervals based on the length of each interval, including the auto-incrementing field.

[0031] In an optional embodiment of the first aspect of the present invention, the step of creating a second remote table on each of the destination data nodes and copying data from the source data table to the second remote table according to the interval segment includes the following steps:

[0032] Establish a connection between the second remote table and the temporary table, wherein each of the second remote tables is connected to all of the temporary tables;

[0033] The temporary table copies the data in the temporary table to the second remote table according to the interval segment, and deletes the temporary table. Each temporary table sends the data content of one of the interval segments to one of the second remote tables.

[0034] A second aspect of the present invention provides a system for cross-database table replication in a distributed database, comprising:

[0035] The first acquisition module is used to acquire data information from a distributed database. The data information from the distributed database includes a source database and a destination database. The source database is distributed across multiple source data nodes, and the destination database is distributed across multiple destination data nodes. Each source data node includes a source data table, and each destination data node includes a destination data table.

[0036] The judgment module is used to determine the distribution type of the target data table;

[0037] The second acquisition module is used to acquire a list of hash values ​​corresponding to each target data node when the target data table is a hash distribution.

[0038] The first creation module is used to create a first remote table on each of the destination data nodes, and copy the data in the source data table to each of the first remote tables according to the hash value list corresponding to each of the destination data nodes.

[0039] The first replication module is used to copy the data on the first remote table to the destination database of the destination data node where the first remote table is located, and to delete the first remote table.

[0040] The segmentation module is used to divide the data in each source data table into multiple interval segments when the destination data table is evenly distributed, wherein the number of interval segments is equal to the number of destination data nodes;

[0041] The second creation module is used to create a second remote table on each of the destination data nodes, and copy the data in the source data table into the second remote table according to the interval segment;

[0042] The second replication module is used to copy the data on the second remote table to the destination database of the destination data node where the second remote table is located, and to delete the second remote table.

[0043] A third aspect of the present invention provides a terminal for cross-database table replication in a distributed database, the terminal comprising: a memory and at least one processor, the memory storing instructions, and the memory and the at least one processor being interconnected via a line;

[0044] The at least one processor invokes the instructions in the memory to cause the terminal to execute a method for cross-database table replication in a distributed database as described in any of the preceding claims.

[0045] A fourth aspect of the present invention provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements a method for cross-database table replication in a distributed database as described in any of the preceding claims.

[0046] Beneficial Effects: This invention provides a method, system, terminal, and storage medium for cross-database table replication in a distributed database. The method includes acquiring data information from a distributed database; the data information includes a source database and a destination database, wherein the source database is distributed across multiple source data nodes, and the destination database is distributed across multiple destination data nodes. Each source data node includes a source data table, and each destination data node includes a destination data table. The method further includes determining the distribution type of the destination data tables; when the destination data tables are hash-distributed, acquiring a list of hash values ​​corresponding to each destination data node; creating a first remote table on each destination data node, and replicating the hash values ​​of each destination data node. The corresponding hash value list is used to copy the data in the source data table to each of the first remote tables; the data in the first remote tables is copied to the destination database of the destination data node where the first remote tables are located, and the first remote tables are deleted; when the destination data table is evenly distributed, the data in each source data table is evenly divided into multiple intervals, wherein the number of intervals is equal to the number of destination data nodes; a second remote table is created on each destination data node, and the data in the source data table is copied to the second remote table according to the intervals; the data in the second remote table is copied to the destination database of the destination data node where the second remote table is located, and the second remote table is deleted. When the destination data table is hash-distributed, it is copied according to the hash value list corresponding to each destination data node. This not only ensures that the copied data in each destination data node is basically consistent, avoiding data distribution skew and thus preventing waste of disk space and machine performance, but also ensures that the data distribution of each destination data node matches the original distribution rules after copying. When the destination data table is evenly distributed, it is copied according to the principle of even distribution. This not only ensures that the copied data in each destination data node is basically consistent, avoiding data distribution skew and thus preventing waste of disk space and machine performance, but also ensures that the data distribution of each destination data node matches the original distribution rules after copying. Attached Figure Description

[0047] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on the structures shown in these drawings without creative effort.

[0048] Figure 1 This is a schematic diagram of an embodiment of a method for cross-database table replication in a distributed database according to the present invention.

[0049] Figure 2 This is a schematic diagram of an embodiment of a distributed database cross-database table replication system according to the present invention.

[0050] Figure 3 This is a schematic diagram of an embodiment of a terminal for cross-database table replication in a distributed database according to the present invention.

[0051] The realization of the objective, functional features and advantages of the present invention will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0052] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of the present invention.

[0053] It should be noted that all directional indications (such as up, down, left, right, front, back, etc.) in the embodiments of the present invention are only used to explain the relative positional relationship and movement of each component in a certain specific posture (as shown in the figure). If the specific posture changes, the directional indication will also change accordingly.

[0054] Furthermore, in this invention, descriptions involving "first," "second," etc., are for descriptive purposes only and should not be construed as indicating or implying their relative importance or implicitly specifying the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include at least one of that feature. In the description of this invention, "a plurality of" means at least two, such as two, three, etc., unless otherwise explicitly specified.

[0055] In this invention, unless otherwise explicitly specified and limited, the terms "connection," "fixed," etc., should be interpreted broadly. For example, "fixed" can mean a fixed connection, a detachable connection, or an integral part; it can mean a mechanical connection or an electrical connection; it can mean a direct connection or an indirect connection through an intermediate medium; it can mean the internal communication of two components or the interaction between two components, unless otherwise explicitly limited. Those skilled in the art can understand the specific meaning of the above terms in this invention according to the specific circumstances.

[0056] Furthermore, the technical solutions of the various embodiments of the present invention can be combined with each other, but only if they are feasible for those skilled in the art. If the combination of technical solutions is contradictory or cannot be implemented, it should be considered that such combination of technical solutions does not exist and is not within the scope of protection claimed by the present invention.

[0057] For ease of understanding, the specific process of the embodiments of the present invention is described below. Please refer to [link / reference]. Figure 1 The first aspect of this invention provides a method for cross-database table replication in a distributed database, comprising the following steps:

[0058] S100. Obtain data information from a distributed database; the data information from the distributed database includes a source database and a destination database, wherein the source database is distributed in multiple source data nodes, the destination database is distributed in multiple destination data nodes, each source data node includes a source data table, and each destination data node includes a destination data table.

[0059] S200, Determine the distribution type of the target data table;

[0060] S300. When the target data table is a hash distribution, obtain the list of hash values ​​corresponding to each target data node.

[0061] S400. Create a first remote table on each of the destination data nodes, and copy the data in the source data table to each of the first remote tables according to the hash value list corresponding to each of the destination data nodes.

[0062] S500: Copy the data on the first remote table to the destination database of the destination data node where the first remote table is located, and delete the first remote table;

[0063] S600. When the target data table is evenly distributed, the data in each source data table is evenly divided into multiple interval segments, wherein the number of interval segments is equal to the number of target data nodes.

[0064] S700. Create a second remote table on each of the destination data nodes, and copy the data in the source data table to the second remote table according to the interval segment;

[0065] S800: Copy the data from the second remote table to the destination database of the destination data node where the second remote table is located, and delete the second remote table.

[0066] In this embodiment, different methods are used to copy data from the source data table to each destination data node depending on the distribution type of the destination data table. Specifically, when the destination data table has a hash distribution, assuming there are 2 source data nodes and 3 destination data nodes, the hash value list corresponding to each destination data node is first obtained. For example, the hash value list for the first destination data node is [1,4,7...], the hash value list for the second destination data node is [2,5,8...], and the hash value list for the third destination data node is [3,6,9...]. A first remote table is created on each destination data node. Then, based on the hash value list corresponding to each destination data node, the data from the source data table is copied to each of the first remote tables. Finally, the data from the first remote tables is copied to the destination database of the destination data node where the first remote tables are located. This completes the data copying process, which is performed according to the hash value list corresponding to each destination data node. This method not only ensures that the copied data across all destination data nodes is essentially consistent, preventing data distribution skew and thus avoiding wasted disk space and machine performance, but also ensures that the data distribution across all destination data nodes matches the original distribution rules after data copying. When the destination data table has an even distribution, assuming there are 2 source data nodes and 3 destination data nodes, the data in each source data table is divided into 3 equal intervals. A second remote table is created on each destination data node, resulting in a total of 3 second remote tables. The data from each of the 3 intervals in the source data table is then copied to one of the second remote tables. Finally, the data from the second remote tables is copied to the destination database of the destination data node containing the second remote table. This completes the data copying process, which follows the principle of even distribution. This ensures that the copied data across all destination data nodes is essentially consistent, preventing data distribution skew and thus avoiding wasted disk space and machine performance, and also ensures that the data distribution across all destination data nodes matches the original distribution rules after data copying.

[0067] In an optional embodiment of the first aspect of the present invention, the step of creating a first remote table on each of the destination data nodes and copying the data in the source data table to each of the first remote tables according to the hash value list corresponding to each of the destination data nodes includes the following steps:

[0068] Multiple hash partition tables are established, wherein the number of hash partition tables is the same as the number of destination data nodes, and the hash value list of the hash partition tables is the same as the hash value list of the destination data nodes.

[0069] Based on the hash value list of each hash partition table, copy the data from all source data tables to each hash partition table respectively;

[0070] A first remote table is created on each of the destination data nodes, and a connection is established between the first remote table and the hash partition table; wherein each of the first remote tables is connected to one of the hash partition tables.

[0071] The data in the hash partition table is copied to the first remote table connected to it, and the hash partition table is deleted.

[0072] In this embodiment, the hash value list corresponding to the first hash partition table is the same as the hash value list corresponding to the first destination data node, which is [1,4,7...]. The hash value list corresponding to the second hash partition table is [2,5,8...], and the hash value list corresponding to the third hash partition table is [3,6,9...]. Then, according to the hash value lists corresponding to these three hash partition tables, the data in all source data tables are copied to these three hash partition tables respectively. Then, these three hash partition tables copy the data to the first remote table connected to the hash partition table respectively.

[0073] In an optional embodiment of the first aspect of the present invention, the step of copying data from all source data tables to each hash partition table according to the hash value list of each hash partition table includes the following steps:

[0074] Establish a connection between the hash partition table and the source data nodes, wherein each hash partition table establishes a connection with all the source data nodes respectively;

[0075] Based on the list of hash values ​​in the hash partition table, copy the data from all source data tables to the respective hash partition tables.

[0076] In this embodiment, each hash partition table establishes connections with all source data nodes. That is, the first hash partition table establishes connections with two source data nodes, the second hash partition table establishes connections with two source data nodes, and the third hash partition table establishes connections with two source data nodes. Then, the data with hash values ​​[1,4,7...] in the source data tables of the two source data nodes are copied to the first hash partition table, the data with hash values ​​[2,5,8...] in the source data tables of the two source data nodes are copied to the second hash partition table, and the data with hash values ​​[3,6,9...] in the source data tables of the two source data nodes are copied to the third hash partition table.

[0077] In an optional embodiment of the first aspect of the present invention, the step of creating a first remote table on each of the destination data nodes and establishing a connection between the first remote table and the hash partition table; wherein each of the first remote tables corresponds to one of the hash partition tables in the step of establishing a connection;

[0078] The hash value list of the destination data node where the first remote table is located is consistent with the hash value list of the hash partition table that is connected to the first remote table.

[0079] In this embodiment, the hash value list corresponding to the first destination data node is [1,4,7...], and the hash value list corresponding to the first hash partition table is [1,4,7...]. Therefore, a connection is established between the first hash partition table and the first destination data node. Similarly, a connection is established between the second hash partition table and the second destination data node, and a connection is established between the third hash partition table and the third destination data node.

[0080] In an optional embodiment of the first aspect of the present invention, when the destination data table is evenly distributed, dividing the data in each source data table into multiple intervals, wherein the number of intervals is equal to the number of destination data nodes, includes the following steps:

[0081] A temporary table is created on each of the source data nodes, and an auto-incrementing field is added to the temporary table. Then, the data in the source data table is copied to the corresponding temporary table.

[0082] The data in the temporary table is divided into multiple intervals based on the auto-increment field on the temporary table, wherein the number of intervals is equal to the number of the destination data nodes.

[0083] In this embodiment, a temporary table is created on each source data node, that is, two temporary tables are created in total. Then, an auto-incrementing field is added to the temporary tables. Then, the data in the source data table of the first source data node is copied to the first temporary table, and the data in the source data table of the second source data node is copied to the second temporary table. Then, the data in the first temporary table is divided into three intervals on an average basis according to the auto-incrementing field on the temporary table. Similarly, the data in the second temporary table is also divided into three intervals on an average basis.

[0084] In an optional embodiment of the first aspect of the present invention, the step of dividing the data in the temporary table into multiple intervals based on an auto-incrementing field on the temporary table, wherein the number of intervals is equal to the number of the destination data nodes, includes the following steps:

[0085] Get the largest auto-incrementing field in the temporary table;

[0086] Calculate the length of each interval segment in the temporary table, including the auto-incrementing field, wherein the length of each interval segment including the auto-incrementing field is equal to the largest auto-incrementing field in the temporary table divided by the number of the destination data nodes;

[0087] The data in the temporary table is divided into different intervals based on the length of each interval, including the auto-incrementing field.

[0088] In this embodiment, the largest auto-incrementing field in the temporary table is first obtained. The auto-incrementing field is divided by the number of the target data nodes to obtain the length of each interval including the auto-incrementing field. When the auto-incrementing field divided by the number of the target data nodes is not an integer, it is rounded up to the nearest integer. Assuming that the length of each interval including the auto-incrementing field is X, then the auto-incrementing field of the first interval is [1, X), the auto-incrementing field of the second interval is [X, 2X), and the auto-incrementing field of the third interval is [2X, 3X].

[0089] In an optional embodiment of the first aspect of the present invention, the step of creating a second remote table on each of the destination data nodes and copying data from the source data table to the second remote table according to the interval segment includes the following steps:

[0090] Establish a connection between the second remote table and the temporary table, wherein each of the second remote tables is connected to all of the temporary tables;

[0091] The temporary table copies the data in the temporary table to the second remote table according to the interval segment, and deletes the temporary table. Each temporary table sends the data content of one interval segment to one of the second remote tables.

[0092] In this embodiment, a total of 3 second remote tables are established. Each second remote table is connected to the first temporary table and the second temporary table. Then, the data of the first interval segment in the first temporary table and the data of the first interval segment in the second temporary table are copied to the first second remote table. The data of the second interval segment in the first temporary table and the data of the second interval segment in the second temporary table are copied to the second second remote table. The data of the third interval segment in the first temporary table and the data of the third interval segment in the second temporary table are copied to the third second remote table.

[0093] See Figure 2 The second aspect of the present invention provides a system for cross-database table replication in a distributed database, characterized in that it includes:

[0094] The first acquisition module 10 is used to acquire data information from a distributed database. The data information from the distributed database includes a source database and a destination database. The source database is distributed in multiple source data nodes, and the destination database is distributed in multiple destination data nodes. Each source data node includes a source data table, and each destination data node includes a destination data table.

[0095] The judgment module 20 is used to determine the distribution type of the target data table;

[0096] The second acquisition module 30 is used to acquire a list of hash values ​​corresponding to each target data node when the target data table is a hash distribution.

[0097] The first creation module 40 is used to create a first remote table on each of the destination data nodes, and copy the data in the source data table to each of the first remote tables according to the hash value list corresponding to each of the destination data nodes.

[0098] The first replication module 50 is used to copy the data on the first remote table to the destination database of the destination data node where the first remote table is located, and delete the first remote table.

[0099] The segmentation module 60 is used to divide the data in each source data table into multiple interval segments when the destination data table is evenly distributed, wherein the number of interval segments is equal to the number of destination data nodes;

[0100] The second creation module 70 is used to create a second remote table on each of the destination data nodes, and copy the data in the source data table to the second remote table according to the interval segment;

[0101] The second replication module 80 is used to copy the data on the second remote table to the destination database of the destination data node where the second remote table is located, and to delete the second remote table.

[0102] In an optional embodiment of the second aspect of the present invention, the first creation module 40 includes:

[0103] The first establishment unit is used to establish multiple hash partition tables, wherein the number of hash partition tables is consistent with the number of destination data nodes, and the hash value list of the hash partition tables is consistent with the hash value list of the destination data nodes.

[0104] The first copying unit is used to copy the data in all source data tables to each hash partition table according to the hash value list of each hash partition table;

[0105] A creation unit is configured to create a first remote table on each of the destination data nodes and establish a connection between the first remote table and the hash partition table; wherein each of the first remote tables establishes a connection with one of the hash partition tables.

[0106] The second copying unit is used to copy the data in the hash partition table to the first remote table connected to it, and to delete the hash partition table.

[0107] In an optional embodiment of a second aspect of the present invention, the first copying unit includes:

[0108] A subunit is established to establish a connection between the hash partition table and the source data node, wherein each hash partition table establishes a connection with all the source data nodes respectively;

[0109] The copying subunit is used to copy the data from all source data tables to each hash partition table according to the list of hash values ​​in the hash partition table.

[0110] In an optional embodiment of the second aspect of the present invention, the segmentation module 60 includes:

[0111] The second establishment unit is used to establish a temporary table on each of the source data nodes, add an auto-incrementing field to the temporary table, and then copy the data in the source data table to the corresponding temporary table.

[0112] The segmentation unit is used to divide the data in the temporary table into multiple intervals based on the auto-incrementing field on the temporary table, wherein the number of intervals is equal to the number of the destination data nodes.

[0113] In an optional embodiment of the second aspect of the present invention, the segmentation unit includes:

[0114] Get the sub-unit, used to retrieve the largest auto-incrementing field in the temporary table;

[0115] A calculation subunit is used to calculate the length of each interval segment including the auto-incrementing field in the temporary table, wherein the length of each interval segment including the auto-incrementing field is equal to the largest auto-incrementing field in the temporary table divided by the number of the destination data nodes;

[0116] The sub-unit is used to divide the data in the temporary table into different intervals based on the length of each interval segment, including the auto-incrementing field.

[0117] In an optional embodiment of the second aspect of the present invention, the second creation module 70 includes:

[0118] The third establishment unit is used to establish a connection between the second remote table and the temporary table, wherein each of the second remote tables establishes a connection with all the temporary tables;

[0119] The third copying unit is used to copy the data in the temporary table to the second remote table according to the interval segment, and delete the temporary table, wherein each temporary table sends the data content of one interval segment to one of the second remote tables.

[0120] Figure 3 This is a schematic diagram of the structure of a distributed database cross-database table replication terminal provided by an embodiment of the present invention. The terminal can vary significantly due to differences in configuration or performance, and may include one or more processors 1 (central processing units, CPUs) (e.g., one or more processors) and memory 2, and one or more storage media 3 (e.g., one or more mass storage devices) for storing applications or data. The memory and storage media can be temporary or persistent storage. The program stored on the storage media may include one or more modules (not shown in the diagram), each module including a series of instruction operations on the distributed database cross-database table replication terminal. Furthermore, the processor may be configured to communicate with the storage media to execute the series of instruction operations stored in the storage media on the distributed database cross-database table replication terminal.

[0121] The terminal for distributed database cross-database table replication of the present invention may further include one or more power supplies 4, one or more wired or wireless network interfaces 5, one or more input / output interfaces 6, and / or one or more operating systems, such as Windows Server, Mac OS X, Unix, Linux, FreeBSD, etc. Those skilled in the art will understand that... Figure 3 The terminal structure of the distributed database cross-database table replication shown does not constitute a limitation on the terminal of the distributed database cross-database table replication, which may include more or fewer components than shown, or combine certain components, or have different component arrangements.

[0122] The present invention also provides a computer-readable storage medium, which may be a non-volatile computer-readable storage medium or a volatile computer-readable storage medium, wherein the computer-readable storage medium stores instructions that, when executed on a computer, cause the computer to perform the steps of the method for cross-database table replication in a distributed database.

[0123] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the specific working process of the system, device, or unit described above can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here.

[0124] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0125] The above are merely preferred embodiments of the present invention and do not limit the patent scope of the present invention. Any equivalent structural transformations made under the concept of the present invention using the description and drawings of the present invention, or direct / indirect applications in other related technical fields, are included within the patent protection scope of the present invention.

Claims

1. A method for cross-database table replication in a distributed database, characterized in that, Includes the following steps: Obtain data information from a distributed database; the data information from the distributed database includes a source database and a destination database, wherein the source database is distributed in multiple source data nodes, the destination database is distributed in multiple destination data nodes, each source data node includes a source data table, and each destination data node includes a destination data table. Determine the distribution type of the target data table; When the target data table is a hash distribution, then obtain the list of hash values ​​corresponding to each target data node; A first remote table is created on each of the destination data nodes, and the data in the source data table is copied to each of the first remote tables according to the hash value list corresponding to each of the destination data nodes. Copy the data from the first remote table to the destination database of the destination data node where the first remote table is located, and delete the first remote table; When the target data table is evenly distributed, the data in each source data table is evenly divided into multiple intervals, wherein the number of intervals is equal to the number of target data nodes; Create a second remote table on each of the destination data nodes, and copy the data in the source data table to the second remote table according to the interval; Copy the data from the second remote table to the destination database of the destination data node where the second remote table is located, and then delete the second remote table; The step of creating a first remote table on each destination data node and copying the data in the source data table to each of the first remote tables according to the hash value list corresponding to each destination data node includes the following steps: Multiple hash partition tables are established, wherein the number of hash partition tables is the same as the number of destination data nodes, and the hash value list of the hash partition tables is the same as the hash value list of the destination data nodes. Based on the hash value list of each hash partition table, copy the data from all the source data tables to each hash partition table respectively; A first remote table is created on each of the destination data nodes, and a connection is established between the first remote table and the hash partition table; wherein each of the first remote tables is connected to one of the hash partition tables. The data in the hash partition table is copied to the first remote table connected to it, and the hash partition table is deleted.

2. The method for cross-database table replication in a distributed database according to claim 1, characterized in that, The step of copying data from all the source data tables to the respective hash partition tables according to the hash value list of each hash partition table includes the following steps: Establish a connection between the hash partition table and the source data nodes, wherein each hash partition table establishes a connection with all the source data nodes respectively; Based on the list of hash values ​​in the hash partition table, copy the data from all the source data tables to the respective hash partition tables.

3. The method for cross-database table replication in a distributed database according to claim 2, characterized in that, The step of creating a first remote table on each of the destination data nodes and establishing a connection between the first remote table and the hash partition table; wherein each of the first remote tables corresponds to one of the hash partition tables in the step of establishing a connection; The hash value list of the destination data node where the first remote table is located is consistent with the hash value list of the hash partition table that is connected to the first remote table.

4. The method for cross-database table replication in a distributed database according to claim 1, characterized in that, When the target data table is evenly distributed, the data in each source data table is evenly divided into multiple intervals, wherein the number of intervals is equal to the number of target data nodes, including the following steps: A temporary table is created on each of the source data nodes, and an auto-incrementing field is added to the temporary table. Then, the data in the source data table is copied to the corresponding temporary table. The data in the temporary table is divided into multiple intervals based on the auto-increment field on the temporary table, wherein the number of intervals is equal to the number of the destination data nodes.

5. The method for cross-database table replication in a distributed database according to claim 4, characterized in that, The step of dividing the data in the temporary table into multiple intervals based on the auto-increment field on the temporary table, wherein the number of intervals is equal to the number of the destination data nodes, includes the following steps: Get the largest auto-incrementing field in the temporary table; Calculate the length of each interval segment in the temporary table, including the auto-incrementing field, wherein the length of each interval segment including the auto-incrementing field is equal to the largest auto-incrementing field in the temporary table divided by the number of the destination data nodes; The data in the temporary table is divided into different intervals based on the length of each interval, including the auto-incrementing field.

6. The method for cross-database table replication in a distributed database according to claim 5, characterized in that, The step of creating a second remote table on each of the destination data nodes and copying data from the source data table to the second remote table according to the interval segment includes the following steps: Establish a connection between the second remote table and the temporary table, wherein each of the second remote tables is connected to all of the temporary tables; The temporary table copies the data in the temporary table to the second remote table according to the interval segment, and deletes the temporary table. Each temporary table sends the data content of one of the interval segments to one of the second remote tables.

7. A system for cross-database table replication in a distributed database, characterized in that, include: The first acquisition module is used to acquire data information from a distributed database. The data information from the distributed database includes a source database and a destination database. The source database is distributed across multiple source data nodes, and the destination database is distributed across multiple destination data nodes. Each source data node includes a source data table, and each destination data node includes a destination data table. The judgment module is used to determine the distribution type of the target data table; The second acquisition module is used to acquire a list of hash values ​​corresponding to each target data node when the target data table is a hash distribution. The first creation module is used to create a first remote table on each of the destination data nodes, and copy the data in the source data table to each of the first remote tables according to the hash value list corresponding to each of the destination data nodes. The first replication module is used to copy the data on the first remote table to the destination database of the destination data node where the first remote table is located, and to delete the first remote table. The segmentation module is used to divide the data in each source data table into multiple interval segments when the destination data table is evenly distributed, wherein the number of interval segments is equal to the number of destination data nodes; The second creation module is used to create a second remote table on each of the destination data nodes, and copy the data in the source data table into the second remote table according to the interval segment; The second replication module is used to copy the data on the second remote table to the destination database of the destination data node where the second remote table is located, and to delete the second remote table. The step of creating a first remote table on each destination data node and copying the data in the source data table to each of the first remote tables according to the hash value list corresponding to each destination data node includes the following steps: Multiple hash partition tables are established, wherein the number of hash partition tables is the same as the number of destination data nodes, and the hash value list of the hash partition tables is the same as the hash value list of the destination data nodes. Based on the hash value list of each hash partition table, copy the data from all the source data tables to each hash partition table respectively; A first remote table is created on each of the destination data nodes, and a connection is established between the first remote table and the hash partition table; wherein each of the first remote tables is connected to one of the hash partition tables. The data in the hash partition table is copied to the first remote table connected to it, and the hash partition table is deleted.

8. A terminal for cross-database table replication in a distributed database, characterized in that, The terminal includes: a memory and at least one processor, wherein the memory stores instructions, and the memory and the at least one processor are interconnected via a line; The at least one processor invokes the instructions in the memory to cause the terminal to execute the method of cross-database table replication in a distributed database according to any one of claims 1-6.

9. A computer-readable storage medium storing a computer program thereon, characterized in that, When the computer program is executed by a processor, it implements the method for cross-database table replication in a distributed database according to any one of claims 1-6.

Images