Establish a reference to a table in an external database so that streams and flex operators in your project can run ad hoc queries on that table for information to be used in conjunction with streaming data in the project or a module.
When defining a reference in the main project (not inside a module), use:
CREATE REFERENCE name schema_clause [PRIMARY KEY (column1, column2, ...)] PROPERTIES service = 'service name', source = 'table name' [, sourceSchema = 'schema name' ] [, maxReconnectAttempts = integer] [, reconnectAttemptDelayMSec = integer] [, exitOnConnectionError = true|false] ;
When defining a reference inside a module, use:
CREATE REFERENCE name schema_clause [PRIMARY KEY (column1, column2, ...)] ;
A reference defined in a module is a placeholder for a reference with a specific schema and primary key that will only be bound to a concrete reference when it is loaded, for this reason the syntax only allows the specification of the name, schema and primary key field of the reference.
|
name |
Enter a string to identify the reference. Unless surrounded by double quotes, it is restricted to the underscore and alphanumeric characters, and may not start with a numeric character, or be a CCL keyword. If surrounded by double quotes, the only restrictions are that it cannot include a period or a newline character. |
|
schema_clause |
Enter either a schema or the name of a previously defined, named schema. Be sure that it matches the schema of the table this reference will query: column names must be identical and datatypes must be equivalent. See Datatype Mapping for the Database Adapter in the Adapters Guide for details on which ESP datatype to use for each datatype in the table. |
|
service |
Enter a string specifying the name of the ODBC or JDBC service to use to connect to the database. This service must be defined in the service.xml file. |
|
source |
Enter a string identifying the table in the database to query. |
| sourceSchema | (Required if the reference has a primary key defined, otherwise optional.) Enter a string identifying the database schema that the source is in. |
|
maxReconnectAttempts |
(Optional) Enter an integer specifying the number of times to retry a dropped connection per query, or enter -1 to specify unlimited attempts to reconnect. By default, this parameter is set to 0, meaning that ESP will not try to reconnect until it attempts another query. |
|
reconnectAttemptDelayMSec |
(Optional) After specifying maxReconnectAttempts, enter an integer (greater than zero) specifying how many milliseconds to wait between attempts. By default, the wait time is 500ms. |
|
exitOnConnectionError |
(Optional) Enter true to force ESP to terminate if a connection drops and all reconnection attempts fail. By default, this is false: a dropped connection does not make ESP terminate. |
Unlike other CCL constructs which receive and process incoming events, references retrieve data from an external database to use in conjunction with incoming event data. To create a reference, you must know the schema of the table, the primary key, the column names, and the datatypes.
Whether creating the reference in the main project or in a module, you must specify the schema_clause; it cannot be deduced. The schema of the table in the external database is the schema that you must specify for the reference: the column names must be identical and datatypes must be equivalent.
The PRIMARY KEY is optional, but omitting it affects the types of joins the reference can participate in. If specified, it must match the primary key on the table in the database being referenced. Whenever the database table has a primary key, it is recommended to use it as the primary key of the reference.
Dropping or altering the table to which a reference is connected while the project is running is not supported. You must shut down all running ESP projects that have reference connections to a table before altering the table.
An external database has a table, customerTable, which includes the customerID, full name, address, and other details for each of a company's customers. The primary key of the table is the customerID column. This example creates a reference to that table so that as orders from customers (who are identified in the source stream by their customer ID) stream in, the database is queried for customer information that is then joined with the order information in an output stream.
CREATE REFERENCE customerRef SCHEMA ( customerID integer, fullName string, address string ) PRIMARY KEY (customerID) PROPERTIES service='databaseServiceName', source='customerTable', sourceSchema='databaseSchema'; CREATE INPUT STREAM orderStream SCHEMA ( orderID integer, customerID integer, itemID integer); CREATE OUTPUT STREAM orderWithCustomerInfoStream SCHEMA ( orderID integer, customerName string, customerAddress string ) AS SELECT orderStream.orderID, customerRef.fullName, customerRef.address FROM orderStream, customerRef WHERE orderStream.customerID = customerRef.customerID;