Processor implemented systems and methods for using the catalog part of an SQL identifier to expose/access heterogeneous data

• US 8,645,386 B2
• Filed: 02/25/2011
• Issued: 02/04/2014
• Est. Priority Date: 02/26/2010
• Status: Active Grant

• Current Assignee: SAS Institute Incorporated
• Original Assignee: SAS Institute Incorporated
• Inventors: Hess, Robert Brian, Beesley, James Michael, Jeffreys, David Kerr
• Primary Examiner(s): Vy, Hung T
• Application Number:
  US13/035,630
• Publication Number:
  US 20110213778A1
• Time in Patent Office: 1,075 Days
• Field of Search: 707/711, 707/740, 707/722, 707/696, 707/713
• 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-program product, tangibly embodied in a machine-readable non-transitory storage medium, including instructions configured to cause a data processing apparatus to perform operations including:

receiving, at a computing device, a connection string, wherein the connection string;

includes a logical catalog name associated with a first data source and a logical catalog name associated with a second data source, and

provides connection properties of the first data source and connection properties of the second data source;

retrieving connection information from a metadata map, wherein retrieving connection information includes retrieving additional connection properties of the first data source and additional connection properties of the second data source, wherein the metadata map associates a first catalog name identifier with the first data source and a second catalog name identifier with the second data source;

augmenting the connection string based on the additional connection properties of the first data source and the additional connection properties of the second data source, wherein the metadata map associates the first catalog name identifier with the first data source and the second catalog name identifier with the second data source;

establishing a connection with the first data source;

establishing a connection with the second data source, wherein establishing the connection with the first data source and establishing the connection with the second data source includes using the augmented connection string;

receiving a query, wherein the query includes the first catalog name identifier, the second catalog name identifier, a first object reference and a second object reference, wherein each of the object references identifies an object according to a catalog.schema.object identifier syntax;

determining that the first data source and the second data source are referenced by the query, wherein determining includes ascertaining that the metadata map associates the first catalog name identifier and the second catalog name identifier with the first data source and the second data source, respectively;

in response to determining that the first data source and the second data source are referenced by the query, generating a first sub-query for information from the first data source and a second sub-query for information from the second data source, wherein generating the first sub-query is based on the first object reference and wherein generating the second sub-query is based on the second object reference;

querying the first data source using the first sub-query;

querying the second data source using the second sub-query;

receiving data retrieved from the first data source based on the first sub-query;

receiving data retrieved from the second data source based on the second sub-query; and

generating a query response by combining the received data retrieved from the first data source and the received data retrieved from the second data source.

2. The computer-program product of claim 1, wherein the operations further include:

generating the metadata map;

storing the metadata map, wherein the metadata map further includes a name and an address of the first data source and a name and an address of the second data source; and

transmitting the query response.

3. The computer-program product of claim 1, wherein the first data source resides on a first server, and wherein the second data source resides on a second server different from the first server.

4. The computer-program product of claim 1, wherein the metadata map is a logical name-to-connection metadata map.

5. The computer-program product of claim 1, wherein the first data source is a DB2 data source, and wherein the second data source is a Structured Query Language (SQL) data source.

6. A system, comprising:

one or more processing units;

one or more non-transitory computer-readable storage mediums containing instructions configured to cause the one or more processing units to perform operations including;

receiving, at a computing device, a connection string, wherein the connection string;

includes a logical catalog name associated with a first data source and a logical catalog name associated with a second data source, and

provides connection properties of the first data source and connection properties of the second data source;

retrieving connection information from a metadata map, wherein retrieving connection information includes retrieving additional connection properties of the first data source and additional connection properties of the second data source, wherein the metadata map associates a first catalog name identifier with the first data source and a second catalog name identifier with the second data source;

augmenting the connection string based on the additional connection properties of the first data source and the additional connection properties of the second data source, wherein the metadata map associates the first catalog name identifier with the first data source and the second catalog name identifier with the second data source;

