Connecting Analysis Tools to your Redshift Database

This article will detail how to connect to your Redshift database using R*, Python*, Tableau Desktop or Power BI.

In order to connect these tools to Redshift, you will first need your Redshift connection details which can be found in the SQL Workbench connection window.

*Note: Redshift database COPY and UNLOAD functions are not enabled for data security purposes. As such, any local data created within the Workspace using R or Python cannot be uploaded back into the Redshift database using these functions.

1. Prerequisites

2. Locating your Redshift connection details

3. Connecting Redshift to Python

4. Connecting Redshift to RStudio

5. Connecting Redshift to Tableau

6. Connecting Redshift to Power BI

Prerequisites

• you have created a project

• you have added people to your project

• your data license has been approved by all parties

• your Workspace has been approved and is running

• you have loaded data to your workspace

Locating your Redshift connection details

1. Open SQL Workbench

2. Click ‘Files’ and select ‘Connect window’ to display your connection profile

Note: If you are using Tableau, R, Python or PowerBI you will need to use the same Redshift connection details from SQL Workbench to access datasets using other applications.

Connecting Redshift to Python

1. Retrieve your Redshift credentials from the SQL Workbench connection window (File > Connect Window).

2. Use the ‘psycopg2’ python package to connect to your Redshift database.

3. Run the below command with reference to your Redshift credentials.

Connecting Redshift to RStudio

1. Retrieve your Redshift credentials from the SQL Workbench connection window (File > Connect Window).

2. Use the ‘dplyr’, ‘dbplyr’ & ‘RPostgreSQL’ R packages to connect to your Redshift database.

3. Run the below command with reference to your Redshift credentials.

Connecting Redshift to Tableau

Note: Tableau requires you to bring your own software licenses for activation.

1. Retrieve your Redshift credentials from the SQL Workbench connection window (File > Connect Window).

2. Open Tableau. From the left-navigation menu, under ‘Connect To a Server’ select Amazon Redshift.

3. Fill in the required fields with your Redshift credentials. For ‘Server’, use the ‘URL’ listed in the SQL Workbench connect window, however, omit the first part (“jdbc:redshift://”) from the server name.

4. Click 'Sign in'.

Connecting Redshift to Power BI

Note: Power BI requires you to bring your own software licenses for activation.

1. Retrieve your Redshift credentials from the SQL Workbench connection window (File > Connect Window).

2. To retrieve the full Server name, open a Command Prompt and type
   nslookup <insert server details from your Redshift Credentials>
   This will return the "Name" of the Server for use in following steps

3. From the Home tab, click ‘Get Data’. In the ‘Get Data’ window, scroll down and select connect to ‘Amazon Redshift’.

4. Fill in the required fields using database credentials retrieved from SQL Workbench: (a) Server: include both the server URL (Name returned from the Command Prompt) and port number (from Redshift Credentials), ensure you put the port number after the server name (with a colon separating the two e.g. “:1234”) (b) Database name (from Redshift Credentials)

5. Select ‘DirectQuery’ under ‘Data Connectivity mode’ and click OK.

Next Step:

Custom configuration of an existing Workspace
Getting your output from the Workspace