MongoDB Translator

MongoDB translator provides relational view to the data that resides in MongoDB database. This translator is capable of converting Teiid SQL based queries into MongoDB based queries. It supports full range of SELECT, INSERT, UPDATE and DELETE calls.

As you may already know MongoDB is document based "schema-less" database with it own query language, it does not really map perfectly with relational concepts or SQL language. Not all applications developed in an enterprise need full transactional relational stores like Oracle, PostgreSQL etc, more and more users are using MongoDB kind of NOSQL stores for scalability and performance. Applications like storing audit logs, is perfectly fine to use database like MongoDB. MongoDB uses document as its primary storage unit, where it can have additional embedded documents inside the parent document. By using this embedded documents it co-locates the related information that achieves the de-normalization that is hard to achieve in the relational database. The same is done in relational database using JOIN between two or more tables.

To make MongoDB work with Teiid, the challenge for MongoDB translator is "How best one can design MongoDB store that can achieve the balance between relational and document based storage?" In our opinion the advantages of "schema-less" design is great at the time of development and not really useful in many different applications at runtime. "Schema-less" can also be a problem with migrations and ability to query.

MongoDB translator works with above problem and provides some pre-defined solutions in designing the MongoDB storage. Since it is hard and may be impossible in certain situations to derive a schema based on existing the MongoDB collection(s), Teiid approaches the problem in reverse compared to other translators. When working with MongoDB, Teiid requires the user to define the MongoDB schema upfront using Teiid metadata. Since Teiid only allows to define relational schema as its metadata, user needs to define their MongoDB schema in relational terms using tables, procedures, functions. For the purposes of MongoDB, the Teiid metadata has been extended to support few extension properties that can be defined on the table to convert into a MongoDB based document. These extension properties let user define, how a MongoDB document is structured and stored. Based on the relationships (primary-key, foreign-key) defined on table, the related data based on ONE-to-ONE, ONE-to-MANY, MANY-to-ONE relations can be embedded in the parent document for co-location (see de-normalization comment above). Currently direct mapping of MANY-to-MANY is not supported.

Who are primary audience for this translator?

After reading above, it may be that this may not satisfy every user's needs of using the MongoDB. The document structure in MongoDB can be more complex than what Teiid can currently define. We hope this will eventually catch up in future versions of Teiid. This is designed for

1. Users that are using relational databases and would like to move/migrate their data to MongoDB and may not want to rewrite their applications to take advantages scaling and performance.

2. Users that are starting out with MongoDB and do not have experience with MongoDB but seasoned SQL developers. This provides a low barrier compared to using MongoDB directly as application developer.

3. Integrate other enterprise data sources with MongoDB based data.

Usage

The name of the translator to use in vdb.xml is "mongodb".
For example:

<vdb name="nothwind" version="1">
	<model name="northwind">
		<source name="local" translator-name="mongodb" connection-jndi-name="java:/mongoDS"/>
	</model>
<vdb>

Translator does not provide connection to the MongoDB, for that purpose, Teiid has a JCA adapter that provides a connection to MongoDB using MongoDB Java Driver. To define such connector, have a following XML fragment in standalone-teiid.xml. See a example in "<jboss-as>/docs/teiid/datasources/mongodb"

    <resource-adapters>
        <resource-adapter id="webservice">
            <module slot="main" id="org.jboss.teiid.resource-adapter.mongodb"/>
            <transaction-support>NoTransaction</transaction-support>
            <connection-definitions>
                <connection-definition class-name="org.teiid.resource.adapter.mongodb.MongoDBManagedConnectionFactory" 
                        jndi-name="java:/mongoDS" 
                        enabled="true" 
                        use-java-context="true" 
                        pool-name="teiid-mongodb-ds">
                        
                      <!-- MongoDB server list (host:port[;host:port...]) -->
                      <config-property name="RemoteServerList">localhost:27017</config-property>
                      <!-- Database Name in the MongoDB -->
                      <config-property name="Database">test</config-property>
                        <!-- 
                            Uncomment these properties to supply user name and password
                        <config-property name="Username">user</config-property>
                        <config-property name="Password">user</config-property>
                        -->  
                </connection-definition>
            </connection-definitions>
        </resource-adapter>
    </resource-adapters>

The above defined the translator and connector, however as mentioned above the MongoDB can NOT derive the metadata based on existing document collections, so user MUST define the metadata. For example, using "northwind" DDL you can do

