Blag's bag of rants: SAP HANA and R (The way of the widget)

sábado, 3 de noviembre de 2012

SAP HANA and R (The way of the widget)

A real developer never stops learning that's a quote I always love to repeat...because it applies to my life...you can know a lot of things but there's always something new to learn, or to re-learn. That's why a couple of days ago I start reading wxPython in Action, a really nice book that show us how to use wxWidgets in Python. While I was reading the book...a thought came into my mind...a lot of programming languages have implementation of wxWidgets like Ruby, Python, Euphoria and Perl, to name a few...but what about R? Does R have something like that? Of course...it is named gWidgets.

After that, I started to read the documentation, search for examples in the web and suddenly I knew that I wanted to build an small application and integrate it with SAP HANA.

For those who doesn't know yet, my good friend and team mate Juergen Schmerder posted on Twitter one of the biggest news (at least for me)...

This means that I can use ODBC in my blogs or in my personal project without worrying about SAP not supporting it...really big news...you can read more here
SAP HANA Opens SQL Interfaces for Custom Developed Apps.

So, here's the app I came with (Keep in mind that you will need the latest version of R (2.15.2) available from CRAN.
You will need the following libraries as well RODBC, gWidgets, cairoDevice and gWidgetsRGTk2.

gWidgets_SAP_HANA.R
library("RODBC") require ( gWidgets ) options ( guiToolkit="RGtk2" ) require( cairoDevice ) ch<-odbcConnect("HANA",uid="SYSTEM",pwd="manager") airline_values<-c() result<-sqlQuery(ch,"SELECT DISTINCT CARRID FROM SFLIGHT.SPFLI") for(i in 1:nrow(result)){ airline_values<-append(airline_values, as.character(result$CARRID[i])) } selected_airline<-"" distances<-data.frame(CONNID="", DISTANCE="", stringsAsFactors=FALSE) g_distance<-c() g_connid<-c() window<-gwindow("gWidgets and SAP HANA", visible=FALSE) group<-ggroup(cont=window, expand=TRUE, horiz=FALSE) lyt<-glayout(cont=group, spacing=2) lyt[1, 1:15, anchor=c(-1,0)]<-(search<-glabel("Carrier", cont=lyt)) lyt[1, 18:34, anchor=c(-1,0)]<-(airlines<-gcombobox(c("", airline_values), cont=lyt)) lyt[1, 38:42, anchor=c(-1,0)]<-(button<-gbutton("Submit", cont=lyt)) lyt[3:200, 1:200, anchor=c(-1,0)]<-(notebook<-gnotebook(cont=lyt)) group1<-ggroup(cont=notebook, label="Table") group2<-ggroup(cont=notebook, label="Graphic") plot_device<-ggraphics(cont=group2) table<-gtable(distances,cont=group1,expand=TRUE) addHandlerClicked(button, handler=function(h, ...){ value<-"" query<-"SELECT CONNID, SUM(DISTANCE) AS DISTANCE FROM SFLIGHT.SPFLI WHERE CARRID =" value<-paste(value,"'",svalue(airlines),"'", sep="") query<-paste(query, value , sep=" ") query<-paste(query, "GROUP BY CONNID", sep=" ") distances<-sqlQuery(ch, query) table[]<-distances barplot(distances$DISTANCE, names.arg=distances$CONNID) }) visible(window)<-TRUE

gWidgets_SAP_HANA.R

library("RODBC")
require ( gWidgets )
options ( guiToolkit="RGtk2" )
require( cairoDevice )

ch<-odbcConnect("HANA",uid="SYSTEM",pwd="manager")
airline_values<-c()
result<-sqlQuery(ch,"SELECT DISTINCT CARRID FROM SFLIGHT.SPFLI")
for(i in 1:nrow(result)){
 airline_values<-append(airline_values, as.character(result$CARRID[i]))
}

selected_airline<-""
distances<-data.frame(CONNID="", DISTANCE="", stringsAsFactors=FALSE)
g_distance<-c()
g_connid<-c()

window<-gwindow("gWidgets and SAP HANA", visible=FALSE)
group<-ggroup(cont=window, expand=TRUE, horiz=FALSE)
lyt<-glayout(cont=group, spacing=2)
lyt[1, 1:15, anchor=c(-1,0)]<-(search<-glabel("Carrier", cont=lyt))
lyt[1, 18:34, anchor=c(-1,0)]<-(airlines<-gcombobox(c("", airline_values), cont=lyt))
lyt[1, 38:42, anchor=c(-1,0)]<-(button<-gbutton("Submit", cont=lyt))
lyt[3:200, 1:200, anchor=c(-1,0)]<-(notebook<-gnotebook(cont=lyt))

group1<-ggroup(cont=notebook, label="Table")
group2<-ggroup(cont=notebook, label="Graphic")
plot_device<-ggraphics(cont=group2)
table<-gtable(distances,cont=group1,expand=TRUE)

addHandlerClicked(button, handler=function(h, ...){
  value<-""
  query<-"SELECT CONNID, SUM(DISTANCE) AS DISTANCE FROM SFLIGHT.SPFLI WHERE CARRID ="
  value<-paste(value,"'",svalue(airlines),"'", sep="")
  query<-paste(query, value , sep=" ")
  query<-paste(query, "GROUP BY CONNID", sep=" ")
  distances<-sqlQuery(ch, query)
  table[]<-distances
  barplot(distances$DISTANCE, names.arg=distances$CONNID)
})

visible(window)<-TRUE

Basically, what we are doing is to create a window container, that it's going hold up a group, which is going to hold up a layout, which is going to hold up a lable, a combobox, a button and a notebook (tabbed panel). The notebook will hold up two groups which are going to contain a table and a graphic output.
When we start the application, we're going to load the CARRID values from SPFLI into the combobox, then after choosing a value and pressing the button, we're going to build a query that will return all the CONNIDs along with a sum of the DISTANCE field. That information will be shown in both the table and the graphic output (using a Barplot).

Let's see how it looks when running the app.

For some reason, the last tab is always shown first and I haven't found a way to get rid of that behaviour.

As we're just running the app, we show the table with an empty structure.

The combobox is filled with the CARRID values.

We choose "AA" and show the values on the table. Now we can switch tabs and the generated graphic.

Now, just to demonstrate that this really works, we're going to choose another carrier, let's say "LH".

Let's see the graphic now.

It works just fine.

So, what do you think? For the user perspective dealing with a gWidgets Application is was much better than dealing with command line Application, no?

Greetings,

Blag.

No hay comentarios:

Publicar un comentario