Knowledge Drop
Last Tested: Dec 18, 2020
Setting Up Connection to Support Multiple Databases
- Nothing extra required. Connect to Snowflake as normal and communication with all DB's within the warehouse is supported by default.
But the Connection Requires Me to Specify a Database Name
- This is still need because Looker needs to know the location of where to store Persisted Derived Tables. Specify the database that either has the most tables in it, or where you're most comfortable storing PDTs.
- If you're referencing databases that aren't what was chosen above, you will need to make sure you specify the db name you want to connect to. Within the sql_table_name: tag in Looker, fully qualify the table name. Format is [database].[schema].
- Looker will only auto-generate views at project creation for the database in the connection, but you can use "create view from table" once the project is created to generate tables individually for tables in the other database(s)
- The sidebar in SQL Runner will only see the tables of the database in the connection.I have a multiple tables, with identical schema and structure, stored across multiple DBs. What is the best way to do consolidate these?
Create a derived table with the following logic.
SELECT *, 'db1' as db_name FROM [db1].[schema].
UNION
SELECT *, 'db2' as db_name FROM [db2].[schema].
UNION
SELECT *, 'db3' as db_name FROM [db3].[schema].
...
...If certain users should have access to all DBs but other users shouldn't. Use access filters wherever this new derived table is referenced in an explore.
Alternatively you could also use user attributes to parameterize the the sql_table_name: tag to be <user_attribute>.[schema].
or parameterized connections.
I heard something about including "&database=." in the additional_params section in the connection menu.
The only reason for doing this would be if you want to expose all the tables, regardless of database, in SQL runner. This still requires table qualification, and if you're in a multi-tenant situation there is no way to differentiate tables with the same name. It can also cause issues if trying to use PDTs.
LinkedIn
Twitter