Why is the struct field not null when the data is empty?

[SGA-taichi-kato]

Question

I'm using pyiceberg==0.7.1.
I inserted empty value to struct field in iceberg table (glue) using "append" function, but not null value is inserted.
It seems to occur only with struct field.

Why does this occur? How can I avoid it?

my script:

from pyiceberg.catalog import load_catalog
import pyarrow as pa

schema = pa.schema(
    [
        pa.field("string_field_1", pa.string(), True),
        pa.field("int_field_1", pa.int32(), True),
        pa.field("float_field_1", pa.float32(), True),
        pa.field(
            "struct_field_1",
            pa.struct(
                [
                    pa.field("string_nested_1", pa.string()),
                    pa.field("int_item_2", pa.int32()),
                    pa.field("float_item_2", pa.float32()),
                ]
            ),
        ),
        pa.field("list_field_1", pa.list_(pa.string())),
        pa.field("list_field_2", pa.list_(pa.int32())),
        pa.field("list_field_3", pa.list_(pa.float32())),
        pa.field("map_field_1", pa.map_(pa.string(), pa.string())),
        pa.field("map_field_2", pa.map_(pa.string(), pa.int32())),
        pa.field("map_field_3", pa.map_(pa.string(), pa.float32())),
    ]
)

records = [
    {
        "string_field_1": "field_1",
        "int_field_1": 123,
        "float_field_1": 1.23,
        "struct_field_1": {
            "string_nested_1": "nest_1",
            "int_item_2": 1234,
            "float_item_2": 1.234,
        },
        "list_field_1": ["a", "b", "c"],
        "list_field_2": [1, 2, 3],
        "list_field_3": [0.1, 0.2, 0.3],
        "map_field_1": {"a": "b", "b": "c"},
        "map_field_2": {"a": 1, "b": 2},
        "map_field_3": {"a": 0.1, "b": 0.2},
    },
    {
        "string_field_1": "field_1_b",
    },
]


catalog = load_catalog(
    "glue",
    **{
        "type": "glue",
        "glue.region": "us-west-2",
        "s3.region": "us-west-2",
    },
)


table_name = "iceberg_test"
location = f"s3://tmp_bucket/test/iceberg/{table_name}"
catalog.drop_table(f"test.{table_name}")
table = catalog.create_table(
    f"test.{table_name}",
    schema,
    location=location,
)


pyarrow_table: pa.Table = pa.Table.from_pylist(records, schema=schema)
table.append(pyarrow_table)

athena result:

"string_field_1","int_field_1","float_field_1","struct_field_1","list_field_1","list_field_2","list_field_3","map_field_1","map_field_2","map_field_3"
"field_1","123","1.23","{string_nested_1=nest_1, int_item_2=1234, float_item_2=1.234}","[a, b, c]","[1, 2, 3]","[0.1, 0.2, 0.3]","{a=b, b=c}","{a=1, b=2}","{a=0.1, b=0.2}"
"field_1_b",,,"{string_nested_1=, int_item_2=0, float_item_2=0.0}",,,,,,

[kevinjqliu]

hi @SGA-taichi-kato thanks for reporting this issue.

From the description, it is not clear to me what the problem is. Do you mind clarifying what you expect to see and what the actual output is?

[SGA-taichi-kato]

Hi @kevinjqliu
Here we define the pyarrow schema. What I'm asking about is the struct field "struct_field_1".

from pyiceberg.catalog import load_catalog
import pyarrow as pa

schema = pa.schema(
    [
        pa.field("string_field_1", pa.string(), True),
        pa.field("int_field_1", pa.int32(), True),
        pa.field("float_field_1", pa.float32(), True),
        pa.field(
            "struct_field_1",
            pa.struct(
                [
                    pa.field("string_nested_1", pa.string()),
                    pa.field("int_item_2", pa.int32()),
                    pa.field("float_item_2", pa.float32()),
                ]
            ),
        ),
        pa.field("list_field_1", pa.list_(pa.string())),
        pa.field("list_field_2", pa.list_(pa.int32())),
        pa.field("list_field_3", pa.list_(pa.float32())),
        pa.field("map_field_1", pa.map_(pa.string(), pa.string())),
        pa.field("map_field_2", pa.map_(pa.string(), pa.int32())),
        pa.field("map_field_3", pa.map_(pa.string(), pa.float32())),
    ]
)

And then, I create the two records, but the second record has no value other than "string_field_1".
So I expect that the value of the second record other than "string_field_1" to be null when I insert these records into the iceberg table using pyiceberg.

