Skip to Content

Using SAP Data Services 4.2 we will walk through an example of consuming a REST web services via a Web Service REST Datastore.

To get a basic idea of what we will do, you can read through the SAP DS Integrator Guide, specifically the REST portions of these three sections:

Consuming external web services in SAP Data Services

>> Accessing a web service using the designer

>> Adding web service calls to a job

Also the processes is essentially the same as consuming as SOAP web service for which Saurav Mitra has posted an excellent article over at dwbi.org

A summary of the steps we will take:

1. Procure the WADL file for the REST web service we want to consume

2. Create the Datastore and function from the WADL file

3. Create our Dataflow that will consume the function (REST web service)

The REST service we will use in our example is Google’s Geocoding API.  I’m not advocating using this as a way to mass geocode addresses in Data Services, it simply is a nice open API to use for demoing the REST capabilities.

Here’s an example Call:

http://maps.googleapis.com/maps/api/geocode/xml?address=1600%20Amphitheatre%20Parkway,%20Mountain%20View,%20CA&sensor=false

In a browser you can see the results as:

/wp-content/uploads/2016/06/googleapiinbrowser_967612.jpg

Let’s get started!

1. Procuring the WADL

This can be one of the most difficult part of this effort.  Sometimes REST service publishers do make a WADL available and sometimes they do not.  The WADL is what defines the REST service call for Data Services, similar to the WSDL for a SOAP service.  You will very likely have to create your own WADL, or use the help of some tools to do so.

If you want to skip this step you can simply download the attached WADL file to your PC with DS Designer on it, and save the file without the .txt extension (GoogleGeocodeAPI.wadl).  Note: if Google changes their API after this blog is posted, you may have to tweak or recreate these. Also, passing in different address types to Google’s API can yield different XML results, meaning the schema we are tying into here isn’t necessarily complete.  For the sake of demo we will use some addresses that yield similar results

One free tool that can help us generate the WADL is SoapUI by Smart Bear.

If you download the free version of SoapUI you can

a. Create a new REST Project (File menu –> New REST Project)

b. Enter the service URI when prompted, and click OK

http://maps.googleapis.com/maps/api/geocode/xml?address=1600%20Amphitheatre%20Parkway,%20Mountain%20View,%20CA&sensor=false

    /wp-content/uploads/2016/06/soapuinewrestproject_967662.jpg

c. In your new project window, you can test the service by clicking the submit button and viewing the result pane.

/wp-content/uploads/2016/06/soapuitest_967663.jpg

d. To generate the WADL, right click on the service in the project navigator and choose “Export WADL”

/wp-content/uploads/2016/06/soapuiwadlexport_967676.jpg

Note: wherever you save it, it will be called “_1.wadl”


e. If you open the file in your favorite XML editor, you’ll notice that it contains information on the parameters to pass to the service (outlined in green), but no details about the response format (outlined in red).  Note: the geocode api has more parameters than we show here, but because we only specified these parameters in our sample call in SoapUI, they are the only ones present in the generated WADL.

/wp-content/uploads/2016/06/initialwadl_967677.jpg


f. To address the missing response info we need to add a <grammars> section just after the first <doc /> tag

/wp-content/uploads/2016/06/wadladdgrammars_968093.jpg

g. Within the grammars section we need to add the schema of the response.  To do this, copy the XML Response from the SoapUI test we did, and paste into an XML to XSD generator.  For example here is one.

h. Copy the schema output into the grammars section

/wp-content/uploads/2016/06/wadlgrammarspaste_968094.jpg


i. Finally we have to tie the response to the schema.  Add the following attributes to the response <representation /> tag:

element="GeocodeResponse" xmlns:xs="http://www.w3.org/2001/XMLSchema"

/wp-content/uploads/2016/06/wadlresponsetie_968095.jpg

j. Save the WADL file…. preferably as something other than _1.wadl



2. Creating the Datastore from the WADL file

a. Launch SAP Data Services Designer

b. On the Datastore tab of the Object Library view, right click on a blank area and choose New

c. Give your data store a name, select type: Web Service REST, and in the Web Service URI browse to the WADL file we created in step 1, and click OK to create.

/wp-content/uploads/2016/06/dsnewdatastoreparms_967857.jpg

If the create was successful, GREAT JOB!  You will see your new Datastore listed.  If it was not successful, there is something wrong with your WADL file, designer will indicate the line and column where it had the issue, try to find and correct the related issue and then try to recreate the Datastore.  We worked through several xml validation issues until we got it right!

/wp-content/uploads/2016/06/dscreated_967859.jpg

d. Next we need to import the function call.  In the Datastore browser, double-click on the f(x) Functions node.  This will open the Datastore explorer.  Expand the tree until you see the XML node.  Right-click the XML node and select Import.  (note: this was another area where we had to work through some XML validation issues originally).

/wp-content/uploads/2016/06/dsimportfunc_967861.jpg

e. You should now see the function listed!

/wp-content/uploads/2016/06/dsimportedfunc_968096.jpg

3. Creating a Dataflow that will consume the function (REST web service)

Next we will walk through a simple data flow where we consume a CSV file that contains our parameters for the service call (address and sensor), calls the service, and writes the the address along with geo coordinates out to another CSV.

CSV (address, sensor) –> REST service –> (address, latitude, longitude).

a. In designer we’ve created a test project with a batch job and a new dataflow.

/wp-content/uploads/2016/06/dsdfsetup_968907.jpg

b. Next we’ll create a test CSV file to use as the source of our parameters to the REST call.  The file as two columns, address and sensor.  A copy of this file is attached (googleresttest.txt).


address, sensor
"1600 Amphitheatre Parkway, Mountain View, CA",false
"3999 West Chester Pike, Newtown Square, PA",false




c. To use our file we’ll create a new flat file format.  Here are the settings used to create it

/wp-content/uploads/2016/06/dsffsetup_968908.jpg

d. Add the Flat file as a source to our data flow, and then add a query in which we will call the function module, and link the two together.

/wp-content/uploads/2016/06/dsdf1_968933.jpg

/wp-content/uploads/2016/06/dsdf2_968934.jpg

e. Inside the Query we add the function call by right clicking the target schema and choosing “new function call…”

/wp-content/uploads/2016/06/dsdf3_968942.jpg

f. Then we select our function

/wp-content/uploads/2016/06/dsdf4_968943.jpg

g. Next we see the parameter mapping screen and notice we are unable to map because of the nested structure that the function takes.  We’ll take care of that in the next step, just hit finish for now.

/wp-content/uploads/2016/06/dsdf5_968944.jpg

You should see this in your target schema of the query.

/wp-content/uploads/2016/06/dsdf6_968948.jpg

h. Let’s go back and add an additional query step in between our flat file and our REST call.  We will use this to structure, or nest, the data in the format needed by the function.  We also need to add a vanilla Row Generator feeding into the nest query, without which we would end up with only one address getting geocoded.

/wp-content/uploads/2016/06/dsdf18_969110.jpg

i. In our nest query, right click on the target schema, and choose “New Output Schema…”, and give fill in a new schema name.

/wp-content/uploads/2016/06/dsdf9_969111.jpg

j. in the target schema, make sure the PARAMETERS sub-schema is select, then in the source schema, select the address and sensor fields, right click and choose map to output.

/wp-content/uploads/2016/06/dsdf19_969112.jpg

k. Next with PARAMETERS still selected ad the Schema Out, we need to hook in the row generator by checking the Row_Generation in the From tab, and unchecking our file format.

/wp-content/uploads/2016/06/dsdf20_969125.jpg

l. Now we go back to the REST function call query, where we can see our new incoming structure.  Next right click on the function call and choose “Modify Function Call…”.  Click on the new PARAMETERS sub-schema and drag it down to the URI_Parameters box. Then click finish.

