1. Using the Snowflake Adapter with Oracle Integration 3
  2. Add the Snowflake Adapter Connection to an Integration
  3. Invoke Operations Page

Invoke Operations Page

Select the operation to perform.

Perform Bulk Import

Element Description
Select stage type Supports the following types of stages:
  • Internal stage
  • External stage
  • External location

An external stage and location refer to data files stored in an external (AWS, GCP, or Microsoft Azure) stage/location outside of Snowflake. An internal stage refers to data files stored internally within Snowflake.

The Snowflake Adapter can access internal stages to import bulk data into the Snowflake database. Along with it, the Snowflake Adapter can import bulk data into the Snowflake database tables using external stages/external locations where the data is uploaded from a cloud storage service (such as AWS-S3, GCP-storage, and Microsoft Azure - storage).

Select stage name

Specify the stage name for loading data into a table from the staged files.
Stage sub path

Specify the stage subpath.
Select integration id

(Only shown if you select External location.)

Select the integration ID.
File path

(Only shown if you select External location.)

Enter the file path.
Select target table

Select the target Snowflake database table name (for example, WORKERS).

Select file format
  • File format name: Select the file format name from the dropdown list.
  • Configure file format: Select/specify the required values for the following parameters and configure a file format.
    • Select format type: Select a format from these options: CSV, AVRO, JSON, PARQUET, XML, and ORC.
    • Compression
    • Record delimiter
    • Field delimiter
    • Skip header
    • Skip blank lines
    • Date format
    • Time format
    • Timestamp format
    • Binary format
    • Escape
    • Escape unenclosed field
    • Trim space
    • Null if
    • Error on column count mismatch
    • Replace invalid characters
    • Empty field as null
    • Skip byte order mark
    • Encoding
Configure Copy Options Select/specify the required values for the following parameters and configure the copy options.
  • On error
  • Size limit
  • Purge
  • Return failed only
  • Match by column name
  • Enforce length
  • Truncatecolumns
  • Force
  • Load uncertain files
Transformation expression You can enter an expression to transform data. Transformation expressions can be used to write native SQL queries. For example, if you need to import data from specific columns 10 and 20 of a stage file, then write select $10, $20.
Validation Mode
  • Return n rows: Specify the number of rows to validate. You can specify a value from 0 to n number of rows. If you don’t specify a value, it validates one row (default value). Use this option if you need to validate limited rows.
  • Return errors: Select to validate a single file. It checks all errors for a single file.
  • Return all errors: Select to validate multiple files.
Validate Configuration Click to validate the configuration/query against the Snowflake database. The validation result is displayed for a successful configuration. If the configuration is invalid, you receive a response for the errors.

Perform Bulk Export

Element Description
Select stage type Supports the following types of stages:
  • Internal Stage
  • External stage
  • External location

An external stage and location refer to data files stored in an external (AWS, GCP, or Microsoft Azure) stage/location outside of Snowflake. An internal stage refers to data files stored internally within Snowflake.

The Snowflake Adapter can access internal stages to export bulk data from the Snowflake database tables. Along with it, the Snowflake Adapter can export bulk data from the Snowflake database tables to external stages/external locations (such as AWS-S3, GCP-storage, and Microsoft Azure - storage).

Select stage name Specify the stage name for unloading data from the Snowflake database table into an external stage/location.
Stage sub path Specify the stage subpath.
Select integration id

(Only shown if you select External location.)

Select the integration ID.
File path

(Only shown if you select External location.)

Enter the file path.
Export source type Select the export source type:
  • From table: Select the target table name.
  • From transformation expression: Enter an expression to write native SQL queries that can be injected into the SQL query followed by #.
Select file format
  • File format name: Select the file format name from the dropdown list.
  • Configure file format: Select/specify the required values for the following parameters and configure a file format.
    • Select format type: Select a format from these options: CSV, JSON, and PARQUET.
    • Compression
    • Record delimiter
    • Field delimiter
    • File extension
    • Date format
    • Time format
    • Timestamp format
    • Binary format
    • Escape
    • Escape unenclosed field
    • Field optionally enclosed by
    • Null if
    • Empty field as null
Configure Copy Options Select/specify the required values for the following parameters and configure the copy options.
  • Overwrite
  • Single
  • Max file size
  • Include query id
  • Detailed output
  • Header
Validation Mode
  • Return rows: Select to validate the rows.
