0xDBE and Redshift
Would like to know what youre experiences have been with these.
In the past have used Postgresql driver with limited success.
In the meantime decided to use latest Amazon own driver 1.1.6 , after upgrading JDK on 0xDBE to 1.8u40
First had to solve what seems a 0xDBE bug to me : after adding the driver and wanting to set com.amazon.redshift.jdbc41.Driver as the driver class, 0xDBE always saves AbstractDriver in the settings which drove me crazy. Had to find the xml file under ~/Library (I'm on a Mac), force it and then things got better.
But only slightly: can connect, and see pg_catalog and public under dev database, but no other schemas and I can't see the objects I create..
Have you been more successful ?
Please sign in to leave a comment.
I should have asked the question longer ago...
All sorted out:
- So, I think there is a problem and you need to edit databaseDrivers.xml to be able to force com.amazon.redshift.jdbc41.Driver, but after editing to something like:
<driver id="java.sql.Driver" name="Redshift" driver- dialect="PostgreSQL">
<url-template name="default" template="jdbc:redshift://{host}:{port}/{database}" />
<option name="auto-commit" value="true" />
<option name="auto-sync" value="true" />
<library>
<url>file://$USER_HOME$/Library/Preferences/0xDBE10/RedshiftJDBC41-1.1.6.1006.jar</url>
</library>
</driver>
What you need to make sure is:
- Use the URL notation, so that you have the username and password fields
- Check Schemas and Tables tab on Properties because for whatever reason they were unchecked by default, which means no obect was there once I connected.
So far, everything perfect.
Hello Ricardo,
What 0xDBE version do you use? Actually IDE should detect driver classes from library jar file and allow to change it via driver popup https://youtrack.jetbrains.com/issue/DBE-1142#comment=27-1025147 . Doesn't it work for you?
The dropdown worked for me, but I had to try it about 4 times before it finally worked. Now im getting the error java.lang.RuntimeException: com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: column "ctid" does not exist in t; Here's a post from DB navigator on how this issue was resolved there. https://youtrack.jetbrains.com/issue/DBE-458#
Hi Paulo!
Please, share what does 'Test Connection' button report?
Database: PostgresSQL/08.00.0002
Driver name: RedshiftJDBC
Driver Version: 01.01.07.1007
JDBC version: 4.1
Case Sensitivity: Mixed (quoted: EXACT)
Connection successful
I'm attempting to use DataGrip (formerly 0xDBE) with Redshift as well, but getting a different error.
Amazon JDBC Driver: 1.1.9.1009 (JDBC 4.1)
Datagrip version 1.0.1
Test Connection yields: (url modified for privacy)
Connection to Redshift Driver - jdbc:redshift://blahblahblah.redshift.amazonaws.com:5439/mydatabase failed: Exception in thread "main" java.lang.InstantiationException
at sun.reflect.InstantiationExceptionConstructorAccessorImpl.newInstance(InstantiationExceptionConstructorAccessorImpl.java:48)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at java.lang.Class.newInstance(Class.java:442)
at com.intellij.database.remote.RemoteJdbcServer.main(RemoteJdbcServer.java:15)
I believe I'm using JDK 6, since /System/Library/Frameworks/JavaVM.framework/Versions/CurrentJDK points to 1.6.0.jdk.
If I change my databaseDrivers.xml to use com.amazon.redshift.jdbc41.Driver (instead of the Abstract), doing a Test Connection sits on "Connecting ..." for a while before timing out.
{{EDIT}} Ya'know, it might be a firewall issue, since I'm doing this at work. I'll attempt to do the same at home and update this accordingly.
It looks like the abstract driver you are using is not correct one: similar exception was reported when this driver is used: https://youtrack.jetbrains.com/issue/DBE-1933
Yes it looks like it judging by the timeout error.
Btw, the Java DataGrip uses to run you could see in About dialog. See also http://blog.jetbrains.com/idea/2015/05/intellij-idea-14-1-4-eap-141-1192-is-available/ on how to change it.
I tried Datagrip at home, where firewalls wouldn't be an issue, and I still got a timeout issue. It's very likely I've got Redshift improperly configured.
But it turns out I don't need to use RedShift (and Datagrip) at all. I was going to use Redshift to load my DynamoDB, but I found a better way to load the info into Dynamo directly.
I'll continue to use Datagrip for my normal work-related stuff, though. It's worked great on my Mac for accessing the in-network MSSQL servers.