CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Paul Cavacas
Paul Cavacas

Reputation: 4454

Snowflake SQL Statement to retrieve the current organizational account

In snowflake I have 3 accounts setup. What I'm looking for is a way to write a query to determine which account the query is running against. I cannot find any way to determine which account the current connection is using.

Additional background What I really have is DDL creation scripts checked into source control and I'm using Flyway to turn these scripts over into the different accounts, which is working well with one exception. Some of the artifacts that are being created are Snow Pipes, which have paths to storage accounts in them.

What I'm trying to do is modify the SQL scripts, so that they will generate the paths to the correct storage accounts depending on the organizational account that the query is running in.

Upvotes: 2

Views: 243

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26058

I think you want CURRENT_ACCOUNT

SELECT CURRENT_ACCOUNT();

gives:

CURRENT_ACCOUNT()
EVA31433

which can be used like:

SELECT 
    CASE CURRENT_ACCOUNT()
        WHEN 'EVA31433' then 'awesome\\demo\\account_path'
        ELSE 'HELP I don\'t know where I am'
    END AS path
;
PATH
awesome\demo\account_path

Upvotes: 2

Related Questions