A data synchronization method and device for an update operation

Abstract

The application relates to the technical field of database synchronization, and provides a data synchronization method and device for an update operation. When the data synchronization is based on logs for an update operation, the blood relationship mapping relationship between the to-be-updated columns in a source table and the to-be-updated columns in a target table is constructed through data blood relationship analysis on the synchronization data, the update operation is only executed on the to-be-updated columns in the target table, the data synchronization efficiency of the update operation is improved, the risk of causing synchronization service running errors is greatly reduced while avoiding huge performance loss caused by full-column update or full-column value comparison, and the data synchronization efficiency is improved.

Description

Technical Field

[0001] This invention relates to the field of database synchronization technology, and in particular to a data synchronization method and apparatus for update operations. Background Technology

[0002] During database data synchronization, when the column information of the source and target tables is inconsistent, or when data processing such as merging, splitting, or replacing is required during synchronization, the synchronized data needs to be cleaned and transformed. Data synchronization is the process of copying changed data from the source database to the target database in the shortest possible time. Data cleaning and transformation refers to cleaning and transforming the extracted data from the source tables according to the requirements of the data warehouse system model, ensuring the consistency and integrity of data from different systems and formats, and loading the processed data into the target table according to business requirements. After the source table data has been cleaned and transformed, since the mapping relationship between the source columns in the source table and the target columns in the target table is unknown, when an update operation on the source table needs to be synchronized to the target table, only a full column update of the target table can be performed, or the columns that need to be updated can be determined by value comparison. In a real production environment, when the target table has a large number of columns, updating the value of a single column in the source table for each record requires updating the entire column, resulting in extremely low update efficiency and significant performance overhead. When the target table contains columns with large fields in their values, comparing the values ​​of the source and target columns becomes very difficult and causes significant performance overhead for data synchronization.

[0003] Therefore, overcoming the shortcomings of the existing technology is an urgent problem to be solved in this technical field. Summary of the Invention

[0004] To address the aforementioned deficiencies or improvement needs of existing technologies, this invention provides a data synchronization method and apparatus for update operations. Its purpose is to construct a lineage mapping relationship between the update columns in the source table and the update columns in the target table by performing lineage analysis on the cleaned and transformed data. This enables update operations to be performed only on the columns that need updating and their corresponding values ​​in the target table, avoiding the significant performance overhead caused by full column updates or full column value comparisons, and improving the data synchronization efficiency of update operations.

[0005] The present invention adopts the following technical solution:

[0006] In a first aspect, the present invention provides a data synchronization method for an update operation, the data synchronization method for the update operation comprising:

[0007] Receive the synchronization log after the source table performs an update operation, and generate the new column name set and the corresponding new column value set of the source table based on the first old column name set and the corresponding first old column value set, the first new column name set and the corresponding first new column value set of the source table in the synchronization log;

[0008] ETL processing is performed on the first old column name set and its corresponding first old column value set, and the new column name set and its corresponding new column value set, respectively, to generate the second old column name set and its corresponding second old column value set, and the second new column name set and its corresponding second new column value set of the target table.

[0009] Based on the first new column name set and the corresponding first new column value set, and the second new column name set and the corresponding second new column value set, construct the lineage mapping relationship between the source table and the target table;

[0010] Based on the bloodline mapping relationship, the second new column name set and the corresponding second new column value set, the second old column name set and the corresponding second old column value set, construct the corresponding update operation statement, and execute the update operation statement.

[0011] Preferably, ETL processing is performed in the form of a process, with the source table as the source node and the target table as the target node. The process further includes at least one data flow node, wherein:

[0012] The step of constructing the lineage mapping relationship between the source table and the target table based on the first new column name set and the corresponding first new column value set, and the second new column name set and the corresponding second new column value set, includes:

[0013] Taking the upstream node of the target node as the current node, each current node recursively traverses the upstream nodes of the current node, constructs at least one column mapping relationship based on all input columns of the current node and all output columns of the current node, and takes the upstream node of the current node as the current node until the current node becomes the downstream node of the source node;

[0014] During recursive traversal, all input columns of the downstream nodes of the source node are the first new column name set and the corresponding first new column value set, and all output columns of the upstream nodes of the target node are the second new column name set and the corresponding second new column value set; each current node constructs a corresponding column mapping relationship based on the rules for ETL processing of the current node, all input columns and all output columns of the current node;

[0015] Construct the bloodline mapping relationship based on all the column mapping relationships.

[0016] Preferably, the process of each current node constructing a corresponding column mapping relationship based on the rules for ETL processing of the current node, all input columns, and all output columns of the current node includes:

[0017] Determine whether the rule for the current node does not change the column names of all input columns;

[0018] If so, then based on the column name of each input column, traverse and find the corresponding column name of the output column of the input column, and construct a column mapping relationship between the column names of all input columns and the column names of all output columns;

[0019] If not, then according to the rules and each input column, traverse and find the corresponding output column of the input column, and construct a column mapping relationship between the column names of all input columns and the column names of all output columns; wherein, when there is a many-to-one or one-to-many transformation in the rules, a many-to-one or one-to-many column mapping relationship is selectively constructed.

[0020] Specifically, when the current node cannot map the input column to the corresponding output column, the column mapping relationship between the column name of the input column and the column name of the output column is not constructed.

[0021] Preferably, the step of constructing and executing the corresponding update operation statement based on the lineage mapping relationship, the second new column name set and the corresponding second new column value set, and the second old column name set and the corresponding second old column value set includes:

[0022] Based on the bloodline mapping relationship, the second new column name set and the corresponding second new column value set, the set of column names to be updated in the target table is obtained;

