Language:
Format:

Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

12.2. Relational View Modeling

12.2.1. Create Materialized Views

For any relational view table you can enable its materialized view option by setting the Materialized property to TRUE and setting the Materialized Table reference, as shown in the figure below. Note that you are required to have already created your relational tables.

Figure 12.11. Materialized Table Properties

Teiid Designer includes a feature to assist in quickly creating materialized relational tables based on your existing view tables.

To create materialized views:

The materialization process is undertaken one view at a time. Select the view to be materialized then right-click the view tables in the Model Explorer View and select the Modeling > Create Materialized Views action. (Note that you should select two or more tables otherwise you will see a different context menu and wizard.)
Figure 12.12.
In the Create Materialized View Model dialog specify or select a target relational model for your generated relational tables.
Figure 12.13.
Selecting the Browse ... button displays the Relational Model Selector dialog where you select an existing relational model or specify a unique name for a new model.
Figure 12.14.
Click OK to create relational tables corresponding to your selected view tables and automatically set the Materialized property to TRUE and the Materialized Table reference value to your newly generated table.

When finished your view tables will be configured with their new materialized properties and the corresponding relational tables will be shown in their package diagram.

Figure 12.15. Materialized Table Properties

12.2.2. JDG Materialization

JDG caches running in client/server mode can use a Red Hat JBoss Data Virtualization-specific JCA connector for accessing as a data source, which is deployed into WildFly 10.0.0 during installation. This connector can be configured to support the accessing of a remote JDG cache using the the Hot Rod client.

Each JDG cache that has a referenced pojo object by doing a "get(key)" on the cache, for which you want to access, will require a different configured resource-adapter.

There are two options for how the JDG schema can be configured in the connector; protobuf annotations or protobuf (.proto) file with marshaller(s). These are the requirements:

Minimum, JDG 6.2 - this requires you provide a protobuf definition file and pojo marshaller(s) for the pojo to configure the JDG schema
Minimum, JDG 6.6 - this can be used when the pojo has defined protobuf annotations which are used to configure the JDG schema.

The following property is required as it provides the mapping to the JDG cache and pojo that will be accessed.

Table 12.1. Mapping

Property Name	Property Template	Description
CacheTypeMap	cacheName:className[;pkFieldName[:cacheKeyJavaType]]	For the indicated cacheName, map the root Java Object (pojo) class name. Optionally, but required for updates, identify which class attribute is the primary key to the cache. Optionally, identify primary key java type when different than class attribute type.

The following properties are required when the protobuf definition file (.proto) and the pojo marshaller(s) are being used to configure the JDG schema:

Table 12.2. Base Execution Properties

Property Name	Required?	Property Template	Description
ProtobufDefinitionFile	Yes	Path to the Google Protobuf file that’s packaged in a jar (ex: /quickstart/addressbook.proto)	NA
MessageMarshallers	Yes	marshaller \[,marshaller,..\]	Contains Class names mapped its respective message marshaller, (class:marshaller,\[class:marshaller,..\]), that are to be registered for serialization
MessageDescriptor	Yes	NA	Message descriptor class name for the root object in cache

The pojo class is the object that is used to store the data in the cache.

If the pojo is to be used to define the schema, then should use the protobuf annotations.

If the protobuf definition and mashaller(s) are to be used, then these should also be packaged in the jar (or a separate jar that is included in the classpath).

The class should be packaged into a jar so that it can be deployed as a module.

public class Person {

   @ProtoField(number = 2, required = true)
   public String name;
   @ProtoField(number = 1, required = true)
   public int id;
   @ProtoField(number = 3)
   public String email;
   private List<PhoneNumber> phones;

   public String getName() {
      return name;
   }

   public void setName(String name) {
      this.name = name;
   }

   public int getId() {
      return id;
   }

   public void setId(int id) {
      this.id = id;
   }

   public String getEmail() {
      return email;
   }

   public void setEmail(String email) {
      this.email = email;
   }

   public List<PhoneNumber> getPhones() {
      return phones;
   }

   public void setPhones(List<PhoneNumber> phones) {
      this.phones = phones;
   }
   }

To configure the resource adapter to use the pojo, deploy the pojo jar as a module in the JBOSS EAP server.

One of the following properties is required for defining how the RemoteCacheManager will be created/accessed:

Table 12.3. Configuration

