How to enrich events with data from SAP HANA
It's often the case that a streaming project needs to have access to data from the SAP HANA database (or another database) 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.
There are several ways this can be done:
1. The easiest (and newest) method is to use the CCL REFERENCE element. When you define a REFERENCE element in a streaming project you are defining a link to a table in HANA. You can then directly join streams and CCL windows to the REFERENCE element - i.e. to the HANA table. Click on the doc link to see an example. 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. A new feature in SP08 is caching for improved efficiency/performance in cases where the same data from HANA is needed repeatedly
- The join only executes on streaming 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 CCL JOIN to be updated. With caching you can control how quickly the cached values "time out"
2. A second option is to use the streaming DB input adapter to query a database and load the results into a CCL window. This data is then available for joins or it can be used in other ways - e.g. within a flex operator. This option is good when it makes sense to have the full data set available in a CCL window. The streaming DB input adapter can be configured to load the data once when the project starts, or to periodically refresh the data at a defined interval.
3. The final option is to use the CCL getdata() function. This can be used within an expression or a CCL Script block to execute a SQL query against a database and return the result. Then you can use the returned value(s) however you wish.