Reference table queries enable you to look up information in an external database table in response to an incoming event.
Incoming events can arrive with only a portion of the information necessary to complete the processing you wish to specify in your project. When that additional information is present in existing tables in an external database, you can use reference table queries to look it up. There are two parts to this: creating the reference to an external table and using the reference to execute an ad hoc query in a join or flex operator.
When creating the reference, you must first decide what data you want to use. Then identify the external database table containing the data by name, obtain the schema of the table, and find out what service to use to contact the database. Decide whether you want to attempt to reconnect if the connection is dropped, and if so, how many times, and how long to wait between attempts.
When joining a stream or window to a reference, you need to decide what you want as the output of the join. There are numerous restrictions on how you can use references in joins, and what types of output you can obtain. For example, if you want the output of the join to be a window, you must specify the primary key of the reference and use the complete primary key in the ON or WHERE clause of the join.
There are several different ways to use references within SPLASH programs. You can iterate over the rows in the table or grab specific rows. Basically, you can utilize a reference in the same ways you can utilize a window. It is simply another source of data for processing in your SPLASH routine.
You can use references - in joins and in SPLASH programs - inside a module as well as within the main body of your project. Like stores, references used in a module must be bound to a reference defined in the main body of your project.
You must have the SAP HANA ODBC client installed on the system where you want to run projects that include reference table queries. SAP recommends that you use the latest version of the SAP HANA ODBC client available, but it must be at least version 1.0.67.
All connections to external databases, including reference table queries, are made using services defined in the services.xml file. This file, as shipped with ESP, includes a service definition, SampleHanaJdbc , that shows how to define a service for use in reference table queries to a HANA database table. On Windows machines it is in the %ESP_HOME%\bin folder. On UNIX and Linux machines it is in the $ESPHOME/bin directory. You can modify entries in this file, or add new ones, for use in reference table queries.
While a project is running, the error scenarios are mostly connection-based. When a failure is caused by a lost connection, the server will attempt to reconnect based on the reconnect parameters specified in the reference's definition.