EFFICIENT PROCESSING OF QUERIES IN FEDERATED DATABASE SYSTEMS

• US 20090234799A1
• Filed: 03/11/2008
• Published: 09/17/2009
• Est. Priority Date: 03/11/2008
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Pirahesh, Hamid, Simmen, David Everett, Betawadkar-Norwood, Anjali
• Granted Patent: US 8,538,985 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2471 Distributed queries

Abstract

Methods and apparatus, including computer program products, implementing and using techniques for processing a federated query in a federated database system. A federated query is received at a federated database server. A federated query execution plan is generated based on the received federated query. The federated query execution plan defines one or more source servers of the federated database and a unique subquery to be executed on each of the source servers. The subqueries are distributed to the source servers in accordance with the federated query execution plan. The respective subqueries are executed asynchronously at the source servers. The subquery results are passed to a first designated source server defined in the federated query execution plan. The subquery results are joined and aggregated at the first designated source server into a final query result. The final query result is returned to the federated database server.

Claims

1. A computer-implemented method for processing a federated query in a federated database system, the method comprising:

receiving a federated query at a federated database server;

generating a federated query execution plan, based on the received federated query, the federated query execution plan defining one or more source servers of the federated database and a unique subquery to be executed on each of the defined one or more source servers;

distributing the subqueries to the one or more source servers in accordance with the federated query execution plan;

executing the respective subqueries asynchronously at the one or more source servers;

passing the subquery results to a first designated source server defined in the federated query execution plan;

joining and aggregating the subquery results at the first designated source server into a final query result; and

returning the final query result to the federated database server.

2. The method of claim 1, wherein passing the results comprises:

connecting a source server to the first designated source server using a message queue; and

inserting the subquery results from the source server into the message queue.

3. The method of claim 2, wherein inserting the subquery results in the message queue includes inserting the subquery results into the message queue as rows are produced.

4. The method of claim 1, wherein joining and aggregating comprises:

receiving subquery results from a specified message queue; and

formatting the received subquery results into rows and columns of appropriate types to generate a final query result.

5. The method of claim 1, wherein a database table is physically distributed across the first designated source server and a second designated source server, further comprising:

passing, based on parameters specified in the federated query, a first subset of the subquery results to the first designated source server and a second subset of the subquery results to the second designated source server;

joining and aggregating the first subset of the subquery results at the first designated source server into a first final query result;

joining and aggregating the second subset of the subquery results at the second designated source server into a second final query result;

returning the first and second final query results to the federated database server; and

combining the first and second final query results at the federated database server.

6. The method of claim 5, wherein passing the first and second subsets of the query results comprises:

determining whether a subquery result is to be sent to the first designated source server or to the second designated source server;

connecting a source server to the first designated source server using a first message queue;

connecting a source server to the second designated source server using a second message queue;

inserting the subquery results from the source server into the first message queue in response to determining that the subquery results should be sent to the first designated source server; and

inserting the subquery results from the source server into the second message queue in response to determining that the subquery should be sent to the second designated source server.

7. The method of claim 6, wherein placing the subquery results in the first and second message queues, respectively, includes inserting the subquery results into either the first message queue or into the second message queue as rows are produced.

8. The method of claim 5, wherein joining and aggregating the first subset comprises:

receiving the first subset of subquery results from the first message queue; and

formatting the received first subset of subquery results into rows and columns of appropriate types to generate a first final query result.

9. The method of claim 5, wherein joining and aggregating the second subset comprises:

receiving the second subset of subquery results from the first message queue; and

formatting the received second subset of subquery results into rows and columns of appropriate types to generate a second final query result.

10. The method of claim 5, further comprising:

replicating the physically distributed database table onto one or more additional source servers in the federated database system; and

using the information about the replicas of the distributed database table in generating the federated query execution plan.

11. The method of claim 5, wherein generating a federated query execution plan includes:

optimizing the federated query based on metadata to generate an execution strategy involving sideways data movement.

12. The method of claim 11, wherein the metadata includes one or more of:

