CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schemasnowflake-connector
Xi12
Xi12

Reputation: 1211

Create a stage to be used across all schemas

I have created a stage in a schema called X, when I run the command "show stages" I am able to see that stage. Now when I go to the other schema "Y" and run the command "show stages", it is not showing that schema. I want to use the stage in "X". How can I do this?

Thanks, Xi

Upvotes: 3

Views: 1493

Answers (3)

Anshul Thakur
Anshul Thakur

Reputation: 524

If you are using the Account admin role and do not specify any Database and Schema for the session, you can list all the stages at the account level. Provided you have the permissions. If you specify a DB and Schema for the session, then you can only get the stages that are created in the same namespace.

If you want to use a stage that is from a different schema, you need to use the complete FQDN of the stage in the SQL statement.

For example - If I am the owner of Stage1 which is in Schema "Schema1" and I want to use Stage1 in Schema2. Also, the DBs of Schema1 and Schema2 are different.

Then to use Stage1 in Schema2 I have to use following -

use Schema Schema2;
ls @DB1.Schema1.Stage1;

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

To use a stage from different schema that currently in use, namespace has to be provided:

COPY INTO ...
FROM @[namespace.]ext_stage_name[/path]

COPY INTO

FROM ...

namespace is the database and/or schema in which the internal or external stage resides, in the form of database_name.schema_name or schema_name. It is optional if a database and schema are currently in use within the user session; otherwise, it is required.

The same applies for ad-hoc querying data from stages:

SELECT ...
FROM @[namespace.]external_stage_name[/path]

Upvotes: 4

NickW
NickW

Reputation: 9768

The show commands use your current db/schema by default. If you want it to apply to a specific database, schema (or the entire account) then you need to use the SHOW STAGES IN … version of the command.

This is all covered in the documentation here

Upvotes: 1

Related Questions