• User Manuals »
  • Denodo OData4 Custom Wrapper - User Manual

Denodo OData4 Custom Wrapper - User Manual

Introduction

OData is a protocol to access data created by Microsoft. In the version 4 OData has become a standardized protocol of the OASIS OData Technical Committee (TC). It provides CRUD operations and is similar to JDBC or ODBC but not limited to databases.

OData uses protocols ATOM and JSON and the requests use a REST model. For this reason, OData is an implementation of the RESTful API that describes the data and their model.

OData services

There are several ways to access to an OData service from Virtual DataPort:

Denodo OData4 Custom Wrapper

The Denodo OData4 Custom Wrapper allows you to access OData services even if they require authentication (HTTP BASIC or NTLM supported) or behind a proxy server (which might also be authenticated). In addition, it supports HTTPS connections with a valid certificate.

Import the Custom Wrapper

To import the custom wrapper, follow these steps:

  1. In the VDP Administration Tool, go to:

  • Until Denodo 6.0: File → Jar management

  • From Denodo 7.0: File → Extension management

  1. Click on “Create” button and select the “denodo-odata4-wrapper-{denodo-version}-{version}-jar-with-dependencies.jar” file downloaded from Denodo Support Site.

Create the OData data source

To create a new OData custom data source:

  1. In the VDP Administration Tool, go to: File → New… → Data source → Custom

  1. In the “Create a New Custom Data Source” window, do the following:

  • Set a name for the new OData data source in the “Name” field.

  • Click on “Select Jars” and select the file imported in the previous section.

  • The “Class name” field must be filled with: com.denodo.connect.odata.wrapper.ODataWrapper

  1. Click on “Ok” button.

Create the base view

To create a new base view using the OData data source:

  1. Double-click on the OData data source and then click on “Create base view”.

  1. Set the parameters as follows:

  • Service Endpoint (mandatory): is the URL to the OData service. Must be something like: http://services.odata.org/OData/OData.svc/ 

  • Entity Collection (mandatory): must be one of the collections defined in the OData service, specified as the complete url path (without parameters) required to reach that collection. For example: "Customers" or "Company('DND')/currentCustomers".

  • Entity Name: optionally, the name of the entity type modeling the data returned by the Entity Collection, as defined in the service's EDM. If no value is specified, then the Entity Collection will be considered to appear in the Service Document and the Entity Name will be inferred from there. For example: "Customers" -> "Customer".

  • Service Format (mandatory): is the format used by the OData custom wrapper to access to the OData service. Must be one of these:

  • JSON
  • XML-Atom

  • Custom Query Options: Data service-specific information to be placed in the data service URI. A custom query option is any query option URI with the following form customOption = customValue.

In this parameter you can put several query options separated by &.

Custom query options MUST NOT begin with a $ or @ character, according to the the standard of OData 4.

For example: http://host/service/Products?debug-mode=true

  • Expand Related Entities: if checked, the references to other entities appear directly in the main entity as arrays or registers.

  • Enable Pagination: if checked, two parameters are added to the view to permit the pagination of the results:

  • fetch_size
  • offset_size

  • Load Streams: if checked, Stream properties and Stream entities will be loaded as BLOB objects instead of the link that allows the access to the media resources.

  • Pass-through session credentials: if checked, the value of the login and password fields are used for introspection. During execution, the credentials of the user authenticated in VDP are used.

  • User: OData service user for HTTP Basic Authentication or NTLM Authentication, this is an optional parameter.

  • Password: OData service password for HTTP Basic Authentication or NTLM Authentication, this is an optional parameter.

  • Timeout. Maximum time (in milliseconds) the custom wrapper will wait for a query to finish. If it is empty, it will wait indefinitely until the sentence ends.

  • Proxy Host: host to connect  to the client through a proxy, this is an optional parameter.

  • Proxy Port: port to connect  to the client through a proxy, this is an optional parameter.

  • Proxy User: user for the authentication proxy, this is an optional parameter.

  • Proxy Password: password for the authentication proxy, this is an optional parameter.

  • NTLM Domain: OData service domain for NTLM Authentication, this is an optional parameter.

  • Use NTLM Authentication: if checked, the fields “User” and “Password” will use NTLM Authentication instead of HTTP Basic Authentication. A NTLM domain could be used using the field “NTLM Domain”.

  • Use OAuth2: if checked, the protocol OAuth 2.0 will be used for authorization. With this option the fields: Access Token, Refresh Token, Client Id, Client Secret, and Token Endpoint Url are mandatory. You can use the OAuth credentials wizard to obtain the Access Token and the Refresh Token. You can read more in the VDP ADMINISTRATION GUIDE in the subsection of OAuth.

  • Access Token: You can use the OAuth credentials wizard to get it

  • Refresh Token: You can use the OAuth credentials wizard to get it. It is used when the Access Token has expired to get a new access token.

  • Client Id: consumer key from the remote access application definition.

  • Client Secret: consumer secret from the remote access application definition.

  • Token Endpoint URL: it is URL to make OAuth refresh request when the Access Token has expired.

  • Refr. Token Auth. Method: controls how the credentials are sent to the service when requesting a new OAuth access token. There are two options:

  • Include the client credentials in the body of the request (this is the default option)

  • Send client credentials using the HTTP Basic authentication scheme

  • HTTP Headers: custom headers to be used in the underlying HTTP client, this is an optional parameter.

