Azure Blob Storage Load (Snowflake)
This article is specific to the following platforms - Snowflake.
Azure Blob Storage Load
Note: This component is only available for Matillion ETL instances hosted on Microsoft Azure.
The Azure Blob Storage Load component lets users load data into an existing table from objects stored in Azure Blob Storage.
|Name||Text||The descriptive name for the component.|
|Stage||Select||Select a staging area for the data. Staging areas can be created through Snowflake using the CREATE STAGE command. Internal stages can be setup this way to store staged data within Snowflake.
Selecting [Custom] will avail the user of properties to specify a custom staging area on Azure Blob Storage
|Azure Storage Location||Text||The Azure storage location (including file path) for any data to be loaded.|
|Pattern||Text||A string that will partially match all filenames that are to be included in the load. Defaults to '.*' indicating all files within the Azure Storage Location.|
|Warehouse||Select||Choose a Snowflake warehouse that will run the load. [Environment Default] will use the Warehouse defined in the environment.|
|Database||Select||Select the database that the newly-created table will belong to. [Environment Default] will use the Database defined in the environment.|
|Schema||Select||Select the table schema. The special value, [Environment Default] will use the schema defined in the environment. For more information on using multiple schemas, see this article.|
|Table||Select||Select an existing table to load data into.|
|Load Columns||Select Multiple||Choose the columns that will be loaded into the target table. Choosing no columns will result in all columns being loaded.|
|Format||Select||Select a premade file format that will automatically set many of the properties accordingly. These formats can be created through the Create File Format component.|
|File Type||Select||Select the expected file type that data will be loaded from. Supports AVRO, CSV, JSON, ORC, PARQUET and XML. Component properties will change to reflect the choice made here and give options based on the specific file type.|
|Compression||Select||Whether the input file is compressed in GZIP format, BROTLI, BZ2, DEFLATE, RAW_DEFLATE, ZSTD or not compressed at all.|
|Record Delimiter||Text||The delimiter to be used that separates records (rows) in the file. Defaults to newline. \n can also signify a newline. \r can signify a carriage return.|
|Field Delimiter||Text||The delimiter that separates columns. The default is a Comma. A [TAB] character can be specified as "\t".|
|Skip Header||Text||The number of rows at the top of the file to ignore - defaults to 0.|
|Date Format||Text||Defaults to 'auto' - this can be used to manually specify a date format.|
|Time Format||Text||Defaults to 'auto' - this can be used to manually specify a time format.|
|Timestamp Format||Text||Defaults to 'auto' - this can be used to manually specify a timestamp format.|
|Escape||Text||Single character used as the escape character for any field values.|
|Escape Unenclosed Field||Text||Single character string used as the escape character for unenclosed field values only. Default is backslash (\).|
|Trim Space||Select||Removes trailing and leading whitespace from the input data.|
|Field Optionally Enclosed||Text||A character that is used to enclose strings. Can be single quote (') or double quote (") or NONE (default). Note that the character chosen can be escaped by that same character.|
|Null If||Text||This option replaces the specified string with null in the output table. Use this is your data has a particular representation of missing data.|
|Error On Column Count Mismatch||Select||Generate an error if the number of delimited columns in the input does not match that of the table. If false, extra columns are not loaded into the table and missing columns are recorded as NULL in the table.|
|Empty Field As Null||Select||If this is set, empty columns in the input file will become NULL.|
|Encoding Type||Select||The type of encoding that has been applied to the data. This defaults to UTF-8.|
|Enable Octal||Select||(JSON File type) Enables parsing of octal numbers.|
|Allow Duplicates||Select||(JSON File type) Allows duplicate object field names (keeping only the last one used).|
|Strip Outer Array||Select||(JSON File type) Enable removal of outer square brackets from JSONs.|
|Strip Null Values||Select||(JSON File type) Enables removal of null values.|
|Ignore UTF8 Errors||Select||(JSON/XML File type) When true, replaces invalid UTF-8 sequences with the unicode replacement character (U+FFFD), instead of throwing an error.|
|Preserve Space||Select||(XML File Type) Leading and trailing spaces in elements are preserved if set to true.|
|Strip Outer Element||Select||(XML File Type) This will strip the outermost XML element, exposing the 2nd level elements as separate documents.|
|Disable Snowflake Data||Select||(XML File Type) Will disable recognition of Snowflake semi-structured data tags if set to true.|
|Disable Auto Convert||Select||(XML File Type) Enables conversion of numeric and Boolean values from text to their native types if set to true.|
|On Error||Select||Choose what to do when this component encounters an error.|
|Size Limit (B)||Text||The upper limit filesize for any individual file loaded.|
|Purge Files||Select||Choose whether to destroy the source file after loading its data.|
|Truncate Columns||Select||If this is set, any instance of data in the input file that is too long to fit into the specified target column width will be truncated to fit instead of causing an error.|
|Force Load||Select||When TRUE, will load all files into Snowflake even if that file has not changed since a prior load. If FALSE (default), the component will attempt to avoid such redundant loads.|
|Metadata Fields||Select Multiple||Choose any Snowflake metadata columns to be included in the load.
Note: To use this parameter, users must ensure that they select a configured Stage in the Stage parameter. To configure a Stage, click the Environments panel in the bottom-left, and then right-click a Matillion environment, and then click Manage Stages.
For further help, please refer to our Manage Stages documentation.