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

• US 9,589,036 B2
• Filed: 08/01/2012
• Issued: 03/07/2017
• 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): Waldron, Scott A
• Assistant Examiner(s): Wang, Dongming
• Application Number:
  US13/564,204
• Publication Number:
  US 20140040310A1
• Time in Patent Office: 1,679 Days
• Field of Search: 707/770
• US Class Current: 1/1
• 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 system to implement query-level access by a database engine to an external distributed file system by identifying a results file location of one or more results files on the external distributed file system, and storing the results file locations in external table files on the database engine for subsequent use during retrieval of data from the results files. The database engine serves to process queries where the query specifies the external table (which in turn references locations of the results files). Execution of the query streams data from the external distributed file system into the database engine. The data from the external distributed file system is not stored in the external table files on the database engine; rather, the external table files specify a location of code or operational directives which, when executed, streams results from the external distributed file system to at least one parallel query engine.

Claims

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

identifying a query against external data, the external data being queried as a table;

identifying one or more external locations for the external data, wherein the one or more external locations are on the external distributed file system;

identifying metadata that specifies code to execute operational directives to operate against the external data in the external distributed file system;

operating against the external data in external files that are in the external distributed file system, wherein one or more results files are generated from operating against the external data; and

retrieving data from the one or more results files from the external distributed file system without copying the data from the one or more results files to one or more table files on the database engine by;

streaming the data to one or more parallel query engines, wherein the one or more parallel query engines process the data, and

outputting the data from the one or more parallel query engines to a user application.

2. The method of claim 1, wherein the query is an SQL query.

3. The method of claim 1, wherein the database engine comprises:

one or more parallel query engines;

an external table engine; and

one or more storage disks.

4. The method of claim 1, wherein the metadata specifies a preprocessor codec.

5. The method of claim 4, wherein the streaming comprises executing the preprocessor codec over at least a portion of the data from the one or more results files resulting from the query.

6. The method of claim 1, wherein the one or more external locations is a pathname to one or more files in the external distributed file system.

7. The method of claim 1, further comprising running a map-reduce process on the external distributed file system to populate data into the one or more results files.

8. A computer system for query-level access by a database engine to an external distributed file 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;

identify a query against external data, the external data being queried as a table;

identify one or more external locations for the external data, wherein the one or more external locations are on the external distributed file system;

identify metadata that specifies code to execute operational directives to operate against the external data in the external distributed file system;

operate against the external data in external files that are in the external distributed file system, wherein one or more results files are generated from operating against the external data; and

retrieve data from the one or more results files from the external distributed file system without copying the data from the one or more results files to one or more table files on the database engine by;

streaming the data to one or more parallel query engines, wherein the one or more parallel query engines process the data, and

outputting the data from the one or more parallel query engines to a user application.

9. The computer system of claim 8, wherein the query is an SQL query.

10. The computer system of claim 8, wherein the database engine comprises:

one or more parallel query engines;

an external table engine; and

one or more storage disks.

11. The computer system of claim 8, wherein the metadata specifies a preprocessor codec.

12. The computer system of claim 11, wherein the streaming comprises executing the preprocessor codec over at least a portion of the data from the one or more results files resulting from the query.

13. The computer system of claim 8, wherein the one or more external locations is a pathname to a file in the external distributed file system.

14. The computer system of claim 8, in which the program code instructions further comprises program code to run a map-reduce process on an external database system to populate data into the one or more results files.

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 perform query-level access by a database engine to an external distributed file system, the process comprising:

identifying a query against external data, the external data being queried as a table;

identifying one or more external locations for the external data, wherein the one or more external locations are on the external distributed file system;

identifying metadata that specifies code to execute operational directives to operate against the external data in the external distributed file system;

operating against the external data in external files that are in the external distributed file system, wherein one or more results files are generated from operating against the external data; and

retrieving data from the one or more results files from the external distributed file system without copying the data from the one or more results files to one or more table files on the database engine by;

streaming the data to one or more parallel query engines, wherein the one or more parallel query engines process the data, and

outputting the data from the one or more parallel query engines to a user application.

16. The computer program product of claim 15, wherein the query is an SQL query.

17. The computer program product of claim 15, wherein the database engine comprises:

one or more parallel query engines;

an external table engine; and

one or more storage disks.

18. The computer program product of claim 15, wherein the metadata specifies a preprocessor codec.

19. The computer program product of claim 18, wherein the streaming comprises executing the preprocessor codec over at least a portion of the data from the one or more results files resulting from the query.

20. The computer program product of claim 15, wherein the one or more external locations is a pathname to a file in the external distributed file system.