Reference Table Queries

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.

Prerequisites

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.

Database Service Definition

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.

Error Handling

When you start a project that contains a reference table query, it does a table schema compatibility check. The reference scans the database table and verifies that:
  • For each column specified in the reference, there is a column of the same name (case insensitive) in the table.
  • The datatype of the column in the table is compatible with the datatype of the column in the reference.
  • If the reference definition specifies a primary key, there is a matching primary key in the database. (If the reference definition doesn't specify a primary key, it doesn't matter whether or not the database has a primary key.)
In order to check the type for each mapped column, the reference attempts to pull a sample row from the database. It's done this way to be as database-agnostic as possible. If it can pull that column into ESP, the check succeeds. Otherwise it fails, except in the following two cases:
  • If the query that the reference uses to do the type-checking is rejected by the database (because it doesn't support SQL 2003 standards), the reference will not complete type checking, but will allow the project to start up, providing a warning that it can't guarantee that the type mapping is valid.
  • If the table has no data in it, then the type checking will stop, and a warning will be printed that it can't guarantee that the type mapping is valid.

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.