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

Answer 1

So if your variable has a fully qualified name db/schema/table name and you use the INDENTIFIER 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);

ID
1
2
3

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);

ID
1
2
3

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; 

ID
1
2
3