Working with REST Client and JSON In Pentaho

Today we will be looking into REST API Component In Pentaho.

So data can be from many source, one source of data can be the internet. As PENTAHO is a ETL tool, it provides us with components to help fetch our data with ease.

Make data easy with Helical Insight.
Helical Insight is world's best open source business intelligence tool.

Click Here to Free Download

Lets say we have REST API url: http://api.openweathermap.org/data/2.5/weather with parameters such as

zip: 68507,us

Appid: 474c8199809c75d65cb23314b57266e7

Now let’s open up our Pentaho Data Integration, a Transformation and then select the REST Client Component

We can input the URL: http://api.openweathermap.org/data/2.5/weather but we also need to input the parameters and the best way to do that is to build URL, hence we can use JavaScript Component to do that.

Make data easy with Helical Insight.
Helical Insight is world's best open source business intelligence tool.

Click Here to Free Download

I would be adding the Data Grid Component first:

And then a Modified JavaScript Component.

The Data Grid will hold our ZIP and APPID parameter values and also URL if required.

Then we build the URL using the Modified JAVA Script Component

Now Next we Open up our REST CLIENT component and we

https://www.helicaltech.com/wp-content/uploads/2020/04/json-pentaho8.PNG

Check the ACCEPT URL checkbox and then Select the field which is the variable we just created in our JavaScript Component in our URL Field Name Drop down. Our Method is a GET and type is J SON so we are good.

Other Tabs are useful based on your scenarios, once we are done our Call is ready.

Now our Data is available in result so now we can connect our REST Client to JSON Input

So the source here is from a field which in our case is result, now we select that option.

Now the fun part is defining the fields

So lets look our data from result:

Make data easy with Helical Insight.
Helical Insight is world's best open source business intelligence tool.

Click Here to Free Download

[
  {
    "coord": {
      "lon": -96.63,
      "lat": 40.85
    },
    "weather": [
      {
        "id": 800,
        "main": "Clear",
        "description": "clear sky",
        "icon": "01d"
      }
    ],
    "base": "stations",
    "main": {
      "temp": 288.28,
      "feels_like": 287.45,
      "temp_min": 287.59,
      "temp_max": 288.71,
      "pressure": 1008,
      "humidity": 82
    },
    "visibility": 16093,
    "wind": {
      "speed": 2.1,
      "deg": 180
    },
    "clouds": {
      "all": 1
    },
    "dt": 1588077555,
    "sys": {
      "type": 1,
      "id": 4736,
      "country": "US",
      "sunrise": 1588073274,
      "sunset": 1588123182
    },
    "timezone": -18000,
    "id": 0,
    "name": "Lincoln",
    "cod": 200
  }
]

I want the Max Temperature and Min Temperature

Now we have to define the Path,So temp_max is available with the “MAIN” Key so our path would be: $.main.temp_max similarly for the minimum.

Lets have a preview:

Now we have our 2 columns, next we pass it to any type of Output(DB,Flat Files and so on).

Thanks
sohail Izebhijie
Helical IT Solutions Pvt Ltd

Best Open Source Business Intelligence Software Helical Insight Here

A Business Intelligence Framework

Free
Download

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download