Snowflake AWS Lambda Integration

In this post I would like to walk you through on the steps for Snowflake AWS lambda integration

“AWS Lambda is a serverless, event-driven compute service that lets you run code for virtually any type of application or backend service without provisioning or managing servers.”

Lets learn it with a sample use case - read from a snowflake table where a data engineer have captured some anomalies in tables as per that day’s load insights and send the report to the required recipients. From technical perspective, we will go through the following

1. Snowflake-AWS lambda Integration — Using Snowflake python connector in AWS lambda. Since AWS Lambda does not have snowflake connector dependency out of box we need to add it explicitly as Layers.
2. Sending notifications from the snowflake table inference — Send the table content to an email using SNS, contents are formatted using an external python library called “tabulate”, which again needs to be added explicitly as Lambda layer

Sample use case

Steps

I. Create a lambda function

• Create a lambda function with python runtime as 3.6.Please choose correct execution roles.

• Go to configuration tab and increase the timeout to ~4mins , as by default it is 3 seconds.

• If you want to launch in a VPC, please choose accordingly

II. Create zip archive for snowflake dependency and add Lambda layer

As lambda does not have snowflake dependencies installed out of box, add the extra dependency using AWS Lambda layers

“A Lambda layer is a .zip file archive that can contain additional code or data. A layer can contain libraries, a custom runtime, data, or configuration files. Layers promote code sharing and separation of responsibilities so that you can iterate faster on writing business logic.”

• Spin up an ec2 t2.micro instance, use ubuntu/linux OS to avoid dependency issue since lambda runs on linux. I have used the following ec2 ami.

**\## Install Prerequisites  
~$** sudo apt update**~$** sudo apt-get install python3-pip**~$** sudo apt install zip  
**~$** sudo apt install awscli  
**~$** sudo apt-get install -y libssl-dev libffi-dev

**\## Install snowflake dependency  
~$** sudo apt install virtualenv  
**~$** mkdir sf\_lambda; cd sf\_lambda  
**~/sf\_lambda$** virtualenv v-env --python=python3;source v-env/bin/activate  
**~/sf\_lambda$** cd v-env/lib/python3.6/site-packages/  
**~/sf\_lambda/v-env/lib/python3.6/site-packages$** pip3 install -r [https://raw.githubusercontent.com/snowflakedb/snowflake-connector-python/v2.3.10/tested\_requirements/requirements\_36.reqs](https://raw.githubusercontent.com/snowflakedb/snowflake-connector-python/v2.3.10/tested_requirements/requirements_36.reqs) -t .  
**~/sf\_lambda/v-env/lib/python3.6/site-packages$** pip3 install snowflake-connector-python==2.3.10 -t .  
**~/sf\_lambda/v-env/lib/python3.6/site-packages$** chmod -R 755 .  
**~/sf\_lambda/v-env/lib/python3.6/site-packages$** deactivate

**\## Create zip archive  
~/sf\_lambda/v-env/lib/python3.6/site-packages**$ cd ../../../../  
**~/sf\_lambda$** mkdir python;cd python/  
**~/sf\_lambda/python$** cp -r ../v-env/lib/python3.6/site-packages/\* .  
**~/sf\_lambda/python$** cd ..  
**~/sf\_lambda$** zip -r sf\_lambda.zip python

**\## Upload zip file in S3  
~/sf\_lambda$** aws s3 cp sf\_lambda.zip s3://<bucket>/<prefix>/ --profile <profile-name>

• Please note that in the step - pip3 install -r https://raw.githubusercontent.com/snowflakedb/snowflake-connector-python/v2.3.10/tested_requirements/requirements_36.reqs -t . based on your python version the 36 will become 35 for python version 3.5, 38 for python 3.8 etc.
• Please make sure your security group is open for ssh(for connecting via putty to create zip file) and tcp(to download the required python dependencies).And also make sure that you have configured your instance to access s3.

Go to AWS Lambda console -> Layers -> Create Layers. Fill in the details as shown in the below screenshot and click create

• Click on Code Tab and under Layers, click Add a Layer

• Copy paste the layer ARN which we just created and click add

III. Create SNS topic

• Go to SNS -> Topics -> create topics

• Click on the topic created and click on create subscription and add the required email id

• Confirm subscription by clicking on the link in the inbox of the email id given in the previous step

Email Formatting

• To pretty print the table content in our email content, I have used the python library “tabulate”, Create another layer in lambda following the steps similar to II. Create zip archive for snowflake dependency and add Lambda layer

**~$** mkdir tabulate\_lambda; cd tabulate\_lambda  
**~/tabulate\_lambda$** virtualenv v-env-t --python=python3  
**~/tabulate\_lambda$** source v-env-t/bin/activate  
**~/tabulate\_lambda$** cd v-env-t/lib/python3.6/site-packages/  
**~/tabulate\_lambda/v-env-t/lib/python3.6/site-packages$** pip3 install tabulate -t .  
**~/tabulate\_lambda/v-env-t/lib/python3.6/site-packages$** chmod -R 755  
 .  
**~/tabulate\_lambda/v-env-t/lib/python3.6/site-packages$** deactivate  
**~/tabulate\_lambda/v-env-t/lib/python3.6/site-packages**$ cd ../../../../  
**~/tabulate\_lambda$** mkdir python;cd python/  
**~/tabulate\_lambda/python$** cp -r ../v-env-t/lib/python3.6/site-packages/\* .  
**~/tabulate\_lambda/python$** cd ..  
**~/tabulate\_lambda$** zip -r tabulate\_lambda.zip python  
**~/tabulate\_lambda$** aws s3 cp tabulate\_lambda.zip s3://<bucket>/<prefix>/ --profile <profile-name>

Code

Now all the ingredients are ready, final step deploy the below code in the lambda function created, add your snowflake/SNS details in the <> place holders and test :)

Code_Samples/medium/snowflake/lambda at master · aparnasaravind/Code_Samples

*Code Samples - learning. Contribute to aparnasaravind/Code_Samples development by creating an account on GitHub.*github.com

Conclusion

In this post we have performed following steps

1. Created a lambda function with required configuration
2. Created 2 zip files for tabulate and snowflake dependencies and added as layers in the created lambda function
3. Created SNS topic and subscription, confirmed the subscription
4. Deployed the code in lambda

Thank you..