Settings/Configuration File

[davidhcoe]

What feature or improvement would you like to see?

We are seeing more scenarios where users and/or administrators need to configure settings in a manner that isn't sent in via code. The Databricks driver came up with its own format for users. More recently, the gosnowflake driver added support for proxy values outside of environment variables, but these can only be sent in via Go code -- there is no place for a user to provide these values without having to interact via code.

It would be nice if there was a common file format that could be used across the drivers to then feed other mechanisms, such as the driver managers. Most users are accustomed to having to do things in .ini files from ODBC, so perhaps a similar structure could work in ADBC as well.

cc @zeroshade , @johnkuch, @CurtHagenlocher

[lidavidm]

@ianmcook I think this is your chance to shine :)

[ianmcook]

We've been thinking about this a lot. I will collect my thoughts and give a long reply here soon.

[davidhcoe]

Penny for your thoughts @ianmcook .

[ianmcook]

Hi @davidhcoe, thanks for the nudge. I had a very long chat with @lidavidm and @zeroshade about this today. Here’s what we’re thinking:

• It seems unambiguously necessary for ADBC to provide a standard way for connection configs to be in a file.
  • We expect that there will be strong agreement from the Arrow/ADBC developer and user communities on this.
• After much thinking and discussion, I propose that we use the name "connection profiles" to refer to this.
• The successful recent effort to add a driver manifests spec and associated implementations to ADBC can serve as a guide for how to add a connection profiles spec and associated implementations.
• There’s nice symmetry here between ADBC and ODBC. We added driver manifests (which are analogous to odbcinst.ini) and now we’re adding connection profiles (which are analogous to the ODBC DSNs stored in odbc.ini).
  • Just like with ODBC DSNs, each ADBC connection profile will be required to specify which driver it’s for.
• We will draft a doc with technical specifics, and circulate it on the Arrow developer mailing list, but here’s what we’re thinking:
  • We should establish specified locations where connection profiles are stored:
    • %LOCAL_APPDATA%\ADBC\Profiles on Windows
    • ~/Library/Application Support/ADBC/Profiles on macOS
    • $XDG_CONFIG_HOME/adbc/profiles on Linux
  • We should define an environment variable ADBC_PROFILE_PATH that overrides the above and takes precedence.
  • We should define different search behaviors when running inside venv and Conda environments for some of the driver managers (to allow virtual environment/per-project isolation).
  • Unlike with the driver manifests, I don’t think we need a system-level config directory for connection profiles.
  • Unlike with the driver manifests, I don’t think we need to use the Windows Registry to store connection profiles on Windows.
  • Inside the connection profiles directory, each connection profile is a separate TOML file.
    • TOML seems like a good choice for this, and we already added a TOML parser to the driver managers for driver manifests.
    • Note the difference from odbc.ini here. With odbc.ini, all the DSNs are defined in a single file. With ADBC connection profiles, each connection profile is defined in its own separate TOML file.
    • The basename of each .toml file is the name of the associated connection profile.
      • We can define rules about the allowed character set, case insensitivity, etc. for these filenames.
  • Within the connection profiles directory, you can add subdirectories to create a hierarchical structure in which to organize the connection profile TOML files.
    • This will be convenient for defining separate dev, test, prod connection profile sets, etc.
  • Each connection profile TOML file will be pretty simple:
    • It will refer to the driver that it’s compatible with.
      • Using the current ADBC driver name search behavior.
    • It will contain key-value pairs matching the name and values of the connection arguments that the drivers expect.
      • The key names will not be standardized by the spec; they will be defined by the requirements of the individual drivers.
    • There will also be a connection profile version number and some other optional metadata in the TOML file.
    • There will be a function env_var that can be used in the connection profile to look up values in environment variables.
      • This will be one of the recommended ways to pass secrets.
    • Later we plan to add other ways to securely store secrets and refer to them in the connection profile TOML.
      • For example, with secrets management tools like HashiCorp Vault et al.
  • The ADBC driver managers will all be changed to accept a new argument named profile that can be used to pass a connection profile name.
    • If the user sets profile to foo, then the driver manager will search the directories described above for foo.toml.
    • If the user sets profile to path/to/foo, then the driver manager will search for a file named foo.toml in the relative path path/to in the directories described above.
    • If the user sets profile to a full absolute path, then the driver manager will use the connection profile file at that path.
  • The ADBC driver managers will also provide a way to specify which connection profile to use through a URI.
    • This is a part of our recent effort to ensure that all the ADBC drivers can accept their configs through a single URI.
    • If the URI begins with profile://, then the driver manager will search the subsequently specified path for a connection profile as described above.
      • profile will therefore become a reserved word in ADBC; no driver will ever be allowed to be named profile.
  • If the user/application code specifies additional connection arguments (as individual parameters passed to the driver manager, or in the URI passed to the driver manager), then those arguments will override the arguments specified in the connection profile.
    • This allows for one-off overrides of arguments specified in connection profiles, and filling in of arguments that are missing in connection profiles.
    • This also provides another way to pass secrets that shouldn’t be hard-coded as literal strings in the connection profile files.
• As a part of this proposal, we also want to specify an abstract interface for connection profiles that can be used in the future to add other non-file-based connection profile providers.

How does that sound?

[davidhcoe]

I'm on board with most of the above, but I still struggle with the discovery aspect. In our use case, it sounds like we would need a pre-named profile that the user would set configuration for. Users also seem to struggle with environment settings. For example, they usually run their service under ServiceUserX, which often isn't a domain-joined account. They struggle with whether to set an environment variable specific to ServiceUserX or system wide. In the current ODBC ini setup, it's easy for a user to add their ini file to the same directory the DLL is running, and the settings are picked up from there.

The above seems very specific to the driver managers, which we don't have in C# today. Would only the driver manager parse the toml file and pass the values? Or would each driver be responsible for doing that?

[zeroshade]

Would only the driver manager parse the toml file and pass the values?

Yea that's the idea so that each driver doesn't have to implement the logic themselves. The manager would be responsible to add the options during loading and forwarding etc.

C# could either embed the c++ driver manager (like Go does) and get the behavior for free once we implement it or implement the entire manager logic itself (like Rust).

I'm on board with most of the above, but I still struggle with the discovery aspect. In our use case, it sounds like we would need a pre-named profile that the user would set configuration for.

Is this handled by allowing an absolute path to the profile.toml file as described by @ianmcook?