Connecting R Studio to SAP HANA via JDBC

[Ian_Henry]

I don't claim to be a data scientist, but I have setup, configured and exploited SAP HANA, Predictive Analytics and a number of other tools before.  R is another great tool to have in your kitbag and it can easily be connected to HANA by either JDBC or ODBC.

What's Needed

• R Desktop


• R Studio


• HANA JDBC Driver


• R Code to open the connection

Download & Install R Desktop
https://cran.rstudio.com

Download & Install R Studio
https://www.rstudio.com/products/rstudio/download3/

Download & Install the HANA JDBC Driver
The only file you need is ngdbc.jar, this is can be found as part of the SAP HANA Client and is available for Windows, Linux and Mac.

SAP ONE Support Launchpad - Software Download - HANA Client

Official documentation for the JDBC driver can be found here
Connect to SAP HANA via JDBC

Pasted below is a code snippet to establish the connection in R Studio.

# Things you may need to change:

# - classPath (This needs to Point to the HANA Client JDBC Driver)

# - jdbcDriver (This should remain the same with the HANA Client JDBC driver)

# - hdbuserstore Key, this is the preferred way to connect securely to HANA

# - SAP HANA Host & port name after jdbc:sap://

# - username (HANA DB User)

# - password (HANA DB User Password)

# - dbGetQuery (Change the Select Query, here I selected a 10% Sample of the CENSUS table in my own schema)



if (!require("RJDBC")) {

  install.packages("RJDBC",repos="http://cran.rstudio.com/")

}



library("RJDBC")

                                    

jdbcDriver <- JDBC(driverClass="com.sap.db.jdbc.Driver",  

                  classPath=/Applications/sap/hdbclient/ngdbc.jar")



jdbcConnection <- dbConnect(jdbcDriver,  "jdbc:sap:", key="SDI_MONSOON")



jdbcConnection <- dbConnect(jdbcDriver,

                  "jdbc:sap://ukhana.mo.sap.corp:30015/?autocommit=false"

                  ,"username"

                  ,"password")



result <- dbGetQuery(jdbcConnection, "select * from CENSUS TABLESAMPLE SYSTEM (10)")

print(result)



dbDisconnect(jdbcConnection)

Below is the code in my R Studio environment


Below is the output of the SQL query inside R from SAP HANA.