AWS Lambda Snowflake Python Connector hangs attempting to connect

Question

I have a small AWS Lambda function that looks like this:

It grabs the credentials to connect to Snowflake from SSM Parameter Store, and then calls snowflake.connector.connect. It's meant to obviously go grab data from my Snowflake data warehouse. However, the code hangs and never finishes the snowflake.connector.connect call.

I believe my subnets and networking are set up properly:

Just to test and develop, I set my security group to allow all inbound and outbound traffic on all ports.
I have my Lambda running in a private subnet, and a route table that directs 0.0.0.0/0 to the NAT Gateway instance. In my code, I print(requests.get('http://216.58.192.142')) just to prove that I do indeed have internet connectivity.
I have many large dependencies that don't fit in the 200MB deploy package for Lambdas, so I have my dependencies mounted in an EFS file system at /mnt/efs path, and I add /mnt/efs/python to my PYTHONPATH in the code before I start to import those dependencies.

import boto3
import snowflake.connector
print("Getting snowflake creds")
session = boto3.session.Session()
ssm = session.client("ssm")
obj = ssm.get_parameter(Name="snowflake", WithDecryption=True)
sf_creds = json.loads(obj.get("Parameter").get("Value"))

def get_data(event, context):
    print(requests.get('http://216.58.192.142'))
    print("Executing")
    print("got parameter, connecting")
    con = snowflake.connector.connect(
        user=sf_creds["USER"],
        password=sf_creds["PASSWORD"],
        account=sf_creds["ACCOUNT"],
        ocsp_response_cache_filename="/tmp/ocsp_response_cache"
    )
    print("connected")

When I run this same exact code locally on my MacBook, I am able to connect fairly quickly, within a second or two. My snowflake-python-connector version is 2.3.2.

However, no matter how long I try, the connect method hands when it executes in an AWS Lambda function. I'm really not sure what is going on.

I've verified that

the AWS Lambda function is connected to the internet (it receives a [200 OK] from the requests.get call).
security groups are as permissive as possible (allow all traffic on all ports both inbound and outbound)
I have not touched the NACL

Really, I'm at a loss as to why this is happening, especially given that the code works fine on my local machine - could someone try to point me in the right direction?

One thing you could do is to check if when you launch a regular instance in your vpc, can it connect to to SF. This way you could verify if this is lambda specific issue, or has a wider scope. — Marcin
Marcin has a great idea, another troubleshooting step would be to emulate what the "SnowCD" tool is doing but in Python, it looks like you're comfortable with calling requests already, so shouldn't be too hard. docs.snowflake.com/en/user-guide/snowcd.html — Rich Murnane
Does your Snowflake instance have any network policies set up that could be restricting your connection to a particular IP range? docs.snowflake.com/en/user-guide/… — Simon D

Anastaia Anastaia · Accepted Answer · 2020-09-30T12:55:46

Lambda can respond with 200, but there can be exception in logs. Check out the CloudWatch logs of this lambda.

AWS Lambda Snowflake Python Connector hangs attempting to connect

1 Answers