Consume Web Service in Data Virtuality using SQL
Data virtualization plarforms enable data architects and SQL professionals to combine and merge data from different data sources like relational databases, NoSQL databases and even from web services and Web API data resources. In this Data Virtuality tutorial, I want to share a sample case showing how to consume Web API using Data Virtuality web service connector.
For Data Virtuality web service connector use case, I want to show how to consume programming quotes API from herokuapp.com
If data virtualization developers visit the home page of the reference web application, you can find the API documentation.
After Data Virtualization administrator launches Data Virtuality Studio, create a new data source using Web service connector.
As seen below, I created a new web service data source named "ws_quotes" using the URL "https://programming-quotes-api.herokuapp.com/quotes" as the end point for the Web API.
Since the web service is available for anonymous consume, the security type is None.
On Data Virtuality Studio, if SQL programmer checks the objects default introduced with this data source, only invoke and invokeHttp procedures are seen.
As seen in following screenshot, data developers can use invoke procedure for consuming a web service that returns XML result.
On the other hand, invokeHttp procedure is used to consume web services that return binary result.
invokeHttp procedure returns XML data using the result parameter indicating the ReturnValue
Below SQL code invokes the web service end point for a random quote in English which return data in JSON.
Because of this reason, contentType is set as application/json
SELECT
to_chars(result, 'UTF-8') as result
FROM (
CALL "ws_quotes.invokeHttp" (
action => 'GET',
contentType => 'application/json',
endpoint => 'random/lang/en',
requestContentType => 'application/json',
failOnHttpError => false
)
) as q;;
When we call the invokeHttp procedure of the ws_quotes schema (or data source) using CALL command, the returned result should be converted into readable format using the to_chars() function.
The result column shows the JSON response of the Web API call.
Using JSONTOXML and XMLTABLE SQL functions, Data Virtuality developers can convert JSON response of the Web API into a tabular structure.
Here is the sample SQL code.
SELECT
en,
author,
source
FROM (
SELECT
to_chars(result, 'UTF-8') as result
FROM (
CALL "ws_quotes.invokeHttp" (
action => 'GET',
contentType => 'application/json',
endpoint => 'random/lang/en',
requestContentType => 'application/json',
failOnHttpError => false
)
) as q
) q,
XMLTABLE (
'/root/.'
PASSING
JSONTOXML('root', q.result)
COLUMNS
en string,
author string,
source string
) x;
As seen in following Data Virtuality Studio screenshot, the en, author and source columns contain the random quote and related information about it.
I hope this Data Virtuality tutorial with sample SQL code is useful for data architects and data engineers working on this data virtualization tool.