Choosing a Stage for Local Files¶
A stage specifies where data files are stored (i.e. “staged”) so that the data in the files can be loaded into a table.
In this Topic:
Types of Stages¶
By default, each user and table in Snowflake is automatically allocated an internal stage for staging data files to be loaded. In addition, you can create named internal stages.
File staging information is required during both steps in the data loading process:
- You must specify an internal stage in the PUT command when uploading files to Snowflake.
- You must specify the same stage in the COPY INTO <table> command when loading data into a table from the staged files.
Consider the best type of stage for specific data files. Each option provides benefits and potential drawbacks.
Each user has a Snowflake stage allocated to them by default for storing files. This stage is a convenient option if your files will only be accessed by a single user, but need to be copied into multiple tables.
User stages have the following characteristics and limitations:
- User stages are referenced using
@~; e.g. use
LIST @~to list the files in a user stage.
- Unlike named stages, user stages cannot be altered or dropped.
- User stages do not support setting file format options. Instead, you must specify file format and copy options as part of the COPY INTO <table> command.
This option is not appropriate if:
- Multiple users require access to the files.
- The current user does not have INSERT privileges on the tables the data will be loaded into.
Each table has a Snowflake stage allocated to it by default for storing files. This stage is a convenient option if your files need to be accessible to multiple users and only need to be copied into a single table.
Table stages have the following characteristics and limitations:
- Table stages have the same name as the table; e.g. a table named mytable has a stage referenced as @%mytable.
- Unlike named stages, table stages cannot be altered or dropped.
- Table stages do not support setting file format options. Instead, you must specify file format and copy options as part of the COPY INTO <table> command.
- Table stages do not support transforming data while loading it (i.e. using a query as the source for the COPY command).
Note that a table stage is not a separate database object; rather, it is an implicit stage tied to the table itself. A table stage has no grantable privileges of its own. To stage files to a table stage, list the files, or query them on the stage, you must be the table owner (have the role with the OWNERSHIP privilege on the table).
This option is not appropriate if you need to copy the data in the files into multiple tables.
Internal Named Stages¶
Internal stages are named database objects that provide the greatest degree of flexibility for data loading. Because they are database objects, the security/access rules that apply to all objects apply:
- Users with the appropriate privileges on the stage can load data into any table.
- Ownership of the stage can be transferred to another role, and privileges granted to use the stage can be modified to add or remove roles.
- When you create a stage, you must explicitly grant privileges on the stage to one or more roles before users with those roles can use the stage.
If you plan to stage data files that will be loaded only by you, or will be loaded only into a single table, then you may prefer to simply use either your user stage or the stage for the table into which you will be loading data.
Named internal stages are optional but recommended when you plan regular data loads that could involve multiple users and/or tables. For instructions on creating a named stage, see Creating a Stage below.
Creating a Stage¶
You can create a named internal stage using either the web interface or SQL:
Web Interface: Click on Databases » <db_name> » Stages SQL: CREATE STAGE
Creating a Named Stage¶
The following example creates an internal stage that references the named file format object called
my_csv_format that was created in Preparing to Load Data:
CREATE OR REPLACE STAGE my_stage FILE_FORMAT = my_csv_format;
By specifying a named file format object (or individual file format options) for the stage, it isn’t necessary to later specify the same file format options in the COPY command used to load data from the stage.
The following example creates an internal stage that specifies ad hoc file format options rather than referencing a named file format. Data files in this stage have a CSV format and a pipe (
|) field delimiter. When this stage is referenced, the COPY command skips the first line in the data files:
CREATE OR REPLACE STAGE my_stage file_format = (type = 'CSV' FIELD_DELIMITER = '|' SKIP_HEADER = 1);