Pentaho DI can't connect to AWS Redshift - Amazon Error 100021

4
votes

Referring to Pentaho's Doc, we should be using RedshiftJDBC4.jar instead of version 4.1. I have downloaded the driver and placed it in the lib/ directory. Relaunched spoon.sh and I noticed it is no longer complaining about not able to find the com.amazon.redshift.jdbc4 class driver as I was using the 4.1 driver earlier. However, it still could not establish the connection.

Error connecting to database [aws_redshift] : org.pentaho.di.core.exception.KettleDatabaseException: Error occurred while trying to connect to the database

Error connecting to database: (using class com.amazon.redshift.jdbc4.Driver) Amazon Error setting default driver property values.

Can anyone help on this?

On the flip side, I can connect to my endpoint using SQLWorkbench/J, a SQL client tool.

1
amazon-web-servicesdatabase-connectionpentahoamazon-redshiftpdi
Were you able to solve your issue? I had a similar problem, but now I fixed that. I can help you on that if you still have this problem. – leventunver
No, it is still not resolved. If you can help it'd be great also for the benefit of others. – dat789
I just tried it again by putting RedshiftJDBC4-1.2.1.1001.jar, RedshiftJDBC41-1.2.1.1001.jar, RedshiftJDBC42-1.2.1.1001.jar -- driver versions 4, 4.1, and 4.2 -- in ../data-integration/lib directory. It did not work for each. The error stack I get Driver class 'com.amazon.redshift.jdbc4.Driver' could not be found, make sure the 'Redshift' driver (jar file) is installed. com.amazon.redshift.jdbc4.Driver org.pentaho.di.core.exception.KettleDatabaseException: Error occurred while trying to connect to the database – dat789
Sorry, I just saw your comments. In my case, the problem was the url that I was using. You should remove "jdbc:redshift" part of the url in the beginning when you are using an SQL client besides SQL Workbench. – leventunver

1 Answers

1
votes

Somehow I managed to get it working. It seems that downloading AWS Redshift drivers version 4, 4.1, or 4.2 and placing them in the lib/ directory did not work for me for each version by choosing Redshift as connection type (in Database Connection setup).

Instead, I chose PostgreSQL using JDBC. In host name field, I included the endpoint WITHOUT port number 5439 at the end. So, the endpoint should just end with ...amazonaws.com. Fill in database name, port number of 5439, and username and password. If this did not work, try downloading the latest PostgreSQL JDBC driver and placing it in lib/ directory and try again.