There have been a number of posts recently on Google about the Data Studio PostgreSQL connector successfully connecting to Redshift as a data source. This seems to be a powerful solution for creating visualizations off Snowplow data.
Unfortunately I have not been able to get this to work - I get errors that it was unable to connect. I have added the IP’s to the white-list on AWS side.
Has anyone tried this and been able to use Data Studio with Redshift?
Yes, connection with redshift works fine.
But… after that, nothing is possible. Datastudio shows the tables without the part before the dot, and tries to access them as such.
So: atomic.events becomes events
web.page_views becomes page_views
All operations end like this:
Error with SQL statement: ERROR: relation "page_views" does not exist
Would be great if there’s a workaround for this.
I have never used Data Studio. Is the issue that it doesn’t support database schemas?
The Data Studio Postgres connector will default to only looking for tables in the
public schema. You can work around this to get Redshift working my modifying the
search_path for the user you are authenticating as for example:
create user datastudio_test with password '';
alter user datastudio_test set search_path to atomic;
grant usage on schema atomic to datastudio_test;
grant select on all tables in schema atomic to datastudio_test;
This is excellent. Works now!
Or while connecting, instead of selecting the table from the list, you can select custom query and write the select query: SELECT * from schema_name.table_name; and Add/Reconnect.