System and method for querying data sources

• US 10,346,397 B2
• Filed: 03/24/2016
• Issued: 07/09/2019
• Est. Priority Date: 03/26/2015
• Status: Active Grant

• Current Assignee: Corporation Caswil
• Original Assignee: Corporation Caswil
• Inventors: Khuong, Sophal, Savoie, Patrick
• Primary Examiner(s): Ly, Cheyne D
• Application Number:
  US15/561,367
• Publication Number:
  US 20180081938A1
• Time in Patent Office: 1,202 Days
• Field of Search: 707602, 707722, 707770
• CPC Class Codes: G06F 16/24535 of sub-queries or views
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24568 Data stream processing; Continuous queries
• CPC Class Codes: G06F 16/24578 using ranking
• CPC Class Codes: G06F 16/248 Presentation of query results
• CPC Class Codes: G06F 16/256 in federated or virtual databases

Abstract

There is described a method and system for obtaining results from one or more data sources without the need to extract, transform and load the data into a database prior to performing a query. The data may retain its original format and remain in its initial location, and selective access to the data is provided using dynamically generated query statements. Multiple data sources may be accessed concurrently, each data source having its own format for stored data. Query statements are generated to obtain data (query results) from the various data sources by defining data sources, target fields, and parameters to be used for a given query. The statements comprise predefined operations that are applied recursively in order to transform (and sometimes aggregate) the data from the data sources into query results. Query statements and query results may be saved for later use.

Claims

1. A method for querying data from data sources, the method comprising:

(a) receiving a first query request;

(b) generating a first query statement corresponding to the first query request, the first query statement identifying;

(i) at least one first data source comprising a second query statement;

(ii)at least one first target field;

(iii) first query parameters; and

(iv) a plurality of nested operations having associated attributes;

(c) executing the first query statement by;

(v) accessing the at least one first data source using the first query parameters;

(vi) retrieving data from a given field in the at least one first data source in accordance with at least one of the nested operations; and

(vii) transforming retrieved data into at least one target result in accordance with remaining ones of the nested operations; and

(d) generating a first query result by populating the at least one target field with the at least one target result, wherein executing the first query statement comprises executing the second query statement and generating a second query result, and wherein retrieving data from the given field comprises retrieving data from the given field of the second query result;

(e) receiving a third query request identifying at least one third data source, defining at least one third target field, and comprising third query parameters;

(f) determining if the at least one third data source, the at least one third target field, and the third query parameters match the at least one first data source, the at least one first target field, and the first query parameters, respectively;

(g) in case of no match, generating a third query statement in accordance with the third query request and executing the third query statement to return a third query result;

(h) in case of a match, retrieving the first query result and returning the first query result as the third query result;

(e) receiving a third query request identifying at least one third data source, defining at least one third target field, and comprising third query parameters;

(f) determining if the at least one third data source, the at least one third target field, and the third query parameters match the at least one first data source, the at least one first target field, and the first query parameters, respectively;

(g) in case of no match, generating a third query statement in accordance with the third query request and executing the third query statement to return a third query result;

(h) in case of a match, retrieving the first query result and returning the first query result as the third query result;

(viii) in case of no change, retrieving the first query result and returning the first query result as the third query result is performed; and

(ix) in case of a change, deleting the first query result, retrieving the first query statement, and executing the first query statement to return the third query result, deleting all query results associated with query statements in a chain of query statements that refer to the changed data source, regenerating query results for the query statements in the chain of query statements that refer to the changed data source, and retrieving stored query results for query statements that are not part of the chain of query statements that refer to the changed data source.

2. The method of claim 1, wherein the at least one first data source comprises at least one chain of query statements that each refer to another query statement until a final data source is referred to for each chain of query statements, and wherein executing the first query statement comprises executing all of the query statements in the chain and generating corresponding query results.

3. The method of claim 1, further comprising saving at least one of query statements and query results for reuse.

4. The method of claim 1, further comprising, in case of no match or a change in at least one parameter between the first query parameters and the third query parameters, regenerating query results for the query statements in the chain of query statements that use or result from the use of the changed at least one parameter, and retrieving stored query results for query statements that are not part of the chain of query statements that use or result from the use of the changed at least one parameter.

5. The method of claim 1, wherein generating the first query statement comprises using recursive data transformation steps to define the steps of (v) accessing, (vi) retrieving, and (vii) transforming, in order to obtain the at least one target result.

6. The method of claim 5, wherein generating the first query result further comprises displaying returned values for each one of the recursive data transformation steps.

7. The method of claim 5, wherein executing the first query statement further comprises filtering at least one of retrieved data and transformed data.

8. The method of claim 7, wherein filtering comprises automatically determining whether the filtering is applied to the retrieved data or the transformed data.

