OData Version 4.0 compatibility

How to Access the data?

For example, if you have a vdb by name northwind deployed that has a customers table in a NW model, then you can access that table with an HTTP GET via the URL:

All users are granted an odata role by default, which allows all authenticated users to access odata end points. In previous versions of Teiid this role had to be manually assigned. If you still want that behavior remove the Identity login module from the teiid-security security domain that is granting the odata role.

http://localhost:8080/odata4/northwind/NW/customers

this would be akin to making a JDBC/ODBC connection and issuing the SQL:

SELECT * FROM NW.customers
Note
 Use correct case (upper or lower) in the resource path. Unlike SQL, the names used in the URI as case-sensitive.

The returned results from OData query can be in Atom/AtomPub XML or JSON format. JSON results are returned by default.

Query Basics

Users can submit predicates with along their query to filter the results:

http://localhost:8080/odata4/northwind/NW/customers?$filter=name eq 'bob'
Note
 Spaces around 'eq' are for readability of the example only; in real URLs they must be percent-encoded as %20. OData mandates percent encoding for all spaces in URLs. http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part2-url-conventions.html

this would be similar to making a JDBC/ODBC connection and issuing the SQL

SELECT * FROM NW.customers where name = 'bob'

To request the result to be formatted in a specific format, add the query option $format

http://localhost:8080/odata4/northwind/NW/customers?$format=JSON

Query options can be combined as needed. For example format with a filter:

http://localhost:8080/odata4/northwind/NW/customers?$filter=name eq 'bob'&$format=xml

OData allows for querying navigations from one entity to another. A navigation is similar to the foreign key relationships in relational databases.

For example, if the customers table has an exported key to the orders table on the customers primary key called the customer_fk, then an OData GET could be issued like:

http://localhost:8080/odata4/northwind/NW/customers(1234)/customer_fk?$filter=orderdate gt datetime'2012-12-31T21:23:38Z'

this would be akin to making a JDBC/ODBC connection and issuing the SQL:

SELECT o.* FROM NW.orders o join NW.customers c on o.customer_id = c.id where c.id=1234 and o.orderdate > {ts '2012-12-31 21:23:38'}
Note
 More Comprehensive Documentation about ODATA - For detailed protocol access you can read the specification at http://odata.org. You can also read this very useful web resource for an example of accessing an OData server.

How to execute a stored procedure?

Odata allows you to call your exposed stored procedure methods via odata.

http://localhost:8080/odata4/northwind/NW/getcustomersearch(id=120,firstname='micheal')

Not seeing all the rows?

See the configuration section below for more details. Generally batching is being utilized, which tooling should understand automatically, and additional queries with a $skiptoken query option specified are needed:

http://localhost:8080/odata4/northwind/NW/customers?$skiptoken=xxx

"EntitySet Not Found" error?

When you issue the above query are you seeing a message similar to below?

{"error":{"code":null,"message":"Cannot find EntitySet, Singleton, ActionImport or FunctionImport with name 'xxx'."}}

Then, it means that either you supplied the model-name/table-name combination wrong, check the spelling and case.

It is possible that the entity is not part of the metadata, such as when a table does not have any PRIMARY KEY or UNIQUE KEY(s).

How to update your data?

Using the OData protocol it is possible to perform CREATE/UPDATE/DELETE operations along with READ operations shown above. These operations use different HTTP methods.

INSERT/CREATE is accomplished through an HTTP method "POST".

Example POST
POST /service.svc/Customers HTTP/1.1
Host: host
Content-Type: application/json
Accept: application/json
{
  "CustomerID": "AS123X",
  "CompanyName": "Contoso Widgets",
  "Address" : {
     "Street": "58 Contoso St",
     "City": "Seattle"
  }
}

An UPDATE is performed with an HTTP "PUT".

Example PUT Update of Customer
PUT /service.svc/Customers('ALFKI') HTTP/1.1
Host: host
Content-Type: application/josn
Accept: application/json
{
  "CustomerID": "AS123X",
  "CompanyName": "Updated Company Name",
  "Address" : {
     "Street": "Updated Street"
  }
}

The DELETE operation uses the HTTP "DELETE" method.

Example Delete
DELETE /service.svc/Customers('ALFKI') HTTP/1.1
Host: host
Content-Type: application/json
Accept: application/json

