Technical Articles
Connecting R Studio to SAP HANA via JDBC
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.
Thanks Ian, great blog.
For those interested in the topic, there is also a playlist on the SAP HANA Academy about R integration for SAP HANA:
https://www.youtube.com/playlist?list=PLkzo92owKnVwJr2iHJDcHjdSpvKz2aaig
Hi
I am getting an error during connection. I used following code :
I am getting following error. Please take a look & give me some clue.
Thanks Advance
It could be a permission issue. Often C:\ is a protected location.
I would try putting ngdbc.jar is a more standard directory, My Documents\Something, and reference that path.
Hi
Thanks for the answer. That problem solved but I'm getting timeout issue !! How can i get rid of this ?
Have you configured the database tunnel for what seems to be a HANA instance in your SAP cloud trial account? If not, then you won't be able to connect to it via JDBC. Check the documentation for details on that.
And before you ask: HANA studio does that automagically under the covers.
Also: username and password have NO PLACE in your application/R code. Use secure storage for that. Whenever you have to manually hide these details in a screenshot - that's a security bug in your code!
Hi All
I am getting an error during connection. I have used the below code :
I am getting the below error. Kindly help me to fix this issue.
could not find function "JDBC"
Thanks Advance
Try adding this before the jdbcDriver line.
I have also updated the code in the blog post.
Hi!
When trying to connect to a HANA Cloud instance I get the following error
The hanaml R libraries require HANA version2 SP03 or above. Please install an appropriate version and try again
The ngdbc.jar version I have is a feasible one according to SAP Help
Any ideas?
Thank you,
Elisa