ODI 11g in a Services Oriented Architecture. Part III. Consuming a RESTful web service.
Consuming web services in ODI
In part one of this series (Oracle Data Integrator in a Services Oriented Architecture) I had laid the groundwork and explained the basics of a SOA environment and how ODI fits into it. In part two we then looked at creating and deploying data services with ODI 11g in Weblogic. In part three we will now look at SOA from the other angle. I will show you how we can consume data services using ODI 11g.
Consuming a RESTful web service: OpenStreetMap (OSM) API
We can use ODI to consume public web services for data augmentation purposes, e.g. we can query the OpenStreetMap Geocode web service to augment customer data with longitude and latitude information.
OpenStreetMap provides geographic information such as maps, geocoding etc free of charge. You may wonder why we are not using Google maps? Well, first of all it’s not open source and Google’s license does not allow us to store the resultsets of our web service queries. I recently read the excellent book the Filter Bubble and since then try to avoid Google et al. even more. That’s why I have replaced Google search with Scroogle search.
Anyway, let’s get back to the topic at hand. As per the OpenStreetMap license anyone can use and store their data. They only ask you to mention OSM as the source of the data.
For details on the OSM license have a look at: http://wiki.openstreetmap.org/wiki/OpenStreetMap_License
There is also a page for the OSM usage policy
The OSM geocoding documentation can be found here
In addition there is an FAQ for the geocoding API.
When we put the above URL in our browser we will get the following XML in return.
We will now look at how we can extract and store this information using ODI. I will show you two ways of extracting longitude and latitude information from the XML. In the first example we use built in functionality of the Oracle database to extract the information. In the second example we will exclusively use ODI functionality to extract the data.
Example 1: Consuming a RESTful web service – ODI piggybacks on the Oracle database
If we know that the target for our RESTful web service request is an Oracle database we can use the utl_http functionality built into the Oracle database to query the OpenStreetMap geocoding web service. This is convenient as we can directly extract the attribute values we are interested in from the XML that the web service returns. We don’t have to first set down the data in an XML file on the file system and we don’t have to use ODI to load the XML file into our target database. This saves us two steps and a bit of development time.
In a first step we need to create an Access Control List (ACL) for user OE. The ACL will give user OE access to the nominatim.openstreetmap.org website, but prevents access to any other websites. ACLs are new in Oracle 11. We need to grant these privileges as user SYS.
We first create the ACL
begin dbms_network_acl_admin.create_acl ( acl => 'utl_http.xml', description => 'Normal Access', principal => 'OE', is_grant => TRUE, privilege => 'connect', start_date => null, end_date => null ); end;
Next we assign the domains we want to access to the ACL
begin dbms_network_acl_admin.assign_acl ( acl => 'utl_http.xml', host => 'nominatim.openstreetmap.org', lower_port => 1, upper_port => 10000); end;
Finally we grant access to user OE to the utl_http package.
grant execute on utl_http to OE;
Once we have granted the correct privileges to the OE user we can directly access the OpenStreetMap API via the Oracle database. We can piggyback on this functionality from within an ODI procedure.
We create the ODI procedure PRC_GEOCODE_CUSTOMERS that loads the longitude and latitude information into the OE schema.
In a first step in our procedure we create a table at runtime, which stores the XML that we retrieve from the OpenStreetMap geocode API
CREATE TABLE address_geocode ( address_geocode XMLTYPE, customer_id NUMBER )
In the next step we query the OpenStreetMap API. We submit customers with customer_ID 240 and 235 from the OE schema to the OSM geocode API. As we don’t want to swamp the OpenStreetMap servers with too many requests we just retrieve data for two customers.
The Oracle database allows us to make direct http requests through the little known HTTPURITYPE function, which is built on top of the utl_http package. Have a look at the Oracle Books site where more information and examples are given.
INSERT INTO address_geocode SELECT XMLTYPE(HTTPURITYPE('http://nominatim.openstreetmap.org/search?q='||street_address||','||postal_code||','||address_city||','||state_province||','||country_id||'&'||'format=xml'||'&'||'polygon=0'||'&'||'addressdetails=1').getclob()), customer_id FROM ( SELECT REPLACE(c.cust_address.street_address,' ','+') street_address, REPLACE(c.cust_address.postal_code,' ','+') postal_code, REPLACE(c.cust_address.city,' ','+') address_city, REPLACE(c.cust_address.state_province,' ','+') state_province, REPLACE(c.cust_address.country_id,' ','+') country_id, customer_id FROM customers c WHERE customer_id IN (240,235) )
If we execute the procedure we will find two XML records in our address_geocode table.
We are now ready to extract longitude and latitude information from the XML and store in table customer_geocodes together with the customer_id.
We add another step to our procedure in which we create table customer_geocodes at runtime.
CREATE TABLE customer_geocodes (customer_id NUMBER, longitude NUMBER, latitude NUMBER)
We then extract longitude and latitude information from the XML and store it in table customer_geocodes. We use the Oracle XMLTABLE functionality to extract these attributes from the XML. This function allows us to convert an XML file to a relational table. More details on XMLTABLE can be found in the documentation.
INSERT INTO customer_geocodes SELECT customer_id, xt.longitude, xt.latitude FROM address_geocode yt, XMLTable('searchresults/place' PASSING yt.address_geocode COLUMNS latitude NUMBER PATH '@lat', longitude NUMBER PATH '@lon' ) xt
We then execute the procedure and verify that the extracted information was loaded into our customer_geocodes table:
Example 2: Consuming a RESTful web service using native ODI functionality
If we don’t have the luxury of an Oracle database we can still query the OpenStreetMap API using ODI native functionality. We can make use of the Jython scripting language that ships with ODI to access the OSM API.
We create another procedure PRC_GEOCODE_CUSTOMERS_JYTHON. We add one step to the procedure and set the Technology to Jython. In the code we connect to the OSM API, retrieve the geocode XML, and store this on the C drive on the file system.
import java.net.URL as URL import java.io.BufferedReader as BR import java.io.InputStreamReader as SR import java.net.HttpURLConnection as con import base64 import string URLFormat="http://nominatim.openstreetmap.org/search?q=3314+Eastern+Ave,21224,Baltimore,MD,US&format=xml&polygon=0&addressdetails=1" XMLFile="C:OSMGeo_240.xml" OSMURL=URLFormat.replace(' ','%20') url = URL(OSMURL) con = url.openConnection() con.setRequestMethod("GET") con.connect() responseCode=con.getResponseCode() responseMessage=con.getResponseMessage() if responseCode == 200 and responseMessage == "OK": br=BR(SR(con.getInputStream())) outRESTXML = "" outline = br.readLine() while (outline != None): outRESTXML += outline + "n" outline = br.readLine() XMLfilehandle= open(XMLFile,'w') XMLfilehandle.write(outRESTXML) XMLfilehandle.close() else: con.disconnect() con.disconnect()
For the purpose of this walk through I have hard coded the address of the customer with CUSTOMER_ID 240.
Before we can extract the data contained in the XML file into the customer_geocodes table we first need to define the connection to the XML file in ODI Studio Topology.
In a first step we create the data server
Then we add the physical schema
Next we add the logical schema
Then we reverse engineer the model for the XML file
Once we have reverse engineered the model we should see the two new data stores Place and Searchresults in the XML_OSM_SRC model.
Before we create the interface to augment our customer data with longitude and latitude information we need to import two Knowledge Modules: LKM SQL to Oracle and IKM SQL Control Append.
The longitude and latitude information is contained in data store PLACE. We will use this data store as the source in our new temporary interface INT_OSM_XML_LOAD. Once we have added the PLACE data store to the interface drag and drop columns LAT and LON to the Target Datastore and manually add column CUSTOMER_ID to target datastore CUST_GEOCODES (right click in the target datastore and select Add Column).
As data type for column CUSTOMER_ID we select NUMBER. Enter 240 for the CUSTOMER_ID.
Note: In a real world scenario you would of course extract the customer_id from the filename of the XML file.
Next we change to the Flow tab and select LKM SQL to Oracle as the Load Knowledge Module.
We also select IKM SQL Control Append as the IKM and modify the following options:
Next we execute the Interface. This should create a new table CUST_GEOCODES in the OE schema and insert longitude and latitude for customer_id 240.
This example has shown that we don’t necessarily need the advanced features of the Oracle database to query a web service using ODI.