[0023] Based on the second old column name set and the corresponding second old column value set, and the column name set to be updated, the column value set to be updated corresponding to the column name set to be updated is obtained;

[0024] Based on the second old column value set, the column name set to be updated, and the column value set to be updated, construct the corresponding update operation statement and execute the update operation statement.

[0025] Preferably, obtaining the set of column names to be updated for the target table based on the lineage mapping relationship, the second new column name set, and the corresponding second new column value set includes:

[0026] Based on each new column name in the second new column name set, traverse the column names of the target table in the lineage mapping relationship and determine whether there is a target table column name that is the same as the new column name;

[0027] If it exists, add the new column name to the set of column names to be updated;

[0028] If it does not exist, the new column name will not be added to the set of column names to be updated.

[0029] Preferably, the step of constructing a corresponding update operation statement based on the second old column value set, the column name set to be updated, and the column value set to be updated, and then executing the update operation statement, includes:

[0030] The old column values ​​of the update operation statement are obtained based on the second set of old column values;

[0031] The column names of the update operation statement are obtained based on the set of column names to be updated;

[0032] The new column values ​​of the update operation statement are obtained based on the set of column values ​​to be updated.

[0033] The table name of the update operation statement is obtained based on the table name of the source table;

[0034] Based on the table name, the column name, the new column value, and the old column value, construct the corresponding update operation statement and execute the update operation statement.

[0035] Preferably, during a merger or acquisition, the data synchronization method is used to synchronize the update operations of the enterprise's employee data, wherein:

[0036] Store the kinship tree of each group in the database;

[0037] The group's tag is used as the root node, and the group's lineage tree is formed by attaching the current lineage tree of other groups within the project to the root node when the group participates in the corresponding project.

[0038] The current kinship tree is the kinship tree formed by the corresponding group before participating in the corresponding project.

[0039] Preferably, during a merger or acquisition of enterprises, the data synchronization method is used to synchronize the update operations of the enterprise's employee data, wherein:

[0040] When merging groups among enterprises, the matching degree between the first lineage tree and each second lineage tree is calculated. The second group corresponding to the second lineage tree with the highest matching degree with the first lineage tree is taken as the target group, and the first group is merged into the target group.

[0041] Wherein, the first bloodline tree is the bloodline tree of the first group in the first enterprise, and the second bloodline tree is the bloodline tree of the corresponding second group in the second enterprise.

[0042] Preferably, during a merger or acquisition of enterprises, the data synchronization method is used to synchronize the update operations of the enterprise's employee data, wherein:

[0043] The matching degree between the first bloodline relationship tree of the first group in the first enterprise and the second bloodline relationship tree of the corresponding second group in the second enterprise is as follows:

[0044] Where M is the matching degree, k1 is the first weight, k2 is the second weight, N is the number of matching node groups in the first and second lineage trees, and p n Let p′ be the nth node in the first kinship tree that matches the second kinship tree. n Let lv() be the nth node in the second kinship tree that matches the first kinship tree, and let lv() be the level of the corresponding node.

[0045] In a second aspect, the present invention also provides a data synchronization device for update operations, used to implement the data synchronization method for update operations described in the first aspect, the data synchronization device for update operations comprising:

[0046] At least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the processor for performing a data synchronization method for performing the update operation described in the first aspect.

[0047] Thirdly, the present invention also provides a non-volatile computer storage medium storing computer-executable instructions that are executed by one or more processors to perform a data synchronization method for performing the update operation described in the first aspect.

[0048] Unlike existing technologies, the present invention has at least the following beneficial effects:

[0049] This invention, when performing data synchronization based on logs, constructs a lineage mapping relationship between the columns to be updated in the source table and the columns to be updated in the target table by performing lineage analysis on the synchronized data. This enables update operations to be performed only on the columns to be updated and their corresponding values ​​in the target table, thereby improving the data synchronization efficiency of the update operation. While avoiding the huge performance loss caused by full column updates or full column value comparisons, it greatly reduces the risk of causing errors in the synchronization service and improves the data synchronization efficiency. Attached Figure Description

[0050] To more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings used in the embodiments of the present invention will be briefly described below. Obviously, the drawings described below are merely some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without any creative effort.

[0051] Figure 1This is a schematic diagram of the overall process of a data synchronization method for update operations provided in an embodiment of the present invention;

[0052] Figure 2a A partial flowchart illustrating a data synchronization method for update operations provided in this embodiment of the invention;

[0053] Figure 2b A partial flowchart illustrating another data synchronization method for update operations provided in this embodiment of the invention;

[0054] Figure 2c A partial flowchart illustrating another data synchronization method for update operations provided in this embodiment of the invention;

[0055] Figure 3 This is a schematic diagram of the specific process of step 30 in an embodiment of the present invention;

[0056] Figure 4 This is a schematic diagram of the specific process of step 40 in an embodiment of the present invention;

[0057] Figure 5 This is a schematic diagram of the specific process of step 401 in an embodiment of the present invention;

[0058] Figure 6 This is a schematic diagram of the specific process of step 403 in an embodiment of the present invention;

[0059] Figure 7 This is a schematic diagram illustrating a data synchronization method for an update operation provided in an embodiment of the present invention.

[0060] Figure 8 This is a schematic diagram of a scenario for another data synchronization method for update operations provided in an embodiment of the present invention;

[0061] Figure 9 This is a schematic diagram of the lineage tree of the corresponding group in a data synchronization method for update operations provided in an embodiment of the present invention;

[0062] Figure 10 This is a schematic diagram illustrating the evolution of the lineage tree of the corresponding group in a data synchronization method for update operations provided in an embodiment of the present invention;

