Snowflake - Glue - Connection - Using - Pem KEY - NAT Gateway

Question

Hello team,

Here's the scenario.

I am establishing connection between Glue and Snowflake using Encrypted Key Pair (decrypt inside glue). Currently, I only managed to see solution using

SNOWFLAKE_SOURCE_NAME = “net.snowflake.spark.snowflake”
sfOptions = {
“sfURL” : args[‘URL’],
“sfUser” : args[‘USERNAME’],
“pem_private_key” : secretstring,
“sfDatabase” : args[‘DB’],
“sfSchema” : args[‘SCHEMA’],
“sfWarehouse” : args[‘WAREHOUSE’],
“application” : “AWSGlue”
}

However.. this method bypass the connector that has network connected (VPC, Subnet, Security ID -> nat gateway to mask glue IP (IPs are whitelisted in Snowflake side)

1. Is it correct to assume that sfOptions  bypass the connector? 
2. Is there other way to this? 
3. Is using user and password is the only way to connect to Snowflake if we want to mask IP address using nat gateway ?

re:Post Agent · Answer

When connecting AWS Glue to Snowflake, there are two authentication methods supported: username/password and key-pair authentication. Your understanding about the connection behavior needs some clarification:

1. The sfOptions parameters you're using don't bypass the AWS Glue connection's network configuration. When you create a Glue connection to Snowflake and specify VPC, subnet, and security group settings, those network configurations are applied regardless of the authentication method you use. The NAT Gateway configuration in your VPC will still mask the Glue job's IP address when connecting to Snowflake, even when using key-pair authentication.

2. You can use key-pair authentication while still leveraging the network settings from your Glue connection. To do this, create a Snowflake connection in the AWS Glue Data Catalog that includes your VPC, subnet, and security group configurations. Then store your authentication details (including the private key) in AWS Secrets Manager. When you reference this connection in your Glue job, it will use both the network settings and the authentication method you've configured.

3. No, using username and password is not the only way to connect to Snowflake when using a NAT Gateway to mask IP addresses. You can use key-pair authentication (`sfUser` and `pem_private_key`) while still benefiting from the NAT Gateway configuration in your VPC. The authentication method and network routing are separate concerns.

To properly set up a connection that uses key-pair authentication and your NAT Gateway:
- Create a secret in AWS Secrets Manager with your `sfUser` and `pem_private_key`
- Create a Snowflake connection in the AWS Glue Data Catalog, specifying your VPC, subnet (with NAT Gateway), and security group
- Reference this connection in your Glue job

This approach will ensure your Glue job connects to Snowflake using key-pair authentication while routing traffic through your NAT Gateway.  
**Sources**  
[Snowflake connections - AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-connect-snowflake-home.html)  
[Unlock scalable analytics with a secure connectivity pattern in AWS Glue to read from or write to Snowflake | AWS Big Data Blog](https://aws.amazon.com/blogs/big-data/unlock-scalable-analytics-with-a-secure-connectivity-pattern-in-aws-glue-to-read-from-or-write-to-snowflake/)  
[Creating a Snowflake connection - AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/creating-snowflake-connection.html)  
[AWS Glue connection properties - AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/connection-properties.html)

RCMV1911 · Answer

can you provide sample configuration both for AWS Secret manager and Glue script

Snowflake - Glue - Connection - Using - Pem KEY - NAT Gateway

Add your answer

Relevant content