Translate

Tuesday, 7 July 2015

connecting R to database




There are several ways to do this, but the following are what worked for me.


Connections 



Connecting to a database using ODBC:

N.B. 32-bit ODBC will only work with 32-bit R, so for 64-bit R, 64-bit ODBC needs to be installed

library(RODBC)
  
con <- odbcConnect(dsn = "database server name as recorded in ODBC", uid = username, pwd = password) 

N.B For Microsoft SQL Server, I have been told to use odbcDriverConnect instead of odbcConnect.
  
Connecting to Teradata database using JDBC:

library(RJDBC)

drv <- JDBC("com.teradata.jdbc.TeraDriver", "C:/your path to jar file/terajdbc4.jar; C:/
your path to jar file/tdgssconfig.jar") 
con <- dbConnect(drv, "jdbc:teradata://host ip details e.g. 000.00.000.00/", user = "username", password = "password", port = port e.g. 00)  
 



Connecting to Oracle database:

library(ROracle)

drv <- dbDriver("Oracle")
   
con <- dbConnect(drv, "username", "password", dbname="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host ip details e.g. 000.000.0.000)(PORT= port e.g. 00))(CONNECT_DATA=(SID=sid name)))")  



Working in Database


To run SQL script from R:


sqlQuery(con, your SQL query)

For example, 
sqlQuery(con, "Select * from XYZ where rownum < 10")



To create and write to table:

sqlSave(con, R object name to write, tablename = "schema.table name")

  
To create and write to table without the rownames of the R object:

sqlSave(con, R object name to write, tablename = "schema.table name", rownames=FALSE)

  
To create and write to table by adding to the existing contents of the table:

sqlSave(con, R object name to write, tablename = "schema.table name", append = TRUE)



To disconnect from the database:

odbcClose(con)           #for RODBC

dbDisconnect(con)          #for RJDBC




For Oracle database or using RJDBC:


To run SQL script from R:


dbGetQuery(con, your SQL query)

For example, 
dbGetQuery(con, "Select * from XYZ where rownum < 10")


To read table:

Dat <- dbReadTable(con, "table name")  

To write to table:

dbWriteTable(con, "table name", object name)  


To write to table without the rownames of the R object:

dbWriteTable(con, "table name", object name, row.names = FALSE)  

  
To write to table by replacing the existing contents of the table:

dbWriteTable(con, "table name", object nameoverwrite = TRUE)  


To write to table by adding to the existing contents of the table:

dbWriteTable(con, "table name", object nameappend = TRUE)  

  


Sometime writing a table with date-time field can create an error when time zones are set differently in the R environment and Oracle. The below is the easiest to fix the error.

1. To change time zone of the R environment:

Sys.setenv(TZ="GMT")

  
2. To change time zone of the Oracle:

Sys.setenv(ORA_SDTZ="GMT")

  


To remove/delete a table:

dbRemoveTable(con, "table name", object name)  


To list all the tables:

dbListTables(con, schema = "schema name (if applicable)")  



To list all fields in a table:

dbListFields(con, "table name", schema = "schema name (if applicable)")  



To disconnect from the database:

dbDisconnect(con)









No comments:

Post a Comment