[0063] Figure 11 This is another schematic diagram of the lineage tree of the corresponding group in a data synchronization method for update operations provided in an embodiment of the present invention;

[0064] Figure 12 This is a schematic diagram of the architecture of the data synchronization device for update operations provided in an embodiment of the present invention. Detailed Implementation

[0065] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0066] In the description of this invention, the terms "inner", "outer", "longitudinal", "lateral", "upper", "lower", "top", "bottom", etc., indicate the orientation or positional relationship based on the orientation or positional relationship shown in the accompanying drawings. They are only for the convenience of describing this invention and do not require that this invention must be constructed and operated in a specific orientation. Therefore, they should not be construed as limiting this invention.

[0067] In this invention, the terms "first," "second," etc., are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features. Therefore, a feature defined with "first," "second," etc., may explicitly or implicitly include one or more of that feature. In the description of this application, unless otherwise stated, "a plurality of" means two or more.

[0068] Furthermore, the technical features involved in the various embodiments of the present invention described below can be combined with each other as long as they do not conflict with each other.

[0069] Example 1:

[0070] Synchronizing update operations involves updating and maintaining existing data, i.e., incremental synchronization. In incremental synchronization, existing technologies employ the following two methods for synchronizing update operations:

[0071] Method 1: Update all columns.

[0072] Based on the synchronization logs of the update operations in the source table, the new and old column values ​​in the source table are cleaned and transformed to obtain old and new column value results. A full column update is then performed on the target table based on these results. The new column values ​​are used as the column values ​​to be updated, and the old column values ​​are used as the WHERE condition. This is used to encapsulate the corresponding SQL (Structured Query Language) statement for the update operation, which is then executed to update the target table.

[0073] Method 2: Compare all column values.

[0074] Based on the synchronization logs of the source table's update operations, the new and old column values ​​of the source table are cleaned and transformed to obtain old and new column value results. Since the old and new column value results are obtained through the same cleansing and transformation rule, the column values ​​have the same order. By comparing the old and new column value results column by column, columns with unequal values ​​are identified, which are the columns in the target table that need to be updated. The new column value of these columns is used as the column value to be updated. The old column value or the primary key is used as the WHERE condition to encapsulate the corresponding SQL statement for the update operation. This SQL statement is then executed to perform the update operation on the target table.

[0075] Method 1 incurs significant performance overhead from updating all columns of the table. This inefficiency is further exacerbated when the target table has a large number of columns. For example, synchronizing updates to each column of a single record in the source table requires updating the values ​​of every column in all records of the target table, wasting the synchronization service's computational resources on all records except the one to be updated. Method 2 involves column-by-column comparisons. When the target table has many columns, comparing the values ​​in each column incurs substantial performance overhead. When columns contain large fields, comparing the values ​​in every column during each synchronization is complex and time-consuming, making it difficult to implement and resulting in low synchronization efficiency. "Large fields" refers to fields with large data lengths stored in the database. For example, in Oracle databases, `long` (long integer), `blob` (unstructured binary data), and `clob` (single-byte character data type) are considered large field types. For example, if only updating each column of a single record in the source table is required for synchronization, the column values ​​of each column in all records of the target table need to be compared column by column to obtain the column that needs to be updated and its corresponding value, and then the column is updated. In this process, all records in the source and target tables are compared, which results in a certain waste of computing resources for the synchronization service.

[0076] To address the aforementioned problems, Embodiment 1 of the present invention provides a data synchronization method for update operations, such as... Figure 1 As shown, the data synchronization method for the update operation includes:

[0077] Step 10: Receive the synchronization log after the source table performs the update operation, and generate the supplementary new column name set and the corresponding supplementary new column value set of the source table based on the first old column name set and the corresponding first old column value set, the first new column name set and the corresponding first new column value set of the source table in the synchronization log.

[0078] The first set of old column names is the set of column names in the source table before the update operation, and the first set of old column values ​​is the set of column values ​​in the source table before the update operation. Since the new column values ​​recorded in the logs after updating the source table during data synchronization are generally the columns that need to be updated, parsing the logs yields the first set of new column names containing only the column names that need to be updated, and the first set of new column values ​​containing only the column values ​​that need to be updated.

[0079] During data synchronization, data processing operations such as cleaning and transformation may involve handling outdated column values. Therefore, before data processing, the new column values ​​in the source table must be padded with the old column values. Failure to padded can easily lead to errors in the synchronization service during subsequent data processing. This is because, depending on business needs and the actual state of the synchronized data in production, data processing may require merging some columns in the source table or incorporating other old columns from the source table into calculations to obtain new columns in the target table. In these cases, it is essential to use the padded, complete new column values ​​for data processing to avoid errors in the data synchronization service.

[0080] Step 20: Perform ETL processing on the first old column name set and its corresponding first old column value set, and the new column name set and its corresponding new column value set, respectively, to generate the second old column name set and its corresponding second old column value set, and the second new column name set and its corresponding second new column value set of the target table.

[0081] ETL (Extract-Transform-Load) is the process of extracting, transforming, and loading data from the source to the destination. During data synchronization, ETL processing is necessary to ensure the data's correctness and validity. Since the basic syntax of the SQL statement for an update operation is "UPDATE table_name SET column_name = new column_value WHERE column_name = old column_value", for the target table, when encapsulating and executing the SQL statement for synchronization, the column name and new column value must be used as the SET condition columns to determine the columns to be updated, and the column name and old column value must be used as the WHERE condition columns to specify the columns to be updated. Therefore, ETL processing is required for the first set of old column names and their corresponding first set of old column values, and for the set of newly updated column names and their corresponding newly updated column values, respectively, to ensure the correctness of the synchronized data. Those skilled in the art can refer to the embodiments of this invention and, without creative effort, choose other implementation methods according to specific business needs and usage scenarios to implement the data synchronization method for the update operation of this invention.

