Back to results
US 11,899,664 B2Granted
Computerized system and method for optimizing queries in a templated virtual semantic layer
AssigneeYahoo Ad Tech LLC
InventorsAaron J. Klish, Jonathan Kilroy, Moiz Arafat, Rishi Agarwal
Life of this patent
Estimating the term…
Abstract
A novel framework optimizes SQL queries that are generated from a templated virtual sematic layer. The framework introduces the use of a virtual semantic layer into database management systems' operations, whereby templated SQL queries can be rewritten according to a determined and measured nesting, dimensional structure that produces an optimized search system. This enables templated SQL fragments to be translated for query optimization, thereby reducing the drain on a database's resources and minimizing a query's impact on the database's performance.
Claims18
A method comprising:
- identifying, by a device, a database comprising a plurality of information organized into at least a set of columns;
- identifying, by the device, a Column Object (CO) within a column of the database, the CO comprising information related to a column name, a Structured Query Language (SQL) expression and an alias;
- expanding, by the device, templated fragments associated with the SQL expression of the identified CO;
- parsing, by the device, each of the expanded templated fragments of the SQL expression, and determining a syntax tree of the SQL expression from the expanded templated fragments;
- analyzing, by the device, the syntax tree of the SQL expression;
- creating, by the device, based on the analysis, a new CO related to the identified CO, the new CO comprising information related to the column name, a templated expression of the SQL expression and the alias, the creation of the new CO comprising:
- identifying an unnested SQL fragment associated with the CO;
- parsing the unnested SQL fragment into the syntax tree;
- analyzing the syntax tree, and identifying aggregation function calls;
- translating each identified aggregation function call into a set of inner aggregations;
- assigning each inner aggregation function a unique alias;
- converting each inner aggregation into another CO;
- translating each aggregation function call into a set of outer aggregations;
- modifying operands of the syntax tree to reference the unique aliases;
- analyzing the modified syntax tree and generating a new native SQL expression;
- replacing aliases in the new native SQL expression with the templated expression;
- and
- converting the outer aggregations into the new CO;
- analyzing, by the device, information related to the new CO, and determining a type of dimension nesting, the type of dimension nesting being based on whether a referenced join is to occur as an inner query or outer query;
- and
- performing, by the device, the determined type of dimension nesting.
18 more elements
A non-transitory computer-readable storage medium tangibly encoded with computer-executable instructions, that when executed by a processor associated with a device, performs a method comprising:
- identifying, by the device, a database comprising a plurality of information organized into at least a set of columns;
- identifying, by the device, a Column Object (CO) within a column of the database, the CO comprising information related to a column name, a Structured Query Language (SQL) expression and an alias;
- expanding, by the device, templated fragments associated with the SQL expression of the identified CO;
- parsing, by the device, each of the expanded templated fragments of the SQL expression, and determining a syntax tree of the SQL expression from the expanded templated fragments;
- analyzing, by the device, the syntax tree of the SQL expression;
- creating, by the device, based on the analysis, a new CO related to the identified CO, the new CO comprising information related to the column name, a templated expression of the SQL expression and the alias, the creation of the new CO comprising:
- identifying an unnested SQL fragment associated with the CO;
- parsing the unnested SQL fragment into the syntax tree;
- analyzing the syntax tree, and identifying aggregation function calls;
- translating each identified aggregation function call into a set of inner aggregations;
- assigning each inner aggregation function a unique alias;
- converting each inner aggregation into another CO;
- translating each aggregation function call into a set of outer aggregations;
- modifying operands of the syntax tree to reference the unique aliases;
- analyzing the modified syntax tree and generating a new native SQL expression;
- replacing aliases in the new native SQL expression with the templated expression;
- and
- converting the outer aggregations into the new CO;
- analyzing, by the device, information related to the new CO, and determining a type of dimension nesting, the type of dimension nesting being based on whether a referenced join is to occur as an inner query or outer query;
- and
- performing, by the device, the determined type of dimension nesting.
18 more elements
A device comprising:
- a processor configured to:
- identify a database comprising a plurality of information organized into at least a set of columns;
- identify a Column Object (CO) within a column of the database, the CO comprising information related to a column name, a Structured Query Language (SQL) expression and an alias;
- expand templated fragments associated with the SQL expression of the identified CO;
- parse each of the expanded templated fragments of the SQL expression, and determine a syntax tree of the SQL expression from the expanded templated fragments;
- analyze the syntax tree of the SQL expression;
- create, based on the analysis, a new CO related to the identified CO, the new CO comprising information related to the column name, a templated expression of the SQL expression and the alias, the creation of the new CO comprising:
- identifying an unnested SQL fragment associated with the CO;
- parsing the unnested SQL fragment into the syntax tree;
- analyzing the syntax tree, and identifying aggregation function calls;
- translating each identified aggregation function call into a set of inner aggregations;
- assigning each inner aggregation function a unique alias;
- converting each inner aggregation into another CO;
- translating each aggregation function call into a set of outer aggregations;
- modifying operands of the syntax tree to reference the unique aliases;
- analyzing the modified syntax tree and generating a new native SQL expression;
- replacing aliases in the new native SQL expression with the templated expression;
- and
- converting the outer aggregations into the new CO;
- analyze information related to the new CO, and determine a type of dimension nesting, the type of dimension nesting being based on whether a referenced join is to occur as an inner query or outer query;
- and
- perform the determined type of dimension nesting.
19 more elements
Dependencies are inferred from the claim text.