MongoDB Translator

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

MongoDB is a document based "schema-less" database with it own query language - it does not map perfectly with relational concepts or the SQL query language. More and more systems are using a MongoDB kind of NOSQL store for scalability and performance. For example, applications like storing audit logs or managing web site data fits well with MongoDB, and does not require using a structural database like Oracle, Postgres ect. MongoDB uses JSON documents as its primary storage unit, and it can have additional embedded documents inside the parent document. By using embedded documents it co-locates the related information to achieves de-normalization that typically requires either duplicate data or joins to achieve in a relational database.

To make MongoDB work with Teiid the challenge for the MongoDB translator is "How best one can design a MongoDB store that can achieve the balance between relational and document based storage?" In our opinion the advantages of "schema-less" design are great at development time. "Schema-less" can also be a problem with migration of application versions and the ability to query and make use of returned information effectively.

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 relational schema as its metadata, the user needs to define their MongoDB schema in relational terms using tables, procedures, and functions. For the purposes of MongoDB, the Teiid metadata has been extended to support extension properties that can be defined on the table to convert it into a MongoDB based document. These extension properties let users define, how a MongoDB document is structured and stored. Based on the relationships (primary-key, foreign-key) defined on a table, and the cardinality (ONE-to-ONE, ONE-to-MANY, MANY-to-ONE) relations between tables are mapped such that related information can be embedded along with the parent document for co-location (see the de-normalization comment above). Thus a relational schema based design, but document based storage in MongoDB. Currently direct mapping of MANY-to-MANY is not supported.

Who is the primary audience for this translator?

The above may not satisfy every user's needs. 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 currently designed for:

1. Users that are using relational databases and would like to move/migrate their data to MongoDB to take advantages scaling and performance with out modifying the end user applications currently running.

2. Users that are starting out with MongoDB and do not have experience with MongoDB, but are seasoned SQL developers. This provides a low barrier of entry compared to using MongoDB directly as an 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>

The translator does not provide a connection to the MongoDB. For that purpose, Teiid has a JCA adapter that provides a connection to MongoDB using the MongoDB Java Driver. To define such connector, use the 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 defines the translator and connector. However as mentioned the MongoDB translator can NOT derive the metadata based on existing document collections - the user MUST define the metadata. For example, you can define a schema using DDL:

<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,
		  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,
		  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,
		  CompanyName varchar(40),
		  Phone varchar(24),
		  PRIMARY KEY (ShipperID)
		)OPTIONS(UPDATABLE 'TRUE', EMBEDDABLE 'TRUE');

		CREATE FOREIGN TABLE Customers (
		  CustomerID varchar(5) NOT NULL,
		  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,
		  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,
		  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,
		  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,
		  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>

There is only two (2) additional properties that are of specific to MongoDB translator.

• EMBEDDABLE - Means that data defined in this table is allowed to be included as an "embeddable" document in a parent document. The parent document is defined by the foreign key relationships. In this situation, Teiid maintains more than one copy of the data in MongoDb store, one in its own collection and also a copy in each of the 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 a 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 then 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, MUST have used either of "EMBEDDABLE" or "EMBEDIN" property, otherwise the query will 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 version 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 are atomic, except for the "UPDATE" of a table that has property "EMBEDDABLE". Since Teiid maintains more than single copy of the data it not possible to modify the data every where with single atomic operation. Teiid hopes to provide a compensating transaction wrapping around this operation in future releases.

"Native Queries" Native queries are not currently supported.