[0082] Step 30: Construct the lineage mapping relationship between the source table and the target table based on the first new column name set and the corresponding first new column value set, and the second new column name set and the corresponding second new column value set.

[0083] The data synchronization method for update operations in this embodiment of the invention obtains the data lineage relationship between the columns to be updated in the source table and the updated columns in the target table, thereby obtaining a set of column names to be updated and a corresponding set of column values ​​to be updated. Since update statements only need to be generated and executed for the columns to be updated for data update synchronization, a first new set of column names and a corresponding first new set of column values ​​are used. That is, only the columns to be updated in the source table are used to find the corresponding data lineage relationship, thereby improving the synchronization efficiency of the update operation. Here, data lineage relationship refers to the relationship formed between data throughout its entire lifecycle, from generation, processing, manipulation, fusion, flow to final destruction. It records the link relationship of data generation, and these relationships are similar to human blood relationships, hence the name data lineage relationship.

[0084] Step 40: Based on the lineage mapping relationship, the second new column name set and the corresponding second new column value set, the second old column name set and the corresponding second old column value set, construct the corresponding update operation statement and execute the update operation statement.

[0085] When the target table has a primary key or unique index, the WHERE condition of the update operation uses the primary key or unique index. A primary key, also known as a primary key, is one or more fields in a table whose values ​​are used to uniquely identify a record in the table. A unique index is an index that does not allow rows to have the same index value; it prohibits duplicate indexes or key values.

[0086] like Figure 2a , Figure 2b and Figure 2c As shown, this embodiment of the invention provides a detailed flowchart of a data synchronization method for update operations, wherein... Figure 2a and Figure 2c middle, Figure 2b and Figure 2cThe content shown in the diagram has some overlap. The data synchronization method of this embodiment of the invention completes the new column set of source table A parsed from the log based on the old column set of source table A, that is, adding the third column of the old column set of source table A as the third column of the new column set of source table A; then, the old column set of source table A and the completed new column set are cleaned and transformed respectively to obtain the old column set and the new column set of target table B. Since the third column of the new column set of source table A has already been cleaned and transformed, the column value of the third column of the new column set of target table B is the new column value after cleansing and transformation; according to the preset cleansing and transformation rules, the lineage mapping relationship between the new column set of source table A and the new column set of target table B is recursively searched and constructed; finally, based on the lineage mapping relationship, the columns in target table B that need to be updated are obtained, and the corresponding SQL statements are constructed and executed to realize the data synchronization of this update operation. The preset cleansing and transformation rules are specified by those skilled in the art according to the specific use case.

[0087] To better illustrate the data synchronization method for the update operation of the present invention, step 30 of the data synchronization method of the embodiment of the present invention will be further refined below. Specifically, as follows: Figure 3 As shown, ETL processing is performed in the form of a process, with the source table as the source node of the process and the target table as the target node of the process. The process also includes at least one data flow node, wherein:

[0088] Step 30 includes:

[0089] Step 301: Taking the upstream node of the target node as the current node, recursively traverse the upstream nodes of each current node. Based on all input columns of the current node and all output columns of the current node, construct at least one column mapping relationship, and use the upstream node of the current node as the current node, until the current node becomes the downstream node of the source node. In the ETL process, the source node and the target node are data flow nodes, and the data flow between the source node and the target node is a data processing data flow node. Starting from the source node, find the column mapping relationship from the source node to the target node for each column in the column to be updated. In an optional embodiment, at least one column mapping relationship from the source node to the target node is maintained in a hash table.

[0090] Step 302: During recursive traversal, all input columns of the downstream nodes of the source node are the first new column name set and the corresponding first new column value set, and all output columns of the upstream nodes of the target node are the second new column name set and the corresponding second new column value set; each current node constructs a corresponding column mapping relationship according to the rules for ETL processing of the current node, all input columns and all output columns of the current node.

[0091] Specifically, when each current node constructs a column mapping relationship based on the rules for ETL processing of the current node, all input columns, and all output columns of the current node, it determines whether the rules of the current node do not change the column names of all input columns. If so, it iterates through the corresponding output column names of each input column to construct a column mapping relationship between the column names of all input columns and the column names of all output columns. If not, it iterates through the corresponding output columns of each input column based on the rules and each input column to construct a column mapping relationship between the column names of all input columns and the column names of all output columns. When there are many-to-one or one-to-many transformations in the rules, many-to-one or one-to-many column mapping relationships are selectively constructed. When the current node cannot map an input column to a corresponding output column, a column mapping relationship between the column names of the input column and the column names of the output column is not constructed. By ensuring that the corresponding column mapping does not include the column mapping relationship from the input column to the output column when it is impossible to map the input column to the corresponding output column, and by ensuring that the column mapping relationship does not contain the column mapping relationship from the input column to the output column in the lineage mapping relationship, the relevant columns are filtered in the constructed and executed update operation statement, thus avoiding errors in the synchronization service.

[0092] Step 303: Construct the bloodline mapping relationship based on all the column mapping relationships.

[0093] Different data stream nodes follow different ETL processing rules. Those skilled in the art, based on the specific use case of data synchronization, can independently select the data stream nodes to be added between the source and target nodes and determine the rules for the corresponding data stream nodes without creative effort. In ETL, each data stream node must construct a column mapping relationship between its input and output columns according to its own rules. The data synchronization method for update operations in this embodiment provides an example method for constructing column mapping relationships. Those skilled in the art can adjust the specific implementation method according to the actual production environment and business needs without creative effort.

