Denodo OData4 Custom Wrapper - User Manual

Last modified on: 04 Aug 2021

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 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:

In the VDP Administration Tool, go to:

Until Denodo 6.0: File → Jar management

From Denodo 7.0: File → Extension management

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

Create the OData data source

The following instructions are based on the Denodo 7.0 version (or later) of the Custom Wrapper. Note that the parameters for 6.0 are the same, but they are all configured right in the base view form.

To create a new OData custom data source:

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

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

Click on the “Click to refresh the input parameters of the data source” option. The data source parameters are now shown
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/

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

JSON
XML-Atom

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 the URL to make the 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.

Grant Type: OAuth implements several flows in order to be able to get new access tokens as they expire. Here, you can choose between:

Refresh Token

If you select this option, the input Refresh Token must be set as well.

Client Credentials

OAuth Extra Parameters: In some scenarios, you’ll need to send to the OAuth token server some additional information such as scope or resource, for instance. You can add as many parameters as you need, following the pattern field=”value” and you have to split these properties with “;”, as explained for HTTP Headers.

Click on the “Save” button.

Create the base view

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

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

Set the parameters as follows:

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".

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 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 the media resources.

Example

Create a base view over this test service:

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

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

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 another custom wrapper to access old versions of OData.

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

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.
With XML Atom selected as service format, the Expand related entities option won’t work. It only works using JSON as format.
OAuth Token cache of the Custom Wrapper lives in memory and is ephemeral, so it does not survive server restarts. This way, if the data source is using a refresh token that could expire in some scenarios (i.e., refresh tokens in Microsoft Identity Platform expire after 90 days), a new and valid refresh token needs to be set.