Querying a data source on a network

• US 10,417,281 B2
• Filed: 06/26/2015
• Issued: 09/17/2019
• Est. Priority Date: 02/18/2015
• 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, Allin, Glenn John
• Primary Examiner(s): Le, Hung D
• Application Number:
  US14/752,094
• Publication Number:
  US 20160239582A1
• Time in Patent Office: 1,544 Days
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: G06F 16/24524 Access plan code generation and invalidation; Reuse of access plans
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/25 Integrating or interfacing systems involving database management systems
• CPC Class Codes: G06F 16/9024 Graphs; Linked lists G06F16/9027 takes precedence

Abstract

Among other things, we describe a computer-implemented method, performed by a data processing system, of executing a computer program based on a query that is expressed in accordance with a query language applicable to a relational database, the computer program executed based at least in part on data stored in a tangible, non-transitory computer-readable medium, the executing including receiving a SQL query, where the SQL query includes an identifier associated with a resource that is external to the data processing system, generating a computer program based on the SQL query, and executing the computer program, causing transmitting one or more instructions to the resource, the instructions defining operations other than operations of the SQL query, and receiving data from the resource in response to the instructions.

Claims

1. A computer-implemented method, performed by a data processing system, of executing a computer program based on a query that is expressed in accordance with a query language applicable to a relational database, the computer program executed based at least in part on data stored in a tangible, non-transitory computer-readable medium, the executing including:

receiving a SQL query, where the SQL query includes an identifier associated with a resource that is external to the data processing system;

generating the computer program based on the SQL query, including;

identifying a catalog including at least one entry associated with the resource, the at least one entry representing configuration data usable to configure a computer program to transmit instructions to the resource for the purpose of receiving data,

identifying an executing system other than a system managing a relational database,

generating a request to a query planner based on the SQL query,

providing the request to the query planner,

receiving a query plan generated by the query planner based on the request, the query plan including a description of one or more steps to be performed by a system managing a relational database, and

generating the computer program based on the query plan, wherein at least a portion of the generated computer program is configured to communicate with the resource based on the at least one entry of the catalog;

receiving, from the identified catalog, the configuration data; and

executing the computer program on the identified executing system, based on the configuration data, the execution of the computer program causing;

transmitting one or more instructions to the resource, the instructions defining operations other than operations of the SQL query, and

receiving data from the resource in response to the instructions.

2. The method of claim 1 in which the computer program includes executable instructions that define a manner in which the resource is accessed, where the executable instructions operate based on the configuration data provided to the portion of the computer program.

3. The method of claim 1 in which the configuration data is specified in a catalog that can be updated based on a change to a data format used by the resource.

4. The method of claim 1 including

generating parameter values based on the configuration data;

and

providing the parameter values to a portion of the computer program, the portion being capable of communicating with the resource;

the execution of the computer program being based on the parameter values.

5. The method of claim 4 in which generating parameter values based on the configuration data includes executing a parameter generator which generates a parameter file in a format readable by the portion of the computer program, and

in which providing the parameter values to the portion of the computer program includes making the parameter file available to the portion of the computer program.

6. The method of claim 1 in which the SQL query includes a SELECT statement that includes an argument, where at least a portion of the argument corresponds to the identifier associated with the resource.

7. The method of claim 1 in which the computer program includes components representing operations of the SQL query.

8. The method of claim 1 in which the computer program is a dataflow graph and a subgraph of the dataflow graph performs the transmitting of the one or more instructions to the resource.

9. The method of claim 1 in which the resource is accessed using an application programming interface (API) exposed by the resource.

10. The method of claim 9 including

causing functions of the API to be executed in response to receiving the SQL query,

the functions of the API being executable at the resource, and the instructions transmitted to the resource causing the functions of the API to be executed.

11. The method of claim 1 including formatting the data received from the external resource in the form of a database table.

12. The method of claim 1 in which the resource is not a relational database management system.

13. The method of claim 1 in which the instructions are transmitted to a facility of the resource that does not return results in response to a SQL query.

14. The method of claim 1 in which the data received from the resource in response to the instructions includes data specified by the SQL query.

