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-
  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- 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!