Snowflake - Unload

Overview

You can use the Snowflake - Unload Snap to unload the result of a query to a file or files stored on the Snowflake stage, Google Cloud Storage, an external S3 workspace, or on Azure Storage Blob if required. The target Snowflake table is not modified. After the data is unloaded to the storage, you can get the file either using Snowflake GET or an S3 File Reader Snap.

Note: Snowflake provides the option to use the Cross Account IAM into the external staging. You can adopt the cross-account access through Storage Integration option . With this setup, you don’t need to pass any credentials and access the storage only using the named stage or integration object. Learn more: Configuring Cross Account IAM Role Support for Snowflake Snaps.

Read-type Snap
Works in Ultra Tasks

Prerequisites

You must have minimum permissions on the database to execute Snowflake Snaps. To understand if you already have them, you must retrieve the current set of permissions. The following commands enable you to retrieve those permissions:

SHOW GRANTS ON DATABASE <database_name>
        SHOW GRANTS ON SCHEMA <schema_name>
        SHOW GRANTS TO USER <user_name>

Security Prererequisites

You must have the following permissions in your Snowflake account to execute this Snap:

Usage (DB and Schema): Privilege to use the database, role, and schema.

The following commands enable minimum privileges in the Snowflake Console:

grant usage on database <database_name> to role <role_name>;
         grant usage on schema <database_name>.<schema_name>;

Learn more about Snowflake privileges: Access Control Privileges.

Internal SQL Commands

This Snap uses the COPY INTO and SELECT commands internally. It enables unloading data from a table (or query) into one or more files in one of the following locations:

Named internal stage (or table/user stage).
Named external stage, that references external location, such as, Amazon S3, Google Cloud Storage, or Microsoft Azure.
External location, such as, Amazon S3, Google Cloud Storage, or Microsoft Azure.

Requirements for External Storage Location

The below are mandatory when using an external staging location:

When using an Amazon S3 bucket for storage:
- The Snowflake account should contain S3 Access-key ID, S3 Secret key, S3 Bucket, and S3 Folder.
- The Amazon S3 bucket where the Snowflake will write the output files must reside in the same region as your cluster.
When using a Microsoft Azure storage blob:
- A working Snowflake Azure database account.
When using a Google Cloud Storage:
- Provide permissions such as Public access and Access control to the Google Cloud Storage bucket on the Google Cloud Platform.

Known Issues

Because of performance issues, all Snowflake Snaps now ignore the Cancel queued queries when pipeline is stopped or if it fails option for Manage Queued Queries, even when selected. Snaps behave as though the default Continue to execute queued queries when the Pipeline is stopped or if it fails option were selected.

Snap views


View	Description	Examples of upstream and downstream Snaps
Input	A JSON document containing parameterized inputs for the Snap’s configuration, if needed.	Mapper Copy JSON Generator
Output	A JSON document containing the unload request details and the result of the unload operation.	JSON Parser CSV Parser Mapper
Error	Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter when running the pipeline by choosing one of the following options from the When errors occur list under the Views tab. The available options are: Stop Pipeline Execution Stops the current pipeline execution when an error occurs. Discard Error Data and Continue Ignores the error, discards that record, and continues with the remaining records. Route Error Data to Error View Routes the error data to an error view without stopping the Snap execution. Learn more about Error handling in Pipelines.

Snap settings

Legend:

Expression icon (): Allows using JavaScript syntax to access SnapLogic Expressions to set field values dynamically (if enabled). If disabled, you can provide a static value. Learn more.
SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
Upload : Uploads files. Learn more.

Learn more about the icons in the Snap settings dialog.


