Data processing method and device for distributed database, equipment, medium and product

Abstract

The invention relates to a data processing method and device for a distributed database, equipment, a medium and a product. The method comprises the following steps: in response to a merging instruction for a distributed database, determining a source table and a target table corresponding to the merging instruction; performing left connection on the source table and the target table to obtain a left connection result; based on the null replacement function and the original distribution strategy, redistributing the left connection result, so that the left connection result is routed to the corresponding target data node for merging operation; wherein the null replacement function comprises a first parameter and a second parameter; under the condition that the first parameter is empty, the return value of the null value replacement function is the second parameter, and under the condition that the first parameter is not empty, the return value of the null value replacement function is the first parameter; the first parameter is a matching identifier, and the second parameter is source data corresponding to the source distribution column under the condition that the source distribution column is not matched with the target distribution column. By adopting the method, the data merging performance in the distributed database can be improved.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a data processing method, apparatus, device, medium and product for a distributed database. Background Technology

[0002] In databases, the `MERGE INTO` command is used to merge data from a source table into a target table. Generally, it works based on the join condition: if a row in the target table can be joined with a row in the source table, that row from the source table is used to update the target table; otherwise, the row from the source table is inserted into the target table. In distributed databases, because data is distributed across different data nodes, the data in the source and target tables must be redistributed according to the join condition before a left join is performed. After the left join, the source table's data is completely output, and the right-hand side of the target table (where data cannot be joined with the source table) is filled with NULL values. Therefore, the distribution column of the target table cannot be used as the distribution key for data redistribution (because the target table will contain empty rows in the left join result), and data cannot be redistributed to the corresponding data nodes based on the target table. Therefore, `MERGE INTO` operations cannot be performed on the data nodes.

[0003] For distributed databases, the commonly used technique is to send the connected data to the coordinator node, and then the coordinator node performs a merge into operation on the corresponding data node to which the data was sent. However, this execution plan is very inefficient, which greatly reduces the performance of merge into. Summary of the Invention

[0004] Therefore, it is necessary to provide a data processing method, apparatus, device, medium, and product for distributed databases to address the aforementioned technical problems, thereby improving the performance of data merging in distributed databases.

[0005] Firstly, this application provides a data processing method for a distributed database, including:

[0006] In response to a merge command for a distributed database, determine the source table and target table corresponding to the merge command;

[0007] A left join is performed on the source table and the target table to obtain a left join result; wherein, the left join result includes the source distribution column and the corresponding source data of the source table, the target distribution column associated with the source distribution column in the target table, and the matching identifier between the source distribution column and the target distribution column;

[0008] Based on the null value replacement function and the original distribution strategy, the left join result is redistributed so that the left join result is routed to the corresponding target data node for merging operation; wherein, the null value replacement function includes a first parameter and a second parameter; when the first parameter is empty, the return value of the null value replacement function is the second parameter, and when the first parameter is not empty, the return value of the null value replacement function is the first parameter; the first parameter is the matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source distribution column and the target distribution column do not match.

[0009] In one embodiment, the redistribution of the left join results based on the null value replacement function and the original distribution strategy, so that the left join results are routed to the corresponding target data nodes for merging operations, includes:

[0010] Based on the null value replacement function, the return value of each value in the matching identifier is used as the distribution key; according to the original distribution strategy, routing calculation is performed on each distribution key to obtain the target data node corresponding to each value; through the data exchange operator, the source data of the source distribution column corresponding to the value is transmitted to the corresponding target data node so that the corresponding target data node can perform a merging operation.

[0011] In one embodiment, when the source distribution column matches the target distribution column, the corresponding value in the matching identifier is the target distribution column; when the source table and the target table do not match, the corresponding value is empty.

[0012] In one embodiment, the step of performing routing calculations on each distribution key according to the original distribution strategy to obtain the target data node corresponding to each row value includes:

[0013] If the distribution key is the first parameter, then according to the original distribution strategy, the data node corresponding to the first parameter is determined as the target data node; if the distribution key is the second parameter, then according to the original distribution strategy, the data node that conforms to the distribution rules of the target table is determined as the target data node.

[0014] In one embodiment, the original distribution strategy is a pre-defined sharding distribution rule for the target table in the distributed database, and the original distribution strategy is a hash distribution rule, a range distribution rule, a list distribution rule, or a composite distribution rule.

[0015] In one embodiment, the left join result further includes a data merging rule for each row in the matching identifier; for rows with non-empty values, the data merging rule is a data update; for rows with empty values, the data merging rule is a data insertion.

[0016] In one embodiment, the step of transmitting the source data of the source distribution column corresponding to each row value to the corresponding target data node through the data exchange operator, so that the corresponding target data node can perform a merging operation, includes:

[0017] The data exchange operator transmits the data merging rules corresponding to each row value and the source data of the source distribution column to the corresponding target data node, so that the corresponding target data node can merge the source data and the target data in the target table according to the data merging rules.

[0018] In one embodiment, when the data merging rule is a data update, the data merging rule is used to instruct the target data node to replace the target data of the target distribution column associated with the source distribution column with the source data of the source distribution column.

[0019] In one embodiment, when the data merging rule is data insertion, the data merging rule is used to instruct the target data node to insert the source data of the source distribution column into the target distribution column associated with the source distribution column.

[0020] In one embodiment, performing a left join on the source table and the target table to obtain the left join result includes:

[0021] The merge instruction is subjected to lexical and syntactic analysis to generate an abstract syntax tree; the association rules between the source table and the target table are extracted from the abstract syntax tree; based on the association rules, a left join is performed on the source table and the target table to obtain the left join result.

[0022] Secondly, this application also provides a data processing apparatus for a distributed database, comprising:

