Tweets analysis with Node.js and SAP Hana

I had the idea to write this blog post, because I was waiting for the first public beta of OS X Yosemite, and after the release of beta, I was curious to know what the people was talking about it.

The idea here, is to get tweets posted using #OSXYosemite hashtag, insert it at SAP Hana table, and analyse the sentimental, using Text Analysis.

An easy way to catch tweets is using Node.js, and with help of amazing module “SAP Hana Database Client for Node” it keep incredibly simple.

Pre-requirements:

1. Node.js installed

2. Twitter dev account

3. SAP Hana environment

Let’s go coding…

1. Creating SAP Hana table

I created a XS Project, a schema called TWEETS and defined a new table with name Tweets:

// Tweets.hdbtable
table.schemaName = "TWEETS";
table.tableType = COLUMNSTORE;
table.columns = [
    {name = "ID"; sqlType = BIGINT; nullable = false;},
    {name = "USER_NAME"; sqlType = NVARCHAR; length = 100; nullable = false;},
    {name = "TEXT"; sqlType = NVARCHAR; nullable = false; length = 340;},
    {name = "PROFILE_IMG_URL"; sqlType = NVARCHAR; nullable = true; length = 200;},
    {name = "KEYWORD"; sqlType = NVARCHAR; nullable = false; length = 100;}];
table.primaryKey.pkcolumns = ["ID"];

This table is used to store the tweets catched, the Node.js app (described below), will insert data into this table.

2. Getting Tweets

As said before, this part was done using Node.js, using the HDB and NTwitter modules. Follow the steps below to create your “Tweets Catcher”:

Install hdb module, using npm install hdb.

Install ntwitter module, using npm install ntwitter.

Create a JSON file with your access token values to Twitter. In my case, I created twitter.json file, with content:

// twitter.json
{
    "consumer_key": <YOUR_API_KEY>,
    "consumer_secret": <YOUR_API_SECRET>,
    "access_token_key": <YOUR_ACCESS_TOKEN>,
    "access_token_secret": <YOUR_ACCESS_TOKEN_SECRET>
}

Create a other JSON file, with your SAP Hana instance credentials, like the below:

// hdb.json
{
  "host": <HOSTNAME_OR_IP_ADDRESS>,
  "port": <PORT>,
  "user": <USERNAME>,
  "password": <PASSWORD>
}

Finally, create the app.js file, with code below:

var hdb            = require('hdb'),
      hdbAuth    = require('./hdb.json'),
      Twitter      = require('ntwitter'),
      twitterAuth = require('./twitter.json');