records = [
    {
        "string_field_1": "field_1",
        "int_field_1": 123,
        "float_field_1": 1.23,
        "struct_field_1": {
            "string_nested_1": "nest_1",
            "int_item_2": 1234,
            "float_item_2": 1.234,
        },
        "list_field_1": ["a", "b", "c"],
        "list_field_2": [1, 2, 3],
        "list_field_3": [0.1, 0.2, 0.3],
        "map_field_1": {"a": "b", "b": "c"},
        "map_field_2": {"a": 1, "b": 2},
        "map_field_3": {"a": 0.1, "b": 0.2},
    },
    {
        "string_field_1": "field_1_b",
    },
]

And, I inserted the records above to glue iceberg table.

catalog = load_catalog(
    "glue",
    **{
        "type": "glue",
        "glue.region": "us-west-2",
        "s3.region": "us-west-2",
    },
)


table_name = "iceberg_test"
location = f"s3://tmp_bucket/test/iceberg/{table_name}"
catalog.drop_table(f"test.{table_name}")
table = catalog.create_table(
    f"test.{table_name}",
    schema,
    location=location,
)


pyarrow_table: pa.Table = pa.Table.from_pylist(records, schema=schema)
table.append(pyarrow_table)

I then checked the table using AWS Athena, but the "struct_field_1" of the second record is not null.
So I'm asking you about why does this occur, and how can I avoid it.

"string_field_1","int_field_1","float_field_1","struct_field_1","list_field_1","list_field_2","list_field_3","map_field_1","map_field_2","map_field_3"
"field_1","123","1.23","{string_nested_1=nest_1, int_item_2=1234, float_item_2=1.234}","[a, b, c]","[1, 2, 3]","[0.1, 0.2, 0.3]","{a=b, b=c}","{a=1, b=2}","{a=0.1, b=0.2}"
"field_1_b",,,"{string_nested_1=, int_item_2=0, float_item_2=0.0}",,,,,,

[kevinjqliu]

So I expect that the value of the second record other than "string_field_1" to be null when I insert these records into the iceberg table using pyiceberg.

import pyarrow as pa

schema = pa.schema(
    [
        pa.field("string_field_1", pa.string(), True),
        pa.field("int_field_1", pa.int32(), True),
        pa.field("float_field_1", pa.float32(), True),
        pa.field(
            "struct_field_1",
            pa.struct(
                [
                    pa.field("string_nested_1", pa.string()),
                    pa.field("int_item_2", pa.int32()),
                    pa.field("float_item_2", pa.float32()),
                ]
            ),
        ),
        pa.field("list_field_1", pa.list_(pa.string())),
        pa.field("list_field_2", pa.list_(pa.int32())),
        pa.field("list_field_3", pa.list_(pa.float32())),
        pa.field("map_field_1", pa.map_(pa.string(), pa.string())),
        pa.field("map_field_2", pa.map_(pa.string(), pa.int32())),
        pa.field("map_field_3", pa.map_(pa.string(), pa.float32())),
    ]
)
records = [
    {
        "string_field_1": "field_1",
        "int_field_1": 123,
        "float_field_1": 1.23,
        "struct_field_1": {
            "string_nested_1": "nest_1",
            "int_item_2": 1234,
            "float_item_2": 1.234,
        },
        "list_field_1": ["a", "b", "c"],
        "list_field_2": [1, 2, 3],
        "list_field_3": [0.1, 0.2, 0.3],
        "map_field_1": {"a": "b", "b": "c"},
        "map_field_2": {"a": 1, "b": 2},
        "map_field_3": {"a": 0.1, "b": 0.2},
    },
    {
        "string_field_1": "field_1_b",
    },
]
pyarrow_table: pa.Table = pa.Table.from_pylist(records, schema=schema)

print(pyarrow_table["struct_field_1"].to_pandas())

returns