[0023] The instruction response module is used to respond to a merge instruction for a distributed database and determine the source table and target table corresponding to the merge instruction.

[0024] The result determination module is used to perform a left join on the source table and the target table to obtain a left join result; wherein, the left join result includes the source distribution column and the corresponding source data of the source table, the target distribution column associated with the source distribution column in the target table, and the matching identifier between the source distribution column and the target distribution column;

[0025] The data merging module is used to redistribute the left join results based on a null value replacement function and the original distribution strategy, so that the left join results are routed to the corresponding target data nodes for merging operations. The null value replacement function includes a first parameter and a second parameter. When the first parameter is empty, the return value of the null value replacement function is the second parameter; when the first parameter is not empty, the return value of the null value replacement function is the first parameter. The first parameter is the matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source distribution column and the target distribution column do not match.

[0026] Thirdly, this application also provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to perform the following steps:

[0027] In response to a merge command for a distributed database, determine the source table and target table corresponding to the merge command;

[0028] A left join is performed on the source table and the target table to obtain a left join result; wherein, the left join result includes the source distribution column and the corresponding source data of the source table, the target distribution column associated with the source distribution column in the target table, and the matching identifier between the source distribution column and the target distribution column;

[0029] Based on the null value replacement function and the original distribution strategy, the left join result is redistributed so that the left join result is routed to the corresponding target data node for merging operation; wherein, the null value replacement function includes a first parameter and a second parameter; when the first parameter is empty, the return value of the null value replacement function is the second parameter, and when the first parameter is not empty, the return value of the null value replacement function is the first parameter; the first parameter is the matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source distribution column and the target distribution column do not match.

[0030] Fourthly, this application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, performs the following steps:

[0031] In response to a merge command for a distributed database, determine the source table and target table corresponding to the merge command;

[0032] A left join is performed on the source table and the target table to obtain a left join result; wherein, the left join result includes the source distribution column and the corresponding source data of the source table, the target distribution column associated with the source distribution column in the target table, and the matching identifier between the source distribution column and the target distribution column;

[0033] Based on the null value replacement function and the original distribution strategy, the left join result is redistributed so that the left join result is routed to the corresponding target data node for merging operation; wherein, the null value replacement function includes a first parameter and a second parameter; when the first parameter is empty, the return value of the null value replacement function is the second parameter, and when the first parameter is not empty, the return value of the null value replacement function is the first parameter; the first parameter is the matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source distribution column and the target distribution column do not match.

[0034] Fifthly, this application also provides a computer program product, including a computer program that, when executed by a processor, performs the following steps:

[0035] In response to a merge command for a distributed database, determine the source table and target table corresponding to the merge command;

[0036] A left join is performed on the source table and the target table to obtain a left join result; wherein, the left join result includes the source distribution column and the corresponding source data of the source table, the target distribution column associated with the source distribution column in the target table, and the matching identifier between the source distribution column and the target distribution column;

[0037] Based on the null value replacement function and the original distribution strategy, the left join result is redistributed so that the left join result is routed to the corresponding target data node for merging operation; wherein, the null value replacement function includes a first parameter and a second parameter; when the first parameter is empty, the return value of the null value replacement function is the second parameter, and when the first parameter is not empty, the return value of the null value replacement function is the first parameter; the first parameter is the matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source distribution column and the target distribution column do not match.

[0038] The aforementioned data processing method, apparatus, device, medium, and product for distributed databases, in response to a merge instruction for the distributed database, determines the source table and target table corresponding to the merge instruction; performs a left join on the source and target tables to obtain a left join result; wherein the left join result includes the source distribution columns and corresponding source data of the source table, the target distribution columns associated with the source distribution columns in the target table, and the matching identifier between the source and target distribution columns; based on a null value replacement function and the original distribution strategy, the left join result is redistributed so that the left join result is routed to the corresponding target data node for the merge operation; wherein the null value replacement function includes a first parameter and a second parameter; if the first parameter is null, the return value of the null value replacement function is the second parameter; if the first parameter is not null, the return value of the null value replacement function is the first parameter; the first parameter is the matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source and target distribution columns do not match. This scheme, by introducing a null value replacement function, can directly redistribute the left join result to the correct data node without sending data to the coordinator node, thus improving the performance of data merging to a certain extent. Attached Figure Description

[0039] To more clearly illustrate the technical solutions in the embodiments or related technologies of this application, the accompanying drawings used in the description of the embodiments or related technologies will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0040] Figure 1 This is a flowchart illustrating a data processing method for a distributed database in one embodiment.

[0041] Figure 2 This is a flowchart illustrating the redistribution of left join results in one embodiment;

[0042] Figure 3 This is a flowchart illustrating the process of determining the left join result in one embodiment;

[0043] Figure 4 This is a flowchart illustrating a data processing method for a distributed database in another embodiment;

[0044] Figure 5 This is a structural block diagram of a data processing device for a distributed database in one embodiment;

[0045] Figure 6 This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation

[0046] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.

[0047] In databases, `MERGE INTO` is used to merge data from a source table into a target table. Generally, it works based on join conditions: if a row in the target table can be joined with a row in the source table, then that row from the source table is used to update the target table; otherwise, if a row in the target table cannot be joined with any row in the source table, then the row from the source table is inserted into the target table. The current internal implementation of the database is as follows: since each row in the source table is either used to update the target table or inserted into it, the execution plan performs a left join between the underlying source and target tables. The upper-level DML operator judges based on the structure of the left join: if the right side of the left join result is not NULL, it means the target table row on the right satisfies the join condition, and an update operation is performed based on the data in the source table; if the right side of the left join result is NULL, it means that this data in the source table cannot be joined with the target table, and this data in the source table is inserted. In distributed databases, because data is distributed across different data nodes, the data in the source and target tables needs to be redistributed according to the join conditions before a left join is performed. After the left join, the source table's data is fully output in the result, while the target table on the right, which cannot be joined with the source table, is padded with NULL values. Therefore, the distribution column of the target table cannot be used as the distribution key for data redistribution (the target table contains empty rows in the left join result). Consequently, data cannot be redistributed to the corresponding data nodes based on the target table, and therefore, merge into operations cannot be performed on the data nodes. Currently, a common technique for distributed databases is to send the joined data to the coordinator node, which then performs the merge into operation on the corresponding data nodes. However, this execution plan is extremely inefficient, significantly reducing the performance of merge into operations.

