Skip to main content

Integrate OData Source Using Teiid

Teiid 8.3.x release has a new translator "odata", that supports consuming of OData sources. Using this translator a user can integrate data from a OData source with other sources in Teiid.

If you like to know what OData is and Teiid support for it, please read this previous post http://teiid.blogspot.com/2013/02/odata-support-in-teiid.html

In this blog, we will write a Teiid VDB, that shows how to consume a OData source, and how Teiid uses OData's CSDL metadata document to expose it in relational form such that it can be integrated with other sources.

So, let's begin..

In a Teiid system, OData translator is a extension of the "ws" web services translator, since all OData queries are REST based web service calls, and results are published in "atom/pub" or "json" format, it uses the same resource adapter "teiid-connector-ws.rar". For this example, we will use a example OData service at http://services.odata.org/Northwind/Northwind.svc/

The below is a CLI Script to create connection to OData source
 
/subsystem=resource-adapters/resource-adapter=northwindDS:add(module=org.jboss.teiid.resource-adapter.webservice)
/subsystem=resource-adapters/resource-adapter=northwindDS/connection-definitions=northwindDS:add(jndi-name=java:/northwindDS, class-name=org.teiid.resource.adapter.ws.WSManagedConnectionFactory, enabled=true, use-java-context=true)
/subsystem=resource-adapters/resource-adapter=northwindDS/connection-definitions=northwindDS/config-properties=EndPoint:add(value=http://services.odata.org/Northwind/Northwind.svc)
/subsystem=resource-adapters/resource-adapter=northwindDS:activate

Once the connection is created, let's create a Dynamic VDB (northwind-vdb.xml) that uses the connection created above and defines virtual database

<vdb name="northwind-rw" version="1">
    <model name="nw">
        <property name="importer.importKeys" value="true"/>
        <property name="importer.importProcedures" value="true">
         <source connection-jndi-name="java:/northwindDS" name="northwind-connector" translator-name="odata"/>
    </model>
</vdb>

When you deploy the above VDB in Teiid server, it automatically imports the metadata of this web service from http://services.odata.org/Northwind/Northwind.svc/$metadata and creates relational structure based on it.

For example, from the metadata call above one of the entity type in the CSDL document looks like below
<EntityType Name="Customer">
  <Key><PropertyRef Name="CustomerID"/></Key>
  <Property Name="CustomerID" Type="Edm.String" Nullable="false" MaxLength="5" Unicode="true" FixedLength="true"/>
  <Property Name="CompanyName" Type="Edm.String" Nullable="false" MaxLength="40" Unicode="true" FixedLength="false"/>
  <Property Name="ContactName" Type="Edm.String" Nullable="true" MaxLength="30" Unicode="true" FixedLength="false"/>
  <Property Name="ContactTitle" Type="Edm.String" Nullable="true" MaxLength="30" Unicode="true" FixedLength="false"/>
  <Property Name="Address" Type="Edm.String" Nullable="true" MaxLength="60" Unicode="true" FixedLength="false"/>
  <Property Name="City" Type="Edm.String" Nullable="true" MaxLength="15" Unicode="true" FixedLength="false"/>
  <Property Name="Region" Type="Edm.String" Nullable="true" MaxLength="15" Unicode="true" FixedLength="false"/>
  <Property Name="PostalCode" Type="Edm.String" Nullable="true" MaxLength="10" Unicode="true" FixedLength="false"/>
  <Property Name="Country" Type="Edm.String" Nullable="true" MaxLength="15" Unicode="true" FixedLength="false"/>
  <Property Name="Phone" Type="Edm.String" Nullable="true" MaxLength="24" Unicode="true" FixedLength="false"/>
  <Property Name="Fax" Type="Edm.String" Nullable="true" MaxLength="24" Unicode="true" FixedLength="false"/>
  <NavigationProperty Name="Orders" Relationship="NorthwindModel.FK_Orders_Customers" FromRole="Customers" ToRole="Orders"/>
  <NavigationProperty Name="CustomerDemographics" Relationship="NorthwindModel.CustomerCustomerDemo" FromRole="Customers" ToRole="CustomerDemographics"/>
</EntityType>

It will be converted to a relational table like below in the Teiid's metadata
CREATE FOREIGN TABLE Customers (
    CustomerID string(5) NOT NULL OPTIONS (FIXED_LENGTH TRUE),
    CompanyName string(40) NOT NULL,
    ContactName string(30),
    ContactTitle string(30),
    Address string(60),
    City string(15),
    Region string(15),
    PostalCode string(10),
    Country string(15),
    Phone string(24),
    Fax string(24),
    PRIMARY KEY(CustomerID)
) OPTIONS (UPDATABLE TRUE, EntityAlias 'Customer', EntityType 'NorthwindModel.Customer');

All "NavigationProperty" elements will be converted into FOREIGN KEYS, based on the "Association" and "AssociationSet" elements defined in the CSDL document.

 * If there is One-to-One relation, the FK are created at both ends of the table.
* If there is One-to-Many relation, at Many end of the table, a FK is created to single end of the table.
* If there are Many-to-Many relation, a virtual mapping table is created with PK's of the both tables.

The mapping table is created so that a VDB user can issue a SQL query that joins these both tables together in their user queries. One limitation is user can not select the columns from this mapping table, it can be strictly used only for the table join purposes. When a user queries their deployed VDB using JDBC/ODBC using SQL, those queries will be automatically converted into OData queries and results will be gathered and presented to user in relation form. For example you can issue a query like

select CustomerID, CompanyName from Customers

CustomerIDCompanyName
ALFKIAlfreds Futterkiste
ANATRAna Trujillo Emparedados y helados
ANTONAntonio Moreno Taquería
AROUTAround the Horn

I hope the above gave glimpse of how OData translator works in Teiid. So, take it for test drive and let us know any issues or comments you may have on this subject.

 Ramesh..

Comments

  1. This comment has been removed by the author.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. That's with the new Odata4 translator. This entry was written using the original Odata 2 translator. None the less you should open an issue.

    ReplyDelete

Post a Comment

Popular posts from this blog

Teiid Runtimes Explained

If you have been following Teiid lately we have been going through a whole lot of renovations. Yes, renovations or reorganization or refactoring or whatever you want to call it. Basically, we are making Teiid more modular with fewer dependencies that can be used by however your use case dictates rather than use it as one monolith application deployed into WildFly JEE Application Server. There is nothing wrong in using Teiid as server model, but with the proliferation of container-based workloads and cloud-based architectures, the previous server-based model does not work or simply won't scale. So, we needed to think of alternatives, thus Teiid team introduced a couple different versions modular Teiid what we are calling as "Teiid Runtimes".

Note that in these modular Teiid runtimes, not all the features you were used to using in Teiid Server model may not be there but you will have extensions to add in those that are most appropriate for your domain. If you are looking …

Teiid Platform Sizing Guidelines and Limitations

Users/customers always ask us about the sizing of their Data Virtaulization infrastructure based on Teiid or the JDV product from Redhat. Typically this is very involved question and not a very easy one answer in plain terms. This is due to fact that it involves taking into consideration questions like:
What kind of sources that user is working with? Relational, file, CRM, NoSQL etc.How many sources they are trying to integrate? 10, 20, 100?What are the volumes of data they are working with? 10K, 100K, 1M+?What are the query latency times from the sources? How you are using Teiid to implement the data integration/virtualization solution. What kind of queries that user is executing? Even small federated results may take a lot of server side processing - especially if the plan needs tweaking.Is materializing being used?Is query written in optimal way?and so on..Each and every one of the question affects the performance profoundly, and if you got mixture of those then it become that much…