El blog tecnológico de Blag.

CREATE COLUMN TABLE TWITTER_HASHTAGS(
USERNAME NVARCHAR(10)
) WITH SCHEMA FLEXIBILITY;

CREATE TYPE T_COL_NAMES AS TABLE(
COL_NAMES NVARCHAR(1000)
);
 
CREATE TYPE T_COL_VALUES AS TABLE(
COL_VALUES NVARCHAR(1000)
);

CREATE PROCEDURE GET_HASHTAGS(IN twittername TWITTER_USERS,IN first_hash FIRST_HASH,
                              OUT out_col_names T_COL_NAMES, OUT out_col_values T_COL_VALUES)
LANGUAGE RLANG AS
BEGIN
UserName = twittername$USERNAME
hashline = first_hash$HASH_LINE
hashvalues = first_hash$HASH_VALUES
 
Get_Twitter<-function(p_source,p_pattern){
  datalines = grep(p_pattern,web_page,value=TRUE)
  getexpr = function(s,g)substring(s,g,g+attr(g,'match.length')-1)
  g_list = gregexpr(p_pattern,datalines)
  matches = mapply(getexpr,datalines,g_list)
  result = gsub(p_pattern,'\\1',matches)
  names(result) = NULL
  return(result)
}
 
Get_Hashtags<-function(p_source){
  check<-!length(grep('\\"([^,\\"]+)\\"', as.character(p_source)))
  if(!check){
    mypattern = '\\"([^,\\"]+)\\"'
    datalines = grep(mypattern,p_source,value=TRUE)
    getexpr = function(s,g)substring(s,g,g+attr(g,'match.length')-1)
    g_list = gregexpr(mypattern,datalines)
    matches = mapply(getexpr,datalines,g_list)
    result = gsub(mypattern,'\\1',matches)
    names(result) = NULL
    return(result)
  }else{
    result<-p_source
    return(result)
  }
}
 
url<-paste("http://api.twitter.com/1/statuses/user_timeline.xml?count=200&screen_name=",UserName,sep="")
mypattern = '<text>([^<]*)</text>'
web_page<-readLines(url)
tweets<-Get_Twitter(web_page,mypattern)
mypattern = '[^\\&]#(\\.?\\w+)'
hash_list<-Get_Twitter(tweets,mypattern)
hashtags<-sapply(hash_list,Get_Hashtags)
hashtags<-as.vector(unlist(hashtags))
hashtags<-toupper(hashtags)
 
dt.hashtags<-data.frame(UserName,hashtags)
tab.hashtags<-table(dt.hashtags)
dt.hashtags<-as.data.frame.matrix(tab.hashtags)
hashtags_names<-names(dt.hashtags)
hashtags_names<-gsub("^\\.",'',hashtags_names)
 
if(length(hashline>=1)){
          hash_line<-gsub("^(\\w)+\\,",'',hashline)
          hash_line<-unlist(strsplit(hash_line, split=","))
          hash_values<-gsub("^(\\'+\\w+\\')+\\,",'',hashvalues)
          hash_values<-as.numeric(unlist(strsplit(hash_values, split=",")))
          hash_frame<-data.frame(names=hash_line,values=hash_values)
          hash_frame["values"]<-0
 
          Col_Names<-""
          Col_Values<-""
 
          for(i in 1:length(hashtags_names)){
                      Col_Names<-paste(Col_Names,hashtags_names[i],sep=",")
                      Col_Values<-paste(Col_Values,dt.hashtags[,i],sep=",")
          }
 
           Col_Names<-gsub("^\\,|\\.",'',Col_Names)
          Col_Values<-gsub("^\\,|\\.",'',Col_Values)
          Col_Names<-unlist(strsplit(Col_Names, split=","))
          Col_Values<-as.numeric(unlist(strsplit(Col_Values, split=",")))
          new_hash_frame<-data.frame(names=Col_Names,values=Col_Values)
          new_hash_frame<-rbind(hash_frame,new_hash_frame)
          new_hash_frame<-aggregate(values ~ names, FUN = "sum", data = new_hash_frame)
          new_hash_names<-new_hash_frame$names
          new_hash_values<-new_hash_frame$values
 
          Col_Names<-"USERNAME"
          Col_Values<-paste("'",UserName,"'",sep="")
 
           for(i in 1:length(new_hash_names)){
                      Col_Names<-paste(Col_Names,new_hash_names[i],sep=",")
                      Col_Values<-paste(Col_Values,new_hash_values[i],sep=",")
          }
}else{
          Col_Names<-"USERNAME"
          Col_Values<-paste("'",UserName,"'",sep="")
 
          for(i in 1:length(hashtags_names)){
                      Col_Names<-paste(Col_Names,hashtags_names[i],sep=",")
                      Col_Values<-paste(Col_Values,dt.hashtags[,i],sep=",")
          }
}
 
