Skip to main content

Snowflake - Upload File to Table from Shipyard

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. If there are any errors with this method, we revert to a slow but consistent method that creates INSERT statements for 10,000 rows at a time.

In all instances, if the table name does not already exist, a new table will be created with datatypes 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 Shipyard or generates a file with code.

Variables​

NameReferenceTypeRequiredDefaultOptionsDescription
UsernameSNOWFLAKE_USERNAMEAlphanumericβœ…--
PasswordSNOWFLAKE_PASSWORDPasswordβœ…--Password for the provided username
Account NameSNOWFLAKE_ACCOUNTAlphanumericβœ…--
WarehouseSNOWFLAKE_WAREHOUSEAlphanumericβž–--
DatabaseSNOWFLAKE_DATABASEAlphanumericβœ…--
SchemaSNOWFLAKE_SCHEMAAlphanumericβž–--
Shipyard Folder NameSNOWFLAKE_SOURCE_FOLDER_NAMEAlphanumericβž–--Folder where the file to upload can be found. Leaving blank will search in the current working directory.
Shipyard File Name Match TypeSNOWFLAKE_SOURCE_FILE_NAME_MATCH_TYPESelectβœ…exact_matchExact Match: exact_match

Regex Match: regex_match

Determines if the text in "File Name" will match to one or multiple files.
Shipyard File NameSNOWFLAKE_SOURCE_FILE_NAMEAlphanumericβœ…--Name of the file to upload to the specified table
Table NameSNOWFLAKE_TABLE_NAMEAlphanumericβœ…--Name of the table where you want data inserted
Insertion MethodSNOWFLAKE_INSERT_METHODSelectβœ…appendAppend Data: append

Replace Data: replace

Add Data Only if Table is Empty: fail

Determines how the data in your file will be added to the table
Snowflake Data TypesSNOWFLAKE_DATA_TYPESAlphanumericβž–--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

YAML​

Below is the YAML template for this Blueprint and can be used in the Fleet YAML Editor.

source:
blueprint: Snowflake - Upload CSV to Table
inputs:
SNOWFLAKE_USERNAME: null ## REQUIRED
SNOWFLAKE_PASSWORD: null ## REQUIRED
SNOWFLAKE_ACCOUNT: null ## REQUIRED
SNOWFLAKE_WAREHOUSE: null
SNOWFLAKE_DATABASE: null ## REQUIRED
SNOWFLAKE_SCHEMA: null
SNOWFLAKE_SOURCE_FOLDER_NAME: null
SNOWFLAKE_SOURCE_FILE_NAME_MATCH_TYPE: exact_match ## REQUIRED
SNOWFLAKE_SOURCE_FILE_NAME: null ## REQUIRED
SNOWFLAKE_TABLE_NAME: null ## REQUIRED
SNOWFLAKE_INSERT_METHOD: append ## REQUIRED
SNOWFLAKE_DATA_TYPES: null
type: BLUEPRINT
guardrails:
retry_count: 1
retry_wait: 0s
runtime_cutoff: 1h0m0s
exclude_exit_code_ranges:
- "200-207"