Read and write an Iceberg orders table with MatrixOne¶
Iceberg documentation index: Iceberg Documentation Overview.
Suppose Spark has written orders to a data lake. Your application needs to query those orders with MatrixOne SQL and add another order. This tutorial starts with an empty local environment and ends by verifying that the new order was written to the same Iceberg table.
The workflow is: start a Nessie REST catalog and MinIO object storage → create an Iceberg table and seed five orders with Spark → register the catalog and external table in MatrixOne → query and append an order → clean up the demo resources.
Scope: MatrixOne v4.2.0 or later with Iceberg support. This tutorial uses the local Tier A environment in the MatrixOne source repository. Nessie, MinIO, and the database run on the same machine. The
root/111login, MinIO test credentials, and HTTP endpoints are for local demonstration only.
Prepare the environment¶
You need Git, Docker Compose, a MySQL client, Java, and Spark 3.5 with the spark-sql command. The Spark script also needs internet access on its first run to download Iceberg and S3 dependencies. For MatrixOne build dependencies and machine requirements, see Deploy MatrixOne from source.
Run these commands in the root of the MatrixOne source repository. Run the remaining shell commands from this directory as well:
make build
make dev-up-iceberg-tier-a
curl -fsS http://127.0.0.1:19120/iceberg/v1/config > /dev/null
make dev-up-iceberg-tier-a starts Nessie at 127.0.0.1:19120 and MinIO at 127.0.0.1:9000, and prepares s3://mo-iceberg/warehouse. The final command succeeds without output when the REST catalog is reachable. Ports 6001, 9000, 9001, 19120, and 19121 must be available.
Note
If Docker reports that the MinIO image tag pinned by the source repository is not found, use the repository’s local service scripts instead. Put the minio, mc, and nessie executables in PATH, then run make dev-up-iceberg-tier-a-brew. This uses the same default endpoints. With this option, run etc/launch-minio-local/tier-a/seed-iceberg-tier-a.sh directly in step 1, and run make dev-down-iceberg-tier-a-brew during cleanup.
Start MatrixOne in another terminal and keep it running:
MO_ICEBERG_ALLOW_PLAIN_HTTP=1 ./mo-service -launch etc/launch-minio-local/launch.toml
The local cn.toml already enables Iceberg reads, writes, DML, and deletes. MO_ICEBERG_ALLOW_PLAIN_HTTP=1 permits access to the local HTTP REST catalog. After the service starts, confirm the connection in a third terminal:
mysql -h 127.0.0.1 -P 6001 -u root -p111 -e 'SELECT 1;'
1. Create a real Iceberg table with Spark¶
The source repository’s Tier A seed script invokes Spark to create Iceberg v2 test tables in Nessie and MinIO. It also creates other test tables; this tutorial uses only tpch_sf01.orders. Run:
make dev-seed-iceberg-tier-a
On success, the script prints Tier A seed complete.. When MO_ICEBERG_MO_SQL_CMD is unset, the script skips MatrixOne mappings; you will create one manually in the next step. The script’s Spark SQL inserts five rows into tpch_sf01.orders, partitioned by bucket. Running the seed script again drops and recreates these test tables, so do not rerun it after appending an order.
2. Register a catalog and external table in MatrixOne¶
Connect to MatrixOne:
mysql -h 127.0.0.1 -P 6001 -u root -p111
Run the following in the MySQL client:
CREATE DATABASE iceberg_orders_demo;
CREATE ICEBERG CATALOG iceberg_orders_cat WITH (
'type' = 'rest',
'uri' = 'http://127.0.0.1:19120/iceberg',
'warehouse' = 's3://mo-iceberg/warehouse',
'auth_mode' = 'none'
);
CALL iceberg_register_access(
'iceberg_orders_cat',
'scope=cluster,account_id=0,external_principal=local-tier-a,endpoint=localhost,region=us-east-1,bucket=mo-iceberg'
);
CREATE EXTERNAL TABLE iceberg_orders_demo.orders (
order_id BIGINT,
cust_id BIGINT,
order_status TEXT,
order_date DATE,
total_price DECIMAL(12, 2),
bucket INT
) ENGINE = ICEBERG WITH (
'catalog' = 'iceberg_orders_cat',
'namespace' = 'tpch_sf01',
'table' = 'orders',
'ref' = 'main',
'read_mode' = 'append_only',
'write_mode' = 'append_only'
);
The column definitions match the table created by Spark. MatrixOne does not automatically turn the remote Iceberg schema into external table columns. iceberg_register_access registers both a principal mapping and a data residency policy. Without these policies, external table access is denied even if the catalog exists. In production, configure the principal, endpoint, region, and bucket for your environment, and use a protected catalog connection.
Inspect the mappings you just created:
SHOW ICEBERG CATALOGS;
SHOW ICEBERG NAMESPACES FROM iceberg_orders_cat;
SHOW ICEBERG TABLES FROM iceberg_orders_cat.tpch_sf01;
SHOW ICEBERG NAMESPACES and SHOW ICEBERG TABLES list external table mappings registered in MatrixOne. They do not discover remote tables that have not been mapped.
3. Query and check the orders¶
SELECT COUNT(*) AS order_count, SUM(total_price) AS total_amount
FROM iceberg_orders_demo.orders;
SELECT order_id, total_price
FROM iceberg_orders_demo.orders
WHERE bucket = 2
ORDER BY order_id;
The first query should return order_count = 5 and total_amount = 1501.60. The second should return orders 3 and 4, with amounts 300.75 and 400.00. These values come from the Spark seed data and confirm that MatrixOne read the actual Iceberg files.
4. Append an order through MatrixOne¶
INSERT INTO iceberg_orders_demo.orders
(order_id, cust_id, order_status, order_date, total_price, bucket)
VALUES (6, 60, 'O', '2026-03-02', 600.00, 3);
SELECT COUNT(*) AS order_count, SUM(total_price) AS total_amount
FROM iceberg_orders_demo.orders;
SELECT order_id, order_status, total_price
FROM iceberg_orders_demo.orders
WHERE order_id = 6;
You should now get order_count = 6 and total_amount = 2101.60; the final query should return order 6. The new row goes into the same Iceberg table in the Nessie catalog, not a local MatrixOne table. To check it again from Spark, use the Spark catalog configuration in the step 1 script and query tiera.tpch_sf01.orders. Do not rerun the seed script.
Clean up the demo resources¶
In the MySQL client, drop the mapping, unregister the access policies, and drop the catalog:
DROP DATABASE iceberg_orders_demo;
CALL iceberg_unregister_access('iceberg_orders_cat');
DROP ICEBERG CATALOG iceberg_orders_cat;
This removes only the MatrixOne demo objects; it does not delete the Iceberg table in Nessie and MinIO. To stop the local services, run make dev-down-iceberg-tier-a in the source repository. MinIO data is retained. Before deleting it manually, check that it contains no other test data.