Overview

In the upcoming 8.6 release of Designer, we have introduced features for consuming JSON REST web services and exposing them as relational sources. This article will describe how to accomplish this and provide resources to allow you to try it out yourself. The demo uses a REST war generated from Teiid Designer using a Teiiid VDB as a source. The attached model project set has all the required artifacts. For those that don't want to play with a unreleased version of Teiid Designer, the required transformations will be shown here to give you a template for consuming JSON REST web services in the current tooling (8.5.x at the time of this post).

Prerequisites

1. An installation of JBDS 7.1.1 with the latest version of Teiid Designer. The nightly build is located here: http://download.jboss.org/jbosstools/builds/staging/jbosstools-teiid-designer_trunk/all/repo/

2. Download and import the attach Teiid Model Project Set (MPS).

3. Create the MySQL database instance using the DDL included in the MPS.

4. Start up your EAP or JBoss AS instance with your target Teiid server in JBDS.

5. Deploy the BooksRest VDB to Teiid. This will create all required data sources for you. You will be prompted to create a datasource for the BooksRest VDB. Accept the default JNDI name as this is what you will use for the JNDI name when generating the REST war. Be sure you have MySQL configured in your AS. There are many blogs and articles that explain how to do this. Here is a nicely done YouTube video that you can use as well: https://www.youtube.com/watch?v=G-oj9XagiPo

6. Generate a REST war in Teiid Designer for the BookRest VDB. If you are not familiar with how to do this, see section 7.4.2.2 of this doc: Chapter 7. Metadata-specific Modeling

7. Deploy the generated war and verify this URL returns a valid ISBN and Title: http://localhost:8080/BooksRest/rest/json/book/0-7356-0877-6

Creating the REST Connection Profile

We need to define a REST connection profile that will allow us to connect to the source JSON REST web service. First, create a REST connection profile via the "Web Service Source >> Source and View Models (REST)" option.

Clicking Next will open the REST Import wizard.

Once you are in the REST Import wizard, click the New button to create the connection profile. Give your connection profile a name such as "Books JSON REST" and click Next.

After clicking Next I am ready to define my service properties. The web service I am consuming in this example is a service to return information about books (ISBN and Title) based on some ISBN number passed into the service via URI. For example, http://localhost:8080/BooksRest/rest/json/book/0-7356-0877-6, will return:

The base URL for my target service is http://localhost:8080/BooksRest/rest/json/book

(without the URI parameter) and that is what I will enter for the Connection URL field. I will also set the Response to JSON, since my service returns JSON instead of XML.

This service is unsecured, so I will leave the Security Type set to None. I am now ready to define my parameters. The parameter for this service is ISBN, so I will click the add button below the Parameter tab and define the parameter as isbn with a type of URI, since the value will be passed via URI rather than as a query parameter. I will also add a default value which will be used to execute the service and bring back a response document that will be used to determine the schema for generating the transformation in the wizard.

Turning our attention to the Optional Request Header Properties tab, we need to override some default values for web service execution in Teiid. Add the following header property for the targeted books service:

After adding the parameter and header property, click the Test Connection button test your connection profile to see if you can successfully ping the service and then click Next.

Importing the Service

On the next page, you will define your virtual procedure name and your source and view models used for generation of the required procedures.

Click Next and you will be presented with a schema representation of your expected JSON result. Expand the books and book elements to add the isbn and title columns. Notice that the transformation in the preview pain at the bottom of the wizard updates as you add and remove columns. Click the Finish button to generate your models and procedures required for consuming the JSON REST web service source.

The generate transformation for the getBookViaISBN procedure is as follows:

BEGIN
  DECLARE STRING VARIABLES.qp = (('http://localhost:8080/BooksRest/rest/json/book' || '/') || MyViewModel.getBookViaISBN.isbn);
  SELECT A.ISBN AS ISBN, A.TITLE AS TITLE FROM (EXEC MySourceModel.invokeHttp('GET', null, VARIABLES.qp, 'TRUE')) AS f, XMLTABLE('/response/books' PASSING JSONTOXML('response', f.result) COLUMNS ISBN string PATH 'book/ISBN/text()', TITLE string PATH 'book/TITLE/text()') AS A;
END

Create a VDB, add the MySourceModel (define the translator type as WS) and MyViewModel, and deploy. Execute the following against your new VDB to test it out:

select * from ( exec "MyViewModel"."getBookViaISBN"('0-7356-0877-6') ) AS X_X

You should see the following result:

Conclusion

Those are the steps for consuming a JSON REST web service in Teiid Designer 8.6. You can use the generated transformation in this article as a guideline for consuming JSON service in versions prior to 8.6 OR take the latest for a drive.

Thanks for reading! Enjoy!