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.
Amazon in their guide uses SQL Workbench/J. It is simple, and it just works!
- Download SQL Workbench/J from here.
- Read the Installing and starting SQL Workbench/J page from here.
- Follow these instructions on how to connect to your Amazon Redshift cluster over a JDBC Connection in SQL Workbench/J from Amazon here.
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.
- Download psql Tool from here.
- Read the how to use psql from here.
- Follow these instructions on how to connect to your Amazon Redshift cluster with the psql tool with this guide from Amazon.
Navicat offers many flavors; this is a compact version of Navicat.
A Windows based, Amazon Redshift specific IDE.
It has a simple interface that works nicely. It is a Mac native and supports database systems derived from PostgreSQL like Amazon Redshift.
It is Java based, Open Source SQL IDE.
You can download it here.
JackDB is an SQL IDE that works with PostgreSQL, Amazon Redshift and more.
Great all around SQL Client. RazorSQL ships with the JDBC drivers necessary to connect to Amazon Redshift.
It is Open Source universal database tool.
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.
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.
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.
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.
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.
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.
Periscope offers an engaging SQL environment for SQL-savvy users.
QlikView is a great product for Guided Analytics and more data-savvy users.
SiSense can handle large databases and datasets quite easily, easy to design and customize.
Popular and powerful desktop data visualization tool.
Yellowfin’s platform includes notable discovery features and ease of use.
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.
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 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 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 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.
A platform used by millions of engineers and scientists worldwide to analyze and design systems and products. Get it here.