a server group representing a set of source servers over which partitions or replicas of the distributed database table reside, and a distribution function and a partitioning key indicating how rows of the distributed database table are distributed across the source servers identified by the server group.

13. A computer program product comprising a computer useable medium including a computer readable program, wherein the computer readable program when executed on a computer causes the computer to:

receive a federated query at a federated database server;

generate a federated query execution plan, based on the received federated query, the federated query execution plan defining one or more source servers of the federated database and a unique subquery to be executed on each of the defined one or more source servers;

distribute the subqueries to the one or more source servers in accordance with the federated query execution plan;

execute the respective subqueries asynchronously at the one or more source servers;

pass the subquery results to a first designated source server defined in the federated query execution plan;

join and aggregate the subquery results at the first designated source server into a final query result; and

return the final query result to the federated database server.

14. The computer program product of claim 13, wherein passing the results comprises:

connecting a source server to the first designated source server using a message queue; and

inserting the subquery results from the source server into the message queue.

15. The computer program product of claim 14, wherein inserting the subquery results in the message queue includes inserting the subquery results into the message queue as rows are produced.

16. The computer program product of claim 13, wherein joining and aggregating comprises:

receiving subquery results from a specified message queue; and

formatting the received subquery results into rows and columns of appropriate types to generate a final query result.

17. The computer program product of claim 13, wherein a database table is physically distributed across the first designated source server and a second designated source server, wherein the computer readable program when executed on a computer further causes the computer to:

pass, based on parameters specified in the federated query, a first subset of the subquery results to the first designated source server and a second subset of the subquery results to the second designated source server;

join and aggregate the first subset of the subquery results at the first designated source server into a first final query result;

join and aggregate the second subset of the subquery results at the second designated source server into a second final query result;

return the first and second final query results to the federated database server; and

combine the first and second final query results at the federated database server.

18. The computer program product of claim 17, wherein passing the first and second subsets of the query results comprises:

determining whether a subquery result is to be sent to the first designated source server or to the second designated source server;

connecting a source server to the first designated source server using a first message queue;

connecting a source server to the second designated source server using a second message queue;

inserting the subquery results from the source server into the first message queue in response to determining that the subquery results should be sent to the first designated source server; and

inserting the subquery results from the source server into the second message queue in response to determining that the subquery should be sent to the second designated source server.

19. The computer program product of claim 18, wherein placing the subquery results in the first and second message queues, respectively, includes inserting the subquery results into either the first message queue or into the second message queue as rows are produced.

20. The computer program product of claim 17, wherein joining and aggregating the first subset comprises:

receiving the first subset of subquery results from the first message queue; and

formatting the received first subset of subquery results into rows and columns of appropriate types to generate a first final query result.

21. The computer program product of claim 17, wherein joining and aggregating the second subset comprises:

receiving the second subset of subquery results from the first message queue; and

formatting the received second subset of subquery results into rows and columns of appropriate types to generate a second final query result.

22. The computer program product of claim 17, wherein the computer readable program when executed on a computer further causes the computer to:

replicate the physically distributed database table onto one or more additional source servers in the federated database system; and

use the information about the replicas of the distributed database table in generating the federated query execution plan.

23. The computer program product of claim 17, wherein generating a federated query execution plan includes:

optimizing the federated query based on metadata to generate an execution strategy involving sideways data movement.

24. The computer program product of claim 23, wherein the metadata includes one or more of:

a server group representing a set of source servers over which partitions or replicas of the distributed database table reside, and a distribution function and a partitioning key indicating how rows of the distributed database table are distributed across the source servers identified by the server group.

25. A federated database system for processing federated queries, comprising:

means for receiving a federated query at a federated database server;

means for generating a federated query execution plan, based on the received federated query, the federated query execution plan defining one or more source servers of the federated database and a unique subquery to be executed on each of the defined one or more source servers;

means for distributing the subqueries to the one or more source servers in accordance with the federated query execution plan;

means for executing the respective subqueries asynchronously at the one or more source servers;

means for passing the subquery results to a first designated source server defined in the federated query execution plan;

means for joining and aggregating the subquery results at the first designated source server into a final query result; and

means for returning the final query result to the federated database server.