Security

By default OData access is secured using HTTPBasic authentication. The user will be authenticated against Teiid’s default security domain "teiid-security".

However, if you wish to change the security domain use a deployment-overlay to override the web.xml file in the odata4 file in the <modules>/org/jboss/teiid/main/deployments directory.

OData WAR is also compatible with Kerberos, SAML and OAuth2 authentications. For information about configuring these security schemes, see Security Guide

Configuration

The OData WAR file can be configured with following properties in the web.xml file.

Property Name Description Default Value

batch-size

Number of rows to send back each time, -1 returns all rows

256

skiptoken-cache-time

Time interval between the results being recycled/expired between $skiptoken requests

300000

invalid-xml10-character-replacement

XML 1.0 replacement character for non UTF-8 characters.

local-transport-name

Teiid Local transport name for connection

odata

invalid-xml10-character-replacement

Replacement string if an invalid XML 1.0 character appears in the data - note that this replacement will occur even if JSON is requested. No value (the default) means that an exception will be thrown with XML results if such a character is encountered.

proxy-base-uri

Defines the proxy server’s URI to be used in OData responses. Only needs to be set for proxies that do not support the Forwarded nor the X-Forwarded headers.

n/a

connection.XXX

Sets XXX as an execution property on the local connection. Can be used for example to enable result set cache mode.

n/a

explicit-vdb-version

When explicit-vdb-version is true, an explicit vdb version needs to be part of the url to use anything other than the default version 1 vdb. When explicit-vdb-version is false, the odata vdb version will be determined just like a JDBC connection.

true
Note
 "Behind Proxy or In Cloud Environments?" - If the Teiid server is configured behind a proxy server or deployed in cloud environment, or using a load-balancer that does not support the Forwarded nor X-Forwarded headers, then the URI of the server which is handling the OData request is different from URI of proxy. To generate valid links in the OData responses configure "proxy-base-uri" property in the web.xml. If this value is available as system property then define the property value like below 
    <init-param>
        <param-name>proxy-base-uri</param-name>
        <param-value>${system-property-name}</param-value>
    </init-param>

To modify the web.xml, create a deployment-overlay using the cli with the modified contents:

deployment-overlay add --name=myOverlay --content=/WEB-INF/web.xml=/modified/web.xml --deployments=teiid-odata-odata4.war --redeploy-affected

Teiid OData server implements cursoring logic when the result rows exceed the configured batch size. On every request, only batch-size number of rows are returned. Each such request is considered an active cursor, with a specified amount of idle time specified by skip-token-cache-time. After the cursor is timed out, the cursor will be closed and remaining results will be cleaned up, and will no longer be available for further queries. Since there is no session based tracking of these cursors, if the request for skiptoken comes after the expired time, the original query will be executed again and tries to reposition the cursor to relative absolute position, however the results are not guaranteed to be same as the underlying sources may have been updated with new information meanwhile.

Limitations

The OData4 interface is subject to some feature limitations. You cannot use the following features.

  • Search.

  • Delta processing.

  • Data-aggregation extension of the OData specification.

  • $it usage is limited to only primitive collection properties.

Client Tools for Access

OData access is really where the user comes in, depending upon your programming model and needs there are various ways you write your access layer into OData. The following are some suggestions:

  • Your Browser: The OData Explorer is an online tool for browsing an OData data service.

  • Olingo: Is a Java framework that supports OData V4, has both consumer and producer framework.

  • Microsoft has various .Net based libraries, see http://odata.github.io/

  • Windows Desktop: LINQPad is a wonderful tool for building OData queries interactively. See https://www.linqpad.net/

  • Shell Scripts: use CURL tool

For latest information other frameworks and tools available please see http://www.odata.org/ecosystem/

OData Metadata (How Teiid interprets the relational schema into OData’s $metadata)

OData defines its schema using Conceptual Schema Definition Language (CSDL). A VDB in an ACTIVE state in Teiid exposes its visible metadata in CSDL format. For example if you want retrieve metadata for your vdb, you need to issue a request like:

http://localhost:8080/odata4/northwind/NW/$metadata

Since OData schema model is not a relational schema model, Teiid uses the following semantics to map its relational schema model to OData schema model.

Relational Entity Mapped OData Entity

Model Name

