Table Mappings and Filtered Data Aliases

This page describes the Spotfire LiveView features that allow you to:

  • Assign mappings, aliases, and filtered aliases to rename a remote data source configured with an External Server Connection type of lvconf file. Remote data sources include StreamBase Query Tables, remote LiveView tables, and TIBCO ActiveSpaces tables.

  • Assign one or more table name aliases to a local or remote data table, along with a related predicate filter for each, such that a filtered table alias serves as a read-only view of the data in that data table.

Both remote table mappings and filtered table aliases are illustrated in the LiveView Services-Only sample.

External Server Connections, Mappings, and Aliases

One of the basic types of LiveView Configuration files (lvconf files) is the External Server Connection type. This configuration is used to define a source of data for a base LiveView data table, where that source is one of:

  • Another LiveView data table, including the results of a Transformation, Aggregation, or Preprocessed table.

  • One or more Spotfire StreamBase Query Tables in a running StreamBase application module.

  • A non-StreamBase table type, where you provide a custom table reading class.

  • JDBC data source.

  • ActiveSpaces data source.

When connecting to a remote data source, you can use either the Mappings feature, or the filtered alias feature, or both. Use the Mappings feature to convert table names from a non-StreamBase table type's allowed character set to the more restrictive table name character set in LiveView. Use the filtered alias feature to provide filtered read-only versions of a data table, such as US and UK versions of a Sales table.

Create an External Server Connection by creating a new, empty lvconf file in StreamBase Studio. Follow these steps:

  1. Invoke File>New>Spotfire LiveView Configuration File.

  2. In the New StreamBase LiveView Configuration dialog:

    1. Select or type the name of a project folder in the Studio workspace.

    2. Specify a name for this lvconf file.

    3. In the Type area, select External Server Configuration.

    4. Click Finish.

    5. The lvconf editor opens, ready to complete the lvconf file just created.

External Server Connection Tab

Use the External Server Connection tab to specify the type of external server, to specify its location by specifying parameters, and to specify retry settings.

In the Type control, use the dropdown list to select one of the following five options:

CEP Query Table

Use this selection to specify that the external data source is a Query Table in a running StreamBase application. With this selection, you must also double-click the uri parameter to specify the full StreamBase URI in sb:// or sbs:// format. Specify the path down to the container that holds the Query Table of interest, if applicable. All Query Tables in the specified StreamBase URI become available to serve from LiveView.

LiveView

Use this selection to specify that the external data source is from another LiveView table running on the same system or remotely. When using this option, you must specify at least the uri parameter to specify one or more LiveView URIs in lv:// or lvs:// format. The other options shown in the Table Provider Parameters field have reasonable default settings and do not need to be set.

JDBC

Use this selection when the external data source supports JDBC. You must configure the data source in the HOCON JDBCDataSourceGroup (type = com.tibco.ep.streambase.configuration.jdbcdatasource) file and specify at least the dataSourceRefName parameter in the lvconf file, which refers to the data source configured in the HOCON JDBCDataSourceGroup, while configuring this type. Run the LiveView JDBC Table sample to learn more about this external data source.

ActiveSpaces

Use this selection to specify that the external data source is TIBCO ActiveSpaces. Run the LiveView ActiveSpaces sample to learn more about using this external data source.

Custom Table Provider

Use this selection to specify that the external data source is in another format not provided by StreamBase or Spotfire LiveView. In this case, you must also specify the fully qualified path to a class in the classpath of the current Studio project. The specified class must know how to locate, read data from, and interpret the custom table type. Remember that the classpath when running projects in Studio is not necessarily the same as when running the same project from the command line. Use the Table Provider Parameters section to specify any parameters needed to locate the custom table.

Whichever server connection type you specify, the Retry Parameters apply equally well. Specify the interval in seconds between retry attempts and number of retries to attempt before abandoning the connection.

Mappings Tab

All settings in the Mappings tab are optional and can be left blank.

