12.17. Red Hat JBoss Data Grid Translator The Red Hat JBoss Data Grid translator, known by the type name infinispan-hotrod , exposes the Red Hat JBoss Data Grid cache store that is to be queried using SQL. The translator uses the HotRod protocol to connect the remote JDG cluster farm. This translator does not work with any arbitary key/value mappings. However, if the JDG store is defined with the probuf file then this translator works with the definition objects found in that file. The resource adapter for this translator is the Infinispan Data Source. This sample VDB reads metadata from a protobuf file based on the AddressBook quick start: A connection to Red Hat JBoss Data Grid is required. Here is an example configuration for the resource adapter. Be sure to edit the "RemoteServerList" to reflect your server's location. Connect to the VDB using the JDBC driver and issue SQL statements like this: There are three different ways to define the metadata for the model. Choose the one that best fits your needs. In the first method, the user can register a .proto file with the translator configuration, which is read and converted to the model's schema. This file is then registered in Red Hat JBoss Data Grid. In the second method, if the protobuf file has already been registered in your Red Hat JBoss Data Grid node, Red Hat JBoss Data Virtualization can obtain it and read the protobuf directly from the cache: The third method is to use metadata tags to define the DDL directly: Note Red Hat JBoss Data Grid restricts the name of the source model because the protobuf code is based on the Java package naming constraints. The model name becomes the package name in the .proto file. This is due to a limitation in the way that the protobuf is 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. For this option, a compatible protobuf definition is generated automatically during the deployment of the VDB and registered in Red Hat JBoss Data Grid. Please note, if for any reason the DDL is modified (Name changed, type changed, add/remove columns) after the initial VDB is deployed, then version of the protobuf file and data contents need to be manually cleared before revision of the VDB is deployed. Failure to clear will result in data encoding/corruption issues. Here is a protobuf file that has been converted to a relational schema: This DDL is generated: Table 12.11. Mappings JBoss Data Grid Mapped to Relational Entity Example Message Table. Person. enum Integer attribute in table. NA. repeated Use as an array for simple types or as a separate table with one-to-many relationship to parent message. PhoneNumber. All required fields are modeled as non-null columns. All indexed columns are marked as searchable. The default values are captured. To enable updates, the top level message object must define the @id annotation on one of its columns. Note Notice the @Id annotation on the Person message's "id" attribute in protobuf file. This is not defined by JDG, but required by Red Hat JBoss Data Virtualization to identify the key column of the cache entry. In the absence of this annotation, only "read only" access (SELECT) is provided to top level objects. Any access to complex objects will not be provided. When the .proto file has more than one single top level "message" object to be stored as the root object in the cache, each of the objects must be stored in a different cache to avoid key conflicts in a single cache store. Since each of the messages will be in a different cache store, you can define the cache store name for the "message" object. For this, define an extension property called teiid_ispn:cache on the corresponding Red Hat JBoss Data Virtualization table: There are no defined execution properties for this translator. Importer properties define the behavior options of the translator during the metadata import from the physical source. Table 12.12. Mappings Name Description Default ProtoFilePath The file path to a Protobuf .proto file accessible to the server to be read and convert into metadata. NA. ProtobufName The name of the Protobuf .proto file that has been registered with the JDG node, that Red Hat JBoss Data Virtualization will read and convert into metadata. The property value must exactly match the registered name. NA. Here are the translator's limitations: Bulk update support is not available. Transactions are not supported. The last edit stands. Aggregate functions like SUM, AVG etc are not supported on inner objects. UPSERT support on complex objects is always results in INSERT. LOBS are not streamed, use caution as this can lead to OOM errors. There is no function library in JDG. Array objects can not be projected but they do show up in the metadata. When using DATE/TIMESTAMP/TIME types in Teiid metadata, they are by default marshaled into a LONG type in JDG. SSL and identity support is not currently available. Native or direct query execution is not supported. Important The infinispan-hotrod translator requires the default cache to be configured. If you remove the default cache because you do not need it, you will encounter a failure. This is because the default cache is referenced. To work around this issue, ensure the default cache is available and correctly configured.