[0048] Based on the above problems, the data processing method for distributed databases provided in this application embodiment can be applied to application environments that merge data from distributed databases. The data processing method for distributed databases provided in this application embodiment can be executed by the kernel of the distributed database.

[0049] In one exemplary embodiment, such as Figure 1 As shown, a data processing method for a distributed database is provided. Taking the application of this method to the kernel of a distributed database as an example, the method includes the following steps:

[0050] S101, in response to a merge command for a distributed database, determines the source table and target table corresponding to the merge command.

[0051] Among them, merging commands such as MERGE INTO are operation commands that trigger the merging of data between the source table and the target table.

[0052] Optionally, the syntax structure of the merge command is parsed to extract the source table name and the target table name; furthermore, the source distribution column of the source table, the target distribution column of the target table, and the original distribution strategies of the two tables are queried from the metadata management node.

[0053] S102, perform a left join on the source table and the target table to obtain the left join result.

[0054] The left join result includes the source distribution column and corresponding source data of the source table, the target distribution column associated with the source distribution column in the target table, and a matching identifier between the source and target distribution columns. The distribution column is a field used for data sharding routing. In this embodiment, the source distribution column is the sharding key of the source table, and the target distribution column is the sharding key of the target table. The matching identifier does not indicate whether the source and target distribution columns match after the left join. If the source and target distribution columns match, the corresponding value in the matching identifier is the target distribution column; if the source and target tables do not match, the corresponding value is empty.

[0055] Optionally, the logic of a left join is to use the source table as a reference, retain all data in the source table, and join the records in the target table that match the distribution columns of the source table to obtain the left join result.

[0056] S103, based on the null value replacement function and the original distribution strategy, redistributes the left join results so that the left join results are routed to the corresponding target data nodes for merging operations.

[0057] The null value replacement function includes a first parameter and a second parameter. If the first parameter is empty, the return value of the null value replacement function is the second parameter; if the first parameter is not empty, the return value of the null value replacement function is the first parameter. The first parameter is a matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source distribution column and the target distribution column do not match. In this embodiment, the original distribution strategy is the sharding distribution rule preset in the distributed database for the target table. The original distribution strategy can be a hash distribution rule, a range distribution rule, a list distribution rule, or a composite distribution rule.

[0058] Optionally, a null value replacement function COALESCE(expr1, expr2) is constructed, where expr1 is the first parameter and expr2 is the second parameter. In this embodiment, a new distribution column can be constructed based on COALESCE(expr1, expr2) and added to the target distribution column. For the join operator under merge into, the COALESCE(expr1, expr2) column is added to the target distribution column of the join operator. A data exchange operator remotesubplan is added to the join operator to redistribute the data, where the distribution key is COALESCE(expr1, expr2). A modified operator is directly added to the remotesubplan operator to perform the merge into operation on the data.

[0059] In the aforementioned distributed database data processing method, in response to a merge command for the distributed database, the source table and target table corresponding to the merge command are determined; a left join is performed on the source table and the target table to obtain the left join result; wherein, the left join result includes the source distribution column and corresponding source data in the source table, the target distribution column associated with the source distribution column in the target table, and the matching identifier between the source distribution column and the target distribution column; based on the null value replacement function and the original distribution strategy, the left join result is redistributed so that the left join result is routed to the corresponding target data node for the merge operation; wherein, the null value replacement function includes a first parameter and a second parameter; if the first parameter is null, the return value of the null value replacement function is the second parameter; if the first parameter is not null, the return value of the null value replacement function is the first parameter; the first parameter is the matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source distribution column and the target distribution column do not match. This scheme, by introducing a null value replacement function, can directly redistribute the left join result to the correct data node without sending data to the coordinator node, thus improving the performance of data merging to a certain extent.

[0060] Optionally, in one embodiment, such as Figure 2 As shown, a method for redistributing the results of left joins is provided, which specifically includes the following steps:

[0061] S201, based on the null replacement function, uses the return value of each value in the matching identifier as the distribution key.

[0062] Optionally, iterate through the matching identifiers of all records in the left join result and extract all possible values. For each value, execute the null replacement function to generate the corresponding distribution key. For example, if the matching identifier is not null, the function returns the first parameter; if the matching identifier is null, the function returns the second parameter, which is the source data corresponding to the source distribution column. Finally, bind each left join result record to its corresponding distribution key.

[0063] S202, based on the original distribution strategy, perform routing calculations on each distribution key to obtain the target data node corresponding to each value.

[0064] Optionally, the core parameters of the original distribution strategy can be obtained from the metadata management node, and routing calculations can be performed for each distribution key. Finally, a distribution key-target node mapping table can be generated based on the calculation results to guide subsequent data transmission.

[0065] In this embodiment of the application, if the distribution key is a first parameter, the data node corresponding to the first parameter is determined as the target data node according to the original distribution strategy; if the distribution key is a second parameter, the data node that conforms to the distribution rules of the target table is determined as the target data node according to the original distribution strategy.

[0066] S203 uses a data exchange operator to transmit the source data of the source distribution column corresponding to the value to the corresponding target data node, so that the corresponding target data node can perform a merging operation.

