SAP HANA and R – Keep shining

Since I discovered Shiny and published my blog A Shiny example – SAP HANA, R and Shiny I always wanted to actually run a Shiny application from SAP HANA Studio, instead of having to call it from RStudio and having to use an ODBC connection.

A couple of days ago…this blog Let R Embrace Data Visualization in HANA Studio gave me the power I need to keep working on this…but of course…life is not that beautiful so I still need to do lots of things in order to get this done…

First…cygwin didn’t worked for me -:(  so I used Xming instead -;)

Now…one thing that it’s really important is to have all the X11 packages loaded into the R Server…so just do this…

Connect to your R Server via Putty and then type “yast” to enter the “Yet another setup tool”. (Make sure you tick the X11 Forwarding)…


Search and install everything related to X11-Devel. Also install/update your Firefox browser (also on yast).

Deleting_R
rm -r R-2.15.0

Then, download the source again…keep in mind that we will need R-2.15.1

Get_R_Again
wget http://cran.r-project.org/src/base/R-2/R-2.15.1.tar.gz

Now…we need support for jpeg images…so let’s download a couple of files…

Getting_support_for_images
wget http://prdownloads.sourceforge.net/libpng/libpng-1.6.3.tar.gz?download wget http://www.ijg.org/files/jpegsrc.v9.tar.gz tar zxf libpng-1.6.3.tar.gz tar zxf jpegsrc.v9.tar.gz mv libpng-1.6.3 R-2.15.1/src/gnuwin32/bitmap/libpng mv jpeg-9 R-2.15.1/src/gnuwin32/bitmap/jpeg-9 cd R-2.15.1/src/gnuwin32/ cp MkRules.dist MkRules.local vi MkRules.local

When you run vi on the file you should comment out the bitmap.dll source directory lines just like in the image (notice that I’m not dealing with TIFF images, as they didn’t worked for me)…


Now, we need to into each folder and compile the libraries…

Compiling_libraries
cd R-2.15.1/src/gnuwin32/bitmap/libpng ./confire make make install cd .. cd jpeg-9 ./configure make make install

When both libraries finished compiling…we can go an compile R -;)

Compiling_R
cd cd R-2.15.1 ./configure --enable-R-shlib --with-readline=no --with-x=yes make clean make make install

As you can see…where using the parameter –with-x=yes to indicate that we want to have X11 into our R installation. As we compiled the JPEG and PNG libraries first…we will have support for this on R as well -;)

For sure…this will take a while…R compilation is a hard task -:P But in the end you should be able to confirm by doing this…

Checking_installation
R capabilities()

Installing_Shiny
install.packages("shiny", dependencies=TRUE)

Easy as cake -:)

But here comes another tricky part…we need to create a new user…why? Because we mostly had a previous user to run the Rserve…that was created before we installed X11…so just create a new one -:)

Creating_new_user
useradd -m login_name passwd login_name

For the X11 to work perfectly…we need to do another thing…

Get_Magic_Cookie
xauth list echo $DISPLAY

This will return us a line that we should copy in a notepad…then…we need to log of and log in again via Putty (with the X11 Forwarding) but this time using our new user…the second line will tell us about the display, so copy that one as well…
Once logged with the new user…do this…

Assign_Magic_Cookie_and_Display
xauth add //Magic_Cookie_from_Notepad// export DISPLAY=localhost:**.* //number get from the $DISPLAY...like 10.0 or 11.0

Now…we’re are complete ready to go…

Start the Rserve server like this…

Start_Rserve
R CMD Rserve --RS-port 6311 --no-save --RS-encoding "utf8"

When our Rserve is up and running…it’s time for SAP HANA to make it’s entrance -;) What I really like about Shiny…is that…in the past you needed to create two files to make it work UI.R and Server.R…right now…Shiny uses the Bootstrap framework so we can create the webpage using just one file…or call it directly from the SAP HANA Studio -;)

Calling_Shiny_from_SAP_HANA_Studio.sql
CREATE TYPE SNVOICE AS TABLE( CARRID CHAR(3), FLDATE CHAR(8), AMOUNT DECIMAL(15,2) ); CREATE TYPE DUMMY AS TABLE( ID INT ); CREATE PROCEDURE GetShiny(IN t_snvoice SNVOICE, OUT t_dummy DUMMY) LANGUAGE RLANG AS BEGIN library("shiny") runApp(list( ui = bootstrapPage( pageWithSidebar( headerPanel("SAP HANA and R using Shiny"), sidebarPanel(selectInput("n","Select Year:",list("2010"="2010","2011"="2011","2012"="2012"))), mainPanel(plotOutput('plot', width="450", height="800px")) )), server = function(input, output) { output$plot <- renderPlot({ year<-paste("",input$n,sep='') t_snvoice$FLDATE<-format(as.Date(as.character(t_snvoice$FLDATE),"%Y%m%d")) snvoice<-subset(t_snvoice,format(as.Date(t_snvoice$FLDATE),"%Y") == year) snvoice_frame<-data.frame(CARRID=snvoice$CARRID,FLDATE=snvoice$FLDATE,AMOUNT=snvoice$AMOUNT) snvoice_agg<-aggregate(AMOUNT~CARRID,data=snvoice_frame,FUN=sum) pct<-round(snvoice_agg$AMOUNT/sum(snvoice_agg$AMOUNT)*100) labels<-paste(snvoice_agg$CARRID," ",pct,"%",sep="") pie(snvoice_agg$AMOUNT,labels=labels) }) } )) END; CREATE PROCEDURE Call_Shiny() LANGUAGE SQLSCRIPT AS BEGIN snvoice = SELECT CARRID, FLDATE, AMOUNT FROM SFLIGHT.SNVOICE WHERE CURRENCY = 'USD'; CALL GetShiny(:snvoice,DUMMY) WITH OVERVIEW; END; CALL Call_Shiny

I’m not going to explain the code, because you should learn some R and Shiny -:P But if you wonder why I have a “dummy” table…it’s mainly because you can’t create an Stored Procedure in R Lang that doesn’t have an OUT parameter…so…does nothing but helps to run the code 🙂

When we call the script or the procedure Call_Shiny, the X11 from our Server is going to call Firefox which is going to appear on our desktop like this…

Before we finish…keep in mind that this approach is really slow…our R Server will send the information via X11 Forwarding to our machine, and will render the Firefox browser…also…we have a timer…so after so many seconds…we will have a Timeout…of course this can be configured, but for Performance purposes…we should limit the time the communication between our SAP HANA and R servers…

Hope you like this blog -:) and see you on the next one -;)

Greetings,

Blag.