Here's an example of a simple HANA smart data streaming project that sends an email whenever a particular situation is detected.
In this case, it is monitoring events from some "machines" and watching for power outages that are longer than 20 seconds in duration. Specifically, it watches for a "Power Out" event that is not followed by a "Power On" event from the same machine within 20 seconds. When the situation is detected, the ALARM_POWER stream emits an event. The SMTP output adapter attached to the ALARM_POWER stream sends events from that stream as emails.
Note that we join the event stream to a HANA table where we pull information about each machine. This uses the CCL REFERENCE element which is sort of a proxy for a HANA table within a CCL project. To optimize performance, the REFERENCE element is configured here to cache results from HANA. The data is pulled in from HANA when needed (lookup by MACHINEID) and cached, with cache refresh time specified.
For details on configuring the SMTP output adapter, see the SDS Adapters reference guide.
//input stream that receives events
CREATE INPUT STREAM MACHINEDATA SCHEMA (
MACHINEID string ,
EVENT_TIME msdate ,
EVENT_NAME string ,
EVENT_DESCRIPTION string ,
EVENT_VALUE string ) ;
//references a HANA table; caches results to improve performance
CREATE REFERENCE MACHINE_REF_reference1
SCHEMA (
MACHINEID string ,
MACHINETYPE string ,
MAX_TEMP decimal(4,2) ,
MIN_TEMP decimal(4,2) ,
IMPORTANCE string ,
ADDRESS string ,
SUBJECT string )
PRIMARY KEY ( MACHINEID )
PROPERTIES service = 'HANA_PM' ,
source = 'MACHINE_REF' ,
sourceSchema = 'MY_STREAMING_USER' ,
cachePolicy = 'ONACCESS' ,
maxCacheSizeMB = 5 ,
maxCacheAge = 12 HOURS ;
//joins the event stream to a HANA table
CREATE OUTPUT STREAM EVENTS AS SELECT
MACHINEDATA.MACHINEID MACHINEID ,
MACHINEDATA.EVENT_TIME EVENT_TIME ,
MACHINEDATA.EVENT_NAME EVENT_NAME ,
MACHINEDATA.EVENT_DESCRIPTION EVENT_DESCRIPTION ,
MACHINEDATA.EVENT_VALUE EVENT_VALUE ,
MACHINE_REF_reference1.MACHINETYPE MACHINETYPE ,
MACHINE_REF_reference1.MAX_TEMP MAX_TEMP ,
MACHINE_REF_reference1.MIN_TEMP MIN_TEMP ,
MACHINE_REF_reference1.IMPORTANCE IMPORTANCE ,
MACHINE_REF_reference1.ADDRESS ADDRESS ,
MACHINE_REF_reference1.SUBJECT SUBJECT
FROM MACHINEDATA INNER JOIN MACHINE_REF_reference1
ON MACHINEDATA.MACHINEID = MACHINE_REF_reference1.MACHINEID;
/**watch for a "power out" event that's not followed by a "power on" event for same machine within 20 seconds
when pattern is detected, emits an event
*/
CREATE OUTPUT STREAM ALARM_POWER
AS SELECT
A.MACHINEID MACHINEID ,
A.EVENT_TIME EVENT_TIME ,
'POWER' ALARM_TYPE ,
'POWER Out for more than 20 seconds' ALARM_DESC ,
A.IMPORTANCE IMPORTANCE ,
A.ADDRESS ADDRESS ,
A.SUBJECT SUBJECT FROM EVENTS A, EVENTS B MATCHING [ 20 SEC : A , ! B ]
ON A.MACHINEID = B.MACHINEID AND A.EVENT_VALUE = 'Power off' AND B.EVENT_VALUE = 'Power on' ;
//receives events from ALARM_POWER stream and sends them as email; body contains all fields (but this is configurable)
ATTACH OUTPUT ADAPTER SMTP_Output1 TYPE smtp_out TO ALARM_POWER
PROPERTIES smtpHost = 'xxx', addressColumn = 'ADDRESS', subjectColumn = 'SUBJECT', importanceColumn = 'IMPORTANCE', fromAddress = 'name@zzz.com';
