You will learn
- Defining a pattern to watch for.
- Defining contents of the event to produce when a pattern has been detected.
- Using the CCL Editor to modify the project.
By billjiangsap
Part 8 of 9. Define and watch for patterns in events. Use CCL Editor.
Click SAP HANA Streaming Development to switch to the perspective.
Drag and drop the Pattern item from the palette into the canvas.
Connect DEVICE_EVENTS
to Pattern1
using the connector item.
Rename the Pattern1
to ALARM_POWER
.
Select the ALARM_POWER
item by clicking on it.
Click on Switch to Text button to switch to CCL Editor. You can also press F6.
Click Yes if there is a prompt to save the file. You can also press Alt+y.
Click Outline to open the view. If you cannot find the button, you can also go to Window > Show View > Other… and type in outline
in the search box to find the desired view.
Select ALARM_TEMP
. The CCL Editor will jump to the corresponding code section.
Click Minimize icon on the top right of the Outline view to hide it.
Select the column expressions from the ALARM_TEMP
window and copy them. You will use these code to speed up the writing of "ALARM_POWER"
functionality.
Now paste the column expressions into the ALARM_POWER
stream, replacing the *
.
Edit the FROM clause to read DEVICE_EVENTS A, DEVICE_EVENTS B
. Since you want to watch for a pattern of 2 events from the same stream, you need define two different aliases for the stream.
Now edit the column expressions to the following shown in the picture.
A
.AVG_TEMP
and MAX_TEMP
columns.ALARM_TYPE
and ALARM_DESC
columns.The final code should be:
CREATE OUTPUT STREAM ALARM_POWER
AS SELECT
A.MACHINEID MACHINEID ,
A.EVENT_TIME EVENT_TIME ,
A.LOCATION LOCATION ,
'POWER' ALARM_TYPE ,
'Power out for more than allowed duration' ALARM_DESC
FROM DEVICE_EVENTS A, DEVICE_EVENTS B;
When finished editing, click Switch to Visual button to switch back to visual editor mode. You can also press F6.
Click Add Pattern icon shown below to define the pattern to watch for.
Enter 20 sec
in the Interval box.
Enter the pattern we want to watch for: A, !B
. The “,” means “followed by” and “!” means not. The expression means to watch for event A that is NOT followed by event B within 20 seconds. (“AND” and “OR” operators are also available in the pattern)
Now we need to define our ON clause. This filters the incoming events to determine which events qualify as an “A” event and a “B” event. Enter the following expression and click OK when done:
A.MACHINEID = B.MACHINEID AND A.EVENT_VALUE = 'Power off' AND B.EVENT_VALUE = 'Power on'
Tip: you can use Ctrl+Space for completion assist.
Click Compile Project button to check for errors. Please refer to part 7 of this tutorial group on how to run and re-test the updated project.