Skip to Content

Hana is a  Structured Database

Hbase is Hadoop’s Unstructured Database.


You probably already used Hbase today and didn’t realise it.

Hbase is used by Facebook, Twitter and Linkedin, to name but a few companies.


Broadly speaking a Hbase table has only 3 fixed points:

1) Table name

2) It’s key (a single field)

3) It’s Column families  [A column family is similar to a BW cube dimension, it represents a logical grouping of fields]


Beyond that anything goes. Columns can be added and populated on the fly.  Columns are unique to a record rather than the entire table.


Logically it makes sense though to make it semi-structured.

With consistent structures you can then use HIVE (Hadoop SQL) or Impala (Cloudera’s Realtime SQL) via smart data access to be read within HANA.   Good luck though as the underlying dataset grows 😉


In an earlier blog I demonstrated how real-time tweets could be loaded into HANA & HBASE using HADOOP Flume.

http://scn.sap.com/community/developer-center/hana/blog/2013/08/07/streaming-real-time-data-to-hadoop-and-hana


This wasn’t necessarily the easiest way to load tweets into HANA  but provided the foundation for this blog.

To keep HANA lean and mean I only transferred the most relevant info to HANA.  E.g. to do sentiment analysis.


Rather than discard the rest of the tweet information (e.g. meta-data), I decided to horde it in Hadoop Hbase, for future reference.  The point of Hadoop is that it is a cheap scalable medium for storing and analyzing Big Data in the years to come.


Much like an iceberg I’ve keep the most important data visible in Hana, and left the large amount of related data, hidden beneath the waves, in HADOOP.


I still want to be able to view the data in Hadoop, so the the following is an example HANA XS development that enables me to view the entire Iceberg.


Using HANA XS & SAPUI5  I’ve created a simple Summary table of the tweets in HANA:


By selecting a Tweet I can now view the full details of the Tweet which is ONLY stored in Hadoop Hbase:

Note: In this case the ‘Detail’ is the full JSON string of the original tweet.

With a bit of time I could have easily split this into different SAPUI5 elements

To achieve this I’ve made use of the HBASE REST API known as Stargate:

http://wiki.apache.org/hadoop/Hbase/Stargate

Using the Hadoop User interface (HUE)  I can also view the same tweet details:

Note:  This screenshot is taken from a CDH version of Hadoop. 

The Hortonworks distribution doesn’t have HBASE visible from HUE yet, but you can still use the Stargate API.


In both HANA and Hbase tables I used the same key – TWEET ID


In order to bridge the gap between HANA and HBASE  I needed to:

– Create a link to the Hadoop Hbase Stargate API using an .xshttpdest  artifact

– Create HANA server side Javascript to perform a GET on HBASE stargate,  using the common KEY.


External http service:

Hbase.xshttpdest

host = “yyy.yyy.yyy.yyy”;       {IP address of the HADOOP Cluster}

port = 20550;                       {Configured Hbase Stargate Port}

description = “Hbase stargate connection”;

useSSL = false;

authType = none;

useProxy = false;

proxyHost = “”;

proxyPort = 0;

timeout = 0;


HANA Server Side JS to GET and re-format the Stargate response:

HbaseTweetGET.xsjs

//import a library for decoding base64 string

$.import(“HanaHbase”,”base64″);

var base64 = $.HanaHbase.base64;

//create client

var client = new $.net.http.Client();

// Use HBASE destination defined in Hbase.xshttpdest

var dest = $.net.http.readDestination(“HanaHbase”, “Hbase”);

var hBaseUrl;

//Next need to build up the url string expected by HBASE Stargate REST service

//to return a single tweet records from the tweet table, by the key

// e.g. /tweets/418677249424904192

//Currently hard coding the name of the Hbase table ‘tweets’

//input is ‘key’  of the HBASE table to return a single row

hBaseUrl = ‘/tweets/’;

hBaseUrl += $.request.parameters.get(“key”) + ‘/’; // || “/”;

var request = new $.net.http.Request($.net.http.GET, hBaseUrl);

request.headers.set(“Accept”, “application/json”);

// send the request and synchronously get the response

client.request(request, dest);

var response = client.getResponse();

//get all the cookies and headers from the response

var co = [], he = [];

for(var c in response.cookies) {

    co.push(response.cookies[c]);

}

for(var c in response.headers) {

     he.push(response.headers[c]);

}

// get the body of the Response from Hbase

var body = undefined;

if(!response.body)

    body = “”;

else

    body = response.body.asString();

var objBody;

objBody = JSON.parse(body);

// Hbase returns strings in base64 encoding

// Strings need to be decoded which could be done in XSJS or in the Front End JS

// I’ve opted to do in XSJS as I am also reformatting the body before sending to front end