[0067] Among them, the data exchange operator is the core component responsible for data transmission between nodes in the distributed database. It supports sending specified data to the target node according to routing rules and has the ability to control traffic and resume interrupted transmission. In this embodiment of the application, the data exchange operator is the remotesubplan operator.

[0068] Optionally, the data exchange operator is first initialized, the distribution key-target node mapping table is loaded, the network connection between the source node and the target node is established, and the transmission parameters are configured. Further, the source data of the source distribution column corresponding to the value is transmitted to the corresponding target data node through the data exchange operator so that the corresponding target data node can perform a merging operation.

[0069] In this embodiment, the left join result also includes a data merging rule for each row in the matching identifier; for rows with non-empty values, the data merging rule is data update; for rows with empty values, the data merging rule is data insertion. In this case, the data merging rule corresponding to each row value and the source data of the source distribution column are transmitted to the corresponding target data node through the data exchange operator, so that the corresponding target data node can merge the source data and the target data in the target table according to the data merging rule.

[0070] In other words, when the data merge rule is for data update, the data merge rule instructs the target data node to replace the target data of the target distribution column associated with the source distribution column with the source data of the source distribution column. When the data merge rule is for data insertion, the data merge rule instructs the target data node to insert the source data of the source distribution column into the target distribution column associated with the source distribution column.

[0071] In this embodiment, a unified distribution key is generated by a null value replacement function to ensure that the associated and unassociated data between the source and target tables are routed to the corresponding data node in the target table according to the original distribution strategy, thereby achieving local merging. The unified distribution key is routed based on the original distribution strategy to ensure that the distribution key is strongly bound to the storage node of the target table data, thus guaranteeing the accuracy and efficiency of redistribution.

[0072] Optionally, in an exemplary embodiment, such as Figure 3 As shown, a method for determining the result of a left join is provided, which specifically includes the following steps:

[0073] S301 performs lexical and syntactic analysis on the merge instruction to generate an abstract syntax tree.

[0074] Optionally, load the merge instruction lexical rules supported by the distributed database, scan the merge instruction string in sequence, split and mark the type of each lexical unit; filter invalid characters, verify the legality of lexical units, and return a lexical error message if invalid.

[0075] Furthermore, the syntax rules for merging instructions (such as the MERGE INTO instruction) are loaded. Based on these rules, lexical units are combined hierarchically to construct an abstract syntax tree. The root node of the abstract syntax tree is the merging instruction, representing the overall operation type. First-level child nodes include the target table, source table, join conditions, and MERGE_ACTION (update / insert action), etc.; second-level child nodes include operators, left operands, right operands, etc. Finally, the syntax logic is validated. If it does not conform, a syntax error warning is returned; if it does conform, the abstract syntax tree is output.

[0076] For example, based on the merge instruction:

[0077] MERGE INTO T USING S ON S.id = T.sid

[0078] WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT;

[0079] The generated abstract syntax tree is:

[0080] MERGE (root node)

[0081] ├─ TARGET_TABLE:T

[0082] ├─ SOURCE_TABLE:S

[0083] ├─ JOIN_CONDITION (Association Condition Node)

[0084] │ ├─ Left operand: S.id (source distribution column)

[0085] │ ├─ Operator: =

[0086] │ └─ Right operand: T.sid (target distribution)

[0087] └─ MERGE_ACTION

[0088] ├─ MATCHED_ACTION:UPDATE

[0089] └─ UNMATCHED_ACTION:INSERT

[0090] S302, extract the association rules between the source table and the target table from the abstract syntax tree.

[0091] Optionally, the hierarchical structure of the abstract syntax tree is traversed to locate the JOIN_CONDITION node (association condition node), which is the core for extracting association rules. Further, the child nodes of the JOIN_CONDITION node are parsed to extract the core elements of the association rules, including the association columns (left operand (column of the source table, i.e., the source distribution column, such as S.id), right operand (column of the target table, i.e., the target distribution column, such as T.sid)), the association operator, and the association logic. Based on the extracted content, the association rules between the source table and the target table are constructed.

[0092] S303, based on the association rule, performs a left join on the source table and the target table to obtain the left join result.

[0093] Among them, the association rules are the basis for the association between the source table and the target table.

[0094] Optionally, the join rule can be used as the execution criterion to ensure that the left join only joins data that meets the conditions, avoiding redundancy caused by invalid joins. Specifically, based on the join rule, the execution method of the left join is determined. The sharding distribution information of the source table and the target table (such as the sharding nodes of the source table S and the target table T) is obtained from the metadata management node. The join order is optimized. According to the join rule, a local left join is performed on the corresponding sharding nodes of the source table and the target table. Taking the source table as the reference, all records of the source table are retained, and only records in the target table that meet the join rule are joined. Finally, the left join result is generated, which includes all data of the source table, the joined data of the target table, and the matching identifier.

[0095] In this embodiment, a three-layer verification process involving lexical analysis, syntax analysis, and abstract syntax tree traversal ensures that the association rules fully match the true intent of the merge instruction, eliminating invalid association conditions. Furthermore, based on precise association rules, local left joins are performed only on shard nodes that meet the conditions, avoiding full shard traversal associations.

[0096] Figure 4 This is a flowchart illustrating a data processing method for a distributed database in another embodiment. Based on the above embodiments, this embodiment provides an optional example of a data processing method for a distributed database. (Combined with...) Figure 4 The specific implementation process is as follows:

[0097] S401, in response to a merge instruction for a distributed database, performs lexical and syntactic analysis on the merge instruction to generate an abstract syntax tree.

[0098] S402, extract the source table, target table, and association rules between the source table and target table corresponding to the merge instruction from the abstract syntax tree.

