12.14. Couchbase Translator
12.14. Couchbase Translator The Couchbase Translator, known by the type name couchbase , exposes querying functionality for Couchbase data sources. The Couchbase Translator provides a solution for integrating Couchbase JSON documents with the relational model. This allows applications to use normal SQL queries against a Couchbase server. The translator converts Red Hat JBoss Data Virtualization push-down commands into Couchbase N1QL. Couchbase is able to store data that does not follow the rules that apply to traditional relational tables and columns. Because metadata and traditional JDBC toolsets might not support these data structures, the data needs to be mapped to a relational form. To achieve this, the Couchbase translator automatically generates schema when the VDB is deployed. Note Alternatively, you can create the schema manually using a source model. The generated schema are tables and procedures. The procedures allow you to execute native queries. The tables are used to map to documents in a specific namespace. There are two kinds of table: Regular Tables: these map to keyspaces. Array Tables: these map to arrays in documents. A table option differentiates regular tables from array tables. To ensure consistent support for your Couchbase data, use importer properties to define your generated schema: Table 12.8. Importer Options Name Description Default sampleSize Set the SampleSize property to the number of documents that you want the connector to sample. 100 sampleKeyspaces This is a comma-separated list of the keyspace names, used to control which keyspaces will be mapped. The smaller the scope of the keyspaces, the larger the sampleSize. Use this if you want to focus on a specific keyspace and want more precise metadata. All typeNameList This is a comma-separated list of key/value pairs that the keyspaces use to specify document types. Each list item must be a keyspace name surrounded by back quotes, a colon, and an attribute name enclosed in back quotes: `KEYSPACE`:`ATTRIBUTE`,`KEYSPACE`:`ATTRIBUTE`,`KEYSPACE`:`ATTRIBUTE` . The keyspaces must be under the same namespace. The attribute must be a non-object or array, resident on the root of keyspace, and its type should be the equivalent String. If a typeNameList that is set on a specific keyspace has multiple types, and a document has all these types, the first one will be chosen. For example, the TypeNameList below indicates that the keyspaces test and default use the type attribute to specify the type of each document. During schema generation, all type referenced values are treated as though they are table names: TypeNameList=`test`:`type`,`default`:`type`:`type` The TypeNameList indicates that the keyspace test use type, name and category attribute to specify the type of each document, during schema generation, the teiid connector scan the documents under test, if a document has attribute as any of type, name and category, its referenced value will be treated as table name: TypeNameList=`test`:`type`,`test`:`name`,`test`:`category` 100 Table 12.9. Additional Table Options Name Description teiid_couchbase:NAMEDTYPEPAIR A NAMEDTYPEPAIR OPTION in table declare the name of typed key/value pair. This option is used once the typeNameList importer property is used and the table is typeName referenced table. teiid_couchbase:ISARRAYTABLE A ISARRAYTABLE OPTION in table used to differentiate the array table from the regular table: A regular table represent data from collections of Couchbase documents. Documents appear as rows, and all attributes that are not arrays appear as columns. In each table, the primary key column named as documentID that that identifies which Couchbase document each row comes from. If no typed name defined the table name is the keyspace name, but in the Couchbase layer, the name of the table will be translate to keyspace name. If a table defined the ISARRAYTABLE OPTION, then it provides support for arrays. Each array table contains the data from one array, and each row in the table represents an element from the array. If an element contains a nested array, an additional virtual table is needed to expand the nested data. In each array table there also has a documentID column play as a foreign key that identifies the Couchbase document from which the array comes and references the documentID from the normal table. An index column (with the suffix _IDX in its name) to indicate the position of the element within the array. Couchbase-supported Red Hat JBoss Data Virtualization data types are String, Boolean, Integer, Long, Double, BigInteger, and BigDecimal. Each table must have a document ID column. It may be arbitrarily named, but it needs to be a string column marked as the primary key. Here are the tables that the Couchbase connector will generate if it is connected to a Couchbase database in which the keyspace named test under the namespace default contains two kinds of documents named Customer and Order. The Customer document is of the type Customer. The SavedAddresses attribute is an array: The Order document is of type Order. The CreditCard attribute is an object, and the Items attribute is an array of objects. When the virtual database deploys and loads the metadata, the connector exposes these collections. The Couchbase Translator supports INSERT, UPSERT, UPDATE, DELETE, SELECT and bulk INSERT statements with a restrictive set of capabilities including: count(*), comparison predicates, Order By, Group By, and LIMIT. Consider a custom extension or create an enhancement request should your usage require additional capabilities. If you are using the Teiid Designer tool, this is how you create the VDB: Click Teiid Connection -> Source Model Create a new JBoss data source connection profile, by specifying Couchbase Data Sources as the JNDI name for the resource adapter. Select Couchbase as the translator type. Click Finish . Create a VDB and deploy into Teiid Server and use either jdbc, odbc or odata to query. Use the DV-specific Couchbase Resource Adapter with this translator. Create source procedures by using the teiid_rel:native-query extension. The procedure will invoke the native query similar to a direct procedure call with the benefits that the query is predetermined and that the result column types are known: Use getDocuments to return JSON documents that match the given document id or id pattern as BLOBs: id : This is the document id or SQL like pattern of what documents to return, for example, the '%' sign is used to define wildcards (missing letters) both before and after the pattern. keyspace : This is the keyspace name used to retrieve the documents. Important Couchbase supports the following data types: String, Boolean, Integer, Long, Double, BigInteger, and BigDecimal. You cannot create a source model with other data types. Each table is expected to have a document ID column. It may be arbitrarily named, but it needs to be a string column marked as the primary key. Note You should always use translator-generated schema. If, however for any reason, you do need to alter the schema manually, do it in this way: Generate the schema using the translator. Use the Web Management Console or the Adminshell to review the schema and make your edits. Add the resulting schema to the VDB. Strictly base your schema on the principles listed in Generating a Schema. Ensure every table has a document ID column. It may be arbitrarily named, but it needs to be a string column marked as the primary key. 12.14.1. Couchbase Data Model Couchbase data is stored in JSON documents. Each document may contain these items: a key pair with no typed value. nested arrays. arrays of differently-typed elements. nested documents which contain the above three items. These items do not follow the rules of data typing and structure that apply to traditional relational tables and columns. To map the documents to a relational form, the Red Hat JBoss Data Virtualization Couchbase connector generates a database schema that maps the Couchbase data to a JDBC-compatible format, using the MetadataProcessor to load the metadata. To map documents to JDBC-compatible tables: the keyspace/bucket is mapped as the top table which contains all of the key/value pairs not including the nested arrays/documents. The key is mapped to the column name and the value type is mapped to column type. The nested arrays/documents are mapped to different tables with names in this format: parenttable_nestarray(ordocument)key. each table has a primary key column mapped to the document id. if a nested array has a nested array item, the array item is treated as an object. Here is an example schema, showing the documents "Customer" and "Order" residing under a keyspace called "test":
<model name="CouchbaseModel"> <property name="importer.sampleSize" value="100"/> <property name="importer.typeNameList" value="`test`:`type`"/> <source name="couchbase" translator-name="translator-couchbase" connection-jndi-name="java:/couchbaseDS"/> </model> { "ID": "Customer_12345", "Name": "John Doe", "SavedAddresses": [ "123 Main St.", "456 1st Ave" ], "type": "Customer" } { "CreditCard": { "CVN": 123, "CardNumber": "4111 1111 1111 111", "Expiry": "12/12", "Type": "Visa" }, "CustomerID": "Customer_12345", "Items": [ { "ItemID": 89123, "Quantity": 1 }, { "ItemID": 92312, "Quantity": 5 } ], "Name": "Air Ticket", "type": "Order" } EXEC CouchbaseVDB.native('DELETE FROM test USE KEYS ["customer-3", "order-3"]') getDocuments(id, keyspace) call getDocuments('customer%', 'test') Customer "ID": "Customer_12345", "Name": "John Doe", "SavedAddresses": [ "123 Main St.", "456 1st Ave" ], "Type": "Customer" Order "CreditCard": { "CVN": 123, "CardNumber": "4111 1111 1111 1111", "Expiry": 12/12", "Type": "Visa" }, "CustomerID": "Customer_12345", "Items": [ { "ItemID": 89123, "Quantity": 1 }, { "ItemId": 92312, "Quantity": 5 } ], "Name": "Air Ticket", "Type": "Order"