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 :).

Implementing GraphQL API with Micronaut and AWS Lambda

First of all, the GraphQL API created using the following technology stack:

As per my own convention, I would like to explore new technology by implementing address book application with the following database schema:

The application is currently running live in AWS lambda with MySQL database. You can test it out by sending HTTP POST request with the following GraphQL query in JSON to https://39nx6hi1ac.execute-api.ap-southeast-1.amazonaws.com/Prod/graphql:

{ "query":"{ findById(id: 27) { lastName firstName gender age } }" }

Please make sure the JSON above is send in one line, I formatted it here for ease of read. If you are not sure what tool to use, I'm using Postman.

You will get the following JSON response:

{ "data": { "findById": { "lastName": "Lim", "firstName": "Chee Kin", "gender": "MALE", "age": 40, } } }

Next, you can further extend the query to include fields of master-detail data:

{ "query":"{ findById(id: 27) { lastName firstName gender age contacts {id type value} } }" }

You will get the following JSON response:

{ "data": { "findById": { "lastName": "Lim", "firstName": "Chee Kin", "gender": "MALE", "age": 40, "contacts": [ { "id": 28, "type": "MOBILE_NUMBER", "value": "+60123456789" }, { "id": 29, "type": "WORK_NUMBER", "value": "+604567890" }, { "id": 30, "type": "EMAIL_ADDRESS", "value": "test@test.com" } ] } } }

The source code of the application is hosted in the Github repository at https://github.com/limcheekin/micronaut-person/tree/graal-data-jpa-2.0.x. 

It is continuous build to native app using GraalVM in Docker container and deploy by Github Actions to AWS Lambda custom runtime.

The blog post only scratch the surface of the GraphQL API, kindly let me know if you are interested to find out more on how the API is created, how the continuous deployment with Github Actions works, how to create the AWS API Gateway and AWS Lambda custom runtime, etc. I will write a dedicated blog post for each topic.

I'd love to hear from you! :)

Updated on Dec 23: If GraalVM is new to you and you're curious on the reasons I go for GraalVM, please check out the well-written article at DZone. 

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. :)