[0099] S403, based on the association rule, performs a left join on the source table and the target table to obtain the left join result.

[0100] The left join result includes the source distribution column and corresponding source data of the source table, the target distribution column in the target table associated with the source distribution column, and the matching identifier between the source distribution column and the target distribution column.

[0101] S404, based on the null replacement function, uses the return value of each value in the matching identifier as the distribution key.

[0102] The null value replacement function includes a first parameter and a second parameter. If the first parameter is empty, the return value of the null value replacement function is the second parameter; if the first parameter is not empty, the return value of the null value replacement function is the first parameter. The first parameter is a matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source distribution column and the target distribution column do not match.

[0103] S405, based on the original distribution strategy, perform routing calculations on each distribution key to obtain the target data node corresponding to each value.

[0104] Optionally, if the distribution key is the first parameter, then the data node corresponding to the first parameter is determined as the target data node according to the original distribution strategy; if the distribution key is the second parameter, then the data node that conforms to the distribution rules of the target table is determined as the target data node according to the original distribution strategy.

[0105] S406 uses a data exchange operator to transmit the source data of the source distribution column corresponding to the value to the corresponding target data node, so that the corresponding target data node can perform a merging operation.

[0106] The data exchange operator is the remotesubplan operator.

[0107] The specific processes of S401-S406 described above can be found in the description of the above method embodiments. Their implementation principles and technical effects are similar, and will not be repeated here.

[0108] It should be understood that although the steps in the flowcharts of the embodiments described above are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the embodiments described above may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages of other steps.

[0109] Based on the same inventive concept, this application also provides a distributed database data processing apparatus for implementing the data processing method of the distributed database described above. The solution provided by this apparatus is similar to the implementation scheme described in the above method; therefore, the specific limitations in one or more distributed database data processing apparatus embodiments provided below can be found in the limitations of the distributed database data processing method described above, and will not be repeated here.

[0110] In one exemplary embodiment, such as Figure 5 As shown, a data processing device 500 for a distributed database is provided, comprising: an instruction response module 510, a result determination module 520, and a data merging module 530, wherein:

[0111] The instruction response module 510 is used to respond to a merge instruction for a distributed database and determine the source table and target table corresponding to the merge instruction.

[0112] The result determination module 520 is used to perform a left join on the source table and the target table to obtain the left join result. The left join result includes the source distribution column and the corresponding source data in the source table, the target distribution column in the target table that is associated with the source distribution column, and the matching identifier between the source distribution column and the target distribution column.

[0113] The data merging module 530 is used to redistribute the left join results based on the null value replacement function and the original distribution strategy, so that the left join results are routed to the corresponding target data nodes for merging operations. The null value replacement function includes a first parameter and a second parameter. When the first parameter is empty, the return value of the null value replacement function is the second parameter. When the first parameter is not empty, the return value of the null value replacement function is the first parameter. The first parameter is a matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source distribution column and the target distribution column do not match.

[0114] The data processing device for the aforementioned distributed database, in response to a merge command for the distributed database, determines the source table and target table corresponding to the merge command; performs a left join on the source and target tables to obtain the left join result; wherein the left join result includes the source distribution column and corresponding source data in the source table, the target distribution column associated with the source distribution column in the target table, and the matching identifier between the source and target distribution columns; based on a null value replacement function and the original distribution strategy, the left join result is redistributed so that the left join result is routed to the corresponding target data node for the merge operation; wherein the null value replacement function includes a first parameter and a second parameter; if the first parameter is null, the return value of the null value replacement function is the second parameter; if the first parameter is not null, the return value of the null value replacement function is the first parameter; the first parameter is the matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source and target distribution columns do not match. This scheme, by introducing a null value replacement function, can directly redistribute the left join result to the correct data node without sending data to the coordinator node, thus improving the performance of data merging to a certain extent.

[0115] In one embodiment, the data merging module 530 includes:

[0116] The distribution key determination unit is used to use the return value of each value in the matching identifier as the distribution key based on the null value replacement function.

[0117] The node determination unit is used to perform routing calculations on each distribution key according to the original distribution strategy to obtain the target data node corresponding to each value.

[0118] The data merging unit is used to transmit the source data of the source distribution column corresponding to the value to the corresponding target data node through the data exchange operator, so that the target data node can perform the merging operation.

[0119] In one embodiment, if the source distribution column matches the target distribution column, the corresponding value in the matching identifier is the target distribution column; if the source table and the target table do not match, the corresponding value is empty.

[0120] In one embodiment, the node determination unit is specifically used for:

[0121] If the distribution key is the first parameter, then the data node corresponding to the first parameter is determined as the target data node according to the original distribution strategy; if the distribution key is the second parameter, then the data node that conforms to the distribution rules of the target table is determined as the target data node according to the original distribution strategy.

[0122] In one embodiment, the original distribution strategy is the sharding distribution rule preset in the distributed database for the target table. The original distribution strategy can be a hash distribution rule, a range distribution rule, a list distribution rule, or a composite distribution rule.

[0123] In one embodiment, the left join result also includes a data merging rule for each row in the matching identifier; for rows with non-empty values, the data merging rule is data update; for rows with empty values, the data merging rule is data insertion.

[0124] In one embodiment, the data merging module 530 is further configured to:

[0125] The data exchange operator transmits the data merging rules corresponding to each row value and the source data of the source distribution column to the corresponding target data node, so that the corresponding target data node can merge the source data and the target data in the target table according to the data merging rules.

[0126] In one embodiment, when the data merging rule is a data update, the data merging rule is used to instruct the target data node to replace the target data of the target distribution column associated with the source distribution column with the source data of the source distribution column.

[0127] In one embodiment, when the data merging rule is data insertion, the data merging rule is used to instruct the target data node to insert the source data of the source distribution column into the target distribution column associated with the source distribution column.

