0
0
Snowflake continues to revolutionize the data world with its modern data platform, scalability, performance, and flexibility. One of its standout features is External Functions, which enable you to call external services directly from Snowflake SQL. In today’s competitive market, understanding customer sentiment is key to enhancing customer experience. Retail companies often collect feedback from various channels and store it in Snowflake. While Snowflake excels at processing structured data, analyzing unstructured text such as customer reviews requires external tools.
In this blog, we’ll explore a real-world scenario where Snowflake External Functions integrate with AWS Lambda to perform sentiment analysis on customer feedback. Using External Functions, we can securely connect Snowflake to AWS Lambda, which uses AWS Comprehend to determine customer sentiment directly from Snowflake queries.
To achieve this, we’ll:
- Create the Remote Service (Lambda Function on AWS) to interact with AWS Comprehend.
- Configure the Proxy Service i.e. Amazon API Gateway.
- Creating a new IAM role in your AWS account.
- Creating an Amazon API Gateway endpoint and configuring it.
- Securing your Amazon API Gateway endpoint.
- Create the API Integration
- API Creation in Snowflake
- Modify Trust Relationship in AWS Role
- Create the External Function.
- Call the Function.
Technical Steps:
Step 1: Create the Remote Service (AWS Lambda Function).
Step 2: Configure the Proxy Service i.e. Amazon API Gateway.
- Create an API Gateway linked to your Lambda function.
- Configure the API Gateway to accept requests and invoke the Lambda function.
- Deploy the API Gateway and note the endpoint URL (e.g.,
https://<your-api-endpoint>).
To configure the API please refer the below blogs:
https://docs.snowflake.com/en/sql-reference/external-functions-creating-aws-ui-proxy-service;
cloudyard.in/2022/04/external-function-in-snowflake;
Step 3: Create the API Integration.
create or replace api integration my_aws_api_integration
api_provider = aws_api_gateway
api_aws_role_arn = 'arn:aws:iam::913267004595:role/Sentiment_Role'
api_allowed_prefixes = ('https://0466l8rbnh.execute-api.us-east-1.amazonaws.com/Test_Senti_Stage/sf_proxy')
enabled = true;
DESCRIBE INTEGRATION my_aws_api_integration;
Setting up Trust Relationship:
Step 4: Create the External Function.
CREATE OR REPLACE EXTERNAL FUNCTION analyze_sentiment(text STRING)
returns variant
API_INTEGRATION = my_aws_api_integration
AS 'https://0466l8rbnh.execute-api.us-east-1.amazonaws.com/Test_Senti_Stage/sf_proxy';
Replace the < api_integration_name > with the name of your API integration
Replace the < invocation_url > value with your resource invocation URL.
Step 4: Call the External function.
This function sends requests to the API Gateway, which invokes the Lambda function to perform sentiment analysis.
SELECT
feedback_id,
feedback_text,
analyze_sentiment(feedback_text)::string AS sentiment
FROM customer_feedback;
Conclusion:
External Functions offer a bridge between Snowflake’s and external services, enabling seamless integration for use cases like sentiment analysis. By leveraging AWS Lambda and API Gateway, businesses can enhance their data pipelines without compromising scalability or security.
