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)