On this Page
Overview
Snap type: | Read | |||||||
---|---|---|---|---|---|---|---|---|
Description: | This Snap unloads the result of a query to a file or files stored on the Snowflake stage, or on an external S3 workspace, or on Azure Storage Blob, if required. The target Snowflake table is not modified. Once the data is unloaded to the storage, users can get the file either using Snowflake GET or S3 file reader. Expected upstream Snaps: Any Snap with a document output view Expected downstream Snaps: Any Snap with a document input view, such as JSON Formatter, Mapper, and so on. The CSV Formatter Snap cannot be connected directly to this Snap since the output document map data is not flat. Expected input: Key-value map data to evaluate expression properties of the Snap Expected output: Document with Snowflake response, query command and the location Url for the unloaded file. The Snap behaves the same on a Groundplex as it does in a Cloudplex. | |||||||
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 Prerequisites: You should have the following permission in your Snowflake account to execute this Snap:
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>; For more information on Snowflake privileges, refer to Access Control Privileges. The below are mandatory when using an external staging location: When using an Amazon S3 bucket for storage:
When using a Microsoft Azure storage blob:
| |||||||
Internal SQL Commands | This Snap uses the COPY INTO command internally. It enables unloading data from a table (or query) into one or more files in one of the following locations:
| |||||||
Support and limitations: |
| |||||||
Account: | This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. This Snap requires a Snowflake Account with S3 or Microsoft Azure properties. See Snowflake Account for information on setting up this type of account. | |||||||
Views: |
| |||||||
Settings | ||||||||
Label | Required. The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline. | |||||||
Query | Required. Defines 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. Example: SELECT * FROM public.company ORDER BY id Default value: None | |||||||
Staging Location | The staging location for the unload. The expected input for this should be a path to a file or filename-prefix. The options available include:
Default value: Internal | |||||||
Target | Required. 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. Example: TestFolder/file_name_prefix Default value: None | |||||||
Storage Integration | The pre-defined storage integration is used to authenticate the external stages. Default value: None | |||||||
File Format Type | The format type for the unloaded file. The options available are None, CSV, and CUSTOMIZED. If CUSTOMIZED is chosen, then the customized format identifier must be specified. Default value: None | |||||||
Customized format identifier | 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. | |||||||
File format option | Specify the file format option. Separate multiple options by using blank spaces and commas. 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 | |||||||
Copy options | ||||||||
Overwrite | If selected, the UNLOAD overwrites the existing files, if any, in the location where files are stored. If unselected, the option does not remove the existing files or overwrite. Default value: Not selected | |||||||
Generate single file | 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 value: Not selected | |||||||
Max file size | 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 | |||||||
Encryption type | Specifies the type of encryption to be used on the data. The available encryption options are:
Default value: No default value. The KMS Encryption option is available only for S3 Accounts (not for Azure Accounts) with Snowflake. | |||||||
KMS key | The KMS key that you want to use for S3 encryption. For more information about the KMS key, see AWS KMS Overview and Using Server Side Encryption. Default value: No default value. This property applies only when you select Server-Side KMS Encryption in the Encryption Type field above. | |||||||
Include column headings | 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 value: Not selected | |||||||
Validation Mode | This mode is useful for visually verifying the data before unloading it. If this is 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:
Default value: NONE | |||||||
Manage Queued Queries | Select this property to decide whether the Snap should continue or cancel the execution of the queued Snowflake Execute SQL queries when you stop the pipeline. 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 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 | |||||||
Snap execution | Page lookup error: page "Anaplan Read" not found. If you're experiencing issues please see our Troubleshooting Guide. |
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.
Examples
Unloading Data (Including Binary Data Types) From Snowflake Database
The following example Pipeline demonstrates how you can unload binary data as a file and load it into an S3 bucket.
First, we configure the Snowflake - Unload Snap by providing the following query:
select * from EMP2
–
this query unloads data from EMP2 table.
Note that we set the File format option as BINARY_FORMAT='UTF-8' to enable the Snap to pass binary data.
Upon validation, the Snap shows the unloadRequest and unloadDestination in its preview.
We connect the JSON Formatter Snap to Snowflake - Unload Snap to transform the binary data to JSON format, and finally write this output to a file in S3 bucket using the File Writer Snap. Upon validation, the File Writer Output Snap writes the output (unload request)- the output preview is as shown below.
The following example illustrates the usage of the Snowflake Unload Snap.
In this example, we run the Snowflake SQL query using the Snowflake Unload Snap. The Snap selects the data from the table, @ADOBEDATA2 and writes the records to the table, adobedatanullif using the File Writer.
Connect the JSON formatter Snap to convert the predefined CSV file format to JSON and write the data using the File Writer Snap.
Successful execution of the pipeline gives the below output preview:
Release | Snap Pack Version | Date | Type | Updates |
---|---|---|---|---|
May 2024 | 437patches26508 |
| Latest |
|
May 2024 | main26341 |
| Stable |
|
February 2024 | 436patches25630 |
| Latest |
|
February 2024 | main25112 |
| Stable | Updated and certified against the current SnapLogic Platform release. |
November 2023 | 435patches24865 |
| Latest | Fixed an issue across the Snowflake Snaps that populated all suggestions for the Schema and Table Names existing in the configured Snowflake Account. Now, the Snaps only populate suggestions related to the database configured in the Account. |
November 2023 | 435patches24110 |
| Latest | Added a lint warning to the Snowflake-Bulk Load Snap that recommends users to select the Purge checkbox when the Data source is input view and the Staging location is External. |
November 2023 | main23721 |
| Stable | The Snowflake Snap Pack is now bundled with the default Snowflake JDBC driver v3.14. |
August 2023 | 434patches23541 |
| Latest | Fixed an issue with the Snowflake-Bulk Load Snap where the Snap wrote irrelevant errors to the error view when both of the following conditions occurred:
Now, the Snap writes the correct errors to the error view. |
August 2023 | main22460 |
| Stable | The Snowflake - Execute Snap now includes a new Query type field. When Auto is selected, the Snap tries to determine the query type automatically. |
May 2023 | 433patches21890 |
| Latest |
|
May 2023 | 433patches21370 |
| Latest |
|
May 2023 | main21015 |
| Stable |
|
February 2023 | 432patches20906 |
| Latest |
|
February 2023 | 432patches20266 |
| Latest | Fixed an issue with the Snowflake - Bulk Load Snap that resulted in lowercase (or mixed case) column names when creating a new table under specific conditions. The new Create table with uppercase column names checkbox addresses this issue. |
February 2023 | 432patches20120 |
| Latest | The Snowflake Bulk Load, Bulk Upsert, and Unload Snaps now support expressions for the Staging location field. |
February 2023 | main19844 |
| Stable |
|
November 2022 | 431patches19581 |
| Latest |
|
November 2022 | 431patches19454 |
| Latest | The Snowflake Snap Pack supports geospatial data types. As the Snowflake Snap Pack requires using our custom Snowflake JDBC driver for full support of all data types, contact support@snaplogic.com for details. |
November 2022 | 431patches19220 |
| Latest | The Snowflake S3 OAuth2 Account now support expressions for external staging fields. |
November 2022 | 431patches19220
|
| Latest |
|
November 2022 | main18944 |
| Stable |
|
November 2022 | 430patches18911 |
| Latest | 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. |
October 2022 | 430patches18781 |
| Latest | The Snowflake Insert and Snowflake Bulk Upsert Snaps now do not fail with the The Snowflake Bulk Load Snap now works as expected when you configure On Error with SKIP_FILE_*error_percent_limit*% and set the Error Percent Limit to more than the percentage of rows with invalid data in the CSV file. |
October 2022 | 430patches18432 |
| Latest | The Snowflake Bulk Load Snap now has a Validation Errors Type dynamic field, which provides options for displaying validation errors. You can now choose Aggregate errors per row to display a summary view of errors. |
September 2022 | 430patches17962 |
| Latest | The Snowflake Bulk Load Snap now triggers the metadata query only once even for invalid input, thereby improving the performance of Snap. |
September 2022 | 430patches17894 |
| Latest | The Snowflake Select Snap now works as expected when the table name is dependent on an upstream input. |
August 2022 | 430patches17748 |
| Latest | Fixes in the Snowflake Bulk Load Snap:
|
August 2022 | 430patches17377 |
| Latest |
|
August 2022 | main17386 |
| Stable | The following Snowflake Accounts support Key Pair Authentication. |
4.29 Patch | 429patches16478 |
| Latest | Fixed an issue with Snowflake - Bulk Load Snap where Snap failed with 301 status code - SnapDataException. |
4.29 Patch | 429patches16458 |
| Latest |
|
4.29 | main15993 |
| Stable | Upgraded with the latest SnapLogic Platform release. |
4.28 Patch | 428patches15236 |
| Latest |
|
4.28 | main14627 |
| Stable |
|
4.27 | 427patches12999 |
| Latest | Enhanced the Snowflake SCD2 Snap to support Pipeline parameters for Natural key and Cause-historization fields. |
4.27 | main12833 |
| Stable |
|
4.26 Patch | 426patches11469 |
| Latest | Fixed an issue with Snowflake Insert and Snowflake Bulk Load Snaps where the schema names or database names containing underscore (_) caused the time out of Pipelines. |
4.26 | main11181 |
| Stable |
|
4.25 | 425patches10190 | Latest | Enhanced the Snowflake S3 Database and Snowflake S3 Dynamic accounts with a new field S3 AWS Token that allows you to connect to private and protected Amazon S3 buckets. | |
4.25 | main9554 |
| Stable | Upgraded with the latest SnapLogic Platform release. |
4.24 Patch | 424patches8905 |
| Latest | Enhanced the Snowflake - Bulk Load Snap to allow transforming data using a new field Select Query before loading data into the Snowflake database. This option enables you to query the staged data files by either reordering the columns or loading a subset of table data from a staged file. This Snap supports CSV and JSON file formats for this data transformation. |
4.24 | main8556 | Stable | Enhanced the Snowflake - Select Snap to return only the selected output fields or columns in the output schema (second output view) using the Fetch Output Fields In Schema check box. If the Output Fields field is empty all the columns are visible. | |
4.23 Patch | 423patches7905 |
| Latest | Fixed the performance issue in the Snowflake - Bulk Load Snap while using External Staging on Amazon S3. |
4.23 | main7430 |
| Stable |
|
4.22 Patch | 422patches7246 |
| Latest | Fixed an issue with the Snowflake Snaps that failed while displaying similar error in the Snowflake URL connection: |
4.22 Patch | 422patches6849 |
| Latest |
|
4.22 | main6403 |
| Stable | Updated with the latest SnapLogic Platform release. |
4.21 Patch | 421patches6272 |
| Latest | Fixes the issue where Snowflake SCD2 Snap generates two output documents despite no changes to Cause-historization fields with DATE, TIME and TIMESTAMP Snowflake data types, and with Ignore unchanged rows field selected. |
4.21 Patch | 421patches6144 |
| Latest |
|
4.21 Patch | db/snowflake8860 |
| Latest | Added a new field, Handle Timestamp and Date Time Data, to Snowflake Lookup Snap. This field enables you to decide whether the Snap should translate UTC time to your local time and the format of the Date Time data. |
4.21 Patch | MULTIPLE8841 |
| Latest | Fixed the connection issue in Database Snaps by detecting and closing open connections after the Snap execution ends. |
4.21 | snapsmrc542 |
| Stable | Upgraded with the latest SnapLogic Platform release. |
4.20 Patch | db/snowflake8800 |
| Latest |
|
4.20 Patch | db/snowflake8758 |
| Latest | Re-release of fixes from db/snowflake8687 for 4.20: Fixes the Snowflake Bulk Load snap where the Snap fails to load documents containing single quotes when the Load empty strings checkbox is not selected. |
4.20 | snapsmrc535 |
| Stable | Upgraded with the latest SnapLogic Platform release. |
4.19 Patch | db/snowflake8687 |
| Latest | Fixed the Snowflake Bulk Load snap where the Snap fails to load documents containing single quotes when the Load empty strings checkbox is not selected. |
4.19 Patch | db/snowflake8499 |
| Latest | Added the property Handle Timestamp and Date Time Data to Snowflake - Execute and Snowflake - Select Snaps. This property enables you to decide whether the Snap should translate UTC time to your local time. |
4.19 Patch | db/snowflake8412 |
| Latest | Fixed an issue with the Snowflake - Update Snap wherein the Snap is unable to perform operations when:
|
4.19 | snaprsmrc528 |
| Stable |
|
4.18 Patch | db/snowflake8044 |
| Latest | Fixed an issue with the Snowflake - Select Snap wherein the Snap converts the Snowflake-provided timestamp value to the local timezone of the account. |
4.18 Patch | db/snowflake8044 |
| Latest | Enhanced the Snap Pack to support AWS SDK 1.11.634 to fix the NullPointerException issue in the AWS SDK. This issue occurred in AWS-related Snaps that had HTTP or HTTPS proxy configured without a username and/or password. |
4.18 Patch | MULTIPLE7884 |
| Latest | Fixed an issue with the PostgreSQL grammar to better handle the single quote characters. |
4.18 Patch | db/snowflake7821 |
| Latest | Fixed an issue with the Snowflake - Execute Snap wherein the Snap is unable to support the '$' character in query syntax. |
4.18 Patch | MULTIPLE7778 |
| Latest | Updated the AWS SDK library version to default to Signature Version 4 Signing process for API requests across all regions. |
4.18 Patch | db/snowflake7739 |
| Latest |
|
4.18 | snapsmrc523 |
| Stable | Added the Use Result Query property to the Multi Execute Snap, which enables you to write results to an output view. |
4.17 | ALL7402 |
| Latest | Pushed automatic rebuild of the latest version of each Snap Pack to SnapLogic UAT and Elastic servers. |
4.17 Patch | db/snowflake7396 |
| Latest | Fixed an issue wherein bit data types in the Snowflake - Select table convert to true or false instead of 0 or 1. |
4.17 Patch | db/snowflake7334 |
| Latest | Added AWS Server-Side Encryption support for AWS S3 and AWS KMS (Key Management Service) for Snowflake Bulk Load, Snowflake Bulk Upsert, and Snowflake Unload Snaps. |
4.17 | snapsmrc515 |
| Latest |
|
4.16 Patch | db/snowflake6945 |
| Latest | Fixed an issue with the Snowflake Lookup Snap failing when Date datatype is used in JavaScript functions. |
4.16 Patch | db/snowflake6928 |
| Latest | Added support for file format options for input data from upstream Snaps, to the Snowflake Bulk Load Snap. |
4.16 Patch | db/snowflake6819 |
| Latest |
|
4.16 | snapsmrc508 |
| Stable |
|
4.15 | snapsmrc500 |
| Stable |
|
4.14 | snapsmrc490 |
| Stable | Upgraded with the latest SnapLogic Platform release. |
4.13 | snapsmrc486 |
| Stable | Upgraded with the latest SnapLogic Platform release. |
4.12 | snapsmrc480 |
| Stable | Upgraded with the latest SnapLogic Platform release. |
4.11 Patch | MULTIPLE4377 |
| Latest | Fixed a document call issue that was slowing down the Snowflake Bulk Load Snap. |
4.11 Patch | db/snowflake4283 |
| Latest | Snowflake Bulk Load - Fixed an issue by adding PUT command to the list of DDL command list for Snowflake. |
4.11 Patch | db/snowflake4273 |
| Latest | Snowflake Bulk Load - Resolved an issue with Snowflake Bulk Load Delimiter Consistency (comma and newline). |
4.11 | snapsmrc465 |
| Stable | Upgraded with the latest SnapLogic Platform release. |
4.10 Patch | snowflake4133 |
| Latest | Updated the Snowflake Bulk Load Snap with Preserve case sensitivity property to preserve the case sensitivity of column names. |
4.10 | snapsmrc414 |
| Stable |
|
4.9.0 Patch | snowflake3234 |
| Latest | Enhanced Snowflake - Execute Snap results to include additional details |
4.9.0 Patch | snowflake3125 |
| Latest | Addressed an issue in Snowflake Bulk Load where the comma character in a value is not escaped. |
4.9 | snapsmrc405 |
| Stable | JDBC Driver Class property added to enable the user to custom configure the JDBC driver in the Database and the Dynamic accounts. |
4.8.0 Patch | snowflake2760 |
| Latest | Potential fix for JDBC deadlock issue. |
4.8.0 Patch | snowflake2739 |
| Latest | Addressed an issue with the Snowflake schema not correctly represented in the Mapper Snap. |
4.8 | snapsmrc398 |
| Stable |
|