15. The method of claim 1 including identifying records and fields in the data received from the resource in response to the instructions, the records and fields identified based on a record format associated with the resource that is external to the data processing system.

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

generating a data structure instantiating a dataflow graph that includes;

a first node that represents at least one operation to be executed,

the first node associated with information usable by an executing system to invoke executable program code to perform the operation,

the first node associated with information usable by an executing system to make data available to the program code,

the operation chosen based on a step described by the query plan, and

at least one link that represents output data of an operation of the first node being provided as input data to an operation of a second node of the dataflow graph.

17. A data processing system including a hardware processor and memory, the data processing system capable of executing a computer program based on a query that is expressed in accordance with a query language applicable to a relational database, the computer program executed based at least in part on data stored in a tangible, non-transitory computer-readable medium, the data processing system configured to perform operations including:

receiving a SQL query, where the SQL query includes an identifier associated with a resource that is external to the data processing system;

generating the computer program based on the SQL query, including;

identifying a catalog including at least one entry associated with the resource, the at least one entry representing configuration data usable to configure a computer program to transmit instructions to the resource for the purpose of receiving data,

identifying an executing system other than a system managing a relational database,

generating a request to a query planner based on the SQL query,

providing the request to the query planner,

receiving a query plan generated by the query planner based on the request, the query plan including a description of one or more steps to be performed by a system managing a relational database, and

generating the computer program based on the query plan, wherein at least a portion of the generated computer program is configured to communicate with the resource based on the at least one entry of the catalog;

receiving, from the identified catalog, the configuration data; and

executing the computer program on the identified executing system, based on the configuration data, the execution of the computer program causing;

transmitting one or more instructions to the resource, the instructions defining operations other than operations of the SQL query, and

receiving data from the resource in response to the instructions.

18. The system of claim 17 in which the computer program includes executable instructions that define a manner in which the resource is accessed, where the executable instructions operate based on the configuration data provided to the portion of the computer program.

19. The system of claim 17 in which the configuration data is specified in a catalog that can be updated based on a change to a data format used by the resource.

20. The system of claim 17, the operations including

generating parameter values based on the configuration data;

and

providing the parameter values to a portion of the computer program, the portion being capable of communicating with the resource;

the execution of the computer program being based on the parameter values.

21. The system of claim 20 in which generating parameter values based on the configuration data includes executing a parameter generator which generates a parameter file in a format readable by the portion of the computer program, and

in which providing the parameter values to the portion of the computer program includes making the parameter file available to the portion of the computer program.

22. The system of claim 17 in which the SQL query includes a SELECT statement that includes an argument, where at least a portion of the argument corresponds to the identifier associated with the resource.

23. The system of claim 17 in which the computer program includes components representing operations of the SQL query.

24. The system of claim 17 in which the computer program is a dataflow graph and a subgraph of the dataflow graph performs the transmitting of the one or more instructions to the resource.

25. The system of claim 17 in which the resource is accessed using an application programming interface (API) exposed by the resource.

26. The system of claim 25, the operations including

causing functions of the API to be executed in response to receiving the SQL query,

the functions of the API being executable at the resource, and the instructions transmitted to the resource causing the functions of the API to be executed.

27. The system of claim 17 including formatting the data received from the external resource in the form of a database table.

28. The system of claim 17 in which the resource is not a relational database management system.

29. The system of claim 17 in which the instructions are transmitted to a facility of the resource that does not return results in response to a SQL query.

30. The system of claim 17 in which the data received from the resource in response to the instructions includes data specified by the SQL query.

31. The system of claim 17 including identifying records and fields in the data received from the resource in response to the instructions, the records and fields identified based on a record format associated with the resource that is external to the data processing system.

32. A non-transitory computer readable storage device storing instructions that enable a data processing system to execute a computer program based on a query that is expressed in accordance with a query language applicable to a relational database, the computer program executed based at least in part on data stored in a tangible, non-transitory computer-readable medium, the instructions causing the data processing system to perform operations including:

receiving a SQL query, where the SQL query includes an identifier associated with a resource that is external to the data processing system;

generating the computer program based on the SQL query, including;