col_names<-gsub("^\\,\\.?",'',Col_Names)
col_values<-gsub("^\\,",'',Col_Values)
 
out_col_names<-data.frame(COL_NAMES=col_names)
out_col_values<-data.frame(COL_VALUES=col_values)
END;

CREATE PROCEDURE SAVE_HASHTAGS(IN in_col_names T_COL_NAMES, IN in_col_values T_COL_VALUES)
LANGUAGE SQLSCRIPT AS
v_select VARCHAR(2000);
v_col_names_char NVARCHAR(1000);
v_col_values_char NVARCHAR(1000);
CURSOR c_cursor1 FOR
SELECT COL_NAMES FROM :in_col_names;
CURSOR c_cursor2 FOR
SELECT COL_VALUES FROM :in_col_values;
BEGIN
                    OPEN c_cursor1;
                    FETCH c_cursor1 into v_col_names_char;
                    CLOSE c_cursor1;
                    OPEN c_cursor2;
                    FETCH c_cursor2 into v_col_values_char;
                    CLOSE c_cursor2;
                    DELETE FROM FIRST_HASH;
                    INSERT INTO FIRST_HASH VALUES(:v_col_names_char,:v_col_values_char);
                    v_select := 'INSERT INTO TWITTER_HASHTAGS (' || v_col_names_char || ') 
                                VALUES (' || v_col_values_char || ')';
                    EXEC v_select;
END;

CREATE PROCEDURE GET_TWITTER_USERS(UserName NVARCHAR(10))
LANGUAGE SQLSCRIPT AS
BEGIN
          Twitter_Users = SELECT USERNAME FROM TWITTER_USERS WHERE USERNAME = :UserName;
          First_Hash = SELECT HASH_LINE, HASH_VALUES FROM FIRST_HASH;
          CALL GET_HASHTAGS(:Twitter_Users,:First_Hash,T_COL_NAMES,T_COL_VALUES);
          CALL SAVE_HASHTAGS(:T_COL_NAMES,:T_COL_VALUES);
END;

CALL GET_TWITTER_USERS('Blag');
CALL GET_TWITTER_USERS('Schmerdy');
CALL GET_TWITTER_USERS('ggread');

library("Rserve")
Rserve()

Open CMD
C:\>ikmvc -out:Rcaller.dll -target:module Rcaller.jar

RserveCli.RConnection conn

#if DEFINED PBDOTNET then
@System.Net.IPAddress ipadd
byte ip[4] = {127,0,0,1}
ipadd = create @System.Net.IPAddress(ip)
@System.Int32 port
port = create System.Int32
port = 6311
conn = create RserveCli.RConnection(ipadd,port,"","")
#end if

double varA[2,2], varB[2,2]
varA[1,1] = double(Array_A_1.Text)
varA[1,2] = double(Array_A_3.Text)
varA[2,1] = double(Array_A_2.Text)
varA[2,2] = double(Array_A_4.Text)
varB[1,1] = double(Array_B_1.Text)
varB[1,2] = double(Array_B_3.Text)
varB[2,1] = double(Array_B_2.Text)
varB[2,2] = double(Array_B_4.Text)
 
#if DEFINED PBDOTNET then
::conn["A"] = RserveCli.Sexp.Make(varA)
::conn["B"] = RserveCli.Sexp.Make(varB)
#end if

#if DEFINED PBDOTNET then
txtResult.Text = ::conn["A+B"].ToString()
#end if

#if DEFINED PBDOTNET then
txtResult.Text = ::conn["A*B"].ToString()
#end if

#if DEFINED PBDOTNET then
RserveCli.Sexp varC = ::conn["min(c(A,B))"]
txtResult.Text = varC.ToString()
#end if

#if DEFINED PBDOTNET then
RserveCli.Sexp varC = ::conn["max(c(A,B))"]
txtResult.Text = varC.ToString()
#end if

#if DEFINED PBDOTNET then
RserveCli.Sexp varC = ::conn["mean(c(A,B))"]
txtResult.Text = varC.ToString()
#end if

#if DEFINED PBDOTNET then
RserveCli.Sexp varC = ::conn["summary(c(A,B))"]
string varS = "Min: " + varC[0].ToString() + " / 1st Qu: " + varC[2].ToString() + " / Median: " &
                                + varC[2].ToString() + " / Mean: " + varC[3].ToString() + " / 3rd Qu: " &
                                + varC[4].ToString() + " / Max: " + varC[5].ToString()
txtResult.Text = varS
#end if

open(w_window)

--Creamos un TYPE T_FLIGHTS y tomamos la informacion de las tablas SPFLI y STICKET.
 
DROP TYPE T_FLIGHTS;
CREATE TYPE T_FLIGHTS AS TABLE (
CARRID NVARCHAR(3),
CUSTOMID NVARCHAR(8),
CITYFROM NVARCHAR(20),
CITYTO NVARCHAR(20)
);
 
--Creamos un TYPE FLIGHT_MODEL_T y una tabla FLIGHT_MODEL para guardar el modelo

--en la base de datos
 
DROP TYPE FLIGHT_MODEL_T;
CREATE TYPE FLIGHT_MODEL_T AS TABLE (
ID INTEGER,
DESCRIPTION VARCHAR(255),
MODEL BLOB
);
 
DROP TABLE FLIGHT_MODEL;
CREATE COLUMN TABLE FLIGHT_MODEL (
ID INTEGER,
DESCRIPTION VARCHAR(255),
MODEL BLOB
);
 
--Este procedimiento R va a recibir la informacion de T_FLIGHTS, 

--crear una tabla con el campo FLIGHT_NAME que a contener la concatenacion de
--los campos CARRID, CITYFROM and CITYTO. ejm: AA-NEW YORK-SAN FRANCISCO.
--Vamos a convertir la tabla en un data.frame, asi que todos los valores iguales en FLIGHT_NAME
--van a ser sumarizados.
--Utilizamos la funcion subset(), vamos deshacernos de todos los FLIGHT_NAME's que tienen una frequencia 
--menor o igual que 0.
--Vamos a utilizar la funcion nrow() para contar todas las ocurrencias de FLIGHT_NAME y multiplicarla por 
--10 (Lo guardamos en f_rows)
--Vamos a utilizar la funcion sum() para sumar todas las frequencias y luego dividirlas por f_rows 
--(Lo guardamos en f_sum)
--Vamos a utilizar la funcion mapply() para dividir cada una las frecuencias por f_sum
--Vamos a utilizar la funcion order() para ordenar por FLIGHT_NAME
--Vamos a utilizar la funcion colnames() para asignar nombres a nuestro data.frame
--Vamos a utilizar la funcion lm() para generar una Regresion Lineal basada en el FLIGHT_NAME 
--y su frecuencia
--Finalmente, vamos a utilizar la funcion personalizada generateRobjColumn() para guardar el resultado 
--del modelo en el buffer.

DROP PROCEDURE FLIGHT_TRAIN_PROC;
CREATE PROCEDURE FLIGHT_TRAIN_PROC (IN traininput "T_FLIGHTS", OUT modelresult FLIGHT_MODEL_T)
LANGUAGE RLANG AS
BEGIN
generateRobjColumn <- function(...){
          result <- as.data.frame(cbind(
                    lapply(
                              list(...),
                              function(x) if (is.null(x)) NULL else serialize(x, NULL)
                    )
          ))
          names(result) <- NULL
          names(result[[1]]) <- NULL
          result
}
tab<-table(FLIGHT_NAME=paste(traininput$CARRID,traininput$CITYFROM,traininput$CITYTO,sep="-"))
df<-data.frame(tab)
ss<-subset(df,(df$Freq>0))
freq<-ss$Freq
f_rows<-(nrow(ss)) * 10
fsum<-sum(freq) / f_rows
ss$Freq<-mapply("/",ss$Freq, fsum)
flights<-ss[order(ss$FLIGHT_NAME),]
colnames(flights)<-c("FLIGHT_NAME","FREQUENCY")
lmModel<-lm(FREQUENCY ~ FLIGHT_NAME,data=flights)
modelresult<-data.frame(
ID=c(1),
DESCRIPTION=c("Flight Model"),
MODEL=generateRobjColumn(lmModel)
)
END;
 
--Este procedimiento SQLSCRIPT va a tomar toda la informacion necesaria de las tablas SPFLI y STICKET 
--y la va a asignar a flights
--Vamos a llamar al procedimiento R FLIGHT_TRAIN_PROC
--Vamos a hacer un INSERT para finalmente grabar el buffer en la base de datos
 
DROP PROCEDURE POPULATE_FLIGHTS;
CREATE PROCEDURE POPULATE_FLIGHTS ()
LANGUAGE SQLSCRIPT AS
BEGIN
flights = SELECT SPFLI.CARRID, CUSTOMID, CITYFROM, CITYTO
             FROM SFLIGHT.SPFLI INNER JOIN SFLIGHT.STICKET
             ON SPFLI.CARRID = STICKET.CARRID
             AND SPFLI.CONNID = STICKET.CONNID;
CALL FLIGHT_TRAIN_PROC(:flights, FLIGHT_MODEL_T);
INSERT INTO "FLIGHT_MODEL" SELECT * FROM :FLIGHT_MODEL_T;
END;
 
CALL POPULATE_FLIGHTS();

--Vamos a crear un TYPE T_PREDICTED_FLIGHTS y una tabla PREDICTED_FLIGHTS para guardar la informacion

--del actual numero de vuelos y el estimado (de acuerdo con nuestra prediccion).
 
DROP TYPE T_PREDICTED_FLIGHTS;
CREATE TYPE T_PREDICTED_FLIGHTS AS TABLE (
CUSTOMID NVARCHAR(8),
FLIGHT_NAME NVARCHAR(60),
FREQUENCY INTEGER,
PREDICTED INTEGER
);
 
DROP TABLE PREDICTED_FLIGHTS;
CREATE TABLE PREDICTED_FLIGHTS (
CUSTOMID NVARCHAR(8),
FLIGHT_NAME NVARCHAR(60),
FREQUENCY INTEGER,
PREDICTED INTEGER
);
 
--En este procedimiento de R, vamos a recibir los vuelos para un cliente en particular, el modelo almacenado 

--en la base de datos y vamos a retornar el resultado para que pueda ser
--guardado en nuestra tabla PREDICTED_FLIGHTS.
--Vamos a utilizar la funcion unserialize() para extraer el modelo.
--Vamos a crear una tabla conteniendo un campo llamado FLIGHT_NAME que sera la concatenacion de los campos

--CARRID, CITYFROM and CITYTO.
--ejm: AA-NEW YORK-SAN FRANCISCO. y tambien el CUSTOMID
--Vamos a convertir la tabla en un data.frame, asi que todos los valores FLIGHT_NAME iguales van a ser

