Denodo OData 4.0 Service - User Manual

Overview

OData (Open Data Protocol) is a REST-based protocol for querying and updating data using simple HTTP messages. It is an OASIS standard based on technologies such as HTTP, Atom/XML and JSON.

Denodo OData Service allows users to connect to the Denodo Platform and query its databases using an OData 4.0 interface.

Previous version of the Denodo OData Service was interoperable with OData 2.0. From OData 2.0 several features were added, improved or pruned. Denodo OData Service 4 addresses these changes.

Note that this document will use <VERSION> as a placeholder for the specific version of the Denodo Platform you are installing the Denodo OData Service for. So this placeholder should be replaced by 5.5 or 6.0 depending on your Denodo version.

Installation

The Denodo OData Service distribution consists of:

A war file: denodo-odata4-service-<VERSION>.war

A documentation folder containing this user manual

A scripts folder containing the scripts to install the service into the Denodo embedded web container

For running the Denodo OData Service you need to deploy the war file in Apache Tomcat 7+ (using Java 7 or later). You need to include the Virtual DataPort (VDP) driver in the folder /lib of Apache Tomcat 7+.

VDP driver (denodo-vdp-jdbcdriver.jar) is in the folder:

$DENODO_HOME\lib\vdp-jdbcdriver-core in Denodo Platform 5.5

$DENODO_HOME\tools\client-drivers\jdbc in Denodo Platform 6.0

The web application container must provide the data source configuration in order to connect to the Virtual DataPort server using JNDI. The JNDI name of the resource must be jdbc/VDPdatabase. See the Configuring JNDI resources in Apache Tomcat section for more information.

The Denodo OData Service has the following configuration properties:

odataserver.address: service name. It is an optional property, /denodo-odata.svc by default.

odataserver.serviceRoot: root URI of the service. It is an optional property, empty by default. It should be configured when the OData service is going to be accessed through a gateway, so links within the OData response use this URI as root.

For example, being:

odataserver.serviceRoot=https://gw.denodo.com:9000/ODATA/

and accessing:

http://server001:9090/denodo-odata4-service-<VERSION>/denodo-odata.svc/movies

will return this kind of URI within the response:

https://gw.denodo.com:9000/ODATA/denodo-odata.svc/movies

server.pageSize: number of returned entries per request (see Pagination section for more information)

enable.adminUser: false if access to the OData service is denied to the admin user. Note that this functionality can be used only with Basic authentication, not with Kerberos authentication.

disable.kerberosAuthentication: true if the access to the OData service is not allowed using Kerberos authentication. Note that if this property is set to true, Basic authentication or OAuth 2.0 authentication (only available for Denodo Platform 7.0) has to be available.

disable.basicAuthentication: true if the access to the OData service is not allowed using HTTP Basic authentication. Note that if this property is set to true, OAuth 2.0 authentication (only available for Denodo Platform 7.0) or Kerberos authentication has to be available.

disable.oauth2Authentication: true if the access to the OData service is not allowed using OAuth 2.0 authentication. Note that this property is only available for Denodo Platform 7.0 and if is set to true, Basic authentication or Kerberos authentication has to be available.

debug.enabled: true for support purposes. With this property enabled the service response contains additional helpful data. It is false by default.

The war file includes a configuration file

WEB-INF/classes/configuration.properties that has default values for the properties explained above:

odataserver.address=/denodo-odata.svc

odataserver.serviceRoot=

server.pageSize=1000

enable.adminUser=true

disable.kerberosAuthentication=false

disable.basicAuthentication=false

debug.enabled=false

Besides, these properties can be provided by the web container via JNDI (see the Configuring JNDI resources in Apache Tomcat section for more information).

Properties configured at the web container as JNDI entries have higher precedence than those established at the configuration.properties file.

Once you deployed the war you can use the Denodo OData Service from a web client, using HTTP Basic Authentication with VDP-valid credentials. URLs are of the form:

http://localhost:8080/denodo-odata.svc/<DBNAME>

Note that for the sake of simplicity in this document, we will consider the Denodo OData Service to be installed at the ROOT context of the web server.

Kerberos Authentication is also supported when Kerberos authentication is enabled in the Virtual DataPort 6.0 server (see the Configuring browsers for Kerberos section for more information).

Deploying into the Denodo embedded web container

For deploying the service in the internal web container of the Denodo Platform you have two options, with the option A you can execute manually the scripts. And with the option B, you can run the start and the stop of the application as a Service in Windows NT/2000/2003/XP/Vista.

Option A

You should follow these steps:

Copy the denodo-odata4-service-<VERSION>.war into <DENODO_HOME>/resources/apache-tomcat/webapps/

Create a context xml file for the service. You can use the xml file in the Denodo OData Service distribution as a template: /scripts/denodo-odata4-service-<VERSION>.xml.

Copy the denodo-odata4-service-<VERSION>.xml file into <DENODO_HOME>/resources/apache-tomcat/conf/DenodoPlatform-<VERSION>/localhost

Check in <DENODO_HOME>/resources/apache-tomcat/conf/catalina.properties file that the common.loader property (shared.loader in Denodo 5.5) includes a reference to the VDP JDBC Driver. If missing, add it:

${catalina.base}/../../tools/client-drivers/jdbc/denodo-vdp-jdbcdriver-basic.jar for Denodo Platform 6.0

${catalina.base}/../../lib/vdp-jdbcdriver-core/denodo-vdp-jdbcdriver-basic.jar for Denodo Platform 5.5

Create launch scripts for the service. You can use as a template the files located at the scripts folder in the Denodo OData Service distribution:

odata4_service_startup.bat(.sh)

odata4_service_shutdown.bat(.sh)

Make sure to modify the DENODO_HOME variable in the script files to point to your Denodo installation.

Copy the launch scripts into <DENODO_HOME>/bin.

Configure via JNDI the VDP data source and those properties you want to change their default value (see the Configuring JNDI resources in Apache Tomcat for more information).

Run the <DENODO_HOME>/bin/odata4_service_startup.bat(.sh) script and navigate to:

http://localhost:9090/denodo-odata4-service-<VERSION>/denodo-odata.svc/<DBNAME>

Option B

This option is only for Windows systems.

Same as option A

Create service script for the Denodo Odata Service (odata4_service_service). You can use the attached template. Make sure to modify the DENODO_HOME variable in the script templates to point to your Denodo installation.

Create the file service.conf and copy in the folder <DENODO_HOME>/conf/denodo-odata4-service-service/, you can use the attached template. Make sure to modify the DENODO_HOME variable in the script templates to point to your Denodo installation.

Install as a Windows service. Execute odata4-service-service.bat install

You can manage the installed service from the application of Windows: Services.

After launching of the service you can navigate to:

http://localhost:9090/denodo-odata4-service-<VERSION>/denodo-odata.svc/<DBNAME>

Configuring JNDI resources in Apache Tomcat

Data source configuration

For Denodo Platform 6.0

Declare the data source with the name jdbc/VDPdatabase in Apache Tomcat’s server.xml file.

If you deploy the Denodo OData Service into the Denodo embedded web container you must declare the JNDI resource in:

<DENODO_HOME>/resources/apache-tomcat/conf/server.xml file

Below, there is an example:

<Resource name="jdbc/VDPdatabase"

auth="Container"

type="javax.sql.DataSource"

username="admin" password="admin"

url="jdbc:vdb://localhost:9999/admin"

driverClassName="com.denodo.vdp.jdbc.Driver"

initialSize="5"

maxWaitMillis="5000"

maxWait="5000"

maxTotal="20"

maxActive="20"

maxIdle="5"

validationQuery="select 1"

poolPreparedStatements="true"/>

</GlobalNamingResources>

Reference the JNDI resource in the Apache Tomcat’s context.xml file.

If you deploy the Denodo OData service into the Denodo embedded web container the context.xml file is in the

<DENODO_HOME>/resources/apache-tomcat/conf folder.

<ResourceLink name="jdbc/VDPdatabase"

global="jdbc/VDPdatabase"

type="javax.sql.DataSource"/>

</Context>

For Denodo Platform 5.5:

Declare the data source with the name jdbc/VDPdatabase in Apache Tomcat’s context.xml file.

If you deploy the Denodo OData Service into the Denodo embedded web container you must declare the JNDI resource in:

<DENODO_HOME>/resources/apache-tomcat/conf/context.xml file

<Resource name="jdbc/VDPdatabase"

auth="Container"

type="javax.sql.DataSource"

username="admin" password="admin"

url="jdbc:vdb://localhost:9999/admin"

driverClassName="com.denodo.vdp.jdbc.Driver"

initialSize="5"

maxWaitMillis="5000"

maxWait="5000"

maxTotal="20"

maxActive="20"

maxIdle="5"

minIdle="0"

validationQuery="select 1"

poolPreparedStatements="true"/>

</Context>

* The settings described for Denodo Platform 6.0 can works with 5.5 but in the initialization of Tomcat embedded would appear a spurious error that you could ignore.

* The parameter minIdle could be greater than 0. In this way the performance of the connections would be better, but other applications could maintain open connections that never will be used.

Properties configuration

Declare the properties values in the context.xml file:

<Environment type="java.lang.String"

name="odataserver.address"

value="/denodo-odata.svc"/>

<Environment type="java.lang.String"

name="odataserver.serviceRoot"

value="https://gw.denodo.com:9000/ODATA/"/>

<Environment type="java.lang.Integer" name="server.pageSize"

value="1000"/>

<Environment type="java.lang.Boolean" name="enable.adminUser"

value="false"/>

<Environment type="java.lang.Boolean"

name="disable.kerberosAuthentication"

value="true"/>

<Environment type="java.lang.Boolean"

name="disable.basicAuthentication"

value="false"/>

<Environment type="java.lang.Boolean"

name="debug.enabled" value="false"/>

</Context>

If you deploy the service in the internal web container of the Denodo Platform, this file is at <DENODO_HOME>/resources/apache-tomcat/conf.

Enabling Cross-origin resource sharing (CORS)

Cross-origin resource sharing (CORS) is a mechanism whereby a browser and server interact to determine whether to allow a web page to perform HTTP requests to a domain other than the domain from which the web page is originated. I.e. so the browser can determine if it is safe that a page served from the domain http://foo.com can send requests to a server in the domain http://bar.com.

Once deployed the Denodo OData Service, follow these steps to enable the CORS support:

Edit the web.xml file which is at the WEB-INF folder. If you have deployed the Denodo OData Service into the Denodo embedded web container the path to the directory is <DENODO_HOME>/resources/apache-tomcat/webapps/denodo-odata4-service-<VERSION>/WEB-INF/.

Add the External Entity adding the elements DOCTYPE and ENTITY above the root element web-app:

