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.
- First of all, we will require the ZappySys ODBC PowerPack.
- Secondly, we will use Pentaho Kettle (Our example in this post uses v8.1)
- Finally, we will require the JDBC driver for SQL Server.
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.
- First of all, open the ODBC Data Source administrator in Windows in the System DSN press Add:
- Secondly, select the ZappySys JSON Driver:
- Finally, add a data source name and specify a link to a URL. In this example, we will connect to the following 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:
- First of all, open the ZappySys Gateway that is installed with the ODBC PowerPack and add a new data source:
- Secondly, add the data source name:
- In addition, in the user’s tab, add the user test and specify a password:
- Also, in settings, press edit and add the ODBC Data Source created in the previous section and add the user created before:
- 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:
How to read REST API in Pentaho
- First of all, in Pentaho, we will create a project like the following:
- Secondly, we will create a query like the following:
- 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:
- If everything is OK, you will be able to see the CSV file generated:
- Also, you can use SQL statements. The following example shows how to do a where filter:
Transact-SQL123SELECT ShipnameFROM valuewhere Shipname='Consolidated Holdings'
- Finally, we will show the first 10 rows using a group by:
Transact-SQL1234SELECT ShipnameFROM valuegroup by shipnamelimit 10
How to read REST API in Pentaho (Gmail)
The following example will show how to get Gmail information from Pentaho.
- 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.
- Secondly, in the ZappySys ODBC driver, specify the URL and select the OAuth connection:
- 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:
- In addition, using the ZappysSys Data Gateway, add the new Data Source and associate with the ODBC connection just created:
- Additionally, add the test user to the data source:
- In Pentaho, change the connection in the table input to connect to the Gateway:
- Finally, in the table input add the following query:
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: