Parallelizing SQL user defined transformation functions
View Patent ↗Example embodiments relate to parallelizing structured query language (SQL) user defined transformation functions. In example embodiments, a subquery of a query is received from a query engine, where each of the subqueries is associated with a distinct magic number in a magic table. A user defined transformation function that includes local, role-based functionality may then be executed, where the magic number triggers parallel execution of the user defined transformation function. At this stage, the results of the user defined transformation function are sent to the query engine, where the query engine unions the results with other results that are obtained from the other database nodes.
1. A database node comprising:
a processor; and
a memory storing instructions executable to cause the processor to:
receive a subquery of a plurality of subqueries from a query engine, wherein each of the plurality of subqueries is associated with one of a plurality of distinct magic numbers in a magic table managed by the query engine, wherein the plurality of distinct magic numbers are used by the query engine to trigger the database node and other database nodes in a database system to execute the plurality of subqueries in parallel;
in response to a receipt of the subquery, determine whether the subquery includes a particular magic number associated with the database node;
in response to a determination that the subquery includes the particular magic number, execute the subquery and a user defined transformation function on the database node in parallel with the other database nodes in the database system, to obtain data from a local database of the database node; and
send results of the user defined transformation function to the query engine, wherein the query engine unions the results from the database node with other results that are obtained from the other database nodes.
2. The database node of claim 1 , wherein the subquery specifies the particular magic number in a partition by clause that references the magic table, and wherein the partition by clause identifies the subquery for execution in parallel on the database node.
3. The database node of claim 2 , wherein the magic table is a partitioned table that comprises a magic tuple of a plurality of magic tuples that is associated with the particular magic number.
4. The database node of claim 1 , wherein the database node is an external source of the query engine.
5. The database node of claim 4 , wherein the magic table simulates table partitioning metadata for a parallelizing execution of the subquery on the database node.
6. A method for parallelizing structured query language (SQL) user defined transformation functions, the method comprising:
processing, by a processor of a computing device, a query to obtain a plurality of subqueries, wherein each of the plurality of subqueries is associated with a magic number of a plurality of distinct magic numbers in a magic table, and wherein the plurality of distinct magic numbers in the magic table are used to trigger a plurality of database nodes in a database system to execute the plurality of subqueries in parallel;
inserting, by the processor, the plurality of distinct magic numbers into the plurality of subqueries associated with the plurality of distinct magic numbers;
sending, by the processor, each of the plurality of subqueries to one of the plurality of database nodes, wherein the plurality of database nodes are caused to detect the plurality of distinct magic numbers in the plurality of subqueries and, in response to detecting the plurality of distinct magic numbers, execute the plurality of subqueries in parallel to obtain data from local databases of the plurality of database nodes; and
in response to receiving a plurality of results from the plurality of database nodes for the plurality of subqueries, unioning, by the processor, the plurality of results to generate a dataset for the query.
7. The method of claim 6 , wherein the query comprises a partition by clause that references the magic table, and wherein the partition by clause identifies the query for execution in parallel on the plurality of database nodes.
8. The method of claim 7 , wherein the magic table comprises a magic tuple of a plurality of magic tuples that is associated with a database node of the plurality of database nodes.
9. The method of claim 8 , wherein the magic tuple is used by the database node to identify the magic number as an argument to a user-defined transformation function for obtaining one of the plurality of results.
10. The method of claim 6 , wherein at least one database node of the plurality of database nodes is an external source.
11. The method of claim 10 , wherein the magic table simulates table partitioning metadata for a parallelizing execution of one of the subqueries on the external source.
12. A non-transitory machine-readable storage medium storing instructions executable by a processor for parallelizing structured query language (SQL) user defined transformation functions, wherein the instructions are executable to cause the processor to:
process a query to obtain a plurality of subqueries, wherein each of the plurality of subqueries is associated with a magic number of a plurality of distinct magic numbers in a magic table, and wherein the plurality of distinct magic numbers in the magic table are used to trigger a plurality of database nodes in a database system to execute the plurality of subqueries in parallel;
insert the plurality of distinct magic numbers into the plurality of subqueries associated with the plurality of distinct magic numbers;
send each of the plurality of subqueries to one of the plurality of database nodes, wherein the plurality of database nodes are caused to detect the plurality of distinct magic numbers in the plurality of subqueries and, in response to detecting the plurality of distinct magic numbers, execute the plurality of subqueries in parallel using a local, role-based version of a user defined transformation function to obtain data from local databases of the plurality of database nodes; and
in response to receiving a plurality of results from the plurality of database nodes for the plurality of subqueries, union the plurality of results to generate a dataset for the query.
13. The non-transitory machine-readable storage medium of claim 12 , wherein the query comprises a partition by clause that references the magic table, and wherein the partition by clause identifies the query for execution in parallel on the plurality of database nodes.
14. The non-transitory machine-readable storage medium of claim 12 , wherein at least one database node of the plurality of database nodes is an external source.
15. The non-transitory machine-readable storage medium of claim 14 , wherein the magic table simulates table partitioning metadata for a parallelizing execution of one of the subqueries on the external source.
16. The non-transitory machine-readable storage medium of claim 12 , wherein the magic table is a partitioned table that comprises a plurality of magic tuples, and wherein each of the plurality of magic tuples is associated with one of the plurality of distinct magic numbers.