Friday, November 27, 2015

RPostgresql : how to pass dynamic parameter to dbGetQuery statement

RPostgresql : R and PostgreSQL Database

Working with RPostgreSQL package

How to pass dynamic / runtime parameter to dbGetQuery in RPostgrSQL ?
#use stri_paste to form a query and pass it into dbGetQuery icd = 'A09'

qry <- stri_paste("SELECT * FROM visualisation.ipd_disease_datamart WHERE icd ='", icd, "'",collapse="")

rs1 <- dbGetQuery(con, qry)
 Error in postgresqlNewConnection(drv, ...) : 
  RS-DBI driver: (cannot allocate a new connection -- 
maximum of 16 connections already opened)
drv <- dbDriver("PostgreSQL")

con <- dbConnect(drv, dbname="DBName", host="",port=5432,user="yes",password="yes")
close the connection. max 16 connection can able to establish from R to PostgreSQL, if exceeds this limit, will throw error.
##list all the connections

## Closes the connection

## Frees all the resources on the driver
#OR on.exit(dbUnloadDriver(drv), add = TRUE)

How to close/drop all the connection Postgresql session.?

We can terminate the PostgreSQL connection using "pg_terminate_backend" SQL command.
In my case I was open up 16 connection using RPostgreSQL unfortunately forget to release them.
So I ended up with Max. connection exceed limit.
SELECT pg_terminate_backend( FROM pg_stat_activity WHERE client_addr = '' and pid > 20613 AND pid <> pg_backend_pid();
In above query, pg_stat_activity will return list of all the active connection.
I have terminating only the connection from R session which made from the (client_addr) IP

Next :

Friday, November 20, 2015

Working with Association in R : arules and arulesViz package

working with association in R : arules package
previous post - working with RHadoop

working with Association in R using arules and arulesViz packages

when I try to visualize the top five rules,
Error in as.double(y) : 
  cannot coerce type 'S4' to vector of type 'double'
load the library "arulesViz" into R session
During the load the library I got error,
Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]) : namespace "lattice" 0.20-24 is already loaded, but >= 0.20.27 is required Error: package or namespace load failed for "arulesViz"
error says that i have outed package that need to be upgraded. to know the installed package list
inst = packageStatus()$inst

inst[inst$Status != "ok", c("Package", "Version", "Status")]

#will list out all the package with installed version( not current version)


#list out the installed version and current version of the package.

#then restart the R session will solve the error.

detach_package("lattice", TRUE)
#will unload the package with out restarting R session
finallly got the output of the plot

Next :