Monday, August 31, 2015

Connecting IntelliJ to Redshift

Are you using Amazon's Redshift Service and you want to connect to it via IntelliJ?  Here's how you do it.

Download Redshift JDBC Driver from Amazon

  1. In your 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 jar file:
    chmod a+r RedshiftJDBC41-

Add Redshift Driver to IntelliJ

  1. In IntelliJ, go to View / Tool Windows / Database
  2. Open the Data Source Properties
  3. Press the + button and select "Database Driver" to create a new Redshift Driver
  4. In the "Driver Files" section, press the + button and select the path where you put the driver JAR file.
  5. If the driver can be read successfully, the Class Name drop down should show more than just "java.sql.Driver".  
    • In my case, it couldn't be read because OS X had put the file in quarantine mode, which you can see with the "ls -l@" command:
      ls -l@ *.jar
      -rw-r--r--@ 1 root  wheel  2198193 Aug 28 13:42 RedshiftJDBC41-     211      67

    • The quarantine attribute can be removed with this command:
      xattr -dr RedshiftJDBC41-

  6. Use the following settings:
    • Class Name:
    • URL Template: jdbc:redshift://{host}:{port}/{database}
    • Default Port: 5439 (or whatever your cluster uses)
    • Dialect: PostgreSQL
    • Requires Authentication: Yes
    • Local Database: No
    • 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)
  7. Click Apply

Connect to your Redshift Instance with the new Driver

  1. In the Same Data Sources and Drivers Window in IntelliJ, press the + button again and select Redshift
  2. Go to your Redshift Console in AWS.  Copy the string under "Endpoint" into the "Host" field in IntelliJ.  this is in host:port format, so cut the port off, delete the ":" and paste the port into the port field.
  3. Enter your database name, user, and password, and test the connection.  
  4. Hit OK.  You can now query Redshift from your IDE!