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).
With that ready...we can keep going -;)
If you had R installed already...please delete it...as easy as this...
| 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()
|
Now...it's time to install Shiny -8)
| 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="100%", 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...
We can choose between 2010, 2011 and 2012...every time we choose a new value, the graphic will be automatically updated...
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.
