[SAP HANA Academy] Learn How to Connect SAP Lumira to SAP HANA Vora

[Former Member]

In a set of three tutorial videos the SAP HANA Academy's tahirhussain.babar details how to connect SAP Lumira to SAP HANA Vora.

All of the files and code used throughout the series are available for free on GitHub.

Overview and Connecting with the SAP Thrift Server

In the series' first video Bob provides a brief overview of what he's attempting to achieve with regards to a SAP Lumira analytic. Then Bob shows how to install all of the relevant drivers within SAP Lumira and how to connect to the SAP HANA Vora system with SAP Lumira.

First, open SAP Lumira and navigate to file and then preferences. Go to the SQL Drivers page and install the Generic JDBC data source drivers. This series assumes that you're using at least version 1.29 of SAP Lumira. With Generic JDBC driver selected click on install driver and then navigate to the Spark JDBC folder. Select all of the lib folders before clicking open and then done. Finally restart SAP Lumira.

Currently, Bob has a SAP HANA Vora instance running on a five node cluster. In the PuTTY for his master node Bob logs in as the Vora user. Then he navigates to his Vora bin folder and then starts the SAP thrift server.

To connect SAP Lumira to SAP HANA Vora go to new in SAP Lumira and choose query with SQL. Choose Generic JDBC datasource and click next. As of late November 2015 we don't need a user name and password so Bob enters X in both text boxes. For the JDBC URL Bob enters a URL with port 10000 as the thrift sever is running on port 10000. Bob has created a Hadoop entry on AWS that is referencing a certain IP Address in an ini file. You will also need to include a pair of switches that are depicted below.

The class value is com.simba.spark.jdbc4.Driver. Then hit connect. Of course you won't be able to see any objects as we have yet to load any data into our SAP HANA Vora system.

Loading Data with Beeline and Building a SAP Lumira Analytic

Next in the second video Bob examines how to use Beeline, a JDBC tool. Bob will show how to create a table and how to load data to HDFS using Beeline. Also, Bob will demonstrate how to build a simple analytics in SAP Lumira using that recently loaded data.

In PuTTY Bob navigates to the home folder of Vora and inserts a new CSV file that lists seven of the richest Football clubs in the Europe. Bob then runs the below command to put the CSV file into HDFS.

Next Bob starts up Beeline. Beeline is a tool that enables JDBC connectivity to a variety of sources including the SAP thrift server. Bob runs the below command to connect to the thrift server.

Then Bob enters the below syntax to create a table for his richest Football clubs data. The table has five columns that include Club string (team name), Revenue double, MatchDay double, Broadcasting double and Commercial double.

The table that Bob just created will be alive as long as the thrift sever is alive.

Back in SAP Lumira the Football clubs table is now visible in the connection that Bob established in the previous video. To preview the data Bob enters the select * command shown below. This is the same command that one would run in PuTTY to view the table's data.

Now Bob creates a report in Lumira from that data preview. Bob adds a new measure for MatchDay and constructs a simple stacked bar chart plotting his club dimension against a combination of the Broadcasting, Commercial and MatchDay measures.

Appending Data in SAP HANA Vora and Refreshing SAP Lumira Analytics

In the third and final tutorial video in the series Bob details how to append data in SAP HANA Vora in order for those changes to be subsequently reflected in the SAP Lumira analytic.

Back in PuTTY Bob stops and then restarts his SAP thrift sever. Now the tables that you've perviously created will no longer exist on the thrift sever but they do exist in Zookeeper. So back in Beeline Bob runs the below command, that specifics each of his Zookeeper severs, to reregister his Football club table back in HDFS.

Next Bob inserts a pair of new CSV files into his home Vora folder to demonstrate that you can append multiple files at a time in SAP HANA Vora. Then Bob runs the commands shown below to put both of those new files into HDFS.

Back in SAP Lumira if you refresh the analytic it won't show any of the new data because it has yet to be appended into the SAP HANA Vora table. To append the data Bob connects to his HDFS system with Beeline and runs the below command.

Now when Bob refreshes his analytic in SAP Lumira the Broadcasting, Commercial and MatchDay revenue for a set of additional Football clubs will be depicted.

Finally, Bob appends his even more Football club data into the HDFS table. He is now able to use SAP Lumira to preform analysis on 20 of the richest Football clubs in Europe.

For more SAP HANA Vora tutorial videos please check out the playlist

SAP HANA Academy - Over 1,200 free tutorial videos on SAP HANA, SAP Analytics and the SAP HANA Cloud Platform.

Follow us on Twitter @saphanaacademy and connect with us on LinkedIn.