Efficient top-down hierarchical join on a hierarchically clustered data stream
View Patent ↗An apparatus for conducting a hierarchical join on a hierarchically clustered data stream is disclosed. The apparatus may include a memory storing query conditions that each determine whether a Structured Query Language query is suitable for performing a non-buffering read operation on a hierarchical data storage. A processor in communication with the memory may be configured to receive a Structured Query Language (“SQL”) query from a client device and evaluate the SQL query for one or more of the query conditions stored in the memory. When the processor determines that the SQL query satisfies one or more of the query conditions, the processor may then perform a non-buffering read operation on the hierarchical data storage to obtain a hierarchically clustered data stream. The hierarchically clustered data stream may be evaluated to obtain a plurality of record results, which may be transmitted to the client device.
1. An apparatus for conducting a hierarchical join on a hierarchically clustered data stream, the apparatus comprising:
a memory configured to store a query condition that determines whether a Structured Query Language query is suitable for performing a non-buffering read operation on a hierarchical data storage; and
a processor in communication with the memory, the processor being configured to:
receive a Structured Query Language (“SQL”) query;
evaluate the SQL query for the query condition;
transform the SQL query into a read operation query for the hierarchical data storage; and,
when the SQL query satisfies the query condition:
perform a non-buffering read operation on the hierarchical data storage based on the read operation query, wherein the non-buffering read operation results in a hierarchically clustered data stream;
evaluate the hierarchically clustered data stream according to at least one predicate of the SQL query to obtain a plurality of record results; and
transmit the plurality of record results to a client device.
2. The apparatus of claim 1 , wherein the query condition establishes that the SQL query is valid for performing the non-buffering read operation when a plurality of tables identified in the SQL query are top-down hierarchical.
3. The apparatus of claim 2 , wherein the processor is configured to evaluate the SQL query for the query condition by evaluating a database schema for the hierarchical data storage.
4. The apparatus of claim 2 , wherein the query condition specifies that the plurality of tables identified in the SQL query are top-down hierarchical when each table of the plurality of tables is only a proper ancestor or a proper descendant of another table in the plurality of tables.
5. The apparatus of claim 2 , wherein the query condition is not satisfied when a first table of the plurality of tables has a hierarchical relationship with a second table of the plurality of tables other than being a proper ancestor or being a proper descendant.
6. The apparatus of claim 1 , wherein the query condition establishes that the SQL query is valid for performing the non-buffering read operation when the at least one predicate of the SQL query specifies that, when a record of a first table is to be joined to records of a second table:
the records of the second table are descendants of the record of the first table; and
the record of the first table is not joined to the records of the second table that are not descendants of the record of the first table.
7. The apparatus of claim 6 , wherein the processor is configured to evaluate the SQL query for the query condition by confirming that a prefix of a primary key value of at least one record of the records of the second table comprises a primary key value of the record of the first table.
8. The apparatus of claim 1 , wherein the query condition establishes that the SQL query is valid for performing the non-buffering read operation when the SQL query comprises only one operation type following a first instance of the operation type.
9. The apparatus of claim 8 , wherein the operation type comprises a SQL “LEFT OUTER JOIN” operation type.
10. The apparatus of claim 1 , wherein processor is further configured to evaluate the SQL query for the query condition by determining whether, after an instance of a “LEFT OUTER JOIN” operation type in the SQL query, no other variations of a SQL “JOIN” operation appear other than the “LEFT OUTER JOIN” operation type.
11. The apparatus of claim 1 , wherein the query condition establishes that the SQL query is valid for performing the non-buffering read operation when a first join condition on an ancestor table is not met, a second join condition on a descendant table is also not met.
12. The apparatus of claim 11 , wherein the processor is configured to evaluate the SQL query for the query condition by determining that the at least one predicate comprises a conjunct of the form “X.A=Y.B,” where:
X is a first table;
A is a first column of the first table;
Y is a second table; and
B is a second column of the second table.
13. A method for conducting a hierarchical join on a hierarchically clustered data stream, the method comprising:
storing, in a computer-readable memory, a query condition that determines whether a Structured Query Language query is suitable for performing a non-buffering read operation on a hierarchical data storage
receiving, with a processor in communication with the computer-readable memory, a Structured Query Language (“SQL”) query;
evaluating the SQL query for the query condition;
transforming the SQL query into a read operation query for the hierarchical data storage; and,
when the SQL query satisfies the query condition:
performing a non-buffering read operation on the hierarchical data storage based on the read operation query, wherein the non-buffering read operation results in a hierarchically clustered data stream;
evaluating the hierarchically clustered data stream according to at least one predicate of the SQL query to obtain a plurality of record results; and
transmitting the plurality of record results to a client device.
14. The method of claim 13 , wherein the query condition establishes that the SQL query is valid for performing the non-buffering read operation when a plurality of tables identified in the SQL query are top-down hierarchical.
15. The method of claim 14 , wherein evaluating the SQL query for the query condition comprises evaluating a database schema for the hierarchical data storage.
16. The method of claim 14 , wherein the query condition specifies that the plurality of tables identified in the SQL query are top-down hierarchical when each table of the plurality of tables is only a proper ancestor or a proper descendant of another table in the plurality of tables.
17. The method of claim 14 , wherein the query condition is not satisfied when a first table of the plurality of tables has a hierarchical relationship with a second table of the plurality of tables other than being a proper ancestor or being a proper descendant.
18. The method of claim 13 , wherein the query condition establishes that the SQL query is valid for performing the non-buffering read operation when the at least one predicate of the SQL query specifies that, when a record of a first table is to be joined to records of a second table:
the records of the second table are descendants of the record of the first table; and
the record of the first table is not joined to the records of the second table that are not descendants of the record of the first table.
19. The method of claim 18 , wherein evaluating the SQL query for the query condition comprises confirming that a prefix of a primary key value of at least one record of the records of the second table comprises a primary key value of the record of the first table.
20. The method of claim 13 , wherein the query condition establishes that the SQL query is valid for performing the non-buffering read operation when the SQL query comprises only one operation type following a first instance of the operation type.
21. The method of claim 20 , wherein the operation type comprises a SQL “LEFT OUTER JOIN” operation type.
22. The method of claim 13 , wherein evaluating the SQL query for the query condition comprises determining whether, after an instance of a “LEFT OUTER JOIN” operation type in the SQL query, no other variations of a SQL “JOIN” operation appear other than the “LEFT OUTER JOIN” operation type.
23. The method of claim 13 , wherein the query condition establishes that the SQL query is valid for performing the non-buffering read operation when a first join condition on an ancestor table is not met, a second join condition on a descendant table is also not met.
24. The method of claim 23 , wherein evaluating the SQL query for the query condition by determining that the at least one predicate comprises a conjunct of the form “X.A=Y.B,” where:
X is a first table;
A is a first column of the first table;
Y is a second table; and
B is a second column of the second table.
25. A computer-readable medium having computer-readable instructions stored thereon that, when executed by a processor, cause a processor to perform a method, the method comprising:
storing, in a computer-readable memory, a query condition that determines whether a Structured Query Language query is suitable for performing a non-buffering read operation on a hierarchical data storage
receiving, with a processor in communication with the computer-readable memory, a Structured Query Language (“SQL”) query;
evaluating the SQL query for the query condition;
transforming the SQL query into a read operation query for the hierarchical data storage; and,
when the SQL query satisfies the query condition:
performing a non-buffering read operation on the hierarchical data storage based on the read operation query, wherein the non-buffering read operation results in a hierarchically clustered data stream;
evaluating the hierarchically clustered data stream according to at least one predicate of the SQL query to obtain a plurality of record results; and
transmitting the plurality of record results to a client device.
26. The computer-readable medium of claim 25 , wherein the query condition establishes that the SQL query is valid for performing the non-buffering read operation when a plurality of tables identified in the SQL query are top-down hierarchical.
27. The computer-readable medium of claim 26 , wherein evaluating the SQL query for the query condition comprises evaluating a database schema for the hierarchical data storage.
28. The computer-readable medium of claim 26 , wherein the query condition specifies that the plurality of tables identified in the SQL query are top-down hierarchical when each table of the plurality of tables is only a proper ancestor or a proper descendant of another table in the plurality of tables.
29. The computer-readable medium of claim 26 , wherein the query condition is not satisfied when a first table of the plurality of tables has a hierarchical relationship with a second table of the plurality of tables other than being a proper ancestor or being a proper descendant.
30. The computer-readable medium of claim 25 , wherein the query condition establishes that the SQL query is valid for performing the non-buffering read operation when the at least one predicate of the SQL query specifies that, when a record of a first table is to be joined to records of a second table:
the records of the second table are descendants of the record of the first table; and
the record of the first table is not joined to the records of the second table that are not descendants of the record of the first table.
31. The computer-readable medium of claim 30 , wherein evaluating the SQL query for the query condition comprises confirming that a prefix of a primary key value of at least one record of the records of the second table comprises a primary key value of the record of the first table.
32. The computer-readable medium of claim 25 , wherein the query condition establishes that the SQL query is valid for performing the non-buffering read operation when the SQL query comprises only one operation type following a first instance of the operation type.
33. The computer-readable medium of claim 32 , wherein the operation type comprises a SQL “LEFT OUTER JOIN” operation type.
34. The computer-readable medium of claim 25 , wherein evaluating the SQL query for the query condition comprises determining whether, after an instance of a “LEFT OUTER JOIN” operation type in the SQL query, no other variations of a SQL “JOIN” operation appear other than the “LEFT OUTER JOIN” operation type.
35. The computer-readable medium of claim 25 , wherein the query condition establishes that the SQL query is valid for performing the non-buffering read operation when a first join condition on an ancestor table is not met, a second join condition on a descendant table is also not met.
36. The computer-readable medium of claim 35 , wherein evaluating the SQL query for the query condition by determining that the at least one predicate comprises a conjunct of the form “X.A=Y.B,” where:
X is a first table;
A is a first column of the first table;
Y is a second table; and
B is a second column of the second table.