Proposal for standardization of metadata keys of schema fields containing the SQL type string

[jasonlin45]

What feature or improvement would you like to see?

This proposal seeks to standardize the naming convention for commonly used metadata keys.

Specifically, we would like to see metadata keys on schemas which preserve the textual representation of the native warehouse type to be standardized. Currently, drivers are all inconsistently naming these keys (SNOWFLAKE_TYPE for Snowflake, ADBC:postgresql:typname for Postgres)

For instance, the BigQuery driver will use BIGQUERY:type as the key for a metadata field containing the native warehouse type.

By enforcing this as a standard, we will be achieve the following:

• Standardization around how to interpret common metadata keys
• Lossless operations relating to data types queried from ADBC

[CurtHagenlocher]

Ideally these should be returned both on the schema for a result set and on the output of AdbcConnectionGetTableSchema.

[lidavidm]

Sounds reasonable. Maybe ADBC:typename for the key?

[felipecrv]

Sounds reasonable. Maybe ADBC:typename for the key?

Can we namespace it by the database name since the type is only fully understandable in the context of its dialect?

SNOWFLAKE:type, BIGQUERY:type, DATABRICKS:type... this makes it clear that the interpretation of the SQL string can only be done reliably considering the specific dialect.

The Arrow types in the schema are dialect agnostic and generic tooling can be developed in terms of it, but for the full resolution, one needs to look at the dialect-specific metadata.

[lidavidm]

Then would it make sense to follow the arrow.opaque type and have a metadata field for vendor, and a metadata field for type name? (That way the keys are always consistent.)

[felipecrv]

By arrow.opaque you mean making every column become an Arrow extension type?

[lidavidm]

Not at all, I mean in analogy to how that type structures its metadata (two fields; one for type name one for vendor name)

[felipecrv]

Not at all, I mean in analogy to how that type structures its metadata (two fields; one for type name one for vendor name)

That doesn't create the restriction I'm trying to impose: you should only be able to access the entry if you are aware (in that code path) of the backend you are using.

Someone very motivated could loop through a set of keys: ["SNOWFLAKE:type", "BIGQUERY:type", ...]. At that point, it's very clear you're doing something weird.

[CurtHagenlocher]

This seems like unnecessarily handholding to me :/.

[amoeba]

Separating the vendor and type into separate fields implies a processing rule like,

"If clients choose to process the ADBC::typename field, they SHOULD first check ARROW::typename_vendor...

with really interesting behavior for clients that don't follow this rule. It seems good to try to avoid splitting this information up because it's not two separate pieces of information. However, one good reason I can come up with to separate the vendor+type tuple out into separate fields is if it's anticipated that there may be more vendor-specific information and we don't want to make clients repeat themselves.

What about namespacing the type name in the value instead like ADBC:typename = $VENDOR_$TYPE_NAME? This implies its own processing rule but I like it better than ADBC:$VENDOR:typename. I'm just throwing that out there since I didn't see it mentioned yet. I'm fairly supportive of ADBC:VENDOR:typename too.

[jasonlin45]

SNOWFLAKE:type, BIGQUERY:type, DATABRICKS:type... this makes it clear that the interpretation of the SQL string can only be done reliably considering the specific dialect.

However, one good reason I can come up with to separate the vendor+type tuple out into separate fields is if it's anticipated that there may be more vendor-specific information and we don't want to make clients repeat themselves.

I agree with this point, clients could then in theory switch on the vendor code to make processing easier. However, the information itself cannot be interpreted in a vacuum and these must always be coupled. OpaqueType canonicalizes this coupling, but Field metadata is just a string map without any guardrails.

Perhaps the client repetition of these vendor is a necessary evil?

If this information is somehow dropped at all for a user, then this field cannot be interpreted correctly unless namespaced.

[lidavidm]

What about namespacing the type name in the value instead like ADBC:typename = $VENDOR_$TYPE_NAME?

I'm not a fan of putting the namespace in the value because that introduces additional parsing and potential ambiguity. I think if we're going to go that way, we'd just want a single field like ADBC:vendor_metadata that contains a JSON blob or something (and I'd still rather not do that because that's less readable and more annoying to work with).

I do agree with Curt that it feels a little unnecessary to do this extra namespacing. I would think it's a given that the type name is vendor-specific (even just the question of whether you should expect INT vs INTEGER vs INT4 vs...). There's nothing stopping applications from just searching for a key that ends in typename. (And note that the current drivers only add the namespacing because there isn't a standard for it.)

Ultimately I'm not going to -1 this though given everyone else seems to have taken this as a base assumption. If we're doing this I would prefer SNOWFLAKE:type over ADBC:SNOWFLAKE:type given the contents at that point really have nothing to do with ADBC.

[amoeba]

It seems, then, like the SNOWFLAKE:type approach ($VENDOR:type) has good support from those who've commented on this issue. Is that a reasonable assessment?