R and HANA

Fetching data from HANA to R can be done by using two R packages:

  • RODBC, which uses preconfigured ODBC HANA Data Source
  • RJDBC, which uses HANA JDBC driver

The example code for both approaches is shown below. I have noticed that RODBC doesn’t fetch varchar text fields (for example “CustName” from my calculation view “_SYS_BIC”.”etk.H10XSproject1/VBAK_ScrClcView2″), so I use RJDBC.

#========

library(‘RODBC’)
ch<-odbcConnect(“H10″,uid=”user”,pwd=”***”,DBMSencoding = “utf8”);

sql<-‘select v.”SalesOrg”,v.”SoldToParty”,v.”Quarter”,sum(v.”NetValue”) AS “NetValue” ‘
sql<-paste(sql, ‘from “_SYS_BIC”.”etk.H10XSproject1/VBAK_SCRCLCVIEW3″ v ‘)
sql<-paste(sql, ‘group by v.”SalesOrg”,v.”SoldToParty”,v.”Quarter” ‘)

result <-sqlQuery(ch, sql,stringsAsFactors=FALSE)
print(result)

#========

library(‘RJDBC’)
jdbcDriver <- JDBC(driverClass=”com.sap.db.jdbc.Driver”, classPath=”C:/Program Files/sap/hdbclient/ngdbc.jar”)
jdbcConnection <- dbConnect(jdbcDriver, “jdbc:sap://hanahost:30015/?autocommit=false”,”user”,”password”)

sql<-‘select v.”SalesOrg”,v.”CustName”,v.”Quarter”,sum(v.”NetValue”) ‘
sql<-paste(sql, ‘from “_SYS_BIC”.”etk.H10XSproject1/VBAK_ScrClcView2″ v ‘)
sql<-paste(sql, ‘group by v.”SalesOrg”,v.”CustName”,v.”Quarter” ‘)

result <- dbGetQuery(jdbcConnection, sql)
print(result)

sql<-‘select * from ( select “CustName”,”Quarter”,”SumNetValue”,rank() over(order by “SumNetValue” desc) as rank ‘
sql<-paste(sql, ‘from ( select “CustName”,”Quarter”,sum(“NetValue”) as “SumNetValue” from emiltom.vbak group by “CustName”,”Quarter” )) ‘)
sql<-paste(sql, ‘where rank <= 10 order by rank’)

result <- dbGetQuery(jdbcConnection, sql)
print(result)
dbDisconnect(jdbcConnection)

Oglasi
Ovaj unos je objavljen u Nekategorizirano. Bookmarkirajte stalnu vezu.

Jedan odgovor na R and HANA

Komentiraj

Popunite niže tražene podatke ili kliknite na neku od ikona za prijavu:

WordPress.com Logo

Ovaj komentar pišete koristeći vaš WordPress.com račun. Odjava / Izmijeni )

Twitter picture

Ovaj komentar pišete koristeći vaš Twitter račun. Odjava / Izmijeni )

Facebook slika

Ovaj komentar pišete koristeći vaš Facebook račun. Odjava / Izmijeni )

Google+ photo

Ovaj komentar pišete koristeći vaš Google+ račun. Odjava / Izmijeni )

Spajanje na %s