[0128] In one embodiment, the result determination module 520 is further configured to:

[0129] The merge instruction is lexical and syntactic parsed to generate an abstract syntax tree; the association rules between the source table and the target table are extracted from the abstract syntax tree; based on the association rules, a left join is performed on the source table and the target table to obtain the left join result.

[0130] Each module in the aforementioned distributed database data processing device can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in a computer device, or stored in the memory of a computer device as software, so that the processor can call and execute the operations corresponding to each module.

[0131] In one exemplary embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as follows: Figure 6 As shown, this computer device includes a processor, memory, input / output (I / O) interfaces, and a communication interface. The processor, memory, and I / O interfaces are connected via a system bus, and the communication interface is also connected to the system bus via the I / O interfaces. The processor provides computational and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and a database. The internal memory provides the environment for the operating system and computer programs stored in the non-volatile storage media to run. The I / O interfaces are used for exchanging information between the processor and external devices. The communication interface is used for communicating with external terminals via a network connection. When the computer program is executed by the processor, it implements a distributed database data processing method.

[0132] Those skilled in the art will understand that Figure 6 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0133] In one exemplary embodiment, a computer device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to perform the following steps:

[0134] In response to a merge command for a distributed database, determine the source and target tables corresponding to the merge command;

[0135] Perform a left join on the source table and the target table to obtain the left join result. The left join result includes the source distribution column and the corresponding source data in the source table, the target distribution column in the target table that is associated with the source distribution column, and the matching identifier between the source distribution column and the target distribution column.

[0136] Based on the null value replacement function and the original distribution strategy, the left join results are redistributed so that the left join results are routed to the corresponding target data nodes for merging operations. The null value replacement function includes a first parameter and a second parameter. If the first parameter is empty, the return value of the null value replacement function is the second parameter; if the first parameter is not empty, the return value of the null value replacement function is the first parameter. The first parameter is a matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source distribution column and the target distribution column do not match.

[0137] In one embodiment, when the processor executes a computer program to redistribute the left join results based on a null value replacement function and the original distribution strategy so that the left join results are routed to the corresponding target data nodes for merging operations, it also performs the following steps:

[0138] Based on the null value replacement function, the return value of each value in the matching identifier is used as the distribution key; according to the original distribution strategy, the routing calculation is performed on each distribution key to obtain the target data node corresponding to each value; through the data exchange operator, the source data of the source distribution column corresponding to the value is transmitted to the corresponding target data node so that the corresponding target data node can perform the merging operation.

[0139] In one embodiment, if the source distribution column matches the target distribution column, the corresponding value in the matching identifier is the target distribution column; if the source table and the target table do not match, the corresponding value is empty.

[0140] In one embodiment, when the processor executes a computer program to perform routing calculations on each distribution key according to the original distribution strategy to obtain the target data node corresponding to each row value, it also performs the following steps:

[0141] If the distribution key is the first parameter, then the data node corresponding to the first parameter is determined as the target data node according to the original distribution strategy; if the distribution key is the second parameter, then the data node that conforms to the distribution rules of the target table is determined as the target data node according to the original distribution strategy.

[0142] In one embodiment, the original distribution strategy is the sharding distribution rule preset in the distributed database for the target table. The original distribution strategy can be a hash distribution rule, a range distribution rule, a list distribution rule, or a composite distribution rule.

[0143] In one embodiment, the left join result also includes a data merging rule for each row in the matching identifier; for rows with non-empty values, the data merging rule is data update; for rows with empty values, the data merging rule is data insertion.

[0144] In one embodiment, the processor executes a computer program to transmit the source data of the source distribution column corresponding to each row value to the corresponding target data node through a data exchange operator, so that when the corresponding target data node performs a merging operation, the following steps are also implemented:

[0145] The data exchange operator transmits the data merging rules corresponding to each row value and the source data of the source distribution column to the corresponding target data node, so that the corresponding target data node can merge the source data and the target data in the target table according to the data merging rules.

[0146] In one embodiment, when the data merging rule is a data update, the data merging rule is used to instruct the target data node to replace the target data of the target distribution column associated with the source distribution column with the source data of the source distribution column.

[0147] In one embodiment, when the data merging rule is data insertion, the data merging rule is used to instruct the target data node to insert the source data of the source distribution column into the target distribution column associated with the source distribution column.

[0148] In one embodiment, when the processor executes a computer program to perform a left join on the source table and the target table and obtains the left join result, it also performs the following steps:

[0149] The merge instruction is lexical and syntactic parsed to generate an abstract syntax tree; the association rules between the source table and the target table are extracted from the abstract syntax tree; based on the association rules, a left join is performed on the source table and the target table to obtain the left join result.

[0150] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon, the computer program performing the following steps when executed by a processor:

[0151] In response to a merge command for a distributed database, determine the source and target tables corresponding to the merge command;

[0152] Perform a left join on the source table and the target table to obtain the left join result. The left join result includes the source distribution column and the corresponding source data in the source table, the target distribution column in the target table that is associated with the source distribution column, and the matching identifier between the source distribution column and the target distribution column.

[0153] Based on the null value replacement function and the original distribution strategy, the left join results are redistributed so that the left join results are routed to the corresponding target data nodes for merging operations. The null value replacement function includes a first parameter and a second parameter. If the first parameter is empty, the return value of the null value replacement function is the second parameter; if the first parameter is not empty, the return value of the null value replacement function is the first parameter. The first parameter is a matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source distribution column and the target distribution column do not match.

[0154] In one embodiment, when the processor executes a computer program to redistribute the left join results based on a null value replacement function and the original distribution strategy so that the left join results are routed to the corresponding target data nodes for merging operations, it also performs the following steps:

