Is it possible to run a snowflake sql script without qualifying the database name.?

Question

Is it possible to run a SQL script without qualifying the database name? Currently, we are using the program like below,

    SELECT  I.XXXX_LOC_SKEY,
            C.COUNTRY_SKEY
    FROM
            DEV_XXXX_DB.STAGING.XX_TABLE_LOCATION_SALES         F,
            DEV_XXXX_PRESENTATION_DB.DIMS.XXXX_LOCATIONS_D      I,
            DEV_XXXX_PRESENTATION_DB.DIMS.COUNTRY_D             C,
            DEV_XXXX_PRESENTATION_DB.DIMS.XXXX_DAILY_CALENDAR_D H
    WHERE
            F.STORE_CODE       = I.DOOR
    AND     I.CHANNEL          = 'XXXX'
    AND     F.COUNTRY          = C.COUNTRY_CODE
    AND     I.COUNTRY_SKEY     = C.COUNTRY_SKEY
    AND     F.DATE = H.DATE;

We would like to run the same script, without mentioning the database names as below.

    SELECT  I.XXXX_LOC_SKEY,
            C.COUNTRY_SKEY
    FROM
            STAGING.XX_TABLE_LOCATION_SALES         F,
            DIMS.XXXX_LOCATIONS_D                   I,
            DIMS.COUNTRY_D                          C,
            DIMS.XXXX_DAILY_CALENDAR_D              H
    WHERE
            F.STORE_CODE       = I.DOOR
    AND     I.CHANNEL          = 'XXXX'
    AND     F.COUNTRY          = C.COUNTRY_CODE
    AND     I.COUNTRY_SKEY     = C.COUNTRY_SKEY
    AND     F.DATE = H.DATE;

score 1 · Answer 1 · answered Dec 13 '19 at 13:07

1

Although the snowflake Web GUI remembers your last database, and other eg. ETL tools may configure a default database for a connection, Snowflake has no concept of a default database. All tools including the Web GUI have to issue the following command to set the database according to tool configuration:

USE DATABASE db_name;

If you want to avoid referencing a database in a command, you have to use the USE DATABASE command beforehand in the session.

answered Dec 13 '19 at 13:07

Hans Henrik Eriksen

2,690
5
12

1

The `default_namespace` attribute that you can specify for a user allows you to login with any tool without having to use the `use database db_name;`. I don't think the tools themselves use this to execute a `use database` command either? – Simon D Dec 13 '19 at 13:47

waldente · Accepted Answer · 2019-12-13T15:17:06.867

1

This is how unqualified objects are resolved:

https://docs.snowflake.net/manuals/sql-reference/name-resolution.html.

Note you can customize the SEARCH_PATH to search multiple schemas.

Alternatively you can put the fully qualified names in variable:

set f = 'DEV_XXXX_DB.STAGING.XX_TABLE_LOCATION_SALES';

select f.* from table($f) f;

select f.* from identifier($f) f;

edited Dec 13 '19 at 15:17

answered Dec 13 '19 at 14:29

waldente

1,324
9
12

score 0 · Answer 3 · edited May 30 '20 at 17:17

0

I can see your SQL accessing the data from different database then you have run SQL with fully qualified name of the object.

If snowflake current session is pointing to the location of the object then you can run SQL without qualifying the database name.

edited May 30 '20 at 17:17

Dharman

30,962
25
85
135

answered Dec 13 '19 at 07:44

Sriga

1,165
4
11

Snowflake can point only one database location at a time, My SQL script is having more than one databases and schema. In this scenario how we can run a script. Let me know anymore information required. – Unnikrishnan Raju Dec 13 '19 at 08:32
@Unnikrish use fully qualified table name to run SQL. When your SQL access data from different table in different DB you have to use fully qualified name. – Sriga Dec 13 '19 at 10:35
Since you're referring to different databases then you will need to fully qualify all of them except the one that your current session points to. There isn't really any other way to do this because you can have the same schema name in different databases which would mean your query would be ambiguous – Simon D Dec 13 '19 at 12:26

score 0 · Answer 4 · answered Dec 13 '19 at 14:18

Inferring a bit based on the SQL provided, you have embedded the environment into your database name (DEV_XXXX_DB). Since you have multiple databases per environment, this forces you to explicitly add the database name to every sql which crosses DB as SimonD has pointed out.

If you haven't discovered this yet, you're going to have a really hard time promoting code between environments b/c everywhere you're going to have to replace DEV_XXXX_DB with PRD_XXXX_DB.

If you're not that far down the journey of Snowflake implementation, I would suggest that you rethink your DB.SCHEMA strategy and create just 1 DB per environment (env_XXXX_DB) and put all the schemas w/in that DB (env_XXXX_DB.STAGING, env_XXXX_DB.DIMS).

this has the added benefit of being able to take advantage of DEFAULT_NAMESPACE and the USE DATABASE features. It also aligns nicely w/ all the tools surrounding the Snowflake ecosystem since they generally require a database specified in the initial connection. E.g., I further isolate my ELT tool DEV vs PRD so that ELT DEV only connects to Snowflake DEV_XXXX_DB, etc. — Erick Roesch, Dec 13 '19 at 14:22

score 0 · Answer 5 · answered Dec 13 '19 at 18:55

If you have many databases but most of the data comes from one of them, you can create views in the one database pointing across to the other one. That way all of your objects can be queried without needing the database.

i.e. CREATE VIEW MY_VIEW as SELECT * FROM DATABASE.SCHEMA.TABLE

Is it possible to run a snowflake sql script without qualifying the database name.?

5 Answers5