The Snowflake Bulk Load Activity

You can use this activity to load a large amount of data into the database. This activity uses the staging concept for loading data into the database table. The stage is an internal location or an external location, like Amazon S3, that is used to store compressed or uncompressed data files. The plug-in can later load the data stored in the stage into the database table.

General

The General tab contains the following fields.

Field Module Property? Visual Diff? Description
Name No Yes Specify the name to be displayed as the label for the activity in the process.
Snowflake Connection Resource Yes Yes Click to select a Snowflake JDBC Connection shared resource. If no matching Snowflake JDBC Connection shared resource is found, click Create Shared Resource to create one. For more details, see Creating and Configuring the Snowflake JDBC Connection Resource.
Create Table from XSD No Yes

Use this option to create a new table in Snowflake from the XSD provided by user. When this check box is selected, the Entity field on General tab is disabled and the Table Name field and the Load Schema button from SQL Builder tab are enabled. Click Load Schema button to provide the XSD schema for the table.

Note: When Stage Type is set to Amazon S3, Create Table from XSD works only when File Format is set as Delimited Files.
Entity No Yes

Click to select an entity. The entities downloaded in Snowflake JDBC Connection shared resource's Schema tab is available for selection.

This field is disabled when Create Table from XSD check box is selected.

Time Out Yes   Defines activity timeout in seconds. The default value is 0 seconds.
Stage Type No Yes There are four stage types available:
  • User Stage
  • Table Stage
  • Named Stage
  • Amazon S3

Select the stage type. Snowflake plug-in supports both the internal stage and the external stage. User, Table, and Named stages are internal stages whereas Amazon S3 is an external stage.
Named Stage Yes Yes

Specify the name of the Named Stage defined in the Snowflake. The field is only enabled if Stage Type is selected as Named Stage or Amazon S3.
File Format No No

This combo box is enabled only when Stage Type is selected as Amazon S3. This field provides information on format of the file that is present in the Amazon S3 bucket. The following file formats are supported:

  • Delimited Files

  • JSON

  • AVRO

  • ORC

  • PARQUET

  • XML

Note: XML data can only be loaded into a variant type column.

For Stage Type=Amazon S3, you need to configure a storage integration object to delegate authentication responsibility for external cloud storage to a Snowflake identity and access management (IAM) entity. For more information, see the Snowflake documentation.

Description

On the Description tab, provide a short description for the activity.

SQL Builder

The SQL Builder tab displays the metadata of the entity selected in the General tab.

Field Editable Description
Table Name Yes

Displays the name of the table to be created.

When General tab > Create Table from XSD check box is selected, this field is enabled. If user provides XSD using the Load Schema button, the value of this field is set to name of root complex element of the XSD. However, user can edit this value and set to desired value.

Note: Table Name can be added as a module property.
Field Name No Displays name of the column.
Data Type No Displays the data type of the column.
Primary Key

Yes, if 'Metadata' table is loaded using XSD provided with 'Load Schema' button from SQL Builder tab.

No, if 'Metadata' table is loaded using Entity field from General tab.

Displays if the column is a primary key.
Not Null No

Displays if the column accepts null value.

The check box is selected if the constraints to the column in snowflake DB is marked as 'Not Null'.

The check box is cleared if the constraints to the column in snowflake DB is not marked as 'Not Null'.

This check box is non-editable.
Dimension No Displays the dimension of the column.
Values Yes Displays the fields that must be part of the Values clause for an insert SQL statement. By default, Values column is selected for all the fields.
Load Schema No When General tab > Create Table from XSD check box is selected, this field is enabled. If user clicks this button, a dialog box is open where the user can create or browse XSD present in the workspace. Once the user selects XSD in the dialog box, its details are displayed into 'Metadata' table and the 'Table Name' field is set to name of root complex element of the XSD.

Advanced

This tab has the following fields:

Field Module Property? Visual Diff? Description
Override Database Name Yes Yes Overrides Database name specified in snowflakejdbcResource.
Override Schema Name Yes Yes Overrides Schema name specified in snowflakejdbcResource.
Interpret Empty String as NULL Yes Yes If this check box is selected, empty string is interpreted as NULL value.
Validation Mode Yes Yes

When this check box is selected, the plug-in returns errors in the data if present. The data is loaded into the table only when there are no errors present in the data.

Note: Validation mode is not supported for semi-structured datatypes
Purge Stage Files Yes Yes Select this check box to automatically delete the stage files once the data is loaded to the database table.
Compress Data Yes Yes Select this check box to compress data in .gz format and load it to Snowflake Internal Stage. This is not applicable when loading data from External Stage like Amazon S3.
On Error No   This field decides how the system behaves after encountering an error. The following actions are available:
  • CONTINUE
  • SKIP_FILE
  • SKIP_FILE_<num>
  • SKIP_FILE_<num>%
  • ABORT_STATEMENT
Skip a File if Error Count Yes Yes This text box is enabled if the SKIP_FILE_<num> option is selected from the On Error dropdown list. It skips the processing of a file if the number of errors present in the data file is equal to the error count specified.
Skip a File if Error Percentage Yes Yes This text box is enabled if the SKIP_FILE_<num>% option is selected from the On Error dropdown list. It skips the processing of a file if the percentage of errors in the data file is equal to error percentage specified.

Input

For User, Table, and Named stage types, all the fields in the SQL Builder tab that have the Values column selected are a part of the Input tab. The information in the schema depends on the fields selected on the SQL Builder tab.

For Stage Type=Amazon S3, the schema in the input tab is populated based on file format selected on the general tab.

The Input tab displays the input schema of the activity as a tree structure.

Output

The Output tab shows the output schema of the activity as a tree structure. The output is read-only. The Output tab displays following fields:

Column Name Description
FILE Name of source file and relative path to the file
STATUS

Contains the following options: loaded, load failed, load_skipped, or partially_loaded.
ROWS_PARSED Number of rows parsed from the source file
ROWS_LOADED Number of rows loaded from the source file
ERROR_LIMIT If the number of errors reaches this limit, then abort.
ERRORS_SEEN Number of error rows in the source file
FIRST_ERROR First error of the source file
FIRST_ERROR_LINE Line number of the first error
FIRST_ERROR_CHARACTER Position of the first error character
FIRST_ERROR_COLUMN_NAME Column name of the first error
Note: If new columns are added in the source table, the target table gets altered during runtime and the new columns are added in the target table. This is applicable only when the table does not contain any columns with semi-structured data types. The alter table operation is applicable only for delimited files.

Fault

The Fault tab lists exceptions that are thrown by this activity.