[0094] Abstract data flow nodes, including source nodes, target nodes, cleaning and transformation nodes, aggregation nodes, connection nodes, and incremental comparison nodes, inherit from data flow nodes. A recursive method is created that starts downstream of the source node and proceeds upstream of the target node, searching for the source column corresponding to the target column. If found, the source column is returned; otherwise, null is returned. A column mapping method is created in each data flow node for data processing. The default column mapping method constructs a column mapping relationship based on the column names of the output and input columns. If other data flow nodes use different mapping methods, those skilled in the art can rewrite the column mapping method to construct the column mapping relationship according to the existing ETL processing rules for each data flow node. In optional embodiments, the cleaning and transformation node processes the column data input to the cleaning and transformation node according to cleaning and transformation rules to obtain the column mapping relationship of the cleaning and transformation node. Since cleaning and transformation rules often have many-to-one or one-to-many relationships, many-to-one or one-to-many column mapping relationships are constructed. The column transformation node constructs its column mapping relationship based on column attributes in the column transformation rules, such as value type and value name. When the column transformation rule is an expression, if the expression contains the column name of the input column data, then the column mapping relationship of the column transformation node is constructed; if the expression does not contain the column name of the input column data, then the column mapping relationship of the column transformation node is not constructed.

[0095] During data lineage analysis, the data synchronization method of this embodiment only needs to trace the data lineage of the column to be updated in order to construct the update statement for the column to be updated, thereby improving the efficiency of data synchronization. Therefore, it is necessary to filter columns in the source table that do not need to be updated. Furthermore, when constructing the lineage mapping relationship between the source column and the target column, there may be cases where some source columns cannot be mapped to the target column. When the source table updates this column, the target table does not need to perform an update operation and can be skipped directly. When constructing the update SQL statement for the target table, this column is filtered out, and the corresponding update operation is ignored to avoid causing errors in the data synchronization service. In ETL, if data A generates data B after processing by a data flow node, then data A and data B have a lineage relationship. To better illustrate the data synchronization method for update operations of this invention, step 40 of the data synchronization method of this embodiment will be further refined below. Specifically, as follows... Figure 4 As shown, step 40 includes:

[0096] Step 401: Based on the lineage mapping relationship, the second new column name set, and the corresponding second new column value set, obtain the column name set to be updated for the target table. The column name set to be updated is the set of column names in the target table that need to be updated.

[0097] Step 402: Based on the second old column name set, the corresponding second old column value set, and the column name set to be updated, obtain the column value set to be updated corresponding to the column name set to be updated. The column value set to be updated is the set of column values ​​in the target table that need to be updated.

[0098] Step 403: Based on the second old column value set, the column name set to be updated, and the column value set to be updated, construct the corresponding update operation statement and execute the update operation statement.

[0099] By using only the columns in the source table that need updating, the corresponding data lineage is found to improve the synchronization efficiency of the update operation. To better illustrate the data synchronization method for update operations of this invention, step 401 of the data synchronization method in this embodiment will be further refined below. Specifically, as follows... Figure 5 As shown, step 401 includes:

[0100] Step 4011: Based on each new column name in the second new column name set, traverse the column names of the target table in the lineage mapping relationship and determine whether there is a target table column name that is the same as the new column name.

[0101] Step 4012: If it exists, add the new column name to the set of column names to be updated.

[0102] Step 4013: If it does not exist, then do not add the new column name to the set of column names to be updated.

[0103] Based on the update operation syntax "UPDATE table_name SET column_name = new column_value WHERE column_name = old column_value", an SQL statement for the update operation is constructed and executed. To better illustrate the data synchronization method for update operations of this invention, step 403 of the data synchronization method in this embodiment will be further refined below. Specifically, as follows... Figure 6 As shown, step 403 includes:

[0104] Step 4031: Obtain the old column values ​​of the update operation statement based on the second old column value set.

[0105] Step 4032: Obtain the column names of the update operation statement based on the set of column names to be updated.

[0106] Step 4033: Obtain the new column values ​​of the update operation statement based on the set of column values ​​to be updated.

[0107] Step 4034: Obtain the table name of the update operation statement based on the table name of the source table.

[0108] Step 4035: Based on the table name, the column name, the new column value, and the old column value, construct the corresponding update operation statement and execute the update operation statement.

[0109] The data synchronization method for update operations in this invention determines the columns to be updated in the source table and the columns to be updated in the target table, as well as the lineage mapping relationship between them, constructs and executes the corresponding update operation statements, and realizes data synchronization of update operations. While avoiding the huge performance loss caused by full column updates or full column value comparisons, it greatly reduces the risk of causing synchronization service operation errors and improves data synchronization efficiency.

[0110] This invention provides a specific example of constructing column mapping relationships. The following uses an ETL process as an example to illustrate the process of constructing column mapping relationships.

[0111] The data in source table A is processed through the cleaning and transformation node, where the data columns are split. Subsequently, through the column transformation node, the column names and column types are transformed, and finally the data is output to the target table B.

[0112] The source columns of table A are A1, B1, and C1. After passing through the cleansing and transformation node, B1 is split into B2-1 and B2-2. That is, after the cleansing and transformation node, the input columns are A1, B1, and C1, and the output columns are A1, B2-1, B2-2, and C1. Subsequently, through the column transformation node, the column names and types are transformed: A1 is transformed to A3, C1 to C3, B2-1 to B3-1, and B2-2 to B3-2. Therefore, for the column transformation node, its input columns are A1, B2-1, B2-2, and C1, and its output columns are A3, B3-1, B3-2, and C3.

[0113] When constructing the column mapping relationship between the source column and the target column, first obtain all column information of the target node A3, B3-1, B3-2, C3, traverse each column in the target node, and trace the column mapping relationship between the source column and the target column column one by one.

