[EPIC] Improved support for nested / structured types (Struct , List, ListArray, and other Composite types)

[alamb]

Is your feature request related to a problem or challenge? Please describe what you are trying to do.
This ticket is designed to capture the work needed to properly support Arrow Struct types in DataFusion

https://arrow.apache.org/datafusion/user-guide/sql/sql_status.html says that nested types are not supported; The are not fully supported, but there are parts of the support already present such as a way to serialize them via ArrowWriter and using field["nested_field"] syntax

Describe the solution you'd like
Research, and describe / implement what is else remains for proper support.

Array (ListArray) support:

• [Epic] General ticket for the concept of the practical implementation of ARRAY #6980
• Support FixedSizeList in array methods #6560
• Implement unnest function #6555
• Support array sort for nested array #9252

Map (MapArray) support:

• [EPIC] A collection of issues for supporting the MAP DataType #11429

Struct (StructArray) support:

• Create a struct with the specified field names and values #5861
• Support DuckDB style stuct syntax #9820
• Create Struct table with explicit type and name #10207
• Support unnest for struct data type #10264
• Support recursive unnest #10660
• Converge StructFunc and NamedStructFunc to one func #11204
• The struct value should not have duplicate and null name #11438
• Implement nested identifier access ( "Nested identifiers not yet supported" ) #11445

Union (UnionArray) support

• Support Union as a function #10206

Other

Known issues so far:

• [Question] Are composite types supported ? #2179 from @Cheappie
• How to build a field of type Struct with DataFusion SQL? #2043 from @lquerel
• Feature request for support for struct and arry data types #3617 from @kesavkolla
• Nested coalesce #6074
• Add note about structured field access to supported SQL docs #1222
• Introduce ProjectionMask To Allow Nested Projection Pushdown #2581
• make_array returns FixedSizeList which has proven to be difficult to work with. #6446
• Add array expressions #6075
• New functions for working with arrays #6119
• Support empty array #6561
• Supports NULL in arrays #6556
• New array method array_contains #6557
• Write Signature method for list datatypes. #6559
• New concatenation operator for working with arrays #6603
• Implement AnyOp and AllOp operators #6602
• Support nested structs in Struct expression #6598
• Constructing and Destructing Objects (JSON) #6631
• Feature request for support for struct and arry data types #3617
• Trouble getting fancy with ARRAY_AGG (DISTINCT ARRAY_AGG) #6743
• Multi-field structs from builtin scalar fn incompatible with UDAF #7012
• Support parquet statistics for struct columns #8334
• Add the ability to create tables with deeply nested schemas in SQL #11746

[nl5887]

This https://github.com/apache/arrow-datafusion/blob/master/datafusion/core/src/physical_plan/file_format/mod.rs#L238 is one reason of errors related to column projection. It compares the complete enum, failing on different field order.

Arrow has a method to compare data types (https://github.com/apache/arrow-rs/blob/master/arrow/src/datatypes/datatype.rs#L674). I think this method should me made public, and used in above.

Currently datafusion uses match_field_names (default true), https://github.com/apache/arrow-rs/blob/master/arrow/src/record_batch.rs#L153 causing the error.

[alamb]

Thanks for the investigation @nl5887 -- that sounds definitely plausible. Feel free to file a PR with proposed changed -- we would love to review them

[nl5887]

This one is also related: #2581

[tv42]

Reminder to write docs: #1222

[alexwilcoxson-rel]

Potential to add to list #7012

[alamb]

We are starting to make progress on struct support --

There is a PR up to support named_struct #9743 and work afoot to support nicer literal syntax: #9820 🚀

[toaiduongdh]

Hi, i think unnest support for struct can be an item in this epic right?

[alamb]

Hi, i think unnest support for struct can be an item in this epic right?

That would make sense to me -- is there a ticket that describes what this means?

[duongcongtoai]

i created a ticket: #10264

[alamb]

i created a ticket: #10264

Thank you. I added this to the list in the ticket description

[duongcongtoai]

I added an issue to support recursive unnest: #10660, i think it shoul belong to this epic

[alamb]

I added an issue to support recursive unnest: #10660, i think it shoul belong to this epic

Added

[goldmedal]

I added an issue to check the duplicate or null name for struct: #11438

[Throne3d]

I think #11445 is related to this epic

[alamb]

I think #11445 is related to this epic

Thank you -- added

[TheBuilderJR]

Right now datafusion doesn't support struct evolution very well. Imagine you have a struct named customData with field someOptionEnabled in one parquet file, later down the line you add a new field newAddedOption to the customData struct in another parquet file. Currently when you try and SELECT * FROM table you'll get this error:

{"message":"Failed to collect DataFrame batches: Plan(\"Cannot cast file schema field customData of type Struct([Field { name: \\\"someOptionEnabled\\\", data_type: Boolean, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]) to table schema field of type Struct([Field { name: \\\"someOptionEnabled\\\", data_type: Boolean, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \\\"newAddedOption\\\", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])\")","status":"error"}

Feels like we should handle this more gracefully. cc @alamb

I'm happy to make contributions if someone can point me to the right places to look.

[alamb]

Right now datafusion doesn't support struct evolution very well. Imagine you have a struct named customData with field someOptionEnabled in one parquet file, later down the line you add a new field newAddedOption to the customData struct in another parquet file. Currently when you try and SELECT * FROM table you'll get this error:

{"message":"Failed to collect DataFrame batches: Plan(\"Cannot cast file schema field customData of type Struct([Field { name: \\\"someOptionEnabled\\\", data_type: Boolean, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]) to table schema field of type Struct([Field { name: \\\"someOptionEnabled\\\", data_type: Boolean, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \\\"newAddedOption\\\", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])\")","status":"error"}

Feels like we should handle this more gracefully. cc @alamb

I agree

I'm happy to make contributions if someone can point me to the right places to look.

My suggestion is to start with filing a ticket with a self contained reproducer (either rust code or SQL) that shows what you are trying to do.

This would likely become part of the test of any code improvement we make, as well as providing some more detail for other contributors to help point to the right place in the code