A unique feature of CCL is the support of database-style operations on CCL Windows through the use of primary keys and OpCodes (short for operation code) on events. Each event going into and coming out of a CCL project has an OpCode. The OpCode of the incoming event indicates whether the event is an insert, update or delete operation and is set by the input adapter, based on the type of source and information from that source. Now in the simplest case, for sources that don’t send updates or deletes, every event is an insert.
OpCodes really only have significance in the CCL project when the event is processed by a Window. Windows have a primary key and retain state, and this is where OpCodes come in. OpCodes allow an event to directly modify the contents of a Window. This means that:
- Insert: An incoming insert event will add a row to the window (as long as there isn’t already a row in the window with the same primary key value, in which case the insert will be ignored and an error logged)
- Update: An incoming update event will update the row in the window with the same primary key value (if no row with that key value exists, the update is ignored and an error is logged)
- Upsert: An incoming upsert event will update the row in the window with the same primary key value if one exists; if not it will insert a row to the window
- Delete: A delete event will delete the row in the window with the same primary key value (if no such row exists, the delete event will cause an error to be logged)
- SafeDelete: A safedelete event will delete the row with the same primary key value if it exists, but if not the event will just be ignored without producing an error
Adapters vary in their capabilities for managing opcodes. For example, some adapters accept events from the source without an opcode, and will then set the opcode for all events streamed into the project as inserts. Some of the output adapters have configuration properties that let you receive all opcodes, or can operate in “DataWarehouse” mode where they convert updates and upserts to inserts and ignore deletes.
Typically, Streams process insert events. How a stream behaves if it receives an event with an OpCode other than insert, depends on whether it is a "Keyed Stream" (i.e. has a primary key defined). For streams without a primary key: any attempt to publish an event with an opCode other than insert to an unkeyed input stream will result in an error. And the output from any unkeyed CCL Stream will only contain insert events. A keyed stream will process events with any opcode and will simply leave the opCode unchanged.
In this example, window1 receives temperature readings from a sensor. The sensor sends simple messages that don’t contain opcodes; the adapter sends all incoming events into window1 as inserts, and the window is keyed on the timestamp of each event:
Note that if the event at T3 has a timestamp of …3.30 it would have been rejected and not added to the window, since each row in the window must have a unique key value.
Now let’s say you have window2 that receives prices from a stock ticker as upserts, and the window is keyed by symbol. In this case the window will only keep the most recent value for each symbol. You would see the following:
So let’s look at how opCodes affect an aggregation window:
And to take this a step further, the KEEP clause on InputWindowA will generate deletes as events “time out” after 30 minutes. So if we fast forward the clock, and no more events are received, 30 minutes after T1 we will see message 1 get deleted, which removes it from the aggregation causing an update to the aggregate value:
Try it yourself
The best way to really get comfortable with opCodes is to experiment. The streaming development tools in the studio makes it easy. Just set up a simple model, run it, and then use the Manual Input tool to send in events with different opCodes to see what happens.
You can use the Stream Viewer in the Studio to see how each Window changes. You can also use the Event Tracer in the Studio to see how each window is affected by the event. In the Studio, after starting the project, just connect the Event Tracer in the Run/Test perspective to the project, and then use the Manual Input tool to send in some events. The color coding in the Event Tracer will indicate the opCode of the record emitted by each window, and if you double click on a window in the Event Tracer it will dump the contents, including the opCode, to the Studio console.
Below is the CCL for the aggregation example above – you can copy it and paste it into the CCL editor in the studio if you want to get started quickly.
CREATE INPUT WINDOW InputWindowA
SCHEMA ( Seq integer , Cat string , Value integer )
PRIMARY KEY ( Seq )
KEEP 30 MIN ;
CREATE OUTPUT WINDOW Average
PRIMARY KEY DEDUCED
InputWindowA.Cat Cat ,
avg ( InputWindowA.Value ) Avg
GROUP BY InputWindowA.Cat ;