Skip to content

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

Closed
#1264
Closed
Why is the struct field not null when the data is empty?#1255
#1264

Description

@SGA-taichi-kato
SGA-taichi-kato
opened on Oct 28, 2024

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}",,,,,,

Activity

  1. kevinjqliu commented on Oct 28, 2024

    @kevinjqliu
    kevinjqliu
    on Oct 28, 2024
    Contributor

    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?

  2. SGA-taichi-kato commented on Oct 29, 2024

    @SGA-taichi-kato
    SGA-taichi-kato
    on Oct 29, 2024
    Author

    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}",,,,,,

  3. kevinjqliu commented on Oct 29, 2024

    @kevinjqliu
    kevinjqliu
    on Oct 29, 2024
    Contributor

    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.

  4. kevinjqliu commented on Oct 29, 2024

    @kevinjqliu
    kevinjqliu
    on Oct 29, 2024
    Contributor

    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.

  5. kevinjqliu commented on Oct 29, 2024

    @kevinjqliu
    kevinjqliu
    on Oct 29, 2024
    Contributor

    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

  6. SGA-taichi-kato commented on Oct 29, 2024

    @SGA-taichi-kato
    SGA-taichi-kato
    on Oct 29, 2024
    Author

    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

  7. SGA-taichi-kato commented on Oct 29, 2024

    @SGA-taichi-kato
    SGA-taichi-kato
    on Oct 29, 2024
    Author

    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?

  8. kevinjqliu commented on Oct 29, 2024

    @kevinjqliu
    kevinjqliu
    on Oct 29, 2024
    Contributor

    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.

  9. Fokko commented on Oct 29, 2024

    @Fokko
    Fokko
    on Oct 29, 2024
    Contributor

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

    image

  10. Fokko commented on Oct 29, 2024

    @Fokko
    Fokko
    on Oct 29, 2024
    Contributor

    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
  ]

  11. Fokko commented on Oct 29, 2024

    @Fokko
    Fokko
    on Oct 29, 2024
    Contributor

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

  12. Fokko
    closed this as completedin #1264on Oct 29, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions