Consuming a REST web service with Oracle

SOAP web services dominate in a SOA environment. However, there is a new kid on the block: REST. This is a more lightweight approach to exchange data over HTTP, which is becoming more and more popular with web mashups and the web 2.0 in general. Amazon, Twitter, Yahoo all expose their APIs via REST.

REST is an architecture style for designing networked applications. The idea is that, rather than using complex mechanisms such as CORBA, RPC or SOAP to connect between machines, simple HTTP is used to make calls between machines.

In this post I’ll show you how you can consume a REST web service via pure SQL in Oracle.

I have picked the Google Translate API as a show case. The API exposes a RESTful infrastructure via HTTP GET. You may want to use this API in a document warehousing situation where you need to translate unstructured documents on the fly.

The API exposes two methods. The Detect Language method allows us to identify a language by submitting a sentence or phrase. The Translate Language method allows us to translate one ore more pieces of texts into one or more languages. Details on parameters etc. can be found in the documentation. The Google Translate web service then returns a JSON object with the language detected or the translated text, depending on the method chosen.

JSON is a better fit [than XML] for Web services that power Web mashups and AJAX widgets due to the fact that it is essentially serialized Javascript objects which makes it fit better client side scripting which is primarily done in Javascript.

In Oracle we can use the HTTPURITYPE function to perform an HTTP GET to consume a RESTful web service. See one of my previous posts for some more details on HTTPURITYPE.

Ok, so let’s finally get started. In a first step we populate a table with the text we want to translate and its source language. For this I have picked an excerpt from the poem Rhyme of the Ancient Mariner by Samuel T. Coleridge. This is a great poem and if you get a chance read it. There is also an Iron Maiden song about it, if you want an executive summary…

CREATE TABLE go_trans_text (text_doc CLOB, language VARCHAR2(2))

INSERT INTO go_trans_text VALUES ('The rhyme of the ancient mariner. Day after day, day after day, we stuck, nor breath nor motion. As idle as a painted ship upon a painted ocean. Water, water, every where, and all the boards did shrink. Water, water, every where,nor any drop to drink.','en')

COMMIT;

Next we just run the following query against the web service and parse the JSON object. In Oracle no native support for JSON is provided. So we’ll just parse this with string functions and regular expressions. I have also come across some third party JSON PL/SQL package but have not tried this out yet. If you do it would be nice to leave me some feedback on it.

SELECT
   SUBSTR(httpget,INSTR(httpget,'langpair',1,level)+14,2),
   SUBSTR(JSONtranslate,INSTR(JSONTRANSLATE,'"translatedText":"',1,level)+18,INSTR(JSONTRANSLATE,'"},"responseDetails"',1,level)-INSTR(JSONTRANSLATE,'"translatedText":"',1,level)-18),
   text_doc
FROM (
   SELECT
      HTTPURITYPE(httpget).getCLOB() as JSONtranslate,
      httpget,
      text_doc
   FROM (
      SELECT
         text_doc,
         'http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&'||'q='||utl_url.escape(text_doc)||'&'||'langpair=' || language || '%7Cde&'||'langpair=' || language || '%7Cfr&'||'langpair=' || language || '%7Cit' AS httpget
      FROM
         go_trans_text
   )
)
CONNECT BY
   level <= (LENGTH(httpget) - LENGTH(REPLACE(httpget, 'q='))) / LENGTH('q=') *
   (LENGTH(httpget) - LENGTH(REPLACE(httpget, 'langpair='))) / LENGTH('langpair=')

We will now go through the above SQL statement step by step.

In the innermost SQL of the above query we construct our query string and bind the document text and source language values from our table into this query string.

SQL>       SELECT
  2           text_doc,
  3           'http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&'||'q='||utl_url.escape(text_doc)||'&'||'langpair=' || language || '%7Cde&'||'langpair=' || language || '%7Cfr&'||'langpair=' || language || '%7Cit' AS httpget
  4        FROM
  5           go_trans_text  ;

Note the use of the utl_erl.escape function. We use this function to URL encode our Coleridge poem.

The result is the following query string:

http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&q=The%20rhyme%20of%20the%20ancient%20mariner.%20Day%20after%20day,%20day%20after%20day,%20we%20stuck,%20nor%20breath%20nor%20motion.%20As%20idle%20as%20a%20painted%20ship%20upon%20a%20painted%20ocean.%20Water,%20water,%20every%20where,%20and%20all%20the%20boards%20did%20shrink.%20Water,%20water,%20every%20where,nor%20any%20drop%20to%20drink.&langpair=en%7Cde&langpair=en%7Cfr&langpair=en%7Cit                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

In the next innermost SQL we perform an HTTP Get to the Google Translate REST API.

SQL>    SELECT
  2        HTTPURITYPE(httpget).getCLOB() as JSONtranslate,
  3        httpget,
  4        text_doc
  5     FROM (
  6        SELECT
  7           text_doc,
  8           'http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&'||'q='||utl_url.escape(text_doc)||'&'||'langpair=' || language || '%7Cde&'||'langpair=' || language || '%7Cfr&'||'langpair=' || language || '%7Cit' AS httpget
  9        FROM
 10           go_trans_text
 11     )  ;

Google returns the translated text in a JSON object:

