Reference Table Queries

Reference Table Queries provide a way to augment the streaming data in an SAP Sybase Event Stream Processor project with information from a table or view in SAP HANA.

This CCL element enables you to establish a reference to a table or view in SAP HANA from within an ESP project. This reference can then be used in a join along with streams and windows. When an event arrives via a stream or window, the reference executes a query on the table in the external database and uses the returned data in the join to enrich streaming data with information from the database.

To create a reference you need the following information:

the name of the database service to use
the name of the table from which to retrieve information
the schema of the table

You also have the option of specifying the following:

the primary key of the table as the primary key of the reference
that the reference should attempt to reconnect when the connection is lost
how many attempts to make
how long to wait between attempts

In CCL, use the CREATE REFERENCE statement to define the reference, and then use the FROM and ON clauses to join data from the reference with streams and windows in an ESP project. Similarly, in the visual editor in the Studio, use the Reference shape, found under Streams and Windows in the Palette.

In SPLASH, you can use an iterator over a reference from a local DECLARE block and in the FLEX operator in the same way you use an iterator over a window. You may also iterate over a reference using the key search (if a primary key is defined), record matching search, and for loop functionality.