Processing queries containing a union-type operation

• US 10,437,819 B2
• Filed: 06/22/2015
• Issued: 10/08/2019
• Est. Priority Date: 11/14/2014
• Status: Active Grant

• Current Assignee: Ab Initio Technology LLC (Ab Initio Software Corporation)
• Original Assignee: Ab Initio Technology LLC (Ab Initio Software Corporation)
• Inventors: Schechter, Ian, Lavrentyev, Alexander
• Primary Examiner(s): Yen, Syling
• Application Number:
  US14/746,188
• Publication Number:
  US 20160140166A1
• Time in Patent Office: 1,569 Days
• Field of Search: 707758, 707759
• CPC Class Codes: G06F 16/244 Grouping and aggregation
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: G06F 16/24544 Join order optimisation
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 16/9024 Graphs; Linked lists G06F16/9027 takes precedence
• CPC Class Codes: G06F 8/30 Creation or generation of source code

Abstract

Among other things, a method of generating a computer program based on an SQL query includes receiving a SQL query, including a reference to a first data set stored at a first data source, and including a reference to a second data set stored at a second data source different from the first data source, determining that the SQL query includes two or more commands, the commands including a first union-type operation, and a first aggregation operation, and determining that the SQL query describes that the first union-type operation shall be applied to at least a portion of data from the first data set, and applied to at least a portion of data from the second data set, determining that the SQL query describes that the first aggregation operation shall be applied to data resulting from the first union-type operation, and generating the computer program.

Claims

1. A computer-implemented method of generating a computer program based on an SQL query, including:

receiving a SQL query, including a reference to a first data set stored at a first data source, and including a reference to a second data set stored at a second data source different from the first data source;

determining that the SQL query includes two or more commands, the commands including a first union-type operation, and a first aggregation operation, and

determining that the SQL query describes that the first union-type operation shall be applied to at least a portion of data from the first data set, and applied to at least a portion of data from the second data set;

determining that the SQL query describes that the first aggregation operation shall be applied to data resulting from the first union-type operation;

generating, on a first system, a computer program based on the SQL query, the generating including

generating at least a first module of the computer program representing a second system, the first module configured for;

transmitting instructions that direct a second system to perform a second aggregation operation on a portion of data from the first data set, and

receiving, from the second system, a particular data set that is a summary of the portion of data from the first data set; and

generating at least a second module of the computer program configured for;

receiving output originating from the first module, and

performing a second union-type operation on the particular data set and a summary of a portion of data from the second data set.

2. The method of claim 1 in which the first system includes a graph-based processing system, and the second system includes a relational database management system.

3. The method of claim 1 in which the computer program is further configured for:

transmitting instructions that direct a third system to perform a second aggregation operation on a portion of data from the second data set; and

receiving, from the third system, a particular data set that is the summary of the portion of data from the second data set.

4. The method of claim 1 in which the computer program includes:

a first component representing the union-type operation of the SQL query that is applied to the data sets that are the summary of the portion of data from the first data set and the summary of the portion of data from the second data set, where the summaries each contain fewer records than their respective corresponding portions of data, and

a second component representing the first data set, where the second component, when executed, causes the instructions to be transmitted.

5. The method of claim 4, the computer program including

a third component representing the second data set, and a fourth component representing a third aggregation operation, where output of the third component flows to the fourth component, and output of the fourth component flows to the first component.

6. The method of claim 4 in which the output of the second component includes the particular data set that is the summary of the portion of data from the first data set.

7. The method of claim 4 in which the component representing the first data source transmits the instructions, wherein the instructions are carried out before the first system causes data to be transmitted to a system executing the computer program.

8. The method of claim 1 in which the instructions include a SQL statement that includes a command corresponding to the second aggregation operation.

9. The method of claim 1 in which generating the computer program based on the SQL query includes

generating a query plan based on the SQL query;

adding, to the query plan, the second aggregation operation that is applied to data from the first data source;

determining that the first system includes functionality for carrying out the second aggregation operation;

appending, to a portion of the query plan representing the first data set, instructions to carry out the second aggregation operation; and

generating the computer program based on the query plan.

10. The method of claim 1 in which the first aggregation operation is a minimum, maximum, sum, or count operation, and the second aggregation operation is of the same type of aggregation operation as the first aggregation operation.

11. The method of claim 1 in which the first aggregation operation is an average operation, and the second aggregation operation is a sum operation or a count operation.

