Processor Implemented Systems and Methods for Using the Catalog Part of an SQL Identifier to Expose/Access Heterogeneous Data

• US 20110213778A1
• Filed: 02/25/2011
• Published: 09/01/2011
• Est. Priority Date: 02/26/2010
• Status: Active Grant

• Current Assignee: SAS Institute Incorporated
• Original Assignee: SAS Institute Incorporated
• Inventors: Beesley, James Michael, Jeffreys, David Kerr, Hess, Robert Brian
• Granted Patent: US 8,645,386 B2
• Time in Patent Office: Days
• US Class Current: 707/740
• CPC Class Codes: G06F 16/2471 Distributed queries

Abstract

Systems and methods are provided for providing data from a plurality of databases stored on a plurality of disparate servers. A query is received for data from a plurality of databases stored on a plurality of disparate servers. An address for a first server is resolved that stores a first database identified by the catalog field in the query using a logical name-to-connection metadata map. A first sub-query is transmitted to the first server based on the received query, and a first sub-query response is received from the first server. The resolving, transmitting, and receiving are repeated for a second server. The first sub-query response and the second sub-query response are combined to generate a query response according to the received query, and the query response is transmitted.

Claims

1. A computer-implemented method of providing data from a plurality of databases stored on a plurality of disparate servers, comprising:

receiving a query for data from a plurality of databases stored on a plurality of disparate servers, the query identifying the plurality of databases by a logical catalog name of an object reference;

resolving an address for a first server that stores a first database identified by the logical catalog name in the query using a logical name-to-connection metadata map;

transmitting a first sub-query to the first server based on the received query;

receiving a first sub-query response from the first server;

resolving an address for a second server that stores a second database identified by the logical catalog name in the query using the logical name-to-connection metadata map;

transmitting a second sub-query to the second server based on the received query;

receiving a second sub-query response from the second server;

combining the first sub-query response and the second sub-query response to generate a query response according to the received query; and

transmitting the query response.

2. The method of claim 1, further comprising generating and storing the logical name-to-connection metadata map, wherein the logical name-to-connection metadata map contains logical names for a plurality of databases and addresses for the plurality of databases, the addresses identifying which of the plurality of disparate database servers the plurality of databases are stored.

3. The method of claim 1, wherein the object reference identifies an object according to a catalog.schema.object identifier syntax.

4. The method of claim 1, further comprising establishing a connection to the plurality of databases stored on the plurality of disparate servers using a connection string.

5. The method of claim 4, wherein connection options of the connection string identify the plurality of databases.

6. The method of claim 5, wherein the connection options of the connection string identifies all catalogs on a server using a ‘

*’

character.

7. The method of claim 1, wherein the query identifies the plurality of databases by logical names without necessarily identifying a physical location of the databases.

8. The method of claim 1, wherein the query response is indiscernible from a single server query response that accesses one or more databases only on single server.

9. The method of claim 1, wherein identifying the first server includes looking up a logical catalog name from the query in a logical name/connection metadata map.

10. A computer-implemented system for providing data from a plurality of databases stored on a plurality of disparate servers, comprising:

one or more data processors;

a computer-readable memory encoded with instructions for commanding the one or more data processors to execute a method comprising;

receiving a query for data from a plurality of databases stored on a plurality of disparate servers, the query identifying the plurality of databases by a logical catalog name of an object reference;

resolving an address for a first server that stores a first database identified by the logical catalog name in the query using a logical name-to-connection metadata map;

transmitting a first sub-query to the first server based on the received query;

receiving a first sub-query response from the first server;

resolving an address for a second server that stores a second database identified by the logical catalog name in the query using the logical name-to-connection metadata map;

transmitting a second sub-query to the second server based on the received query;

receiving a second sub-query response from the second server;

combining the first sub-query response and the second sub-query response to generate a query response according to the received query; and

transmitting the query response.

11. The system of claim 10, wherein the method further comprises generating and storing the logical name-to-connection metadata map, wherein the logical name-to-connection metadata map contains logical names for a plurality of databases and addresses for the plurality of databases, the addresses identifying which of the plurality of disparate database servers the plurality of databases are stored.

12. The system of claim 10, wherein the object reference identifies an object according to a catalog.schema.object identifier syntax.

13. The system of claim 10, wherein the method further comprises establishing a connection to the plurality of databases stored on the plurality of disparate servers using a connection string.

14. The system of claim 13, wherein connection options of the connection string identify the plurality of databases.

15. The system of claim 14, wherein the connection options of the connection string identifies all databases on a server using a ‘

*’

character.

16. The system of claim 10, wherein the query identifies the plurality of databases by logical names without necessarily identifying a physical location of the databases.

17. The system of claim 10, wherein the query response is indiscernible from a single server query response that accesses one or more databases only on single server.

18. The system of claim 10, wherein identifying the first server includes looking up a logical catalog name from the query in a logical name/connection metadata map.

19. A computer-readable medium encoded with instructions for commanding one or more data processors to execute a method of providing data from a plurality of databases stored on a plurality of disparate servers, the method comprising:

receiving a query for data from a plurality of databases stored on a plurality of disparate servers, the query identifying the plurality of databases by a logical catalog name of an object reference;

resolving an address for a first server that stores a first database identified by the logical catalog name in the query using a logical name-to-connection metadata map;

transmitting a first sub-query to the first server based on the received query;

receiving a first sub-query response from the first server;

resolving an address for a second server that stores a second database identified by the logical catalog name in the query using the logical name-to-connection metadata map;

transmitting a second sub-query to the second server based on the received query;

receiving a second sub-query response from the second server;

combining the first sub-query response and the second sub-query response to generate a query response according to the received query; and

transmitting the query response.