Property Name	Required?	Property Template	Description
CacheTypeMap	Yes	cacheName:className[;pkFieldName[:cacheKeyJavaType]]	For the indicated cache, map the root Java Object class name. Optionally, but required for updates, identify which class attribute is the primary key to the cache. Identify primary key java type when different than class attribute type
ProtobinFile	Yes	NA	Path to the Google Protobin file that's packaged in a jar (ex: /quickstart/addressbook.protobin)
MessageMarshallers	Yes	marshaller [,marshaller,..]	Contains Class names mapped its respective message marshaller, (class:marshaller,[class:marshaller,..]), that are to be registered for serialization
MessageDescriptor	Yes	NA	Message descriptor class name for the root object in cache
module	No	NA	Specify the JBoss EAP module that contains the cache classes that need to be loaded
CacheJndiName	No	NA	JNDI name to find the CacheContainer
RemoteServerList	No	host:port[;host:port….]	Specify the host and ports that will be clustered together to access the caches
HotRodClientPropertiesFile	No	NA	The HotRod properties file for configuring a connection to a remote cache

The following property should be defined when using protobuf definition file and marshallers:

Table 12.4. Properties

Property Name	Required?	Property Template	Description
module	No	NA	Specify the WildFly module that contains the cache classes that need to be loaded

The following are the additional properties that need to be configured if using the Remote Cache for external materialization:

Table 12.5. Mapping

Property Name	Required?	Description
StagingCacheName	Yes	Cache name for the staging cache used in materialization .
AliasCacheName	Yes	Cache name for the alias cache used in tracking aliasing of the caches used in materialization. This cache can be shared with other configured materializations. .

There are many ways to create the data source, using the CLI, AdminShell, admin-console and so forth. The first example is an XML snippet of a resource-adapter that is used to connect to the JDG remote-query quick start:me Sample Resource Adapter defining Protobuf Definition and Marshaller:

               <resource-adapter id="infinispanRemQS">
                    <module slot="main" id="org.jboss.teiid.resource-adapter.infinispan.hotrod"/>
                    <connection-definitions>
                        <connection-definition class-name="org.teiid.resource.adapter.infinispan.hotrod.InfinispanManagedConnectionFactory" jndi-name="java:/infinispanRemote" enabled="true" use-java-context="true" pool-name="infinispanDS">
                            <config-property name="CacheTypeMap">
                                addressbook:org.jboss.as.quickstarts.datagrid.hotrod.query.domain.Person;id
                            </config-property>
                            <config-property name="ProtobufDefinitionFile">
                                 /quickstart/addressbook.proto
                            </config-property>
                            <config-property name="MessageDescriptor">
                                quickstart.Person
                            </config-property>
                            <config-property name="Module">
                                com.client.quickstart.pojos
                            </config-property>
                            <config-property name="MessageMarshallers">                              org.jboss.as.quickstarts.datagrid.hotrod.query.domain.Person:org.jboss.as.quickstarts.datagrid.hotrod.query.marshallers.PersonMarshaller,org.jboss.as.quickstarts.datagrid.hotrod.query.domain.PhoneNumber:org.jboss.as.quickstarts.datagrid.hotrod.query.marshallers.PhoneNumberMarshaller,org.jboss.as.quickstarts.datagrid.hotrod.query.domain.PhoneType:org.jboss.as.quickstarts.datagrid.hotrod.query.marshallers.PhoneTypeMarshaller
                            </config-property>
                            <config-property name="RemoteServerList">
                                127.0.0.1:11322
                            </config-property>
                        </connection-definition>
                    </connection-definitions>
                </resource-adapter>

Here is a sample Resource Adapter using Pojo with annotations:

               <resource-adapter id="infinispanRemQSDSL">
                    <module slot="main" id="org.jboss.teiid.resource-adapter.infinispan.dsl"/>
                    <connection-definitions>
                        <connection-definition class-name="org.teiid.resource.adapter.infinispan.dsl.InfinispanManagedConnectionFactory" jndi-name="java:/infinispanRemoteDSL" enabled="true" use-java-context="true" pool-name="infinispanRemoteDSL">
                            <config-property name="RemoteServerList">
                                127.0.0.1:11322
                            </config-property>
                            <config-property name="CacheTypeMap">
                                addressbook_indexed:org.jboss.as.quickstarts.datagrid.hotrod.query.domain.Person;id
                            </config-property>
                        </connection-definition>
                    </connection-definitions>
                </resource-adapter>

