12.11. Apache Accumulo Translator The Apache Accumulo Translator, known by the type name accumulo, exposes querying functionality for Accumulo Data Sources. Here are some use cases for this translator: The Accumulo source can be used in JDV, to continually add/update the documents in the Accumulo system from other sources automatically. Access Accumulo through the SQL interface. Make use of cell-level security through enterprise roles. The Accumulo translator can be used as an indexing system to gather data from other enterprise sources such as RDBMS, Web Services, SalesForce and so forth, all in a single-client call transparently with out any coding. Apache Accumulo is distributed key value store with a unique data model. It allows you to group its key-value pairs in a collection called a "table". Define a schema representing Accumulo table structures in JDV using DDL or Teiid Designer with the metadata extension properties defined below. Since no data type information is defined on the columns, by default all of the columns are considered to be string data types. However, during modeling of the schema, one can use various other data types supported through JDV to define a data type of column, that the user wants to expose. Once this schema is defined and exposed through VDB in a JDV database, and Accumulo data sources are created, the user can issue "INSERT/UPDATE/DELETE" based SQL calls to insert/update/delete records into the Accumulo, and issue "SELECT" based calls to retrieve records from Accumulo. Important By default, the Accumulo table structure is flat and thus can not define relationships among tables. A SQL JOIN is performed in the JDV layer rather than pushed to source even if both tables on either side of the JOIN reside in the Accumulo. Currently any criteria based on EQUALITY and/or COMPARISON using complex AND/OR clauses are handled by Accumulo translator and will be properly executed at source. Here is an example Dynamic VDB: The translator does NOT provide a connection to the Accumulo. For that purpose, Teiid has a JCA adapter that provides a connection to Accumulo using Accumulo Java libraries. If you are using the Designer Tooling, to create a VDB create/use a Teiid Designer Model project, use the "Teiid Connection- Source Model" importer, create Accumulo Data Source using data source creation wizard and use accumulo as translator in the importer. The table is created in a source model by the time you finish with this importer. Create a VDB and deploy into Teiid Server and use either jdbc, odbc or odata to query. The Accumulo translator is capable of traversing through Accumulo table structures and build a metadata structure for Teiid translator. The schema importer can understand simple tables by traversing a single ROWID of data, then looks for all the unique keys, based on it and comes up with a tabular structure for Accumulo based table. Using the following import properties, you can further refine the import behavior. Table 12.6. Import Properties Property Name Description Required? Default ColumnNamePattern How the column name is to be formed false {CF}_{CQ} ValueIn Where the value for column is defined CQ or VALUE false {VALUE} Note {CQ}, {CF}, {ROWID} are expressions that you can use to define above properties in any pattern, and respective values of Column Qualifer, Column Familiy or ROWID will be replaced at import time. ROW ID of the Accumulo table, is automatically created as ROWID column, and will be defined as Primary Key on the table. You can also define the metadata for the Accumulo based model, using DDL or using the Teiid Designer. When doing such exercise, the Accumulo Translator currently defines following extended metadata properties to be defined on its Teiid schema model to guide the translator to make proper decisions. The following properties are described under NAMESPACE "http://www.teiid.org/translator/accumulo/2013", for user convenience this namespace has alias name teiid_accumulo defined in JDV. To define an extension property use expression like "teiid_accumulo:{property-name} value". All the properties below are intended to be used as OPTION properties on COLUMNS. See DDL Metadata for more information on defining DDL based metadata. Table 12.7. Extension Metadata Properties Property Name Description Required? Default CF Column Family true none CQ Column Qualifier false empty VALUE-IN Value of column defined in. Possible values (VALUE, CQ) false VALUE Here is an example for a table called "User". A scan returns the following data: If you used the default importer from the Accumulo translator (like the Dynamic VDB defined above), the table will look like this: You can set import property 'ColumnNamePattern' to "{CQ}" to generate the column names based on Column Qualifier instead of the default "{CF}_{CQ}" pattern. Then the metadata will look like this: Using import properties you can dictate how the table is to be modeled. If the column name is defined as {CF}, you can use "ColumnNamePattern" as "{CF}". If the value for that column exists in the Column Qualifier then you can use "ValueIn" as "{CQ}". If you did not use the built-in import (not using Teiid Designer's Teiid Connection >> Source Model or Dynamic VDB), and would like to manually design the table in Designer then you must make sure you supply the Extension Metadata Properties defined above on the User table's columns from Accumulo extended metadata(In Designer, right-click on Model, and select "Model Extension Definitions" and select Accumulo. The Red Hat JBoss Data Virtualization-specific Accumulo Resource Adapter must be used with this translator. You cannot perform native queries or use direct query procedures with this translator.

Commands:
<vdb name="myvdb" version="1"> <model name="accumulo"> <source name="node-one" translator-name="accumulo" connection-jndi-name="java:/accumuloDS"/> </model> <vdb>
root@teiid> table User root@teiid User> scan 1 name:age [] 43 1 name:firstname [] John 1 name:lastname [] Does 2 name:age [] 10 2 name:firstname [] Jane 2 name:lastname [] Smith 3 name:age [] 13 3 name:firstname [] Mike 3 name:lastname [] Davis
CREATE FOREIGN TABLE "User" ( rowid string OPTIONS (UPDATABLE FALSE, SEARCHABLE 'All_Except_Like'), name_age string OPTIONS (SEARCHABLE 'All_Except_Like', "teiid_accumulo:CF" 'name', "teiid_accumulo:CQ" 'age', "teiid_accumulo:VALUE-IN" '{VALUE}'), name_firstname string OPTIONS (SEARCHABLE 'All_Except_Like', "teiid_accumulo:CF" 'name', "teiid_accumulo:CQ" 'firstname', "teiid_accumulo:VALUE-IN" '{VALUE}'), name_lastname string OPTIONS (SEARCHABLE 'All_Except_Like', "teiid_accumulo:CF" 'name', "teiid_accumulo:CQ" 'lastname', "teiid_accumulo:VALUE-IN" '{VALUE}'), CONSTRAINT PK0 PRIMARY KEY(rowid) ) OPTIONS (UPDATABLE TRUE);
CREATE FOREIGN TABLE "User" ( rowid string OPTIONS (UPDATABLE FALSE, SEARCHABLE 'All_Except_Like'), age string OPTIONS (SEARCHABLE 'All_Except_Like', "teiid_accumulo:CF" 'name', "teiid_accumulo:CQ" 'age', "teiid_accumulo:VALUE-IN" '{VALUE}'), firstname string OPTIONS (SEARCHABLE 'All_Except_Like', "teiid_accumulo:CF" 'name', "teiid_accumulo:CQ" 'firstname', "teiid_accumulo:VALUE-IN" '{VALUE}'), lastname string OPTIONS (SEARCHABLE 'All_Except_Like', "teiid_accumulo:CF" 'name', "teiid_accumulo:CQ" 'lastname', "teiid_accumulo:VALUE-IN" '{VALUE}'), CONSTRAINT PK0 PRIMARY KEY(rowid) ) OPTIONS (UPDATABLE TRUE);