Skip to Content

In a set of three tutorial videos the SAP HANA Academy’s Tahir Babar Hussain (aka Bob) 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

Screen Shot 2015-11-24 at 4.47.23 PM.png

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.

Screen Shot 2015-11-24 at 5.54.03 PM.png

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.

Screen Shot 2015-11-24 at 5.56.53 PM.png

Loading Data with Beeline and Building a SAP Lumira Analytic

Screen Shot 2015-11-24 at 4.50.47 PM.png

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.

Screen Shot 2015-11-25 at 9.41.21 AM.png

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.

Screen Shot 2015-11-25 at 9.44.14 AM.png

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.

Screen Shot 2015-11-25 at 9.49.49 AM.png

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.

Screen Shot 2015-11-25 at 10.03.30 AM.png

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.

Screen Shot 2015-11-25 at 10.06.37 AM.png

Appending Data in SAP HANA Vora and Refreshing SAP Lumira Analytics

Screen Shot 2015-11-24 at 4.52.24 PM.png

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.

Screen Shot 2015-11-25 at 10.21.05 AM.png

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.

Screen Shot 2015-11-25 at 10.25.07 AM.png

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.

Screen Shot 2015-11-25 at 10.27.37 AM.png

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.

Screen Shot 2015-11-25 at 10.30.32 AM.png

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.

Screen Shot 2015-11-25 at 10.32.54 AM.png

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.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Sundeep Chalasani

    Hello,

    Trying to Connect SAP Lumira 1.31.6 to the SAP HANA Vora 1.3 hosted through amazon EC2 using the SAP CAL

    1. The Thrift server is up and running on port 8500
    2. The Spark Controller services have been started and I can see the VORA tables registered with spark in the VORA tools in qhich the queries work as expected
    3. Installed the JDBC Simba drivers including the Spark driver

    When trying to connect through SAP Lumira using the JDBC URL from the generic JDBC driver, Lumira just hangs in the “Connecting..” stage forever. The user is a vora user and I validated that the credentials are correct and can’t find any logs on the vora side either.

    Let me know if anyone faced similar issue with Lumira and any ideas if you resolved it.

    (0) 
  2. Florian Morandell

     

    Hello,

    i am currently writing my masterthesis about Sap Vora and facing Problems at connecting SAP Lumira to SAP VORA. I would like to ask if anyone can help me solving these issues.

    I installed SAP Vora Developer Edition 1.3 with SAP CAL in AWS. Also I installed a windows machine in the same Network, Subnetwork and the same security group on AWS.

    In the Settings of the AWS Security Group all Traffic is open for the internal and external IP Adress of Lumira and Vora.

    On the windows machine is SAP Lumira Desktop Version 1.31.7 and the „Generic JDBC datasource Drivers„ installed. Also i created the lumira User in Vora. In the SAP Vora Manager Services i can see that the Thriftserver is running.

    When i try connecting via SAP Lumira-> Query with SQL -> JDBC Driver à

    Username: lumira

    Password: XXXXXX

    JDBC URL: jdbc:spark://<host>:<port>/default;CatalogSchemaSwitch=0;UseNativeQuery=1

    For <host> i tried : internal Vora IP, external Vora IP, hadoopaws, vorahost

    For <port> i tried : 19123, 10000

    JDBC Class : com.simba.spark.jdbc4.Driver

     

    Unfortunately I get the following error:

    Driver error: {Simba}{SparkJDBCDriver}(500164) Error initialized or created transport for authentication: java.netConnectExeption: Connection refused: connect (port XXXXX to adress :ffff: XX.X.XX.XX).

    Does anyone have an idea what the problem is and how to fix it?

    Best regards

    Florian M

     

     

    (0) 

Leave a Reply