[0114] The target node A3 first passes through the upstream column transformation node. The output column corresponding to the input column A1 of the column transformation node is A3. Tracing back to the source column of A3, it is A1. Then it passes through the upstream cleaning transformation node. The output column corresponding to the input column A1 of the cleaning transformation node is A1. Tracing back to the source column of A3, it is still A1. Thus, the mapping relationship from the source column A1 to the target column A3 is obtained.

[0115] The target node B3-1 first passes through the upstream column transformation node. The output column corresponding to the input column B2-1 of the column transformation node is B3-1. Tracing back to the source column of B3-1, it is B2-1. Then it passes through the upstream cleaning transformation node. The output columns corresponding to the input column B1 of the cleaning transformation node are B2-1 and B2-2. Tracing back to the source column of B3-1, it is B1. Thus, the mapping relationship from the source column B1 to the target column B3-1 is obtained.

[0116] The target node B3-2 first passes through the upstream column transformation node. The output column corresponding to the input column B2-2 of the column transformation node is B3-2. Tracing back to the source column of B3-2, it is B2-2. Then it passes through the upstream cleaning transformation node. The output columns corresponding to the input column B1 of the cleaning transformation node are B2-1 and B2-2. Tracing back to the source column of B3-2, it is B1. Thus, the mapping relationship from the source column B1 to the target column B3-2 is obtained.

[0117] The target node C3 first passes through the upstream column transformation node. The output column corresponding to the input column C1 of the column transformation node is C3. Tracing back to the source column of C3, it is C1. Then it passes through the upstream cleaning transformation node. The output column corresponding to the input column C1 of the cleaning transformation node is C1. Tracing back to the source column of C3, it is still C1. Thus, the mapping relationship from the source column C1 to the target column C3 is obtained.

[0118] As can be seen from the above tracing process, the mapping relationship between the source column and the target column is A1 to A3, B1 to B3-1 and B3-2, and C1 to C3.

[0119] The source table updates A1 and B1. Based on the column mapping relationship between the source and target columns, the columns to be updated in the target table are A3, B3-1, and B3-2.

[0120] It is worth noting that in actual production, those skilled in the art can directly apply the data synchronization method of the update operation in the embodiments of the present invention to data traceability scenarios, or improve the data synchronization method of the embodiments of the present invention and apply it to data traceability scenarios without creative effort. When performing data analysis and processing, data comes from numerous sources. After data undergoes generation, cleaning, processing, and circulation, if problems arise, it is necessary to trace the data to find the cause of the problem. Based on the traceability of data lineage, the source and destination of specified data can be traced throughout the entire data processing process, thereby facilitating user problem identification and improving data analysis efficiency.

[0121] Example 2:

[0122] This embodiment also incorporates the application scenario of data migration between enterprises, and uses the technical descriptions in the relevant scenarios to illustrate the implementation process of the features of the present invention in the scenario.

[0123] This embodiment takes data governance in a corporate acquisition or merger scenario as an example. For instance, during a merger or acquisition, the data synchronization method for update operations in Embodiment 1 of this invention is used. By performing data lineage analysis on employee data (synchronized data), a lineage mapping relationship is constructed between the original company's employee data (the column to be updated in the source table) and the current company's employee data (the column to be updated in the target table). This allows for updating only the column to be updated in the target table while simultaneously using the lineage mapping relationship to allocate employees, ensuring that employees are assigned to appropriate teams (i.e., work groups responsible for corresponding functions), and performing employee data migration and governance in the database. This embodiment provides the following methods, specifically including:

[0124] The database stores the lineage tree of each group; wherein, the group's label is used as the root node, and the lineage tree of the group is formed by attaching the current lineage tree of other groups in the project when the group participates in the corresponding project (hereinafter also referred to as the project group) to the root node; the current lineage tree is the lineage tree formed by the corresponding group before participating in the corresponding project;

[0125] When merging groups among enterprises, the matching degree between the first bloodline tree and each second bloodline tree is calculated. The second group corresponding to the second bloodline tree with the highest matching degree with the first bloodline tree is taken as the target group, and the first group is merged into the target group. Herein, the first bloodline tree is the bloodline tree of the first group in the first enterprise, and the second bloodline tree is the bloodline tree of the corresponding second group in the second enterprise.

[0126] The specific matching degree between the first bloodline relationship tree of the first group in the first enterprise and the second bloodline relationship tree of the corresponding second group in the second enterprise is as follows:

[0127] Where M is the matching degree, k1 is the first weight, k2 is the second weight, N is the number of matching node groups in the first and second lineage trees, and p n p is the nth node in the first kinship tree that matches the second kinship tree. ′ n Let lv() be the nth node in the second kinship tree that matches the first kinship tree, and let lv() be the level of the corresponding node.

[0128] The process of merging the first group into the target group includes not only incorporating the employees of the first group into the target group in practice, but also incorporating the employees of the first group into the data corresponding to the target group in the database using the data synchronization method of the update operation of Embodiment 1 of the present invention.

[0129] This embodiment takes the acquisition or merger of Company A and Company B as an example. If it is necessary to assign employees of Company B to corresponding groups in Company A and migrate all employee data in Company B to the database of Company A, before the data migration, Company A and Company B each have their own database, and a complete data lineage mapping relationship is formed in the database. The data lineage mapping relationship is carried in the database in the form of a lineage tree. This lineage mapping relationship represents the familiarity of the corresponding group or employee with a certain field.

