Change schema on connection with RedShift

Question

I have a Tableau application that works above RedShift. I have many set of data tables saved in different schemas. Each schema has exactly the same tables in the same format.

I also have a set of users. Each user should work with his own schema only (I have a table of mappings between users and schemas).

The question is how to make Tableau look on a different schema depending on the current username?

Thanks!

Answer 1

Redshift doesn't have the sort of capacity to set a default schema as one can set a default database in MSSQL, but perhaps you can use the search_path functionality, where when the user logs in, Tableau queries the user/schema map table then sets the search_path to that schema. Any subsequent queries look for tables/views in that schema first. See http://docs.aws.amazon.com/redshift/latest/dg/r_search_path.html for more info on search_path.