4

When using the JDBC function to connect R to amazon redshift (I'm using windows 10) I get the following error:

Error in .jfindClass(as.character(driverClass)[1]) : class not found

The code I'm running is

install.packages("rJava")
install.packages("RJDBC")
library(rJava)
library(RJDBC)

download.file('http://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC41-1.1.13.1013.jar','RedshiftJDBC41-1.1.13.1013.jar')
driver <- JDBC("com.amazon.redshift.jdbc41.Driver", "RedshiftJDBC41-1.1.13.1013.jar", identifier.quote = "`")

To get more infos about the error I runned this:

 .jclassLoader()$setDebug(1L)

and the full error trace is:

RJavaClassLoader: added 'RedshiftJDBC41-1.1.13.1013.jar' to the URL class path loader
RJavaClassLoader: adding Java archive file 'RedshiftJDBC41-1.1.13.1013.jar' to the internal class path
RJavaClassLoader: added 'C:/Users/user/Documents/R/win-library/3.2/RJDBC/java/RJDBC.jar' to the URL class path loader
RJavaClassLoader: adding Java archive file 'C:/Users/user/Documents/R/win-library/3.2/RJDBC/java/RJDBC.jar' to the internal class path
RJavaClassLoader@33909752.findClass(com.amazon.redshift.jdbc41.Driver)
 - URL loader did not find it: java.lang.ClassNotFoundException: com.amazon.redshift.jdbc41.Driver
RJavaClassLoader.findClass("com.amazon.redshift.jdbc41.Driver")
 - trying class path "C:\Users\user\Documents\R\win-library\3.2\rJava\java"
 Directory, can get 'C:\Users\user\Documents\R\win-library\3.2\rJava\java\com\amazon\redshift\jdbc41\Driver.class'? NO
 - trying class path "C:\Users\user\Documents\R\win-library\3.2\RJDBC\java\RJDBC.jar"
JAR file, can get 'com/amazon/redshift/jdbc41/Driver'? NO
- trying class path "C:\Users\user\Desktop\Pricing"
   Directory, can get 'C:\Users\user\Desktop\Pricing\com\amazon\redshift\jdbc41\Driver.class'? NO
- trying class path "C:\Users\user\Desktop\Pricing\RedshiftJDBC41-1.1.9.1009.jar"
JAR file, can get 'com/amazon/redshift/jdbc41/Driver'? NO
- trying class path "RedshiftJDBC41-1.1.9.1009.jar"
JAR file, can get 'com/amazon/redshift/jdbc41/Driver'? NO
- trying class path "." Directory, can get '.\com\amazon\redshift\jdbc41\Driver.class'? NO
- trying class path "C:\RedshiftJDBC41-1.1.9.1009.jar"
JAR file, can get 'com/amazon/redshift/jdbc41/Driver'? NO
- trying class path "RedshiftJDBC41-1.1.13.1013.jar"
JAR file, can get 'com/amazon/redshift/jdbc41/Driver'? NO
>> ClassNotFoundException

If you have any idea on something that could help it would be awesome! Thanks

Viola
  • 43
  • 1
  • 4

3 Answers3

6

Exact same issue affected me. For some reason, download.file() grabbed a corrupted .jar. Dev in our group found the following fix:

  1. omit:

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

  2. Instead download the .jar directly from http://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html

  3. Copy the .jar to your working directory.

  4. Run remainder of code.

lowndrul
  • 3,715
  • 7
  • 36
  • 54
  • 2
    Exactly the same issue! I used `download` function from `downloader` package to solve the issue. – discipulus Mar 22 '17 at 06:57
  • 2
    This is the answer. Other answers don't address the problem. Something about `download.file` is corrupting the .jar file. – giraffehere Jun 22 '17 at 14:58
  • [Amazon's official tutorial](https://aws.amazon.com/blogs/big-data/connecting-r-with-amazon-redshift/) uses `download.file()`. This answer really is the only thing that solved it. @giraffehere is right. AMAZON: terrible way to start out a tutorial. – Jarad Mar 10 '18 at 18:31
  • Hope more people upvote this as the accepted answer directs people away from a dedicated package. Also, since another downloading package works, I'm pretty sure it is not on Amazon's court and the issue is with R's download.file() command. – AChervony May 09 '18 at 23:30
2

my recommendation for you is to connect redshift with RPostgreSQL package.

install.packages("RPostgreSQL")
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
conn <-dbConnect(drv,host='host link',port='5439',dbname='dbname',user='xxx',password='yyy')
dbSendQuery(conn,"insert your query")
user3600910
  • 2,839
  • 4
  • 22
  • 36
1

Hi I am referring to your piece of code

install.packages("rJava") install.packages("RJDBC") library(rJava) library(RJDBC)

download.file('http://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC41-1.1.13.1013.jar','RedshiftJDBC41-1.1.13.1013.jar') driver <- JDBC("com.amazon.redshift.jdbc41.Driver", "RedshiftJDBC41-1.1.13.1013.jar", identifier.quote = "`")

The place where you write the driver object, the syntax is

driver <- JDBC("CLASSNAME","PATH WHERE REDSHIFT JAR FILE IS DOWNLOADED')

I had the same error, Once I realized I had to just put the right path, I could connect. My second argument to JDBC was 'C:\Users\person.name\Downloads\RedshiftJDBC41-1.1.13.1013.jar'

Hope this helps

Ajay Kumar
  • 71
  • 1
  • 3
  • 11