Commands:
<vdb name="addressbook" version="1"> <model name="ispn"> <property name="importer.ProtobufName" value="addressbook.proto"/> <source name="localhost" translator-name="infinispan-hotrod" connection-jndi-name="java:/ispnDS"/> <metadata type = "NATIVE"/> </model> </vdb>
<resource-adapter id="infinispanDS"> <module slot="main" id="org.jboss.teiid.resource-adapter.infinispan.hotrod"/> <transaction-support>NoTransaction</transaction-support> <connection-definitions> <connection-definition class-name="org.teiid.resource.adapter.infinispan.hotrod.InfinispanManagedConnectionFactory" jndi-name="java:/ispnDS" enabled="true" use-java-context="true" pool-name="teiid-ispn-ds"> <config-property name="RemoteServerList"> localhost:11222 </config-property> </connection-definition> </connection-definitions> </resource-adapter>
select * from Person; select * PhoneNumber where number = <value>; insert into Person (...) values (...); update Person set name = <value> where id = <value>; delete from person where id = <value>;
<vdb name="vdbname" version="1"> <model name="modelname"> .. <property name="importer.ProtoFilePath" value="/path/to/myschema.proto"/> .. </model> </vdb>
<vdb name="vdbname" version="1"> <model name="modelname"> .. <property name="importer.ProtobufName" value="existing.proto"/> .. </model> </vdb>
<model name="ispn"> <source name="localhost" translator-name="infinispan-hotrod" connection-jndi-name="java:/ispnDS"/> <metadata type = "DDL"> CREATE FOREIGN TABLE G1 (e1 integer PRIMARY KEY, e2 varchar(25), e3 double) OPTIONS(UPDATABLE true, , "teiid_ispn:cache" 'g1Cache'); </metadata> <metadata type = "NATIVE"/> </model>
package quickstart; /* @Indexed */ message Person { /* @IndexedField */ required string name = 1; /* @Id @IndexedField(index=false, store=false) */ required int32 id = 2; optional string email = 3; enum PhoneType { MOBILE = 0; HOME = 1; WORK = 2; } /* @Indexed */ message PhoneNumber { /* @IndexedField */ required string number = 1; /* @IndexedField(index=false, store=false) */ optional PhoneType type = 2 [default = HOME]; } /* @IndexedField(index=true, store=false) */ repeated PhoneNumber phone = 4; }
CREATE FOREIGN TABLE Person ( name string NOT NULL OPTIONS (ANNOTATION '@IndexedField', SEARCHABLE 'Searchable', NATIVE_TYPE 'string', "teiid_ispn:TAG" '1'), id integer NOT NULL OPTIONS (ANNOTATION '@Id @IndexedField(index=false, store=false)', NATIVE_TYPE 'int32', "teiid_ispn:TAG" '2'), email string OPTIONS (SEARCHABLE 'Searchable', NATIVE_TYPE 'string', "teiid_ispn:TAG" '3'), CONSTRAINT PK_ID PRIMARY KEY(id) ) OPTIONS (ANNOTATION '@Indexed', NAMEINSOURCE 'quickstart.Person', UPDATABLE TRUE, "teiid_ispn:cache" 'personCache'); CREATE FOREIGN TABLE PhoneNumber ( number string NOT NULL OPTIONS (ANNOTATION '@IndexedField', SEARCHABLE 'Searchable', NATIVE_TYPE 'string', "teiid_ispn:TAG" '1'), type integer DEFAULT '1' OPTIONS (ANNOTATION '@IndexedField(index=false, store=false)', NATIVE_TYPE 'PhoneType', "teiid_ispn:TAG" '2'), Person_id integer OPTIONS (NAMEINSOURCE 'id', SEARCHABLE 'Searchable', "teiid_ispn:PSEUDO" 'phone'), CONSTRAINT FK_PERSON FOREIGN KEY(Person_id) REFERENCES Person (id) ) OPTIONS (ANNOTATION '@Indexed', NAMEINSOURCE 'quickstart.Person.PhoneNumber', UPDATABLE TRUE, "teiid_ispn:MERGE" 'model.Person', "teiid_ispn:PARENT_COLUMN_NAME" 'phone', "teiid_ispn:PARENT_TAG" '4');
<model name="ispn"> <property name="importer.ProtobufName" value="addressbook.proto"/> <source name="localhost" translator-name="infinispan-hotrod" connection-jndi-name="java:/ispnDS"/> <metadata type = "NATIVE"/> <metadata type = "DDL"> ALTER FOREIGN TABLE Person OPTIONS (SET "teiid_ispn:cache" '<cache-name>'); </metadata> </model>
<vdb name="vdbname" version="1"> <model name="modelname"> .. <property name="importer.ProtoFilePath" value="/path/to/myschema.proto"/> .. </model> </vdb>