Tutorial 4: Using Views

<< Click to Display Table of Contents >>

Navigation:  Building Streaming Applications >

Tutorial 4: Using Views

Previous pageReturn to chapter overviewNext page

Views are reusable definitions of queries. They are similar to database views, but are compiled once and continue to process incoming data rows in real time. (Database views are compiled each time the query is executed.) The diagram at the right is an example of the CREATE VIEW statement that shows its general structure by example .

As with a database view, you can SELECT from a view. Because the view continues to process data, the results of SELECT from a view are the same as if the original query were run. For example, if you created a view to get all products, you might use the following code:

CREATE VIEW all_prod AS SELECT STREAM * FROM products

 

Later, whenever "all_prod" is invoked, as in

SELECT STREAM * FROM "all_prod" WHERE CategoryName='Shoes'

 

the data from "all_prod" will read as if the original query--SELECT STREAM * FROM products--were run.

In many respects, streaming views are coded like database views, but 1) they continually update and 2) SQLstream views can only be selected, not written to.

Usefulness of views

Streaming views are like "macros" and are extremely useful for expanding a complex query in streaming SQL, more useful than they are in an RDBMS context.

While views are used in RDBMS systems, to implement joins or simplify access to a large table, they are used in more specific ways inview streaming SQL.

Because SQLstream s-Server is effective at listening to data at different points in a series of processing steps, chained views allow you to "listen" (SELECT) to data from one or more streams, foreign streams or views. Each view can perform filters, transformations, or analysis, and can produce results for "downstream" listeners. Each named view is then available for other listeners--internal pipelines or external clients--to SELECT. This allows you to break down a complex business pipeline into easily understood chunks, which can be selected at any time.

Examples

The following are simple examples of views. The first example creates and sets a schema called "WebData," and creates a stream called "OrderData." Both views use "OrderData" as a source.

Hourly sales totals

CREATE OR REPLACE SCHEMA "WebData";

SET SCHEMA '"WebData"';

 

CREATE OR REPLACE STREAM "OrderData" (

"key_order" BIGINT NOT NULL,

"key_user" BIGINT,

"country" SMALLINT,

"key_product" INTEGER,

"quantity" SMALLINT,

"eur" DECIMAL(19,5),

"usd" DECIMAL(19,5)

) DESCRIPTION 'conditioned order data, ready for analysis';

 

CREATE OR REPLACE VIEW "HourlyTotals" AS
SELECT STREAM
   COUNT(*) AS "count",
   SUM("eur") AS "EUR",
   SUM("usd") AS "USD"
FROM "OrderData" AS OD
GROUP BY FLOOR(OD.ROWTIME TO HOUR);
 

Hourly sales totals by country

CREATE OR REPLACE VIEW "HourlyTotalsByCountry" AS
SELECT STREAM
   "country",
   COUNT(*) AS "count",
   SUM("eur") AS "EUR",
   SUM("usd") AS "USD"
FROM "OrderData" AS OD
GROUP BY FLOOR(OD.ROWTIME TO HOUR), "country";