Snowflake external functions Part 2– How to do NLP and analyze product reviews stored in Snowflake

This tutorial is a hands-on tutorial for Snowflake external functions and shows how you can categorize your Snowflake data using Amazon Comprehend services.

Prerequisite:
External Functions Tutorial | Snowflake

Steps to be followed:
-----------------------------------
Step 1: Create Lambda Role with comprehend access
Step 2: Create Lambda Function
Step 3: Create Rest API
Step 4: Create AWS Integration Role
Step 6: Create Snwoflake API Integration & External Function

Lambda Code:
----------------------
import json
import boto3

def getSentiment(sentence):


def lambda_handler(event,context):
# TODO implement
print(event)
translated=[]
for row in event['data']:
data=row[1]
message=getSentiment(data)['Sentiment']
return {
'statusCode': 200,
'data': translated
}



Snowflake Table , API Integration & External Function Creation Code:
---------------------------------------------------------------------------------------------------------------

--drop database if required
drop database if exists ramu;
--Create Database
create database if not exists ramu;
use ramu;

CREATE OR REPLACE api integration hellotesting
api_provider = aws_api_gateway
api_aws_role_arn = ''
enabled = true
api_allowed_prefixes = ('');




DESCRIBE integration hellotesting;

create or replace external function helloworld
(Chat varchar)
returns variant
api_integration = hellotesting
as '';

create table "RAMU"."PUBLIC"."Chat" (Name Varchar, Chat Varchar);
insert into "RAMU"."PUBLIC"."Chat" values('Ramu','I hope this year''s festival isn''t as crashy as this year''s iPhone app');
insert into "RAMU"."PUBLIC"."Chat" values('Samu','If you have an iPad DO NOT upgrade to the newest iOS yet, TweetDeck is very unstable on it');
insert into "RAMU"."PUBLIC"."Chat" values('Ramu','I''ve always used Camera+ for my iPhone b/c it has an image stabilizer mode which works pretty well.');
insert into "RAMU"."PUBLIC"."Chat" values('Jadu','im doing that shit right now ... im on vacation and you got me workin');
insert into "RAMU"."PUBLIC"."Chat" values('Soham','Thank you for the rec but that doesn''t work');

select Name,Chat,helloworld(Chat) as Sentiment from "RAMU"."PUBLIC"."Chat";

--------------------------------------------------------------------------------------------------------------------------------------------------------------
Improvement:
------------------------

You can add AWS Translate feature also in this pipeline , for example , the reviews are coming in different languages , then to understand the sentiment , first use Translate to convert those in English (Language Detection followed by Language Translation) & then pass the translated review to AMAZON Comprehend .
If you want to implement this advancement , then please refer this link--

Check this playlist for more AWS Projects in Big Data domain:

[stevedickson]

Hello, thanks for making the video! I am getting an error when I run it: 100351 (P0000): Request failed for external function HELLO_WORLD with remote service error: 502 '{"message": "Internal server error"}'; requests batch-id: request batch size: 1 rows; request retries: 10; response time (last retry): 81ms. I did copy the Lambda code as is, I even checked and did it again.... any thoughts or help is greatly appreciated!

[MirrorNeuron]

Hi, I am getting this error :

Error parsing JSON response for external function helloworld with request batch id: Error: "data" JSON array must contain exact number of rows as the request. Response batch expected to have 87 rows, but received 1 rows. I added more data.