/wp-content/uploads/2016/06/dsdf12_969012.jpg

m. Now let’s add a new Query after the REST function query to unnest the results.  Map the GeocodeResonse to the output, then right click the QRY_UNEST and choose “Unnest with Sub-shemas”

/wp-content/uploads/2016/06/dsdf15_969126.jpg

n. Now we’ll add another query after the unnest, and map the columns we want to dump to a file.  However after you map the columns, you’ll notice if we run a validation that it comes up with an error about each column.  I’m not sure if this is a bug in how DS maps unnested columns, but you can correct by removing the original subschema names from the new mapping.  The other thing we need to do is mark the select “distinct rows” option because of the format of the response, the unnesting can result in multiple rows per address.

/wp-content/uploads/2016/06/dsdf16_969127.jpg

o. Lastly we can take the result and dump it out to a file, by generating a file format from our final query, and making it a target.

/wp-content/uploads/2016/06/dsdf17_969143.jpg

p. Now if we execute our test, the resulting file contains:


formatted_address,lat,lng
"1600 Amphitheatre Pkwy, Mountain View, CA 94043, USA",37.4224607000,-122.0842702000
"3999 West Chester Pike, Newtown Square, PA 19073, USA",39.9880211000,-75.4153618000

We hope this example proves useful. Again, the Google geocode API has many variations that are not all accounted for in this example, so if you try other addresses, don’t be surprised if it breaks due to the different elements in the response.  Also, there are probably other better ways to mass geocode addresses, again we are just using it as an easy example.  Thanks!

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Tobias Strobel

    Great Post Perry!

    Unfortunetley I can’t get it to work.

    When I execute the Job the error ” QRY_GEOCODE_REST-Function1, HTTP-Client error:<7>:<Couldn’t connect to server>.” occurs.

    I am not sure why this is not working. Calling the WS from SOAP UI and via Browser is working fine (my Laptop). It is also possible to call the WS with Internet Explorer on the Data Services server.

    I was able to create the data store and import the function, so at least from the designer the connection to the server was possible, right?

    Do you have any idea?

    (0) 
  2. Anirban Haldar

    Thanks Perry for posting step by step instruction.
    I am able to implement the same now.
    I have another task where we need to consume JSON response from REST web service call.
    But I am stuck with capturing response,in DS Designer I get only EMPTYBODY as reply schema when I imported the function module and getting NULL output….I think this is to do with WADL file defination.
    Do you have similar post to deal with(properly construct) JSON based WADL file

    Regards
    Anirban

    (0) 
    1. Dushyant Desai

      Hi, I am also facing same error  HTTP-Client error:<7>:<Couldn’t connect to server>.

      What were the steps u performed to get it worked?

       

      Best Regards

      Praveen

      (0) 
  3. Pankaj Singh

    Hi,

    After doing all this i am not able to consume this in BODS as datastore as it throws an error. Does anyone has a working WADL file with above URL?

     

    Thanks

    Pankaj

    (0) 
  4. Shashi G

    I was able to complete till step f. On step g, I have below issue.

    Under URI_Parameters in BODS I see Address and Sensor duplicated. How do I remove the 2 extra ones?

    (0) 
  5. Mike Way

    Hi, like other users I have the same problem as them, the HTTP client error:<7>:<Couldn’t connect to server>. I checked the proxy and nothing is solved, a part of it, I checked the AL_ERROR_NUM that gets a number 3, checking the guides the AL_ERROR_NUM = 3 is because: The call to the server failed. Possible causes include:

    • invalid URL
    • malformed URL
    • server does not exist
    • SSL certificate is missing
    • proxy or firewall does not allow Data Services to communicate with the URL
    • no internet communication
    • resource can’t be found
    • lack of privileges to access the resource

     

    So I don’t know where the error can be, if someone that was able to do it please reply, I will appreciate.

     

    Regards.

     

    Mike.

    (0) 

Leave a Reply