USER DEFINED FUNCTIONS FOR DATA LOADING

• US 20120239612A1
• Filed: 05/31/2012
• Published: 09/20/2012
• Est. Priority Date: 01/25/2011
• Status: Abandoned Application

• Current Assignee: Hewlett Packard Enterprise Development LP (Hewlett-Packard Enterprise Company)
• Original Assignee: Hewlett Packard Enterprise Development LP (Hewlett-Packard Enterprise Company)
• Inventors: George, Muthian, Wang, Song
• Application Number:
  US13/485,246
• Publication Number:
  US 20120239612A1
• Time in Patent Office: Days
• US Class Current: 707/602
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

Data loading with user defined functions is described in various implementations. An example system for data loading may include a structured query language (SQL) compiler to identify a call to a table valued user defined function (TVUDF) within a SQL statement that includes an insert statement; identify metadata associated with the TVUDF; validate and resolve a subclass type of the TVUDF based on the metadata and the insert statement; and generate a data loading plan to retrieve and load data from an external data source into a table of a database based on the subclass type of the TVUDF. The system may also include a data loading engine in the database to execute the data loading plan, the data loading plan including the TVUDF to retrieve data from the external data source, and load the retrieved data into the table of the database in accordance with the data loading plan.

Claims

1. A system for data loading comprising:

a structured query language (SQL) compiler to identify a call to a table valued user defined function within a SQL statement that includes an insert statement, identify metadata associated with the table valued user defined function, validate and resolve a subclass type of the table valued user defined function based on the metadata and the insert statement, and generate a data loading plan to retrieve and load data from an external data source into a table of a loading database based on the subclass type of the table valued user defined function; and

a data loading engine in the loading database to execute the data loading plan generated by the SQL compiler, the data loading plan including the table valued user defined function to retrieve data from the external data source, and load the retrieved data into the table of the loading database in accordance with the data loading plan.

2. The system of claim 1, wherein the data loading plan executes in an on-demand manner when the insert statement comprises an on-demand data loading insert statement.

3. The system of claim 1, wherein the data loading plan executes in a continuous manner when the insert statement comprises a continuously loading insert statement.

4. The system of claim 3, wherein the data loading plan indicates that the data are being loaded continuously in a background process having a name identifier in the loading database, and wherein management and monitoring of the background process are based on the name identifier.

5. The system of claim 1, wherein the SQL compiler returns an error in response to the call to the table valued user defined function being used outside of a FROM clause of a SELECT statement or an INSERT statement with extended syntax.

6. The system of claim 1, wherein the SQL compiler determines a number of data partitions in the external data source and a number of partitions in the table of the loading database, and wherein the data loading plan is based on the determined number of data partitions in the external data source and the number of partitions in the table of the loading database.

7. The system of claim 6, wherein the data loading plan indicates that an identification number from the SQL compiler is required for the table valued user defined function to obtain a configuration resource to process each of the data partitions.

8. The system of claim 6, wherein the data loading plan comprises collecting data in parallel from the external data source for loading in the partitions in the table of the loading database.

9. The system of claim 1, wherein the data loading plan indicates that the table valued user defined function processes a row of data at a time.

10. The system of claim 1, wherein the data loading plan indicates that the table valued user defined function processes a page of data at a time.

11. The system of claim 1, wherein the data loading plan includes an output field mapping of the retrieved data.

12. The system of claim 1, wherein the data loading plan indicates that the table valued user defined function retrieves the data in sorted order.

13. The system of claim 1, wherein the data loading plan indicates that the table valued user defined function executes in the data storage computer node.

14. A computer-implemented method for data loading, the method comprising:

receiving, at a computing device, a call to a table valued user defined function within a structured query language (SQL) statement that includes an insert statement;

processing the call, using the computing device, to validate and resolve a subclass type of the table valued user defined function based on metadata associated with the table valued user defined function; and

generating, using the computing device, a data loading plan to retrieve and load data from a data source that is external to the computing device based on the subclass type of the table valued user defined function, the data loading plan including the table valued user defined function; and

executing, using the computing device, the data loading plan to retrieve the data and load the retrieved data into a table in accordance with the data loading plan.

15. A non-transitory computer-readable storage medium Storing instructions that, when executed by a processor, cause the processor to:

receive a call to a table valued user defined function within a structured query language (SQL) statement that includes an insert statement;

process the call to validate and resolve a subclass type of the table valued user defined function based on metadata associated with the table valued user defined function; and

generate a data loading plan to retrieve and load data from an external data source based on the subclass type of the table valued user defined function, the data loading plan including the table valued user defined function; and

execute the data loading plan to retrieve the data and load the retrieved data into a table in accordance with the data loading plan.