[0130] For example, the root node is the label of the group to which each employee belongs, representing the area that the employee is most familiar with. In actual use, multiple groups may participate in the same project. During the project, employees will exchange technical knowledge, thus expanding their areas of expertise. Therefore, the lineage mapping relationship of the corresponding group or employee is expanded. For example, when an employee's group participates in the corresponding project group, the lineage relationship tree of each group in the corresponding project group is used as a lower branch of the root node. That is, the current lineage relationship tree of other groups in the project group is attached to the root node. When the same node appears during attachment, the higher-level node (i.e., the root node or the node with fewer nodes between it and the root node) is retained, and the lower-level node (i.e., the node with more nodes between it and the root node) is removed, thereby updating the lineage mapping relationship of each group.

[0131] like Figure 7 As shown, if company B has the following... Figure 7 The four groups shown are labeled as Label 1, Label 2, Label 3, and Label 4. This is merely an example; in actual implementation, each label would be derived by a person skilled in the art based on the main work functions of each group. Furthermore, the projects each group has participated in since its inception would be detailed. Figure 8 As shown, the timeline progresses from top to bottom, meaning project A occurs first and project D occurs last. Then, after project D, the lineage mapping relationship between the groups, i.e., the lineage tree, is as follows: Figure 9 As shown, in this embodiment, Figure 10 The example shown illustrates the process of updating bloodline mapping relationships. After experiencing Project C, the bloodline relationship tree of Group 3 is as follows: Figure 10 As shown in kinship tree 1, after experiencing project C, the kinship tree of group 5 is as follows: Figure 10As shown in lineage tree 2, after Project D, which involved both groups 3 and 5, the root node of group 3 is attached to lineage tree 2, and the root node of group 5 is attached to lineage tree 1. Since lineage tree 1 already contains nodes identical to those in lineage tree 2 (labels 1, 3, and 4), a deduplication operation is performed during attachment. Nodes relatively far from the root node (i.e., nodes with a large number of nodes between them and the root node) are removed, and their corresponding branches are also removed. Figure 10 In the bloodline tree 3, the branch marked with × is the branch to be removed. Similarly, when bloodline tree 1 is attached to bloodline tree 2, the branch marked with × is the branch to be removed.

[0132] When merging Company B into Company A, the lineage trees of each group in Company B are matched with the lineage trees of each group in Company A. The group with the highest matching degree is selected for merging. That is, the employees of the corresponding group in Company B are assigned to the group in Company A with the highest matching degree. For example, the group in Company B is called the first group, and the group in Company A is called the second group. In the process of calculating the matching degree, the corresponding lineage tree actually refers to the corresponding group. Therefore, in the following embodiments, for the sake of simplicity, the first group is used as a substitute for the lineage tree of the first group, and the second group is used as a substitute for the lineage tree of the second group.

[0133] For each group 1, calculate the matching degree between group 1 and each group 2, specifically as follows:

[0134]

[0135] Where M is the matching degree, k1 is the first weight, k2 is the second weight, N is the number of matching nodes in the first and second groups, and p n p is the nth node in the first group that matches the second group. ′ n Let lv() be the nth node in the second group that matches the first group. lv() is the level of the corresponding node. That is, according to the distance between the node and the root node, the root node is classified as level 1, and the subordinate nodes of the root node are classified as level 2, level 3, etc.

[0136] The rules for matching nodes are derived by those skilled in the art based on experience. For example, if company A has the label "Upper-level Application Group" and company B has the label "Interface Function Group," then those skilled in the art can analyze that both labels belong to higher-level software functions, and therefore the two labels can be considered to match. To more clearly demonstrate that two nodes are matched, this embodiment... Figure 11Taking the example of matching "label n" with "label n'", such as "label 1" matching "label 1'", forming a set of matching nodes, then according to... Figure 11 As can be seen, the number of matching node groups N = 5 in the first group and the second group 1, where label 1 is level 2 in the first group and label 1' is level 2 in the second group 1, then |lv(p1) - lv(p ′ 1) |=2-2=0; Label 2 is level 3 in the first group, label 2' is level 1 in the second group 1, |lv(p2)-lv(p ′ 2) |=3-1=2, and so on. The number of matching node groups in the first group and the second group 2 is N=4. Among them, label 1 in the first group has no matching node in the second group 2 and does not participate. The matching degree between the first group and the second group 2 is calculated using the same method as the matching degree calculation for the first group and the second group 1. This allows for comparison, and the second group with the highest matching degree is selected as the target group. The first group is then merged into the target group.

[0137] This embodiment uses the establishment of lineage mapping relationships for groups and the merging of groups as an example. However, in actual implementation, it is also possible to establish lineage mapping relationships for individual employees. Employees are merged into corresponding groups according to their matching degree with each group. When establishing lineage mapping relationships for individual employees, the label corresponding to the group is used as the root node of the employee's lineage tree. When an employee participates in a project group, the lineage trees of other employees in that project group are attached to the employee's root node, and node deduplication is performed to obtain the employee's lineage tree after participating in the project group. When merging enterprises, it is necessary to assign the employee to the group with the highest matching degree with the employee. The matching degree between the employee and the corresponding group is the average of the matching degrees of the employee's lineage trees with all employees in the group.

[0138] Example 3:

[0139] like Figure 12 The diagram shown is an architectural schematic of a data synchronization device for update operations according to an embodiment of the present invention. The data synchronization device for update operations in this embodiment includes one or more processors 31 and a memory 32. Figure 12 Take a processor 31 as an example.

[0140] Processor 31 and memory 32 can be connected via a bus or other means. Figure 12 Taking the example of a connection between China and Israel via a bus.

[0141] The memory 32, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs and non-volatile computer-executable programs, such as the data synchronization method for update operations in Embodiment 1. The processor 31 executes the data synchronization method for update operations by running the non-volatile software program and instructions stored in the memory 32.

