Continuous query (CQ)
Most of an application’s logic is specified by continuous queries. Striim queries are in most respects similar to SQL, except that they are continually running and act on real-time data instead of relational tables.
Queries may be used to filter, aggregate, join, enrich, and transform events. A query may have multiple input streams to combine data from multiple sources, windows, caches, and/or WActionStores.
Some example queries illustrating common use cases:
Filtering events
The GetErrors query, from the MultiLogApp sample application, filters the log file data in Log4ErrorWarningStream to pass only error messages to ErrorStream:
CREATE CQ GetErrors INSERT INTO ErrorStream SELECT log4j FROM Log4ErrorWarningStream log4j WHERE log4j.level = 'ERROR';
Warning messages are discarded.
Filtering fields
The TrackCompanyApiDetail query, also from the MultiLogApp sample application, inserts a subset of the fields in a stream into a WActionStore:
CREATE CQ TrackCompanyApiDetail INSERT INTO CompanyApiActivity(company,companyZip,companyLat,companyLong,state,ts) SELECT company,companyZip,companyLat,companyLong,state,ts FROM CompanyApiUsageStream;
Values for the fields not inserted by TrackCompanyApiDetail are picked up from the most recent insertion by TrackCompanyApiSummary with the same company
value.
Alerting
CREATE CQ SendErrorAlerts INSERT INTO ErrorAlertStream SELECT 'ErrorAlert', ''+logTime, 'error', 'raise', 'Error in log ' + message FROM ErrorStream;
The SendErrorAlerts query, from the MultiLogApp sample application, sends an alert whenever an error message appears in ErrorStream.
Aggregation
This portion of the GenerateMerchantTxRateOnly query, from the PosApp sample application, aggregates the data from the incoming PosData5Minutes stream and outputs one event per merchant per five-minute batch of transactions to MerchantTxRateOnlyStream:
CREATE CQ GenerateMerchantTxRateOnly INSERT INTO MerchantTxRateOnlyStream SELECT p.merchantId, FIRST(p.zip), FIRST(p.dateTime), COUNT(p.merchantId), SUM(p.amount) ... FROM PosData5Minutes p ... GROUP BY p.merchantId;
Each output event includes the zip code and timestamp of the first transaction, the total number of transactions in the batch, and the total amount of those transactions.
Enrichment
The GetUserDetails query, from the MultiLogApp sample application, enhances the event log message events in InfoStream by joining the corresponding user and company names and zip codes from the MLogUserLookup cache:
CREATE CQ GetUserDetails INSERT INTO ApiEnrichedStream SELECT a.userId, a.api, a.sobject, a.logTime, u.userName, u.company, u.userZip, u.companyZip FROM InfoStream a, MLogUserLookup u WHERE a.userId = u.userId;
A subsequent query further enhances the data with latitude and longitude values corresponding to the zip codes, and uses the result to populate maps on the dashboard.
Handling nulls
The following will return values from the stream when there is no match for the join in the cache:
SELECT ... FROM stream S LEFT OUTER JOIN cache C ON S.joinkey=C.joinkey WHERE C.joinkey IS NULL