Tools to connect to your Amazon Redshift Cluster

Now that we saw how we export data from Amazon Redshift let’s see an application of such data. As we said in the previous section, two are the main reasons we need our data.

First, every action we perform to the data stored in Amazon Redshift creates new data. This data can be used raw or by a Business Intelligence tool.

Second, you might need to unload data to analyze it using statistical methodologies or to build predictive models. That means the data analyst should go beyond the SQL capabilities of the data warehouse.

Data management and ETL as a service tools like Blendo gives you the power to consolidate all your data in Amazon Redshift. The next step is up to your use case, and the tools below can get you covered.

Which SQL IDE is the best to connect to AWS Redshift?

Amazon does not provide any SQL client tools, but they recommend SQL Workbench/J. Though you can connect to your cluster using psql or any SQL IDE that supports PostgreSQL JDBC or ODBC drivers.

SQL Workbench/J

Amazon in their guide uses SQL Workbench/J. It is simple, and it just works!

  1. Download SQL Workbench/J from here.
  2. Read the Installing and starting SQL Workbench/J page from here.
  3. Follow these instructions on how to connect to your Amazon Redshift cluster over a JDBC Connection in SQL Workbench/J from Amazon here.

Connect to Your Cluster over a JDBC Connection in SQL Workbench/J

psql command line tool

You may also connect with psql to an Amazon Redshift cluster. Psql is a terminal-based front end from PostgreSQL, and it is pretty straightforward to use. You will need your Amazon Redshift cluster endpoint, database, and port to connect.

  1. Download psql Tool from here.
  2. Read the how to use psql from here.
  3. Follow these instructions on how to connect to your Amazon Redshift cluster with the psql tool with this guide from Amazon.

Connect to Your Cluster by Using the psql Tool

pgAdmin

You may also connect with pgAdmin. It is all time classic in the PostgreSQL community and its latest version (as of today) pgAdmin 4, is a complete rewrite of pgAdmin, built using Python and Javascript/jQuery.

  1. You can find it here.
  2. Documentation is here.

pgadmin

JetBrains DataGrip

  1. An excellent SQL IDE from JetBrains. Get it here.
  2. Read their blog for new updates.

Navicat Essentials

Navicat offers many flavors; this is a compact version of Navicat.

  1. Get Navicat from here.
  2. Read how to connect to Amazon Redshift here.

Amazon Redshift Connection Navicat

Aginity Workbench

A Windows based, Amazon Redshift specific IDE.

  1. Get Aginity Workbench from here.
  2. Read how to connect to Amazon Redshift from here.

Aginity Workbench for Redshift

Postico

It has a simple interface that works nicely. It is a Mac native and supports database systems derived from PostgreSQL like Amazon Redshift.

  1. Get Postico from here.
  2. Get started guide is here.

postico amazon redshift

SQuirreL

It is Java based, Open Source SQL IDE.

You can download it here.

JackDB

JackDB is an SQL IDE that works with PostgreSQL, Amazon Redshift and more.

  1. You can find it here.
  2. Documentation is here.

jackdb redshift

RazorSQL

Great all around SQL Client. RazorSQL ships with the JDBC drivers necessary to connect to Amazon Redshift.

  1. You can get it from here.
  2. Redshift instructions here.
  3. Instructions to connect here.

RazorSQL amazon redshift

DBeaver

It is Open Source universal database tool.

  1. You can get it here.
  2. Instructions / Wiki here.

We are sure there are more in the vastness of the web. If you want to add one tool just mail us at team [at] blendo.co

Which BI tools can I use to connect a BI tool to Amazon Redshift?

It is of most importance to get all your data in Redshift first. Then connecting a BI tool in an Amazon Redshift cluster is usually, straightforward. Some time ago we wrote a post with the ultimate list of custom dashboards and BI tools. The list gets updated with new tools, but for our Amazon Redshift guide, we stay to those that work with specifically with Amazon Redshift.

For this reason, we took as a benchmark the Business Intelligence Partners list in Amazon’s Partner Network. Here are some of the most prominent.

Amazon QuickSight

An Amazon product, fast and can connect to all of Amazon’s products as data sources like Redshift. QuickSight can access data from many different sources, both on-premises and in the cloud. There’s built-in support for Amazon Redshift, RDS, Amazon Aurora, EMR, Kinesis, PostgreSQL, and more.

Amazon QuickSight

Chartio

Great BI tool out there and Blendo partner. They provide a comfortable, light-weight tool for data pulls, so either you use SQL or their drag-and-drop Interactive Mode.

Chartio partners with Blendo. So if you need all your data into Amazon Redshift and then a tool for your visualizations then Blendo + Chartio are one of your great choices.

chartio

IBM Cognos Business Intelligence

IBM Cognos Business Intelligence is a web-based, integrated business intelligence suite by IBM. It provides a toolset for reporting, analysis, scorecards, and monitoring of events and metrics.

Analysis-studio-IBM-Cognos-bi

Looker

A lightweight but powerful “data exploration” tool that allows you to “look” into your data without writing any code or SQL. Their LookML is awesome.

Looker partners with Blendo. So if you need all your data into Amazon Redshift and then a tool for your visualizations then Blendo + Looker are one of your great choices.

looker

Mode

Mode is a data analysis platform that combines a powerful, web-based SQL editor with charting and sharing tools. Their product was built for SQL proficient users.

mode analytics

Periscope

Periscope offers an engaging SQL environment for SQL-savvy users.

periscope-data

Qlik

QlikView is a great product for Guided Analytics and more data-savvy users.

qlik-view

SiSense

SiSense can handle large databases and datasets quite easily, easy to design and customize.

SiSense

Tableau

Popular and powerful desktop data visualization tool.

tableau

Yellowfin

Yellowfin’s platform includes notable discovery features and ease of use.

Yellowfin

You may find the full Business Intelligence Partners list in Amazon’s Partner Network (APN).

How can I connect as a Data Analyst / Data Scientist to Redshift?

Amazon Machine Learning

Amazon Machine Learning is a service that makes it easy for developers of all skill levels to use machine learning technology. You can get it from here.

Jupiter

The Jupyter Notebook is an open-source web application that allows you to create and share documents that contain live code, equations, visualizations and explanatory text. More information here.

Python

Python is widely used in Data Science projects. More info on Python here and a tutorial on how to connect to Amazon Redshift and PostgreSQL with Python.

R

R along withy Python are the most commonly used programming languages in Data Science today. Here is the official site of R. Here is a tutorial on how to connect to Amazon Redshift and PostgreSQL with R.

Julia

Julia is a high-level, high-performance dynamic programming language for numerical computing. It provides a sophisticated compiler, distributed parallel execution, numerical accuracy, and an extensive mathematical function library. More information here.

Matlab

A platform used by millions of engineers and scientists worldwide to analyze and design systems and products. Get it here.

load data into any data warehouse - Blendo