[0155] Based on the null value replacement function, the return value of each value in the matching identifier is used as the distribution key; according to the original distribution strategy, the routing calculation is performed on each distribution key to obtain the target data node corresponding to each value; through the data exchange operator, the source data of the source distribution column corresponding to the value is transmitted to the corresponding target data node so that the corresponding target data node can perform the merging operation.

[0156] In one embodiment, if the source distribution column matches the target distribution column, the corresponding value in the matching identifier is the target distribution column; if the source table and the target table do not match, the corresponding value is empty.

[0157] In one embodiment, when the processor executes a computer program to perform routing calculations on each distribution key according to the original distribution strategy to obtain the target data node corresponding to each row value, it also performs the following steps:

[0158] If the distribution key is the first parameter, then the data node corresponding to the first parameter is determined as the target data node according to the original distribution strategy; if the distribution key is the second parameter, then the data node that conforms to the distribution rules of the target table is determined as the target data node according to the original distribution strategy.

[0159] In one embodiment, the original distribution strategy is the sharding distribution rule preset in the distributed database for the target table. The original distribution strategy can be a hash distribution rule, a range distribution rule, a list distribution rule, or a composite distribution rule.

[0160] In one embodiment, the left join result also includes a data merging rule for each row in the matching identifier; for rows with non-empty values, the data merging rule is data update; for rows with empty values, the data merging rule is data insertion.

[0161] In one embodiment, the processor executes a computer program to transmit the source data of the source distribution column corresponding to each row value to the corresponding target data node through a data exchange operator, so that when the corresponding target data node performs a merging operation, the following steps are also implemented:

[0162] The data exchange operator transmits the data merging rules corresponding to each row value and the source data of the source distribution column to the corresponding target data node, so that the corresponding target data node can merge the source data and the target data in the target table according to the data merging rules.

[0163] In one embodiment, when the data merging rule is a data update, the data merging rule is used to instruct the target data node to replace the target data of the target distribution column associated with the source distribution column with the source data of the source distribution column.

[0164] In one embodiment, when the data merging rule is data insertion, the data merging rule is used to instruct the target data node to insert the source data of the source distribution column into the target distribution column associated with the source distribution column.

[0165] In one embodiment, when the processor executes a computer program to perform a left join on the source table and the target table and obtains the left join result, it also performs the following steps:

[0166] The merge instruction is lexical and syntactic parsed to generate an abstract syntax tree; the association rules between the source table and the target table are extracted from the abstract syntax tree; based on the association rules, a left join is performed on the source table and the target table to obtain the left join result.

[0167] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, performs the following steps:

[0168] In response to a merge command for a distributed database, determine the source and target tables corresponding to the merge command;

[0169] Perform a left join on the source table and the target table to obtain the left join result. The left join result includes the source distribution column and the corresponding source data in the source table, the target distribution column in the target table that is associated with the source distribution column, and the matching identifier between the source distribution column and the target distribution column.

[0170] Based on the null value replacement function and the original distribution strategy, the left join results are redistributed so that the left join results are routed to the corresponding target data nodes for merging operations. The null value replacement function includes a first parameter and a second parameter. If the first parameter is empty, the return value of the null value replacement function is the second parameter; if the first parameter is not empty, the return value of the null value replacement function is the first parameter. The first parameter is a matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source distribution column and the target distribution column do not match.

[0171] In one embodiment, when the processor executes a computer program to redistribute the left join results based on a null value replacement function and the original distribution strategy so that the left join results are routed to the corresponding target data nodes for merging operations, it also performs the following steps:

[0172] Based on the null value replacement function, the return value of each value in the matching identifier is used as the distribution key; according to the original distribution strategy, the routing calculation is performed on each distribution key to obtain the target data node corresponding to each value; through the data exchange operator, the source data of the source distribution column corresponding to the value is transmitted to the corresponding target data node so that the corresponding target data node can perform the merging operation.

[0173] In one embodiment, if the source distribution column matches the target distribution column, the corresponding value in the matching identifier is the target distribution column; if the source table and the target table do not match, the corresponding value is empty.

[0174] In one embodiment, when the processor executes a computer program to perform routing calculations on each distribution key according to the original distribution strategy to obtain the target data node corresponding to each row value, it also performs the following steps:

[0175] If the distribution key is the first parameter, then the data node corresponding to the first parameter is determined as the target data node according to the original distribution strategy; if the distribution key is the second parameter, then the data node that conforms to the distribution rules of the target table is determined as the target data node according to the original distribution strategy.

[0176] In one embodiment, the original distribution strategy is the sharding distribution rule preset in the distributed database for the target table. The original distribution strategy can be a hash distribution rule, a range distribution rule, a list distribution rule, or a composite distribution rule.

[0177] In one embodiment, the left join result also includes a data merging rule for each row in the matching identifier; for rows with non-empty values, the data merging rule is data update; for rows with empty values, the data merging rule is data insertion.

[0178] In one embodiment, the processor executes a computer program to transmit the source data of the source distribution column corresponding to each row value to the corresponding target data node through a data exchange operator, so that when the corresponding target data node performs a merging operation, the following steps are also implemented:

[0179] The data exchange operator transmits the data merging rules corresponding to each row value and the source data of the source distribution column to the corresponding target data node, so that the corresponding target data node can merge the source data and the target data in the target table according to the data merging rules.

[0180] In one embodiment, when the data merging rule is a data update, the data merging rule is used to instruct the target data node to replace the target data of the target distribution column associated with the source distribution column with the source data of the source distribution column.

[0181] In one embodiment, when the data merging rule is data insertion, the data merging rule is used to instruct the target data node to insert the source data of the source distribution column into the target distribution column associated with the source distribution column.

