Invoking RESTful WebServices - ODI11g

--

Web services can be accessed via HTTP and so they can be invoked\consumed using HTTP GET/POST methods.

With ODI there are different options like creating a customized Open tool or Knowledge Module to implement this. In this post, will be demonstrating how to invoke a RESTful webservice and write the response XML to a file using an ODI Procedure.

Will be using Yahoo's Place finder geocoding webservice as an example to pass street address to get additional information like latitude,longitude,county,etc.

Using the Yahoo's geocoding webservice -

To use geocoding webservice you need an Applicaion_Id (appid) key from yahoo, you can generate it from here(There is no charge to it).

The format to invoke the webserivce in this case is http://where.yahooapis.com/geocode?q=STATE,CITY,STATE&appid=[yourappidhere]

If you hit the URL below on any browser you should see the XML (that's one of the beauty of REST )
http://where.yahooapis.com/geocode?q=1600+Pennsylvania+Avenue,+Washington,+DC&appid=[yourappidhere]




ODI procedure to Invoke a single RESTful web service:

A simple ODI procedure which uses Jython to invoke and consume web service. This procedure has many input parameter.These parameters can be set either using Variables or OPTIONS.

Required Parameter -
1) URLFormat - URL of the webservice to be invoked.

In this case the value is

URLFormat="http://where.yahooapis.com/geocode?q='#STREET','#CITY','#STATE'&appid=myappid"

Note: STREET,CITY,STATE are the ODI variables.
         myappid is the application key provided by yahoo

2) OutputXMLFile="C:\\temp\\yahooREST.xml"

Optional Parameters -
There are other optional parameters which you can set if needed

PROXY_HOST - proxy host name
PROXY_PORT - proxy port number
and others

ODI Procedure Code -

import java.io.BufferedReader as BR
import java.io.InputStreamReader as ISR
import java.net.HttpURLConnection as uc
import java.net.URL as URL
import java.net.Proxy as Proxy
import java.net.InetSocketAddress as ISA
import base64
import string

#### Set the parameters below - Use Variables or Options

########### Required Parameters #######################

URLFormat="http://where.yahooapis.com/geocode?q='#STREET','#CITY','#STATE'&appid=myappid"

OutputXMLFile="C:\\temp\\yahooREST.xml"

######### Optional Parameters - set if required ##############

# Encrypt the procedure, if needed 

# if you are setting the Proxy_Host , set the Proxy_Port too
Proxy_Host=""
# Proxy Port  - If not using Proxy, default value should be 0
Proxy_Port=0

## Use Either Proxy_Username and Proxy_Password OR Proxy_UserPass_Encoded
Proxy_Username = ""
Proxy_Password = ""
Proxy_UserPass_Encoded=""

## Use Either Webservice_Username and Webservice_Password OR Webservice_UserPass_Encoded
Webservice_Username = ""
Webservice_Password = ""
Webservice_UserPass_Encoded=""


#####################################
####### Code starts here   ##########
#####################################

## Encode the username password for basic authentication

if Webservice_UserPass_Encoded == "":
   Webservice_Authorization = 'Basic ' + string.strip(base64.encodestring(Webservice_Username + ':'  +  Webservice_Password))
else:   
   Webservice_Authorization = 'Basic ' + Webservice_UserPass_Encoded
   
if Proxy_UserPass_Encoded == "":
   Proxy_Authorization = 'Basic ' + string.strip(base64.encodestring(Proxy_Username + ':' + Proxy_Password))
else:
   Proxy_Authorization = 'Basic ' + Proxy_UserPass_Encoded

inURL=URLFormat.replace(' ','%20')

if Proxy_Host == "": 
   url = URL(inURL)
   uc =  url.openConnection()
else:
   isa = ISA(Proxy_Host,Proxy_Port)
   proxy = Proxy(Proxy.Type.HTTP, isa)
   url = URL(inURL)
   uc =  url.openConnection(proxy)   
uc.setRequestMethod("GET")
uc.setRequestProperty("Authorization",Webservice_Authorization) 
uc.setRequestProperty("Proxy-Authorization",Proxy_Authorization)
uc.connect()
responseCode=uc.getResponseCode()
responseMessage=uc.getResponseMessage()

if responseCode ==  200 and responseMessage == "OK":
   br=BR(ISR(uc.getInputStream()))
   outRESTXML = ""                           
   outline = br.readLine()
   while (outline != None):  
          outRESTXML += outline + "\n" 
          outline = br.readLine() 
   XMLfilehandle= open(OutputXMLFile,'w')
   XMLfilehandle.write(outRESTXML)
   XMLfilehandle.close()
else:
   uc.disconnect()
   raise  'HTTP Error - Response Code:' + str(responseCode) + ' and Response Message: ' + responseMessage + '\n'

uc.disconnect()

To run it for every row , design a package to loop through all rows , populate variables and run the procedure and parse the XML using XML adapter.

