Question around fixed hierarchy for schema

[GavinRay97]

I noticed that the ADBC metadata information assumes a fixed hierarchy:

arrow-adbc/java/core/src/main/java/org/apache/arrow/adbc/core/AdbcConnection.java

Line 58 in 2485d7c

* Get a hierarchical view of all catalogs, database schemas, tables, and columns.

What would the advice be for datasources that don't fit this, like Snowflake/Trino/Dremio where the hierarchy might be:

• datasource.database.schema.table
• postgres1.mydb.public.emps

[lidavidm]

Hmm, real-world edge cases are always fun, thanks for poking around. (Admittedly we should've looked more closely at these.)

We were debating whether to keep the hierarchy or split it into multiple views (akin to Flight SQL), so this might be an argument for splitting them.

Do you have a reference for the Snowflake/Trino behavior? I see examples like the following that document three levels of hierarchy, but not the fourth level:

https://trino.io/docs/current/connector/postgresql.html#querying-postgresql
https://docs.snowflake.com/en/user-guide/databases.html

[lidavidm]

A few people are plotting out an ADBC 1.1.0 API specification already. In #320 I put up a quick solution to this, based on what we talked about before (letting db_schema have a delimiter).

[lidavidm]

It's been a long time, but this question has surfaced again for the next spec revision. I think it's worth thinking about.

• format: generalize current catalog/schema for arbitrarily nested namespaces #4564
• discussion: ConnectionGetObjects vs ConnectionGetTableSchema #1704 (comment)
• [Format] Add arbitrarily nested (metadata) schemas #320

I'm going to try to consolidate questions and known changes here.

[lidavidm]

Changes we need to make:

• GetObjects: the generic metadata API (format: add a generalized self-describing metadata API #4400, feat(format): add generic metadata API #4544) should make this possible.
• CurrentCatalog/CurrentSchema: these need to be generalized. It's hard to pass a list of strings through the API: maybe we need a Get/SetOptionList?
• Bulk ingest: currently the catalog/schema/table are separated out; this also needs to be generalized. See above.

Open questions:

• Even with format: add a generalized self-describing metadata API #4400, do we immediately generalize all collections (we only provide catalog APIs that are fully-arbitrarily-nested), or whether we provide duplicate collections (one set that uses the 'normal' SQL hierarchy, and one that follows the arbitrary hierarchy). I don't love duplication, but I think it might help signify to users what 'kind' of system to expect
• Another point towards duplication: navigating schemas is far more expensive in a fully generalized system. Whereas the standard SQL hierarchy means you can list all catalogs/schemas immediately, in (say) Iceberg REST catalog, you have to query the root, then query for children, and so on recursively.