R connection to Redshift using AWS driver doesn't work but does work with Postgre driver

Question

I am trying establish a connection to my redshift database after following the example provided by AWS https://blogs.aws.amazon.com/bigdata/post/Tx1G8828SPGX3PK/Connecting-R-with-Amazon-Redshift. However, I get errors when trying to establish the connection using their recommended driver. However, when I use the Postgre driver I can establish a connection to the redshift DB.

AWS says their driver is "optimized for performance and memory management", so I would rather use it. Can someone please review my code below, and let me know if they see something wrong? I suspect that I am not setting the URL up correctly, but not sure what I should be using instead? Thanks in advance for any help.

#' This code attempts to establish a connection to redshift database.  It
#' attempts to establish a connection using the suggested redshift but doesn't
#' work.

## Clear up space and set working directory

#Clear Variables
rm(list=ls(all=TRUE))
gc()

## Libriries for analyis

library(RJDBC)
library(RPostgreSQL)

#Create DBI driver for working with redshift driver directly

# download Amazon Redshift JDBC driver
download.file('http://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC41-1.1.9.1009.jar',
              'RedshiftJDBC41-1.1.9.1009.jar')

# connect to Amazon Redshift using specific driver        
driver_redshift <- JDBC("com.amazon.redshift.jdbc41.Driver",
               "RedshiftJDBC41-1.1.9.1009.jar", identifier.quote="`")

## Using postgre connection that works 

#postgre driver
driver_postgre <- dbDriver("PostgreSQL")

#establish connection
conn_postgre <- dbConnect(driver_postgre, host="nhdev.c6htwjfdocsl.us-west-2.redshift.amazonaws.com",
                 port="5439",dbname="dev", 
                 user="xxxx", password="xxxx")

#list the tables available
tables = dbListTables(conn_postgre)

## Use URL option to establish connection like the example on AWS website

# url <- "<JDBCURL>:<PORT>/<DBNAME>?user=<USER>&password=<PW>
# url <- "jdbc:redshift://demo.ckffhmu2rolb.eu-west-1.redshift.amazonaws.com
# :5439/demo?user=XXX&password=XXX" #useses example from AWS instructions

#url using my redshift database
url <- "jdbc:redshift://nhdev.c6htwjfdocsl.us-west-2.redshift.amazonaws.com
:5439/dev?user=xxxx&password=xxxx"

#attempt connect but gives an error
conn_redshift <- dbConnect(driver_redshift, url)

#gives the following error:
# Error in .jcall(drv@jdrv, "Ljava/sql/Connection;", "connect", as.character(url)[1],  : 
#                   java.sql.SQLException: Error message not found: CONN_GENERAL_ERR. Can't find bundle for base name com.amazon.redshift.core.messages, locale en

## Similier to postgre example that works but doesn't work when using redshift specific driver

#gives an error saying url is missing, but I am not sure which url to use?
conn <- dbConnect(driver_redshift, host="nhdev.c6htwjfdocsl.us-west-2.redshift.amazonaws.com",
                  port="5439",dbname="dev", 
                  user="xxxx", password="xxxx")

# gives the following error: 
#Error in .jcall("java/sql/DriverManager", "Ljava/sql/Connection;", "getConnection",  : 
# argument "url" is missing, with no default

Akshay Hazari Akshay Hazari · Accepted Answer · 2015-11-17T05:07:43

I've done it this way it works for me:

drv <- JDBC("com.amazon.redshift.jdbc41.Driver","PathTO/RedshiftJDBC41-1.1.2.0002.jar")
conn <- dbConnect(drv,"jdbc:redshift://......redshift.amazonaws.com:5439/dev",User,PWD)

The difference I see in yours is that you don't mention the full path to redshift jar in driver_redshift.

Hope it works.

R connection to Redshift using AWS driver doesn't work but does work with Postgre driver

1 Answers