Note: This code can also be used as a workaround for Jython URLLIB2 issue.


ODI procedure to Invoke a RESTful web service for all the rows in a query:

In some cases , the XML returned also contains information about the parameters that were used to invoke the webserivce. In such a case you can customize the code to invoke webservice and generate XML file for every row. This help save lot of work and time.

Note: This scenario is not suitable in the above case as the returned XML does not contain the street,city,state. Code can be changed to insert the XML into a table also.


Command on Source:

Command on Target:

The entire code:

import java.io.BufferedReader as BR
import java.io.InputStreamReader as ISR
import java.net.HttpURLConnection as uc
import java.net.URL as URL
import java.net.Proxy as Proxy
import java.net.InetSocketAddress as ISA
import java.text.SimpleDateFormat as SDF
import java.util.Date as date
import base64,string


## Set the parameters below - Use Variables or Options

######### Required Parameters ###############


# input URL format - replace the input parameters with # variables
URLFormat="""http://where.yahooapis.com/geocode?q='#STREET','#CITY','#STATE'&appid=myappid"""

# rownum as part of filename to generate distinct filenames
rownbr = '#ROWNBR'


# Output XML file Format - don't add the ".xml" extension
OutputXMLFileFormat="C:\\temp\\yahooOutput\\YahooREST"


######### Optional Parameters - set if required ##############

# Encrypt the procedure, if needed 

# if you are setting the Proxy_Host , set the Proxy_Port too
Proxy_Host=""
# Proxy Port  - If not using Proxy, default value should be 0
Proxy_Port=0

## Use Either Proxy_Username and Proxy_Password OR Proxy_UserPass_Encoded
Proxy_Username = ""
Proxy_Password = ""
Proxy_UserPass_Encoded=""

## Use Either Webservice_Username and Webservice_Password OR Webservice_UserPass_Encoded
Webservice_Username = ""
Webservice_Password = ""
Webservice_UserPass_Encoded=""

# the date/datetime format will be appended to the xml file (needs to be Java Compatible)
xmlFileNameDateAppend = "yyyy-MM-dd"


############################################
###  Main Starts here                  #####
############################################
         
def getRESTXML(inURLData):
# setup and call the webservice
    responseCode=0
    responseMessage=""
    errorMessage=''
    outRESTXML = ""                           
    if Proxy_Host == "":
       url = URL(inURLData)
       uc =  url.openConnection()
    else:
       isa = ISA(Proxy_Host,Proxy_Port)
       proxy = Proxy(Proxy.Type.HTTP, isa)
       url = URL(inURLData)
       uc =  url.openConnection(proxy)   
    uc.setRequestMethod("GET")
    uc.setRequestProperty("Authorization",Webservice_Authorization)
    uc.setRequestProperty("Proxy-Authorization",Proxy_Authorization)
    uc.connect()
    responseCode=uc.getResponseCode()
    responseMessage=uc.getResponseMessage()      
    if responseCode ==  200 and responseMessage == "OK":
       br=BR(ISR(uc.getInputStream()))
       outline = br.readLine() 
       while (outline != None):  
            outRESTXML += outline + "\n" 
            outline = br.readLine() 
       uc.disconnect()
    else:
       errorMessage = 'Error - Response Code:' + str(responseCode) + ' and Response Message: ' + responseMessage + '\n'
    return outRESTXML,errorMessage

### Start of Main

## Encode the username password for basic authentication
if Webservice_UserPass_Encoded == "":
   Webservice_Authorization = 'Basic ' + string.strip(base64.encodestring(Webservice_Username + ':'  +  Webservice_Password))
else:   
   Webservice_Authorization = 'Basic ' + Webservice_UserPass_Encoded
   
if Proxy_UserPass_Encoded == "":
   Proxy_Authorization = 'Basic ' + string.strip(base64.encodestring(Proxy_Username + ':' + Proxy_Password))
else:
   Proxy_Authorization = 'Basic ' + Proxy_UserPass_Encoded

inDateFormat = SDF(xmlFileNameDateAppend)
inDate=inDateFormat.format(date())

if xmlFileNameDateAppend == "":
    outXMLFile = OutputXMLFileFormat + '_ '+ str(rownbr)  +  '.xml'
else:
    outXMLFile = OutputXMLFileFormat + '_ ' + str(rownbr)  + '_' +  inDate + '.xml'


inURLData = URLFormat.replace(' ','%20')
retRESTXML,errorMessage=getRESTXML(inURLData)
if errorMessage <> "":
    raise errorMessage
else:
    xmlFileHandle=open(outXMLFile,'w')
    xmlFileHandle.write(retRESTXML)
    xmlFileHandle.close()

Output -

RESTful webservices can also return data in different format like CSV,TEXT,JSON etc.

They can also manipulate data using HTTP POST\DELETE methods.

Code can be easily customized to handle any condition.