Use the Mappings field to combine a regular expression in the Selector column on the left with a formatting string in the Formatter column on the right. Use formatting strings in the manner of the Java.lang.String.format() method (which uses java.util.Formatter conventions).

In addition to Selector and Formatter attributes, the Ignore attribute, when set to true, ignores all tables from the external connection that match this mapping. To set the Ignore attribute, you must configure the lvconf file's XML in the Source tab or a text editor. The attribute is not currently available in the Mappings tab. For example:

<?xml version="1.0" encoding="UTF-8"?>
<liveview-configuration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:noNamespaceSchemaLocation="http://www.streambase.com/schemas/lvconf/">
   <external-server-connection id="remoteLiveViewConnectionTest" type="LiveView">
      <parameters>
          <parameter key="uri">sb://localhost:11080/</parameter>
      </parameters>
      <mappings>   
        <mapping selector=".*demo.*" formatter="%s" >
          <filtered-table-aliases>
          <filtered-table-alias filter="y=2 " table-name-formatter="%s_Remote_TWO" />
          <filtered-table-alias filter="y=3 " table-name-formatter="%s_Remote_THREE" />
          </filtered-table-aliases>
        </mapping>
        <mapping selector="(.*)" ignore="true" />
      </mappings>
      <retry-parameters wait-seconds="10"  maximum-failed-attempts="0" />
    </external-server-connection>     
</liveview-configuration>

The mappings feature is not normally required when using the CEP Query Table or LiveView types of external server connections. When using a Custom Table Provider type, use mappings to convert table names and paths from the remote connection to a form usable by LiveView. For example, use mappings to convert table names from a non-StreamBase table type's allowed character set to the character set allowed in LiveView, where table and field names are restricted to certain rules:

  • The name must begin with an alphabetic character or an underscore.

  • The name can contain only alphabetic characters, numerals, and underscores.

  • The name cannot contain hyphens, colons, periods, or other non-alphabetic characters.

The Table Aliases and Filter fields in the Aliases for selected mapping section of the Mapping tab are used in exactly the same way as in the Aliases tab for Data Table lvconf types, as described in the next section. The only difference is that for external sources, each alias and/or filter setting applies to the currently selected mapping in the Mappings section of this tab.

Using the Alias Tab for Data Tables

LiveView configuration files of the Data Table type have an Aliases tab when opened in the lvconf editor. All settings in this tab are optional, and can be left blank.

This tab contains two fields:

Table Aliases

Use this field to specify an alias format string that will provide a new name pattern for data that matches the predicate filter specified in the Filter field. Use formatting strings in the manner of the Java.lang.String.format() method (which uses java.util.Formatter conventions).

When you click the green Plus sign to add a new alias, the default format string is %s_alias. This accepts the current table name and appends an underscore and the string alias. Adjust this default as required.

For example, in the Hello LiveView sample, in the ItemsSales.lvconf file, you can specify an alias of %s_Toys. Since the table name for this configuration is the basename of the lvconf file, ItemsSales, the resulting alias becomes ItemsSales_Toys.

Filter for (selected table alias)

Select an alias specification in the Table Aliases column, then specify a predicate filter in the Filter for column. The filter is a StreamBase expression that is to be ANDed with the predicate that creates the table in a LiveView client application. That is, the predicate expression you enter here is ANDed with the WHERE clause, if any, in the SELECT statement that queries the server.

Continuing the example above, the Filter expression corresponding to the Table Alias format string %s_Toys might be category == 'toy'

You can use either single quotes in your filter expression as shown, or double quotes. Single quotes result in cleaner-looking XML for the underlying lvconf file, because the filter expression attribute is already within double quotes. However, if you use double quotes, they are automatically converted into quote entities and are read correctly.

The combination of a table alias and filter expression creates a read-only filtered view of the table being configured. For example, for a Sales table, you can specify both US and UK views of the Sales table named Sales_US and Sales_UK that each shows only the location-specific sales data to appropriate audiences. Your application still makes all table updates only to the underlying base table, Sales. Those changes are immediately reflected in both filtered alias table views.