Wednesday, January 22, 2014

Pentaho Data Integration : HTTP Client

HTTP Client provide ability to perform a call to a base URL with parameter values and return result value as a string. Sample transformation given below.

Free Yahoo finance API to download stock quotes used here for demo purpose.
Current stock prices with a 15 minute delay can be retrieved using this API.
Service return data in CSV format.

Base URL : http://finance.yahoo.com/d/quotes.csv



Parameters

s --> Stock symbols ( AAPL, GOOG, FB e.t.c )
f --> List of flags. Here are some sample values for f.

o : open
a : Ask
b : Bid
l1: Last Trade Price
h : Day’s High
g : Day’s Low
v : Volume
k : 52 Week High
j : 52 week Low

Sample output for AAPL : http://finance.yahoo.com/d/quotes.csv?s=AAPL&f=oabl1hgvkj

Sample Transformation

Stepwise illustration to download stock quotes using HTTP Client step given below.
Output formatted and inserted into MySQL Database table.

Step 1

Create a new transformation. Drag CSV file input step to the design canvas.


Step 2

Configured CSV input step to read input data.
STOCK_SYMBOL and STOCK_NAME are the input values here.


Preview of input test data

Step 3

"Add constants" step used to generate the Quote Type parameter.

Parameter value "oabl1hgvkj" used here for demo purpose.

Step 4

Configured HTTP Client step to call Yahoo Finance API.
The service will return comma separated values based on the parameters passed.

URL set to  http://finance.yahoo.com/d/quotes.csv


Configured Stock Symbol : "s" and Quote Type : "f" parameters.


Step 5

Split Fields step used to convert coma separated "result" field into multiple columns.
Column names are based on the quote type parameter.


Here are the list of columns for quote type "oabl1hgvkj".

Step 6

Populate Run date and time using Get System Info.

Step 7

MySQL database table used here to store output data.

Step 8

Sample output data after couple of test runs.

3 comments:

  1. Can you publish the ktr? I am having problems setting the parameters

    ReplyDelete
  2. Finally I made it work using the "Add constants" step

    ReplyDelete
  3. we have almost all country students as our subscribers for online course.We have 10+ years of experience we can serve various ascent people. oracle fusion Cloud HCM online training at erptree.com is worlds best online training center. we have excelent knowledge sharing Platform we have user friendly website where you
    will be provided with all the required details and Self-paced DEMO videos. we have our branches in pune, gurgaon, noida, india, usa, uk, uae, oracle fusion hcm training, fusion Procurement training, fusion hcm,
    scm training


    Oracle Fusion HCM Training

    ReplyDelete