Denodo OData4 Custom Wrapper - User Manual

Download original document


You can translate the document:

Introduction

OData is a protocol to access data created by Microsoft. In 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 Design Studio

  1. Navigate to File → Extension management
  2. Click on the “Import” button and select the “denodo-odata4-wrapper-{denodo-version}-{version}-jar-with-dependencies.jar” file downloaded from the Denodo Support Site.

Please, note that since Denodo 9 the Denodo OData4 Custom Wrapper is integrated into the Denodo Platform so there is no need to import manually the tool. See the OData 4 Sources section of the Virtual Dataport Administration Guide for further details.

Create the OData data source in Denodo 8.0

To create a new OData custom data source:

  1. In Design Studio, 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.OData4Wrapper. Please note that the class name is OData4Wrapper instead of ODataWrapper, which is the legacy one.

  • 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:
  1. Service Endpoint (mandatory): is the URL to the OData service. Here you can configure all the Endpoint parameters, grouped in four tabs: Configuration, Proxy, Authentication and Filters. Check the HTTP Path section of the VDP manual for further instructions.
  2. Service Format (mandatory): is the format used by the OData custom wrapper to access the OData service. Must be one of these:
  1. JSON
  2. XML-Atom

Note that for versions prior to 8.0 20251110, the data source needs to be created as explained in the next section. New versions of the Custom Wrapper in Denodo 8.0 will also support this legacy way to create the OData data sources, but it is not recommended as it has been deprecated since Denodo 9.

Create the OData data source in Denodo 8.0 before 20251110 version

To create a new OData custom data source:

  1. In Design Studio, go to: File → New → Data source → Custom
  2. 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.

  1. Click on the “Save” 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:

  • 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 to 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.

  • Use key predicates in URL paths when possible: if checked, key predicates will be used in URL paths instead of filtering.

Example

  1. Create a base view over this test service:

a)

  • Data source
  1. Service Endpoint
  1. Base URL =  

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

  1. Service Format = XML-Atom
  • Base view
  1. Entity Collection = Products
  2. Expand Related Entities = false
  3. Enable Pagination = false
  4. Load Streams = false

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

  • Data source
  1. Service Endpoint
  1. Base URL =
    http://services.odata.org/V4/(S(gsrk2wcskjydxw2iixw3kvdr))/OData/OData.svc/
  2. Request timeout  = 1000000
  3. Proxy (Manual)
  1. Host = proxy.denodo.com
  2. Port = 3128
  3. Proxy login = guest
  4. Proxy password = ******
  1. Service Format = XML-Atom
  • Base view
  1. Entity Collection = Products
  2. Expand Related Entities = false
  3. Enable Pagination = false
  4. Load Streams = false

  1. The schema of the base view is shown. You can select Execution panel and then Execute to test if the view is working as expected:


  1. Now 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 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.

Dynamics Endpoint Limitations

There is a limit in the number of related entities that can be returned from the Dynamics endpoint since Microsoft Dynamics does not allow to include more than 10 entities.

This limitation of Dynamics should be easily overcome by creating a derived view in Denodo on top of the base view, and including in its projection only the desired entities.

Other ways to consume OData sources

Using ATOM/XML

Is possible to access the 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 Design Studio, 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:  https://services.odata.org/V4/OData/OData.svc/Products?$format=atom

  1. Create a new base view:

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

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

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 Design Studio, 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 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