0    {'string_nested_1': 'nest_1', 'int_item_2': 12...
1                                                 None
Name: struct_field_1, dtype: object

which confirms the value is None in pyarrow.

After appending to the table, is the record None when you read it back? table.scan().to_pandas()

I then checked the table using AWS Athena, but the "struct_field_1" of the second record is not null.

It is not clear to me that Athena returns a non-null value in the example you provided.

[kevinjqliu]

If the value shows as None in the pyarrow table, I'm pretty sure it'll be written as that during append. It would be weird otherwise.
Another thing you can check is reading the underlying parquet file directly.

[kevinjqliu]

In your example, the second row is

    {
        "string_field_1": "field_1_b",
    },

which corresponds with string_field_1

        pa.field("string_field_1", pa.string(), True),

and not with struct_field_1

        pa.field(
            "struct_field_1",
            pa.struct(
                [
                    pa.field("string_nested_1", pa.string()),
                    pa.field("int_item_2", pa.int32()),
                    pa.field("float_item_2", pa.float32()),
                ]
            ),
        ),

So the Athena result contains field_1_b because its written to the string_field_1 column

[SGA-taichi-kato]

Hi @kevinjqliu

After appending to the table, is the record None when you read it back? table.scan().to_pandas()

Here is the result.

~
pyarrow_table: pa.Table = pa.Table.from_pylist(records, schema=schema)
print(pyarrow_table.to_pandas())
table.append(pyarrow_table)

print("============================")
print(table.scan().to_pandas())

  string_field_1  int_field_1  float_field_1                                     struct_field_1 list_field_1 list_field_2     list_field_3       map_field_1       map_field_2                                        map_field_3
0        field_1        123.0           1.23  {'string_nested_1': 'nest_1', 'int_item_2': 12...    [a, b, c]    [1, 2, 3]  [0.1, 0.2, 0.3]  [(a, b), (b, c)]  [(a, 1), (b, 2)]  [(a, 0.10000000149011612), (b, 0.2000000029802...
1      field_1_b          NaN            NaN                                               None         None         None             None              None              None                                               None
============================
  string_field_1  int_field_1  float_field_1                                     struct_field_1 list_field_1 list_field_2     list_field_3       map_field_1       map_field_2                                        map_field_3
0        field_1        123.0           1.23  {'string_nested_1': 'nest_1', 'int_item_2': 12...    [a, b, c]    [1, 2, 3]  [0.1, 0.2, 0.3]  [(a, b), (b, c)]  [(a, 1), (b, 2)]  [(a, 0.10000000149011612), (b, 0.2000000029802...
1      field_1_b          NaN            NaN  {'string_nested_1': '', 'int_item_2': 0, 'floa...         None         None             None              None              None                                               None

[SGA-taichi-kato]

Here is the parquet-tools result.

$ parquet-tools show 00000-0-515d281f-afd7-4873-89c9-c5b1a992a822.parquet 
+------------------+---------------+-----------------+---------------------------------------------------------------------------------------+----------------+----------------+----------------+--------------------------+----------------------+----------------------------------------------------------+
| string_field_1   |   int_field_1 |   float_field_1 | struct_field_1                                                                        | list_field_1   | list_field_2   | list_field_3   | map_field_1              | map_field_2          | map_field_3                                              |
|------------------+---------------+-----------------+---------------------------------------------------------------------------------------+----------------+----------------+----------------+--------------------------+----------------------+----------------------------------------------------------|
| field_1          |           123 |            1.23 | {'string_nested_1': 'nest_1', 'int_item_2': 1234, 'float_item_2': 1.2339999675750732} | ['a' 'b' 'c']  | [1 2 3]        | [0.1 0.2 0.3]  | [('a', 'b'), ('b', 'c')] | [('a', 1), ('b', 2)] | [('a', 0.10000000149011612), ('b', 0.20000000298023224)] |
| field_1_b        |           nan |          nan    | {'string_nested_1': '', 'int_item_2': 0, 'float_item_2': 0.0}                         |                |                |                |                          |                      |                                                          |
+------------------+---------------+-----------------+---------------------------------------------------------------------------------------+----------------+----------------+----------------+--------------------------+----------------------+----------------------------------------------------------+

Is this a bug of Pyiceberg, or a bug of Glue Catalog?

[kevinjqliu]

Okay, I see what the problem is now. Thanks for the example!

The value of struct type field (struct_field_1) changes from None to

{'string_nested_1': '', 'int_item_2': 0, 'float_item_2': 0.0}

during the append operation.
Even though the pyarrow_table clearly shows a None value.

[Fokko]

I can confirm as well, looks like when saving and retrieving the data, we mess something up at PyIceberg:

[Fokko]

Looks like it is happening when casting it into the right schema:

Before:

<pyarrow.lib.StructArray object at 0x134ec1f00>
-- is_valid:
  [
    true,
    false
  ]
-- child 0 type: string
  [
    "nest_1",
    ""
  ]
-- child 1 type: int32
  [
    1234,
    0
  ]
-- child 2 type: float
  [
    1.234,
    0
  ]

After:

<pyarrow.lib.StructArray object at 0x134ec3160>
-- is_valid: all not null
-- child 0 type: string
  [
    "nest_1",
    ""
  ]
-- child 1 type: int32
  [
    1234,
    0
  ]
-- child 2 type: float
  [
    1.234,
    0
  ]

[Fokko]

Thanks @SGA-taichi-kato for raising this, and thanks @kevinjqliu for getting to the bottom of this. I believe #1264 should fix this issue 👍