When SAP HANA met R – Bring home your graphics
A couple of days ago, I started to think about SAP HANA and R on Amazon Web Services…as far as I know, graphics can get generated using this kind of integration because the graphic will get generated on the server and could not make the trip back into HANA Studio…so I kept thinking as said to myself…”Wouldn’t it be a good idea to use a Linux command to send my email the graphics generated in the R server?”…I had a deal for sure…
I spend a couple of days trying to install X11 on my SUSE box…until I discovered…that it’s a headless server and doesn’t provide any support for X11…that really bring me down as I tried to hard…anyway…as nothing can stop my ideas from becoming some real…I thought of using PDF generation as it doesn’t need X11 at all…but…today…after doing some more research…I discovered that the Cairo library supports image creation without the need of X11…
If you haven’t installed SAP HANA and R on Amazon Web Services, read this… When SAP HANA met R – First kiss.
Let’s take a look at what we need to do 😉
| Install Cairo |
|---|
| zypper install cairo-devel |
| Install Cairo library on R |
|---|
|
>R >install.packages(“Cairo”) |
| Start the Rserve Server |
|---|
| R CMD Rserve –RS-port 6311 –no-save –RS-encoding “utf8” |
Using the same user you used for starting the Rserver Server, create a folder and change it’s permissions…
| Header 1 |
|---|
| >chmod 777 Blag |
Now, we can move to HANA Studio.
We need to first create a table called TICKETS_BY_YEAR_MONTH.
Then, we can create this nice script with some SQLScript and R commands…
| Create_and_Send_Graphics |
|---|
|
drop procedure GetTicketsByYearMonth; drop procedure Generate_Graphic; drop procedure Get_Tickets;
CREATE PROCEDURE GetTicketsByYearMonth(IN var_year NVARCHAR(4),IN var_month NVARCHAR(2)) LANGUAGE SQLSCRIPT AS BEGIN select count(bookid), carrid from sflight.snvoice where year(fldate) = VAR_YEAR and month(fldate) = VAR_MONTH group by carrid into TICKETS_BY_YEAR_MONTH; END;
CREATE PROCEDURE Generate_Graphic(IN tickets_year TICKETS_BY_YEAR_MONTH, OUT result TICKETS_BY_YEAR_MONTH) LANGUAGE RLANG AS BEGIN setwd(“/Blag”) library(“Cairo”) tickets=as.integer(tickets_year$TICKETS) carriers=as.character(tickets_year$CARRIERS) Cairo(600,600,file=”Tickets.png”,type=”png”,bg=”white”) barplot(tickets,names.arg=carriers,main=”Tickets for December 2011″) dev.off() command<-“uuencode Tickets.png Tickets.png | mail -s ‘Tickets December Report’ atejada@gmail.com“ system(command,intern=TRUE) result<-data.frame(TICKETS=tickets,CARRIERS=carriers) END; CREATE PROCEDURE Get_Tickets() LANGUAGE SQLSCRIPT AS BEGIN CALL GetTicketsByYearMonth(‘2011′,’12’); Tickets = SELECT * FROM TICKETS_BY_YEAR_MONTH; CALL Generate_Graphic(:Tickets,TICKETS_BY_YEAR_MONTH); END;
CALL Get_Tickets(); |
When we execute this, a couple of things are going to happen…
- We’re going to get the amount of tickets per airline and per year and month. We’re going to save this info in a table.
- We’re going to read this information, create a graphic and save it as an .png
- We’re going to send this graphic to ourselves by email.
After execution, we’re going to see a nice email…
Cool, huh? Now, we can create graphics on SAP HANA and R on Amazon Web Services 😘
Hi Alvaro,
I have read that HANA will be integrated with ECC and we can access it using ABAP like we do now.
So, is it worth learning sql script and r scripting?
thanks,
Arun
Arun:
Simply put...can you create Stored Procedures on ABAP? No...can you do Statistical Analysis and Graphics on ABAP? No...then...yes...it's for sure worth learning SQLScript and R -;)
Remember, the more weapons you have, the better you're going to stand...
Greetings,
Blag.
Is the SAP HANA and R package in Amazon Web Server and SAP Hana Studio in linux box (i.e. SUSE box)?
Or
SAP HANA and R package and SAP Hana Studio installed in same SUSE Linux?
Is RODBC package for R, still needed for this linux box?
Do we need SAP Hana Studio and/or SAP Hana client to use R for SAP Hana?
I like to see something in SAP Hana for Mac OS X.
Noli
Noli:
Both SAP HANA and R are running on Amazon Web Services, but on different boxes...R is on SUSE Linux Server and SAP HANA...well...a Linux box but I haven't really check which one it is...if you install both on the same box, then it's not going to work...
The installation is detailed here: http://scn.sap.com/community/developer-center/hana/blog/2012/05/21/when-sap-hana-met-r--first-kiss
RODBC is not needed for this kind of integration...
Yes, we need both Studio and Client, as the R code is going to be written inside the SAP HANA Studio as you can see in the example...
SAP HANA for Mac OS X haven't been released yet...I don't have any information on the pipeline...but...as soon as it gets available, we're going to post it to SCN...
Greetings,
Blag.
Hi Blag,
Thanks for clarifying these things.
I saw in this blog http://allthingsr.blogspot.com.au/
that he is using D3 with Sencha Touch for iPad for visualization of SAP Hana. Is SAP Hana live i.e. on-line in this case? It seems it is hosted in AWS.
However, he has 2 D3 examples using .csv and .json files from SAP Hana results. I think they are also hosted and data is also hosted in AWS, probably outside the SAP Hana server?.
They reason, I am asking these questions are, I am interested in Hybrid apps using SAP Hana (e.g. results from data mining and or live queries from SAP Hana). I have created a Hybrid app for iOS using Cartodb (Cartodb.com).
Here are the screenshots of the cartodb
https://picasaweb.google.com/116847891529748214201/LeafletCartoDBProtectedPlanetIPhone
Here are some of screenshots for D3 and g.Raphael which I intended for SAP Hana mobile hybrid apps using PhoneGap.
https://picasaweb.google.com/116847891529748214201
/D3AndGRaphaelForSAPHanaHybridMobile
Thanks.
Noli
Noli:
I really have no clue...you should ask Jitender as he's the one who wrote that blog...I have never tried to integrate SAP HANA with mobile devices (Otherwise I would have blog about it)...
Greetings,
Blag.