[0182] In one embodiment, when the processor executes a computer program to perform a left join on the source table and the target table and obtains the left join result, it also performs the following steps:

[0183] The merge instruction is lexical and syntactic parsed to generate an abstract syntax tree; the association rules between the source table and the target table are extracted from the abstract syntax tree; based on the association rules, a left join is performed on the source table and the target table to obtain the left join result.

[0184] It should be noted that the data involved in this application (including but not limited to data used for analysis, data stored, data displayed, etc.) are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of the relevant data must comply with relevant regulations.

[0185] Those skilled in the art will understand that all or part of the processes in the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium. When executed, the computer program can include the processes of the embodiments described above. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, etc., and are not limited to these.

[0186] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0187] The embodiments described above are merely illustrative of several implementation methods of this application, and while the descriptions are specific and detailed, they should not be construed as limiting the scope of this patent application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.

Claims

1. A data processing method for a distributed database, characterized in that, The method includes: In response to a merge command for a distributed database, determine the source table and target table corresponding to the merge command; A left join is performed on the source table and the target table to obtain a left join result; wherein, the left join result includes the source distribution column and the corresponding source data of the source table, the target distribution column associated with the source distribution column in the target table, and the matching identifier between the source distribution column and the target distribution column; Based on the null value replacement function and the original distribution strategy, the left join result is redistributed so that the left join result is routed to the corresponding target data node for merging operation; wherein, the null value replacement function includes a first parameter and a second parameter; when the first parameter is empty, the return value of the null value replacement function is the second parameter, and when the first parameter is not empty, the return value of the null value replacement function is the first parameter; the first parameter is the matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source distribution column and the target distribution column do not match.

2. The method according to claim 1, characterized in that, The redistribution of the left join results based on the null value replacement function and the original distribution strategy, so that the left join results are routed to the corresponding target data nodes for merging operations, includes: Based on the null value replacement function, the return value of each value in the matching identifier is used as the distribution key; Based on the original distribution strategy, routing calculations are performed on each distribution key to obtain the target data node corresponding to each value; The source data of the source distribution column corresponding to the value is transmitted to the corresponding target data node through the data exchange operator, so that the target data node can perform the merging operation.

3. The method according to claim 2, characterized in that, When the source distribution column matches the target distribution column, the corresponding value in the matching identifier is the target distribution column; when the source table and the target table do not match, the corresponding value is empty.

4. The method according to claim 3, characterized in that, The step of performing routing calculations on each distribution key according to the original distribution strategy to obtain the target data node corresponding to each row value includes: If the distribution key is the first parameter, then according to the original distribution strategy, the data node corresponding to the first parameter is determined as the target data node; If the distribution key is the second parameter, then according to the original distribution strategy, the data node that conforms to the distribution rules of the target table is determined as the target data node.

5. The method according to any one of claims 1-4, characterized in that, The original distribution strategy is the sharding distribution rule preset for the target table in the distributed database. The original distribution strategy can be a hash distribution rule, a range distribution rule, a list distribution rule, or a composite distribution rule.

6. The method according to claim 2, characterized in that, The left join result also includes the data merging rules for each row in the matching identifier; For rows with non-empty values, the data merging rule is to update the data; For rows with empty values, the data merging rule is data insertion.

7. The method according to claim 6, characterized in that, The step of transmitting the source data of the source distribution column corresponding to each row value to the corresponding target data node through the data exchange operator, so that the corresponding target data node can perform a merging operation, includes: The data exchange operator transmits the data merging rules corresponding to each row value and the source data of the source distribution column to the corresponding target data node, so that the corresponding target data node can merge the source data and the target data in the target table according to the data merging rules.

8. The method according to claim 6, characterized in that, When the data merging rule is a data update, the data merging rule is used to instruct the target data node to replace the target data of the target distribution column associated with the source distribution column with the source data of the source distribution column.

9. The method according to claim 6, characterized in that, When the data merging rule is data insertion, the data merging rule is used to instruct the target data node to insert the source data of the source distribution column into the target distribution column associated with the source distribution column.

10. The method according to claim 1, characterized in that, The step of performing a left join on the source table and the target table to obtain the left join result includes: The merging instructions are subjected to lexical and syntactic analysis to generate an abstract syntax tree; Extract the association rules between the source table and the target table from the abstract syntax tree; Based on the association rules, a left join is performed on the source table and the target table to obtain the left join result.

11. A data processing device for a distributed database, characterized in that, The device includes: The instruction response module is used to respond to a merge instruction for a distributed database and determine the source table and target table corresponding to the merge instruction. The result determination module is used to perform a left join on the source table and the target table to obtain a left join result; wherein, the left join result includes the source distribution column and the corresponding source data of the source table, the target distribution column associated with the source distribution column in the target table, and the matching identifier between the source distribution column and the target distribution column; The data merging module is used to redistribute the left join results based on a null value replacement function and the original distribution strategy, so that the left join results are routed to the corresponding target data nodes for merging operations. The null value replacement function includes a first parameter and a second parameter. When the first parameter is empty, the return value of the null value replacement function is the second parameter; when the first parameter is not empty, the return value of the null value replacement function is the first parameter. The first parameter is the matching identifier, and the second parameter is the source data corresponding to the source distribution column when the source distribution column and the target distribution column do not match.

12. The apparatus according to claim 11, characterized in that, The data merging module includes: The distribution key determination unit is used to take the return value of each value in the matching identifier as the distribution key based on the null value replacement function; The node determination unit is used to perform routing calculations on each distribution key according to the original distribution strategy to obtain the target data node corresponding to each value. The data merging unit is used to transmit the source data of the source distribution column corresponding to the value to the corresponding target data node through the data exchange operator, so that the target data node can perform the merging operation.

13. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 11.

14. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 11.

15. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 11.

Images