if (objBody.Row != undefined ) {

  for (var i=0;i<objBody.Row.length;i++) {

  objBody.Row[i].key = base64.decode(objBody.Row[i].key);

  for (var j=0;j<objBody.Row[i].Cell.length;j++) {

  objBody.Row[i].Cell[j].column = base64.decode(objBody.Row[i].Cell[j].column);

  objBody.Row[i].Cell[j].$ = base64.decode(objBody.Row[i].Cell[j].$);

  }

  }

}

// send the response as JSON

$.response.contentType = “application/json”;

$.response.setBody(JSON.stringify({“status”: response.status, “cookies”: co, “headers”: he, “body”: objBody}));

NOTE:  HBASE Stargate uses BASE64 encoding so I also used a library (base64.xsjslib) to decode the dataset.

I used the following code to create base64.xsjslib:

Algorithm Implementation/Miscellaneous/Base64 – Wikibooks, open books for an open world


The Results of the HbaseTweetGET.xsjs are:


NOTE:  The areas marked in    above denote the data already stored in HANA (tip of the iceberg).  The rest resides submerged in Hbase.


With a working connection in place it’s then pretty straightforward to create a simple SAPUI5 page that enables the summary data to be read from HANA and the details from HBASE:

index.html

<!DOCTYPE html>

<html><head>

    <meta http-equiv=’X-UA-Compatible’ content=’IE=edge’ />

    <meta http-equiv=”Content-Type” content=”text/html;charset=UTF-8″/>

    <title>Hana Hbase integration</title>

    <script id=’sap-ui-bootstrap’

        src=”/sap/ui5/1/resources/sap-ui-core.js”

        data-sap-ui-theme=’sap_goldreflection’

        data-sap-ui-libs=’sap.ui.commons,sap.ui.ux3,sap.ui.table’></script>