12. The method of claim 1, including determining the second aggregation operation based on first aggregation operation.

13. The method of claim 12 in which determining the second aggregation operation based on first aggregation operation includes

determining a sequence of operations that, when carried out, produces output equivalent to output of the first aggregation operation, and

selecting the second aggregation operation from the sequence of operations.

14. The method of claim 1 in which the first union-type operation is of the same type to the second union-type operation.

15. The method of claim 1, wherein generating the first module includes configuring parameters of the first module, the parameters being values that affect the behavior of the modules of the generated computer program.

16. A system for generating a computer program based on an SQL query, including:

one or more processors configured for;

receiving a SQL query, including a reference to a first data set stored at a first data source, and including a reference to a second data set stored at a second data source different from the first data source;

determining that the SQL query includes two or more commands, the commands including a first union-type operation, and a first aggregation operation, and

determining that the SQL query describes that the first union-type operation shall be applied to at least a portion of data from the first data set, and applied to at least a portion of data from the second data set;

determining that the SQL query describes that the first aggregation operation shall be applied to data resulting from the first union-type operation;

generating, on a first system, a computer program based on the SQL query, the generating including

generating at least a first module of the computer program representing a second system, the first module configured for;

transmitting instructions that direct a second system to perform a second aggregation operation on a portion of data from the first data set, and

receiving, from the second system, a particular data set that is a summary of the portion of data from the first data set; and

generating at least a second module of the computer program configured for;

receiving output originating from the first module, and

performing a second union-type operation on the particular data set and a summary of a portion of data from the second data set.

17. The system of claim 16 in which the computer program is further configured for:

transmitting instructions that direct a third system to perform a second aggregation operation on a portion of data from the second data set; and

receiving, from the third system, a particular data set that is the summary of the portion of data from the second data set.

18. The system of claim 16 in which the computer program includes:

a first component representing the union-type operation of the SQL query that is applied to the data sets that are the summary of the portion of data from the first data set and the summary of the portion of data from the second data set, where the summaries each contain fewer records than their respective corresponding portions of data, and

a second component representing the first data set, where the second component, when executed, causes the instructions to be transmitted.

19. The system of claim 18, in which the computer program includes

a third component representing the second data set, and a fourth component representing a third aggregation operation, where output of the third component flows to the fourth component, and output of the fourth component flows to the first component.

20. The system of claim 18 in which the output of the second component includes the particular data set that is the summary of the portion of data from the first data set.

21. The system of claim 18 in which the component representing the first data source transmits the instructions, wherein the instructions are carried out before the first system causes data to be transmitted to a system executing the computer program.

22. The system of claim 16 in which the instructions include a SQL statement that includes a command corresponding to the second aggregation operation.

23. The system of claim 16 in which generating the computer program based on the SQL query includes

generating a query plan based on the SQL query;

adding, to the query plan, the second aggregation operation that is applied to data from the first data source;

determining that the first system includes functionality for carrying out the second aggregation operation;

appending, to a portion of the query plan representing the first data set, instructions to carry out the second aggregation operation; and

generating the computer program based on the query plan.

24. The system of claim 16 in which the first aggregation operation is a minimum, maximum, sum, or count operation, and the second aggregation operation is of the same type of aggregation operation as the first aggregation operation.

25. The system of claim 16 in which the first aggregation operation is an average operation, and the second aggregation operation is a sum operation or a count operation.

26. The system of claim 16, the one or more processors configured for determining the second aggregation operation based on first aggregation operation.

27. The system of claim 26 in which determining the second aggregation operation based on first aggregation operation includes

determining a sequence of operations that, when carried out, produces output equivalent to output of the first aggregation operation, and

selecting the second aggregation operation from the sequence of operations.

28. The system of claim 16 in which the first union-type operation is of the same type to the second union-type operation.

29. The system of claim 16, in which generating the first module includes configuring parameters of the first module, the parameters being values that affect the behavior of the modules of the generated computer program.

30. A non-transitory computer-readable medium storing executable code that, when executed, causes a computer system to carry out operations for generating a computer program based on an SQL query, including:

receiving a SQL query, including a reference to a first data set stored at a first data source, and including a reference to a second data set stored at a second data source different from the first data source;

determining that the SQL query includes two or more commands, the commands including a first union-type operation, and a first aggregation operation, and

