How to read REST API in Pentaho Kettle

Introduction

In this article, we will learn how to read REST API in Pentaho Kettle. Pentaho is a very popular tool in Business Intelligence to integrate data for OLAP services and to create reports and dashboards. In this article, we will use our ZappySys ODBC PowerPack to connect to REST API in Pentaho Kettle.

Our software requires the JDBC connector for SQL Server and then Uses our ZappySys Gateway to connect.

Requirements

1. First of all, we will require the ZappySys ODBC PowerPack.
2. Secondly, we will use Pentaho Kettle (Our example in this post uses v8.1)
3. Finally, we will require the JDBC driver for SQL Server.

Getting started

First of all, we will need to create an ODBC connection to REST API using the Windows ODBC Data Source Administrator. Next, we will create a gateway and finally, we will invoke the data in Pentaho.

Create an ODBC connection to read REST API in Pentaho Kettle

In this example, we will first create an ODBC connection to an URL.

1. First of all, open the ODBC Data Source administrator in Windows in the System DSN press Add:
   

   Add new ODBC driver

2. Secondly, select the ZappySys JSON Driver:
   

   Select JSON driver in ODBC DS Administrator

3. Finally, add a data source name and specify a link to a URL. In this example, we will connect to the following url:
   
   URL: https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json

   1	URL: https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json

   

   REST API ODBC URL

Create a ZappySys gateway to JDBC to read REST API in Pentaho

In order to connect to REST API, ZappySys ODBC PowerPack uses the JDBC for SQL Server and a gateway. Let’s take a look at it:

1. First of all, open the ZappySys Gateway that is installed with the ODBC PowerPack and add a new data source:
   

   Add new Data Source in ZappySys Gateway

2. Secondly, add the data source name:
   

   Pentaho REST Client

3. In addition, in the user’s tab, add the user test and specify a password:
   

   Add user and login

4. Also, in settings, press edit and add the ODBC Data Source created in the previous section and add the user created before:
   

   Link Data Source in ODBC with the default gateway

5. Finally, in the General tab, you can specify the port. By default, it is 5000.
   Make sure to save the changes and check the gateway is started with the start button:
   

   Default gateway with port 5000

How to read REST API in Pentaho

1. First of all, in Pentaho, we will create a project like the following:
   

   Pentaho Project REST API csv

2. Secondly, we will create a query like the following:
   

   A query in Pentaho to get data

3. In addition, we will add the following connection. We will connect to the data source named customers created in the ZappySys Gateway with port 5000 and the user created in the Gateway:
   

   JDBC connection to SQL Server

4. If everything is OK, you will be able to see the CSV file generated:
   

   Data in CSV format

5. Also, you can use SQL statements. The following example shows how to do a where filter:
   
   Transact-SQL

   SELECT Shipname FROM value where Shipname='Consolidated Holdings'

   1 2 3

   SELECT Shipname FROM value where Shipname='Consolidated Holdings'

6. Finally, we will show the first 10 rows using a group by:
   
   Transact-SQL

   SELECT Shipname FROM value group by shipname limit 10

   1 2 3 4

   SELECT Shipname FROM value group by shipname limit 10

How to read REST API in Pentaho (Gmail)

The following example will show how to get Gmail information from Pentaho.

1. First of all, we will need to create a connection to Gmail in ODBC using the ZappySys ODBC Driver (Check the Create an ODBC connection for a Pentaho REST Client section). In the ODBC Administrator, we will add a ZappySys JSON Driver connection.
2. Secondly, in the ZappySys ODBC driver, specify the URL and select the OAuth connection:
   

   ODBC configuration

3. Also, in the OAuth properties specify the Google OAuth provider and the https://mail.google.com scope. Press the generate token button and specify your credentials and accept the access permission request:
   

   Gmail OAuth connection

4. In addition, using the ZappysSys Data Gateway, add the new Data Source and associate with the ODBC connection just created:
   

   Connect Pentaho with Gmail

5. Additionally, add the test user to the data source:
   

   Include user and permission to the gateway

6. In Pentaho, change the connection in the table input to connect to the Gateway:
   

   Pentaho Rest client to Gmail

7. Finally, in the table input add the following query:
   

   Connect Pentaho to Gmail

Conclusions

To conclude, we can say that it is simple to connect to the REST API using Pentaho with the help of ZappySys ODBC PowerPack. This tool allows to easily query REST API data like a simple SQL Query. If you like this tool you can
download the ODBC PowerPack here.

References

For more information, refer to the following links:

• ZappySys ODBC PowerPack
• Introduction To Pentaho