discussion: ConnectionGetObjects vs ConnectionGetTableSchema

[CurtHagenlocher]

What would you like help with?

ConnectionGetObjects is more general and more flexible but it doesn't return Arrow-specific type information. Having to call both of these APIs in the client might result in two round-trips to a server, which would be unfortunate. What if we were to do something like add a new column TABLE_SCHEMA.column_schema which contains a serialized Arrow schema? It would be null if the depth didn't include column information, of course.

Or am I missing something?

[lidavidm]

Possibly. It's long bugged me that Arrow schemas don't have a good Arrow-native representation.

The driver could cache too, of course, but that complicates things. It might have been better to return some secondary object (fetching data up front) that could then be queried separately (so you don't have to unwrap such a complicated schema)...

@zeroshade and I have also chatted a bit offline about possibly trying to break up GetObjects since the current version is a little inextensible. That, and it's unclear to me whether changing one of these 'fixed' schemas is a backwards compatibility break or not.

[taozuhong]

From database client developer's view, we hope provide a uniform Api to get all meta data:
Database/Schema/Table/View/Function/Procedure/Trigger/Index/Constraint/Column/....

Maybe GetTableSchema could be replaced with GetObjects.

And should consider make a certificate to valid the feature level(Base/Full/Partial/...)

[lidavidm]

We could add new columns easily enough. I've also wanted to add procedures and other fields. For me, there's two problems:

• Is adding a new column a breaking change?
• For GetTableSchema specifically, there's not a great way to embed an Arrow schema into an Arrow table. You have to serialize it to a byte blob which isn't great. (Perhaps an extension type, or [C++] Support for textual, JSON schema representation arrow#25078, would help, though the PR for that issue got shut down by a maintainer.)

Also, I've been a bit conflicted on the GetObjects API. While in theory it is very efficient and uniform, in practice it is a bit complicated to implement and use. I'd be curious to hear more opinions on it vs the standard JDBC array of GetFoo methods.

[CurtHagenlocher]

As someone who's very dependent on being able get metadata, it's really easy for me to list the shortcomings of GetObjects. It's a lot harder to think of a spec that's generally better in all respects. I like the idea in principle of an API that can reduce round-trips by merging different kinds of metadata in a single result, but in practice I think that the work required on the server to fetch and structure that data ends up being more expensive. And in modern systems aimed at data warehousing, the metadata is typically stored in a different type of system than the data and often doesn't seem to get the same kind of attention in terms of optimization.

I've also had problems with xDBC-style GetFoo methods, but they're familiar (and therefore more accessible) to people already in the database client space. They also align better with Flight SQL.

No matter what else we do, we could explicitly amend the spec as of vNext to say that new columns aren't a breaking change.

Whatever else we decide to do, I feel like the lack of a canonical serialization format for Arrow schemas ought to be tackled first.

[lidavidm]

The canonical serialization format is technically the IPC binary format, it's just not readable for this sort of application. FWIW I went and looked at the proposed approach of using the JSON flatbuffers serialization just now but it is basically only supported in the C++ implementation which sort of defeats the point. (Unless we go and spend a bunch of time to upstream things first which may or may not even get accepted.)

@zeroshade I think we discussed the possibility of using xDBC-style GetFoo methods when defining the async versions of APIs? (~generally, using the async versions of APIs to do a 'v2' of any APIs that don't work well). But maybe this creates more complication than it is worth.

[zeroshade]

I'm not as familiar with the xDBC-style GetFoo methods personally, is that a per metadata element function? I like the idea of going with something more accessible and familiar to people, but I also like that ADBC's APIs are relatively small so would prefer to avoid adding a whole bunch of Get* methods (if that's the way it works).

That all said, I agree that we should use the async versions of APIs to do a 'v2' for any APIs that don't work well currently :)

[lidavidm]

Yeah, you can see there's getTables, getSchemas, getCatalogs, ...

https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/DatabaseMetaData.html

(The other potential wart to correct is that all of this assumes a fixed 3-level catalog-schema-table hierarchy but more recent systems support arbitrary nesting)

[taozuhong]

xDBC-style GetFoo methods will make more Apis, and the Api will be updated with new object types, I support that uniform them with GetObjects for all metadata, and return a data model;

GetObjects(string object_type, string database = null, string schema = null, string object_name = null, string extent_name = null)

[iconara]

I've been looking at GetObjects from the point of view of implementing it for Amazon Athena. Athena does not have a catalog of its own, but uses the AWS Glue Data Catalog. There are many mismatches between ADBC's GetObjects and the reality of this catalog unfortunately:

Data lake catalogs can be huge. I've seen catalogs with hundreds of thousands of tables. GetObjects require the full hierarchy of a catalog to be materialized in memory before anything can be returned to the client. In JDBC, the tabular ResultSet returned from DatabaseMetaData#getTables can be backed by iterators that don't materialze more than one page each from the catalog, schema, and table APIs.

Data lake catalogs are slow. It can take minutes to list all the tables in a schema. JDBC is able to provide liveness: the client can start showing the first table quickly, and continue to load tables in the background.

The resource usage and slowness become a problem in BI tools. Many of them assume that listing all tables that a user has access to is a cheap and quick operation. We have seen examples where they call synchronously call JDBC DatabaseMetaData#getTables from a UI thread, passing null for all parameters (meaning "give me everything"). While we can't do much about the time it takes to fulfill this request, in JDBC we can at least minimize the amount of memory used by not materializing more than strictly necessary, and in theory we can provide liveness.

Modern catalogs have arbitrary depth. In common with JDBC and ODBC, ADBC assumes that there is a three level hierarchy: catalog, schema, table. The Glue Data Catalog now supports nested catalogs. Since this causes problems almost everywhere, there is a flat view of the catalogs, but this is something that should influence the design of the ADBC metadata API.

I don't know if a modern metadata API should support arbitrary nesting, but maybe? A catalog is more like a graph than a table, and though GetCatalogs, GetSchemas, GetTables isn't perfect, it matches the graph nature slightly better. Arrow doesn't do graphs very well and I found the list-based approach of GetObject difficult to work with. In many ways, the tabular style of JDBC and OBDC feels like it would fit Arrow much better.

#4400 proposes a generalized API, but I must admit I don't entirely follow the proposal since I'm not familiar with ADO.NET – I should read up.

The API is vague when it comes to filtering. It's an easy to miss detail that the catalog, schema, table, and column parameters to GetObjects are patterns. Filtering with LIKE patterns enables filter pushdown in some implementations (if you dare passing the strings down into SQL). It's an ODBC/JDBC feature I wish wasn't replicated. Catalog objects tend to be named using snake case, and it's very easy to miss that the parameters are patterns, not names, and that underscore matches any character. Implementations that don't support LIKE patterns have to translate these to native filters, and/or implementing filtering client side.

I don't have a solution to this, I just wish it was more explicit – it should be obvious to client code when a parameter is a pattern and when it is not, and for implementations when filtering is expected and when it's not. I think ODBC has a feature to turn wildcard handling on and off.

An concrete example: when you use GetObjects to list tables, the catalog and schema are patterns. This means that the implementation needs to list all catalogs, find matches, then list schemas, find matches, and finally list tables. Most of the time the catalog and schema will be a specific catalog and schema, and listing and filtering is unnecessary overhead. The implementation could be optimized by looking at the patterns and if they don't contain wildcards, do a direct lookup for the catalog and schema instead of listing and filtering (maybe the lookups can be skipped entirely since the metadata for the catalog and schema levels are just the names) – but this assumes the client has understood the API and is not passing unescaped underscores!

It would help clients if they didn't have to know that underscores have to be escaped to avoid the overhead of listing catalogs and schemas, and it would help implementations if they knew whether a pattern is a pattern or a concrete value

[CurtHagenlocher]

Modern catalogs have arbitrary depth: this concern is reflected in #46 and #320. An open question is whether the depth is fixed (either at a driver or a connection level) or whether the depth can be variable -- e.g. a filesystem hierarchy. I think #4400 is mostly orthogonal to this concern.

The API is vague when it comes to filtering: yes, this has been raised a few times in e.g. #1321, #1508, #3220

Data lake catalogs can be huge, Data lake catalogs are slow: There's nothing in the existing API which says that all the data needs to be returned in a single RecordBatch. The driver could choose to start enumerating values against the back end and then use a time- or size- based trigger to return all the values it has so far as the next batch. This would allow for incremental population of a UI.

Anyway; above all, I agree with your implicit point that this area would benefit from a holistic rethink.

[iconara]

Thanks for all the references.

There's nothing in the existing API which says that all the data needs to be returned in a single RecordBatch.

Can you explain this in more detail? The way I understand things is that a list can't span record batches. Unless I've misunderstood that detail, I don't see how the driver can yield to the client until it has materialized a full catalog.

David suggested returning a partial list of schemas and/or tables and then repeating the catalog in the next record batch with a new (potentially partial) list of schemas. That is not something that I would expect to get as a client from reading the API docs.