Processing database queries based on external tables

Abstract

A database system processes database queries that process external tables representing data obtained from an external systems. The database system generates an execution plan to execute the database query. The database system sends a request for data of the external table to an external system. The request provides connection information to the external system. The external system sends a request to connect to the database system and provides data of the external table as a data stream. If the database system reaches an operator that processes the data of the external table, the database system waits for data from the external system. The database system processes the database query using the data of the data stream. The database system provides the result of the database query to a requestor, for example, a client device.

We claim: 
     
         1 . A computer-implemented method for executing database queries based on external tables, the method comprising:
 receiving, by a database system, description of an external table, the description identifying an external system as a source of the external table;   receiving a database query processing data one or more tables, the one or more tables including the external table;   generating execution plan for the database query, the execution plan comprising one or more external table operators for processing data of the external table;   executing, by the database system, the execution plan, comprising for each external table operator of the execution plan:
 sending, by the database system, to the external system, data stream connection information associated with an external table operator; 
 waiting for a request to create a data stream connection from the external system; 
 receiving, by the database system, from the external system, the data stream connection associated with the external table operator; 
 responsive to establishing the data stream connection with the external system, performing one or more of: receiving a data stream from the external system or sending a data stream to the external system via the data stream connection; and 
   storing the result of execution of the execution plan.   
     
     
         2 . The computer-implemented method of  claim 1 , wherein executing the execution plan further comprises:
 providing a result of execution of the external table operator to another operator of the execution plan.   
     
     
         3 . The computer-implemented method of  claim 1 , wherein the other operator receives data as input from the external table operator and the database system pipelines execution of the other operator with execution of the external table operator such that the database system starts execution of the other operator before all the data of the data stream is received from the external system. 
     
     
         4 . The computer-implemented method of  claim 1 , wherein the other operator provides data as input to the external table operator and the database system pipelines execution of the external table operator with the other operator such that the database system starts processing the external table operator to transmit data to the external system before the other operator has provided all the data to the external table operator. 
     
     
         5 . The computer-implemented method of  claim 1 , wherein the database query is a first database query, wherein sending the data stream connection information by the database system is in response to a request to execute a second database query received from the external system, the second database query requesting information stored in a system table of the database system. 
     
     
         6 . The computer-implemented method of  claim 5 , wherein the database system stores the data stream connection information in the system table such that the execution of the second database query provides the data stream connection information to the external system. 
     
     
         7 . The computer-implemented method of  claim 1 , further comprising:
 generating code configured to provide data to the database system; and   sending the generated code to the external system, wherein the external system executes the generated code for providing the data stream to the database system.   
     
     
         8 . The computer-implemented method of  claim 1 , wherein the external table operator is one of:
 an external table scan operator,   an external table insert operator,   an external table modify operator, or   an external table delete operator.   
     
     
         9 . The computer-implemented method of  claim 1 , responsive to the external table operator being an external table scan operator, receiving a set of records from the external system via the data stream connection. 
     
     
         10 . The computer-implemented method of  claim 1 , responsive to the external table operator being an external table insert operator, sending a set of records to the external system via the data stream connection. 
     
     
         11 . The computer-implemented method of  claim 1 , responsive to the external table operator being an external table modify operator, receiving a set of records from the external system via the data stream connection, modifying the records and sending a set of modified records to the external system via the data stream connection. 
     
     
         12 . The computer-implemented method of  claim 1 , responsive to the external table operator being an external table delete operator, sending information identifying a set of records to the external system via the data stream connection, wherein the external system deletes data corresponding to the set of identified records. 
     
     
         13 . The computer-implemented method of  claim 1 , wherein receiving, by a database system, description of an external table comprises, receiving a data definition language statement that registers the external table with the database system. 
     
     
         14 . The computer-implemented method of  claim 1 , wherein the database system comprises a plurality of compute nodes, each compute node executing the external table operator, wherein each compute node receives a data stream connection from the external system for performing parallel data transfer. 
     
     
         15 . A computer readable non-transitory storage medium storing instructions thereon, the instructions when executed by a processor cause the processor to perform the steps of:
 receiving, by a database system, description of an external table, the description identifying an external system as a source of the external table;   receiving a database query processing data one or more tables, the one or more tables including the external table;   generating execution plan for the database query, the execution plan comprising one or more external table operators for processing data of the external table;   executing, by the database system, the execution plan, comprising for each external table operator of the execution plan:
 sending, by the database system, to the external system, data stream connection information associated with an external table operator; 
 waiting for a request to create a data stream connection from the external system; 
 receiving, by the database system, from the external system, the data stream connection associated with the external table operator; 
 responsive to establishing the data stream connection with the external system, performing one or more of: receiving a data stream from the external system or sending a data stream to the external system via the data stream connection; and 
   storing the result of execution of the execution plan.   
     
     
         16 . The computer readable non-transitory storage medium of  claim 15 , wherein the database query is a first database query, wherein sending the data stream connection information by the database system is in response to a request to execute a second database query received from the external system, the second database query requesting information stored in a system table of the database system. 
     
     
         17 . The computer readable non-transitory storage medium of  claim 16 , wherein the database system stores the data stream connection information in the system table such that the execution of the second database query provides the data stream connection information to the external system. 
     
     
         18 . The computer readable non-transitory storage medium of  claim 15 , further comprising:
 generating code configured to provide data to the database system; and   sending the generated code to the external system, wherein the external system executes the generated code for providing the data stream to the database system.   
     
     
         19 . The computer readable non-transitory storage medium of  claim 15 , wherein the database system comprises a plurality of compute nodes, each compute node executing the external table operator, wherein each compute node receives a data stream connection from the external system for performing parallel data transfer. 
     
     
         20 . A computer-implemented system for scheduling queries for execution in a database system, the system comprising:
 a computer processor; and   a computer-readable storage medium storing instructions thereon, the instructions when executed by a processor cause the processor to perform the steps of:
 receiving, by a database system, description of an external table, the description identifying an external system as a source of the external table; 
 receiving a database query processing data one or more tables, the one or more tables including the external table; 
 generating execution plan for the database query, the execution plan comprising one or more external table operators for processing data of the external table; 
 executing, by the database system, the execution plan, comprising for each external table operator of the execution plan:
 sending, by the database system, to the external system, data stream connection information associated with an external table operator; 
 waiting for a request to create a data stream connection from the external system; 
 receiving, by the database system, from the external system, the data stream connection associated with the external table operator; 
 responsive to establishing the data stream connection with the external system, performing one or more of: receiving a data stream from the external system or sending a data stream to the external system via the data stream connection; and 
 
 storing the result of execution of the execution plan.