Tutorial 4: Creating a View

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. They are more useful in a streaming context than they are in an RDBMS context. This is because views can be chained together, and their contents stopped and started using pumps.

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";