Querying One or More Databases

• US 20180165329A1
• Filed: 11/20/2017
• Published: 06/14/2018
• Est. Priority Date: 03/15/2013
• Status: Active Grant

• Current Assignee: Google LLC (Alphabet Inc.)
• Original Assignee: Looker Data Sciences, Inc. (Alphabet Inc.)
• Inventors: Tabb, Lloyd, Porterfield, Benjamin
• Granted Patent: US 10,698,887 B2
• Time in Patent Office: Days
• CPC Class Codes: G06F 16/245 Query processing

Abstract

A system and method for querying a database is disclosed. Database tables are represented as nodes in a model. Each node is associated with at least one leaf. The nodes can be interconnected with one another. A model input is received by a server from a client device, the model input including a starting node, one or more leaves, and optionally one or more filters. A query is executed against a database based on the model input. A subsequent query can be generated by selecting a result of the first query. Also disclosed is a technique for cancelling queries.

Claims

1. A method for querying one or more databases, comprising:

receiving, at a computing device, a selection of a starting node, wherein the starting node is included in a model that corresponds to one or more database tables;

receiving, at the computing device, a selection of a first set of one or more leaves, wherein each leaf is connected to a node in the model;

generating a first database query based on the starting node and the first set of leaves;

providing a first results output based on the first database query executing on the one or more databases;

receiving a selection of a result in the first results output;

generating a second database query based on the selection of the result in the first results output, wherein the second database query is associated with a detail set associated with the result; and

providing a second results output based on the second database query executing on the one or more databases.

2. The method according to claim 1, wherein the one or more databases are relational databases and the first and second database queries are SQL (Structured Query Language) queries.

3. The method according to claim 1, wherein each leaf in the first set of leaves is associated with one of a first leaf type or a second leaf type.

4. The method according to claim 3, wherein the first results output includes a column for each leaf in the first set and a separate row for each unique tuple of values for leaves associated with the first leaf type in the first set.

5. The method according to claim 4, wherein, for each column in the first results output corresponding to a leaf associated with the second leaf type, an aggregate value is provided in each row of the column based on data in the row.

6. The method according to claim 5, wherein the aggregate value is based on computing a sum, a count, an average, a minimum, or a maximum of one or more values in the row.

7. The method according to claim 1, wherein the model includes a plurality of interconnected nodes, wherein each node is associated with one or more leaves.

8. The method according to claim 7, wherein each leaf in the first set is associated with the starting node or another node in the plurality of interconnected nodes.

9. The method according to claim 1, wherein generating the first database query comprises:

generating five data sets to store database query fragments;

for each leaf in the first set, adding one or more database query fragments to one or more of the five data sets based on attributes of the leaf; and

constructing the first database query by appending together the database query fragments from the five data sets.

10. The method according to claim 1, wherein the first database query comprises an SQL (Structured Query Language) query, and the five data sets corresponds to SQL commands for SELECT, JOIN, WHERE, GROUP BY, and HAVING.

11. The method according to claim 1, wherein the detail set associated with the result is associated with a second starting node and a second set of leaves, wherein generating the second database query comprises generating the second database query based on the second starting node and the second set of leaves, and wherein the second database query is filtered by one or more values included in a row in the first results output corresponding to the selected result.

12. The method according to claim 1, wherein the one or more databases comprise relational databases, non-relational databases, file-based databases, and/or non-file-based databases.

13. The method according to claim 1, wherein the first results output is sorted according to one of the leaves in the first set.

14. A system for generating a database query, the system comprising:

one or more databases;

a client device; and

a server configured to;

receive a model input from the client device over a data network, wherein the model input includes a node and a first set of leaves included in a model corresponding to one or more database tables stored in the one or more databases;

generate a plurality of data sets to store database query fragments;

for each leaf in the first set, add one or more database query fragments to one or more of the plurality of data sets based on attributes of the leaf;

construct a database query by appending together the database query fragments from the plurality of data sets;

execute the database query against the one or more databases; and

return results of the database query to the client device.

15. The system according to claim 14, wherein the database query comprises an SQL (Structured Query Language) query, and the plurality of data sets corresponds to the SQL commands for SELECT, JOIN, WHERE, GROUP BY, and HAVING.

16. The system according to claim 15, wherein the leaf is associated with a leaf SQL fragment and a leaf identifier, and wherein the server is configured to add a SQL fragment to the SELECT data set using the syntax “

{leaf SQL fragment} AS {leaf identifier}”

when the leaf is reachable from the node.

17. The system according to claim 15, wherein the leaf is associated with a leaf identifier, and wherein the server is configured to add a SQL fragment to the GROUP BY data set using the syntax “

{leaf identifier}”

when the leaf is associated with a first leaf type.

18. The system according to claim 15, wherein the leaf is associated with a leaf identifier and a node connection SQL fragment, and wherein the server is configured to add a SQL fragment to the JOIN data set using the syntax “

LEFT JOIN {node identifier} ON {node connection SQL fragment}”

when the leaf is not connected to the node, but instead is connected to another node in the model, wherein the node connection SQL fragment is associated with joining two nodes in the model.

19. A method for querying a database, comprising:

receiving, at a server device, a query input from a client device over a network connection;

generating a database query based on the query input;

causing the database query to begin executing against one or more databases;

determining whether a network connection exists between the client device and the server device; and

causing the database query to be cancelled when the server determines that the network connection does not exist between the client device and the server device.

20. The method according to claim 19, wherein determining whether the network connection exists between the client device and the server device comprises performing a non-blocking read on the network connection.