Skip to main content

Connecting to the Redshift cluster from Google Data Studio

Intro​

This guide will step you through how to connect to Redshift, on the Data Platform, from Google Data Studio. You will then be able to access the data stored on the platform from Data Studio.

Prerequisites​

  • You have access to your department's credentials listed here or you have had credentials shared with you by the platform team.

Steps​

First, download the bundle certificate you will need to connect to the Redshift cluster here:

https://s3.amazonaws.com/redshift-downloads/amazon-trust-ca-bundle.crt

Then, when adding a data source in Google Data Studio

  1. Select "Amazon Redshift".
  2. If this is the first time you've made a connection, you may need to select "Authorise".
  3. Ensure "Basic" is selected to the left of 'Database authentication' (not JDBC URL).
  4. Retrieve your credentials from AWS using the links below for department specific credentials. Once on the AWS Secrets Manager page, scroll down to the "Secret Value" box and click "Retrieve secret value" to reveal the credentials (e.g. Database, Host Name or IP etc). Alternatively, use the credentials shared with you by the data platform team.
  5. Copy and paste these credentials into the corresponding fields in Google Data Studio.
  6. Tick the "Enable SSL" box.
  7. Upload the amazon-trust-ca-bundle.crt file you previously downloaded:

gds-redshift-crt.png

  1. Click "Authenticate".
  2. To the right of the 'Database authentication' panel, you should see options for TABLES or CUSTOM QUERY; select CUSTOM QUERY and provide a PostgreSQL query for the data you want. (Note: The TABLES option will not list out the available tables even when your connection is successful.) An example could be:
SELECT * FROM liberator_refined_zone.denormalised_pcns where import_date = (
    SELECT max(import_date) from liberator_refined_zone.denormalised_pcns
) limit 100;

This query can only return a maximum of 150,000 rows as specified in Googles Documentation. It might be helpful to test your query in AWS Athena first to check there aren't too many rows. 12. Click "Add" (if adding data directly to a report) or "Connect" (if creating a new data source), this bit might take a little while. To improve the query performance you may also want to create a data extract.

Department specific credentials​

  1. Log into the AWS Console for the DataPlatform-Production or DataPlatform-Pre-Production account depending on which you wish to connect to.

  2. Go to the AWS Secrets Manager secret for your department

ProductionPre-production
Parking CredentialsParking Credentials
Planning CredentialsPlanning Credentials
Housing Repairs CredentialsHousing Repairs Credentials

If you click the link above before logging into the relevant Data Platform account you will be presented with an AWS login screen.

If your department is not listed above but exists with the platform, you should be able to find the credentials by searching for 'YOUR DEPARTMENT/redshift' in Secrets Manager.

  1. Scroll down to the "Secret value" section and select "Retrieve secret value".