{"responseData": [{"responseData":{"translatedText":"Der Reim der alten Seefahrer. Tag für Tag, Tag für Tag, wir stecken noch Atem noch Bewegung. Im Leerlauf wie ein gemaltes Schiff auf einem gemalten Ozean. Wasser, Wasser, überall, und alle Platten haben schrumpfen. Wasser, Wasser, überall, noch ein Tropfen zu trinken."},"responseDetails":null,"responseStatus":200},{"responseData":{"translatedText":"La comptine de l\u0026#39;ancien marin. Jour après jour, jour après jour, nous avons bloqué, ni souffle, ni mouvement. Comme au ralenti comme une peinture sur un bateau peint océan. L\u0026#39;eau, l\u0026#39;eau, où tous, et tous les conseils scolaires ne diminuera. L\u0026#39;eau, l\u0026#39;eau, partout, ni de goutte à boire."},"responseDetails":null,"responseStatus":200},{"responseData":{"translatedText":"La rima del vecchio marinaio. Giorno dopo giorno, giorno dopo giorno, abbiamo bloccato, né respiro né movimento. Come minimo dipinta come una nave dipinta su un oceano. Acqua, acqua, ogni dove, e tutti i pannelli non si restringono. Acqua, acqua, ogni dove, né alcuna goccia da bere."},"responseDetails":null,"responseStatus":200}], "responseDetails": null, "responseStatus": 200}

In a last step we now need to multiply our JSON object by the number of target languages and then parse out the translated text. This will give us the translation:

TARGET_LANG	TRANSLATED_TEXT	                                                                                                                                                                                                                                                                              ORIGINAL_TEXT
de	         Der Reim der alten Seefahrer. Tag für Tag, Tag für Tag, wir stecken noch Atem noch Bewegung. Im Leerlauf wie ein gemaltes Schiff auf einem gemalten Ozean. Wasser, Wasser, überall, und alle Platten haben schrumpfen. Wasser, Wasser, überall, noch ein Tropfen zu trinken.	                  The rhyme of the ancient mariner. Day after day, day after day, we stuck, nor breath nor motion. As idle as a painted ship upon a painted ocean. Water, water, every where, and all the boards did shrink. Water, water, every where,nor any drop to drink.
fr	         La comptine de l'ancien marin. Jour après jour, jour après jour, nous avons bloqué, ni souffle, ni mouvement. Comme au ralenti comme une peinture sur un bateau peint océan. L'eau, l'eau, où tous, et tous les conseils scolaires ne diminuera. L'eau, l'eau, partout, ni de goutte à boire.	The rhyme of the ancient mariner. Day after day, day after day, we stuck, nor breath nor motion. As idle as a painted ship upon a painted ocean. Water, water, every where, and all the boards did shrink. Water, water, every where,nor any drop to drink.
it	         La rima del vecchio marinaio. Giorno dopo giorno, giorno dopo giorno, abbiamo bloccato, né respiro né movimento. Come minimo dipinta come una nave dipinta su un oceano. Acqua, acqua, ogni dove, e tutti i pannelli non si restringono. Acqua, acqua, ogni dove, né alcuna goccia da bere.	   The rhyme of the ancient mariner. Day after day, day after day, we stuck, nor breath nor motion. As idle as a painted ship upon a painted ocean. Water, water, every where, and all the boards did shrink. Water, water, every where,nor any drop to drink.

Note: As per Google terms and conditions you are only allowed to store this information for caching purposes, but are not allowed to persist, e.g. in a database.

It’s the tool, stupid. Or is it?

Every couple of months or so, they run an article on Business Intelligence in one of the Sunday business papers here in Dublin. The tenor of these articles goes like this: Just pick the right BI tool, sit back, and relax. It will all sort itself out. This reminds me of the Fast = On database switch a lot of people are looking for when they are doing performance tuning. I am always amused by the sales tone of these articles (In fairness there are also some valid points in the article, but a lot of it is just boring sales pitch). In reality, the right tool only contributes about 5-10% to a successful DW/BI project. At the end of the day, the big three BI tool vendors (Oracle/Siebel, SAP/Business Objects, IBM/Cognos) offer pretty much the same functionality. Their offerings only differ in nuances.

Don’t get me wrong. Of course I appreciate the presence of Business Intelligence in the mainstream media. However, this just gets across the wrong message, sets the wrong expectations, and eventually is damaging to the BI industry.

So then, what is actually important for a successful BI project?

From a technical point of view, this is without a shadow of a doubt the Data Warehouse. You may get away without a Data Warehouse in small pilot type BI projects that involve low volumes of data and just run against one data source. Howver, once it gets just a little bit more complex the BI tools run into all sorts of issues. Think of performance, data integration, data quality, concurrency, complexity (did you ever have to deal with the spider-web like chaos of a Business Objects Universe run against an OLTP system?), no or limited historical data, non-replicable data queries, performance (did we have that before?), and performance (again). Also tell me, how are you going to report against a recursive hierarchical relationship in a transactional system? Not easy, not easy.

In the words of Ralph Kimball (one of the founders of modern data warehouses):

“Periodically, there’s distracting noise in the industry about the feasibility of bypassing dimensional modeling and data warehouse databases to simply query and analyze operational data directly. Vendors promise magical middleware that hides the complexity of the source system so that business users can theoretically query the system [...] eliminating the costly and time consuming extract, transformation, and load processing. Though middleware may be able to mask the underlying data structures, it doesn’t address the inherent problems surrounding the performance [...].You may find middleware solutions are only capable of relatively light-on-thefly data transformations, leaving your data integration requirements dangling.”

Ralph Kimball, The Data Warehouse Lifecycle Toolkit, p.238.

From a business point of view, the single most important criteria for a successful BI project is to align the BI initiative with the overall business strategy. BI has to support the core business processes, resulting either in cost reductions or increased profits.

“For any given company in any given industry, we should systematically evaluate its industry, strategy, and business design as a means of identifying potential BI opportunities”.

Nancy & Steve Williams, The profict impact of Business Intelligence.

Of course, there are a lot of other factors that determine the success of a BI project (skill sets, implementation methodology, training etc.). Get the above two wrong though, and you are out.