createConnectionDetails() with snowflake dbms

[haleyhuston1]

Hi,

Trying to use createConnectionDetails() to them use as a parameter in getDbCovariateData(). However, I am trying to connect to snowflake where we use a JWT authenticator rather than individual passwords.

connectionDetails <- createConnectionDetails( dbms = "snowflake", server = keyring::key_get("server"), user = keyring::key_get("user"), connectionString = keyring::key_get("connectionString"), extraSettings = list(AUTHENTICATOR = "SNOWFLAKE_JWT", PRIV_KEY_FILE = fs::path_home(".p8/private_key.p8"), PRIV_KEY_FILE_PWD = Sys.getenv("PRIVATE_KEY_PWD")), pathToDriver = fs::path_home("...") )

I've tried capturing my authenticator connection information under extraSettings but getting an error message:

Connecting using Snowflake driver
Error in connectUsingJdbcDriver():
! Connection propery 'password' is NULL.

Can someone advise? Thanks!

[anthonysena]

Hi,

I haven't seen an example with using JWT to authenticate to Snowflake but it seems similar to what is done when we are connecting to Google BigQuery as shown here:

FeatureExtraction/tests/testthat/setup.R

Lines 111 to 126 in 437570a

	bqKeyFile <- tempfile(fileext = ".json")
	writeLines(Sys.getenv("CDM_BIG_QUERY_KEY_FILE"), bqKeyFile)
	if (testthat::is_testing()) {
	withr::defer(unlink(bqKeyFile, force = TRUE), testthat::teardown_env())
	}
	bqConnectionString <- gsub(
	"<keyfile path>",
	normalizePath(bqKeyFile, winslash = "/"),
	Sys.getenv("CDM_BIG_QUERY_CONNECTION_STRING")
	)
	connectionDetails <- DatabaseConnector::createConnectionDetails(
	dbms = dbms,
	user = "",
	password = "",
	connectionString = !!bqConnectionString
	)

So you may want to skip using the extraSettings and construct a connectionString that includes the private key file paths and properties that you are using. I'd also suggest using absolute paths vs relative ones to make sure the resources can be found. You can test this out by constructing your connection details and then using DatabaseConnector to connect/disconnect to make sure the connection details are in working order. Then you can re-attempt to run FeatureExtraction.

[haleyhuston1]

Thanks for this - we ended up creating a separate user account that didn't go through single sign on to better access SNOWFLAKE using connectionDetails.

However, now that the connectionDetails are "working", I tried running getDbCovariateData with the connection details and ran into a different issue:

script run:

covariateData <- getDbCovariateData(
connectionDetails = connectionDetails,
cdmDatabaseSchema = cdmDatabaseSchema,
)

error message:
Error in .createErrorReport():
! Error executing SQL:
net.snowflake.client.jdbc.SnowflakeSQLException: JDBC driver internal error: exception creating result java.lang.NoClassDefFoundError: Could not initialize class net.snowflake.client.jdbc.internal.apache.arrow.memory.RootAllocator at net.snowflake.client.jdbc.SnowflakeResultSetSerializableV1.create(SnowflakeResultSetSerializableV1.java:577).

[anthonysena]

Sorry for the delay - it is hard to determine what is causing the issue here - are you using the latest Snowflake JDBC driver? Perhaps you can try to simply query the database with DatabaseConnector to verify that you can download a simple results set?