How to use ESP in conjunction with SAP HANA
SAP ESP is often used in conjunction with SAP HANA. In most cases, ESP is used to process incoming event streams, in real-time, in order to do one or both of the following:
1. Capture information from event streams in HANA
This can be as simple as capturing raw event data in HANA, where ESP is used as a way of collecting the data as fast as it arrives and assembling it into micro-batches for efficient loading into HANA.
But more often it involves applying some processing logic to transform the raw incoming data into the desired data. This can range from filtering to enrichment or can even involve aggregation or correlation.
2. Monitor event streams to generate alerts or initiate a response, based on current activity and information
As events stream into ESP, they can be analyzed in the context of other events as well as historical or reference information from HANA. ESP can watch for trends, correlations or patterns - and can produce alerts or initiate a response when defined conditions are met.
This can all be done by ESP working very closely with HANA as follows:
ESP sending data to HANA
- ESP can capture events in HANA - either raw events or derived streams of events. This is done using the ESP HANA Output adapter. The adapter can be attached to any ESP streams or windows and pointed at a HANA table. Some things to note:
- The HANA table must have a column set that is compatible with the ESP stream/window that is pushing events into HANA. The studio provides tools, in the ESP visual editor, to either import the schema of a HANA table into the ESP project, or to create a HANA table based on the schema of the selected ESP stream/window
- You should always used the HANA output adapter for this, rather than the "Generic Database Adapter" - the HANA output adapter is faster, scales better, and has support for HANA features that aren't in the generic database adapter
- If you are feeding events from ESP windows into HANA table, consider the impact of opCodes. ESP Windows can publish inserts, updates and deletes. Be sure the datawarehouse property of the HANA adapter is set to produce the desired results. See this note for more explanation. In particular, keep in mind that a KEEP clause on the ESP window will emit delete events when events are removed from the window. With the datawarehouse mode of the HANA output adapter set to "off", these will cause rows to be deleted from the attached HANA tables. Adjust the datawarehouse mode setting to achieve the desired behavior.
- ESP can also update tables in HANA. Instead of just adding data to HANA tables, ESP can update tables in HANA. So, for example, if you had a table in HANA that was intended to always have the current status of some set of 'things', ESP could update that table whenever it detected a change in status based on incoming information
ESP pulling data from HANA
Joining event streams to HANA tables/views
It's often the case that ESP needs to have access to data from SAP HANA to use in processing the incoming events. This data might be used to enrich the events - for example it might be reference data that allows additional fields to be added to the event to make the data contained in the event more meaningful or to get it into the desired format. Or it might be used in deciding how to interpret the event - for example comparing the current data to historical norms.
The primary way of doing this is to define a CCL REFERENCE element in your ESP project. A project can contain any number of reference elements and each element points to a table or view in HANA. You can then join ESP streams and ESP windows to the REFERENCE element - i.e. to the HANA table.
A couple of notes on usage:
- The data is pulled from HANA as needed - it's not preloaded. Thus an incoming event that is joined to the REFERENCE element causes a query to be executed against HANA. The reference element can be configured with caching for improved efficiency/performance in cases where the same data from HANA is needed repeatedly, and the age property on the cache can ensure that data is refreshed from HANA with the desired frequency.
- The join only executes on ESP events - not on changes to the HANA reference table. Without caching, each event will cause the HANA table to be queried, so you will always be using the latest data in the HANA table, but changes to the HANA table don't cause the ESP JOIN to be updated. With caching you can control how quickly the cached values "time out"
- Reference elements aren't limited to joins, they can also be used in Flex operators. But remember that reference elements don't produce events, they can only be queried.
Executing HANA queries (and other SQL statements) from CCL expressions
The CCL getdata() function can be used within any expression - a column expression or a SPLASH block - to execute a SQL query against a HANA database and return the result. While this is most often used to query HANA, it isn't limited to simple lookups and can be used to execute analytics or even to run a stored procedure.
Pre-loading data from HANA into ESP Windows or data structures
It's also possible to query HANA using an ESP input adapter. Here we just use the generic database input adapter (we don't have a specialized HANA input adapter). The adapter will run the query when the project starts and can (optionally) be configured to regularly re-run the query at a defined interval. Typically the adapter would be attached to an ESP input window which would hold the results, though it could also be attached to an ESP input stream feeding into a CCL flex operator where the data from HANA could be used to update variables or data structures.
When it comes to deciding whether to use a REFERENCE element or an input adapter, consider the following:
- In general, where you just need to do lookups in HANA, use a REFERENCE element
- Incoming events cause data to be retrieved from the REFERENCE element
- Input adapters pull data from HANA at project startup, and can refresh that data on a periodic basis. The data retrieved from HANA then needs to be held in a window in the project or used to update data structures.
- The rows retrieved from the HANA table by an input adapter can be processed as events in the ESP project
Making ESP windows "visible" in HANA using HANA Smart Data Access
And finally, ESP supports HANA smart data access. This allows event windows in an ESP project to be mapped to virtual tables in HANA, with the result that HANA queries can query ESP windows.