[0142] Memory 32 may include high-speed random access memory, and may also include non-volatile memory, such as at least one disk storage device, flash memory device, or other non-volatile solid-state storage device. In some embodiments, memory 32 may optionally include memory remotely located relative to processor 31, which can be connected to processor 31 via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks, mobile communication networks, and combinations thereof.

[0143] The program instructions / modules are stored in the memory 32. When executed by one or more processors 31, they perform the data synchronization method for updating operations described in Embodiment 1 above, for example, executing the above-described... Figure 1 and Figures 2a-6 The steps shown.

[0144] It is worth noting that the information interaction and execution process between the modules and units in the above-mentioned device and system are based on the same concept as the processing method embodiment of the present invention. For details, please refer to the description in the method embodiment of the present invention, and will not be repeated here.

[0145] Those skilled in the art will understand that all or part of the steps in the various methods of the embodiments can be implemented by a program instructing related hardware. The program can be stored in a computer-readable storage medium, which may include: read-only memory (ROM), random access memory (RAM), magnetic disk or optical disk, etc.

[0146] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A data synchronization method for update operations, characterized in that, The data synchronization method for the update operation includes: The system receives the synchronization log after the source table performs an update operation. Based on the first old column name set and its corresponding first old column value set, and the first new column name set and its corresponding first new column value set of the source table in the synchronization log, the system adds the column names and their corresponding first old column values ​​that are not included in the first new column name set from the first old column name set to the first new column name set and the first new column value set, thereby generating a new set of column names and a corresponding new set of column values ​​for the source table. The first old column name set is the set of column names in the source table before the update operation was performed. ETL processing is performed on the first old column name set and its corresponding first old column value set, and the new column name set and its corresponding new column value set, respectively, to generate the second old column name set and its corresponding second old column value set, and the second new column name set and its corresponding second new column value set of the target table. Based on the first new column name set and the corresponding first new column value set, and the second new column name set and the corresponding second new column value set, construct the lineage mapping relationship between the source table and the target table; Based on the lineage mapping relationship, the second new column name set and its corresponding second new column value set, and the second old column name set and its corresponding second old column value set, an update operation statement for the target table is constructed and executed. This includes: obtaining the column name set to be updated for the target table based on the lineage mapping relationship, the second new column name set and its corresponding second new column value set; obtaining the column value set to be updated corresponding to the column name set based on the second new column name set and its corresponding second new column value set, and the column name set to be updated; constructing a corresponding update operation statement based on the second old column value set, the column name set to be updated, and the column value set to be updated, and executing the update operation statement.

2. The data synchronization method for update operations according to claim 1, characterized in that, ETL processing is performed in the form of a process, with the source table as the source node and the target table as the target node. The process also includes at least one data flow node, wherein: The step of constructing the lineage mapping relationship between the source table and the target table based on the first new column name set and the corresponding first new column value set, and the second new column name set and the corresponding second new column value set, includes: Taking the upstream node of the target node as the current node, recursively traverse the upstream nodes of the current node, construct at least one column mapping relationship based on all input columns of the current node and all output columns of the current node, and take the upstream node of the current node as the new current node until the current node becomes the downstream node of the source node. During recursive traversal, all input columns of the downstream nodes of the source node are the first new column name set and the corresponding first new column value set, and all output columns of the upstream nodes of the target node are the second new column name set and the corresponding second new column value set; each current node constructs a corresponding column mapping relationship based on the rules for ETL processing of the current node, all input columns and all output columns of the current node; Construct the bloodline mapping relationship based on all the column mapping relationships.

3. The data synchronization method for update operations according to claim 2, characterized in that, The construction of a corresponding column mapping relationship for each current node based on the rules for ETL processing of the current node, all input columns and all output columns of the current node includes: Determine whether the rule for the current node does not change the column names of all input columns; If so, then based on the column name of each input column, traverse and find the corresponding column name of the output column of the input column, and construct a column mapping relationship between the column names of all input columns and the column names of all output columns; If not, then according to the rules and each input column, traverse and find the corresponding output column of the input column, and construct a column mapping relationship between the column names of all input columns and the column names of all output columns; Specifically, when the current node cannot map the input column to the corresponding output column, the column mapping relationship between the column name of the input column and the column name of the output column is not constructed.

4. The data synchronization method for update operations according to claim 1, characterized in that, The step of obtaining the set of column names to be updated in the target table based on the bloodline mapping relationship, the second new column name set, and the corresponding second new column value set includes: Based on each new column name in the second new column name set, traverse the column names of the target table in the lineage mapping relationship and determine whether there is a target table column name that is the same as the new column name; If it exists, add the new column name to the set of column names to be updated; If it does not exist, the new column name will not be added to the set of column names to be updated.

5. The data synchronization method for update operations according to claim 1, characterized in that, The step of constructing and executing the corresponding update operation statement based on the second old column value set, the column name set to be updated, and the column value set to be updated includes: The old column values ​​of the update operation statement are obtained based on the second set of old column values; The column names of the update operation statement are obtained based on the set of column names to be updated; The new column values ​​of the update operation statement are obtained based on the set of column values ​​to be updated. The table name of the update operation statement is obtained based on the table name of the source table; Based on the table name, the column name, the new column value, and the old column value, construct the corresponding update operation statement and execute the update operation statement.

6. A data synchronization device for update operations, characterized in that, The method includes at least one processor and a memory, which are connected via a data bus. The memory stores instructions that can be executed by the at least one processor. After being executed by the processor, the instructions are used to perform the data synchronization method for updating the operation according to any one of claims 1-5.

Images