Handling nulls with CQs
The following CQ would insert events from PosSource_TransformedStream
(see Joining cache data with CQs) for which there was no matching zip code in ZipCache
into NoMatchingZipStream
:
CREATE TrapZipMatchingErrors INSERT INTO NoMatchingZipStream SELECT p.MERCHANTID, p.DATETIME, p.AUTHAMOUNT FROM PosSource_TransformedStream p LEFT OUTER JOIN ZipCache z ON p.ZIP = z.Zip WHERE z.Zip IS NULL;
The following CQ joins events from two streams using the MATCH_PATTERN
(see Using pattern matching) clause. If after 24 hours no matching event has been received, the event is output with <Timeout>
and null
in place of the matching event's data source name and record.
CREATE CQ MatchedRecordsCQ INSERT INTO PatternMatchStream SELECT a.unique_id, a.data_source_name, a.data_source_record, CASE WHEN b IS NOT NULL THEN b.data_source_name ELSE "<Timeout>" END, CASE WHEN b IS NOT NULL THEN b.data_source_record ELSE null END FROM MergedDataSourcesStream MATCH_PATTERN (T a (b | W)) DEFINE T = timer(interval 24 hour), a = MergedDataSourcesStream(), b = MergedDataSourcesStream(unique_id == a.unique_id), W = wait(T) PARTITION BY unique_id;