SNOW-1674866: Support jdbc driver autoconfiguration in Snwoflake container

[amergey]

What is the current behavior?

The way to configure a jdbc connection from inside a snowpark container service, is to use "public address" of snowflake account jdbc:snowflake://<account_identifier>.snowflakecomputing.com/?<connection_params> which requires to configure External Access Integration

What is the desired behavior?

Beeing able to connect to snowflake from inside with minimal/automated configuration (similarly to jdbc:snowflake:auto)
as described here with python
https://docs.snowflake.com/en/developer-guide/snowpark-container-services/additional-considerations-services-jobs#connecting-to-snowflake
by leveraging SNOWFLAKE_HOST, SNOWFLAKE_ACCOUNT env variables and /snowflake/session/token token

How would this improve snowflake-jdbc?

Better support for java application deployed as container in snowpark / snowflake native app

References, Other Background

What is your Snowflake account identifier, if any?

[sfc-gh-wfateem]

Hi @amergey,

I'm trying to understand the exact requirements from your description. The link you provided explains how you can leverage the environment variables SNOWFLAKE_ACCOUNT and SNOWFLAKE_HOST. However, you still need code to grab the values of those environment variables and pass them to the connector. The same would be required in your scenario.

Are you asking that once you pass the connection string jdbc:snowflake:auto or something similar, we automatically extract the environment variables for you and determine the destination host?

In both approaches, you'll still need to create a network rule.

Other than the account name/hostname, you're still going to need to pass other configurations to the JDBC driver. What extra work do you need to do here to pass that information to the JDBC driver that you wouldn't already need to do for all the other configurations like the username, authentication method, role, etc...?

[amergey]

Hi @amergey,

I'm trying to understand the exact requirements from your description. The link you provided explains how you can leverage the environment variables SNOWFLAKE_ACCOUNT and SNOWFLAKE_HOST. However, you still need code to grab the values of those environment variables and pass them to the connector. The same would be required in your scenario.

Of course similar to what python code is doing in your documentation, java code could read env variables to properly configure jdbc driver (url, and properties), but actually from the documentation, I do not know if it is possible to use internal snowflake host with jdbc driver. Documentation only mention "public address" way of connecting to snowflake, while with python we can do both (public and private network address)

Are you asking that once you pass the connection string jdbc:snowflake:auto or something similar, we automatically extract the environment variables for you and determine the destination host?

Yes exactly, it could be a great feature to improve connectivity experience from snowflake container service in java

In both approaches, you'll still need to create a network rule.

In python if you use public address with SNOWFLAKE_ACCOUNT you are right it is necessary to create network rule, but as explained in documentation if you use env variables SNOWFLAKE_HOST + oauth token, no need for network rule, I guess it should be similar in java

Other than the account name/hostname, you're still going to need to pass other configurations to the JDBC driver. What extra work do you need to do here to pass that information to the JDBC driver that you wouldn't already need to do for all the other configurations like the username, authentication method, role, etc...?

automated configuration can get all of that, from env variables defined by snowflake in container including authentication which is oauth with the given token, all is explained in your documentation (https://docs.snowflake.com/en/developer-guide/snowpark-container-services/additional-considerations-services-jobs).

{
   "account": SNOWFLAKE_ACCOUNT,
   "host": SNOWFLAKE_HOST,
   "authenticator": "oauth",
   "token": get_login_token(),
   "warehouse": SNOWFLAKE_WAREHOUSE,
   "database": SNOWFLAKE_DATABASE,
   "schema": SNOWFLAKE_SCHEMA
}
...

And for those who need custom configuration, they will not use auto configuration but pass configuration to the driver explicitely as of today

[sfc-gh-wfateem]

It looks like this is already possible today:
https://docs.snowflake.com/en/developer-guide/jdbc/jdbc-configure#connecting-using-the-connections-toml-file

However, given there's an issue reported in #1902 that would be a blocker. We're already looking into #1902.

Have you tried the approach outlined in the link I provided?

[amergey]

The approoach described in #1902 is exactly what I expect here, thanks !
This approch will work (if 1902 is fixed) and the connections.toml provided in 1902 will work for every container, so it could be nice to have jdbc driver behave in auto like if there were this configuration by default. It would eliminate the step of copying the file into the container, and would make very easy to setup java app inside snowpark