Red Hat Summit12.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. Red Hat JBoss Data Grid Materialization
JBoss Data Grid 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 JBoss Data Grid cache using the the Hot Rod client.
12.2.2.1. JBoss Data Grid 6 Materialization
Each JBoss Data Grid 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.
| 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:
| 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:
| 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:
| 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:
| 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:
| 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.2.2. JBoss Data Grid 7 Materialization
You can use Red Hat JBoss Data Grid (JDG) 7 as a target data source for external materialization to improve query result performance.
Prerequisites
- JBoss Data Virtualization 6.4 and above
- JBoss Data Grid 7.1.2 and above
- Configure the primary and staging cache and the
teiid-alias-naming-cachecache in JDG. For details, see the External Materialization and Red Hat JBoss Data Grid section in the Development Guide Volume 5: Caching Guide for JBoss Data Virtualization 6. - Configure the translator and the
jdgmodule. For details, see the Configure Red Hat JBoss Data Grid Connectors section in the Installation Guide for JBoss Data Virtualization 6.
Important
Due to a bug, if the server does not contain the
infinispan-hotrod-translator extension or Model Extension Registry is not synchronized with the server, an attempt to create the JDG source model fails and the NullPointerException message appears in the log. To work around this issue, make sure that the Model Extension view includes infinispan-hotrod-translator. If the view does not include infinispan-hotrod-translator, ensure that the server contains it and refresh the server in the Teiid Designer Connection view.
Procedure 12.1. Configuring JBoss Data Grid 7 Materialization
- Create a JDG source model. In the Teiid Designer, right-click on the view to materialize and select Modeling > Materialize.Make sure, that the view has a primary key defined because the Red Hat JBoss Data Grid source table requires the key for updates. Otherwise, manually create a primary key on each of the new JDG source tables.
Figure 12.16. Generate Materialized JDG Module
- Enter the primary and staging cache names.
- Optionally, change the model name and the directory in which the model is saved. Note that Red Hat JBoss Data Grid restricts the name of the source model because the
protobufcode is based on the Java package naming constraints. The model name becomes the package name in the.protofile. This is due to a limitation in the way thatprotobufis defined. Because Red Hat JBoss Data Grid uses Java, the package name must follow the Java package naming standards. Dashes, for instance, are not allowed. - Click .
- Create a new connection profile.
- In the view click .
- Choose JDG 7.x, set name, and click .
- Set properties. Specify the Java Naming and Directory Interface (JNDI) Name, which is the name of the JDG Source model.For details about other properties to set, see the Red Hat JBoss Data Grid 7.x Configuration section in the Administration and Configuration Guide for JBoss Data Virtualization 6.
Figure 12.17. New connection profile
- Click .
- Add the connection profile to the created JDG Source model. Right-click the JDG Source Model and select Modeling > Set connection profile.
- Create Data Source for the JDG Source model. Right-click JDG Source Model and select Modeling > Create Data Source.
- To control the materialization process, update the materialized view extension properties on the above selected view:
- MATVIEW_TTL - to set the refresh rate, in milliseconds
- If you use the materialization management status table, set the following extension properties:
- ALLOW_MATVIEW_MANAGEMENT = true
- MATVIEW_STATUS_TABLE = {status table name}
- Create the Virtual Database (VDB), using the models needed for materialization.
- For the JDG Source model, be sure the JNDI is mapped to the JDG data source.
- Enable native queries. To do this, create a translator override for the
infinispan-hotrodtranslator. Click the property and set the value to .
- Create a VDB XML file. Right-click the VDB and select Modeling > Generate VDB XML.
Important
Due to a bug, the VDB must be deployed to the server as a VDB XML file. - Deploy the VDB XML file. Right-click the VDB XML file and select Modeling > Deploy
12.2.3. Create Relational View Table Wizard
You can create relational view tables by right-clicking on view model and select New Child > Table... action. This action also includes creating view tables with its target columns and SQL transformation.
Figure 12.18. New View Table Wizard Action
Running the action will display the Create Virtual Table Options wizard. You have two options how to create a table:
Figure 12.19. Create Virtual Table Options wizard
12.2.3.1. Build with new table wizard
Select the option 1 and click OK. Next will be display 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.20. 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.21. 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.22. 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.23. SQL Templates Dialog
A number of common SQL templates may be chosen using the dialog on the left side of wizard. Also you can choose Insert Text Options on the right side. You can create and update SQL Definition query for table. The SQL Template dialog can also be accessed in the Transformation Editor, from the right-click context menu and click to Apply Template....
12.2.3.2. Build using columns from source model
Select the option 2 and click OK. Next will be display Select Source Table wizard. Select table from what you want to create virtual table and click OK. After that Select Columns for View dialog shows.
Figure 12.24. Select Columns for View dialog
This dialog has prefilled labels but you can change it.
- Source table is table from what you want to build your virtual table.
- Target View Model is your view model to where you want to build your virtual table.
- View Name is name of your new virtual table.
Also you can pick which columns from source table will be in your virtual table. After that click OK and your virtual table will be created.
