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!
    Posted by at

    7 comments:

    1. KMMarch 22, 2016 at 11:27 AM

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

      ReplyDelete
    2. UnknownJune 25, 2016 at 1:01 PM

      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. Jeff GrahamJune 27, 2016 at 9:49 AM

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

      ReplyDelete
    4. UnknownJuly 28, 2016 at 3:23 AM

      Perfect instructions, thank you.

      ReplyDelete
    5. KerrSeptember 1, 2016 at 8:17 AM

      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
    6. Carol HooperSeptember 2, 2020 at 3:36 AM

      If you guys also want to know proper details about the reputed company, then you can read more about data transformation or the data warehouse solutions through this page.

      ReplyDelete

    Subscribe to: Post Comments (Atom)