SNOW-1899165: Add a method to create a data source (SnowflakeConnectionPoolDataSource and SnowflakeBasicDataSource) from a map of properties

[fabiencelier]

What is the current behavior?

Currently, when creating a SnowflakeBasicDataSource, we need to call individual methods to set each parameter, such as setPasscode.
In my current application I have the properties I want to use as a Map<String, String> or Map<SFSessionProperty, String>, so in order to set all the properties into the Data Source I have to run a huge switch to call the correct method depending on the property.

This is not only tiring but also there are complex cases which are not straightforward:

• some methods such as setPrivateKeyFile map to several properties (SFSessionProperty.PRIVATE_KEY_FILE, SFSessionProperty.PRIVATE_KEY_PWD and SFSessionProperty.AUTHENTICATOR) so it's not a simple 1:1 matching
• some properties have no equivalent method (SFSessionProperty.APP_ID for instance)

What is the desired behavior?

Add a new method to create a data source from a Map<SFSessionProperty, String>, or at least expose a method setProperty(SFSessionProperty, String) of the DataSource.

How would this improve snowflake-jdbc?

This would make the use of the DataSource API easier.

[sfc-gh-dszmolka]

hi - thanks for raising it. calling individual methods is possible, but should not be required. You should be able to populate the connection string with 2 other methods too:

1. using Properties and populating it
2. putting said configuration directly into the connection string.

Can you please take a look at https://docs.snowflake.com/en/developer-guide/jdbc/jdbc-configure#connection-parameters under <connection_params> and see if it helps ?
Also if it still misses something you need, and if you're up for implementing the enhancement, it's more than welcome!

[fabiencelier]

Hello, thanks for your answer !

using Properties and populating it

The only method I can see is getProperties() which is protected so I cannot call it from my code :/

  protected Properties getProperties() {
    return this.properties;
  }

Am I missing something ?

putting said configuration directly into the connection string.

I did not think of putting everything in connection String, I can try that :)
I see that at least user and password must be set explicitly because they are fields in the SnowflakeBasicDataSource and if I don't set them I will get a NullPointerException when calling getConnection()
But for other fields I'll try to put them all in the connection String

[sfc-gh-dszmolka]

by Properties i meant

Properties props = new Properties();
props.put("parameter1", parameter1Value);
props.put("parameter2", parameter2Value);
Connection con = DriverManager.getConnection("jdbc:snowflake://<account_identifier>.snowflakecomputing.com/", props);

as seen in the documentation referenced in my comment; could this work too?

edit: I see now JDBC driver also supports the almost-universal connections.toml approach: https://docs.snowflake.com/en/developer-guide/jdbc/jdbc-configure#connecting-using-the-connections-toml-file

whch, depending on your use-case, might also work for you and if you're using multiple Snowflake drivers (Python, etc) then its even reusable across different drivers.

[fabiencelier]

I want to avoid using DriverManager and want to use DataSource for various reasons (benefit from the DataSource pooling ability, avoid DriverManager scanning all JDBC Drivers in the JVM...).
I'm creating my Connection like this

DataSource dataSource = new SnowflakeBasicDataSource();
dataSource.setUrl();
dataSource.setUser("myuser");
dataSource.setPassword("mypass");
...
Connection connection = dataSource.getConnection()

[sfc-gh-dszmolka]

got you. then this is surely an enhancement request, and the team will take a look, without any timeline attached. (To set some kind of expectations, due to other critical priorities, it is almost sure this won't be addressed in the next upcoming quarter, so if you're inclined to actually implement it too, that would be more than appreciated)