Here is a sample Resource Adapter for external materialization:

                <resource-adapter id="infinispanRemQSDSL">
                    <module slot="main" id="org.jboss.teiid.resource-adapter.infinispan.hotrod"/>
                    <connection-definitions>
                        <connection-definition class-name="org.teiid.resource.adapter.infinispan.hotrod.InfinispanManagedConnectionFactory" jndi-name="java:/infinispanRemoteDSL" enabled="true" use-java-context="true" pool-name="infinispanRemoteDSL">
                            <config-property name="CacheTypeMap">
                                addressbook_indexed:org.jboss.as.quickstarts.datagrid.hotrod.query.domain.Person;id
                            </config-property>
                            <config-property name="StagingCacheName">
                                addressbook_indexed_mat
                            </config-property>
                            <config-property name="AliasCacheName">
                                aliasCache
                            </config-property>
                            <config-property name="Module">
                                com.client.quickstart.addressbook.pojos
                            </config-property>
                            <config-property name="RemoteServerList">
                                127.0.0.1:11322
                            </config-property>
                        </connection-definition>
                    </connection-definitions>
                </resource-adapter>

The HotRod Translator, known by the type ispn-hotrod, can read the java objects from a remote JDG Cache via the Hot Rod client using the Google Protobuf for serialization. This will enable Teiid to query the remote cache using JDG DSL. This translator extends the Object Translator and uses it for the core processing of reading and writing objects. It can do the following:

Retrieve objects from a cache and transform into rows and columns.
Supports performing writes to the cache
Use for external materialization to improve query performance

The following are the connector capabilities:

Compare Criteria - EQ
Compare Criteria Ordered - LT, GT, LE, GE - support for SupportsComapareCriteriaOrdered will be controlled by the version of JDG being accessed. Any JDG version 6.5 and prior will have this set to false due to an issue with JDG.
And/Or Criteria
In Criteria
Like Criteria
Order By
INSERT, UPDATE, DELETE (non-transactional)

The following will not be pushed down to JDG for processing, but will be done within Red Hat JBoss Data Virtualization:

Not (NE)
IsNull

These are its limitations:

