ROWTIME

<< Click to Display Table of Contents >>

Navigation:  Understanding Streaming SQL Concepts > SQLstream Overview >

ROWTIME

Previous pageReturn to chapter overviewNext page

Every streaming row (message) carries a time value called a rowtime, implemented as a pseudo-column in every row. Rowtimes can be implicit or explicit.

Implicit rowtimes are established by the "arrival time" of the row, that is, the time that s-Server receives the row. In the following example, no explicit rowtime is specified, and so the row containing the specified data will receive the implicit rowtime. Even though there is no explicit mention of ROWTIME, it is nevertheless part of that row:

 INSERT INTO logStream VALUES('test', 'message1');

 

Explicit rowtimes are provided by the source application with INSERT, as in the following examples:

INSERT INTO logStream (ROWTIME, source, message)
VALUES(TIMESTAMP '2014-09-30 19:13:00', 'test', 'message1');

 

INSERT INTO logStream (ROWTIME, source, message)
VALUES(LOCALTIMESTAMP, 'test', 'message1');

 

 
Note: When setting an explicit ROWTIME, TIMESTAMP must be monotonically increasing from the previous TIMESTAMP. In the example above, '2014-09-30 19:13:00' needs to be later than the previous TIMESTAMP.

In each of the above cases, a timestamp from the source application is explicitly assigned to ROWTIME.

In either case, the ROWTIME of the arriving row establishes the current time of the stream, known as the stream clock.

The rowtime for newly arriving rows cannot be less than the rowtime for previously received rows (though it can be equal to the rowtime of the current row). Since streaming queries are typically time-sensitive, rowtimes often determine when processing can proceed based on criteria in the receiving query.

ROWTIME and Queries

The following code sets up a stream for the examples below:

CREATE OR REPLACE STREAM S1 (

source VARCHAR(20),

message VARCHAR(3072),

dataTime TIMESTAMP)

DESCRIPTION 'Stream Example';

 

Since ROWTIME is a pseudo-column, it will not be returned with an open stream query such as the following:

SELECT STREAM * FROM S1;

 

Instead, you need to request rowtime explicitly.

SELECT STREAM ROWTIME, * FROM S1;

Saving ROWTIME to "normal" column

You can assign ROWTIME to another column, for clarity or usefulness. The following code assigns ROWTIME to a column called arrivalTime in a stream called S1.

SELECT STREAM ROWTIME AS arrivalTime, * FROM S1;

Promoting data time to ROWTIME

Promoting data time to ROWTIME makes rowtime explicit. The following code promotes the column "dataTime" to ROWTIME in a stream called S1.

 

SELECT STREAM dataTime AS ROWTIME, * FROM S1;

 

The following code first creates a stream called "RawSampleData," then promotes the column "order_created_date" to ROWTIME. Note: all streams need to be created within a schema.

CREATE OR REPLACE STREAM "RawSampleData" (

"order_created_date" TIMESTAMP,

"key_order" VARCHAR(20),

"key_user" VARCHAR(20),

"key_billing_country" VARCHAR(20),

"key_product" VARCHAR(20),

"quantity" VARCHAR(20),

"orderdetail_price_eur" VARCHAR(20),

"orderdetail_price_usd" VARCHAR(20))

DESCRIPTION 'Product Order Example';

 

SELECT STREAM
   "order_created_date" AS ROWTIME,
   "key_order",
   "key_user",
   "key_billing_country",
   "key_product",
   "quantity",
   "orderdetail_price_eur" AS "eur",
   "orderdetail_price_usd" AS "usd"
FROM "RawSampleData";