Snowflake - Upload File to Table from Workflows
Overview
Upload one or more CSV files to any table in Snowflake. With the file data, you can: - **Append Data** - Add the contents of your file to the end of the table. - **Replace Data** - Drop the existing table and create a new table that contains the entire contents of your file. - **Add Data Only if Table is Empty** - Add data to the table if no data exists. Column names are inferred from the header row of your CSV file. If the table already exists, the header values are matched to the table column names. The header names must match the existing column names in your table, case insensitive. If a header name doesn't match, only the data associated with that specific column will not be uploaded. Column names and table names are automatically made into upper case values, a standard for Snowflake. Data is inserted into the table by PUTing the file on Snowflake temporary staging for the named table, running a COPY INTO function, then purging the file from Snowflake staging. In all instances, if the table name does not already exist, a new table will be created with datatypes provided, otherwise they are inferred from the CSV contents. **Note:** This Vessel cannot be used to upload a local file from your computer. **Recommended Setup:** 1. A Vessel built with this Blueprint should typically run after a Vessel that either downloads a file to Workflows or generates a file with code.
Variables
Name | Reference | Type | Required | Default | Options | Description |
---|---|---|---|---|---|---|
Username | SNOWFLAKE_USERNAME | Alphanumeric | ✅ | None | - | The Snowflake Username that has access to the table, schema, and warehouse that you want to use. |
Password | SNOWFLAKE_PASSWORD | Password | ➖ | None | - | Password for the provided username |
Account Name | SNOWFLAKE_ACCOUNT | Alphanumeric | ✅ | None | - | Typically found in the URL you use to access Snowflake, before `.snowflakecomputing.com`. |
Warehouse | SNOWFLAKE_WAREHOUSE | Alphanumeric | ➖ | None | - | |
Database | SNOWFLAKE_DATABASE | Alphanumeric | ✅ | None | - | |
Schema | SNOWFLAKE_SCHEMA | Alphanumeric | ➖ | None | - | |
Workflows Folder Name | SNOWFLAKE_SOURCE_FOLDER_NAME | Alphanumeric | ➖ | None | - | Folder where the file to upload can be found. Leaving blank will search in the current working directory. |
Workflows File Name Match Type | SNOWFLAKE_SOURCE_FILE_NAME_MATCH_TYPE | Select | ✅ | exact_match | Exact Match: exact_match Regex Match: regex_match | Determines if the text in "File Name" will match to one or multiple files. |
Workflows File Name | SNOWFLAKE_SOURCE_FILE_NAME | Alphanumeric | ✅ | None | - | Name of the file to upload to the specified table |
Table Name | SNOWFLAKE_TABLE_NAME | Alphanumeric | ✅ | None | - | Name of the table where you want data inserted |
Insertion Method | SNOWFLAKE_INSERT_METHOD | Select | ✅ | append | Append Data: append Replace Data: replace Add Data Only if Table is Empty: add | Determines how the data in your file will be added to the table |
Snowflake Data Types | SNOWFLAKE_DATA_TYPES | Alphanumeric | ➖ | None | - | The option to declare the Snowflake datatypes. The input needs to be similar to a list of lists, which will be used to form a CREATE OR REPLACE TABLE statement. For more information on Snowflake data types, visit https://docs.snowflake.com/en/sql-reference/intro-summary-data-types.html |
User Role | SNOWFLAKE_USER_ROLE | Alphanumeric | ➖ | - | - | The optional role for the database user |
Private Key | SNOWFLAKE_PRIVATE_KEY | Alphanumeric | ➖ | - | - | The optional private key to use for authentication |
Private Key Passphrase | SNOWFLAKE_PRIVATE_KEY_PASSPHRASE | Password | ➖ | - | - | The passphrase for the private key file. Is required only if authenticating with a private key |
YAML
Below is the YAML template
source:
template: Snowflake - Upload File to Table from Workflows
inputs:
SNOWFLAKE_USERNAME:
SNOWFLAKE_PASSWORD:
SNOWFLAKE_ACCOUNT:
SNOWFLAKE_WAREHOUSE:
SNOWFLAKE_DATABASE:
SNOWFLAKE_SCHEMA:
SNOWFLAKE_SOURCE_FOLDER_NAME:
SNOWFLAKE_SOURCE_FILE_NAME_MATCH_TYPE: exact_match
SNOWFLAKE_SOURCE_FILE_NAME:
SNOWFLAKE_TABLE_NAME:
SNOWFLAKE_INSERT_METHOD: append
SNOWFLAKE_DATA_TYPES:
SNOWFLAKE_USER_ROLE:
SNOWFLAKE_PRIVATE_KEY:
SNOWFLAKE_PRIVATE_KEY_PASSPHRASE:
type: TEMPLATE
guardrails:
retry_count: 1
retry_wait: 0h0m0s
runtime_cutoff: 1h0m0s
exclude_exit_code_ranges:
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 249