Create the API Integration for AWS in Snowflake

Overview

In today’s world of data-driven decision-making, integrating data from different sources has become crucial. One powerful combination is integrating AWS with Snowflake. Snowflake is a robust cloud-based data warehousing solution, and when paired with AWS, it offers a streamlined approach to managing and analyzing large datasets. In this blog post, we will walk you through the steps to create API integration for AWS in Snowflake, ensuring you can effortlessly leverage the strengths of both platforms.

Prerequisites

Before we jump into the procedure, let’s cover the essentials you’ll need:

1. AWS Account: Ensure you have an AWS account set up. This will be the source of your data.
2. Snowflake Account: You need to have a Snowflake account where the data will be stored and analyzed.
3. Access Permissions: Proper permissions in both AWS and Snowflake to create and manage resources.
4. Basic Knowledge: Familiarity with SQL and cloud storage concepts will be beneficial.

Procedure :-

1. Open a Snowflake session, typically a Snowflake web interface session.
2. Use a Snowflake ACCOUNTADMIN role with privileges or the CREATE INTEGRATION.
3. Create a worksheet for integration.
4. Open thw worksheet and type the CREATE API INTEGRATION command in the worksheet to create an API integration. The command should look similar to the following:

create database if not exists trigger_lambda;
CREATE OR REPLACE API INTEGRATION my_api_integration_01
  api_provider = aws_api_gateway
  api_aws_role_arn = '<new_IAM_role_ARN>'
  api_allowed_prefixes = ('<api-gw stage invoke arn>')
  enabled = true;

Customize the command:

• creating a database for the integration
• create a snowflake api integration with a name
• set the api_provider clause to aws_api_gateway
• enter the role_arn in which u have created for integration
• The api_allowed_prefixes field should contain your api-gateway stage invoke arn.

Below is an example of a complete CREATE API INTEGRATION statement:

create or replace api integration snowflake-aws-api_integration
    api_provider=aws_api_gateway
    api_aws_role_arn='arn:aws:iam::123456789:role/snowflake-aws-api_integration'
    api_allowed_prefixes=('https://abcdxyz.execute-api.ca-central-1.amazonaws.com/dev/trigger_lambda')
    enabled=true;

• Execute the CREATE API INTEGRATION command you typed above.

Record the API_AWS_IAM_USER_ARN and API_AWS_EXTERNAL_ID

1. Execute the DESCRIBE INTEGRATION command.

DESCRIBE INTEGRATION snowflake-aws-api_integration;

Record the below integration values after executing the describe command

• Look for the property named API_AWS_IAM_USER_ARN and then record that property’s property_value in the tracking worksheet.
• Find the property named API_AWS_EXTERNAL_ID and record that property’s property_value in the tracking worksheet.
• Note that the property_value of the API_AWS_EXTERNAL_ID often ends with an equals sign (“=”). That equals sign is part of the value; make sure that you cut and paste it along with the rest of the property_value.

Conclusion :-

Integrating AWS with Snowflake can elevate your data management and analysis capabilities, providing a seamless way to store, query, and derive insights from your data. This guide has covered the foundational steps to set up this integration, from configuring your AWS environment to querying data in Snowflake.