Ce contenu n'est pas disponible dans la langue sélectionnée.
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:
<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>
| 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 |
| 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:
|
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:
{
"ID": "Customer_12345",
"Name": "John Doe",
"SavedAddresses": [
"123 Main St.",
"456 1st Ave"
],
"type": "Customer"
}
The Order document is of type Order. The CreditCard attribute is an object, and the Items attribute is an array of objects.
{
"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"
}
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 ->
- Create a new JBoss data source connection profile, by specifying as the JNDI name for the resource adapter. Select as the translator type.
- Click .
- 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:
EXEC CouchbaseVDB.native('DELETE FROM test USE KEYS ["customer-3", "order-3"]')
Use
getDocuments to return JSON documents that match the given document id or id pattern as BLOBs:
getDocuments(id, keyspace)
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.
call getDocuments('customer%', 'test')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":
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"