var client = hdb.createClient(hdbAuth);
var keyword = '#OSXYosemite';    // Tweets with this keyword will be catched
var twitter = new Twitter(twitterAuth);
function saveTweet(tweet) {
    client.connect(function(err) {
        if (err) {
            return console.error('Connect error', err);
        }
        var sql = 'INSERT INTO "TWEETS"."Tweets::Tweets" values(' + tweet.id + ', \'' + tweet.user.screen_name + '\', \'' + tweet.text.replace(/'/g, " ") + '\', \'' + tweet.user.prl', '') + '\', \'' + keyword + '\')';
        console.log('SQL:', sql);
        client.exec(sql, function (err, affectedRows) {
          client.end();
          if (err) {
            return console.error('Insert error:', err);
          }
          console.log('Added with success!');
        });
    });
};
function getTwitterStream() {
    twitter.stream('statuses/filter', {track: keyword}, function (stream) {
        stream.on('data', function (data) {
            console.log('New tweet!');
            saveTweet(data);
        });
        stream.on('error', function (err) {
            console.log('ERROR');
            console.log(err);
        });
    });
}
getTwitterStream();

Start getting tweets: node app.js

The console output will be something like that:

I keep this app running for about 2 hours on the day OS X Yosemite public beta was released, just to get some sampling data. The table Tweets was populated with more than 150 tweets.

3. Analysing the sentimentals

Since SP5, SAP Hana provide a service to analyse unstructured data, like texts. I created FullText index, analysing the column Text of Tweets table, using the following SQL statement:

CREATE FullText INDEX "TWEETS_FT" ON "TWEETS"."Tweets::Tweets"(Text) TEXT ANALYSIS ON CONFIGURATION 'EXTRACTION_CORE_VOICEOFCUSTOMER';

The SAP Hana created a new table, called $TA_TWEETS_FT. Two columns of this new table is important for this exercise, the TA_TYPE, that classify the text analysed into a specific type, and the TA_COUNTER, that show the number of occurrences of this type. I’m looking for the followings types: StrongPositiveSentiment, WeakPositiveSentiment, NeutralSentiment, WeakNegativeSentiment and StrongWeakSentiment. As an option, you can use the types related with emoticons, like StrongPositiveEmoticon, etc.

The table populated:

The next step is create the OData service, but before that, I created a view where the OData service will be based on. This view select data from $TA_TWEETS_FT and return two columns, one with sentimental and other with percentual. The query used in the view:

SELECT "TA_TYPE", ROUND("UNIT_VALUE"/ "TOTAL_VALUE" * 100,2) AS "VALUE_PERCENTAGE"
FROM
( SELECT "TA_TYPE", SUM("TA_COUNTER") AS "UNIT_VALUE" FROM "TWEETS"."$TA_TWEETS_FT"
  WHERE TA_TYPE IN ('StrongPositiveSentiment', 'WeakPositiveSentiment', 'NeutralSentiment', 'WeakNegativeSentiment', 'StrongNegativeSentiment')
  GROUP BY "TA_TYPE" ) AS TABLE_UNIT,
( SELECT SUM("TA_COUNTER") AS "TOTAL_VALUE" FROM "TWEETS"."$TA_TWEETS_FT"
  WHERE TA_TYPE IN ('StrongPositiveSentiment', 'WeakPositiveSentiment', 'NeutralSentiment', 'WeakNegativeSentiment', 'StrongNegativeSentiment') ) AS TABLE_TOTAL ORDER BY VALUE_PERCENTAGE DESC

And the result:

The view created, to be used by OData service:

// SentimentalView.hdbview
schema="TWEETS";
query="SELECT \"TA_TYPE\", ROUND(\"UNIT_VALUE\"/ \"TOTAL_VALUE\" * 100,2) AS \"VALUE_PERCENTAGE\" FROM ( SELECT \"TA_TYPE\", SUM(\"TA_COUNTER\") AS \"UNIT_VALUE\" FROM \"TWEETS\".\"$TA_TWEETS_FT\" WHERE TA_TYPE IN ('StrongPositiveSentiment', 'WeakPositiveSentiment', 'NeutralSentiment', 'WeakNegativeSentiment', 'StrongNegativeSentiment') GROUP BY \"TA_TYPE\" ) AS TABLE_UNIT, ( SELECT SUM(\"TA_COUNTER\") AS \"TOTAL_VALUE\" FROM \"TWEETS\".\"$TA_TWEETS_FT\" WHERE TA_TYPE IN ('StrongPositiveSentiment', 'WeakPositiveSentiment', 'NeutralSentiment', 'WeakNegativeSentiment', 'StrongNegativeSentiment') ) AS TABLE_TOTAL ORDER BY VALUE_PERCENTAGE DESC";

4. Creating OData service

The OData service was used to provide sentimental ranking to UI, done using SAPUI5.

I created a OData service based on SentimentalView view, created on the step before.

// Sentimental.xsodata
service {
 "Tweets::SentimentalView" key ("TA_TYPE");
}

5. Creating SAPUI5 Chart

The final step was the creation of a SAPUI5 Application, with just one view, to show the chart with result of OData service, following the code of view.

// Chart.view.js
createContent : function(oController) {
  var url = <URL_ODATA>;
  var oModel = new sap.ui.model.odata.ODataModel(url, true);
  var oDataset = new sap.viz.ui5.data.FlattenedDataset({
            dimensions : [ {
                axis : 1,
                name : 'Sentimental',
                value : "{TA_TYPE}"
            } ],
            measures : [{
                name : 'Percentual',
                value : '{VALUE_PERCENTAGE}'
            } ],
            data : {
                path : "/SentimentalView"
            }
        });
  var oPieChart = new sap.viz.ui5.Pie({
  width : "80%",
  height : "400px",
  plotArea : {
  },
  title : {
  visible : true,
  text : 'OS X Yosemite Sentimental'
  },
  dataset : oDataset
  });
  oPieChart.setModel(oController.oModel);
        return oPieChart;
  }

The final result:

I hope this post can be useful and give you an idea of a kind of SAP Hana development.