The Snap creates temporary files in JCC when the Staging location is internal and the Data source is input view. These temporary files are removed automatically once the Pipeline completes execution.

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>

grant usage on database <database_name> to role <role_name>;
grant usage on schema <database_name>.<schema_name>;
 
grant "CREATE TABLE" on database <database_name> to role <role_name>;
grant "CREATE TABLE" on schema <database_name>.<schema_name>;

• Asterisk ( *  ): Indicates a mandatory field.

• Suggestion icon (): Indicates a list that is dynamically populated based on the configuration.

• Expression icon ( ): Indicates the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.

• Add icon (  ): Indicates that you can add fields in the fieldset.

• Remove icon ( ): Indicates that you can remove fields from the fieldset.

Instead of building multiple Snaps with inter dependent DML queries, we recommend you to use the Stored Procedure or the Multi Execute Snap.
In a scenario where the downstream Snap depends on the data processed on an upstream database Bulk Load Snap, use the Script Snap to add delay for the data to be available.

For example, when performing a create, insert and a delete function sequentially on a Pipeline using a Script Snap helps in creating a delay between the insert and delete function. Else, it may turn out that the delete function is triggered even before inserting the records on the table.

We recommend that you do not use a temporary stage to prevent issues while loading and transforming your data.

You must provide the stage name along with schema name in the Select Query, else the Snap displays an error. For instance,
SELECT t.$1,t.$4,t.$3,t.$4,t.$5,t.$6,t.$7 from @mys3stage t", displays an error.
SELECT t.$1,t.$4,t.$3,t.$4,t.$5,t.$6,t.$7 from @<Schema Name>.<stagename> t", executes correctly.

You can remove empty spaces from input documents. When you select Input view as Data Source, enter TRIM_SPACE=TRUE in the File Format Option field to remove empty spaces, if any.

Download this pipeline.

In this example, the Pipeline uses the following Snaps:

• Data (JSON Generator): Generates a JSON document for the Snowflake - Bulk Load Snap in the Pipeline. In this example, the JSON document contains an empty space.
• BL: Executes a Snowflake Bulk Load, writing data into an Amazon S3 bucket or a Microsoft Azure Storage Blob. Also, it enables you to remove all spaces from the input document.
• Schema (JSON Generator): Provides the schema to interpret the document passed to the Snowflake - Bulk Load Snap.
• Snowflake - Execute:Reads the newly uploaded document and enables you to check whether the spaces were removed as expected.

Anchor
DATA DATA

Data (JSON Generator)

Data streams from your database source, and you do not necessarily need a Snap to provide input documents. In this example, however, we use the JSON Generator Snap to provide the input document.

Input:

Output:

Image Modified

As you can see, the value listed against the key ACCOUNT_NAME has empty spaces in it.

Anchor
BL BL

Snowflake - Bulk Load

Input:

Output:

Anchor
GEN GEN

Schema (JSON Generator)

Table schema is taken from your database source, and you do not necessarily need a Schema Snap to provide the table schema. In this example, however, we use the JSON Generator Snap to provide the table schema.

Input:

Image Modified

Output:

Image Modified

Anchor
EXEC EXEC

Snowflake - Execute

Input:

Output:

As you can see, the data no longer contains any spaces.

In this pipeline, the Snowflake Bulk Load Snap loads the records from a staged file 'employee.csv content' on to a table on Snowflake.

The staged file 'employee.csv content' is passed via the Upstream Snap:

The Snowflake Bulk Load Snap is configured with Data source as Staged files and the Table Columns added as ID, FIRSTNAME, CITY, to be loaded into a table "PRASANNA"."EMPLOYEE" on Snowflake.

The successful execution of the pipeline displays the below output preview:

If the 'employee.csv content' (Staged file)has the below details:

1,PRASANNA,Hyderabad
2,Aparna,hyderabad

Table Columns added are:
ID, FIRSTNAME, CITY

then the table, "PRASANNA"."EMPLOYEE" on Snowflake is as below:

Create table "PRASANNA"."EMPLOYEE" (ID int, FIRSTNAME varchar(30), LASTNAME varchar(30), CITY varchar(30), ADDRESS varchar(30), JOIN_DATE date)

Note the columns ID, FIRSTNAME and CITY are populated as provided and the LASTNAME, ADDRESS and JOIN DATE are null.

In this pipeline, the Snowflake Bulk Load Snap loads the input document with empty string values as empty strings to the string-type fields. The values to be updated are passed via the upstream Mapper Snap and the Snowflake Execute Snap displays the output view of the table records.

The Mapper Snap passes the values to be updated to the table on Snowflake:

The Snowflake Bulk Load Snap with Load empty strings property selected:

The Snowflake Execute Snap runs the query to select the table 'TEST BULKLOAD' for the 'ID=1002':

The successful execution of the pipeline displays the below output preview:

The below screenshot displays the output preview (on a table with an ID=1003), wherein the Load empty strings property is not selected, and the the Name field has a value 'Null' in it:

In this example, the data is loaded from the table "TECTONIC"."ADOBEPRASANNA_NEW1124"  using Oracle Select Snap to the target table "PRASANNA"."ADOBEDATA123"  using Snowflake Bulk Load Snap.

The Pipeline:

The Oracle Select Snap gets records in the table "TECTONIC"."ADOBEPRASANNA_NEW1124" and passes them to the Snowflake Bulk Load Snap: 

The Snowflake Bulk Load Snap loads records to table "PRASANNA"."ADOBEDATA123":

The output of the Snowflake Bulk Load Snap after executing the pipeline:Image Modified  

This example demonstrates how you can use the Snowflake Bulk Snap to load files from an external staging location such as S3. It further shows the configuration required when loading numeric data.

Download this Pipeline. 

Data is to be loaded from the staged file threecolumns.csv present in a S3 folder into the table "EXTR_PERF_01_SC"."THREECOLUMNS".

Below is a screenshot of the data in threecolumns.csv:

The Snowflake Bulk Load Snap is configured accordingly as follows:

Furthermore, since this data has numeric values, the Snowflake Bulk Load Snap is configured with the following file format options to handle any string/NULL values that may be present in the dataset: 

• SKIP_HEADER = 1: Specifies that the first row is a header row so that all rows after the first row are loaded.
• NULL_IF = "": Convert empty spaces to SQL NULL.
• FIELD_OPTIONALLY_ENCLOSED_BY ="": Specifies the character used to enclose strings.

See Format Type Options for a detailed explanation of the above file format options.

Upon execution, the Snowflake Bulk Load Snap loads three rows:

To confirm that three rows were loaded, we use a Snowflake Execute Snap configured to count the number of rows in the target table:

Below is a preview of the output from the Snowflake Execute Snap. 

We can see that the count is 3, thereby confirming a successful bulk load.

You can also modify the SELECT query in the Snowflake Execute Snap to read the data in the table and thus verify the data loaded into the target table.