How are Struct type columns stored/accessed (interested in efficiency)?

[crowley]

Hello, I've searched around for awhile and didn't find a similar question here or elsewhere, so thought I'd ask...

I'm assessing the storage/access efficiency of Struct type columns in delta tables.  I want to know more about how Databricks is storing Struct type field.  Can an SME add some details?

Example question I'm looking at:  Suppose I add an int field with low cardinality to a Struct column... in columnar database this would be stored/accessed efficiently, I believe... so would it also be stored/accessed efficiently as a field in a Struct column?

Note: I did find a Databricks page describing (maybe) how Apache Arrow is used in Databricks runtime 14+ (link below), but it referenced use in UDFs... I am using Structs in vanilla delta tables and figured that was significantly different.

- https://www.databricks.com/blog/arrow-optimized-python-udfs-apache-sparktm-35#:~:text=In%20Apache%20...   

[Kaniz_Fatma · Accepted solution]

Hi @crowley, Let’s delve into the storage and access efficiency of Struct type columns in Delta tables within the context of Databricks.

1. Structured Data Sources and Efficiency:

   • Structured data sources, such as Parquet and ORC, define a schema on the data. This schema-awareness provides efficient storage and performance benefits.
   • For instance, columnar formats like Parquet and ORC make it easier to extract values from specific c...¹.

2. Struct Type in Databricks:

   • The Struct type represents values with a structure described by a sequence of fields.
   • When you use Structs in Databricks, the underlying storage mechanism leverages the schema information to optimize storage and retrieval.
   • Adding an integer field with low cardinality to a Struct column should still benefit from the schema-aware storage optimizations.
   • Just like in a columnar database, Databricks optimizes access to individual fields within a Struct c...².

3. Apache Arrow and UDFs:

   • While the Databricks blog post you mentioned discusses Apache Arrow in the context of UDFs, the principles extend beyond UDFs.
   • Apache Arrow is a cross-language development platform for in-memory data that optimizes data interchange between different systems.
   • Although the blog post emphasizes UDFs, the same underlying technology influences how Databricks handles Struct type columns in vanilla Delta tables.
   • The efficient handling of Struct fields remains consistent, regardless of whether you’re using UDFs ...³.

4. Predictive Optimization:

   • Databricks also employs Predictive Optimization, an AI-driven approach that intelligently determines which optimizations to apply based on usage patterns, data layout, and performance characteristics.
   • While this feature primarily focuses on query performance, it indirectly benefits storage efficiency...⁴.

In summary, Databricks leverages schema awareness, Apache Arrow, and predictive optimization to ensure efficient storage and access for Struct type columns, even within vanilla Delta tables. Feel free to explore these capabilities further to enhance your understanding! 🚀