KeyError when omitting optional columns while writing to Delta Tables

[chinmaychandak]

Hey guys, I'm new to using Delta Lake.

When I run the below code in Python, I get KeyError: "name 'b' present in the specified schema is not found in the columns or index" for df2. The data I deal with comes in batches and not all columns registered in the schema are guaranteed to always be present in each batch/dataframe. It works if I try with df3 but it is not practical to add all the missing columns with None or np.NaN since the schemas can have 100s of optional fields.

Can someone please help me figure out how can I get this to work?

import pandas as pd
import pyarrow

from deltalake.writer import write_deltalake

storage_options = {
    "AWS_S3_ALLOW_UNSAFE_RENAME": "true"
}

# schema with all possible columns
delta_table_schema = pyarrow.schema({"a": pyarrow.int64(), "b": pyarrow.string(), "c": pyarrow.bool_()})

# works: df with all columns
df1 = pd.DataFrame({"a": [1, 2], "b": ["this", "that"], "c": [True, False]})
write_deltalake("s3://bucket/delta/f_table_test/v01", df1, 
                partition_by=["a"],
                mode="append", 
                schema=delta_table_schema,
                overwrite_schema=True,
                storage_options=storage_options
               )

# fails: df missing optional columns b and c
df2 = pd.DataFrame({"a": [3, 4]}) 
write_deltalake("s3://bucket/delta/f_table_test/v01", df2, 
                partition_by=["a"],
                mode="append", 
                schema=delta_table_schema,
                overwrite_schema=True,
                storage_options=storage_options
               )

# works: adding np.NaN/pd.NA or None in case of missing data works
df3 = pd.DataFrame({"a": [5, 6], "b": [None, None], "c": [None, None]}) 
write_deltalake("s3://bucket/delta/f_table_test/v01", df3, 
                partition_by=["a"],
                mode="append", 
                schema=delta_table_schema,
                overwrite_schema=True,
                storage_options=storage_options
               )

I apologize in advance if my question is too naive or if this isn't the correct place to post questions.

[roeap]

@chinmaychandak - right now there is no other way then to include the columns as null. we are beginning some work to cast columns into the correct data types if there is a (lossless?) conversion.

My suggestion would be to leverage the available table schema to write some code that can just generate all missing null columns.

[chinmaychandak]

Thank you so much for the response, @roeap! Really appreciate it. Is there an issue I can track for these efforts? Would also love to contribute if possible.

On a side note, I'm trying to join the Slack group but could not find a link that hasn't expired. Can you please help?

This link is no longer active
To join this workspace, you’ll need to ask the person who originally invited you for a new link.