Query-level access to external petabyte-scale distributed file systems

• US 10,108,682 B2
• Filed: 03/06/2017
• Issued: 10/23/2018
• Est. Priority Date: 08/01/2012
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Hanckel, Robert, Swart, Garret
• Primary Examiner(s): Vy, Hung T
• Application Number:
  US15/451,134
• Publication Number:
  US 20170242901A1
• Time in Patent Office: 596 Days
• Field of Search: 707770
• CPC Class Codes: G06F 16/168 Details of user interfaces specifically adapted to file systems, e.g. browsing and visualisation, 2d or 3d GUIs query results presentation G06F16/156
• CPC Class Codes: G06F 16/182 Distributed file systems
• CPC Class Codes: G06F 16/24532 of parallel queries
• CPC Class Codes: G06F 16/24568 Data stream processing; Continuous queries
• CPC Class Codes: G06F 16/25 Integrating or interfacing systems involving database management systems
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor

Abstract

A method and system to creating query-level access to an external distributed file system by identifying a location of one or more external data residing on the external distributed file system, creating a query specifying an external table within a database engine having one or more location files, wherein the location files identify metadata operations for accessing and processing the one or more external data, defining metadata operations for accessing and processing the one or more external data, wherein the processing that produces one or more result files occurs at the external distributed file system, and executing the query at the database engine to create the external table, the external table comprising the one or more location files identifying the metadata directives for processing query-level requests on the one or more external data stored on the external distributed file system.

Claims

1. A computer-implemented method for creating query-level access by a database engine to an external distributed file system, the method comprising:

identifying a location of external data residing on the external distributed file system;

creating a query specifying an external table and metadata within the database engine, the external table associated with one or more location files that specify code to execute operational directives against the external data in the external distributed file system, the operational directives producing one or more results at the external distributed system; and

defining the metadata comprising;

generating the external table via an external table declarative statement, and

specifying a location of the one or more location files for the external table, wherein the code specified by the one or more location files, when executed, streams data from the one or more results to a user application without copying the data to table files on the database engine.

2. The method of claim 1, wherein the one or more location files comprise a publishing tool, the publishing tool facilitating load balancing at a file level granularity by analyzing the one or more results.

3. The method of claim 1, wherein an operational directive is a codec specification.

4. The method of claim 1, wherein the one or more results are stored as one or more result files at the external distributed file system.

5. The method of claim 1, wherein the one or more location files are updated and redirected to new external data without having to redefine the external table.

6. The method of claim 1, wherein an access driver reads the external data.

7. The method of claim 1, wherein the external table declarative statement comprises a create table, organization external, and location clauses.

8. A computer system for creating query-level access by a database engine to an external distributed file system, the system comprising:

A computer processor to execute a set of program code instructions; and

a memory to hold the program code instructions, in which the program code instructions comprises program code to perform;

identifying a location of external data residing on the external distributed file system;

creating a query specifying an external table and metadata within the database engine, the external table associated with one or more location files that specify code to execute operational directives against the external data in the external distributed file system, the operational directives producing one or more results at the external distributed system; and

defining the metadata comprising;

generating the external table via an external table declarative statement, and

specifying a location of the one or more location files for the external table, wherein the code specified by the one or more location files, when executed, stream data from the one or more results to a user application without copying the data to table files on the database engine.

9. The computer system of claim 8, wherein the one or more location files comprise a publishing tool, the publishing tool facilitating load balancing at a file level granularity by analyzing the one or more results.

10. The computer system of claim 8, wherein an operational directive is a codec specification.

11. The computer system of claim 8, wherein the one or more results are stored as one or more result files at the external distributed file system.

12. The computer system of claim 8, wherein the one or more location files are updated and redirected to new external data without having to redefine the external table.

13. The computer system of claim 8, wherein an access driver reads the external data.

14. The computer system of claim 8, wherein the external table declarative statement comprises a create table, organization external, and location clauses.

15. A computer program product embodied in a non-transitory computer readable medium, the computer readable medium having stored thereon a sequence of instructions which, when executed by a processor causes the processor to execute a process to create a query-level access by a database engine to an external distributed file system, the process comprising:

identifying a location of external data residing on the external distributed file system;

creating a query specifying an external table and metadata within the database engine, the external table associated with one or more location files that specify code to execute operational directives against the external data in the external distributed file system, the operational directives producing one or more results at the external distributed system; and

defining the metadata comprising;

generating the external table via an external table declarative statement, and

specifying a location of the one or more location files for the external table, wherein the code specified by the one or more location files, when executed, stream data from the one or more results to a user application without copying the data to table files on the database engine.

16. The computer program product of claim 15, wherein the one or more location files comprise a publishing tool, the publishing tool facilitating load balancing at a file level granularity by analyzing the one or more results.

17. The computer program product of claim 15, wherein an operational directive is a codec specification.

18. The computer program product of claim 15, wherein the one or more results are stored as one or more result files at the external distributed file system.

19. The computer program product of claim 15, wherein the one or more location files are updated and redirected to new external data without having to redefine the external table.

20. The computer program product of claim 15, wherein an access driver reads the external data.

21. The computer program product of claim 15, wherein the external table declarative statement comprises a create table, organization external, and location clauses.