Schema Namespace, EntityContainer Name

Table/View

EntityType, EntitySet

Table Columns

EntityType’s Properties

Primary Key

EntityType’s Key Properties

Foreign Key

Navigation Property on EntityType

Procedure/Function

FunctionImport, ActionImport

Procedure’s Table Return

ComplexType

Teiid by design does not define any "embedded" ComplexType in the EntityType.

Since OData access is more key based, it is MANDATORY that every table Teiid exposes through OData must have a PK or at least one UNIQUE key. A table which does not either of these will be dropped out of the $metadata.

Since all data roles are not consulted in the construction of the OData metadata there are times when tables or procedures will need to be specifically hidden. This can be done in the vdb via a "teiid_odata:visible" extension metadata property on the object.

create foreign table HIDDEN (id long primary key, ...) OPTIONS ("teiid_odata:visible" false);

With teiid_odata:visible set to false the OData layer will not expose the given object.

Datatype Mapping

Teiid Type

OData Type

STRING

Edm.String

BOOLEAN

Edm.Boolean

BYTE

Edm.SByte

SHORT

Edm.Int16

INTEGER

Edm.Int32

LONG

Edm.Int64

FLOAT

Edm.Single

DOUBLE

Edm.Double

BIG_INTEGER

Edm.Decimal

BIG_DECIMAL

Edm.Decimal

DATE

Edm.Date

TIME

Edm.TimeOfDay

TIMESTAMP

Edm.DateTimeOffset

BLOB

Edm.Stream

CLOB

Edm.Stream

XML

Edm.Stream

VARBINARY

Edm.Binary

Geography and Geometry will be mapped to the corresponding Edm.GeometryXXX and Edm.GeographyXXX types based upon the associated {http://www.teiid.org/translator/spatial/2015}type property. A general mapping to Edm.Geometry or EdmGeography will fail to serialize the values correctly.

Where possible, array types will be mapped to a collection type. However you cannot include multidimensional arrays. Also array/collection values cannot be used as parameters nor in comparisons.

Functions And Actions

The mapping of entities and their properties is relatively straight-forward. The mapping of Teiid procedures/functions to OData Functions and Actions is more involved. Virtual procedures, source procedure, and virtual functions defined by DDL (not a Java class) are all eligible to be mapped. Source functions or virtual functions defined by a Java class are currently not mapped to corresponding OData constructs - please create a virtual procedure that invokes the desired function if calling through odata is needed. OData does not have an out parameter concept, thus OUT parameters are ignored, and INOUT parameters are treated only as IN. VARIADIC support is not yet enabled. If there is a VARIADIC parameter it will be represented by single parameter. A result set is mapped to a complex type collection result. An array result will be mapped to a simple type collection.

An OData Function will be used if:

  • The procedure/function has a return value - either scalar or a result set.

  • The procedure/function has no LOB input parameters - currently Clob, Blob, XML, Geometry, Geography, and JSON are considered LOB types.

  • The procedure/function is side effect free - this is determined by an explicit value of 0 for the update count. For example: CREATE VIRTUAL PROCEDURE …​ OPTIONS (UPDATECOUNT 0) AS BEGIN …​

If any one of those conditions are not met the procedure/function is represented instead by an OData Action. However if there is a result set that has a LOB value, then the procedure is not mapped at all as multiple streaming values cannot be returned.

Note that OData Functions and Actions are called differently. A Function is called by a GET request where the parameter values are included in the URI. An Action is called by a POST where the content provides the parameter values.

Currently only unbounded Functions and Actions are compatible.

You should always consult the $metadata about Functions and Actions to validate how the procedures/functions were mapped.

OpenAPI Metadata

An experimental feature is available to automatically provide a Swagger 2.0 / OpenAPI metadata via [swagger|openapi].json rather than $metadata.

Example OpenAPI 2.0 URLs
http://localhost:8080/odata4/northwind/NW/swagger.json
http://localhost:8080/odata4/northwind/NW/openapi.json
http://localhost:8080/odata4/northwind/NW/openapi.json?version=2
Example OpenAPI 3.0 URL
http://localhost:8080/odata4/northwind/NW/openapi.json?version=3
Warning
 Due to all of the possible query options and expansions this metadata will be significantly larger than the OData EDM representation.

results matching ""

    No results matching ""