Data continuous SQL process

• US 8,725,707 B2
• Filed: 03/26/2009
• Issued: 05/13/2014
• Est. Priority Date: 03/26/2009
• Status: Active Grant

• Current Assignee: Hewlett Packard Enterprise Development LP (Hewlett-Packard Enterprise Company)
• Original Assignee: Hewlett-Packard Development Company, L.P. (HP Inc.)
• Inventors: Chen, Qiming, Hsu, Meichun
• Primary Examiner(s): Mamillapalli, Pavan
• Application Number:
  US12/411,670
• Publication Number:
  US 20100250572A1
• Time in Patent Office: 1,874 Days
• Field of Search: 707/702
• US Class Current: 707/702
• CPC Class Codes: G06F 16/24565 Triggers; Constraints
• CPC Class Codes: G06F 16/24568 Data stream processing; Continuous queries

Abstract

One embodiment is a method that repeatedly triggers a User Defined Function (UDF) that generates a data stream. A data continuous Structured Query Language (SQL) query is applied to the data stream to construct a data continuous SQL process (DCSP) which executes in a database.

Claims

1. A method, comprising:

repeatedly triggering a User Defined Function (UDF) that generates a data stream;

applying, with a computer, a data continuous Structured Query Language (SQL) query to the data stream to construct a data continuous SQL process (DCSP);

modeling the DCSP as a graph of stations specified with triggering conditions and outgoing pipes, the outgoing pipes defined with a relation schema for type-preservation and with a stream key for identifying stream elements, wherein relations returned from the UDF are replicated to multiple pipes for multiple destination stations; and

executing, in a database engine located in a database, the DCSP, wherein the UDF includes relational operators in the SQL query and expresses a relational transformation since the UDF includes an input value as a relation and an output value as a relation.

2. The method of claim 1, wherein the UDF is repeatedly triggered by stream inputs, timers, and event conditions.

3. The method of claim 1 further comprising:

specifying data-stream-driven processes with SQL queries;

executing the data-stream-driven processes directly at a database management system (DBMS) level.

4. The method of claim 1 further comprising, executing UDFs under streamed Input/Outputs.

5. The method of claim 1, wherein the DCSP is data-stream driven and continuously running.

6. The method of claim 1 further comprising, constructing the DSCP from stations and pipes recursively as a Data Continuous Query (DCQ).

7. The method of claim 6 further comprising, using multiple correlated DCQs to represent the DSCP, wherein data flows are naturally expressed by DCQs and execution ordering is based on a DCQ station defined before being referred to in other DCQs.

8. The method of claim 1 wherein the UDFs have as input a list of relations and return value as a relation called Relation Valued Functions (RVFs).

9. The method of claim 1 wherein a producer RVF inserts an element identifiable by a stream key value, and a consumer RVF de-queues the inserted element.

10. A non-transitory computer readable storage medium having instructions for causing a computer to execute a method, comprising:

executing a continuous data stream process with three functions that include an extract, transform, load (ETL) process, an aggregation process, and reporting process;

specifying the continuous data stream process with Structured Query Language (SQL) queries;

executing the SQL queries at a database management system (DBMS) level, wherein the SQL queries include User Defined Functions (UDFs) that perform relational transformations like a relational operator since the UDFs include input values as relations and output values as relations; and

identifying stations with triggering conditions and outgoing pipes, the outgoing pipes defined with a relation schema for type-preservation and with a stream key for identifying stream elements, wherein relations returned from the UDF are replicated to multiple pipes for multiple destination stations.

11. The non-transitory computer readable storage medium of claim 10, wherein the ETL process, the aggregation process, and the reporting process execute in different cycles with the ETL process executing more frequently than the reporting process.

12. The non-transitory computer readable storage medium of claim 10 further comprising, pushing down the continuous data stream process to the DBMS level to avoid round-trip traffic in data access and utilize parallel database communications to increase performance of executing the SQL queries.

13. The non-transitory computer readable storage medium of claim 10, wherein the ETL process, the aggregation process, and the reporting process are not cascaded on a performance execution basis since for every given number of plural executions of the ETL process the aggregate process executes only once.

14. A computer system, comprising:

a computer that generates a data continuous Structured Query Language (SQL) query to a continuous data stream to construct a data continuous SQL process (DCSP);

a database in communication with the computer, the database including a database engine that executes the DCSP in the database, wherein the DCSP is constructed from User Defined Functions (UDFs) that include relational operators in the SQL query and that express a relational transformation since the UDFs include an input value as a relation and an output value as a relation; and

wherein the DCSP includes stations specified with triggering conditions and outgoing pipes, the outgoing pipes defined with a relation schema for type-preservation and with a stream key for identifying stream elements, wherein relations returned from the UDF are replicated to multiple pipes for multiple destination stations.

15. The computer system of claim 14, wherein the UDFs are continuously running.

16. The computer system of claim 14, wherein execution of the DCSP occurs in the database to increase performance of executing queries to the database.

17. The computer system of claim 14, wherein the UDFs are triggered to repeatedly run by stream inputs to generate a data stream that is deposited to a stream table.

18. The computer system of claim 14, wherein the UDFs are triggered to repeatedly run by stream inputs to generate a data stream that is fed into other UDFs.

19. The computer system of claim 14, wherein the UDFs are triggered to repeatedly run by stream inputs and are not composed on a one-execution basis but coupled asynchronously together.

20. The computer system of claim 14 further comprising:

stations that host the UDFs that are continuously executed;

pipes that function as first-in-first-out containers that connect the stations.