Query routing and rewriting

• US 10,579,618 B1
• Filed: 03/28/2016
• Issued: 03/03/2020
• Est. Priority Date: 12/15/2015
• Status: Active Grant

• Current Assignee: Amazon Technologies, Inc. (Amazon.com, Inc.)
• Original Assignee: Amazon Technologies, Inc. (Amazon.com, Inc.)
• Inventors: Strahan, Robert
• Primary Examiner(s): Ly, Anh
• Application Number:
  US15/083,087
• Time in Patent Office: 1,436 Days
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 16/213 with details for schema evolution support
• CPC Class Codes: G06F 16/24534 Query rewriting; Transformation
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/248 Presentation of query results
• CPC Class Codes: G06F 16/93 Document management systems
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries

Abstract

A data warehouse system may include a connection pool manager. The connection pool manager may invoke a first client-provided function for rerouting a connection request from a first database to a second database based on a query that is to be executed using the connection. The connection pool manager may invoke a second client-provided function for rewriting the query to be executed to leverage database aspects found on the second database.

Claims

1. A system, comprising:

a first logical database comprising a first logical table;

a second database comprising a second logical table; and

a computing node, that includes at least one processor and at least one memory, that at least;

receives, from a client device, one or more requests collectively indicative of connecting the client device to the first logical database and processing a first query on the first logical table;

causes a connection to be made to the second database in place of a connection to the first logical database, in response to a determination to connect to the second database that is based at least in part on a first result of a first function, the first function comprising instructions for analyzing the first query;

obtains a second query by applying a second function to the first query, wherein the second function comprises instructions for rewriting the first query, wherein rewriting the first query comprises adapting to a difference between the first and second logical tables;

sends a request to process the second query to the second database; and

sends results of processing the second query to the client device.

2. The system of claim 1, wherein the computing node at least:

receives definitions of the first function and the second function from an administrative client device.

3. The system of claim 1, wherein the difference between the first logical table and the second table comprises at least one of a different column, different partitioning scheme, different dataset, different index structures, different query processing engine, or different storage structure.

4. The system of claim 1, wherein the computing node at least:

maintains a first connection pool comprising connections to the first logical database;

maintains a second connection pool comprising connections to the second database; and

determines to connect the client device to a connection to the second database from the second connection pool, the determination based at least in part on output of the first function.

5. The system of claim 1, wherein the first function determines that the second database is preferable for processing the first query based at least in part on the analyzing of the first query.

6. A method, comprising:

receiving one or more requests collectively indicative of connecting a client device to a first logical database and processing a query involving the first logical database;

connecting the client device to a second database, in response to a determination to connect the client device to the second database instead of the first logical database, the determination based at least in part on a result of invoking a first function, the first function comprising executable instructions for analyzing the query;

sending a request to process a version of the query to the second database; and

sending results of processing the version of the query to the client device.

7. The method of claim 6, further comprising:

forming the version of the query by applying a second function to the query, wherein the second function comprises executable instructions for rewriting the query.

8. The method of claim 7, wherein processing the version of the query comprises utilizing an index not present on the first logical database.

9. The method of claim 8, wherein executing the instructions for rewriting the query causes an optimization available on the second database to be used, wherein the optimization is unavailable on the first logical database.

10. The method of claim 6, further comprising:

receiving a definition of the first function from an administrative client device.

11. The method of claim 6, further comprising:

connecting the client device to a proxy for the first logical database and connecting the proxy to the second database.

12. The method of claim 6, wherein connecting to the second logical database instead of the first logical database comprises obtaining a connection to the second logical database from a pool of database connections identified based at least in part on the result of the first function.

13. The method of claim 6, wherein the first function determines that the second database is preferable for processing the query based at least in part on the analyzing of the query.

14. The method of claim 6, further comprising:

determining to connect to the second database instead of the first logical database based at least in part on executing the instructions for analyzing the query and determining, based at least in part on the analyzing, that the second database has greater ability to process the version of the query than the first logical database.

15. A non-transitory computer-readable storage medium having stored thereon instructions that, upon execution by one or more computing devices, cause the one or more computing devices at least to:

receive, from a client device, one or more requests collectively indicative of connecting the client device to a first logical database and processing a first query on the first logical database;

connect the client device to a second database, in response to a determination to connect the client device to the second database instead of the first logical database, the determination based at least in part on a result of a first function, the first function comprising executable instructions for analyzing the first query;

send a request to process a second query based on the first query to the second database; and

send results of processing the second query to the client device.

16. The non-transitory computer-readable storage medium of claim 15, comprising further instructions that, upon execution by the one or more computing devices, cause the one or more computing devices to at least:

form the second query by applying a second function to the first query, wherein the second function comprises executable instructions for rewriting the first query to adapt to a difference between the first logical database and the second database.

17. The non-transitory computer-readable storage medium of claim 15, wherein the first function returns an identifier corresponding to a plurality of connections to the second database.

18. The non-transitory computer-readable storage medium of claim 15, wherein the one or more requests comprise a first request to connect to the first logical database and a second request to connect to the second database, the first request sent separately from the second request.

19. The non-transitory computer-readable storage medium of claim 15, wherein the first logical database corresponds to an operative database.

20. The non-transitory computer-readable storage medium of claim 15, comprising further instructions that, upon execution by the one or more computing devices, cause the one or more computing devices to at least:

determine to connect to the second database instead of the first logical database based at least in part on executing the instructions for analyzing the first query and determining, based at least in part on the analyzing, that capacity of the second database to process the second query is greater than capacity of the first logical database to process the first query.