PyIceberg append/scan with an explicit SQL bridge¶
Iceberg documentation index: Iceberg Documentation Overview.
Validation boundary¶
Reference versions are PyIceberg 0.12.0 and PyArrow 26.0.0. Validation created a catalog, appended typed Arrow, scanned snapshots by load_id and then loaded MatrixOne through DBAPI. This is an explicit bridge, not a native MatrixOne Iceberg connector or an atomic Iceberg snapshot/SQL transaction.
Of 5000 main-plan combinations, 4500 passed and 500 native-transform combinations failed before SQL loading because Arrow lacked binary_join_element_wise(large_string,string,string). MySQL controls failed at the same boundary. This is an intermediate representation/kernel issue rather than an MO SQL bug; this tool did not pass all 5000 combinations. The example below uses basic append/scan.
Install and choose representations¶
Install pyiceberg[sql-sqlite,pyarrow] pymysql and complete the shared connection. This example uses a local SQLite catalog and file warehouse to exercise format interoperability. Production catalogs, object-store permissions and concurrent commits require separate checks.
SQL type |
Iceberg representation |
Contract |
|---|---|---|
DECIMAL(38,18) |
decimal(38,18) |
Decimal, with no float intermediary |
DECIMAL(65,30) |
Exact string |
Explicit adapter retaining precision/scale |
UInt64/BIT64 |
decimal(20,0) |
Unsigned range; record BIT byte decoding |
TIME(6) |
long, signed microseconds |
Duration rather than time of day |
DATETIME(6) |
timestamp, microseconds |
Defined time-zone contract |
SET |
List of strings |
Empty set differs from NULL; qualify SQL domain enforcement |
from decimal import Decimal
from pathlib import Path
from uuid import uuid4
import pyarrow as pa
from pyiceberg.catalog import load_catalog
from pyiceberg.schema import Schema
from pyiceberg.types import NestedField, LongType, DecimalType, BinaryType, StringType
from pyiceberg.io.pyarrow import schema_to_pyarrow
from pyiceberg.expressions import EqualTo
from mo_common import connect
root = Path("iceberg_demo").resolve()
root.mkdir(exist_ok=True)
warehouse = root / "warehouse"
warehouse.mkdir(exist_ok=True)
cat = load_catalog("demo", type="sql", uri="sqlite:///" + str(root / "catalog.db"),
warehouse=warehouse.as_uri())
ns = "run_" + uuid4().hex
cat.create_namespace(ns)
schema = Schema(NestedField(1,"id",LongType(),required=True),
NestedField(2,"amount",DecimalType(38,18),required=False),
NestedField(3,"payload",BinaryType(),required=False),
NestedField(4,"label",StringType(),required=False),
NestedField(5,"rev",LongType(),required=False),
NestedField(6,"load_id",StringType(),required=True))
t = cat.create_table((ns,"rows"), schema=schema)
load_id = uuid4().hex
rows = [dict(id=301, amount=Decimal("12345678901234567890.123456789012345678"),
payload=b"\x00\xff\x80", label="Iceberg😀", rev=0, load_id=load_id)]
arrow = pa.Table.from_pylist(rows, schema=schema_to_pyarrow(t.schema()))
t.append(arrow)
scanned = t.scan(row_filter=EqualTo("load_id", load_id)).to_arrow().to_pylist()
assert scanned == rows
print("snapshot", t.current_snapshot().snapshot_id, "load_id", load_id)
with connect() as d:
try:
with d.cursor() as c:
c.executemany("INSERT INTO lake_demo(id,amount,payload,label,rev) VALUES(%s,%s,%s,%s,%s)",
[[r[k] for k in ("id","amount","payload","label","rev")] for r in scanned])
d.commit()
except Exception:
d.rollback()
raise
Transform troubleshooting, schema and recovery¶
For the string-kernel error, inspect Arrow schema and every input type. A candidate repair is to unify participating columns/scalars as string, then restore the type required by the table schema. This needs an independent regression; it does not turn the failed cases into passes. Use Iceberg update_schema() and synchronize SQL DDL for added fields. An added-field check does not qualify renames, narrowing or field removal.
Iceberg append does not enforce SQL primary-key uniqueness. Repeated append can duplicate rows even if the SQL sink later rejects duplicates. Retain load_id, snapshot_id, schema_id, file hashes and SQL commit state. For an uncertain append, reload the catalog and scan by load_id before attempting another append. Bound retries to identified catalog CAS/lock conflicts and preserve other errors.
Physical absence from a scan is not a reliable delete event. Snapshot-to-SQL consistency needs an explicit event/merge protocol, with separate tombstone, snapshot replay and recovery checks.