<script>

   /***************************************************

        HANA Output Table

      ***************************************************/

        var oPanel = new sap.ui.commons.Panel().setText(‘Tweets in Hana’);

        var oModel = new sap.ui.model.odata.ODataModel(“tweets.xsodata”, false);

        oTableHana = new sap.ui.table.Table(“tweetsTable”,{tableId: “tableID”,

                   visibleRowCount: 4,

                   firstVisibleRow: 3,

                   visibleRowCountMode: sap.ui.table.VisibleRowCountMode.Fixed,

                   rowSelectionChange: onRowSelect,

                   selectionMode: sap.ui.table.SelectionMode.Single,

                   selectionBehavior: sap.ui.table.SelectionBehavior.Row

                    });

        oTableHana.setTitle(“Tweets”);

        oTableHana.setModel(oModel);

        var colGby = new sap.ui.table.Column({label: new sap.ui.commons.Label({text:”Tweet id”}),

                                         template: new sap.ui.commons.TextView().bindProperty(“text”,”ID”),

                                         width: “40px”,

                                      sortProperty: “Tweet id”,

                                      filterProperty: “Tweet id”

                                             });

     oTableHana.addColumn(colGby);

     colGby = new sap.ui.table.Column({label: new sap.ui.commons.Label({text:”Created At”}),

                                         template: new sap.ui.commons.TextView().bindProperty(“text”,”CREATEDAT”),

                                         width: “40px”,

                                      sortProperty: “CREATEDAT”,

                                      filterProperty: “CCREATEDAT”

                                             });

     oTableHana.addColumn(colGby);

     colGby = new sap.ui.table.Column({label: new sap.ui.commons.Label({text:”User Name”}),

                                         template: new sap.ui.commons.TextView().bindProperty(“text”,”USERNAME”),

                                         width: “40px”,

                                      sortProperty: “User Name”,

                                      filterProperty: “User Name”

                                             });

     oTableHana.addColumn(colGby);

     colGby = new sap.ui.table.Column({label: new sap.ui.commons.Label({text:”Tweet”}),

                                         template: new sap.ui.commons.TextView().bindProperty(“text”,”CONTENT”),

                                         width: “300px”,

                                      sortProperty: “Tweet”,

                                      filterProperty: “Tweet”

                                             });

      oTableHana.addColumn(colGby);

  //Initially sort the table

       var sort1 = new sap.ui.model.Sorter(“ID”);

       oTableHana.bindRows(“/TWEETS”,sort1);

        oTableHana.sort(“Tweet id”);

       oPanel.addContent(oTableHana);

        oPanel.placeAt(“uiArea”);

   /***************************************************

        HADOOP HBASE Output

      ***************************************************/

        var oPanelHbase = new sap.ui.commons.Panel().setText(‘Tweet Detail in Hbase’);

 

 

        //******* new try

        var oModelHbase = new sap.ui.model.json.JSONModel();

        oLayout = new sap.ui.commons.layout.MatrixLayout(“mHbaseLayout”,{columns: 2, widths : [‘5%’, ‘95%’ ]} );

        oLayout.setModel(oModelHbase);

        var vText = ”;

        var vField = ”;

   

   

        // Twitter profile image

       var oImage = new sap.ui.commons.Image(“i1”);

       oImage.bindProperty(“src”, “Cell/6/$”, function(sValue) {

          return sValue;

        });

       oImage.setTooltip(“Tweet Profile Image”);

       oImage.setDecorative(false);

   

   

        // Tweet Comment

        vText = ‘Tweet’;

        vField = ‘comment’;

      var oTF = new sap.ui.commons.TextArea(“HTA-TextArea-“+ vField, {tooltip: vText, editable: false,

      value: ”,

      width: ‘100%’, height: ’50px’,

            wrapping : sap.ui.core.Wrapping.Soft

        });

        oTF.bindProperty(“value”, “Cell/3/$”, function(sValue) {

          return sValue; // && sValue.toUpperCase();

        });

    

        oLayout.createRow(oImage,oTF);

 

        // Full Tweet JSON String

        vText = ‘Tweet JSON’;

        vField = ‘jsonstr’;

        var oLabel_JStr = new sap.ui.commons.Label(“HLabel-l”+ vField, {text: vText, labelFor: oTF});

      var oTA_JStr = new sap.ui.commons.TextArea(“HTA-TextArea-“+ vField, {tooltip: vText, editable: false,

      value: ”,

      width: ‘100%’, height: ‘300px’,

            wrapping : sap.ui.core.Wrapping.Soft

       });

       oTA_JStr.bindProperty(“value”, “Cell/2/$”, function(sValue) {

          return sValue;

       });

 

       var oCell = new sap.ui.commons.layout.MatrixLayoutCell({colSpan : 2 });

       oCell.addContent(oTA_JStr);

       oLayout.createRow(oCell);

  

       //Add Layout to Hbase Panel

       oPanelHbase.addContent(oLayout);

    

    

      oPanelHbase.placeAt(“uiArea”);

   /***************************************************

        ON ROW SELECT

      ***************************************************/

  function onRowSelect (oEvent){

     var oContext = oEvent.getParameter(“rowContext”);

     var TweetID = oContext.getProperty(“ID”);

     var HbaseJSON =  “HbaseTweetGET.xsjs?key=” + TweetID;

                        

      jQuery.ajax({

       url: HbaseJSON,

       method: ‘GET’,

       dataType: ‘json’,

       //async: false, // Switch of ASync

       success: setTweet,

       error: function(xhr, textStatus, errorThrown) {return;} });

  }

   

         function setTweet(collection) {

           oModelHbase.setData(collection);

           oLayout.bindContext(“/body/Row/0”);

         }

   

</script>

</head>

<body class=’sapUiBody’>

    <div id=”uiArea”></div>

</body>

</html>



I hope you found this interesting, your comments and suggestions are welcome.



3-June-2014:

If you are also interested in writing to HBASE using HANA, then check out this related blog.

Reading and Writing to HADOOP HBASE with HANA XS


To report this post you need to login first.

5 Comments

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

    1. Aron MacDonald Post author

      Thanks for the comment.

      I’m happy to add a version in the Hortonworks GitHub.


      I’ve done a few other hadoop/hana blogs, some may be more applicable than this. Perhaps you can send me an email and we can discuss briefly which (if any) you may think suitable for inclusion.

      (0) 
  1. Kevin Small

    Good article Aron, clearly explained.

    Would it be possible to achieve the same functionality using Smart Data Access?  So in the article you get the “bottom of the iceberg” via the Stargate API.  Could the same data be retrieved from Smart Data Access, if you know the Tweet key, then would it perform ok for single records?

    (0) 
    1. Aron MacDonald Post author

      Thanks Kevin and interesting question.

      The main problem with using SDA in this scenario would be the lag time. HIVE or Impala SQL reads on Hbase tables are slower than off other data formats in  HADOOP.

      Even if properly tuned I’m guessing a SQL read on HBASE might be 5 seconds or more [or 30 seconds+  if NOT using the latest Hortonworks enhancements to Hive  OR Clouderas Impala] . HIVE,Impala  & SDA will just add processing time to the Hbase read.

      For Reports the lag probably wouldn’t be an issue, but for an application the delay between clicks might be frustrating.

      By contrast accessing Hbase directly enables a more instantaneous response.

      The other advantage is that the Hbase Stargate API is Bi-directional, so data can also be written back to Hadoop. That’ll be a blog for another day. 😉

      (0) 

Leave a Reply