Field / Field set	Type	Description
Label	String	Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if there are more than one of the same Snap in the pipeline.
Query	String/Expression	Required. Specify a SELECT query. The results of the query are unloaded. In most cases, it is worthwhile to unload data in sorted order by specifying an ORDER BY clause in the query; this approach will save the time Required. to sort the data when it is reloaded. Default value: N/A Example: SELECT * FROM public.company ORDER BY id
Staging location	Dropdown list/Expression	Select the type of staging location that is to be used for data loading: External: Location that is not managed by Snowflake. The location should be an AWS S3 Bucket or Microsoft Azure Storage Blob or Google Cloud Storage. These credentials are mandatory while validating the Account. Internal: Location that is managed by Snowflake. Default value: Internal Example: External
Target	String/Expression	Specify the staging area where the unloaded file(s) are placed. If the staging location is external, it will be put under the S3 Bucket or Microsoft Azure Storage Blob specified for the Account. If the staging location is internal, the files will be placed in the user’s home folder. Default value: N/A Example: TestFolder/file_name_prefix
Storage Integration	String/Expression	Specify the preconfigured Storage Integration that is used to authenticate the external settings. Default value: N/A Example: Demo_Storage_Int
File Format Type	Dropdown list	The format type for the unloaded file. The options available are None, CSV, and CUSTOMIZED. Note: If CUSTOMIZED is chosen, then the customized format identifier must be specified. Default value: None Example: CSV
Customized format identifier	String	Specify the file format object to use for unloading data from the table. The field is valid only when the File format type is specified as Customized. Otherwise, this will be ignored. Default value: None Example: CSV
File format option	String	Specify the file format option. Separate multiple options by using blank spaces and commas. Note: You can use various file format options including binary format which passes through in the same way as other file formats. See File Format Type Options for more information. Before loading binary data into Snowflake, you must specify the binary encoding format, so that the Snap can decode the string type to binary types before loading into Snowflake. This can be done by specifying the following binary file format: `BINARY_FORMAT=xxx (Where XXX = HEX\|BASE64\|UTF-8)` However, the file you upload and download must be in similar formats. For instance, if you load a file in HEX binary format, you should specify the HEX format for download as well. Default value: None Example: Binary CSV JSON
Overwrite	Checkbox	If selected, the UNLOAD overwrites the existing files, if any, in the location where files are stored. If deselected, the option does not remove the existing files or overwrite. Default status: Deselected
Generate single file	Checkbox	If selected, the UNLOAD will generate a single file. If it is not selected, the filename prefix needs to be included in the path. Default status: Deselected
Max file size		Specify the maximum size (in bytes) of each file to be generated in parallel per thread. The number should be greater than 0, If it is less than or equals 0, the Snap will use the default size for snowflake: 16000000 (16MB). Default value: 0 Example: 10
Encryption type	Dropdown list	Default value: None Example: Server-Side Encryption Specify the type of encryption to be used on the data. The available encryption options are: None: Files do not get encrypted. Server Side Encryption: The output files on Amazon S3 are encrypted with server-side encryption. Server-Side KMS Encryption: The output files on Amazon S3 are encrypted with an Amazon S3-generated KMS key. The KMS Encryption option is available only for S3 Accounts (not for Azure Accounts and GCS) with Snowflake. Default value: None Example: Server Side Encryption
KMS key	String/Expression	Specify the KMS key that you want to use for S3 encryption. Learn more about the KMS key: AWS KMS Overview and Using Server Side Encryption. Note: This property applies only when you select Server-Side KMS Encryption in the Encryption Type field above. Default value: N/A Example: 28e3c2b6-74e2-4a3e-9890-6cd8e1c03661
Include column headings	Checkbox	If selected, the table column heading will be included in the generated files. If multiple files are generated, the heading will be included in every file. Default status: Deselected
Validation Mode	Dropdown list	Select this mode for visually verifying the data before unloading it. If you select NONE, validation mode is disabled or, the unloaded data will not be written to the file. Instead, it will be sent as a response to the output. The options available include: None RETURN_ROWS
Manage Queued Queries	Dropdown list	Default value: Choose an option to determine whether the Snap should continue or cancel the execution of the queued queries when the pipeline stops or fails. Note: If you select Cancel queued queries when the pipeline is stopped or if it fails, then the read queries under execution are canceled, whereas the write type of queries under execution are not canceled. Snowflake internally determines which queries are safe to be canceled and cancels those queries. Default value: Continue to execute queued queries when the Pipeline is stopped or if it fails Example: Cancel queued queries when the pipeline is stopped or if it fails
Snap execution	Dropdown list	Choose one of the three modes in which the Snap executes. Available options are: Validate & Execute. Performs limited execution of the Snap and generates a data preview during pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during pipeline runtime. Execute only. Performs full execution of the Snap during pipeline execution without generating preview data. Disabled. Disables the Snap and all Snaps that are downstream from it.

Note: The Snap behaves the same on a Groundplex as it does in a Cloudplex.

Troubleshooting

The preview on this Snap will not execute the Snowflake UNLOAD operation. Connect a JSON Formatter and a File Writer Snaps to the error view and then execute the pipeline. If there is any error, you will be able to preview the output file in the File Writer Snap for the error information.