Is there support to join across multiple Snowflake databases?

[chintanparikh]

I have a Snowflake data warehouse. This Snowflake data warehouse has multiple databases inside it. I would like to join across these databases. Snowflake does support this (https://support.snowflake.net/s/question/0D50Z00007AptG4SAJ/is-there-anyway-to-achieve-cross-db-join-in-snowflake), so I'm wondering how I can support this inside Superset.

I think the issue is that Superset treats the different Snowflake databases as separate Snowflake databases rather than recognizing they are all in Snowflake and using the Snowflake engine to run queries.

Trying to add a Snowflake data source with no database selected almost works - it's able to pull out all the tables from each database (not ideal because of conflicts, but it will work for now). However, when I try to query in SQL Lab it says there is no active database selected.

Would love some advice here.

[issue-label-bot]

Issue-Label Bot is automatically applying the label #question to this issue, with a confidence of 0.83. Please mark this comment with 👍 or 👎 to give our bot feedback!

Links: app homepage, dashboard and code for this bot.

[villebro]

Can you describe in more detail what you mean by join across databases? I believe writing a query in SQL Lab where the table name is prefixed by database.schema. should work, regardless of what database has been selected.

[chintanparikh]

@villebro So let's say I have two databases - one called database1 and another called database2. I'm trying to do something like SELECT * FROM database1.crm.customers INNER JOIN database2.crm.leads ON customers.uuid = leads.uuid but it doesn't work. Even SELECT * FROM database1.crm.customers gives me 002003 (02000): 018c6168-026b-2e5b-0000-5bf10005077a: SQL compilation error: Database 'DATABASE1' does not exist.

[villebro]

Ok that's interesting. I can look into this. I assume you have all the right permissions, and have been able to execute the same query via the SF Web UI?

[chintanparikh]

@villebro Got it. I was trying to use the name I gave the source in superset as the database name, rather than the actual database name in snowflake. When I do the latter it works. Appreciate the help!

[abhishekray2481984]

Hi @villebro , @chintanparikh ,

Does this work for tables of different database types . i.e one is Postgres and the other is oracle.

I am facing error while joining tables belonging to different databases.

[ajaydevtron]

Hi @villebro , @chintanparikh ,

Does this work for tables of different database types . i.e one is Postgres and the other is oracle.

I am facing error while joining tables belonging to different databases.

I am also facing this in the case of Postgres ,Could anyone help me out ?