Tuesday, January 19, 2016

Connecting DataGrip to Redshift

Recently I started evaluating JetBrains' new DataGrip product, and I decided to create an updated version of my instructions for connecting to Amazon Redshift, using this new tool with the latest JDBC driver from Amazon.

Prepare Amazon Redshift JDBC Driver

  1. Browse to your AWS Redshift console, go to the "Connect Client" section and download the Redshift JDBC driver from Amazon.
  2. Copy the file from your Downloads folder to somewhere more permanent, like your home directory or a location accessible to all users on the computer.
  3. Set permissions to allow everyone to read the driver jar file:
    chmod a+r RedshiftJDBC41-1.1.9.1009.jar
  4. On OS X, you may need to remove the quarantine mode attribute:You can see this attribute with the "ls -l@" command:
    ls -l@ *.jar-rw-r--r--@ 1 ryan staff 2198193 Aug 28 13:42 RedshiftJDBC41-1.1.9.1009.jar com.apple.metadata:kMDItemWhereFroms 194 com.apple.quarantine 64
    
    The quarantine attribute can be removed with this command:xattr -dr com.apple.quarantine RedshiftJDBC*.jar

Add Redshift Driver to DataGrip

  1. In Datagrip, go to Manage Data Source (Cmd + ; on OS X)
  2. Press the + button in the upper left and select "Driver" to create a new Database Driver
  3. In the "Driver Files" section, press the + button in the lower left and select the path where you put the driver JAR file.
  4. Use the following settings: 
    • Class Name: com.amazon.redshift.jdbc41.Driver
    • Dialect: PostgreSQL
    • Auto Commit: 
      • Yes for convenience, 
      • No for safety (This Requires you to explicitly commit after executing DML)
    • Auto Sync: Yes (turn this off if it slows you down too much)
    • Click Apply

    Connect to your Redshift Instance with the new Driver



    1. Press the + button again and select the name of the driver you just created (Redshift)
    2. Go to your Redshift Console in AWS.  Under "Connect Client", select your cluster and get the JDBC URL.
    3. Copy the JDBC URL into the URL field in DataGrip
    4. Enter your username and password, and test the connection.  
    5. Hit OK.  You can now query Redshift from DataGrip!

    5 comments:

    1. Thanks for this, you just solved the issue I was having and now I can connect. Much appreciated.

      ReplyDelete
    2. Thanks for this. Adding to this, I had ssl problems. After setting up the driver (RedshiftJDBC41-1.1.10.1010.jar) as described in your post, I still got an SSL error. I resolved it with the following url :
      jdbc:redshift://localhost:5439/yourdatabasename?ssl=true&sslfactory=com.amazon.redshift.ssl.NonValidatingFactory

      ReplyDelete
    3. Is there any advantage using the redshift JDBC over the included Postrgres driver?

      ReplyDelete
    4. Perfect instructions, thank you.

      ReplyDelete
    5. I used the JDBC 4.2 driver and didn't see tables under each of my schemas in the Database Tool view. I noticed you used the JDBC 4.1 driver, so I backed off to that one in the hopes that I could get a table list. Alas, no tables. :(

      However, I can see my table list under each schema while connecting to the same RS cluster using the built-in PostgreSQL driver. I'd like to use the Redshift driver since it's currently recommended by Amazon. Does anyone have pointers on how I could get my tables to display whilst using the Amazon JDBC driver?

      ReplyDelete