Note: Multiple HTTP headers are allowed to be added.

The format must be as follows: field_1="value_1";field_2="value_2";...;field_n="value_n" ;. Being “field“ the name of the header and “value“ its value.

Example

  1. Create a base view over this test service:

a)

  • Service Endpoint =

http://services.odata.org/V4/(S(gsrk2wcskjydxw2iixw3kvdr))/OData/OData.svc/

  • Entity Collection = Products

  • Service Format = XML-Atom

  • Expand Related Entities = false

  • Enable Pagination = false

  • Load Streams = false

b) This is the same option but accessing with a proxy

  • Service Endpoint =

http://services.odata.org/V4/(S(gsrk2wcskjydxw2iixw3kvdr))/OData/OData.svc/

  • Entity Collection = Products

  • Service Format = XML-Atom

  • Expand Related Entities = false

  • Use NTLM Authentication = false

  • Enable Pagination = false

  • Load Streams = false

  • Proxy Host = proxy.denodo.com

  • Proxy Port = 3128

  • Proxy User = guest

  • Proxy Password = ******

  • Timeout = 1000000

  1. The schema of the base view is shown and you can rename it.


  1. After clicking on “Ok”, you can execute queries (SELECT, INSERT, UPDATE or DELETE), for example:
  • SELECT * FROM products WHERE id = 6;

  • INSERT INTO products

(id,name,description,releasedate,rating,price) VALUES

(9,'HDTV','32 inch 720p television',NOW(), 2, 600);

  • UPDATE products SET price = 800 WHERE id = 9;

  • DELETE FROM products WHERE ID = 9;

Known Limitations

  • This custom data source currently only works with OData version 4.0. The previous versions of OData are not supported by this wrapper. There is other custom wrapper to access to old versions of OData.

  • Filtering of elements by array properties is not supported. OData does not allow this kind of searches.

  • Filtering by media read links properties is not supported.

  • The insertion of arrays is not supported.

  • The authentication using NTLM through a proxy is not supported.

  • The insertion or update of  media file properties is not supported.

  • Addressing derived types properties is not available. When you have entities with a type derived from the declared type of the requested collection, their properties will be added to the schema of the base view but you can't project these properties separately.

Other ways to consume OData sources

Using ATOM/XML

Is possible to access to OData server through an URL. For example, the following URL returns all the entities of the OData server:

http://services.odata.org/V4/OData/OData.svc/

Using this URL you can access to all entities of one type:

http://services.odata.org/V4/OData/OData.svc/Products

And you can access to one entity using the identifier:

http://services.odata.org/V4/OData/OData.svc/Products(0)

Importing ATOM/XML into VDP

  1. In the VDP Administration Tool, go to: File → New… → Data source → XML

  1. Set the parameters as follows:

  1. Name: the name of the new XML data source.

  1. Data route: “HTTP Client” configured as:

  1. HTTP method: GET

  1. URL: URL to the entities. For example:  http://services.odata.org/V4/OData/OData.svc/Products

  1. Create a new base view:

  1. We can only recover data selecting “entry” in the section “Stream output at specified level”.

  1. When clicking “Ok” we have a similar base view to the next one:




  1. If we execute the view, the results are located into the field “entry”:


Using JSON

To access OData using JSON only is necessary to add the following parameter to the URL:

        ?$format=JSON

It’s also possible to access OData using JSON adding the following parameters to the HTTP header when doing a GET:

        Accept: application/json

The following is an example of using the URL to access to OData thought JSON:

http://services.odata.org/V4/OData/OData.svc/Products?$format=json

Importing JSON into VDP

The steps to access to OData using JSON are:

  1. In the VDP Administration Tool, go to: File → New… → Data source → JSON

  1. Set the parameters as follows:

  • Name: the name of the new data source.

  • Data route: “HTTP Client” configured as:

  • HTTP method: GET

  1. Create a base view from the new datasource:

  • We can get only the data setting the JSON root as: /JSONFile/value

  1. When clicking “Ok” we have a similar base view to the next one:


  1. Data are located in the field “array_value”:

  1. Data can be directly accessed if you specified the root “/JSONFile/value”:


References

OData official page

  • Documentation:

  • Libraries:

Wikipedia article:

OData references into the Microsoft webpage:

  • Create and Consume JSON-Formatted OData:

  • Building Rich Internet Apps with the Open Data Protocol:

  • OData Operations:

  • Examples:

Web pages with OData examples:

  • Example read-only service in the official web site:

  • Example read-write service in the official web site:

  • Example OData installing the module odata-server of JayData server