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.
- You have access to your departments credentials listed here or you have had credentials shared with you by the platform team.
First, download the following resources
- Client Certificate. This will expire a year after you download it, after which you will have to download a new certificate.
- Client Private Key.
- Server Certificate.
Then, when adding a data source in Google Data Studio
- Select "PostgreSQL".
- 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.
- Tick the "Enable SSL" box
- Upload the three files downloaded in the first step.
- Click "Authenticate", then select "CUSTOM QUERY"
- 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 beThis 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.
SELECT * FROM liberator_refined_zone.denormalised_pcns where import_date = ( SELECT max(import_date) from liberator_refined_zone.denormalised_pcns) limit 100000;
- Click "Add", this bit might take a little while. To improve the query performance you may also want to create a data extract.
Log into the AWS Console for the DataPlatform-Staging account.
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.
Scroll down to the "Secret value" section and select "Retrieve secret value".