Integrating multiple data sources to OData API with Spring Boot and Teiid

 

The example showing how to integrate data from H2 database and CSV file and then exposed it as OData API with Spring Boot and Teiid. 

Teiid is a data virtualization tool created by Red Hat for data integration purpose and it has the capability of expose the integrated data as OData API. Besides H2 database and CSV file, Teiid support wide range of data sources such as Oracle DB, MS SQL, MySQL, MongoDB, REST API, Excel file, JSON, Google Spreadsheet, OData API, etc. you can find out more from the official samples. 

The project demostrated how to use Teiid Spring Boot runtime on joining product_symbols table of H2 database and product_data CSV file with productId column and then created a product view. You can see from the diagram above that name and type column of product view is come from product_data CSV file and the rest come from product_symbols table.

The product view then exposed as OData API that can be accessed by clients such as Excel, PowerBI, Java application, .Net application, etc.

You can find out more by take a look on the source code and steps to execute the example locally in your own machine at https://github.com/limcheekin/teiid-spring-boot-demo. 

Happy new year 2021! I'd love to hear your comments :).

Standard query language for your Web API, why GraphQL and OData?

GraphQL

GraphQL created by Facebook in 2012 and released publicly in 2015. I get the following definition of GraphQL from Wikipedia:

GraphQL is an open-source data query and manipulation language for APIs, and a runtime for fulfilling queries with existing data.

In my opinion, the killer feature of GraphQL is in the data query side instead of data manipulation, as REST API have good enough capability of data manipulation. The following is the key reasons I adopted GraphQL in my Web API:

• Client applications of limited network bandwidth and latency is critical such as mobile application get to choose what fields of object graph (a hierarchical structure of related objects) need to be included in the response.
• Client applications can inspect the schema of GraphQL API via its introspection capability. Development tools of strongly-type programming language such as Java, Dart, Go, etc. can generate client code via GraphQL introspection, it will greatly improve the productivity of developers.

Updated on Dec 17: You can find out more from a GraphQL API implemented by me in the following blog post: 

http://limcheekin.blogspot.com/2020/12/implementing-graphql-api-with-micronaut.html

OData

OData created by Microsoft in 2007. I get the following definition of OData from Wikipedia:

Open Data Protocol (OData) is an open protocol that allows the creation and consumption of queryable and interoperable REST APIs in a simple and standard way.

In a blog post published by Progress, it has better definition of OData:

OData advocates a standard way of implementing REST APIs that allows for SQL-like querying capabilities using these RESTful APIs. OData is essentially SQL for the web built on top of standard protocols – HTTP, JSON & ATOM XML – while leveraging the REST architecture style.

In my opinion, benefits of OData is similar to GraphQL with the following differences:

• OData supports schema discovery by query the metadata directly with $metadata URL without introspection.
• OData supports ATOM XML and JSON response.
• OData has been adopted by a lot of technologies and companies including SAP, IBM, Salesforce, Tableau, Databoom, Progress, Red Hat and Dell especially in spreadsheet, analytics, reporting, dashboard and business intelligence applications.

Updated on Dec 30: You can find out more from a OData API implemented by me in the following blog post: 

http://limcheekin.blogspot.com/2020/12/integrating-multiple-data-sources-to.html

What do you think? I'd love to hear from you. :)