Currently Being Moderated

In my previous HANA and R blogs, I have been forced to create .csv files from HANA and read them on R...an easy but also boring procedure...specially if your R report is supposed to be run on a regular basis...having to create an .csv file every time you need to run your report it's not a nice thing...

 

After spending some time reading and researching R...I finally came to a library that can read data from any relational database and being HANA, ODBC capable, the work is just a piece of cake -;)

 

For this examples, we must install two libraries: RODBC and Plotrix and create the DSN connection as shown here...

 

HANA already provides us a driver, so we're cool

HANA_Meets_R_02.png

 

Here we're going to "Add..." a new "System DSN"

 

HANA_Meets_R_01.png

 

Assign a name for the "Data Source Name", "Description" is optional and "Server:Port" should be of course filled.

 

Now...we're ready to go to our HANA studio an create a table and a stored procedure...

 

HANA_Meets_R_03.png

 

 

GetTicketsByYearMonth

CREATE PROCEDURE GetTicketsByYearMonth(IN var_year NVARCHAR(4),IN var_month NVARCHAR(2))

LANGUAGE SQLSCRIPT AS BEGIN

select count(bookid), carridfrom sflight.snvoice

where year(fldate) = VAR_YEAR

  and month(fldate) = VAR_MONTH

group by carrid

into TICKETS_BY_YEAR_MONTH;

END;

 

 

After we run our Stored Procedure...we have all the information in the table...Ok...only two fields...today was a hard day...I'm tired -:P

 

HANA_Meets_R_04.png

 

Finally...we can code some R! First, we're going to create a Fan Plot (The Plotix library is needed for that one) and then a Bar Plot...I used the same code for both, so just replace the comment on one by the other one and run it again...I know...I'm being lazy again...but at least I'm not reinveting the wheel -;) Two codes with only 1 different line? No thanks...

 

 

Fan_Plot_Graph.R

library("plotrix")

library("RODBC")

ch<-odbcConnect("HANA",uid="P075400",pwd="***")

res<-sqlFetch(ch,"P075400.TICKETS_BY_YEAR_MONTH")

fan.plot(res$TICKETS,labels=res$CARRIER,

         main="Tickets for December 2011")

#barplot(res$TICKETS,names.arg=res$CARRIER)

odbcClose(ch)

 

The code is very simple...we call the libraries we need, we stablish a communication to our DSN, we fetch the data from the table, we create the graphics and finally we close the connection.

 

And here come the graphics...

 

image

image

I will keep investigating on this way to connect HANA and R...more blogs should be on the way -;)

Comments

Actions

Filter Blog

By author:
By date:
By tag: