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, in the data platform, from Google Data Studio. You will then have access to the data stored in the platform from Data Studio.

Prerequisites#

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

Steps#

First, download the following resources

  1. Client Certificate. This will expire a year after you download it, after which you will have to download a new certificate.
  2. Client Private Key.
  3. Server Certificate.

Then, when adding a data source in Google Data Studio

  1. Select "PostgreSQL".
  2. Under "Database Authentication", ensure "BASIC" is selected then enter the credentials shared with you by the data platform team. See below for links to department specific credentials.
  3. Tick the "Enable SSL" box
  4. Upload the three files downloaded in the first step.
  5. Click "Authenticate", then select "CUSTOM QUERY"
  6. In the free text box you need to enter a PostgreSQL query that defines the data that's in this data source. 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 100000;
    This query can only return a maximum of 100,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.
  7. Click "Add", this bit might take a little while.

Department specific credentials#

  1. Log into the AWS Console for the DataPlatform-Staging account.

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

    If you clicking the link above before logging into the DataPlatform-staging account you will be presented with an AWS login screen.

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