go/adbc/driver/snowflake: option for session parameters

[frbvianna]

What feature or improvement would you like to see?

If using adbc.OptionKeyURI to provide the DSN, multiple session parameters can be specified:
https://arrow.apache.org/adbc/current/driver/snowflake.html#uri-format

However, the DSN option is only compatible to user/password authentication (snowflake.OptionValueAuthSnowflake) since it is ensured by calling gosnowflake.ParseDSN to necessarily contain both of these fields. In a scenario where the user requires other types of authentication that do not rely on passwords (e.g. private key), if they wish to specify session parameters, they would have to construct a DSN with "mocked" password values, and additionally provide the account, in order to successfully specify the parameters in URI query format. Then, use the options to provide the correct authentication.
Otherwise, the user sends ALTER SESSION SET to set all parameters.

Instead, the driver could accept session parameters as a new option e.g. adbc.snowflake.sql.session_parameters expecting an encoded URI query segment. For instance:

sessionParams := url.Values{
	"param1": {"value1"},
	"param2": {"value2"},
}

db, err := drv.NewDatabase(map[string]string{
	"snowflake.OptionSessionParameters": sessionParams.Encode(),
})

Then the driver can decode these values using url.ParseQuery and send the ALTER SESSION statement to set all session parameters, which saves the effort for the user.

[zeroshade]

so this would be the way to pass session parameters when you aren't using a URI at all? Am I understanding correctly?

[frbvianna]

so this would be the way to pass session parameters when you aren't using a URI at all? Am I understanding correctly?

@zeroshade I think that is one way to put it.
At least from my testing, I could not set the URI to just the session parameters portion, so my understanding is that the only way to specify session parameters with the ADBC driver is to do the whole authentication with the DSN string, which does not seem possible for private key auth.

[frbvianna]

Sharing some info I collected on setting session parameters that were somewhat unclear from the docs but may be useful for this:

Multiple parameters may be set in a single ALTER SESSION statement such as ALTER SESSION SET param_1 = "value""1" param_2 = True param_3 = 1000 where parameter values can be of type STRING, BOOLEAN or NUMBER (see usage notes).
For STRING type, parameter values must be enclosed in double-quotes and each contained double-quote must be escaped with one additional double-quote character.
For BOOLEAN and NUMBER types, parameter values should not be enclosed in double-quotes. Boolean parameters are either TRUE or FALSE (case insensitive).

It may not be so straightforward to tell the parameter type from the URI query string. I think one way to approach this would be to match the values to a regex pattern for either boolean or number values only and consider anything else as string (enclosing in double-quotes with proper escaping).