identifying a catalog including at least one entry associated with the resource, the at least one entry representing configuration data usable to configure a computer program to transmit instructions to the resource for the purpose of receiving data,

identifying an executing system other than a system managing a relational database,

generating a request to a query planner based on the SQL query,

providing the request to the query planner,

receiving a query plan generated by the query planner based on the request, the query plan including a description of one or more steps to be performed by a system managing a relational database, and

generating the computer program based on the query plan, wherein at least a portion of the generated computer program is configured to communicate with the resource based on the at least one entry of the catalog;

receiving, from the identified catalog, the configuration data; and

executing the computer program on the identified executing system, based on the configuration data, the execution of the computer program causing;

transmitting one or more instructions to the resource, the instructions defining operations other than operations of the SQL query, and

receiving data from the resource in response to the instructions.

33. The computer readable storage device of claim 32 in which the computer program includes executable instructions that define a manner in which the resource is accessed, where the executable instructions operate based on the configuration data provided to the portion of the computer program.

34. The computer readable storage device of claim 32 in which the configuration data is specified in a catalog that can be updated based on a change to a data format used by the resource.

35. The computer readable storage device of claim 32, the operations including

generating parameter values based on the configuration data;

and

providing the parameter values to a portion of the computer program, the portion being capable of communicating with the resource;

the execution of the computer program being based on the parameter values.

36. The computer readable storage device of claim 35 in which generating parameter values based on the configuration data includes executing a parameter generator which generates a parameter file in a format readable by the portion of the computer program, and

in which providing the parameter values to the portion of the computer program includes making the parameter file available to the portion of the computer program.

37. The computer readable storage device of claim 32 in which the SQL query includes a SELECT statement that includes an argument, where at least a portion of the argument corresponds to the identifier associated with the resource.

38. The computer readable storage device of claim 32 in which the computer program includes components representing operations of the SQL query.

39. The computer readable storage device of claim 32 in which the computer program is a dataflow graph and a subgraph of the dataflow graph performs the transmitting of the one or more instructions to the resource.

40. The computer readable storage device of claim 32 in which the resource is accessed using an application programming interface (API) exposed by the resource.

41. The computer readable storage device of claim 40, the operations including

causing functions of the API to be executed in response to receiving the SQL query,

the functions of the API being executable at the resource, and the instructions transmitted to the resource causing the functions of the API to be executed.

42. The computer readable storage device of claim 32 including formatting the data received from the external resource in the form of a database table.

43. The computer readable storage device of claim 32 in which the resource is not a relational database management system.

44. The computer readable storage device of claim 32 in which the instructions are transmitted to a facility of the resource that does not return results in response to a SQL query.

45. The computer readable storage device of claim 32 in which the data received from the resource in response to the instructions includes data specified by the SQL query.

46. The computer readable storage device of claim 32 including identifying records and fields in the data received from the resource in response to the instructions, the records and fields identified based on a record format associated with the resource that is external to the data processing system.

47. A data processing system capable of executing a computer program based on a query that is expressed in accordance with a query language applicable to a relational database, the computer program executed based at least in part on data stored in a tangible, non-transitory computer-readable medium, the data processing system including:

means for receiving a SQL query, where the SQL query includes an identifier associated with a resource that is external to the data processing system;

means for generating the computer program based on the SQL query, including;

identifying a catalog including at least one entry associated with the resource, the at least one entry representing configuration data usable to configure a computer program to transmit instructions to the resource for the purpose of receiving data,

identifying an executing system other than a system managing a relational database,

generating a request to a query planner based on the SQL query,

providing the request to the query planner,

receiving a query plan generated by the query planner based on the request, the query plan including a description of one or more steps to be performed by a system managing a relational database, and

generating the computer program based on the query plan, wherein at least a portion of the generated computer program is configured to communicate with the resource based on the at least one entry of the catalog;

receiving, from the identified catalog, the configuration data; and

means for executing the computer program on the identified executing system, based on the configuration data, the execution of the computer program causing;

transmitting one or more instructions to the resource, the instructions defining operations other than operations of the SQL query, and

receiving data from the resource in response to the instructions.