OData: Is it the universal query language for RDBMS, RDF, and other datastores?

Posted on by peterlawrence

OData not only offers a RESTful interface to any datastore to perform CRUD operations, it also provides a very powerful query interface to the underlying datastore making it a candidate for the ‘universal’ query language. However the query capability is often neglected. Why?

Simple OData Query Example

It is always easier to start simply. So the question I want answered from a Northwind datastore is

Find customers located in France

Given an OData endpoint, the query is simply answered with the following URL:

http://localhost:8080/odata2sparql.v4/NW/

Customer?

$filter=contains(customerCountry,’France’)

The elements of this URL are as follows:

  1. The first line identifies the OData endpoint, in this case it is a local OData2SPARQL (http://inova8.com/bg_inova8.com/offerings/odata2sparql/) endpoint that is publishing a RDF/SPARQL triplestore hosting an RDF version of Northwind (https://github.com/peterjohnlawrence/com.inova8.northwind).

Alternatively you could use a publicly hosted endpoint such as http://services.odata.org/V4/Northwind/Northwind.svc

  1. The next line specifies the entity or entityset, in this case the Customer entitySet that is the start of the query
  2. The final line specifies a filter condition to be applied to the property values of each entity in the entitySet

The partial results are shown below in JSON format, although OData supports other formats

But that is not much more than any custom RESTful API would provide, right? Actually OData querying is far more powerful as illustrated in the next section.

Complex OData Query Example

The question I now want answered from a Northwind datastore is

Get product unit prices for any product that is part of an order placed since 1996 made by customers located in France

One thing is that the terminology used by OData is different than that of RDBMS/SQL, RDF/RDFS/OWL/SPARQL, Graph, POJO, etc. The following table shows the corresponding terms that will be used in this description

OData Terminology Mapping to Relational, RDF, and Graph
Mapped OData Entity Relational Entity RDF/RDFS/OWL Graph
Schema Namespace, EntityContainer Name Model Name owl:Ontology Graph
EntityType, EntitySet Table/View rdfs:Class

owl:Class

 Node
EntityType’s Properties Table Column owl:DatatypeProperty Attribute
EntityType’s Key Properties Primary Key URI Node ID
Navigation Property on EntityType, Association, AssosiationSet Foreign Key owl:ObjectProperty Edge
FunctionImport Procedure spin:Query  

Given an OData endpoint, the query is answered with the following URL:

http://localhost:8080/odata2sparql.v4/NW/

Customer?

$filter=contains(customerCountry,’France’)&

$select=customerCompanyName,customerAddress,customerContactName&

$expand=hasPlacedOrder(

$filter=orderDate gt 1996-07-05;

$select=shipAddress,orderDate;

$expand=hasOrderDetail(

$select=quantity;

$expand=product(

$select=productUnitPrice

)

)

)

The elements of this URL are much the same as before, but now we nest some queryOptions as we navigate from one related entity to another, just as we would when navigating through a graph or object structure.

  1. Customer entitySet is specified as the start of the query
  2. The $filter specifies the same filter condition as before since we want only customers with an address in France.
  3. Next we include a $select to limit the properties (aka RDBMS column, or OWL DatatypeProperty) of a Customer entity that are returned.
  4. Then the $expand is a query option to specify that we want to move along a navigationProperty (aka RDBMS foreign key, Graph edge or OWL ObjectProperty). In this case the navigationProperty takes us to all orders placed by that customer.

Now we are at a new Order entity we can add queryOptions to this entity

  1. The $filter specifies that only orders after a certain date should be included.
  2. The $select limits the properties of an Order entity that are returned.
  3. The $expand further navigates the query to the order details (aka line items) of this order.

Yet again we are at another entity so we can add queryOptions to this Order_Detail entity:

  1. The $select limits to only the quantity ordered
  2. The $expand yet again navigates the query to the product entity referred to in the Order_Detail

Finally we can add queryOptions to this product entity:

  1. The $select limits only the productUnitPrice to be returned.

The partial results are shown below:

You can test the same query at the public Northwind endpoint, the only difference being the modified names of entitySets, properties, and navigationProperties.

http://services.odata.org

/V4/Northwind/Northwind.svc/Customers?

$filter=contains(Country, ‘France’)&

$select=CompanyName,Address,ContactName&

$expand=Orders(

$filter=OrderDate gt 1996-07-05;

$select=ShipAddress,OrderDate;

$expand=Order_Details(

$select=Quantity;

$expand=Product(

$select=UnitPrice

)

)

)

Structure of an OData Query

The approximate BNF of an OData query is shown below. This is only illustrative, not pure BNF. The accurate version is available here (http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part2-url-conventions.html)

ODataRequest              := entity|entitySet?queryOptions

entity                            a single entity

                                    := Customer(‘NWD~Customer-GREAL’)

                                    := Customer(‘NWD~Customer-GREAL’)/hasPlacedOrder(‘NWD~Order-10528’)

entitySet                       a set of entities

                                    := Customer

                                    := Customer(‘NWD~Customer-GREAL’)/hasPlacedOrder

queryOptions               The optional conditions placed on the current entity

                                    := filter ; select ; expand

filter                             Specify a filter to limit what entities should be included

                                    := $filter = {filterCondition}*

                                    := $filter = contains(customerCountry,’France’)

select                            Specify what properties of the current entity should be returned

                                    := $select = {property}*

                                    := $select = customerCompanyName,customerAddress,customerContactName

expand                        Specify the navigationProperty along which the query should proceed

                                    := $expand = {navigationProperty(queryOptions)}*

.                                   := $expand = hasPlacedOrder($expand=..)

Perhaps this can be illustrated more clearly (for some!):

Using the same diagram notation the complex query can be seen as traversing nodes in a graph:

Other Notes

  1. OData publishes the underlying model via a $metamodel document, so any application can be truly model-driven rather than hard-coded. This means that OData query builders allow one to access any OData endpoint and start constructing complex queries. Examples include:
  2. The examples in this description use OData V4 syntax ( http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part2-url-conventions.html ). The same queries can be expressed in the older OData V2 syntax (http://www.odata.org/documentation/odata-version-2-0/uri-conventions/), but V4 has changed the syntax to be far more query friendly.
  3. What about really, really complex queries: do I have to define them always via the URL? Of course not because each underlying datastore has a way of composing ‘views’ of the data that can then be published as pseudo-entityTypes via OData:
    • RDBMS/SQL has user defined views
    • RDF/RDFS/OWL/SPARQL has SPIN queries (http://spinrdf.org/) that can be added as pseudo EntityTypes via OData2SPARQL
  4. By providing a standardized RESTful interface that also is capable of complex queries we can isolate application development and developers from the underlying datastore:
    • The development could start with a POJO database, migrate through an RDBMS, and then., realizing the supremacy of RDF, end up with a triplestore without having to change any of the application code. OData can be a true Janus-point in the development

Conclusions

OData is much more than JDBC/ODBC for the 21st century: it is also a candidate for the universal query language that allows development and developers to be completely isolated from the underlying datastore technology..

Additional Resources

Odata2SPARQL (http://inova8.com/bg_inova8.com/offerings/odata2sparql/)

Lens2Odata (http://inova8.com/bg_inova8.com/offerings/lens2odata/)

Leave a Reply