establishing a connection with the first data source;

establishing a connection with the second data source, wherein establishing the connection with the first data source and establishing the connection with the second data source includes using the augmented connection string;

receiving a query, wherein the query includes the first catalog name identifier, the second catalog name identifier, a first object reference and a second object reference, wherein each of the object references identifies an object according to a catalog.schema.object identifier syntax;

determining that the first data source and the second data source are referenced by the query, wherein determining includes ascertaining that the metadata map associates the first catalog name identifier and the second catalog name identifier with the first data source and the second data source, respectively;

in response to determining that the first data source and the second data source are referenced by the query, generating a first sub-query for information from the first data source and a second sub-query for information from the second data source, wherein generating the first sub-query is based on the first object reference and wherein generating the second sub-query is based on the second object reference;

querying the first data source using the first sub-query;

querying the second data source using the second sub-query;

receiving data retrieved from the first data source based on the first sub-query;

receiving data retrieved from the second data source based on the second sub-query; and

generating a query response by combining the received data retrieved from the first data source and the received data retrieved from the second data source.

7. The system of claim 6, wherein the operations further include:

generating the metadata map;

storing the metadata map, wherein the metadata map further includes a name and an address of the first data source and a name and an address of the second data source; and

transmitting the query response.

8. The system of claim 6, wherein the first data source resides on a first server, and wherein the second data source resides on a second server different from the first server.

9. The system of claim 6, wherein the metadata map is a logical name-to-connection metadata map.

10. The system of claim 6, wherein the first data source is a DB2 data source, and wherein the second data source is a Structured Query Language (SQL) data source.

11. A computer-implemented method, comprising:

receiving, at a computing device, a connection string, wherein the connection string;

includes a logical catalog name associated with a first data source and a logical catalog name associated with a second data source, and

provides connection properties of the first data source and connection properties of the second data source;

retrieving connection information from a metadata map, wherein retrieving connection information includes retrieving additional connection properties of the first data source and additional connection properties of the second data source, wherein the metadata map associates a first catalog name identifier with the first data source and a second catalog name identifier with the second data source;

augmenting the connection string based on the additional connection properties of the first data source and the additional connection properties of the second data source, wherein the metadata map associates the first catalog name identifier with the first data source and the second catalog name identifier with the second data source;

establishing a connection with the first data source;

establishing a connection with the second data source, wherein establishing the connection with the first data source and establishing the connection with the second data source includes using the augmented connection string;

receiving a query, wherein the query includes the first catalog name identifier, the second catalog name identifier, a first object reference and a second object reference, wherein each of the object references identifies an object according to a catalog.schema.object identifier syntax;

determining that the first data source and the second data source are referenced by the query, wherein determining includes ascertaining that the metadata map associates the first catalog name identifier and the second catalog name identifier with the first data source and the second data source, respectively;

in response to determining that the first data source and the second data source are referenced by the query, generating a first sub-query for information from the first data source and a second sub-query for information from the second data source, wherein generating the first sub-query is based on the first object reference and wherein generating the second sub-query is based on the second object reference;

querying the first data source using the first sub-query;

querying the second data source using the second sub-query;

receiving data retrieved from the first data source based on the first sub-query;

receiving data retrieved from the second data source based on the second sub-query;

generating a query response by combining the received data retrieved from the first data source and the received data retrieved from the second data source.

12. The method of claim 11, further comprising:

generating the metadata map;

storing the metadata map, wherein the metadata map further includes a name and an address of the first data source and a name and an address of the second data source; and

transmitting the query response.

13. The method of claim 11, wherein the first data source resides on a first server, and wherein the second data source resides on a second server different from the first server.

14. The method of claim 11, wherein the metadata map is a logical name-to-connection metadata map.

15. The method of claim 11, wherein the first data source is a DB2 data source, and wherein the second data source is a Structured Query Language (SQL) data source.