... |
{code:lang=XML} <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 Customer ( |
CategoryID integer NOT NULL, CategoryName varchar(15), Description varchar(4000), Picture varchar(40), PRIMARY KEY (CategoryID), UNIQUE (CategoryName) |
customer_id integer, FirstName varchar(25), LastName varchar(25), |
) OPTIONS(UPDATABLE 'TRUE', EMBEDDABLE 'TRUE'); |
]]> </metadata> </model> <vdb> {code} |
|
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'); |
when INSERT operation below executed against table using Teiid, {code:lang=SQL} INSERT INTO Customer(customer_id, FirstName, LastName) VALUES (1, 'John', 'Doe'); {code} MongoDB translator will create a below document in the MongoDB {code} { _id: ObjectID("509a8fb2f3f4948bd2f983a0"), customer_id: 1, FirstName: "John", LastName: "Doe" } {code} |
|
CREATE FOREIGN TABLE Shippers ( ShipperID integer NOT NULL, CompanyName varchar(40), Phone varchar(24), PRIMARY KEY (ShipperID) )OPTIONS(UPDATABLE 'TRUE', EMBEDDABLE 'TRUE'); |
If a PRIMARY KEY is defined on the table as |
|
{code:lang=SQL} |
CREATE FOREIGN TABLE Customers Customer ( |
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) |
customer_id integer PRIMARY KEY, FirstName varchar(25), LastName varchar(25), |
) OPTIONS(UPDATABLE 'TRUE'); |
{code} |
|
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'); |
then that column name is automatically used as "_id" field in the MongoDB collection, then document structure is stored in the MongoDB as |
|
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'); |
{code} { _id: 1, FirstName: "John", LastName: "Doe" } {code} |
|
If you defined the composite PRIMARY KEY on Customer table as {code:lang=SQL} |
CREATE FOREIGN TABLE Orders Customer ( |
OrderID integer NOT NULL, CustomerID varchar(5), |
EmployeeID customer_id 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) |
FirstName varchar(25), LastName varchar(25), PRIMARY KEY (FirstName, LastName) |
) OPTIONS(UPDATABLE 'TRUE'); |
{code} |
|
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> |
the document structure will be |
{code} |
{ _id: { FirstName: "John", LastName: "Doe" }, customer_id: 1, } {code} |
|
There is only two (2) additional properties that are of specific to MongoDB translator. |
h2. Data Types |
|
* *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. |
MongoDB translator supports automatic mapping of Teiid data types into MongoDB data types, including the support for Blobs, Clobs and XML. The LOB support is based on GridFS in MonogoDB. MongoDB Arrays, Regular Expressions, MongoDB::Code, MongoDB::MinKey, MongoDB::MaxKey, MongoDB::OID is not currently supported. |
|
* *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 |
h2. Extension Metadata Properties To Build Complex Documents Using the above DDL or any other metadata facility, a user can map a table in a relational store into a document in MongoDB, however to make effective use of MongoDB, you need to be able to build complex documents, that can co-locate related information, so that data can queried in a single MongoDB query. Otherwise, since MongoDB does not support join relationships like relational database, you need to issue multiple queries to retrieve and join data manually. The power of MongoDB comes from its "embedded" documents and its support of complex data types like arrays and use of the aggregation framework to be able to query them. This translator provides way to achieve that goals. |
|
* 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" |
MongoDB translator defines two additional metadata properties along with other [Teiid metadata properties|DDL Metadata] to aid in building the complex "embedded" documents. You can use the following metadata properties in your DDL. |
|
* 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. |
* *EMBEDDABLE* - Means that data defined in this table is allowed to be included as an "embeddable" document in *any* parent document. The parent document is inferenced by the foreign key relationships. In this scenario, 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. |
|
* MANY-2-ONE: same as ONE-2-MANY, just apply them in reverse. |
* *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. Parent document is inferenced by foreign key relationships. |
|
* MANY-2-MAY: no support yet.. |
Using the above properties and FOREIGN KEY relationships, we will illustrate how to build complex documents in MongoDB |
|
{info:title=DBRefs} All the key references are automatically stored as "DBRef" values. The examples below were stripped for clarity reasons. {info} h3. ONE-2-ONE Mapping If your current DDL structure representing ONE-2-ONE relationship is like {code:lang=SQL} CREATE FOREIGN TABLE Customer ( CustomerId integer PRIMARY KEY, FirstName varchar(25), LastName varchar(25), ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE Address ( CustomerId integer, Street varchar(50), City varchar(25), State varchar(25), Zipcode varchar(6), FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId), ) OPTIONS(UPDATABLE 'TRUE'); {code} by default, this will produce two different collections in MongoDB, like with sample data it will look like {code} Customer { _id: 1, FirstName: "John", LastName: "Doe" } Address { _id: ObjectID("..."), CustomerId: 1, Street: "123 Lane" City: "New York", State: "NY" Zipcode: "12345" } {code} You can enhance the storage in MongoDB to a single collection by using "EMBEDIN' extension property on the table's OPTIONS clause {code:lang=SQL} CREATE FOREIGN TABLE Customer ( CustomerId integer PRIMARY KEY, FirstName varchar(25), LastName varchar(25), ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE Address ( CustomerId integer, Street varchar(50), City varchar(25), State varchar(25), Zipcode varchar(6), FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId), ) OPTIONS(UPDATABLE 'TRUE', EMBEDIN 'Customer'); {code} this will produce single collection in MongoDB, like {code} Customer { _id: 1, FirstName: "John", LastName: "Doe", Address: { _id: ObjectID("..."), CustomerId: 1, Street: "123 Lane" City: "New York", State: "NY" Zipcode: "12345" } } {code} With the above both tables are merged into a single collection that can be queried together using the JOIN clause in the SQL command. Since the existence of child/additional record has no meaning with out parent table using the "_EMBEDIN_" extension property is right choice. h3. ONE-2-MANY Mapping. Typically there can be more than two (2) tables involved in this relationship. If MANY side is only associated *single* table, then use "EMBEDIN" property on MANY side of table and define it as the parent. If associated with with more than single table then use "EMBEDDABLE". For example if you have DDL like {code:lang=SQL} CREATE FOREIGN TABLE Customer ( CustomerId integer PRIMARY KEY, FirstName varchar(25), LastName varchar(25), ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE Order ( OrderID integer PRIMARY KEY, CustomerId integer, OrderDate date, Status integer, FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId) ) OPTIONS(UPDATABLE 'TRUE'); {code} in the above a Single Customer can have MANY Orders. There are two options to define the how we store the MongoDB document. If in your schema, the Customer table's CustomerId is *only* referenced in Order table (i.e. Customer information used for only Order purposes), you can use {code:lang=SQL} CREATE FOREIGN TABLE Customer ( CustomerId integer PRIMARY KEY, FirstName varchar(25), LastName varchar(25), ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE Order ( OrderID integer PRIMARY KEY, CustomerId integer, OrderDate date, Status integer, FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId) ) OPTIONS(UPDATABLE 'TRUE', EMBEDIN 'Customer'); {code} that will produce a single document for Customer table like {code} { _id: 1, FirstName: "John", LastName: "Doe", Order: [ { _id: 100, CustomerId: 1, OrderDate: ISODate("2000-01-01T06:00:00Z") Status: 2 }, { _id: 101, CustomerId: 1, OrderDate: ISODate("2001-03-06T06:00:00Z") Status: 5 } ... ] } {code} If Customer table is referenced in more tables other than Order table, then use "EMBEDDABLE" property {code:lang=SQL} CREATE FOREIGN TABLE Customer ( CustomerId integer PRIMARY KEY, FirstName varchar(25), LastName varchar(25), ) OPTIONS(UPDATABLE 'TRUE', EMBEDDABLE 'TRUE'); CREATE FOREIGN TABLE Order ( OrderID integer PRIMARY KEY, CustomerId integer, OrderDate date, Status integer, FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId) ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE Comments ( CommentID integer PRIMARY KEY, CustomerId integer, Comment varchar(140), FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId) ) OPTIONS(UPDATABLE 'TRUE'); {code} This creates three different collections in MongoDB. {code} Customer { _id: 1, FirstName: "John", LastName: "Doe" } Order { _id: 100, CustomerId: 1, OrderDate: ISODate("2000-01-01T06:00:00Z") Status: 2 Customer: { _id: 1, FirstName: "John", LastName: "Doe" } } Comment { _id: 12, CustomerId: 1, Comment: "This works!!!" Customer: { _id: 1, FirstName: "John", LastName: "Doe" } } {code} Here as you can see the Cutomer table contents are embedded along with other table's data where they were referenced. This creates duplicated data where multiple of these embedded documents are managed automatically in the MongoDB. {warning} All the SELECT, INSERT, DELETE operations that are generated against the tables with "EMBEDDABLE" property are atomic, except for UPDATES, as there can be multiple operations involved to update all the copies. Since there are no transactions in MongoDB, Teiid plans to provide automatic compensating transactional framework around this in future releases. {warning} h3. MANY-2-ONE Mapping. This is same as ONE-2-MANY, see above to define relationships. {tip} A parent table can have multiple "embedded" and as well as "embedin" documents inside it, it not limited so either one or other. the document size is only limited by the MongoDB document size, which is 16MB. {tip} h3. MANY-2-MANY Mapping. This can also mapped with combination of "EMBEDIN" and "EMBEDDABLE" properties (partially). For example if DDL looks like {code:lang=SQL} CREATE FOREIGN TABLE Order ( OrderID integer PRIMARY KEY, OrderDate date, Status integer, ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE OrderDetail ( OrderID integer, ProductID integer, PRIMARY KEY (OrderID,ProductID), FOREIGN KEY (OrderID) REFERENCES Order (OrderID), FOREIGN KEY (ProductID) REFERENCES Product (ProductID), ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE Products ( ProductID integer PRIMARY KEY, ProductName varchar(40), ) OPTIONS(UPDATABLE 'TRUE'); {code} you modify the DDL like below, to have {code:lang=SQL} CREATE FOREIGN TABLE Order ( OrderID integer PRIMARY KEY, OrderDate date, Status integer, ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE OrderDetail ( OrderID integer, ProductID integer, PRIMARY KEY (OrderID,ProductID), FOREIGN KEY (OrderID) REFERENCES Order (OrderID), FOREIGN KEY (ProductID) REFERENCES Product (ProductID), ) OPTIONS(UPDATABLE 'TRUE', EMBEDIN 'Order'); CREATE FOREIGN TABLE Products ( ProductID integer PRIMARY KEY, ProductName varchar(40), ) OPTIONS(UPDATABLE 'TRUE'); {code} That will produce a document like {code} { _id : 10248, OrderDate : ISODate("1996-07-04T05:00:00Z"), Status : 5 OrderDetails : [ { _id : { OrderID : 10248, ProductID : 11 } }, { _id : { OrderID : 10248, ProductID : 14 } } ] } {code} |
{warning:title=Limitations} |
* Currently a "EMBEDDALBLE" can not have its own "EMBEDDABLE" or "EMBEDIN" property |
* Currently a "EMBEDDALBLE" can not have its own "EMBEDDABLE" or "EMBEDIN" property. i.e. they can be nested with each other. |
* JOINS between related tables, MUST have used either of "EMBEDDABLE" or "EMBEDIN" property, otherwise the query will result in error. {warning} |
... |
h2. Capabilities |
MongoDB translator designed on top of the MongoDB aggregation framework, use of MongoDB version that supports this framework is mandatory. This translator supports |
MongoDB translator designed on top of the MongoDB aggregation framework, use of MongoDB version that supports this framework is mandatory. Apart from SELECT queries, this translator also supports INSERT, UPDATE and DELETE queries. |
|
This translator supports |
* grouping * matching |
... |
* filerting * limit |
* support for LOBs using GridFS * Composite primary and foreign keys. |
|
Apart from SELECT, this translator also supports INSERT, UPDATE and DELETE. |
{tip:title=example} For a full example see [https://github.com/teiid/teiid/blob/master/connectors/translator-mongodb/src/test/resources/northwind.ddl] {tip} |
|
{warning} 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. {warning} |
{tip:title="Native Queries"} Native queries are not currently supported. |
... |
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.
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.
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 Customer ( customer_id integer, FirstName varchar(25), LastName varchar(25), ) OPTIONS(UPDATABLE 'TRUE'); ]]> </metadata> </model> <vdb>
when INSERT operation below executed against table using Teiid,
INSERT INTO Customer(customer_id, FirstName, LastName) VALUES (1, 'John', 'Doe');
MongoDB translator will create a below document in the MongoDB
{ _id: ObjectID("509a8fb2f3f4948bd2f983a0"), customer_id: 1, FirstName: "John", LastName: "Doe" }
If a PRIMARY KEY is defined on the table as
CREATE FOREIGN TABLE Customer ( customer_id integer PRIMARY KEY, FirstName varchar(25), LastName varchar(25), ) OPTIONS(UPDATABLE 'TRUE');
then that column name is automatically used as "_id" field in the MongoDB collection, then document structure is stored in the MongoDB as
{ _id: 1, FirstName: "John", LastName: "Doe" }
If you defined the composite PRIMARY KEY on Customer table as
CREATE FOREIGN TABLE Customer ( customer_id integer, FirstName varchar(25), LastName varchar(25), PRIMARY KEY (FirstName, LastName) ) OPTIONS(UPDATABLE 'TRUE');
the document structure will be
{ _id: { FirstName: "John", LastName: "Doe" }, customer_id: 1, }
MongoDB translator supports automatic mapping of Teiid data types into MongoDB data types, including the support for Blobs, Clobs and XML. The LOB support is based on GridFS in MonogoDB. MongoDB Arrays, Regular Expressions, MongoDB::Code, MongoDB::MinKey, MongoDB::MaxKey, MongoDB::OID is not currently supported.
Using the above DDL or any other metadata facility, a user can map a table in a relational store into a document in MongoDB, however to make effective use of MongoDB, you need to be able to build complex documents, that can co-locate related information, so that data can queried in a single MongoDB query. Otherwise, since MongoDB does not support join relationships like relational database, you need to issue multiple queries to retrieve and join data manually. The power of MongoDB comes from its "embedded" documents and its support of complex data types like arrays and use of the aggregation framework to be able to query them. This translator provides way to achieve that goals.
MongoDB translator defines two additional metadata properties along with other Teiid metadata properties to aid in building the complex "embedded" documents. You can use the following metadata properties in your DDL.
Using the above properties and FOREIGN KEY relationships, we will illustrate how to build complex documents in MongoDB
DBRefs All the key references are automatically stored as "DBRef" values. The examples below were stripped for clarity reasons. |
If your current DDL structure representing ONE-2-ONE relationship is like
CREATE FOREIGN TABLE Customer ( CustomerId integer PRIMARY KEY, FirstName varchar(25), LastName varchar(25), ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE Address ( CustomerId integer, Street varchar(50), City varchar(25), State varchar(25), Zipcode varchar(6), FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId), ) OPTIONS(UPDATABLE 'TRUE');
by default, this will produce two different collections in MongoDB, like with sample data it will look like
Customer { _id: 1, FirstName: "John", LastName: "Doe" } Address { _id: ObjectID("..."), CustomerId: 1, Street: "123 Lane" City: "New York", State: "NY" Zipcode: "12345" }
You can enhance the storage in MongoDB to a single collection by using "EMBEDIN' extension property on the table's OPTIONS clause
CREATE FOREIGN TABLE Customer ( CustomerId integer PRIMARY KEY, FirstName varchar(25), LastName varchar(25), ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE Address ( CustomerId integer, Street varchar(50), City varchar(25), State varchar(25), Zipcode varchar(6), FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId), ) OPTIONS(UPDATABLE 'TRUE', EMBEDIN 'Customer');
this will produce single collection in MongoDB, like
Customer { _id: 1, FirstName: "John", LastName: "Doe", Address: { _id: ObjectID("..."), CustomerId: 1, Street: "123 Lane" City: "New York", State: "NY" Zipcode: "12345" } }
With the above both tables are merged into a single collection that can be queried together using the JOIN clause in the SQL command. Since the existence of child/additional record has no meaning with out parent table using the "EMBEDIN" extension property is right choice.
Typically there can be more than two (2) tables involved in this relationship. If MANY side is only associated single table, then use "EMBEDIN" property on MANY side of table and define it as the parent. If associated with with more than single table then use "EMBEDDABLE".
For example if you have DDL like
CREATE FOREIGN TABLE Customer ( CustomerId integer PRIMARY KEY, FirstName varchar(25), LastName varchar(25), ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE Order ( OrderID integer PRIMARY KEY, CustomerId integer, OrderDate date, Status integer, FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId) ) OPTIONS(UPDATABLE 'TRUE');
in the above a Single Customer can have MANY Orders. There are two options to define the how we store the MongoDB document. If in your schema, the Customer table's CustomerId is only referenced in Order table (i.e. Customer information used for only Order purposes), you can use
CREATE FOREIGN TABLE Customer ( CustomerId integer PRIMARY KEY, FirstName varchar(25), LastName varchar(25), ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE Order ( OrderID integer PRIMARY KEY, CustomerId integer, OrderDate date, Status integer, FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId) ) OPTIONS(UPDATABLE 'TRUE', EMBEDIN 'Customer');
that will produce a single document for Customer table like
{ _id: 1, FirstName: "John", LastName: "Doe", Order: [ { _id: 100, CustomerId: 1, OrderDate: ISODate("2000-01-01T06:00:00Z") Status: 2 }, { _id: 101, CustomerId: 1, OrderDate: ISODate("2001-03-06T06:00:00Z") Status: 5 } ... ] }
If Customer table is referenced in more tables other than Order table, then use "EMBEDDABLE" property
CREATE FOREIGN TABLE Customer ( CustomerId integer PRIMARY KEY, FirstName varchar(25), LastName varchar(25), ) OPTIONS(UPDATABLE 'TRUE', EMBEDDABLE 'TRUE'); CREATE FOREIGN TABLE Order ( OrderID integer PRIMARY KEY, CustomerId integer, OrderDate date, Status integer, FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId) ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE Comments ( CommentID integer PRIMARY KEY, CustomerId integer, Comment varchar(140), FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId) ) OPTIONS(UPDATABLE 'TRUE');
This creates three different collections in MongoDB.
Customer { _id: 1, FirstName: "John", LastName: "Doe" } Order { _id: 100, CustomerId: 1, OrderDate: ISODate("2000-01-01T06:00:00Z") Status: 2 Customer: { _id: 1, FirstName: "John", LastName: "Doe" } } Comment { _id: 12, CustomerId: 1, Comment: "This works!!!" Customer: { _id: 1, FirstName: "John", LastName: "Doe" } }
Here as you can see the Cutomer table contents are embedded along with other table's data where they were referenced. This creates duplicated data where multiple of these embedded documents are managed automatically in the MongoDB.
All the SELECT, INSERT, DELETE operations that are generated against the tables with "EMBEDDABLE" property are atomic, except for UPDATES, as there can be multiple operations involved to update all the copies. Since there are no transactions in MongoDB, Teiid plans to provide automatic compensating transactional framework around this in future releases. |
This is same as ONE-2-MANY, see above to define relationships.
A parent table can have multiple "embedded" and as well as "embedin" documents inside it, it not limited so either one or other. the document size is only limited by the MongoDB document size, which is 16MB. |
This can also mapped with combination of "EMBEDIN" and "EMBEDDABLE" properties (partially). For example if DDL looks like
CREATE FOREIGN TABLE Order ( OrderID integer PRIMARY KEY, OrderDate date, Status integer, ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE OrderDetail ( OrderID integer, ProductID integer, PRIMARY KEY (OrderID,ProductID), FOREIGN KEY (OrderID) REFERENCES Order (OrderID), FOREIGN KEY (ProductID) REFERENCES Product (ProductID), ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE Products ( ProductID integer PRIMARY KEY, ProductName varchar(40), ) OPTIONS(UPDATABLE 'TRUE');
you modify the DDL like below, to have
CREATE FOREIGN TABLE Order ( OrderID integer PRIMARY KEY, OrderDate date, Status integer, ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE OrderDetail ( OrderID integer, ProductID integer, PRIMARY KEY (OrderID,ProductID), FOREIGN KEY (OrderID) REFERENCES Order (OrderID), FOREIGN KEY (ProductID) REFERENCES Product (ProductID), ) OPTIONS(UPDATABLE 'TRUE', EMBEDIN 'Order'); CREATE FOREIGN TABLE Products ( ProductID integer PRIMARY KEY, ProductName varchar(40), ) OPTIONS(UPDATABLE 'TRUE');
That will produce a document like
{ _id : 10248, OrderDate : ISODate("1996-07-04T05:00:00Z"), Status : 5 OrderDetails : [ { _id : { OrderID : 10248, ProductID : 11 } }, { _id : { OrderID : 10248, ProductID : 14 } } ] }
Limitations
|
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.
MongoDB translator designed on top of the MongoDB aggregation framework, use of MongoDB version that supports this framework is mandatory. Apart from SELECT queries, this translator also supports INSERT, UPDATE and DELETE queries.
This translator supports
example For a full example see https://github.com/teiid/teiid/blob/master/connectors/translator-mongodb/src/test/resources/northwind.ddl |
"Native Queries" Native queries are not currently supported. |