Skip to main content

Handling variable-length events with CQs

The following TQL shows an example of handling events with a varying number of fields. The events may have 6, 8, 10, or 12 fields depending on whether they have zero, one, two, or three pairs of objectName and objectValue fields. This TQL discards the events without object fields and consolidates all the object fields from the other events into objectStream.

CREATE TYPE objectStreamType(
  dateTime org.joda.time.DateTime KEY , 
  eventName java.lang.String KEY , 
  objectName java.lang.String KEY , 
  objectValue java.lang.Long  
);
CREATE STREAM objectStream OF objectStreamType;

CREATE CQ RawParser1 
INSERT INTO objectStream
-- the first pair of object fields is present when there are at least 8 fields in the record
SELECT 
  TO_DATEF(data[0],'yyyy-MM-dd HH:mm:ss.SSSZ') AS dateTime,
  TO_STRING(data[1]) AS eventName,
  TO_STRING(data[7]) AS objectName,
  TO_STRING(data[8]) AS objectValue
FROM rawData
WHERE arlen(data) >= 8;

CREATE CQ RawParser2 
INSERT INTO objectStream
-- the second pair of object fields is present when there are at least 10 fields in the record
SELECT 
  TO_DATEF(data[0],'yyyy-MM-dd HH:mm:ss.SSSZ') AS dateTime,
  TO_STRING(data[1]) AS eventName,
  TO_STRING(data[9]) AS objectName,
  TO_STRING(data[10]) AS objectValue
FROM rawData
WHERE arlen(data) >= 15;

CREATE CQ RawParser33 
INSERT INTO objectStream
-- the third pair of object fields is present when there are at least 12 fields in the record
SELECT 
  TO_DATEF(data[0],'yyyy-MM-dd HH:mm:ss.SSSZ') AS dateTime,
  TO_STRING(data[1]) AS eventName,
  TO_STRING(data[11]) AS objectName,
  TO_STRING(data[12]) AS objectValue
FROM rawData
WHERE arlen(data) >= 12;