Hierarchical window database query execution
Addressed herein is the problem of expressing and evaluating computations on hierarchies represented as database tables. Engine support for such computations is very limited today, and so they are usually outsourced into stored procedures or client code. Structural grouping is applied to relational algebra to provide concise syntax to express a class of useful computations. Algorithms are also provided to evaluate such structural groupings efficiently by exploiting available indexing schemes. Related apparatus, systems, techniques and articles are also described.
1. A method for implementation by one or more data processors forming part of at least one computing device, the method comprising:
receiving, by a database, a query requesting data in the form of a table in which each row is related to a different node of a hierarchy of nodes, the query providing for unary structural grouping with structural recursion in which each node acts as an input node and an output node, the query specifying a hierarchical window clause and at least one expression containing a window function on a hierarchical window specified by the hierarchical window clause, the hierarchical window clause specifies a NODE field, an underlying hierarchy index, and a direction of intended data flow;
constructing a directed acyclic graph of at least a portion of the rows in a windowed table based on the hierarchical window clause including the specified NODE field, the underlying hierarchy index and the direction of intended data flow;
obtaining, based on expressions specified by the query, results responsive to the query by accumulating data from the rows across edges of the directed acyclic graph, the obtaining reusing previous results to form part of the of the results responsive to the query when available; and
providing data comprising at least a portion of the obtained results;
wherein the direction of desired data flow specified in the hierarchical window clause specified by the query follows a direction of (i) edges of the hierarchy of nodes, or (ii) inverted edges of the hierarchy of nodes.
2. The method of claim 1 , wherein the directed acyclic graph is explicitly constructed.
3. The method of claim 1 , wherein the directed acyclic graph is not explicitly constructed.
4. The method of claim 1 , wherein the hierarchical window clause specifies a row field whose contents represent the position of the row in a hierarchy of nodes, and wherein the structure of the directed acyclic graph is derived from the structure of the hierarchy of nodes.
5. The method of claim 3 , wherein the data flow across the directed acyclic graph bypasses at least one intermediate node of the hierarchy of nodes.
6. The method of claim 1 , wherein providing data comprises at least one of: persisting at least a portion of the obtained results, loading at least a portion of the obtained results into memory, transmitting at least a portion of the obtained results to a remote computing system, or displaying at least a portion of the obtained results in an electronic visual display.
7. The method of claim 1 , wherein the query further specifies a window partition clause and the data stored in the hierarchy of nodes is partitioned according to the window partition clause, wherein the generated directed acyclic graph of the hierarchy of nodes is based on the partitioned data.
8. The method of claim 1 , wherein the query is formulated in Structured Query Language (SQL).
9. The method of claim 1 , wherein the query comprises a RECURSIVE keyword wrapping at least one expression having at least one window function over the hierarchical window.
10. The method of claim 1 , wherein the database is a main-memory relational database management system.
11. The method of claim 8 , wherein the database is a column-oriented in-memory database.
12. The method of claim 1 , wherein the database is a distributed database in which data is stored across multiple computing systems.
13. A system comprising:
at least one data processor; and
memory storing instructions which, when executed by the at least one data processor, result in operations comprising:
receiving, by a database, a query requesting data in the form of a table in which each row is related to a different node of a hierarchy of nodes, the query providing for unary structural grouping with structural recursion in which each node acts as an input node and an output node, the query specifying a hierarchical window clause and at least one expression containing a window function on a hierarchical window specified by the hierarchical window clause, the hierarchical window clause specifies a NODE field, an underlying hierarchy index, and a direction of intended data flow;
constructing a directed acyclic graph of at least a portion of the rows in a windowed table based on the hierarchical window clause including the specified NODE field, the underlying hierarchy index and the direction of intended data flow;
obtaining, based on expressions specified by the query, results responsive to the query by accumulating data from the rows across edges of the directed acyclic graph, the obtaining reusing previous results to form part of the of the results responsive to the query when available; and
providing data comprising at least a portion of the obtained results;
wherein the direction of desired data flow specified in the hierarchical window clause specified by the query follows a direction of (i) edges of the hierarchy of nodes, or (ii) inverted edges of the hierarchy of nodes.
14. The system of claim 13 , wherein the directed acyclic graph is explicitly constructed.
15. The system of claim 13 , wherein the directed acyclic graph is not explicitly constructed.
16. The system of claim 13 , wherein the hierarchical window clause specifies a row field whose contents represent the position of the row in a hierarchy of nodes, and wherein the structure of the directed acyclic graph is derived from the structure of the hierarchy of nodes.
17. A non-transitory computer program product storing instructions which, when executed by at least one data processor forming part of at least one computing system, result in operations comprising:
receiving, by a database, a query requesting data in the form of a table in which each row is related to a different node of a hierarchy of nodes, the query providing for unary structural grouping with structural recursion in which each node acts as an input node and an output node, the query specifying a hierarchical window clause and at least one expression containing a window function on a hierarchical window specified by the hierarchical window clause, the hierarchical window clause specifies a NODE field, an underlying hierarchy index, and a direction of intended data flow;
constructing a directed acyclic graph of at least a portion of the rows in a windowed table based on the hierarchical window clause including the specified NODE field, the underlying hierarchy index and the direction of intended data flow;
obtaining, based on expressions specified by the query, results responsive to the query by accumulating data from the rows across edges of the directed acyclic graph, the obtaining reusing previous results to form part of the of the results responsive to the query when available; and
providing data comprising at least a portion of the obtained results;
wherein the direction of desired data flow specified in the hierarchical window clause specified by the query follows a direction of (i) edges of the hierarchy of nodes, or (ii) inverted edges of the hierarchy of nodes.