column "iceberg_type" does not exist

[tafia]

Apache Iceberg Rust version

0.8.0 (latest version)

Describe the bug

I am trying to connect to an existing lake which is mostly used with python pyiceberg 0.9.x.

I am using a SqlCatalog and I get what I understand is some format mismatch related error:

column "iceberg_type" does not exist

Is there any way to work around it? maybe downgrade what protocol we use etc? I am new to iceberg.

To Reproduce

This is a private lake so I am not sure how to reproduce it.

Expected behavior

Being able to load from existing (even if old) lake without error. Updating the lake is I believe not possible given how widely used it is already.

Willingness to contribute

Happy to help but I am very new to iceberg.

[liurenjie1024]

Hi, @tafia thanks for reporting this. Could you try to reproduce it with leaking your data? I guess this is a common problem due to the schema mismatch between rust and pyiceberg.

[brodiealexander]

@liurenjie1024 I have been experiencing this issue too. It seems to also break iceberg-rust for MySQL and SQLite both (I have not tested Postgres yet). Perhaps I can create a test dataset later, but I suspect any pyiceberg db will be cause this issue. I'm going to open an RFC later today to discuss how to make this fault-tolerant.

[kevinjqliu]

i dug into this a bit.
Looks like in iceberg-rust, the database schema for SqlCatalog contains the iceberg_type column.

iceberg-rust/crates/catalog/sql/src/catalog.rs

Line 259 in c56411a

{CATALOG_FIELD_RECORD_TYPE} VARCHAR(5),

However, this is not the case in pyiceberg and java's implementation:

• https://github.com/apache/iceberg-python/blob/062a252dd9f0f445ed5062a950db9ebd26682406/pyiceberg/catalog/sql.py#L84-L91
• https://github.com/apache/iceberg/blob/8072acd66e69bd0888ca67c8ffb9b213501be8fc/core/src/main/java/org/apache/iceberg/jdbc/JdbcUtil.java#L126-L147

Perhaps we can make iceberg_type optional and eventually removed

[kevinjqliu]

ah ok, iceberg_type is used to distinguish between tables and views
added in apache/iceberg#9487 and apache/iceberg#10111

[brodiealexander]

@kevinjqliu Actually, it is used in the Java implementation for the V1 methods

  private static final String V1_DO_COMMIT_TABLE_SQL =
      "UPDATE "
          + CATALOG_TABLE_VIEW_NAME
          + " SET "
          + JdbcTableOperations.METADATA_LOCATION_PROP
          + " = ? , "
          + JdbcTableOperations.PREVIOUS_METADATA_LOCATION_PROP
          + " = ?"
          + " WHERE "
          + CATALOG_NAME
          + " = ? AND "
          + TABLE_NAMESPACE
          + " = ? AND "
          + TABLE_NAME
          + " = ? AND "
          + JdbcTableOperations.METADATA_LOCATION_PROP
          + " = ? AND ("
          + RECORD_TYPE
          + " = '"
          + TABLE_RECORD_TYPE
          + "'"
          + " OR "
          + RECORD_TYPE
          + " IS NULL)";
  private static final String V1_DO_COMMIT_VIEW_SQL =
      "UPDATE "
          + CATALOG_TABLE_VIEW_NAME
          + " SET "
          + JdbcTableOperations.METADATA_LOCATION_PROP
          + " = ? , "
          + JdbcTableOperations.PREVIOUS_METADATA_LOCATION_PROP
          + " = ?"
          + " WHERE "
          + CATALOG_NAME
          + " = ? AND "
          + TABLE_NAMESPACE
          + " = ? AND "
          + TABLE_NAME
          + " = ? AND "
          + JdbcTableOperations.METADATA_LOCATION_PROP
          + " = ? AND "
          + RECORD_TYPE
          + " = "
          + "'"
          + VIEW_RECORD_TYPE
          + "'";
  private static final String V0_DO_COMMIT_SQL =
      "UPDATE "
          + CATALOG_TABLE_VIEW_NAME
          + " SET "
          + JdbcTableOperations.METADATA_LOCATION_PROP
          + " = ? , "
          + JdbcTableOperations.PREVIOUS_METADATA_LOCATION_PROP
          + " = ?"
          + " WHERE "
          + CATALOG_NAME
          + " = ? AND "
          + TABLE_NAMESPACE
          + " = ? AND "
          + TABLE_NAME
          + " = ? AND "
          + JdbcTableOperations.METADATA_LOCATION_PROP
          + " = ?";

[kevinjqliu]

Looks like java and rust are correct here, and its pyiceberg that lacks this field. This makes sense since we dont have support for views yet in pyiceberg.

[kevinjqliu]

So the issue here seems to be a mismatch of the underlying db's schema.
V0 (without "record_type" column) is created by pyiceberg. But rust expects only V1 (with the "record_type").

Seems like java solved this by doing a schema migration from V0 -> V1

• [JDBC Catalog] Table commit fails if iceberg_type field is NULL iceberg#10046
• Core: Fix JDBC Catalog table commit when migrating from schema V0 to V1 iceberg#10111

I think there are a couple things here

• rust should allow reading V0 schema without evolution (for backwards compatibility)
• rust should be able to evolve schema from V0 -> V1

Let me know if this makes sense (@brodiealexander i typically like to have the discussion in the issue rather than the PR since its easier for others to follow)

[brodiealexander]

That makes sense. For schema evolution from V0 to V1, is adding the column all that's required, or would that involve pieces outside of iceberg-catalog-sql? I suppose it's safe to assume everything is a table unless iceberg_type is present.

I could pursue a solution like this:
(1) On first open, set a flag if iceberg_type is present. Something like SqlCatalog.views_supported: bool. We could then factor out the query strings into utility functions or constants like Java does and use match statements in the catalog code to select them based on whether the iceberg_type column is present.
(2) On first write, we can add the iceberg_type column to the table with the assumption that all rows were TABLEs and change the flag on SqlCatalog to match.

What do you think of this approach?

[kevinjqliu]

Looks like for java, schema evolution is on the write path (updating the database) https://github.com/apache/iceberg/pull/10111/files#diff-1daee532e0ff506f3e2fa8186deb993eec463498c6aea8c814dcd604891aa1e1R534-R536

I think to resolve this specific issue, we can do the same and evolve the schema if the iceberg_type column is not present.

For further improvements, we might want to introduce the concept of schema versions (v0/v1) that can be optionally set for SqlCatalog. SqlCatalog with V0 will not attempt to evolve the schema, but V1 will. This is just some additional hardening and give users the optionality to not evolve schema if they should choose (or they dont have the permission to)

WDDYT?

[brodiealexander]

I'm all for more choice, but depending on how things change later it might become unrealistic to support different logic paths for all previous versions. I'll go sketch out an implementation that does this exact thing and maybe we can get a better idea of what it would look like.

For maximum clarity to users, I think a good design could be a simple "migrate_schema" option in the builder.

[kevinjqliu]

This is how java treats the column after evolution.

After V0 → V1 migration:

• Existing records (created under V0) have iceberg_type = NULL
• New tables (created under V1) have iceberg_type = 'TABLE'
• New views (created under V1) have iceberg_type = 'VIEW'

The queries treat NULL as TABLE type

[brodiealexander]

Is it worth emulating the NULL behavior? If views didn't exist in V0, we could have an opportunity to avoid putting more NULLs into the world here.

[kevinjqliu]

V0 -> V1 migration (ie adding the iceberg_type) will create NULL values for existing entries. These are assumed to be tables, since the view concept did not exist in V0.
Back filling NULL with "TABLE" is an option, but is too expensive IMO