Method and system for outer join of database tables

Patent metadata

Field	Value
Publication number	US-10885035-B2
Application number	US-202016877394-A
Country	US
Kind code	B2
Filing date	May 18, 2020
Priority date	Apr 26, 2019
Publication date	Jan 5, 2021
Grant date	Jan 5, 2021

Abstract

The specification provides a system, method, and storage medium for executing an outer join command on a first table and a second table in a distributed database. The method may comprise: broadcasting the first table to a plurality of second nodes storing the second table; selecting one or more second nodes from the plurality of second nodes; obtaining, by each of the selected one or more second nodes, an outer join result after executing an outer join operation; obtaining, by each of the plurality of second nodes other than the selected one or more second nodes, an inner join result; removing a row with (1) a join key value and (2) a NULL value from the outer join result when the inner join result comprises another row with the join key; and returning, after the removal, a result to the outer join command by aggregating the outer join result and the inner join result.

First claim

The invention claimed is:

1. A computer-implemented method for executing an OUTER JOIN command on a first table and a second table in a distributed database, wherein the method comprises:
broadcasting the first table to a plurality of database nodes of a distributed database storing the second table, wherein the second table is a distributed database table, and each of the plurality of database nodes stores a portion of the second table;
dividing the plurality of database nodes storing the second table into a first subset and a second subset, wherein the first subset and the second subset each comprises less than all of the plurality of database nodes;
obtaining, by each database node of the first subset, an OUTER JOIN result by executing an OUTER JOIN operation based on the first table broadcasted to the each database node of the first subset and the portion of the second table stored in the each database node of the first subset;
obtaining, by each database node of the second subset, an INNER JOIN result by executing an INNER JOIN operation based on the first table broadcasted to the each database node of the second subset and the portion of the second table stored in the each database node of the second subset
identifying and removing a first row with a NULL value from the OUTER JOIN result when the INNER JOIN result comprises a second row sharing a same join key with the first row; and
returning a result to the OUTER JOIN command by aggregating the OUTER JOIN result from each database node of the first subset and the INNER JOIN result from each database node of the second subset.

2. The method according to claim 1 , wherein the identifying and removing a first row with a NULL value from the OUTER JOIN result when the INNER JOIN result comprises a second row sharing a same join key with the first row comprises:
performing a GROUP BY operation to the OUTER JOIN result and the INNER JOIN result based on one or more join keys of the OUTER JOIN command.

3. The method according to claim 1 , wherein the dividing the plurality of database nodes storing the second table into a first subset and a second subset comprises:
selecting one or more of the plurality of database nodes storing a-smallest portions of the second table as the first subset.

4. The method according to claim 1 , wherein the dividing the plurality of database nodes storing the second table into a first subset and a second subset comprises:
selecting one or more of the plurality of database nodes with a fastest connection as the first subset.

5. The method according to claim 1 , wherein the first table is a distributed database table stored in a plurality of second database nodes, and the broadcasting the first table to a plurality of database nodes storing the second table comprises:
broadcasting data from each database node of the plurality of second database nodes to each database node of the plurality of database nodes.

6. The method according to claim 1 , wherein the executing the OUTER JOIN operation based on the first table broadcasted to the each database node of the first subset and the portion of the second table stored in the each database node of the first subset comprises:
performing the OUTER JOIN operation by using the first table broadcasted to the each database node of the first subset as an outer table and the portion of the second table stored on the each database node of the first subset as an inner table.

7. The method according to claim 1 , wherein: the OUTER JOIN operation executed by each of database nodes of the first subset is based on the one or more join keys of the OUTER JOIN command; and
the INNER JOIN operation executed by each of database nodes of the second subset is based on the one or more join keys of the OUTER JOIN command.

8. A system for executing an outer join command on a first table and a second table in a distributed database, comprising a processor and a non-transitory computer-readable storage medium storing instructions executable by the processor to cause the system to perform operations comprising:
broadcasting the first table to a plurality of database nodes of a distributed database storing the second table, wherein the second table is a distributed database table, and each of the plurality of database nodes stores a portion of the second table;
dividing the plurality of database nodes storing the second table into a first subset and a second subset, wherein the first subset and the second subset each comprises less than all of the plurality of database nodes;
obtaining, by each database node of the first subset, an OUTER JOIN result by executing an OUTER JOIN operation based on the first table broadcasted to the each database node of the first subset and the portion of the second table stored in the each database node of the first subset;
obtaining, by each database node of the second subset, an INNER JOIN result by executing an INNER JOIN operation based on the first table broadcasted to the each database node of the second subset and the portion of the second table stored in the each database node of the second subset;
identifying and removing a first row with a NULL value from the OUTER JOIN result when the INNER JOIN result comprises a second row sharing a same join key with the first row; and
returning a result to the OUTER JOIN command by aggregating the OUTER JOIN result from each database node of the first subset and the INNER JOIN result from each database node of the second subset.

9. The system of claim 8 , wherein the identifying and removing a first row with a NULL value from the OUTER JOIN result when the INNER JOIN result comprises a second row sharing a same join key with the first row comprises:
performing a GROUP BY operation to the OUTER JOIN result and the INNER JOIN result based on one or more join keys of the OUTER JOIN command.

10. The system of claim 8 , wherein the dividing the plurality of database nodes storing the second table into a first subset and a second subset comprises:
selecting one or more of the plurality of database nodes storing a-smallest portions of the second table as the first subset.

11. The system of claim 8 , wherein the dividing the plurality of database nodes storing the second table into a first subset and a second subset comprises:
selecting one or more of the plurality of database nodes with a fastest connection as the first subset.

12. The system of claim 8 , wherein the first table is a distributed database table stored in a plurality of second database nodes, and the broadcasting the first table to a plurality of database nodes storing the second table comprises:
broadcasting data from each database node of the plurality of second database nodes to each database node of the plurality of database nodes.

13. The system of claim 8 , wherein the executing the OUTER JOIN operation based on the first table broadcasted to the each database node of the first subset and the portion of the second table stored in the each database node of the first subset comprises:
performing the OUTER JOIN operation by using the first table broadcasted to the each database node of the first subset as an outer table and the portion of the second table stored on the each database node of the first subset as an inner table.

14. A non-transitory computer-readable storage medium for executing an outer join command on a first table and a second table in a distributed database, configured with instructions executable by one or more processors to cause the one or more processors to perform operations comprising:
broadcasting the first table to a plurality of database nodes of a

Assignees

• Advanced New Technologies Co Ltd

Inventors

• Chen Mengmeng

Classifications

• G06F16/24556

  Aggregation; Duplicate elimination · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/24544 Primary

  Join order optimisation · CPC title

• G06F16/24537

  of operators · CPC title

• G06F16/2456

  Join operations · CPC title