STAGE
Config path: /<database>/<schema>/stage/<name>.yaml
Example for internal stage:
Example of external stage:
Schema
url (str) - bucket URL for external stage
storage_integration (ident) - name of storage integration for external stage
encryption (dict)
{key} (ident) - name of encryption parameter
{value} (bool, float, int, str) - value of encryption parameter
directory (dict)
{key} (ident) - name of directory parameter
{value} (bool, float, int, str) - value of directory parameter
file_format (ident) - file format for files in stage
copy_options (dict)
{key} (ident) - COPY option name
{value} (bool, float, int, list, str) - COPY option value
comment (str)
Usage notes
Stages with url and storage_integration are EXTERNAL. Stages without these parameters are INTERNAL.
When INTERNAL stage is being dropped, all files will be lost. When EXTERNAL stage is being dropped, nothing happens. EXTERNAL stage is just a metadata pointing to location in bucket.
Business users should only access buckets through stages. Business users should never use
STORAGE INTEGRATION
objects directly for security reasons.SnowDDL is able to maintain not only stages in general, but also specific files in stages.
file_format can only be specified by name referencing to
FILE_FORMAT
object.
Additional privileges
In order for STAGE
objects to operate properly, the following additional grants should be added to OWNER role in schema config:
owner_integration_usage
- please specify names of STORAGE INTEGRATION objects used by external stages. Stages may work without explicit INTEGRATION USAGE grant to OWNER role, but it is not guaranteed for Snowflake keep it this way forever.
Encryption updates
Encryption on existing EXTERNAL stages cannot be updated by SnowDDL. Snowflake does not provide meta-data, so it is not possible to compare config with existing object.
In order to update encryption information manually, you should use an additional CLI argument:
--refresh-stage-encryption
If you want to remove an encryption from EXTERNAL stage, you should set encryption type to NONE
explicitly. It is not enough to remove encryption
section from config, since default encryption settings are different for each cloud provider and may change in future.
Safe & unsafe operations
All operations on stages are considered as "safe", EXCEPT:
Replace existing stage due to change of stage type (INTERNAL to EXTERNAL or vice versa) or due to change of INTERNAL stage encryption method.
Drop existing stage, both INTERNAL and EXTERNAL.
"Unsafe" operations on stages lead to loss of data or meta-data. Also, it may affect other objects depending on stages, like PIPES or EXTERNAL TABLES.
Links
Last updated