Skip to Content
Author's profile photo shivam shukla

R Integration with SAP HANA

Just thought to share my new found interest in ‘R’. Here I am writing about the successful integration of R (using R-Studio) with SAP HANA that I did recently.
For the beginners it would be really helpful to follow the below steps and a smooth integration with R studio , enjoy accessing HANA artifacts easily like accessing the tables/view information & Consuming procedures , intention is to let the people know the integration & want to share more later on this topic once I am friendly in “R” development fashion –  “still in learning mode” J.
Here are the prerequisites you must have before trying your hands on “R”

HBDCLIENT

HDServer

R-ODBC Package

R Studio

Setup the Data source (in this case SAP HANA DB Server)

If you are already working on HANA Studio then you may have the HANA Studio Installed & HANA Client as well if not then you can install , refer below link.

Eclipse with HANA Tools Plugin is used
(-> same as HANA Studio) + download link for HANA Client Software

https://store.sap.com/sap/cp/ui/resources/store/html/SolutionDetails.html?pid=0000012950&catID=&sap-language=EN&_cp_id=id-1482214928523-0

Once you have installed HANADBClient in your system then you need to setup data source.

Go to -> Control Panel -> Search for data Source (ODBC) -> Set up data sources (ODBC)

You will get the below popup

On Current tab click on Add one popup will appear for Server/Port information

Enter any data source name/any description for server ad then enter server: port –  if you don’t know the server and port name go to your hana studio (SAP HANA Development perspective) and right click on system then click on properties tab -> Additional properties tab -> Host details. After entering these details click on connect to check if the details are correct or not by entering the username/password if all ok then you get a prompt on connect that connection is successful.

Once you are done with above now download the R Studio (https://www.rstudio.com/ ) and R package (https://cran.r-project.org/ )

Now install both the Components once you are done with R Studio installation download the RODBC package (https://cran.r-project.org/web/packages/RODBC/index.html )  goto your R Studio now & all install r Studio package.

Once we have added the RODBC package now we can check on R studio using library function

Now we can go ahead for accessing our HANA database artifacts, establish the database connection using R commands & then execute sqlQuery function for executing sql commands on R.

I created one table in HANA server for billing docs that I am accessing using “R” below is the sample code.

library (‘RODBC’)

ch<-odbcConnect(“data source name”,uid=”test_hana”,pwd=”test12″);

sqlQuery(ch, ‘SELECT * FROM “_SYS_BIC”.”BILLING_DATA” ‘)

result ->

Here is the output from “R”

Another sample for consuming procedure in “R”

I created one procedure which returns Company Code , Accounting doc & fiscal year from BSEG table.

sqlQuery(ch, ‘CALL “ABAPDEMO”.”GETDATA”(10 , ? )’);

Output in “R”


Note –  you can also use hdbuserstore set default for storing username/password for you hana server hence you don’t need to expose that into your connection string , below link elaborates the way of securing your username/password when dealing with ODBC –

https://help.sap.com/saphelp_hanaplatform/helpdata/en/dd/95ac9dbb571014a7d7f0234d762fdb/content.htm

The secure user store is installed with the SAP HANA client package. After you install the SAP HANA client, the hdbuserstore program is located in one for the following directories:

  • /usr/sap/hdbclient (Linux/UNIX)
  • %SystemDrive%\Program Files\sap\hdbclient (Microsoft Windows)

Your suggestions/questions/improvements are most welcome, I will try to share more sample codes I progress in this.

Thank you!

Assigned Tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Narenda Pratap Singh
      Narenda Pratap Singh

      Thank you for sharing Shivam

      Author's profile photo Paras Gupta
      Paras Gupta

      nice blog Shivam.. 🙂

      Author's profile photo Lars Breddemann
      Lars Breddemann

      For an example of how to use the ODBC connections (not just in R but in all programs) in a more secure way, see https://blogs.sap.com/2013/01/23/hana-quick-note-checking-my-connections-and-using-them-securely/ .

      Author's profile photo Former Member
      Former Member

      Thanks for the step by step set up. Can This be possible with Hana db which is on Hana cloud platform?

      -Srivatsan

      Author's profile photo Shivam Shukla
      Shivam Shukla

      Hi ,

       

      it should be , I haven't tried , i will try & get back to you on this.

       

      Thanks,

      Sivam

      Author's profile photo akira jain
      akira jain

      Thanks For Sharing..Nice Information.???

      R is an open source programming language and software environment for statistical computing and graphics. The R language has become very popular among statisticians and data miners for developing statistical software and is widely used for advanced data analysis.

      Author's profile photo Shivam Shukla
      Shivam Shukla

      Hi Akira,

       

      Thank you !

       

      Shivam

      Author's profile photo Former Member
      Former Member

      Hi Shivam,

       

      Can we use same host where R & R serve installed to all Non-prod systems. Such as for Dev, QA & Prod as well.  if yes, is there any system specification for installation.

      Or we need separate R Installation for each system. Please suggest.

       

      regards,

      Prashanth

       

      Author's profile photo Shivam Shukla
      Shivam Shukla

      Hi Prashanth ,

       

      Sorry for the late reply .

      Yes I guess , we can access data of any system from "R", we just need to define Host Details for that particular system while accessing , you don't need any separate installation in each system.

      Thanks,

      Shivam

       

      Author's profile photo Pavankumar Gopinath
      Pavankumar Gopinath

       

      Hi All,

       

      My requirement is to schedule the R Code in HANA studio which picks the data from quandl system and writes it into our HANA DB, Could you please let me know how we can achieve this?

      Author's profile photo David Bertsche
      David Bertsche

      How do you write a dataframe from R back into HANA as a table? This only shows exporting a table from HANA into R.

       

      Author's profile photo Shivam Shukla
      Shivam Shukla

      Hi David,

       

      For sure i will check the possibility , let me know please if you want it in R or i can check this in python , As of now i am a bit untouched from R.

       

      From Python it would be for sure a DML command to be executed on the connection object and we can get the result as true/false once the query get’s executed.

       

      Sorry for this long delay as this is my old SCN account.

      Thanks,

      Shivam

      Author's profile photo David Bertsche
      David Bertsche

      Hi Shivam, thanks for the info -- we managed to find a different solution and have already moved on from this project. --David

      Author's profile photo Shivam Shukla
      Shivam Shukla

      Really Glad to hear this David .

       

      Thanks,

      Shivam

      Author's profile photo Shreya Dubey
      Shreya Dubey

      Hello Shivam,

       

      I have HANA 2 SP04 and I want to use R code.

      Do do the setup, do I also need to install R server first?

       

      Thanks

      Shreya

      Author's profile photo Shivam Shukla
      Shivam Shukla

      Hi Shreya Dubey,

      Sorry for replying late here -  No you dont need to install its a direct way you can execute the R code inside SAP HANA -

       

      https://www.youtube.com/watch?v=ndlFay40M-c

       

       

      Here you go.

       

      Thanks,

      Shivam