Connect Oracle Or Redshift using R

PREREQUISITES TO CONNECT R WITH ORACLE DW

Go to R-Studio then type the below commands

1.           install.packages("RODBC")

2.           library(RODBC)

Once library command executed successfully go to Go to Start---> Search ODBC. It will show ODBC administrator.Select that and Click on 'Add' then select DataDirect 6.0 Oracle Wire Protocol as show below.

After that it will open ODBC Oracle Wire Protocol Driver setup as shown below.

Then fill the details based on your db details which you can find in you tnsfile or database detils from you team.Sample tns would be like below

  (DESCRIPTION=

                   (enable=broken)

                   (LOAD_BALANCE = YES)

                                  (ADDRESS= (PROTOCOL=TCP) (Host= AA-A-orasvr.db.amazon.com) (Port=20002))

                                  (CONNECT_DATA=(SERVICE_NAME= AA.amazon)

                (SERVER=DEDICATED)

                )

  )

as like below example

After that click on test if you find connection successful then go back to R and type below commands.

 con <- odbcConnect("Oracle_DW", uid="user_id", pwd="password", rows_at_time = 500)

                                                    ODBC datasource name which you saved.

 sqlQuery(con, "select SESSIONTIMEZONE from dual").

Connect redshift using R:

install.packages("RJDBC")

library(RJDBC)

# download Amazon Redshift JDBC driver

download.file('http://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC41-1.1.10.1010.jar','RedshiftJDBC41-1.1.10.1010.jar')

# connect to Amazon Redshift

driver <- JDBC("com.amazon.redshift.jdbc41.Driver", "path_of_the_file/RedshiftJDBC41-1.1.10.1010.jar", identifier.quote="`")

# url <- "<JDBCURL>:<PORT>/<DBNAME>?user=<USER>&password=<PW>

url <- "jdbc:postgresql://host_details:port/dwrsg004?user=user_id&password=password_of_db"

conn <- dbConnect(driver, url)

# get some data from the Redshift table

dbGetQuery(conn, "select count(*) from bin_items")

# this is not a good idea – the table has more than 1000 rows so better store it into data or dataset”

# bin_items <- dbReadTable(conn, "bin_items")

# close connection

dbDisconnect(conn)