--sumarizados.
--Vamos a utilizar la funcion colnames() para asignar nombres a nuestro data.frame
--Vamos a utilizar la funcion nrow() para obtener el numero de registros en nuestro data.frame (Se guarda en dfrows)
--Vamos a utilizar la funcion rep() para repetir el valor de CUSTOMID del primer registro dfrows veces
--Vamos a utilizar la funcion predict() para predecir la cantidad de vuelos basados en nuestro model (obtenido de 

--la base de datos) y la nueva informacion que hemos obtenido
--Finalmente, vamos a crear un data.frame conteniendo toda la informacion que deberia ser guardada en nuestra

--tabla PREDICTED_FLIGHTS

DROP PROCEDURE USE_FLIGHT;
CREATE PROCEDURE USE_FLIGHT(IN flights T_FLIGHTS, IN modeltbl FLIGHT_MODEL_T, OUT out_flights T_PREDICTED_FLIGHTS)
LANGUAGE RLANG AS
BEGIN
lmModel<-unserialize(modeltbl$MODEL[[1]])
tab<-table(FLIGHT_NAME=paste(flights$CARRID,flights$CITYFROM,flights$CITYTO,sep="-"),CUSTOMID=flights$CUSTOMID)
df<-data.frame(tab)
colnames(df)<-c("FLIGHT_NAME","CUSTOMID","FREQUENCY")
dfrows<-nrow(df)
customid<-rep(df$CUSTOMID[1],dfrows)
prediction=predict(lmModel,df,interval="none")
out_flights<-data.frame(CUSTOMID=customid,FLIGHT_NAME=df$FLIGHT_NAME,FREQUENCY=df$FREQUENCY,PREDICTED=prediction)
END;
 
--Este procedimiento SQLSCRIPT seleccionara informacion de la tabla FLIGHT_MODEL y la guardara en la variable

--flight_model
--Vamos a seleccionar toda la informacion necesaria de las tablas SPFLI y STICKET basada en el customer ID
--Vamos a llamar al procedimiento R USE_FLIGHT y este nos retornara PREDICTED_FLIGHTS que vamos a  

--guardar en la base de datos
 
DROP PROCEDURE GET_FLIGHTS;
CREATE PROCEDURE GET_FLIGHTS(IN customId NVARCHAR(8))
LANGUAGE SQLSCRIPT AS
BEGIN
flight_model = SELECT * FROM FLIGHT_MODEL;
out_flights = SELECT SPFLI.CARRID, CUSTOMID, CITYFROM, CITYTO FROM SFLIGHT.SPFLI INNER JOIN SFLIGHT.STICKET
                   ON SPFLI.CARRID = STICKET.CARRID AND SPFLI.CONNID = STICKET.CONNID
                   WHERE CUSTOMID = :customId;
CALL USE_FLIGHT(:out_flights, :flight_model, PREDICTED_FLIGHTS);
INSERT INTO "PREDICTED_FLIGHTS" SELECT * FROM :PREDICTED_FLIGHTS;
END;

CALL GET_FLIGHTS('00000156');
CALL GET_FLIGHTS('00002078');
CALL GET_FLIGHTS('00002463');

library("shiny")
library("RODBC")
 
ch<-odbcConnect("HANA_TK",uid="SYSTEM",pwd="manager")
odbcQuery(ch,"SELECT table_name from SYS.CS_TABLES_ where schema_name = 'SFLIGHT'")
tables<-sqlGetResults(ch)
odbcClose(ch)
 
shinyUI(pageWithSidebar(
 
  headerPanel("SAP HANA and R using Shiny"),
 
  sidebarPanel(
    selectInput("Table", "Choose a table:",
                choices = tables$TABLE_NAME),
    numericInput("Records", "Number of Records to view:", 10)
  ),
 
  mainPanel(
    tableOutput("view")
  )
))

