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 the Denodo version number of your installation.
The Denodo OData Service distribution consists of:
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:
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:
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 |
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 disable.oauth2Authentication=false debug.enabled=false enable.streaming=true |
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 deploy 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 server (see the Configuring browsers for Kerberos section for more information).
For deploying the service in the internal web container of the Denodo Platform you have two options, with 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.
You should follow these steps:
Make sure to modify the DENODO_HOME variable in the script files to point to your Denodo installation.
http://localhost:9090/denodo-odata4-service-<VERSION>/denodo-odata.svc/<DBNAME>
This option is only for Windows systems.
After launching of the service you can navigate to:
http://localhost:9090/denodo-odata4-service-<VERSION>/denodo-odata.svc/<DBNAME>
If you deploy the Denodo OData Service into the Denodo embedded web container you must declare the JNDI resource in:
Below, there is an example:
<GlobalNamingResources> <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 and maxWait for --> <!-- compatibility between Tomcat versions --> maxWaitMillis="5000" maxWait="5000" <!-- maxTotal and maxActive for compatibility --> <!-- between Tomcat versions --> maxTotal="20" maxActive="20" maxIdle="5" validationQuery="select 1" poolPreparedStatements="true"/> </GlobalNamingResources> |
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.
<Context> <ResourceLink name="jdbc/VDPdatabase" global="jdbc/VDPdatabase" type="javax.sql.DataSource"/> </Context> |
If you deploy the Denodo OData Service into the Denodo embedded web container you must declare the JNDI resource in:
<Context> <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 and maxWait for --> <!-- compatibility between Tomcat versions --> maxWaitMillis="5000" maxWait="5000" <!-- maxTotal and maxActive for compatibility --> <!-- between Tomcat versions --> maxTotal="20" maxActive="20" maxIdle="5" minIdle="0" validationQuery="select 1" poolPreparedStatements="true"/> </Context> |
* The settings described for Denodo Platform 6.0 can work 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.
Declare the properties values in the context.xml file:
<Context> <!-- OPTIONAL PARAMETER --> <Environment type="java.lang.String" name="odataserver.address" value="/denodo-odata.svc"/>
<!-- OPTIONAL PARAMETER --> <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="disable.oauth2Authentication" value="false"/> <Environment type="java.lang.Boolean" name="debug.enabled" value="false"/> <Environment type="java.lang.Boolean" name="enable.streaming" value="true"/> </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.
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:
<!DOCTYPE doc [ <!ENTITY cors_settings SYSTEM "cors_settings.xml"> ]> <web-app ...> |
And add the reference in the filters section:
<!-- ******************* --> <!-- Filters --> <!-- ******************* --> &cors_settings; |
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 do not contain the protocol are invalid. E.g. foo.com is invalid.
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.
Users of OData Service should have the following VDP privileges assigned:
OData 4.0 defines three levels of conformance for OData services. OData Denodo Service conforms to the Intermediate Conformance Level providing the following functionality:
There are two types of metadata documents:
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" } ] } |
http://localhost:8080/denodo-odata.svc/movies/$metadata
Example of an entity country that has a relationship with zero or more city elements:
<EntityType Name="country"> <Key> <PropertyRef Name="country_id"/> </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 |
- |
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
Note that you cannot access entities with white spaces in the name, according to the OData 4 specification.
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.
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)
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" } |
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 |
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" } |
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 |
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.
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:
/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.
/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" |
/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:
Note that, if specified, $format overrides any value specified in the Accept header.
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.
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:
<EntityType Name="country"> <Key> <PropertyRef Name="country_id"/> </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> |
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" } ] } |
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)" } ] } |
OData defines some query options that allows refining the requests: $filter, $select, $orderby and $expand.
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:
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) |
The following functions are available for strings operations:
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' |
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 |
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() |
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" } }, ... |
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
$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": "An Astounding Epistle of a Database Administrator And an 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": "An Astounding Epistle of a Database Administrator And an 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 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'
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" } ] } |
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" } ] } |
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" }, ... |
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.
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
The Entity Collection Streaming option enables support for chunked HTTP responses without the need to have the entire Entity Collection pre-loaded (which probably would be in memory).
To enable this option it is necessary to carry out the following two steps:
Please, note that properties configured at the web container as JNDI entries have higher precedence than those established at the configuration.properties file.
By default, the configuration.properties configuration file has the value of the property set to true.
The header should be included in the request as follows:
Prefer: odata.entitystreaming=true
For Entity Collection Streaming support enabled, the previous two parameters (enable.streaming and odata.entitystreaming) are mandatory to be established and its value to be true.
Because the browser is connected to the Denodo OData Service which is communicating with a VDP server, Kerberos authentication and delegation should be enabled for the browser.
By default Firefox does not enable SPNEGO authentication, and consequently Kerberos.
Restart browser and see if everything works.
Restart browser and see if everything works.
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.
The access to Denodo databases via this Denodo OData Service is read-only.
Denodo OData Service does not provide support for the operator has.
Denodo OData Service does not provide support for functions:
Any requests containing these functions will return the error code: 501 Not Implemented.
Denodo OData Service does not provide support for lambda operators: any and all.
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.
Denodo OData Service does not allow navigation properties as selection clauses.
The $search system query option is not available in Denodo OData Service.
Denodo OData Service does not provide support for the $levels expand option that allows requesting recursive expands.
Denodo OData Service does not provide support for the resource ~/crossjoin to represent Cartesian products of entities.
Denodo OData Service does not provide support for the resource ~/$entity that allows resolving entity references using the query option $id.
Denodo OData Service cannot access entities with white spaces in the name. VDP allows white space in the base view names but white spaces are not allowed in entity names according to the OData 4 specification.
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.
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.
Denodo OData Service does not provide support for the $orderby query option inside $expand system query option.
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.
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.
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.
Denodo OData Service does not disable user access to the admin user when using Kerberos authentication.
An $expand expression is a comma-separated list of navigation properties that specifies the related entities that should be represented inline.
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.