9. The method of claim 5, wherein generating the first query statement comprises defining an initial step of the recursive data transformation steps as a join function in order to join at least two data sources together, and defining the at least one first target field as at least one join target field.

10. The method of claim 9, wherein the join function uses fields from any one of the at least one first data source, ad hoc parameters, and hardcoded values.

11. The method of claim 9, wherein executing the first query statement comprises automatically determining an index of rows from the at least two data sources to match the at least one join target field.

12. The method of claim 11, wherein transforming retrieved data comprises using the index of rows for any non-join target fields.

13. The method of claim 5, wherein executing the first query statement further comprises applying a first grouping function to the at least one target result, and populating the at least one target field comprises using only fields from the first grouping function.

14. The method of claim 5, wherein executing the first query statement further comprises applying a second grouping function to the at least one first data source, and retrieving data comprises retrieving only from fields from the second grouping function.

15. A system for querying data from data sources, the system comprising:

a processing unit; and

a memory, communicatively coupled to the processing unit and comprising computer-readable program instructions, the program instructions executable by the processing unit for;

(a) receiving a first query request;

(b) generating a first query statement corresponding to the first query request, the first query statement identifying;

(i) at least one first data source comprising a second query statement;

(ii) at least one first target field;

(iii) first query parameters; and

(iv) a plurality of nested operations having associated attributes;

(c) executing the first query statement by;

(v) accessing the at least one first data source using the first query parameters;

(vi) retrieving data from a given field in the at least one first data source in accordance with at least one of the nested operations; and

(vii) transforming retrieved data into at least one target result in accordance with remaining ones of the nested operations; and

(d) generating a first query result by populating the at least one target field with the at least one target result, wherein executing the first query statement comprises executing the second query statement and generating a second query result, and wherein retrieving data from the given field comprises retrieving data from the given field of the second query result;

(e) receiving a third query request identifying at least one third data source, defining at least one third target field, and comprising third query parameters;

(f) determining if the at least one third data source, the at least one third target field, and the third query parameters match the at least one first data source, the at least one first target field, and the first query parameters, respectively;

(g) in case of no match, generating a third query statement in accordance with the third query request and executing the third query statement to return a third query result;

(h) in case of a match, retrieving the first query result and returning the first query result as the third query result;

(viii) in case of no change, retrieving the first query result and returning the first query result as the third query result is performed; and

(ix) in case of a change, deleting the first query result, retrieving the first query statement, and executing the first query statement to return the third query result, deleting all query results associated with query statements in a chain of query statements that refer to the changed data source, regenerating query results for the query statements in the chain of query statements that refer to the changed data source, and retrieving stored query results for query statements that are not part of the chain of query statements that refer to the changed data source.

16. The system of claim 15, wherein the at least one first data source comprises at least one chain of query statements that each refer to another query statement until a final data source is referred to for each chain of query statements, and wherein executing the first query statement comprises executing all of the query statements in the chain and generating corresponding query results.

17. The system of claim 15, further comprising saving at least one of query statements and query results for reuse.

18. The system of claim 15, further comprising, in case of no match or a change in at least one parameter between the first query parameters and the third query parameters, regenerating query results for the query statements in the chain of query statements that use or result from the use of the changed at least one parameter, and retrieving stored query results for query statements that are not part of the chain of query statements that use or result from the use of the changed at least one parameter.

19. The system of claim 15, wherein generating the first query statement comprises using recursive data transformation steps to define the steps of (v) accessing, (vi) retrieving, and (vii) transforming, in order to obtain the at least one target result.

20. The system of claim 19, wherein generating the first query result further comprises displaying returned values for each one of the recursive data transformation steps.

21. The system of claim 19, wherein executing the first query statement further comprises filtering at least one of retrieved data and transformed data.

22. The system of claim 21, wherein filtering comprises automatically determining whether the filtering is applied to the retrieved data or the transformed data.

23. The system of claim 19, wherein generating the first query statement comprises defining an initial step of the recursive data transformation steps as a join function in order to join at least two data sources together, and defining the at least one first target field as at least one join target field.

24. The system of claim 23, wherein the join function uses fields from any one of the at least one first data source, ad hoc parameters, and hardcoded values.

25. The system of claim 23, wherein executing the first query statement comprises automatically determining an index of rows from the at least two data sources to match the at least one join target field.

26. The system of claim 25, wherein transforming retrieved data comprises using the index of rows for any non-join target fields.

27. The system of claim 19, wherein executing the first query statement further comprises applying a first grouping function to the at least one target result, and populating the at least one target field comprises using only fields from the first grouping function.

28. The system of claim 19, wherein executing the first query statement further comprises applying a second grouping function to the at least one first data source, and retrieving data comprises retrieving only from fields from the second grouping function.