How to read REST API in Pentaho Kettle


Pentaho logoIn 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.


  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:
    Include JSON driver in ODBC Data source

    Add new ODBC driver

  2. Secondly, select the ZappySys JSON Driver:
    Use ZappySys ODBC 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 for OData REST API


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:
    Gateway add Data Source

    Add new Data Source in ZappySys Gateway

  2. Secondly, add the data source name:
    Create gateway to REST API

    Pentaho REST Client

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

    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:
    ODBC connection to Default gateway

    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:
    Port 5000 in the default gateway

    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:
    Client Pentaho REST API

    Pentaho Project REST API csv

  2. Secondly, we will create a query like the following:
    SQL query to REST API in Pentaho

    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:
    How to connect to the Gateway

    JDBC connection to SQL Server

  4. If everything is OK, you will be able to see the CSV file generated:
    Exported data from REST API to CSV

    Data in CSV format

  5. Also, you can use SQL statements. The following example shows how to do a where filter:
  6. Finally, we will show the first 10 rows using a group by:

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:
    Configure ODBC to REST API

    ODBC configuration

  3. Also, in the OAuth properties specify the Google OAuth provider and the scope. Press the generate token button and specify your credentials and accept the access permission request:
    Configure OAuth connection

    Gmail OAuth connection

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

    Connect Pentaho with Gmail

  5. Additionally, add the test user to the data source:
    Gateway user to connect to REST API

    Include user and permission to the gateway

  6. In Pentaho, change the connection in the table input to connect to the Gateway:
    Use Pentaho Rest client to connect to Gamil

    Pentaho Rest client to Gmail

  7. Finally, in the table input add the following query:
    Query to get Gmail data and read REST API in Pentaho

    Connect Pentaho to Gmail


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.


For more information, refer to the following links:


Posted in ETL - Pentaho Kettle, JSON File / REST API Driver, ODBC Gateway, ODBC PowerPack and tagged , , , , .