library("shiny")
library("RODBC")
 
shinyServer(function(input, output) {
 
  output$view <- reactiveTable(function() {
    ch<-odbcConnect("HANA_TK",uid="SYSTEM",pwd="manager")
    schema_table<-paste("SFLIGHT.",input$Table,sep="")
    query<-paste("SELECT TOP",input$Records,"* FROM",schema_table)
    odbcQuery(ch,query)
    result<-sqlGetResults(ch)
    odbcClose(ch)
 
    head(result, n = input$Records)
  })
})

library(shiny)
setwd("C:/Blag/R_Scripts")
runApp("Shiny/SAP_HANA_R")

$ sudo apt-get install tasksel
$ sudo tasksel install lamp-server

$ sudo apt-get install unixODBC unixODBC-dev

$ sudo vi odbc.ini
 
[HDB]
driver = /usr/sap/hdbclient32/libodbcHDB32.so
ServerNode = hana_server:30115

$ isql -v HDB SYSTEM manager

$ ldd /usr/sap/hdbclient32/libodbcHDB32.so

<?php
$conn = odbc_connect("HDB","SYSTEM","manager",SQL_CUR_USE_ODBC);
if (!($conn))
{
          echo "<p>Connection to DB via ODBC failed: ";
        echo odbc_errormsg ($conn);
        echo "</p>\n";
}
else{
          if(isset($_POST["CARRID"]) == false){
                  $sql = "SELECT CARRID, CARRNAME FROM SFLIGHT.SCARR WHERE MANDT = 300";
               $rs = odbc_exec($conn,$sql);
               print("<DIV ALIGN='CENTER'>");
               print("<H1>SAP HANA from PHP</H1>");
               print("<FORM NAME='Get_Data' ACTION='$_SERVER[PHP_SELF]' METHOD='POST'>");
               print("<SELECT NAME='CARRID'>");
               while($row = odbc_fetch_array($rs)){
                     $carrid = $row["CARRID"];
                     $carrname = $row["CARRNAME"];
                     print("<OPTION VALUE='$carrid'>$carrname");
               }
        print("</SELECT>");
        print("<INPUT TYPE='SUBMIT' VALUE='Get Data'>");
        print("</FORM>");
        print("</DIV>");
        }
        else{
                  $carrid_param = $_POST["CARRID"];
                $sql = "SELECT * FROM \"_SYS_BIC\".\"blag/AV_FLIGHTS\"
                        WHERE CARRID = '$carrid_param'";
                $rs = odbc_exec($conn,$sql);
                print("<DIV ALIGN='CENTER'><TABLE BORDER=1>");
                print("<TR><TH>MANDT</TH><TH>CARRID</TH><TH>CONNID</TH>
                       <TH>COUNTRYFR</TH><TH>CITYFROM</TH>
                       <TH>AIRPFROM</TH><TH>COUNTRYTO</TH>
                       <TH>CARRNAME</TH><TH>DISTANCE</TH></TR>");
                while($row = odbc_fetch_array($rs)){
                          $mandt = $row["MANDT"];
                $carrid = $row["CARRID"];
                $connid = $row["CONNID"];
                $countryfr = $row["COUNTRYFR"];
                $cityfrom = $row["CITYFROM"];
                $airpfrom = $row["AIRPFROM"];
                $countryto = $row["COUNTRYTO"];
                $carrname = $row["CARRNAME"];
                $distance = $row["DISTANCE"];
                print("<TR><TD>$mandt</TD><TD>$carrid</TD>
                         <TD>$connid</TD><TD>$countryfr</TD>
                         <TD>$cityfrom</TD><TD>$airpfrom</TD>
                         <TD>$countryto</TD><TD>$carrname</TD>
                         <TD>$distance</TD></TR>");
               }
               print("</TABLE>");
               print("<A HREF='PHP_HANA.php'>Go Back</A></DIV>");
          }
}
?>