Integration of calculation models into SQL layer

Patent metadata

Field	Value
Publication number	US-9846724-B2
Application number	US-201414540792-A
Country	US
Kind code	B2
Filing date	Nov 13, 2014
Priority date	Nov 13, 2014
Publication date	Dec 19, 2017
Grant date	Dec 19, 2017

Abstract

According to an aspect, a database system for integrating calculation models into execution plans includes a first engine configured to parse a query to be applied on a database. The first engine is configured to invoke a second engine during query compilation. The second engine is configured to instantiate a calculation model based on the query, and the second engine is configured to derive a converted calculation model by converting the calculation model into a format compatible with the first engine. The first engine is configured to incorporate the converted calculation model into an execution plan during the query compilation and execute the query on the database according to execution plan.

First claim

What is claimed is:

1. A database system for converting and integrating calculation models into execution plans, the database system comprising:
at least one processor;
a non-transitory computer-readable medium configured to store executable instructions that when executed by the at least one processor are configured to implement:
a structured query language (SQL) engine configured to parse a query to be applied on a relational database, and obtain an SQL model having a first format, the SQL engine configured to invoke a calculation engine to obtain a calculation model, the calculation model having a second format not compatible with the SQL engine,
the calculation engine configured to convert, during query compilation and before execution, the second format of the calculation model to the first format that is compatible with the SQL engine,
the SQL engine configured to incorporate, during query compilation and before execution, the converted calculation model into an execution plan, the execution plan also having the SQL model,
the SQL engine configured to optimize the execution plan having both the converted calculation model and the SQL model by reordering join operators and pushing an aggregation operator downstream in the execution plan,
the SQL engine configured to execute the query on the relational database according to the optimized execution plan.

2. The database system of claim 1 , wherein the SQL engine is configured to determine whether or not the calculation model includes at least one non-relational operator, and the calculation engine is configured to convert the second format of the calculation model to the first format in response to the determination that the calculation model does not include the at least one non-relational operator.

3. The database system of claim 1 , wherein, before converting, the calculation engine is configured to instantiate the calculation model by merging the query with the calculation model and removing one or more attributes from the calculation model that are not required by the query.

4. The database system of claim 1 , wherein the second format of the calculation model is a directed acyclic graph (DAG), the DAG being a format not compatible with the SQL engine.

5. The database system of claim 1 , wherein the first format is a Query Optimization (QO) graph that is compatible with the SQL engine.

6. The database system of claim 1 , wherein the calculation model includes a plurality of operators within the second format, wherein one of the plurality of operators is converted to two or more operators in the first format.

7. The database system of claim 2 , wherein, in response to the determination that the calculation model includes the at least one non-relational operator, the calculation model is not converted, but rather the calculation engine is configured to execute the calculation model to obtain intermediate results, and the SQL engine is configured to combine the intermediate results with the SQL model for further execution by the SQL engine.

8. The database system of claim 1 , wherein the converted calculation model is joined with the SQL model within the execution plan.

9. The database system of claim 1 , wherein the query specifies a calculation view that incorporates an attribute view, the SQL engine configured to obtain the SQL model for the attribute view, the SQL engine configured to invoke the calculation engine to obtain the calculation model for the calculation view, the execution plan integrating both the SQL model and the converted calculation model such that the calculation model is not separately executed to obtain intermediate results.

10. A computer program product tangibly embodied on a non-transitory computer-readable storage medium and including executable code that, when executed, is configured to cause at least one processor to:
parse, by a structured query language (SQL) engine, a query to be executed on a relational database;
obtain, by the SQL engine, an SQL model having a first format;
invoke, by the SQL engine, a calculation engine to obtain a calculation model, the calculation model having a second format that is not compatible with the SQL engine;
determine, by the SQL engine, whether or not the calculation model includes at least one non-relational operator;
convert, by the calculation engine, during query compilation and before execution, the second format of the calculation model to the first format in response to the determination that the calculation model does not include the at least one non-relational operator, the first format being compatible with the SQL engine;
incorporate, by the SQL engine, during query compilation and before execution, the converted calculation model into an execution plan, the converted calculation model being joined with the SQL model within the execution plan;
optimize, by the SQL engine, the execution plan having the converted calculation model joined with the SQL model including reordering join operators, and pushing an aggregation operator downstream in the execution plan; and
execute, by the SQL engine, the query on the relational database according to the execution plan.

11. The computer program product of claim 10 , wherein the executable code includes instructions that, when executed by the at least one processor, are configured to:
instantiate, by the calculation engine, the calculation model by merging the query with the calculation model and removing one or more attributes from the calculation model that are not required by the query; and
optimize, by the calculation engine, the calculation model before converting the second format of the calculation model to the first format.

12. The computer program product of claim 10 , wherein the second format is a directed acyclic graph (DAG), and the first format is a Query Optimization (QO) graph, wherein the executable code to convert the second format of the calculation model to the first format includes instructions to convert the DAG to the QO graph.

13. The computer program product of claim 10 , wherein, in response to the determination that the calculation model includes the at least one non-relational operator, the calculation model is not converted, but rather is executed to obtain intermediate results, and the intermediate results are combined with the SQL model for further execution by the SQL engine.

14. The computer program product of claim 10 , wherein the calculation model includes a plurality of operators within the second format, wherein one of the plurality of operators is converted to two or more operators in the first format.

15. A method for converting and integrating calculation models into execution plans, the method being performed by at least one processor, the method comprising:
parsing, by a structured query language (SQL) engine, a query to be executed on a relational database, the query requiring a calculation model and an SQL model, the SQL model having a first format, the calculation model having a second format not compatible with the SQL engine;
determining, by the SQL engine, whether the calculation model is convertible into the first format by determining whether or not the calculation model includes at least one non-relational operator;
converting, by a calculation engine, during query compilation and before execution, the second format of the calculation model to the first format in response to the determination that the calculation model does not include the at least one non-relational operator;
integrating, by the SQL engine, during query compilation and before execution, the converted calculation mode

Assignees

• Weyerhaeuser Christoph
• Mindnich Tobias
• Merx Johannes
• Yoon Yongsik
• Wi Sung Heun
• Han Jeong Ae
• Sap Se

Inventors

• Weyerhaeuser Christoph
• Mindnich Tobias
• Merx Johannes
• Yoon Yongsik
• Wi Sung Heun
• Han Jeong Ae

Classifications

• G06F16/24542 Primary

  Plan optimisation · CPC title

• G06F17/30463 Primary

  Physics · mapped topic