App Connect

 View Only

Using IBM® App Connect to interact with Snowflake

By Deepak Ayilliath posted Fri February 03, 2023 03:49 AM

  

Snowflake is a fully managed SaaS (software as a service) solution that provides a single platform for data warehousing, data lakes, data engineering, data science, data application development, and secure sharing and consumption of real-time/shared data.

Building a flow in IBM App Connect with Snowflake

Use App Connect to build flows that integrate with Snowflake and other applications. The connector is displayed as Snowflake on the App Connect User Interface (UI). 

To allow App Connect to connect to your Snowflake account, you need to fill in the connection fields that you see in the App Connect Designer Catalog page or flow editor.

Required connection fields: Endpoint URL, Username, Password, Database, Schema, Warehouse, and Role.
For more information about how to generate connection field values and the Templates gallery, see How to use App Connect with Snowflake.

Supported objects in Snowflake

You can run the following supported objects for Snowflake in App Connect.

Object Description
Custom SQL queries Runs a custom SQL statement under the selected database and schema.
Pipes Used to load data from an ingestion queue into tables.
Rows A row of data in a table on Snowflake.
Stages Used for loading data from files into Snowflake tables and unloading data from tables into files.
Stored procedures A procedure object returned by invoking a stored procedure on the database. When the stored procedure returns one or more result set instances, streaming is automatically applied to avoid preemptive consumption of such results, which can lead to performance and memory issues.
Tables Used to copy data to and from the table.
Tasks A task in the current or specified schema.

Scenario: Insert data into the Snowflake table when a new file gets created in Amazon S3

Consider this scenario; when Amazon S3 receives a new request, an object is created in Amazon S3, a corresponding stage gets created in Snowflake (if not available already), and this data gets copied into a table which is then sent as a response.

The following steps are involved in the flow:

  1. The event-driven flow gets triggered when Amazon S3 receives a new request containing data from the API.
  2. The Create object action in Amazon S3 creates a file using the data with the specified details.
    Example of a file created:
  3. Snowflake uses this file to stage data into the specified account using the Create stage action.
  4. The staged data is then copied into a Snowflake table using the Copy data into table action.
    Example of data copied into a Snowflake table:
  5. This data is then shared as a file in the response API.

Useful resources

You can view this template in the Templates gallery of your App Connect Designer instance.

  • Insert data into the Snowflake table when a new file gets created in Amazon S3
    Template URL: https://<your-instance-id>/templates/Insert%20data%20into%20the%20Snowflake%20table%20when%20a%20new%20file%20gets%20created%20in%20Amazon%20S3

    Note: Replace <your-instance-id> in the URL with your custom instance ID.

You must enable the Designer AI features in your containerized environment to access the App Connect templates. For more information, see The preloaded IBM App Connect templates.

You can also choose to import the .yaml file for this template directly into your App Connect Designer instance. These templates are available in a public GitHub repository.

0 comments
50 views

Permalink