Twitter Paradise: Smart Data Integration : Twitter Replication Pt 3 of 3 [SPS09]

Ah Twitter, that blissful paradise for bad grammar and teen fanatics.   In this video by the SAP HANA Academy, Tahir Hussain Babar aka Bob shows us how to replicate Twitter into SAP HANA.

What you are going to do

In this video you are going to take the view of the twitter adapter or data source made in the last video and replicate it into SAP HANA using the Web IDE.  You are going to create a replication task that will filter the objects that are coming into your SAP HANA system

How you are going to do it

The Twitter adapter takes a few minutes to connect to your tweets.  You take your various keys within the Twitter API and you put them into remote data source which creates a virtual view in SAP HANA connecting to your Tweets via dev.twitter.com.

Look out for

The problem is that this is just a view, the data is not in HANA and consists of all Tweets.

Historic Moment

Bob describes making his first tweet using this test account, he also demonstrates that after refreshing this tweet called imaginatively enough “This is a test” it appears at the top with all the relevant columns concerning the source, whether it was replied to, if it was a re-Tweet etc.

Filtering Tweet data in SAP HANA

The focus of this video is the creation of a replication task which will filter the data to be stored.    This simple example which filters for the Tweets that Bob has made can be easily customised according to your requirements.

Step 1 – Launch the SAP Web IDE

Login as the developer user using the link below, you may have a different instance number. Remember when you created the user you should have allocated it the developer role.  You need to create yourself a file with the specific extension.

Bob then describes how to make a new file in the package for DEV01.

The file extension must be as shown below.

Step 2 – Using the UI

Bob then outlines the key features of the UI that has been generated.  In this example Bob selects DEV01 as the Target Schema and Virtual Table Schema.  He wants to capture all the updates so he deselects the box for Initial load only. He also wants to clearly identify the table as virtual so he prefixes it with VT_

At this point you can Add Objects.  Bob then adds the table from the Remote Data Source called Status which as you may recall from the previous video contains all your tweets.

Step 3 – Filtering

Once the table has been added you can see the actual columns in that table.    Bob then demonstrates filtering by ScreenName and emphasises that this is the only way you can accomplish this task.

Bob uses the filter tab to type in the command below then clicks on Save and Activate.

Step 4 – Invoking the Procedure

Now the task and been saved and activated you need to invoke the procedure which can be accessed in SAP HANA Studio.  However, Bob demonstrates another method outlined below to access the Catalog and invoke the procedure from there.

All you need to do now is execute the procedure you have just called as shown below.

You will be able to see if the procedure has been successfully completed in the dark grey area under the call statement.

Step 5 – Demonstrating the Task is Completed

Bob returns to his list of tables and highlights the two objects that have created during the replication task.  The source table ended contains VT_ as part of the name whilst the output table includes the word Status.

Bob the shows how the source table still contains all Tweets but the output table only contains the filtered Tweets which Bob created. He does this in both cases by right clicking and selecting Generate Select, then Execute.  In typical thorough style Bob tests this by adding another Tweet which of course works.  He goes on to explain how the SQL commands actually work.

The first line is creating the procedure using SQL.  The rest of the code establishes what you are going to do, creates a replication task but while this task is being created you don’t want to miss any incoming data.  The queue ensures that this does not happen.

As always Bob concludes by outlining what’s coming next.  So here there is was folks, how you can replicate twitter into SAP HANA academy. #schooled .  Did I mention the veritable gods of the modern age, the ‘twitter famous’. Funnily enough, a website dedicated entirely to talking to yourself whilst thousands can hear is an excellent form of marketing and connecting people. Twitter can be held responsible for many things, the creation of a whole subculture and slang and also, improving your business.