Text Analysis in SAP HANA integrate with Twitter API

Update the configurations

In the purpose to maintain the configurations easily, we put all the required information in a single interface and it is mandatory for you update it with your own account or settings before you can connect to either HANA or Twitter.

1. Open the file Configurations.java in your project. Basically, there are 4 category of setting you can override:

• Network Proxy Settings: The proxy host and port, set the HAS_PROXY as false if you do not need to

  use proxy

• HANA Connection Settings: Replace the HANA URL with your own HANA host and port, user,

  password and the schema where you created your table

• Twitter Authentication Settings: Replace with your own authentication information from your twitter

  application as described in the prerequisites

• Search Term: We will search the twitter based on the search term “startup” and we want to know what

  people were talking around the startups in twitter. You can always replace it with your own term if you are interested in other topics

Test Connection to Twitter

Once have the twitter authentication maintained correctly in the previous step. You can open TwitterConnection.java and run it. You will see the message “Connection to Twitter Successfully!” following with your twitter user id in the console as the screenshot shows below.

Test Connection to SAP HANA

Now let us open the file HDBConnection.java and run it. You will see the message “Connection to HANA Successfully!” in the console as the screenshot shows below. Check the Configurations.java if you encountering any issue.

The data access object TweetDAO is the single point to communicate with HANA from java, take a look how the source code looks like and you will know the SQL statement and how to use the jdbc library.

Invoke Twitter API and save the tweets into HANA

Now it’s time to the do the real stuff. Open the file SearchTweets.java and run it, which will search the tweets based on the search term we specified in the Configurations.java and everything we got will saved to HANA table. You will see the messages in the console indicate the tweets have been inserted to HANA successfully like the screenshot shows:

After that, you can run the data preview in HANA studio and see the contents of the table TWEETS in your schema like this:

Run text analysis in HANA

Now we already have the tweets stored in the HANA table. The next step, we are going to run the text analysis to see what people are talking around the “startup” in twitter.

To run the text analysis, the only thing we need to do is creating a fulltext index for the column of the table we want to analysis and HANA will process the linguistic analysis, entity extraction, stemming for us and save the results in a generated table $TA_YOUR_INDEX_NAME at the same schema. After that, you can build views on top of the table and leverage all existing analysis tools around HANA to do the visualization even the predictive analysis.

1. Copy the SQL statement from the CreateFullTextIndex.sql and execute it in SQL console:

— Replace the Scheme with your own Schema! —
SET SCHEMA “I045664”;
DROP FULLTEXT INDEX “TWEETS_FTI”;

Create FullText Index “TWEETS_FTI” On “TWEETS”(“TEXT”) TEXT ANALYSIS ON CONFIGURATION ‘EXTRACTION_CORE’;

2. Do you believe the text analysis is already done by HANA? Yes, it is. Now you know how simple it is! You will be able to find a generated table $TA_TWEETS_FTI in your schema. The structure of the table looks like this, which is the standardized format for the results of text analysis:

3. And here is the data preview of the $TA table, you will see the Tokens extracted from the tweets and the number of occurrence and entity type of each token.

4. Based on this, you can use the knowledge you learned in the previous modelling exercises and use the table to build a view if you want. Here, we just go to the Analysis tab and build a tag cloud like this: