Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

13.23. OData Translator

13.23.1. OData Translator

The OData translator exposes the OData V2 and V3 data sources. This translator implements a simple connection for web services in the same way as the Web Services translator.
The OData translator is implemented by the org.teiid.translator.odata.ODataExecutionFactory class and known by the translator type name odata.

Note

Open Data Protocol (OData) is a Web protocol for querying and updating data that provides a way to unlock your data and free it from silos that exist in applications today. OData does this by applying and building upon Web technologies such as HTTP, Atom Publishing Protocol (AtomPub) and JSON to provide access to information from a variety of applications, services, and stores. OData is being used to expose and access information from a variety of sources including, but not limited to, relational databases, file systems, content management systems and traditional Web sites.
Using this specification from OASIS group, and with the help from framework OData4J, JBoss Data Virtualization maps OData entities into relational schema. JBoss Data Virtualization supports reading of CSDL (Conceptual Schema Definition Language) from the OData endpoint provided and converts the OData schema into relational schema. The below table shows the mapping selections in OData Translator from CSDL document.
OData
Mapped to Relational Entity
EntitySet
Table
FunctionImport
Procedure
AssociationSet
Foreign Keys on the Table*
ComplexType
ignored**
* A Many to Many association will result in a link table that can not be selected from, but can be used for join purposes.
** When used in Functions, an implicit table is exposed. When used to define a embedded table, all the columns will be in-lined.
All CRUD operations will be appropriately mapped to the resulting entity based on the SQL submitted to the OData translator.

Note

The resource adapter for this translator is provided by configuring the webservice data source in the JBoss EAP instance. See the Red Hat JBoss Data Virtualization Administration and Configuration Guide for more configuration information.
The OData V4 translator, known by the type name "odata4" exposes the OData Version 4 data sources and uses the Teiid WS resource adapter for making web service calls. This translator is an extension of the Web Services Translator.
Using this specification from OASIS group, with the help from the Olingo framework, Teiid maps OData V4 CSDL (Conceptual Schema Definition Language) document from the OData endpoint provided and converts the OData metadata into Teiid's relational schema. The below table shows the mapping selections in OData V4 Translator from CSDL document

13.23.2. OData Translator: Execution Properties

Table 13.16. Execution Properties

Name
Description
Default
DatabaseTimeZone
The time zone of the database. Used when fetching date, time, or timestamp values
The system default time zone

13.23.3. OData Translator: Importer Properties

Table 13.17. Importer Properties

Name
Description
Default
schemaNamespace
Namespace of the schema to import
null
entityContainer
Entity Container Name to import
default container
Example importer settings to only import tables and views from NetflixCatalog: 
<property name="importer.schemaNamespace" value="System.Data.Objects"/>
<property name="importer.schemaPattern" value="NetflixCatalog"/>

13.23.4. OData Translator: Usage

Usage of an OData source is similar to a JDBC translator. The metadata import is supported through the translator, once the metadata is imported from source system and exposed in relational terms, then this source can be queried as if the EntitySets and Function Imports were local to the JBoss Data Virtualization system.

Table 13.18. Execution Properties

Property Description Default
DatabaseTimeZone
The time zone of the database. Used when fetchings date, time, or timestamp values
The system default time zone
SupportsOdataCount
Supports $count
True
SupportsOdataFilter
Supports $filter
True
SupportsOdataOrderBy
Supports $orderby
true
SupportsOdataSkip
Supports $skip
True
SupportsOdataTop
Supports $top
True

Table 13.19. Importer Properties

Property Description Default
schemaNamespace
Namespace of the schema to import
Null
entityContainer
Entity Container Name to import
Default container
Here are some importer settings to import tables and views only from NetflixCatalog: 
<property name="importer.schemaNamespace" value="System.Data.Objects"/>
<property name="importer.schemaPattern" value="NetflixCatalog"/>

Note

Sometimes it's possible that the odata server you are querying does not fully implement all OData specification features. If your OData implementation does not support a certain feature, then turn off the corresponding capability using "execution Properties", so that Teiid will not pushdown invalid queries to the translator. For example, to turn off $filter you add following to your vdb.xml then use "odata-override" as the translator name on your source model: 
<translator name="odata-override" type="odata">
<property name="SupportsOdataFilter" value="false"/>
</translator>

Note

Native or direct query execution is not supported through OData translator. However, user can use Web Services Translator's invokehttp method directly to issue a Rest based call and parse results using SQLXML.

Note

Teiid can not only consume OData based data sources, but it can expose any data source as an Odata based webservice. For more information see OData Support.

13.23.5. OData Version 4 Translator

