Query generation based on a logical data model with one-to-one joins

Patent metadata

Field	Value
Publication number	US-12259889-B2
Application number	US-202418599389-A
Country	US
Kind code	B2
Filing date	Mar 8, 2024
Priority date	Apr 23, 2019
Publication date	Mar 25, 2025
Grant date	Mar 25, 2025

Abstract

Systems and methods for query generation based on a logical data model with one-to-one joins are described. For example, methods may include accessing a join graph representing tables in a database; receiving a first query; selecting a connected subgraph of the join graph that includes the two or more tables referenced in the first query; accessing an indication that a directed edge of the connected subgraph corresponds to a one-to-one join; modifying the connected subgraph based on the indication to obtain a modified subgraph; generating one or more leaf queries based on the modified subgraph; generating a query graph that specifies joining of results from queries based on the one or more leaf queries; invoking a transformed query on the database that is based on the query graph and the queries based on the one or more leaf queries.

First claim

What is claimed is:

1. A method comprising:
obtaining, by a data analysis system, a database query for obtaining results data, wherein the database query is expressed in accordance with a defined structured data access language implemented by a database, wherein obtaining the database query includes:
automatically generating, by the data analysis system, the database query in accordance with a transformed database agnostic representation of the database query; and
prior to automatically generating the database query, automatically generating, by the data analysis system, the transformed database agnostic representation of the database query by:
obtaining, by the data analysis system, user input data, wherein the results data is responsive to the user input data;
prior to obtaining the user input data, obtaining, by the data analysis system, a join graph, wherein the join graph indicates a first join between a first table stored in the database and a second table stored in the database, wherein relationship data indicating that the first join is a one-to-one join is unavailable from the join graph;
automatically generating, by the data analysis system, in response to the user input data, a database agnostic representation of the database query; and
transforming, by the data analysis system, the database agnostic representation to obtain the transformed database agnostic representation, wherein transforming the database agnostic representation includes:
obtaining, by the data analysis system, from the join graph, a connected subgraph, wherein the connected subgraph includes a first vertex representing the first table, a second vertex representing the second table, and a first unidirectional edge extending from the first vertex to the second vertex representing the first join;
accessing, by the data analysis system, the relationship data;
in response to the relationship data, obtaining, by the data analysis system, a modified subgraph by modifying the connected subgraph, wherein the modified subgraph includes the first vertex representing the first table, the second vertex representing the second table, and a second unidirectional edge extending from the second vertex to the first vertex, and wherein the modified subgraph omits the first unidirectional edge; and
automatically generating, by the data analysis system, in response to the modified subgraph, a query graph including a root vertex corresponding to the database query;

obtaining, by the data analysis system, from the database, the results data, responsive to execution of the database query by the database; and
outputting, by the data analysis system, at least a portion of the results data for presentation to a user.

2. The method of claim 1 , wherein:
the connected subgraph includes a first cardinality of root vertices; and
the modified subgraph includes a second cardinality of root vertices that is less than the first cardinality.

3. The method of claim 1 , wherein:
obtaining the modified subgraph includes determining, by the data analysis system, that the connected subgraph omits an edge representing a many-to-one join between the first table and a fourth table.

4. The method of claim 1 , wherein obtaining the query graph includes including, in the query graph, a non-root vertex representing a database agnostic representation of a second database query that references the second table, wherein the second vertex is a root vertex of the modified subgraph.

5. The method of claim 4 , wherein obtaining the query graph includes automatically generating the database agnostic representation of the second database query.

6. The method of claim 1 , wherein obtaining the query graph includes including, in the query graph, a non-root vertex representing a database agnostic representation of a second database query that references the first table, wherein the first table includes a measure column referenced in the user input data.

7. The method of claim 6 , wherein obtaining the query graph includes automatically generating the database agnostic representation of the second database query.

8. A data analysis system, comprising:
a memory; and
a processor that executes instructions stored in the memory to:
obtain a database query for obtaining results data, wherein the database query is expressed in accordance with a defined structured data access language implemented by a database, wherein to obtain the database query the processor executes the instructions to:
automatically generate the database query in accordance with a transformed database agnostic representation of the database query; and
prior to the generation of the database query, automatically generate the transformed database agnostic representation of the database query, wherein to automatically generate the transformed database agnostic representation of the database query the processor executes the instructions to:
obtain user input data, wherein the results data is responsive to the user input data;
prior to obtaining the user input data, obtain a join graph, wherein the join graph indicates a first join between a first table stored in the database and a second table stored in the database, wherein relationship data that indicates that the first join is a one-to-one join is unavailable from the join graph;
automatically generate, in response to the user input data, a database agnostic representation of the database query; and
transform the database agnostic representation to obtain the transformed database agnostic representation, wherein to transform the database agnostic representation the processor executes the instructions to:
 obtain, from the join graph, a connected subgraph, wherein the connected subgraph includes a first vertex representing the first table, a second vertex representing the second table, and a first unidirectional edge extending from the first vertex to the second vertex representing the first join;
 access the relationship data;
 in response to the relationship data, obtain a modified subgraph by modification of the connected subgraph, wherein the modified subgraph includes the first vertex representing the first table, the second vertex representing the second table, and a second unidirectional edge extending from the second vertex to the first vertex, and wherein the modified subgraph omits the first unidirectional edge; and
 automatically generate, in response to the modified subgraph, a query graph including a root vertex corresponding to the database query;

obtain, from the database, the results data, responsive to execution of the database query by the database; and
output at least a portion of the results data for presentation to a user.

9. The data analysis system of claim 8 , wherein:
the connected subgraph includes a first cardinality of root vertices; and
the modified subgraph includes a second cardinality of root vertices that is less than the first cardinality.

10. The data analysis system of claim 8 , wherein:
to obtain the modified subgraph the processor executes the instructions to determine that the connected subgraph omits an edge representing a many-to-one join between the first table and a fourth table.

11. The data analysis system of claim 8 , wherein to obtain the query graph the processor executes the instructions to include, in the query graph, a non-root vertex representing a database agnostic representation of a second database query that references the second table, wherein the second vertex is a root vertex of the modified subgraph.

12. The data analysis system of claim 11 , wherein to obtain the query graph the processor executes the instructions to automatically genera

Assignees

• Thoughtspot Inc

Inventors

• Shah Naman
• Kothari Rakesh
• Agarwal Vaibhav

Classifications

• G06F16/2246

  Trees, e.g. B+trees · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/211

  Schema design and management · CPC title

• G06F16/24534 Primary

  Query rewriting; Transformation · CPC title

• G06F16/2456 Primary

  Join operations · CPC title