AWS Serverless Application Model (SAM) and JDBC driver for Snowflake

 Using AWS Serverless Application Model (SAM) and JDBC driver for Snowflake, you can set up a Lambda function to serve as a federated query connector, enabling Amazon Athena to query Snowflake data. Here’s a comprehensive step-by-step guide, from setting up the environment to deploying the SAM template and calling Snowflake from Athena.

---

Overview

1. Download and Upload Snowflake JDBC Driver to S3.
2. Create a Lambda Layer with the JDBC driver using SAM.
3. Configure the Lambda Function in SAM for Athena to query Snowflake.
4. Deploy the Application using SAM CLI.
5. Register the Connector in Athena.
6. Query Snowflake from Athena.

Step 1: Download the Snowflake JDBC Driver and Upload to S3

1. Download JDBC Driver:

   • Get the JDBC driver JAR file from the Snowflake Documentation.

2. Upload to S3:

• Choose an S3 bucket where you have permission to upload. For example, my-s3-bucket.
• Upload the snowflake-jdbc-<version>.jar file to this bucket. Note the S3 URI (s3://my-s3-bucket/snowflake-jdbc-<version>.jar).

Step 2: Create the SAM Application with the JDBC Layer and Lambda Function

Directory Structure

my-snowflake-athena-connector/

├── src/

│   └── app.py

└── template.yaml

template.yaml (SAM Template)

AWSTemplateFormatVersion: '2010-09-09'

Transform: 'AWS::Serverless-2016-10-31'

Resources:

  # Lambda Layer to hold the JDBC Driver

  SnowflakeJDBCLayer:

    Type: 'AWS::Serverless::LayerVersion'

    Properties:

      ContentUri: s3://my-s3-bucket/snowflake-jdbc-<version>.jar

      CompatibleRuntimes:

        - python3.8

        - python3.9

      Description: 'Layer containing Snowflake JDBC driver for Lambda function'

  # Lambda Function that queries Snowflake

  AthenaSnowflakeConnector:

    Type: 'AWS::Serverless::Function'

    Properties:

      Handler: src/app.lambda_handler

      Runtime: python3.8

      Layers:

        - !Ref SnowflakeJDBCLayer

      MemorySize: 1024

      Timeout: 300

      Environment:

        Variables:

          SNOWFLAKE_ACCOUNT: 'your_snowflake_account'

          SNOWFLAKE_USER: 'your_snowflake_user'

          SNOWFLAKE_PASSWORD: 'your_snowflake_password'

          SNOWFLAKE_DATABASE: 'your_snowflake_database'

          SNOWFLAKE_SCHEMA: 'your_snowflake_schema'

          SNOWFLAKE_WAREHOUSE: 'your_snowflake_warehouse'

      Policies:

        - AWSLambdaBasicExecutionRole

        - Effect: Allow

          Action:

            - "athena:*"

          Resource: "*"

Replace:

• s3://my-s3-bucket/snowflake-jdbc-<version>.jar with the actual path of your uploaded Snowflake JDBC driver.
• your_snowflake_account, your_snowflake_user, etc., with your Snowflake connection details.

Step 3: Write the Lambda Function Code

Create the app.py file inside the src/ directory to handle connections and query execution.

src/app.py

import snowflake.connector

import os

import json

def lambda_handler(event, context):

    # Extract SQL query from Athena event

    sql_query = event.get('query')

    if not sql_query:

        return {"error": "No query provided"}

    # Establish connection to Snowflake

    conn = snowflake.connector.connect(

        user=os.getenv('SNOWFLAKE_USER'),

        password=os.getenv('SNOWFLAKE_PASSWORD'),

        account=os.getenv('SNOWFLAKE_ACCOUNT'),

        warehouse=os.getenv('SNOWFLAKE_WAREHOUSE'),

        database=os.getenv('SNOWFLAKE_DATABASE'),

        schema=os.getenv('SNOWFLAKE_SCHEMA')

    )

    cursor = conn.cursor()

    try:

        # Execute the query

        cursor.execute(sql_query)

        # Fetch results

        results = cursor.fetchall()

        # Format the results in a way Athena can interpret

        response = [{"column_name": value for value in row} for row in results]

        return {"statusCode": 200, "body": json.dumps(response)}

    except Exception as e:

        return {"error": str(e)}

    finally:

        cursor.close()

        conn.close()

Step 4: Deploy the SAM Application

1. Build and Package:
   

   sam build

   sam package --s3-bucket my-s3-bucket --output-template-file packaged.yaml

2. Deploy the SAM Application:
   

   sam deploy --template-file packaged.yaml --stack-name AthenaSnowflakeConnector --capabilities CAPABILITY_IAM

The stack will create the Lambda function and layer, enabling it to connect to Snowflake with the JDBC driver.

Step 5: Register the Connector in Athena

1. Go to the Athena Console:

   • Open the Athena Console.
   • Go to Data Sources > Connect Data Source.
   • Select Lambda as the connection type.

2. Choose the Lambda Function:

   • Select the AthenaSnowflakeConnector Lambda function from the list.
   • Name the data source (e.g., snowflake_connector).

3. Test the Connection:

   • Run a test query in Athena, such as:
     
   • SELECT * FROM snowflake_connector."your_database"."your_schema"."your_table" LIMIT 10;
   • 
     
4. Athena will invoke the Lambda function, which will execute the SQL query in Snowflake and return the results.
   
   
   
   

   Step 6: Query Snowflake Data in Athena

   Now, you can use Athena’s SQL interface to query Snowflake data using the snowflake_connector you configured.
   
   

   SELECT * FROM snowflake_connector."your_database"."your_schema"."your_table" LIMIT 10;