Apparatus and Method for Operating a Distributed Database with Foreign Tables

• US 20140067792A1
• Filed: 08/30/2012
• Published: 03/06/2014
• Est. Priority Date: 08/30/2012
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Citus Data Bilgi Islemleri Ticaret A.S.
• Inventors: Erdogan, Ozgun Ali, Pathak, Sumedh Suhas, Moshayedi, Hadi
• Granted Patent: US 10,579,634 B2
• Time in Patent Office: Days
• US Class Current: 707/718
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: G06F 16/256 in federated or virtual databases
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
• CPC Class Codes: G06F 16/278 Data partitioning, e.g. horizontal or vertical partitioning

Abstract

A system includes a coordinator node and worker nodes in communication with the coordinator node. Each worker node stores data blocks. Each data block has data in a semi-structured format and each data block has an associated foreign table declaration specifying conversion of the data in the semi-structured format into a tabular format interpretable by a query language. A query processor executed by the coordinator node produces a distributed query plan in response to a query language query. The distributed query plan includes sub-queries. The sub-queries are executed by selected worker nodes of the worker nodes. The selected worker nodes use foreign table declarations to convert data in semi-structured formats into tabular formats of a distributed database to provide tabular data in response to the query language query.

Claims

1. A system, comprising:

a coordinator node; and

a plurality of worker nodes in communication with the coordinator node, wherein each worker node stores data blocks, wherein each data block has data in a semi-structured format and each data block has an associated foreign table declaration specifying conversion of the data in the semi-structured format into a tabular format interpretable by a query language;

a query processor executed by the coordinator node to produce a distributed query plan in response to a query language query, wherein the distributed query plan includes sub-queries;

wherein the sub-queries are executed by selected worker nodes of the plurality of worker nodes; and

wherein the selected worker nodes use foreign table declarations to convert data in semi-structured formats into tabular formats of a distributed database to provide tabular data in response to the query language query.

2. The system of claim 1 wherein the coordinator node merges the tabular data from the selected worker nodes to provide a query result.

3. The system of claim 1 wherein each sub-query is associated with a related data block.

4. The system of claim 3 wherein the query processor directs each sub-query to a worker node that contains the related data block.

5. The system of claim 1 wherein the coordinator node maintains foreign table metadata, wherein the foreign table metadata characterizes the tabular format.

6. The system of claim 5 wherein the query processor accesses the foreign table metadata to develop the distributed query plan.

7. The system of claim 1 wherein the coordinator node maintains metadata on the data blocks, wherein the metadata includes the location of the data blocks across the plurality of worker nodes.

8. The system of claim 7 wherein the query processor accesses the metadata on the data blocks to develop the distributed query plan.

9. The system of claim 1 wherein the query processor evaluates alternate network input/output costs to develop the distributed query plan.

10. The system of claim 1 wherein the query processor evaluates statistics on the data blocks to develop the distributed query plan.

11. The system of claim 1 wherein the query processor issues commands to copy one or more data blocks between the plurality of worker nodes to execute the distributed query plan.

12. The system of claim 1 wherein the query processor repartitions the data in the data blocks to execute the distributed query plan.

13. The system of claim 1 wherein a data block is replicated across the plurality of worker nodes.

14. The system of claim 1 wherein a data block is stored as a text file.

15. The system of claim 1 wherein a data block is stored as a binary file.

16. The system of claim 1 wherein the selected worker nodes evaluate statistics on the data blocks to develop a sub-query plan.

17. The system of claim 1 wherein the selected worker nodes evaluate data block indexes to develop a sub-query plan.

18. The system of claim 1 wherein the query language query is a Structured Query Language (SQL) query.

19. The system of claim 1 wherein the query language sub-query is a Structured Query Language (SQL) query.

20. A computer implemented method, comprising:

storing data blocks on networked worker nodes, wherein the data blocks have data in semi-structured formats;

associating the data blocks with foreign table declarations specifying conversions of the data in the semi-structured formats into tabular formats interpretable by a query language;

producing a distributed query plan in response to a query language query, wherein the distributed query plan includes sub-queries;

directing the sub-queries to selected worker nodes of the networked worker nodes; and

executing the sub-queries at the selected worker nodes, wherein executing includes using the foreign table declarations to convert data in semi-structured formats into tabular formats to produce tabular data responsive to the query language query; and

merging the tabular data to produce a query result.

21. The computer implemented method of claim 20 further comprising associating each sub-query with a related data block.

22. The computer implemented method of claim 21 further comprising directing each sub-query to a worker node that contains the related data block.

23. The computer implemented method of claim 20 further comprising generating foreign table metadata, wherein the foreign table metadata characterizes the tabular formats.

24. The computer implemented method of claim 23 further comprising accessing the foreign table metadata to develop the distributed query plan.

25. The computer implemented method of claim 20 further comprising generating metadata on the data blocks, wherein the metadata includes the location of the data blocks across the networked worker nodes.

26. The computer implemented method of claim 25 further comprising accessing the metadata on the data blocks to develop the distributed query plan.

27. The computer implemented method of claim 20 further comprising evaluating alternate network input/output costs to develop the distributed query plan.

28. The computer implemented method of claim 20 further comprising evaluating statistics on the data blocks to develop the distributed query plan.

29. The computer implemented method of claim 20 further comprising issuing commands to copy one or more data blocks to execute the distributed query plan.

30. The computer implemented method of claim 20 further comprising repartitioning the data in data blocks to execute the distributed query plan.

31. The computer implemented method of claim 20 wherein a data block is replicated across the networked worker nodes.

32. The computer implemented method of claim 20 further comprising storing a data block as a text file.

33. The computer implemented method of claim 20 further comprising storing a data block as a binary file.

34. The computer implemented method of claim 20 further comprising evaluating statistics on the data blocks to develop a sub-query plan.

35. The computer implemented method of claim 20 further comprising evaluating data block indexes to develop a sub-query plan.

36. The computer implemented method of claim 20 wherein the query language query is a Structured Query Language (SQL) query.

37. The computer implemented method of claim 20 wherein the query language sub-query is a Structured Query Language (SQL) query.