SAP HANA talks to Twitter for Prediction !!

This blog is intended to focus on how you can connect with social media like for eg. Twitter and work on text analysis or predictive analysis with the social media data. Use cases could be the Football World Cup, EPL, Cricket IPL T20 and many predictive apps. 😉

Twitter has become one the most widely used platform for its trending data using the # (hash tags).

I came across a use case where we worked on the predictive analysis with the tweets. Shared here are some of the key points on “How we go about getting this connectivity between HANA & Twitter” and “How we read these tweets, store in HANA DB and take it further for predictive analysis”.

In this example I have used SAP HANA XSJS. You can use the JAVA API’s as well.

With HANA XSJS I tried with two solutions 1) UI request -> XSJS -> Twitter -> Response back to UI

2) Use of XS Jobs for getting Tweets + Separate Service call for UI rendering.

Ok now let’s go ahead and see how it’s done.

With Twitter’s new authentication mechanism below steps are necessary for having a successful connection.

HttpDest would look like this:

description = "Twitter";
host = "api.twitter.com";
port = 443;
useProxy = false;
proxyHost = "proxy";
proxyPort = 8080;
authType = none;
useSSL = true;

Another important step would be to setup the TRUST STORE from XS ADMIN tool :

Outbound httpS with HANA XS (part 2) – set up the trust relation

Twitter offers applications to issue authenticated requests on behalf of the application itself (as opposed to a specific user).

We need to create a twitter application (Manage Aps) in https://dev.twitter.com/. The settings tab/OAuth tool would give us the Consumer Key and secret Key which is very important for setting up the Request authorization header.

Critical steps is to get the bearer token ready:

1. URL encode the consumer key to RFC 1738 – xvz1evFS4wEEPTGEFPHBog
2. The consumer secret to RFC 1738 – L8qq9PZyRg6ieKGEKhZolGC0vJWLw8iEJ88DR
3. Concatenate the encoded consumer key, a colon character “:”, and the encoded consumer secret into a single string.

        xvz1evFS4wEEPTGEFPHBog:L8qq9PZyRg6ieKGEKhZolGC0vJWLw8iEJ88DR

   4. Base64 encode the string from the previous step –  V2RlM0d0VFUFRHRUZQSEJvZzpMOHFxOVBaeVJnNmll==

There you have the BASIC token. We need to get a BEARER token from the BASIC token by issuing a POST:

https://dev.twitter.com/oauth/reference/post/oauth2/token

Response:

HTTP/1.1 200 OK

Status: 200 OK

Content-Type: application/json; charset=utf-8

Content-Encoding: gzip

Content-Length: 140

{“token_type”:”bearer“,”access_token”:”AAAA%2FAAA%3DAAAAAAAA“}

The BEARER token is the gateway for using in your XSJS service to talk to twitter. For checking your twitter URL request format you can use the Twitter Developer Console.

Response:

XSJS code snippet :

var dest = $.net.http.readDestination("Playground", "twitter");
var client = new $.net.http.Client();
var url_suffix = "/1.1/search/tweets.json?q=#SAPHANA&count=1";
var req = new $.web.WebRequest($.net.http.GET, url_suffix); //MAIN URL
req.headers.set("Authorization","Bearer AAAAAAAA");
client.request(req, dest);

If all is setup correctly we would get a Response that gives you the Tweet text in Statuses ARRAY..

var response = client.getResponse();
var body = response.body.asString();
myTweets = JSON.parse(body);

myTweets.statuses[index].text ===> Tweet data

Once you have the tweets array, you can loop it, process and store them in our SAP HANA DB for further predictive analysis 🙂

var conn = $.db.getConnection();
var pstmt = conn.prepareStatement(insert_hana_tweet);
pstmt.setString(1, myTweets.statuses[index].id_str);
pstmt.setString(1, myTweets.statuses[index].text);                              
pstmt.setString(2, myTweets.statuses[index].created_at);
pstmt.setString(3, myTweets.statuses[index].user.screen_name);
pstmt.execute();
conn.commit();
pstmt.close();
conn.close();

In my use case I have used “SEARCH” tweets but you have many other options eg. Read Messages, retweets, followers & so on.

The request parameters can be used based on your use case eg. Most recent tweet (result_type=recent), number of tweets to fetch (count=10), fetch all tweets after a specific tweet id(since_id).

I have the XS service in a XS JOB which would check for tweets and store them in HANA DB.

{
"description": "Insert Tweets",
    "action": "Playground:job_twitter.xsjs::collectTweet",
"schedules": [
        {
            "description": "Tweets",
"xscron": "* * * * * * *"
        }
    ]
}

Have a look at the developer guide to understand the various options available for scheduling… XSCRON parameter is used for setting the time and duration of the job.

Talk to Twitter and keep trending 🙂 Hope this post was helpful ! 🙂

Avinash Raju

SAP HANA Consultant