determining that the SQL query describes that the first union-type operation shall be applied to at least a portion of data from the first data set, and applied to at least a portion of data from the second data set;

determining that the SQL query describes that the first aggregation operation shall be applied to data resulting from the first union-type operation;

generating, on a first system, a computer program based on the SQL query, the generating including

generating at least a first module of the computer program representing a second system, the first module configured for;

transmitting instructions that direct a second system to perform a second aggregation operation on a portion of data from the first data set, and

receiving, from the second system, a particular data set that is a summary of the portion of data from the first data set; and

generating at least a second module of the computer program configured for;

receiving output originating from the first module, and

performing a second union-type operation on the particular data set and a summary of a portion of data from the second data set.

31. The non-transitory computer-readable medium of claim 30 in which the computer program is further configured for:

transmitting instructions that direct a third system to perform a second aggregation operation on a portion of data from the second data set; and

receiving, from the third system, a particular data set that is the summary of the portion of data from the second data set.

32. The non-transitory computer-readable medium of claim 30 in which the computer program includes:

a first component representing the union-type operation of the SQL query that is applied to the data sets that are the summary of the portion of data from the first data set and the summary of the portion of data from the second data set, where the summaries each contain fewer records than their respective corresponding portions of data, and

a second component representing the first data set, where the second component, when executed, causes the instructions to be transmitted.

33. The non-transitory computer-readable medium of claim 32 in which the computer program includes

a third component representing the second data set, and a fourth component representing a third aggregation operation, where output of the third component flows to the fourth component, and output of the fourth component flows to the first component.

34. The non-transitory computer-readable medium of claim 32 in which the output of the second component includes the particular data set that is the summary of the portion of data from the first data set.

35. The non-transitory computer-readable medium of claim 32 in which the component representing the first data source transmits the instructions, wherein the instructions are carried out before the first system causes data to be transmitted to a system executing the computer program.

36. The non-transitory computer-readable medium of claim 30 in which the instructions include a SQL statement that includes a command corresponding to the second aggregation operation.

37. The non-transitory computer-readable medium of claim 30 in which generating the computer program based on the SQL query includes

generating a query plan based on the SQL query;

adding, to the query plan, the second aggregation operation that is applied to data from the first data source;

determining that the first system includes functionality for carrying out the second aggregation operation;

appending, to a portion of the query plan representing the first data set, instructions to carry out the second aggregation operation; and

generating the computer program based on the query plan.

38. The non-transitory computer-readable medium of claim 30 in which the first aggregation operation is a minimum, maximum, sum, or count operation, and the second aggregation operation is of the same type of aggregation operation as the first aggregation operation.

39. The non-transitory computer-readable medium of claim 30 in which the first aggregation operation is an average operation, and the second aggregation operation is a sum operation or a count operation.

40. The non-transitory computer-readable medium of claim 30, the operations including determining the second aggregation operation based on first aggregation operation.

41. The non-transitory computer-readable medium of claim 40 in which determining the second aggregation operation based on first aggregation operation includes

determining a sequence of operations that, when carried out, produces output equivalent to output of the first aggregation operation, and

selecting the second aggregation operation from the sequence of operations.

42. The non-transitory computer-readable medium of claim 30 in which the first union-type operation is of the same type to the second union-type operation.

43. The non-transitory computer-readable medium of claim 30, in which generating the first module includes configuring parameters of the first module, the parameters being values that affect the behavior of the modules of the generated computer program.

44. A system for generating a computer program based on an SQL query, including:

means for receiving a SQL query, including a reference to a first data set stored at a first data source, and including a reference to a second data set stored at a second data source different from the first data source;

means for determining that the SQL query includes two or more commands, the commands including a first union-type operation, and a first aggregation operation, and

means for determining that the SQL query describes that the first union-type operation shall be applied to at least a portion of data from the first data set, and applied to at least a portion of data from the second data set;

means for determining that the SQL query describes that the first aggregation operation shall be applied to data resulting from the first union-type operation;

means for generating, on a first system, a computer program based on the SQL query, the generating including

generating at least a first module of the computer program representing a second system, the first module configured for;

transmitting instructions that direct a second system to perform a second aggregation operation on a portion of data from the first data set, and

receiving, from the second system, a particular data set that is a summary of the portion of data from the first data set; and

generating at least a second module of the computer program configured for;

receiving output originating from the first module, and

performing a second union-type operation on the particular data set and a summary of a portion of data from the second data set.