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.

CodePudding user response：

I think you want CURRENT_ACCOUNT

SELECT CURRENT_ACCOUNT();

gives:

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
;