Using SQL-based instance tables

SQL-based instance tables support the use of an SQL query for the specification of a data attachment. (See SQL-based instance table for a description of the contents of this type of table.) By using these tables, you can simplify your implementation of complex filtering, and improve performance for dashboards that must handle a large number of DataView items. In particular, SQL-based instance tables have the following potential advantages over other types of data tables (which require you to use the standard fields of the Attach to Apama dialog):

Filtering is optimizable. You can specify indexes which Apama can use to join data tables and filter data attachments more efficiently. This can dramatically improve performance, particularly for large data tables (that is, tables with thousands of rows or more).

A single attachment specification can refer to multiple tables, including tables from multiple correlators. This can simplify implementation, which would otherwise require attaching properties to dashboard functions whose arguments are attached to data tables.

Arbitrarily complex filtering and data aggregation is supported, since any read-only SQL select statement can be used. This can simplify implementation, which would otherwise require complex chains of dashboard functions.

Important:

When SQL-based data tables are in use for deployed dashboards, authorization for DataView items does not use scenario authorities (see Administering authentication). By default, all users have access to all instances or items. Authorization must be built into attachment queries.

To attach an object property to Apama data by using an SQL-based instance table

1. Ensure that Builder has been started with the -Y or --enhancedQuery command-line option.

2. Right-click the property in the property panel.

A popup menu appears.

3. In the displayed popup menu pick Attach to Data > Apama.

The Attach to Apama dialog appears.

4. In the Attach to field select Instance table query.

This changes the Attach to Apama dialog, so that there is a single remaining field, SQL Statement.

5. Enter an SQL query into the text box.

Any read-only select statement is allowed, with the following restrictions and modifications:

You must designate tables with table names of the form correlator-name.DataView-ID.

You can designate values with predefined or user-defined dashboard substitution variables (for example, $apama_user or $instanceId).

You must enclose table names and column names in quotes.

You must enclose strings in single quotes.

As you construct your query, you can right-click to get suggestions for table names, column names, or substitution variables.

Note:

Errors in the SQL query are logged in the dashboard log file.

Following is an example of a query that you can use to specify a data attachment. It specifies a three-way join, that is, a join involving three different data tables:

SELECT "prod_name", "vend_name", "prod_price", "quantity"
  FROM "Correlator2.DV_OrderItems_Table", "Correlator1.DV_Products_Table",
    "Correlator1.Scenario_Vendors_Table"
  WHERE "Correlator1.DV_Products_Table"."vend_id" =
"Correlator1.Scenario_Vendors_Table"."vend_id"
    AND "Correlator2.DV_OrderItems_Table"."prod_id" =
      "Correlator1.DV_Products_Table"."prod_id"
    AND "Correlator2.DV_OrderItems_Table"."order_num" = 20007

Below is a query that filters out instances that are not owned by the current dashboard user. The example assumes that there is a DataView field, owner, whose value is the instance owner.

SELECT "prod_id", "prod_price"
FROM "Correlator1.Scenario_Vendors_Table"
WHERE "Correlator1.Scenario_Vendors_Table"."owner" = '$apama_user'

To specify indexes into an SQL-based data table, use the --queryIndex option on the command line when you do any of the following:

Start the data server or display server

Start the Dashboard Builder with a direct connection to the correlator

Start the Dashboard Viewer with a direct connection to the correlator

This option has the form

--queryIndex table-name:key-list

table-name is the name of a DataView. key-list is a comma-separated list of variable names or field names. Here is an example:

--queryIndex DV_Products_Table:prod_id,vend_id

You can only add one index per table, but you can specify this option multiple times in a single command line in order to index multiple tables. Deployed dashboards that use SQL-based instance tables must be connected to a data server or display server that is started with the -Y or --enhancedQuery command-line option. For deployed dashboards that use Viewer connected directly to a correlator, Viewer must be started with the -Y or --enhancedQuery command-line option.