Method and system for outer join of database tables
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.
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 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.
15. The storage medium of claim 14 , 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.
16. The storage medium of claim 14 , 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.
17. The storage medium of claim 14 , 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.
18. The storage medium of claim 14 , wherein the first table is also 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.
19. The storage medium of claim 14 , wherein the 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 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.