Unable to connect Snowflake through AWS lambda using JAVA 8

Question

Trying to connect Snowflake from AWS lambda using Java 8.

  Class.forName("net.snowflake.client.jdbc.SnowflakeDriver"); // v3.9.1 as maven dependency

            final Properties snowflakProperties = new Properties();
            snowflakProperties.put("user", "username");
            snowflakProperties.put("password", "password");
            snowflakProperties.put("account", "account"); // accountname.regionname
            snowflakProperties.put("db", "dbname");
            snowflakProperties.put("schema", "schemaname");
            snowflakProperties.put("role", "rolename");

            DriverManager.getConnection("jdbc:snowflake://accountname.snowflakecomputing.com", 
                                                       snowflakProperties);

Unfortunately, this is unable to connect and lambda goes out of time. Following trials has been taken place to solve :

Set environment variables in AWS lambda like SF_TEMPORARY_CREDENTIAL_CACHE_DIR and SF_OCSP_RESPONSE_CACHE_DIR to /tmp/. also tried JAVA_TOOL_OPTIONS as -Duser.home=/tmp/
JDBC version tried : 3.11.1, 3.9.1, 3.8.4
Tried to make cache directory as /home/ which throws an exception with message Failed to touch the cache file: /home/ocsp_response_cache.json

Still didn't get proper solution. Does AWS lambda doesn't allow to get Snowflake connection using temp directory ?

hence, look forward to solution quickly.

Thank you.

This is a tough one because it seems like you've tried all the things I've seen that typically resolve the issue. Perhaps you could try setting the JVM options as well in addition to the environment variables as mentioned here? I would also make sure you start with a fresh machine just for testing so you don't have any conflicting dependencies or JDBC driver versions. The latest JDBC driver should be less likely to see exceptions. docs.snowflake.com/en/user-guide/… — Suzy Lockwood
Thanks for the observation @Suzy Lockwood, Tried and got same outcome as mentioned in point 1 and 3 — A v
It is strange that setting these variables don't seem to be working. I see a related GitHub issue, which looks like it was addressed in 3.11.0. Maybe try that exact version to make sure it's not some weird regression in 3.11.1. github.com/snowflakedb/snowflake-jdbc/issues/115 The JDBC code where they look for the writable directory (and defaults to creating files in tmp as needed) is here: github.com/snowflakedb/snowflake-jdbc/blob/master/src/main/java/… — Suzy Lockwood
And you're setting these environment variables all up within Lambda, correct, as opposed to the OS? Is there any JDBC or Lambda logging you can do to make sure they are being picked up? docs.aws.amazon.com/lambda/latest/dg/configuration-envvars.html — Suzy Lockwood
My only other idea is to give this a try with Python. I know you want to use JAVA but maybe setting it up in Python will give some clues. — Suzy Lockwood

Ankur Srivastava Ankur Srivastava · Accepted Answer · 2020-05-20T04:25:15

I tested the Lambda fn with java .Change the values accordingly.
You can try this one below:

-----------------------------------------------------------------------------

package com.snowflake.LambdaTest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import com.amazonaws.services.lambda.runtime.Context;
import com.amazonaws.services.lambda.runtime.RequestHandler;

public class LambdaRequestHandler implements RequestHandler<Object, String> {
    public String handleRequest(Object input, Context context) {
        String data= input != null ? input.toString() : "{}";
        context.getLogger().log("Input: \n" + data);
        context.getLogger().log("Connecting to Snowflake DB ......\n");

        String connectionUrl = "jdbc:snowflake://<URL>/?account=<AccountName>&warehouse=<WareHouseName>&role=<RoleName>&db=<DBName>&schema=<SchemaName>&tracing=ALL";

        String user = "<UserName>";
        String passwd = "<Password>";

        Properties _connectionProperties = new Properties();
        _connectionProperties.put("user", user);
        _connectionProperties.put("password", passwd);

        try {
            Class.forName("net.snowflake.client.jdbc.SnowflakeDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        context.getLogger().log("Getting Conn...\n");
        Connection testConnection = null;

        try {
            testConnection = DriverManager.getConnection(connectionUrl, _connectionProperties);

            testConnection.setAutoCommit(false);
            Statement stat = testConnection.createStatement();
            ResultSet rs = stat.executeQuery("select * from <TableName>");
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }

        } catch (SQLException e) {

            e.printStackTrace();
        }
        try {
            testConnection.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return "Success";

    }

}
----------------------------------------------------------------------

Unable to connect Snowflake through AWS lambda using JAVA 8

1 Answers