[BUG] Valid column characters fail on to_arrow() or to_pandas() ArrowInvalid: No match for FieldRef.Name

[gwindes]

Apache Iceberg version

0.6.0 (latest release)

Please describe the bug 🐞

Related to: #81

Platform: MacOS M1
Python v3.12

pyiceberg 0.6.0
pyarrow 15.0.2
pandas 2.2.1

I believe this is a bug, but I may also be misunderstanding how pyiceberg and pyarrow are working with iceberg tables and thus I may be doing something wrong. However, when I sanitize the column name before writing the data to remove : . - / I'm able to query just fine.

My understanding is that the following iceberg column name is a valid name TEST:A1B2.RAW.ABC-GG-1-A. With the caveat that it is NOT a nested field (which I don't need). I'm able to write the data to the iceberg table and it shows the metadata with the fully qualified name of TEST:A1B2.RAW.ABC-GG-1-A in the metadata json.

It appears to be only fail when I want to read the data. I'm following the basic getting started in the pyiceberg

table = catalog.load_table("A1B2.A1-301")

# neither table scan works (throws the same error):
df_pyarrow = table.scan().to_arrow()
df_panda = table.scan().to_pandas()

I created a sample project that reproduces my problem with the pyarrow.lib.ArrowInvalid: No match for FieldRef.Name(TEST_x3AA1B2_x2ERAW_x2EABC_x2DGG_x2D1_x2DA) in TEST:A1B2.RAW.ABC-GG-1-A: double error.

Also to clarify, these column names do need to be in this format as their format has a very specific use case within our hardware environments. We try to follow a URI style naming schema for our columns & sensors.

Image showing metadata is storing channel name as expected.

[gwindes]

Further research shows that when I use daft that I'm able to read and use the to_arrow() functionality just fine. This is interesting especially because daft utilizes pyiceberg.

Basic script:

from pyiceberg.catalog import load_catalog
import daft

print(f"Daft version: {daft.__version__}")

warehouse_path = "./warehouse"
catalog = load_catalog(**{
    "uri": f"sqlite:///{warehouse_path}/pyiceberg-catalog.db",
    "warehouse": f"file://{warehouse_path}"
})
table = catalog.load_table("A1B2.A1-301")

df = daft.read_iceberg(table)

print(df)
print(df.to_arrow())

Resulting successful output:

Daft version: 0.2.20
╭──────────────────────────┬──────────────────────────┬──────────────────────────┬───────╮
│ TEST:A1B2.RAW.ABC-GG-1-A ┆ TEST:A1B2.RAW.ABC-GG-1-B ┆ TEST:A1B2.RAW.ABC-GG-1-C ┆ time  │
│ ---                      ┆ ---                      ┆ ---                      ┆ ---   │
│ Float64                  ┆ Float64                  ┆ Float64                  ┆ Int64 │
╰──────────────────────────┴──────────────────────────┴──────────────────────────┴───────╯

pyarrow.Table
TEST:A1B2.RAW.ABC-GG-1-A: double
TEST:A1B2.RAW.ABC-GG-1-B: double
TEST:A1B2.RAW.ABC-GG-1-C: double
time: int64
----
TEST:A1B2.RAW.ABC-GG-1-A: [[0,1,2,3,4,5,6,7,8,9]]
TEST:A1B2.RAW.ABC-GG-1-B: [[0,1.1,2.2,3.3,4.4,5.5,6.6,7.7,8.8,9.9]]
TEST:A1B2.RAW.ABC-GG-1-C: [[0,1.1,2.2,3.3,4.4,5.5,6.6,7.7,8.8,9.9]]
time: [[1702090722998897808,1702090722998947809,1702090722998997809,1702090722999047809,1702090722999097809,1702090722999147809,1702090722999197809,1702090722999247809,1702090722999297809,1702090722999347809]]

[kevinjqliu]

Thanks for reporting this! And providing a reproducible example.

Here's what I've found.

In the _task_to_table function, the provided schema is modified before its passed to the underlying arrow Scanner. This is done in sanitize_column_names here

The file_project_schema field is then passed into the Scanner, which leads to the error above.
The schema name is changed from TEST:A1B2.RAW.ABC-GG-1-A to TEST_x3AA1B2_x2ERAW_x2EABC_x2DGG_x2D1_x2DA.

The read will work if you comment out the schema modification (L981) and just return the arrow_table instead of L1014

[kevinjqliu]

This was a super interesting deep dive.

So Iceberg has an obscure behavior of transforming column names with special characters. As you see above, TEST:A1B2.RAW.ABC-GG-1-A is transformed into TEST_x3AA1B2_x2ERAW_x2EABC_x2DGG_x2D1_x2DA. This is mentioned in #83 and refers to the AvroSchemaUtil::makeCompatibleName function.

Java Iceberg Behavior

When there is a special character in the column name, Iceberg will transform the column name first before writing to parquet. The resulting parquet file will have the transformed column name while Iceberg retains the original column name in the metadata.
When writing, Iceberg will write parquet files with the transformed column name. When reading, Iceberg will perform the transformation to read the transformed column name. This is done by matching the column id.

Python Iceberg Behavior

The issue in PyIceberg here is not the read side, it's the write side! When an Iceberg table's column name has special characters, the parquet files should contain the transformed column name. Instead, PyIceberg writes the column name with the special characters.

That is the issue above, there is a mismatch between the expected column name (transformed, TEST_x3AA1B2_x2ERAW_x2EABC_x2DGG_x2D1_x2DA) and the actual column name (untransformed, TEST:A1B2.RAW.ABC-GG-1-A).

[kevinjqliu]

@Fokko Are you familiar with this behavior? I can't find any documentations on it. The original PR by Ryan (apache/iceberg #601) suggests that this was done to be compatible with Avro, since Avro spec does not allow special characters.

[kevinjqliu]

@gwindes please take a look at #590, it worked for me locally using the sample project you provided above

[kevinjqliu]

Further research shows that when I use daft that I'm able to read and use the to_arrow() functionality just fine. This is interesting especially because daft utilizes pyiceberg.

The column name transformation behavior is part of the Java Iceberg spec when reading/writing parquet files. Specifically, the transformed schema is pushed down to parquet reader/writer.
I suspect this is happening since the Java parquet implementation supports both Avro and parquet schema (See parquet cli). So to be compatible with both parquet and Avro schemas, this column name transformation behavior is used.

From what I've seen, libraries in other languages do not do this. This means these libraries can read/write parquet files having special characters in their column names.

Daft uses the Rust Arrow library which can read parquet files with special characters in their column names.
Similarly, pyarrow can read it as well.

I checked major parquet libraries in Python, Rust, Golang and they can all support reading special characters in parquet column names.

[Fokko]

Oof, this is a big one. Thanks for reporting this @gwindes and thanks @kevinjqliu for jumping on this, and getting to the bottom of it. I'm also looping in @HonahX here since we want to include this fix in 0.6.1.

@Fokko Are you familiar with this behavior? I can't find any documentations on it. The original PR by Ryan (apache/iceberg#601) suggests that this was done to be compatible with Avro, since Avro spec does not allow special characters.

I don't think Avro is the issue, we reference the fields using the field-id.

• For writing, we want to have the same behavior as Java.
• For reading in PyIceberg we have an additional step in PyIceberg: When reading we read the Parquet files using the original column names, and we rename the fields afterward in this visitor. We could correct it there, but we want to make sure that we don't write any invalid Parquet filenames in the first place.

[Fokko]

Generated a Parquet file using both Spark and Python:

Looking at the Python file:

parq 00000-0-624f6b17-7d8d-435e-9fbe-217caeb25ca4.parquet --schema 

 # Schema 
 <pyarrow._parquet.ParquetSchema object at 0x108ba68c0>
required group field_id=-1 schema {
  optional double field_id=1 ABC-GG-1-A;
}

And the Spark file:

parq 00000-0-875786b8-dbcd-4f8c-80a7-d261205a0333-0-00001.parquet --schema 

 # Schema 
 <pyarrow._parquet.ParquetSchema object at 0x126623780>
required group field_id=-1 table {
  required double field_id=1 ABC_x2DGG_x2D1_x2DA;
}

I would argue that the Python one is correct, but probably missing some context. The names shouldn't matter in the end, so probably we should look them up by ID.

[kevinjqliu]

I would argue that the Python one is correct

Yeah me too. But I think Java Iceberg doesn't support this since parquet files with ABC-GG-1-A column will be read as Iceberg column ABC_x2DGG_x2D1_x2DA. I think it's worth opening an issue to track this in the main Iceberg repo.

PyIceberg can already read a parquet file with a "sanitized" column name, this is handled by #83. file_project_schema, which is the schema with "sanitized" column name, is used to read the parquet files.

So we just need to ensure that PyIceberg writes parquet files with the same behavior as Java Iceberg.

#590 verifies that fixing the write behavior will fix the entire roundtrip described in the issue.

[Fokko]

Yeah me too. But I think Java Iceberg doesn't support this since parquet files with ABC-GG-1-A column will be read as Iceberg column ABC_x2DGG_x2D1_x2DA. I think it's worth opening an issue to track this in the main Iceberg repo.

I agree. I asked Dan, but he didn't recall why this was done. Maybe @rdblue has some historical context.

Technically it should not be a problem. If we truly read by Field-IDs the names should be irrelevant, so we should probably update our mapping to ensure we correctly project by IDs. I think the issue is that we correctly take the names, and then sanitize them, so we should add some additional logic there.

[gwindes]

@kevinjqliu @Fokko I verified that the fix from #590 @kevinjqliu DOES fix the pyarrow issue I was seeing with the column names I have. Though I understand there's some discussion regarding the proper implementation between the java & python implementations which I don't have enough iceberg context about.

Attached some photos of the metadata and the column names being stored with the transformed characters.

Metdata file still stores the original column name as expected

@kevinjqliu I made a couple basic cleanup comments/questions on your PR #590

[kevinjqliu]

I've added apache/iceberg/#10120 as a feature request to allow writing column names with special characters.

I've also cleaned up the PR to fix this issue in #590. Please take a look.