Skip to Content
Author's profile photo Thiago Sasai

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.


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
  "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.user.screen_name + '\', \'' + tweet.text.replace(/'/g, " ") + '\', \'' + tweet.user.prl', '') + '\', \'' + keyword + '\')';
        console.log('SQL:', sql);
        client.exec(sql, function (err, affectedRows) {
          if (err) {
            return console.error('Insert error:', err);
          console.log('Added with success!');
function getTwitterStream() {'statuses/filter', {track: keyword}, function (stream) {
        stream.on('data', function (data) {
            console.log('New tweet!');
        stream.on('error', function (err) {

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:


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:

  WHERE TA_TYPE IN ('StrongPositiveSentiment', 'WeakPositiveSentiment', 'NeutralSentiment', 'WeakNegativeSentiment', 'StrongNegativeSentiment')
  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
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{
            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
        return oPieChart;

The final result:


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

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Naveen Vishal
      Naveen Vishal

      Nicely explained Thiago Sasai ! Will give it a try. 🙂

      Author's profile photo Michael Pytel
      Michael Pytel

      Great blog!  Where can I find more info on the unstructured text analysis?

      Author's profile photo Thiago Sasai
      Thiago Sasai
      Blog Post Author


      Thank you.

      You can find it at SAP Hana Academy channel on YouTube ( Text Analysis and Search - YouTube), and on SAP Hana Developer Guide (, section 10.1.3 as well.


      Author's profile photo Former Member
      Former Member

      I am trying out your code Thiago.

      I have a technical question on how to run app.js to get the tweets into the table in the first part. It seems I cannot be behind a proxy ( in my case, VPN) to get the tweets.The error if I call the app from behind a proxy is:


      { [Error: getaddrinfo ENOTFOUND] code: 'ENOTFOUND', errno: 'ENOTFOUND', syscall:

      'getaddrinfo' }

      So, I can get 'New tweet!', if I get out of vpn but then I no longer have the connection to HDB which sits in SAP network, and thus get this error:

      C:\_2_documents\Node\applications\node1>node app.js
      New tweet!
      New tweet!
      Connect error { [Error: Cannot connect in state "opening"] code: 'EHDBCONNECT' }

      New tweet!
      Connect error { [Error: Cannot connect in state "opening"] code: 'EHDBCONNECT' }

      New tweet!
      Connect error { [Error: Cannot connect in state "opening"] code: 'EHDBCONNECT' }

      New tweet!
      Connect error { [Error: Cannot connect in state "opening"] code: 'EHDBCONNECT' }

      { [Error: read ECONNRESET] code: 'ECONNRESET', errno: 'ECONNRESET', syscall: 're
      ad' }
      Connect error { [Error: connect ETIMEDOUT] code: 'ETIMEDOUT', errno: 'ETIMEDOUT'
      , syscall: 'connect' }

      Any help will be appreciated Thiago. Did I miss a config somewhere?



      Author's profile photo Kannan Presanna Kumar
      Kannan Presanna Kumar

      Hi Michelle,

      I'm facing with the same proxy issue. Did you find any solution or workaround for this?