Extension Types / User Defined Types

[findepi]

Is your feature request related to a problem or challenge?

Currently DataFusion provides a lot of built-in types which are useful when building applications / query engines on top of DataFusion. However, even plethora of types is not enough. DataFusion doesn't have types existing in other systems, limiting DataFusion applicability as "LLVM for query engines"

For example, these types commonly found in other systems do not exist today

• char(n)
• varchar(n)
• timestamp with time zone (a pair of "point in time" + "time zone" information; found in Oracle, Trino, Snowflake, etc.)
  • DataFusion currently uses Arrow DataType and the closest Arrow has is "timestamp(zone)" where each value is in same zone
• timestamp with local time zone (point in time without zone information; found in Spark, Hive, PostgreSQL)
  • DataFusion currently uses Arrow DataType and the closest Arrow has is "timestamp(zone)" with eg UTC zone. however cast to varchar for "timestamp(UTC)" and for "timestamp with local time zone" should behave differently
• time with time zone
• JSON
  • DataFusion currently uses Arrow DataType and the closest Arrow has Utf8 potentially with some metadata information. Utf8 might be a perfect carrier type for JSON data, but "cast(json AS T)" and "cast(utf8 AS T)" are usually pretty different operations
• VARIANT (Open Variant Type for semi-structured data #10987)
• geospatial Geometry types (Spatial data support #7859)
• HLL (hyperloglog), digests (t-digest, q-digest, other statistical digests)
• extensions for applications building on top of DF; including user defined types (UDT) ([Proposal] Support User-Defined Types (UDT) #7923)
  • ability to provide user-defined types is even broader than ability to provide extension types ("rust-defined types")

Describe the solution you'd like

1. Introduction of DataFusion own type system
   • this is generally covered by [Proposal] Decouple logical from physical types #11513 & [EPIC] Decouple logical from physical types #12622
2. Introduction of extensions in DataFusion type system allowing applications building on DataFusion to provide more types
   • the extension types -- not unlike DataFusion built-in types -- need to use Arrow types as "carrier type" for transporting
   • the Arrow type metadata weaved into schema fields can be used to indicate use of extension types to the client, when data is returned to the user in Arrow form
   • for example, a "timestamp with time zone" type could be represented as Struct with two fields: point_in_time, time_zone
3. Ability to dynamically find operations on types during function resolution or runtime
   • for example a CAST(array<T> AS varchar) needs to know how to do cast(T AS varchar). It cannot delegate this logic fully to Arrow, because Arrow won't have a notion of extension types.
     • eg if "timestamp with time zone" uses a Struct as a carrier type, it still needs to define its own cast(... AS varchar). It cannot use the default cast(struct AS varchar).

Describe alternatives you've considered

Everything is built-in

DataFusion could provide all types needed by applications building on top of DataFusion as built-in DataFusion types.
This would be easiest to implement, but could lead to scope-creep for the project. This could also lead to conflicts where types look the same but the desired behavior differs between applications building on top of DataFusion. For example Oracle's and Trino's "timestamp with time zone" can represent political zones while Snowflake's allows only fixed offsets.

No-op

Not providing extension types. This would limit DataFusion applicability.
DataFusion cannot be considered "LLVM for query engines" if it cannot serve as an engine, or potential engine, for existing popular query engines.

Additional context

The need to create extension types was raised in the [Proposal] Decouple logical from physical types

• [Proposal] Decouple logical from physical types #11513 & [EPIC] Decouple logical from physical types #12622

However introduction of DataFusion own types does not require introduction of extension types.
Extension types are complex enough (especially given their impact on functions) that they deserve their own roadmap issue.

The impact of extension types on functions, functions runtime and resolution is very clear, so this relates to Simple Functions initiative:

• Simple Functions  #12635

Having ExtensionType in arrow-rs would could the implementation simpler:

• Add support for Arrow Extension Types arrow-rs#4472

[findepi]

cc @alamb, @andygrove, @jayzhan211, @ozankabak, @notfilippo, @comphead, @kylebarron, @yukkit, @sunchao, @Folyd, @wjones127, @Xuanwo, @sadboy, @milevin

[kylebarron]

I'm not very knowledgeable about DataFusion internals or database theory, so it's hard for me to provide feedback on the proposal, but I'm very excited about the prospect of extension types to enable spatial types (#7859). I've been collaborating on the GeoArrow spec, which defines Arrow extension types for spatial data. It's important to have additional logical types because the same physical layout can be interpreted in multiple logical ways (e.g. an array of LineString and MultiPoint), and to store coordinate reference system information (what physical locations on earth these numbers represent) as part of the type. I'm happy to provide more motivating examples if that would help!

[findepi]

FYI i touched upon the topic of types on DataFusion meetup in Belgrade yesterday.
The slides are here if anyone is interested: https://docs.google.com/presentation/d/1VW_JCGbN22lrGUOMRvUXGpAmlJopbG02hn_SDYJouiY . It was an attempt to summarize why we need both: simpler types (#11513), more types (#12644), and simple function "SDK" (#12635).
The document has comments disabled to avoid diverging the discussion from the github issue.

[alamb]

See a previous proposal from @yukkit : #7923

[paleolimbot]

I'm interested in this as well; however, I'm new to DataFusion development and I am not sure I have a handle on what the barriers are here (e.g., Is support for this blocked by lack of support in arrow-rs? Is there opposition to this on principle or is it just a matter of development time?).

A few more (albeit geospatial) reasons to add support for this are (not-quite-merged!) support for spatial data in Iceberg and Parquet. As currently written, those proposals include type-level metadata (notably: the CRS), which would mean that reading Parquet files with those types would loose information. There are also other Arrow canonical extension types that have type-level metadata (which to my current reading, means that canonical extension types are not usable in DataFusion?).