The odata4 translator exposes OData 4 data sources and uses the Red Hat JBoss Data Virtualization Web Service resource adapter to make calls. (This resource adapter is an extension of the Web Services Translator.)
Use the Open Data (OData) 4 Web Protocol to standardize APIs for accessing data from a variety of sources. OData is an abstraction layer that allows you to access data from such places as databases, file systems and content management systems. OData provides a REST-based protocol for various database operations.
OData is useful if you intend to build RESTful APIs as it standardizes the ways in which you build and consume such things as request and response headers, payload formats, status codes, query options and so forth. By standardizing these, it frees you to focus on developing your business logic.
Red Hat JBoss Data Virtualization can expose any data source as an OData-based web service.
Red Hat JBoss Data Virtualization maps OData 4 Conceptual Schema Documentation Language (CSDL) documents to relational entities. It does this in order to convert OData information into a relational schema understood by Data Virtualization. Here are the mappings to show how the OData elements are converted:

Table 13.20. Mappings

ODataRelational Entity
EntitySetTable
EntityTypeTable. (This is only mapped if the EntityType is exposed as the EntitySet in the Entity Container.)
ComplexTypeTable. (This is mapped only if the complex type is used as a property in the exposed EntitySet. This table will be either a child table with a foreign key [one to one] or [one to many] relationship with its parent.)
FunctionImport Procedure. (If the return type is EntityType or ComplexType, the procedure will return a table.)
ActionImportProcedure. (If the return type is EntityType or ComplexType, the procedure will return a table.)
NavigationPropertiesTable. (Navigation properties are exposed as tables. These tables are created with foreign key relationships to the parent.)
After the entities are generated by these mappings, the translator maps CRUD operations to them based on them that are derived from the submitted SQL.
The OData translator works in a similar way to the JDBC translator in that, once the metadata is imported from the source system and exposed in relational terms, then that source can be queried as if the EntitySets, Function Imports and Action Imports were based locally on the Red Hat JBoss Data Virtualization system.
Here is a sample virtual database that can read the metadata service from the TripPin example (which you can find on http://odata.org): 
<vdb name="trippin" version="1">
    <model name="trippin">
         <source name="odata4" translator-name="odata4" connection-jndi-name="java:/tripDS"/>
    </model>
</vdb>
  1. Configure your resource adapter to look like this: 
    <resource-adapter id="trippin">
    <module slot="main" id="org.jboss.teiid.resource-adapter.webservice"/>
    <transaction-support>NoTransaction</transaction-support>
    <connection-definitions>
        <connection-definition class-name="org.teiid.resource.adapter.ws.WSManagedConnectionFactory" jndi-name="java:/tripDS" enabled="true" use-java-context="true" pool-name="teiid-trip-ds">
            <config-property name="EndPoint">
                http://services.odata.org/V4/(S(va3tkzikqbtgu1ist44bbft5))/TripPinServiceRW
            </config-property>
        </connection-definition>
    </connection-definitions>
</resource-adapter>
  2. Deployed the virtual database.
  3. Connect to it using the JDBC driver.
  4. You can then issue SQL queries like these: 
    SELECT * FROM trippin.People;
SELECT * FROM trippin.People WHERE UserName = 'russelwhyte';
SELECT * FROM trippin.People p INNER JOIN trippin.People_Friends pf ON p.UserName = pf.People_UserName; 
EXEC GetNearestAirport(lat, lon) ;

    Note

    People_UserName is implicitly added by the metadata.

13.23.5.1. Translator Configuration Options

13.23.5.1.1. Execution Options
Use execution properties to extend or limit the functionality of the translator to match the capabilities of the physical data source. You will sometimes need to adjust the defaults so that the translator works in the manner you expect.

Table 13.21. Execution Properties

NameDescriptionDefault
SupportsOdataCountSupports $counttrue
SupportsOdataFilterSupports $filtertrue
SupportsOdataOrderBySupports $orderbytrue
SupportsOdataSkipSupports $skiptrue
SupportsOdataTopSupports $toptrue
SupportsUpdatesSupports INSERT/UPDATE/DELETEtrue
Sometimes the OData server might not have implemented the entire OData specification. If your OData implementation does not support a certain feature, then turn off the corresponding capability via the "execution Properties" so that you do not encounter any unexpected behaviour. Here is how you do so:
  1. To turn off $filter, add the following configuration to your vdb.xml file: 
    <translator name="odata-override" type="odata">
     <property name="SupportsOdataFilter" value="false"/>
</translator>
  2. Use "odata-override" as the source model's translator name.
13.23.5.1.2. Importer Properties
If you wish, you can set the importer's properties. This allows you to define the behavior of the translator when it is importing the metadata from the physical source giving you more flexibility:

Table 13.22. Importer Properties

NameDescriptionDefault
schemaNamespaceThis is the namespace of the schema to import.null
In this example, the importer is told to import only those tables and views from the TripPin service that have been exposed on odata.org: 
<property name="importer.schemaNamespace" value="Microsoft.OData.SampleService.Models.TripPin"/>

Note

You can leave this property undefined. If you do so, the EntityContainer's default name is used instead.
13.23.5.1.3. JCA Resource Adapter
The resource adapter for this translator is a web service data source.

Important

You cannot perform native or direct query execution through the OData translator. However, to work around this you can use the Web Services Translator's invokehttp method directly to issue a REST-based call. You can then parse the results with SQLXML.