<vdb name="nothwind" version="1">
	<model name="northwind">
		<source name="local" translator-name="mongodb" connection-jndi-name="java:/mongoDS"/>
		<metadata type="DDL"><![CDATA[
		CREATE FOREIGN TABLE  Categories (
		  CategoryID integer NOT NULL auto_increment OPTIONS (NAMEINSOURCE '_id'),
		  CategoryName varchar(15),
		  Description varchar(4000),
		  Picture varchar(40),
		  PRIMARY KEY  (CategoryID),
		  UNIQUE (CategoryName)
		) OPTIONS(UPDATABLE 'TRUE', EMBEDDABLE 'TRUE');

		CREATE FOREIGN TABLE Suppliers (
		  SupplierID integer NOT NULL auto_increment OPTIONS (NAMEINSOURCE '_id'),
		  CompanyName varchar(40),
		  ContactName varchar(30),
		  ContactTitle varchar(30),
		  Address varchar(60),
		  City varchar(15),
		  Region varchar(15),
		  PostalCode varchar(10),
		  Country varchar(15),
		  Phone varchar(24),
		  Fax varchar(24),
		  HomePage varchar(4000),
		  PRIMARY KEY  (SupplierID)
		)OPTIONS(UPDATABLE 'TRUE', EMBEDDABLE 'TRUE');

		CREATE FOREIGN TABLE Shippers (
		  ShipperID integer NOT NULL auto_increment OPTIONS (NAMEINSOURCE '_id'),
		  CompanyName varchar(40),
		  Phone varchar(24),
		  PRIMARY KEY (ShipperID)
		)OPTIONS(UPDATABLE 'TRUE', EMBEDDABLE 'TRUE');

		CREATE FOREIGN TABLE Customers (
		  CustomerID varchar(5) NOT NULL default '' OPTIONS (NAMEINSOURCE '_id'),
		  CompanyName varchar(40),
		  ContactName varchar(30),
		  ContactTitle varchar(30),
		  Address varchar(60),
		  City varchar(15),
		  Region varchar(15),
		  PostalCode varchar(10),
		  Country varchar(15),
		  Phone varchar(24),
		  Fax varchar(24),
		  PRIMARY KEY  (CustomerID)
		)OPTIONS(UPDATABLE 'TRUE');

		CREATE FOREIGN TABLE Employees (
		  EmployeeID integer NOT NULL auto_increment OPTIONS (NAMEINSOURCE '_id'),
		  LastName varchar(20),
		  FirstName varchar(10),
		  Title varchar(30),
		  TitleOfCourtesy varchar(25),
		  BirthDate date,
		  HireDate date,
		  Address varchar(60),
		  City varchar(15),
		  Region varchar(15),
		  PostalCode varchar(10),
		  Country varchar(15),
		  HomePhone varchar(24),
		  Extension varchar(4),
		  Photo varchar(40),
		  Notes varchar(4000),
		  ReportsTo integer,
		  PRIMARY KEY  (EmployeeID)
		) OPTIONS(UPDATABLE 'TRUE');

		CREATE FOREIGN TABLE Products (
		  ProductID integer NOT NULL auto_increment OPTIONS (NAMEINSOURCE '_id'),
		  ProductName varchar(40),
		  SupplierID integer NOT NULL,
		  CategoryID integer NOT NULL,
		  QuantityPerUnit varchar(20),
		  UnitPrice float default '0',
		  UnitsInStock integer default '0',
		  UnitsOnOrder integer default '0',
		  ReorderLevel integer default '0',
		  Discontinued integer default '0',
		  PRIMARY KEY  (ProductID),
		  FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
		  FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
		) OPTIONS(UPDATABLE 'TRUE');

		CREATE FOREIGN TABLE Orders (
		  OrderID integer NOT NULL auto_increment OPTIONS (NAMEINSOURCE '_id'),
		  CustomerID varchar(5),
		  EmployeeID integer,
		  OrderDate date,
		  RequiredDate date,
		  ShippedDate date,
		  ShipVia integer,
		  Freight float default '0',
		  ShipName varchar(40),
		  ShipAddress varchar(60),
		  ShipCity varchar(15),
		  ShipRegion varchar(15),
		  ShipPostalCode varchar(10),
		  ShipCountry varchar(15),
		  PRIMARY KEY  (OrderID),
		  FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
		  FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
		  FOREIGN KEY (ShipVia) REFERENCES Shippers (ShipperID)
		) OPTIONS(UPDATABLE 'TRUE');

		CREATE FOREIGN TABLE OrderDetails (
		  odID integer OPTIONS (NAMEINSOURCE '_id'),
		  OrderID integer NOT NULL,
		  ProductID integer NOT NULL,
		  UnitPrice float default '0',
		  Quantity integer default '1',
		  Discount float default '0',
		  FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
		  FOREIGN KEY (ProductID) REFERENCES Products (ProductID),
		  PRIMARY KEY (OrderID,ProductID)
		) OPTIONS (EMBEDIN 'Orders', UPDATABLE 'TRUE');
		]]> </metadata>
	</model>
<vdb>

If carefully see the above schema defined in DDL, there is only two (2) additional properties that are of specific to MongoDB translator.

• EMBEDDABLE - Means that data of this table is allowed to be included as "embeddable" document in parent document. Teiid maintains more than one copy of of data one in its own collection and also in all parent tables that have relationship to this table.

• EMBEDIN - Means that data of this table is embeddded-in the defined parent table. There is only single copy of the data, that is embedded in the parent document.

Now lets see how these properties behave in different types of relationships on the schema

• ONE-2-ONE: Since the existence of child record has no meaning with out parent table use "EMBEDIN", however if the ONE-2-ONE relationship is between more than two tables use "EMBEDDABLE"

• ONE-2-MANY: Typically there are only two tables involved in this relationship. If MANY side is only associated one table, then use "EMBEDIN" property on MANY side of table and define the parent. If associated with with more than single table use "EMBEDDABLE". When MANY side is stored in ONE side, they are stored as array of embedded document.

• MANY-2-ONE: same as ONE-2-MANY, just apply them in reverse.

• MANY-2-MAY: no support yet..

Limitations Currently a "EMBEDDALBLE" can not have its own "EMBEDDABLE" or "EMBEDIN" property JOINS between related tables, that have used either of the property can be satisfied, otherwise they result in error

When you use above properties and carefully design the MongoDB document structure, Teiid translator can intelligently collate data based on their co-location and take advantage of it while querying.

Capabilities

MongoDB translator designed on top of the MongoDB aggregation framework, use of MongoDB that supports this framework is mandatory. This translator supports

• grouping
• matching
• sorting
• filerting
• limit

Apart from SELECT, this translator also supports INSERT, UPDATE and delete.

All the operations involved as atomic, except for the "UPDATE" of a table that is "EMBEDDABLE". Since Teiid maintains more than single copy of the data it not possible to modify the data every where with single atomic operation.

"Native Queries" Native queries are not currently supported