support for 'Not' has been disabled
boolean data type: JDG will throw an exception if no value is specified on the insert or when no default value is defined in the protobuf definition file.
char data type: is not a supported type in theProtobuf data types (https://developers.google.com/protocol-buffers/docs/proto#scalar). Would either have to handle conversion in the protobuf marshaller or create a Teiid view with the data type as char.
1-to-Many, currently only supports Collection or Array, not Maps
Write transactions not supported by JDG when using Hot Rod client

There are several options to defining the metadata representing your object in the cache. Red Hat recommmends that you use the Teiid Connection Importer in Teiid Designer to create the physical source model based on your object cache. The table columns will be created from the google protobuf definition, that corresponds to a registered class.

Use Teiid Designer to manually create the physical source model based on your object cache using the below Definition Requirements:

    <model name="People" type="Physical">
        <property name="importer.useFullSchemaName" value="false"/>

        <source name="infinispan-hotrod-connector" translator-name="ispn-hotrod" connection-jndi-name="java:/infinispanRemoteDSL" />
    </model>

The metadata will be resolved by reverse engineering the defined object in the cache. This can be useful when using the Teiid Designer Teiid Connection Importer for building the physical source model(s).

You can also define the metadata using DDL. See the Object Translator for an example.

See the Object Translator Metadata section for base definition requirements.

Columns will be identified as SEARCHABLE if either the protobuf definition for a column indicates its indexed or the pojo class has the attribute/method annotated.

A 1-to-* relationship class must have a foreign key to map to the root class/table, where the name in source for the foreign key is the name of the root class method to access those child objects. Note, this is the class method, not a reference in the google protobuf definition.

A container/child class will have attributes where the NIS contain a period. Example: phone.number. This is because this maps to to google protobuf definition and what is expected to be used in the DSL query.

This translator supports using the cache for external materialization. However, there are specific configuration changes that are required at the [Infinispan-HotRod resource-adapter] and at the translator.

External materialization is enabled by the use of native queries in the BEFORE_LOAD_SCRIPT and AFTER_LOAD_SCRIPT. A translator override will need to be set to enable native queries: SupportsNativeQueries=true

The following materialization properties must be defined:

Table 12.6. Mapping

Script	Native query	Description
teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT	truncate cache	To truncate the cache identified as the staging cache.
teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT	swap cache names	To swap the aliases for the caches, so that the primary cache points to the recently loaded cache.

This is how you define the load scripts in DDL:

..
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute StockMatCache.native(''truncate cache'');',
"teiid_rel:MATVIEW_LOAD_SCRIPT" 'insert into StockMatCache.Stock (productId, symbol, price, companyName) SELECT  A.ID, S.symbol, S.price, A.COMPANY_NAME FROM Stocks.StockPrices AS S, Accounts.PRODUCT AS A WHERE S.symbol = A.SYMBOL',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT"  'execute StockMatCache.native(''swap cache names'');',

Native queries are used to simulate how its done using RDBMS and renaming tables, because JDG doesn’t currently support renaming a cache. So the native queries will trigger the clearing of the "staging" cache, and the swapping of the cache aliases.

Additionally, the execution of native queries is done through the support of direct query procedures. The procedure to be executed is called native.

Warning

This feature is turned off by default because of the security risk this exposes to execute any command against the source. To enable this feature, override the execution property [Override Execution Properties] called SupportsDirectQueryProcedure to true.

If you manually model the cache table in Teiid Designer, then you will need to add the property extension for defining the property "primary_table":

SET NAMESPACE 'http://www.teiid.org/translator/object/2016' AS n0;

CREATE FOREIGN TABLE Trade (
         ....
	CONSTRAINT PK_TRADEID PRIMARY KEY(tradeId)
) OPTIONS (UPDATABLE TRUE);

CREATE FOREIGN TABLE ST_Trade (
        ....
) OPTIONS (NAMEINSOURCE 'Trade', UPDATABLE TRUE, "n0:primary_table" 'ObjectSchema.Trade');

Note

If you do manual modeling, your column name must match that of the POJO.

See the JDG HotRod Data Sources resource adapter for this translator. It can be configured to look up the cache container via JNDI, server list, or hot rod properties.

Important

It is highly recommended that you use the Teiid Designer tooling wizards (such as the Teiid Connection Importer and/or Materialize) to create the table and column names. If, instead, you use a VDB XML form which points to a JDG resource adatper, do not define the DDL metadata but, rather, let the translator expose the metadata.

The reason for this is that the configuration of the JDG schema is case sensitive. The 'name' used in the query to JDG must match the name of the property (methodName) that was used.

Using annotations, the pojo method names are used to configure the JDG schema, by passing the Class to the configuration builder.
Using protobuf/mashallers, the JDG schema is configured by using the protobuf file.

Both configuration methods enable the JDG cache to provide a descriptor, for which the names of the columns (and nameInSources when using complex objects) are derived. It is these names that must be used in the model.

12.2.3. Create Relational View Table Wizard

You can create relational view tables by clicking New Child > Table... action. This action also includes creating view tables with its target columns and SQL transformation.

Figure 12.16. New View Table Wizard Action

Running the action will display the Create Relational View Table wizard. The wizard page contains tabbed panels representing the various properties and components that make up the possible definition of a relational view table. Enter your table name, define the desired columns and specify the SQL transformation, then click OK.

This wizard is designed to provide feedback as to the completeness of the relational view table information, as well as the validation state of the table and its components. Note that although errors or warnings may be displayed during editing, the wizard is designed to allow finishing even if the table definition is incomplete.

The first tab labeled Properties contains the input for the simple table properties including name and supports update.

Figure 12.17. Properties Tab

The Columns tab allows creation and editing of basic relational columns. This includes adding, deleting or moving columns as well as changing the name, datatype and length properties.

Figure 12.18. Columns Tab

The Transformation SQL tab allows editing of the SQL Transformation for the relational view. The desired SQL can be entered directly into the text area or a SQL Template may be selected by clicking the Select SQL Template button.

Figure 12.19. Transformation SQL Tab

If the Select SQL Template button is selected on the Transformation SQL tab, the Choose a SQL Template dialog is displayed.

Figure 12.20. SQL Templates Dialog

A number of common SQL templates may be chosen using the dialog. Depending on usage context, the PROCEDURES tab may also be available in addition to the SELECT queries tab. The SQL Template dialog can also be accessed in the Transformation Editor, from the right-click context menu.

Select Your Language

Red Hat Training

12.2. Relational View Modeling

12.2.1. Create Materialized Views

12.2.2. JDG Materialization

12.2.3. Create Relational View Table Wizard

Quick Links

Help

Site Info

Related Sites

About

Red Hat legal and privacy links

Red Hat legal and privacy links

Language and Page Formatting Options

Red Hat Training

12.2. Relational View Modeling

12.2.1. Create Materialized Views

12.2.2. JDG Materialization

12.2.3. Create Relational View Table Wizard

Quick Links

Help

Site Info

Related Sites

Systems Status

About

Red Hat legal and privacy links

Red Hat legal and privacy links