Validate Configuration Click to validate the configuration/query against the Snowflake database. The validation result is displayed for a successful configuration. If the configuration is invalid, you receive a response for the errors.

Invoke a Stored Procedure

Element Description
Select Procedure Displays the list of stored procedures you created in Snowflake. Select the stored procedure to execute in the Snowflake database.
Filter by Procedure name Type a keyword or the initial letters of the stored procedure to filter the display of names in the list.
Description Displays the description of the selected stored procedure.
Input Parameters Displays the input parameters of the selected stored procedure.

Check/Cancel Statement Execution

Element Description
Please select any one of the following operations Select the operation:
  • Check the status of the execution of a statement
  • Cancel the execution of a statement
Select previous operation type Select the previous operation type:
  • Import Operation
  • Export Operation
  • Procedure Operation
    • Select Procedure: Displays the list of stored procedures. Select a stored procedure to check the status of the execution.

Perform an Operation On a Table

Select the database tables. To retrieve and display the records, you must choose the SELECT operation.

Element Description

What operation do you want to perform on Table?
Select to perform one of the following operations on a table:
  • Insert
  • Update
  • Insert or Update (Merge)
  • Select

Schema

Select the database schema that includes the tables to process.

Table Name

Enter a filter with which to search the schema (for example, %TAB to search for tables with TAB in the name).

Table Type

Specify the table type filter to get a subset of the appropriate database objects, then click Search.

  • ALL

  • TABLE

  • VIEW

Filter By

Enter the initial letters to filter the display of table names.

Available

Lists the tables that meet the selection criteria.

Selected

Lists your table selection.

Import Tables

Click to import the tables. The page is refreshed for you to select the parent database table.

Primary Keys

Appears when you select tables without a primary key defined. Select the virtual primary key for the table.

Note: Having the primary key at the database level is the best practice.

Select the parent database table

Select the parent (root) table from the list. If using multiple related tables, this is the top-level parent table in the relationship. After making your selection, the page is refreshed for you to view and edit the table relationships.

Add || Remove Tables

Click to add more tables or remove tables no longer in use.

Review and manage parent database table relationships

Click Edit to view and edit the table relationships. The relationships automatically identified by the adapter are displayed. See Review and manage parent database table relationships Option.

Review and filter columns from selected database tables

Click Edit to view and edit the table attributes. You can deselect any attributes to exclude from the database queries. Primary key attributes cannot be excluded. See Review and filter columns from selected database tables Option.

Review and edit SQL query

(Displayed only if the Select operation is selected.)

Click Edit to view and edit the default SQL query. See Review and edit SQL query Option.

Review and manage parent database table relationships Option

Specify values for the Review and manage parent database table relationships option.
Element Description
Create New Click to create a new relationship.
Parent Table Select the parent table.
Child Table Select the child table.
Relationships Select the relation type (one-to-many, one-to-one, or one-to-one with the foreign key on the child table). For example, if you selected EMPLOYEE as the parent table and ORDERS as the child table, the following options are displayed:
  • EMPLOYEE has a 1:1 Relationship with ORDERS
  • EMPLOYEE has a 1:1 Relationship with ORDERS (Foreign Key on Child table)
  • EMPLOYEE has a 1:M Relationship with ORDERS
Attribute Name Applies attributes to the table relationship.

Review and filter columns from selected database tables Option

Specify values for the Review and filter columns from selected database tables option.
Element Description
Select the Columns View and deselect attributes automatically created by the adapter. Deselect any attributes to exclude from the database query.

Review and edit SQL query Option

Specify values for the Review and edit SQL query option.

Note:

This is only applicable for a Select operation on a table.
Element Description
SQL Edit Click to manually edit the query in the SQL Query field.
Parameter Click to specify a bind parameter.
Add New Click to add new criteria to the SQL query.
Remove Click to remove the SQL criteria you specified.
Maximum Number of Records to be fetched Select the number of records to fetch with this SQL query.

Run a SQL Statement

Note:

Do not use schema/database names in SQL queries. Configure the details in the connection. For example: 
Update HR.employee set HR.employee.first_name = 'Name' where HR.employee.employee_id='1'
can be changed to a simple query, such as:
Update employee set first_name = 'Name' where employee_id='1'

where HR is used in the connection details. This restricts a user with specific privileges to a particular schema/database.

Element Description

SQL Query

Enter the SQL query.

Status

Displays the results of the SQL query validation. The Status field displays Success! when a query is successfully validated.