<!DOCTYPE doc [

<!ENTITY cors_settings SYSTEM "cors_settings.xml">

<web-app ...>

And add the reference in the filters section:

&cors_settings;

Edit the file cors_settings.xml which is at the WEB-INF folder. If you have deployed the Denodo OData Service into the Denodo embedded web container the path to the directory is <DENODO_HOME>/resources/apache-tomcat/webapps/denodo-odata4-service-<VERSION>/WEB-INF/.

Uncomment the contents of the file.

If the value of the property is *, the Service will allow CORS requests received from any domain.

To limit the domains from which CORS requests are allowed, change the value of the property cors.allowOrigin. Replace * with the list of allowed URLs. Separate each URL by a comma. For example: https://foo.com, http://foo.com, https://foo.bar.com.

CORS requests from any other origin will be denied with the HTTP code 403 (Forbidden)

Important: for each URL, enter its protocol as well. URLs that not contain the protocol are invalid. E.g. foo.com is invalid.

Restart the Denodo OData Service to apply the changes.

Note that if you have deployed the Denodo OData Service in an external web container you need to configure the container to allow external entities to be loaded or, manually replace in web.xml file at WEB-INF folder &cors_settings reference with the content of the file cors_settings.xml which is at WEB-INF folder and remove the elements DOCTYPE and ENTITY above the root element web-app.

Virtual DataPort Privilege Requirements

Users of OData Service should have the following VDP privileges assigned:

At database level

Connect

At view level

Metadata (for Denodo Platform 6.0)

Read: If you do not have Read access to an entire database, but you have it over some of its elements, the OData Service will only list the elements that you have Read privilege over.

Features

OData 4.0 defines three levels of conformance for OData services. OData Denodo Service conforms to the Intermediate Conformance Level providing the following functionality:

Read-only access to Denodo databases:

Metadata
Entities
Items of an entity
Properties of an item
Property values
Relationships between entities

Format results in Atom and JSON

Query options

$select
$filter
$expand
$orderby
$skip
$top
$count

Parameter aliases

Pagination

HTTP Authentication

Basic
Kerberos
OAuth 2.0 (only available for Denodo Platform 7.0)

Serving Metadata

There are two types of metadata documents:

The Service Document lists all the entities offered by the data service.

It is available at the root URI of the service, specifying the database name where we are going to get information: /denodo-odata.svc/<DBNAME>.

Below, there is an example where the accessible collections of movies database are actor, address, city, country, film and film_actor.

http://localhost:8080/denodo-odata.svc/movies

{

"@odata.context": "/denodo-odata.svc/movies/$metadata",

"value": [

{

"name": "actor",

"url": "actor"

{

"name": "address",

"url": "address"

{

"name": "city",

"url": "city"

{

"name": "country",

"url": "country"

{

"name": "film",

"url": "film"

{

"name": "film_actor",

"url": "film_actor"

}

]

}

The Service Metadata Document, also called Entity Data Model (EDM), is an XML representation of the data model exposed by the service. It is available at .../$metadata:

http://localhost:8080/denodo-odata.svc/movies/$metadata

Example of an entity country that has a relationship with zero or more city elements:

<Key>

</Key>

<Property Name="country_id" Type="Edm.Int16"

Nullable="false"/>

<Property Name="country" Type="Edm.String"

MaxLength="50"/>

<Property Name="last_update" Type="Edm.DateTimeOffset"

Precision="19"/>

<NavigationProperty Name="cities"

Type="Collection(com.denodo.odata4.city)"

Partner="country">

<ReferentialConstraint Property="country_id"

ReferencedProperty="country_id"/>

</NavigationProperty>

</EntityType>

The Denodo OData Service maps these OData structures to VDP concepts like this:

Denodo OData Service	VDP
Entity Type	View Definition
Entity Type > Property	View Column
Entity Type > Navigation Property	Association Role
Relationship	Association Definition
Entity Set	View Data
Imported Functions	-

Querying Data: The Basics

Querying collections

In the Service Metadata Document (see the Serving Metadata section for more information) you can see the entity set names and to see their data you must use the following URL:

/denodo-odata.svc/<DBNAME>/collectionName

Example:

/denodo-odata.svc/movies/actor

Response:

{

"@odata.context": "/denodo-odata.svc/movies/$metadata#actor",

"value": [

{

"actor_id": 1,

"first_name": "PENELOPE",

"last_name": "GUINESS",

"last_update": "2006-02-15T11:34:33Z"

…

{

"actor_id": 1000,

"first_name": "CHRISTIAN",

"last_name": "GABLE",

"last_update": "2006-02-15T11:34:33Z"

}

"@odata.nextLink": "/denodo-odata.svc/movies/actor?$skiptoken=1000"

Note that, for the sake of simplicity, we are removing the server and port from the examples.

Obtaining items by primary key

Each item could be identified using its primary key property:

denodo-odata.svc/<DBNAME>/collectionName(keyvalue)

Examples:

/denodo-odata.svc/movies/actor(1)

/denodo-odata.svc/movies/store_category('F0')

The primary key can be a compound key, and in that case you must include all values separated by commas:

/denodo-odata.svc/<DBNAME>/collectionName(key1,key2)

Example:

/denodo-odata.svc/movies/film_actor(actor_id=1,film_id=1)

Accessing individual properties

Properties of an item can be accessed individually:

/denodo-odata.svc/<DBNAME>/collectionName(key)/propertyName

Example:

/denodo-odata.svc/movies/actor(1)/first_name

Response:

{

"@odata.context": "/denodo-odata.svc/movies/$metadata

#actor/first_name",

"value": "PENELOPE"

}

Accessing individual property values

The value of a property is available as a raw value:

/denodo-odata.svc/<DBNAME>/collectionName(key)/propertyName/$value

Example:

/denodo-odata.svc/movies/actor(1)/first_name/$value

Response:

PENELOPE

Accessing complex properties

Properties can be complex but they are also accessible. You must point out the property path, from the complex to the simple one:

/denodo-odata.svc/<DBNAME>/collectionName(key)/propName/complexProp/propName

For example, for the following film_data complex field in a struct_table_film entity:

{

"@odata.context": "/denodo-odata.svc/movies/$metadata

#struct_table_film/$entity",

"table_id": 1,

"film_data": {

"id": 1,

"title": "ACADEMY DINOSAUR",

"description": "ELIZABETH"

}

we could perform the following call:

/denodo-odata.svc/movies/struct_table_film(1)/film_data/title

Response:

{

"@odata.context": "/denodo-odata.svc/movies/$metadata

#struct_table_film/title",

"value": "ACADEMY DINOSAUR"

}

Counting elements in a collection: $count

If you want to know the number of elements in a collection you have to add $count to the URL:

/denodo-odata.svc/<DBNAME>/collectionName/$count

Example:

/denodo-odata.svc/movies/actor/$count

Response:

200

Establishing the response format

OData resources can be represented in Atom or JSON, being JSON the default format.

Clients can request the format through the system query option $format or through the Accept header. In the case that both the Accept header and the $format query option are specified on a request, the $format query option has a higher precedence.

JSON format

This format can be requested using the system query option $format that shows data using the JSON format as defined in OData:JSON:

/denodo-odata.svc/movies/actor?$format=json

Response:

{

"@odata.context": "/denodo-odata.svc/movies/$metadata#actor",

"value": [

{

"actor_id": 1,

"first_name": "PENELOPE",

"last_name": "GUINESS",

"last_update": "2006-02-15T11:34:33Z"

…

{

"actor_id": 1000,

"first_name": "CHRISTIAN",

"last_name": "GABLE",

"last_update": "2006-02-15T11:34:33Z"

}

"@odata.nextLink": "/denodo-odata.svc/movies/actor?$skiptoken=1000"

You can also use the MIME type application/json optionally followed by the format parameter odata.metadata. This parameter allows users to specify the amount of control information that will be included in the response:

odata.metadata=minimal indicates that the service should remove computable control information from the payload wherever possible. This is the default value.

/denodo-odata.svc/movies/actor?$format=application/json;odata.metadata=minimal

Response:

Actor data, just the same as a request with the abbreviation json and without the parameter odata.metadata=minimal.

odata.metadata=full indicates that the service must include all control information explicitly in the payload.

/denodo-odata.svc/movies/actor?$format=application/json;odata.metadata=full

Response:

{

"@odata.context": "/denodo-odata.svc/movies/$metadata#actor",

"value": [

{

"@odata.type": "#com.denodo.odata4.actor",

"@odata.id": "/denodo-odata.svc/admin/actor(1)",

"@odata.readLink": "/denodo-odata.svc/admin/actor(1)",

"actor_id@odata.type": "#Int16",

"actor_id": 1,

"first_name": "PENELOPE",

"last_name": "GUINESS",

"last_update": "2006-02-15T11:34:33Z"

"films@odata.navigationLink": "/denodo-odata.svc/admin/actor(1)/films",

"films@odata.associationLink": "/denodo-odata.svc/admin/actor(1)/films/$ref"

…

{

"@odata.type": "#com.denodo.odata4.actor",

"@odata.id": "/denodo-odata.svc/admin/actor(1000)",

"@odata.readLink": "/denodo-odata.svc/admin/actor(1000)",

"actor_id@odata.type": "#Int16",

"actor_id": 1000,

"first_name": "CHRISTIAN",

"last_name": "GABLE",

"last_update": "2006-02-15T11:34:33Z"

"films@odata.navigationLink": "/denodo-odata.svc/admin/actor(1000)/films",

"films@odata.associationLink": "/denodo-odata.svc/admin/actor(1000)/films/$ref"

}

"@odata.nextLink": "/denodo-odata.svc/movies/actor?$skiptoken=1000"

odata.metadata=none indicates that the service should omit control information other than odata.nextLink and odata.count.

/denodo-odata.svc/movies/actor?$format=application/json;odata.metadata=none

Response:

{

"value": [

{

"actor_id": 1,

"first_name": "PENELOPE",

"last_name": "GUINESS",

"last_update": "2006-02-15T11:34:33Z"

…

{

"actor_id": 1000,

"first_name": "CHRISTIAN",

"last_name": "GABLE",

"last_update": "2006-02-15T11:34:33Z"

}

"@odata.nextLink": "/denodo-odata.svc/movies/actor?$skiptoken=1000"

Alternatively, this format can be requested using the HTTP header Accept:

Accept: application/json

Accept: application/json;odata.metadata=minimal

Accept: application/json;odata.metadata=full

Accept: application/json;odata.metadata=none

Note that, if specified, $format overrides any value specified in the Accept header.

Atom format

This format can be requested using the system query option $format that shows data using the Atom format defined in OData:Atom:

/denodo-odata.svc/movies/actor?$format=atom

Response:

<?xml version='1.0' encoding='UTF-8'?>

<a:feed xmlns:a="http://www.w3.org/2005/Atom" xmlns:m="http://docs.oasis-open.org/odata/ns/metadata" xmlns:d="http://docs.oasis-open.org/odata/ns/data" m:context="/denodo-odata.svc/movies/$metadata#actor">

<a:id>/denodo-odata.svc/movies/actor</a:id>

<a:link rel="next" href="/denodo-odata.svc/movies/actor?$format=atom&$skiptoken=1000"/>

<a:entry>

<a:id>/denodo-odata.svc/movies/actor(1)</a:id>

<a:title/>

<a:summary/>

<a:updated>2016-04-22T13:07:42Z</a:updated>

<a:author>

<a:name/>

</a:author>

<a:link rel="edit" href="/denodo-odata.svc/movies/actor(1)"/>

<a:link rel="http://docs.oasis-open.org/odata/ns/related/actors" type="application/atom+xml;type=feed" title="actors" href="/denodo-odata.svc/movies/actor(1)/actors"/>

<a:category scheme="http://docs.oasis-open.org/odata/ns/scheme" term="#com.denodo.odata4.actor"/>

<a:content type="application/xml">

<m:properties>

<d:actor_id m:type="Int16">1</d:actor_id>

<d:first_name>PENELOPE</d:first_name>

<d:last_name>GUINESS</d:last_name>

<d:last_update m:type="DateTimeOffset">2006-02-15T11:34:33Z</d:last_update>

</m:properties>

</a:content>

</a:entry>

...

Alternatively, this format can be requested using the HTTP header Accept:

Accept: application/atom+xml

Note that, if specified, $format overrides any value specified in the Accept header.

Navigating Associations

Publishing VDP Associations through OData

Denodo Virtual DataPort allows you to define relationships between the elements of two views. The following example shows an association where the elements of the country view can be related with the elements of the city view. Every country is related with zero or more cities.

This relationship is represented in the Service Metadata Document as a navigation property:

<Key>

</Key>

<Property Name="country_id" Type="Edm.Int16"

Nullable="false"/>

<Property Name="last_update" Type="Edm.DateTimeOffset"

Precision="19"/>

<NavigationProperty Name="cities"

Type="Collection(com.denodo.odata4.city)"

Partner="country">

<ReferentialConstraint Property="country_id"

ReferencedProperty="country_id"/>

</NavigationProperty>

</EntityType>

Querying related entries

Denodo OData Service allows navigating the associations defined in VDP in order to get all the entities related with a particular entity. This is done by means of navigation properties.

/denodo-odata.svc/<DBNAME>/collectionName(key)/navigationPropertyName

For example, being cities a navigation property in the entity type country that navigates towards the city entity type:

/denodo-odata.svc/movies/country(2)/cities

Response:

{

"@odata.context": "/denodo-odata.svc/movies/$metadata#city",

"value": [

{

"city_id": 59,

"city": "Batna",

"country_id": 2,

"last_update": "2006-02-15T11:45:25Z"

{

"city_id": 63,

"city": "Bchar",

"country_id": 2,

"last_update": "2006-02-15T11:45:25Z"

{

"city_id": 483,

"city": "Skikda",

"country_id": 2,

"last_update": "2006-02-15T11:45:25Z"

}

]

}

Referencing related entities

You can get the references to the collection of entities (instead of the actual entities) related with one particular entity.

This is requested appending $ref to the URL path. The old syntax /$links/ has been dropped in OData 4.

/denodo-odata.svc/<DBNAME>/collectionName(key)/navProp/$ref

The URL specified below will show the references of every city associated with the entry country(2) through the navigation property cities:

/denodo-odata.svc/movies/country(2)/cities/$ref

Response:

{

"@odata.context": "/denodo-odata.svc/movies/$metadata#cities",

"value": [

{

"@odata.id": "city(59)"

{

"@odata.id": "city(63)"

{

"@odata.id": "city(483)"

}

]

}

Advanced Querying

OData defines some query options that allows refining the requests: $filter, $select, $orderby and $expand.

Selection: $filter

A URI with a $filter system query option identifies a subset of the entries from the collection that satisfy the $filter predicate expression.

Expressions can reference properties and literals. The latter can be strings (enclosed in single quotes), the null literal, numbers or boolean values.

Denodo OData service supports the following operations and functions:

Operators

Operator	Description	Example
eq	Equal	/actor?$filter=first_name eq 'GRACE'
ne	Not equal	/actor?$filter=first_name ne 'GRACE'
gt	Greater than	/actor?$filter=actor_id gt 5
ge	Greater than or equal	/actor?$filter=actor_id ge 5
lt	Less than	/actor?$filter=actor_id lt 10
le	Less than or equal	/actor?$filter=actor_id le 10
and	Logical and	/actor?$filter=actor_id gt 5 and actor_id lt 10
or	Logical or	/actor?$filter=actor_id lt 5 or first_name eq 'GRACE'
not	Logical negation	/actor?$filter=not (actor_id eq 1)
add	Addition	/film?$filter=length add 30 gt 180
sub	Subtraction	/film?$filter=length sub 30 gt 120
mul	Multiplication	/film?$filter=length mul 2 ge 300
div	Division	/film?$filter=length div 3 eq 60
mod	Modulo	/film?$filter=length mod 10 eq 8
()	Precedence grouping	/actor?$filter=actor_id lt 7 and (first_name eq 'NICK' or actor_id gt 3)

String functions

The following functions are available for strings operations:

contains(string p0, string p1) returns true when the value of the property name specified in p0 contains the string p1. Otherwise returns false.

startswith(string p0, string p1) returns true when the value of the property name specified in p0 starts with the string p1. Otherwise returns false.

endswith(string p0, string p1) returns true when the value of the property name specified in p0 ends with the string p1. Otherwise returns false.

indexof(string p0, string p1) returns the position of the string p1 in the value of the property name specified in p0.

length(p0) returns the length of the value of the property name specified in p0.

substring(string p0, int pos) returns a new string that is a substring of the value of the property name specified in p0. The substring begins with the character at the specified pos and extends to the end of this string.

substring(string p0, int pos, int length) returns a new string that is a substring of the value of the property name specified in p0. The substring begins at the specified pos and extends to the character at index pos + length.

tolower(string p0) returns a copy of the value of the property name specified in p0 converted to lowercase.

toupper(string p0) returns a copy of the value of the property name specified in p0 converted to uppercase.

trim(string p0) returns a copy of the value of the property name specified in p0 with leading and trailing whitespace omitted.

concat(string p0, string p1) returns a new string that is a concatenation of the string p0 and the string p1.

The following table shows a summary and examples of these functions:

Function	Example
bool contains(string p0, string p1)	/actor?$filter=contains(first_name, 'LO')
bool startswith(string p0, string p1)	/actor?$filter=startswith(first_name, 'JO')
bool endswith(string p0, string p1)	/actor?$filter=endswith(first_name,'ER')
int indexof(string p0, string p1)	/actor?$filter=indexof(last_name, 'LO') eq 3
int length(string p0)	/actor?$filter=length(first_name) eq 4
string substring(string p0, int pos)	/actor?$filter=substring(first_name, 2) eq 'RO'
string substring(string p0, int pos, int length)	/actor?$filter=substring(first_name, 2,3) eq 'TTH'
string tolower(string p0)	/actor?$filter=tolower(first_name) eq 'nick'
string toupper(string p0)	/actor?$filter=toupper(first_name) eq 'NICK'
string trim(string p0)	/actor?$filter=trim(first_name) eq 'JENNIFER'
string concat(string p0, string p1)	/actor?$filter=concat( concat(first_name,', '), last_name) eq 'JENNIFER, DAVIS'

Math functions

There are three math functions: round, floor, ceiling. Each one allows Double or Decimal types as parameters and the returned value is of the same type as the parameter.

Function	Example
round	/film?$filter=round(replacement_cost) eq 21
floor	/film?$filter=floor(replacement_cost) eq 20
ceiling	/film?$filter=ceiling(replacement_cost) eq 21

Date functions

Function	Example
int year(DateTimeOffset p0) int year(Date p0) -since Denodo 7.0	/actor?$filter=year(last_update) eq 2016
int month(DateTimeOffset p0) int month(Date p0) -since Denodo 7.0	/actor?$filter=month(last_update) eq 12
int day(DateTimeOffset p0) int day(Date p0) -since Denodo 7.0	/actor?$filter=day(last_update) eq 31
int hour(DateTimeOffset p0) int hour(TimeOfDay p0) -since Denodo 7.0	/actor?$filter=hour(last_update) eq 3
int minute(DateTimeOffset p0) int minute(TimeOfDay p0) -since Denodo 7.0	/actor?$filter=minute(last_update) eq 34
int second(DateTimeOffset p0) int second(TimeOfDay p0) -since Denodo 7.0	/actor?$filter=second(last_update) eq 33
DateTimeOffset now()	/actor?$filter=last_update lt now()

Projection: $select

The $select system query option returns only the properties explicitly requested.

$select expression can be a comma-separated lists of properties or the star operator (*), which will retrieve all the properties.

Example:

/denodo-odata.svc/movies/actor?$select=actor_id,first_name,last_name

Response:

{

"@odata.context":"/denodo-odata.svc/movies/$metadata#actor

(actor_id,first_name,last_name)",

"value": [

{

"actor_id": 1,

"first_name": "PENELOPE",

"last_name": "GUINESS"

...

Another example:

/denodo-odata.svc/movies/actor?$select=*

Response:

{

"@odata.context": "/denodo-odata.svc/movies/$metadata#actor(*)",

"value": [

{

"actor_id": 1,

"first_name": "PENELOPE",

"last_name": "GUINESS",

"last_update": "2006-02-15T11:34:33Z"

...

Note that complex properties can be used in $select expressions:

denodo-odata.svc/admin/struct_table_film?$select=film_data/title

Response:

"@odata.context":"/denodo-odata.svc/admin/$metadata

#struct_table_film(film_data/title)",

"value": [

{

"@odata.id": "/denodo-odata.svc/admin/struct_table_film(1)",

"film_data": {

"title": "ACADEMY DINOSAUR"

}

...

Ordering results: $orderby

The $orderby query string option specifies the order in which items are returned:

/denodo-odata.svc/<DBNAME>/collectionName?$orderby=attribute [asc|desc]

To order the collection the resource path must identified a collection of entries, otherwise this option is unavailable.

The keywords asc and desc determine the direction of the sort (ascending or descending, respectively). If asc or desc are not specified items are returned in ascending order. Null values come before non-null values when sorting in ascending order and vice versa.

You can also sort by multiple attributes:

/denodo-odata.svc/<DBNAME>/collectionName?$orderby=attribute1[asc|desc],attribute2 [asc|desc]

Example:

/denodo-odata.svc/movies/address?$orderby=zip,client_identifier desc

Including related resources: $expand

$expand is available since Denodo Platform 6.0.

An $expand expression is a comma-separated list of navigation properties that specifies the related entities that should be represented inline.

Example:

/denodo-odata.svc/movies/country?$expand=cities

Response:

{

"@odata.context": "/denodo-odata.svc/movies/$metadata#country",

"value": [

{

"country_id": 1,

"country": "Afghanistan",

"last_update": "2006-02-15T11:44:00Z",

"city": [

{

"city_id": 251,

"city": "Kabul",

"country_id": 1,

"last_update": "2006-02-15T11:45:25Z"

}

]

...

The following is an example with two navigation properties. This URI identifies the film set as well as the film_actor (actors is the navigation property) and the film_category (categories is the navigation property) associated with each film:

/denodo-odata.svc/movies/film?$expand=actors,categories

Response:

...

{

"film_id": 2,

"title": "ACE GOLDFINGER",

"description": "A Astounding Epistle of a Database

Administrator And a Explorer who must Find a

Car in Ancient China",

"release_year": "2005-12-31T23:00:00Z",

"language_id": 1,

"original_language_id": null,

"rental_duration": 3,

"rental_rate": 4.99,

"length": 48,

"replacement_cost": 12.99,

"rating": "G",

"special_features": "Trailers,Deleted Scenes",

"last_update": "2006-02-15T12:03:42Z",

"categories": [

{

"film_id": 2,

"category_id": 11,

"last_update": "2006-02-15T12:07:09Z"

}

"actors": [

{

"actor_id": 19,

"film_id": 2,

"last_update": "2006-02-15T12:05:03Z"

{

"actor_id": 85,

"film_id": 2,

"last_update": "2006-02-15T12:05:03Z"

{

"actor_id": 90,

"film_id": 2,

"last_update": "2006-02-15T12:05:03Z"

{

"actor_id": 160,

"film_id": 2,

"last_update": "2006-02-15T12:05:03Z"

}

]

...

Expanded entities can be filtered, ordered, paged, projected and expanded. Allowed system query options are $filter, $select, $orderby, $skip, $top, $count and $expand. These expand options are expressed as a semicolon-separated list enclosed in parentheses:

/denodo-odata.svc/movies/film?$expand=categories($select=last_update)

/denodo-odata.svc/movies/film?$expand=categories($orderby=last_update asc)

/denodo-odata.svc/movies/film?$expand=actors($count=true)

Response:

...

{

"film_id": 2,

"title": "ACE GOLDFINGER",

"description": "A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient

China",

"release_year": "2005-12-31T23:00:00Z",

"language_id": 1,

"original_language_id": null,

"rental_duration": 3,

"rental_rate": 4.99,

"length": 48,

"replacement_cost": 12.99,

"rating": "G",

"special_features": "Trailers,Deleted Scenes",

"last_update": "2006-02-15T12:03:42Z",

"actors": [

{

"actor_id": 19,

"film_id": 2,

"last_update": "2006-02-15T12:05:03Z",

"categories": [

{

"film_id": 2,

"category_id": 11,

"last_update": "2006-02-15T12:07:09Z"

}

]

{

"actor_id": 85,

"film_id": 2,

"last_update": "2006-02-15T12:05:03Z",

"categories": [

{

"film_id": 2,

"category_id": 11,

"last_update": "2006-02-15T12:07:09Z"

}

]

{

"actor_id": 90,

"film_id": 2,

"last_update": "2006-02-15T12:05:03Z",

"categories": [

{

"film_id": 2,

"category_id": 11,

"last_update": "2006-02-15T12:07:09Z"

}

]

{

"actor_id": 160,

"film_id": 2,

"last_update": "2006-02-15T12:05:03Z",

"categories": [

{

"film_id": 2,

"category_id": 11,

"last_update": "2006-02-15T12:07:09Z"

}

]

}

]

...

Parameter Aliases

Parameter aliases are identifiers prefixed with an @ sign. They can be used in query expressions to avoid stating the same literal multiple times, or deferring lengthy literals to a place where they are easier to read.

Example:

/denodo-odata.svc/movies/film?$filter=contains(title,@p1) and not contains(description,@p1)&@p1='ACADEMY DINOSAUR'

Specifying maximum number of results: $top

With the $top option you can select the n first entries of the collection, being n a non-negative integer:

/denodo-odata.svc/<DBNAME>/collectionName?$top=n

Example:

/denodo-odata.svc/movies/actor?$top=1

Response:

{

"@odata.context": "/denodo-odata.svc/movies/$metadata#actor",

"value": [

{

"actor_id": 1,

"first_name": "PENELOPE",

"last_name": "GUINESS",

"last_update": "2006-02-15T11:34:33Z"

}

]

}

Specifying offset: $skip

With the option $skip, the n first entries of the collection will not be shown in the response. n is a non-negative integer.

denodo-odata.svc/<DBNAME>/collectionName?$skip=n

Example:

/denodo-odata.svc/movies/actor?$skip=199

Response:

{

"@odata.context": "/denodo-odata.svc/movies/$metadata#actor",

"value": [

{

"actor_id": 200,

"first_name": "THORA",

"last_name": "TEMPLE",

"last_update": "2006-02-15T11:34:33Z"

}

]

}

Asking for total result count: $count

The $count system query option returns the number of items returned in the response along with the result.

The old syntax $inlinecount=allpages has been shortened in OData 4 to $count=true.

The $count system query option ignores $top, $skip, and $expand query options, and returns the total count of results across all pages including only those results matching any specified $filter .

Examples:

/denodo-odata.svc/movies/actor?$count=true

Response:

{

"@odata.context": "/denodo-odata.svc/movies/$metadata#actor",

"@odata.count": 200,

"value": [

{

"actor_id": 1,

"first_name": "PENELOPE",

"last_name": "GUINESS",

"last_update": "2006-02-15T11:34:33Z"

...

Another example:

/denodo-odata.svc/movies/actor?$count=true&$filter=actor_id eq 1

Response:

{

"@odata.context": "/denodo-odata.svc/movies/$metadata#actor",

"@odata.count": 1,

"value": [

{

"actor_id": 1,

"first_name": "PENELOPE",

"last_name": "GUINESS",

"last_update": "2006-02-15T11:34:33Z"

}

]

}

Another example:

/denodo-odata.svc/movies/actor?$count=false

Response:

Actor data, just the same as a request without $count option.

{

"@odata.context": "/denodo-odata.svc/movies/$metadata#actor",

"value": [

{

"actor_id": 1,

"first_name": "PENELOPE",

"last_name": "GUINESS",

"last_update": "2006-02-15T11:34:33Z"

...

Pagination

Whenever the Denodo OData Service has to return a collection of entries which size exceeds that configured at the server.pageSize property of its configuration file, it will split the response into pages, returning only the first 1000 entries (the default value for page size).

Denodo OData Service will add to the response a next link, which will easily allow the client to request the next page of results.

Next links include a $skiptoken parameter. They look like this:

/denodo-odata.svc/movies/actor?$skiptoken=1000

If the original request includes query options, the result will show the next link with these query options and a $skiptoken as in the examples below.

Example request:

/denodo-odata.svc/movies/actor?$top=2&$skip=1

Response:

...

"@odata.nextLink":"/denodo-odata.svc/movies/actor?$top=2&$skip=1&$skiptoken=1000"

...

Besides using the configuration property server.pageSize users can request another page size using the odata.maxpagesize preference in the Prefer header.

Debug Option

For debug purposes there is a possibility to enrich the OData service response with additional helpful data.

The additional data consists of information about the request, the response, the parsed request URI, the server environment, library timings, and the stack trace in case an error occurred.

To request the debug output for a request to the OData service the query parameter odata-debug=json or odata-debug=html must be appended to the original request URL:

http://localhost:8080/denodo-odata.svc/movies/?odata-debug=json

Configuring browsers for Kerberos

Because the browser is connecting to the Denodo OData Service which is communicating with a VDP server, Kerberos authentication and delegation should be enabled for the browser.

Firefox

By default Firefox does not enable SPNEGO authentication, and consequently Kerberos.

Enable Kerberos Authentication

Go to about:config in the address bar in Firefox

Click I'll be careful, I promise when warned about changing advanced settings

Enter negotiate in the Search box

Set value of the network.negotiate-auth.trusted-uris to your domain name

Enable Kerberos Delegation

Go to about:config in the address bar in Firefox

Click I'll be careful, I promise when warned about changing advanced settings

Enter negotiate in the Search box

Set value of the network.negotiate-auth.delegation-uris to your domain name

Restart browser and see if everything works.

Internet Explorer

Enable Kerberos Authentication

Click Tools -> Internet Options

Advanced tab

Enable checkbox for Enable Integrated Windows Authentication

Enable Kerberos Delegation

Click Tools -> Internet Options

Security tab -> Local intranet -> Sites

Add the site in question.

Restart browser and see if everything works.

Chrome

Chrome in Windows will use the Internet Explorer settings, so configure them within Internet Explorer's Tools -> Internet Options dialog as explained in the previous section.

Limitations

Read-only access

The access to Denodo databases via this Denodo OData Service is read-only.

Unavailable functions in the $filter system query option

Denodo OData Service does not provide support for the operator has.

Denodo OData Service does not provide support for functions:

fractionalseconds, date, time, totaloffsetminutes, mindatetime and maxdatetime

isof and cast

geo.distance, geo.length and geo.intersects

Any requests containing these functions will return the error code: 501 Not Implemented.

Unavailable operators in the $filter system query option

Denodo OData Service does not provide support for lambda operators: any and all.

Unavailable literals in the $filter system query option

Denodo OData Service does not provide support for the literal $it in expressions to refer to the current instance of the collection identified by the resource path.

Denodo OData Service does not provide support for the literal $root in expressions to refer to resources of the same service.

Navigation properties in the $select system query option

Denodo OData Service does not allow navigation properties as selection clauses.

Unavailable $search system query option

The $search system query option is not available in Denodo OData Service.

Unavailable expand option $levels

Denodo OData Service does not provide support for the $levels expand option that allows requesting recursive expands.

Unavailable Cross-Join queries

Denodo OData Service does not provide support for the resource ~/crossjoin to represent Cartesian products of entities.

Resolving references via the resource $entity

Denodo OData Service does not provide support for the resource ~/$entity that allows resolving entity references using the query option $id.

White spaces in the URL

Denodo OData Service does not allow white spaces between function parameters and before or after the equal sign (=) that is used to specify query options.

Redundant keys for dependent entities

Denodo OData Service does not allow the omission of redundant keys when there are key properties determined by the parent in a relationship. You have to add all key properties in each level.

Order by with $expand system query option

Denodo OData Service does not provide support for the $orderby query option inside $expand system query option.

Order by using complex properties

Denodo Virtual DataPort does not support the order by clause using fields of registers, therefore the $orderby query option is not available for complex properties.

Navigation using complex properties

When there is an association where one of the elements of an end point is a field of a register, Denodo Virtual DataPort does not allow the navigation from the end point with the complex property.

Referencing expanded entities

Denodo OData Service does not provide support for referencing a single entity in a "to-one" relationship when the related data is requested using the $expand query option and the selected format for the result is JSON.

Below, there is an example of this limitation.

/denodo-odata.svc/movies/city?$expand=country/$ref

Note that this request will fail because JSON is the default format.

Admin user and Kerberos authentication

Denodo OData Service does not disable user access to the admin user when using Kerberos authentication.

Limitations in versions prior to Denodo Platform 6.0

Unavailable $expand system query option

An $expand expression is a comma-separated list of navigation properties that specifies the related entities that should be represented inline.

Counting entries of a collection when there is navigation

Navigational queries does not allow the count(*) function. When the URL contains a navigation the option of counting items in a collection ($count) is not available.