How can i replace a db name using variable in snowflake?

Question

I can use variables for query filtering conditions. E.g.,

set mytime = '2021-12-12 09:00:00';

select col1
from db1.schema.table1
where event_time > $mytime

However, if I use the same way to replace the db1 with a variable, it will not work. I.e.,

set mytime = '2021-12-12 09:00:00';
set db_name = 'db1';

select col1
from $db_name.schema.table1
where event_time > $mytime

You might not be able to use it directly, but you can do use database identifier($db_name) and then select query excluding dbname qualifier, like select colname from schema_name.table; — Pankaj
– Pankaj, Commented Apr 16, 2022 at 0:53
See the session variable section of docs.snowflake.com/en/sql-reference/… — Simeon Pilgrim
– Simeon Pilgrim, Commented Apr 16, 2022 at 2:01

Hobo · Accepted Answer · 2023-07-14 00:24:32Z

3

So if your variable has a fully qualified name db/schema/table name and you use the IDENTIFIER function can help.

create table test.test.db1(id number);

set db_name = 'test.test.db1';

insert into test.test.db1 values (1),(2),(3);

then this works:

select id
from identifier ($db_name);

but composing the string on the fly does not presently work:

select id
from identifier ($just_db_name||'.test.db1');

but you can two step this:

set fqn_db_name = $just_db_name||'.test.db1';

select id
from identifier ($fqn_db_name);

Snowflake Scripting:

Using snowflake scripting, it can be done as a single "statement", like so:

begin
    let fqn text := $just_db_name || '.test.db1';
    let res resultset := (select id from identifier(:fqn));
    return table(res);
end;

Hobo

7,6215 gold badges44 silver badges52 bronze badges

answered Apr 16, 2022 at 3:39

Simeon Pilgrim

26.7k3 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.