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.
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)