Read and write Iceberg external tables using Spark SQL - AnalyticDB

Choose a storage type

AnalyticDB for MySQL supports two storage types for Iceberg external tables. Choose one before proceeding — each type requires a different set of configuration parameters throughout all subsequent steps.

	Data Lake Storage tables	Non-Data Lake Storage tables (OSS)
Storage location	Managed Data Lake Storage	OSS bucket you provision
Required session parameter	`spark.adb.lakehouse.enabled=true`	`spark.adb.version=3.5` and `spark.iceberg.warehouse`
Spark version	Not restricted	Must be 3.5
DROP TABLE behavior	Deletes metadata and data	Deletes metadata only by default; add `purge` to also delete OSS data

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster with:
- Enterprise Edition or Basic Edition: reserved resources greater than 0 AnalyticDB compute units (ACUs)
- Data Lakehouse Edition: reserved storage resources greater than 0 ACUs
Kernel version 3.2.5.0 or later

To view and update the minor version of your cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.
A job resource group or an interactive resource group with the Spark engine configured for the cluster
A database account:
- Alibaba Cloud account: create a privileged account
- Resource Access Management (RAM) user: create a privileged account and a standard account, then associate the standard account with the RAM user
An OSS bucket in the same region as the cluster
Data Lake Storage activated (required only for Data Lake Storage tables)

Step 1: Open SQL Development

Log on to the AnalyticDB for MySQL console. In the upper-left corner, select a region. In the left-side navigation pane, click Clusters, then click the cluster ID.
In the left-side navigation pane, choose Job Development > SQL Development.
On the SQLConsole tab, select the Spark engine and a resource group.

Step 2: Create a database and an Iceberg external table

Run the following SQL statements in batch or interactive mode.

Data Lake Storage tables

Create a database.

Scope	Behavior
Specified at the database level	All tables in the database default to that Data Lake Storage bucket
Specified at the table level only	Only that table uses the specified bucket; if not specified at either level, an error occurs
Specified at both database and table level	The table uses its own bucket; other tables use the database-level bucket

CREATE DATABASE adb_external_db_iceberg
WITH DBPROPERTIES ('adb_lake_bucket' = '<your-lake-bucket-name>');

Replace <your-lake-bucket-name> with your Data Lake Storage bucket name (for example, adb-lake-cn-shanghai-6gml****). The adb_lake_bucket property controls where table data is stored:

Create an Iceberg external table.

SET spark.adb.lakehouse.enabled=true;
CREATE TABLE adb_external_db_iceberg.test_iceberg_tbl (
  `id` int,
  `name` string,
  `age` int
) USING iceberg
PARTITIONED BY (age)
TBLPROPERTIES ('adb_lake_bucket' = '<your-lake-bucket-name>');

Omit TBLPROPERTIES if you already set adb_lake_bucket at the database level.

Non-Data Lake Storage tables

Determine which database to use. Run SHOW CREATE DATABASE <db_name> to inspect an existing database. The database is usable if: Otherwise, create a new database:
- No Location parameter appears in the DDL, or
- Location is present and the Catalog parameter value is mix
```
CREATE DATABASE adb_external_db_iceberg;
```

Create an Iceberg external table.

Important

If the database has a Location parameter, the OSS path set in spark.iceberg.warehouse must match that Location value when you create, read, and write the table.

Placeholder	Description	Example
`<your-bucket-name>`	Name of your OSS bucket	`testBucketName`

SET spark.adb.version=3.5;
SET spark.iceberg.warehouse=oss://<your-bucket-name>/iceberg/;
CREATE TABLE adb_external_db_iceberg.test_iceberg_tbl (
  `id` int,
  `name` string,
  `age` int
) USING iceberg
PARTITIONED BY (age);

Replace the following placeholders:

Step 3: Write data to or delete data from the Iceberg external table

Data Lake Storage tables

Prepend the following session parameter before all write and delete statements:

SET spark.adb.lakehouse.enabled=true;

Write data

-- Append rows
INSERT INTO adb_external_db_iceberg.test_iceberg_tbl VALUES (1, 'lisa', 10), (2, 'jams', 20);

-- Overwrite the entire table
INSERT OVERWRITE adb_external_db_iceberg.test_iceberg_tbl VALUES (1, 'lisa', 10), (2, 'jams', 30);

-- Overwrite a static partition
INSERT OVERWRITE adb_external_db_iceberg.test_iceberg_tbl PARTITION(age=10) VALUES (1, 'anna');

-- Overwrite only matching dynamic partitions
SET spark.sql.sources.partitionOverwriteMode=dynamic;
INSERT OVERWRITE adb_external_db_iceberg.test_iceberg_tbl PARTITION(age) VALUES (1, 'bom', 10);

-- Update rows
UPDATE adb_external_db_iceberg.test_iceberg_tbl SET name = 'box' WHERE id = 2;

Warning

By default, INSERT OVERWRITE replaces all data in the table. To overwrite only the partitions matched by the new data, set spark.sql.sources.partitionOverwriteMode=dynamic before the statement. Without this setting, all existing rows are deleted.

Delete data

DELETE FROM adb_external_db_iceberg.test_iceberg_tbl WHERE id = 1;
DELETE FROM adb_external_db_iceberg.test_iceberg_tbl WHERE age = 20;

Non-Data Lake Storage tables

Prepend the following session parameters before all write and delete statements:

SET spark.adb.version=3.5;
SET spark.iceberg.warehouse=oss://<your-bucket-name>/iceberg/;

Write data

-- Append rows
INSERT INTO adb_external_db_iceberg.test_iceberg_tbl VALUES (1, 'Frank', 10), (2, 'Amy', 10);

-- Overwrite the entire table
INSERT OVERWRITE adb_external_db_iceberg.test_iceberg_tbl VALUES (1, 'Frank', 10), (2, 'Amy', 20);

-- Overwrite a static partition
INSERT OVERWRITE adb_external_db_iceberg.test_iceberg_tbl PARTITION(age=10) VALUES (1, 'Frank');

-- Overwrite only matching dynamic partitions
SET spark.sql.sources.partitionOverwriteMode=dynamic;
INSERT OVERWRITE adb_external_db_iceberg.test_iceberg_tbl PARTITION(age) VALUES (1, 'Bom', 10);

-- Update rows
UPDATE adb_external_db_iceberg.test_iceberg_tbl SET name = 'box' WHERE id = 2;

Warning

By default, INSERT OVERWRITE replaces all data in the table. To overwrite only the partitions matched by the new data, set spark.sql.sources.partitionOverwriteMode=dynamic before the statement. Without this setting, all existing rows are deleted.

Delete data

DELETE FROM adb_external_db_iceberg.test_iceberg_tbl WHERE id = 1;
DELETE FROM adb_external_db_iceberg.test_iceberg_tbl WHERE age = 20;

Step 4: Query data from the Iceberg external table

Data Lake Storage tables

SET spark.adb.lakehouse.enabled=true;
SELECT * FROM adb_external_db_iceberg.test_iceberg_tbl;

Non-Data Lake Storage tables

SET spark.adb.version=3.5;
SET spark.iceberg.warehouse=oss://<your-bucket-name>/iceberg/;
SELECT * FROM adb_external_db_iceberg.test_iceberg_tbl;

Both queries return output in the following format:

+---+----+---+
|id |name|age|
+---+----+---+
|1  |anna|10 |
|2  |jams|20 |
+---+----+---+

Step 5: Drop the Iceberg external table

Data Lake Storage tables

Drops the table and deletes all metadata and data from Data Lake Storage:

SET spark.adb.lakehouse.enabled=true;
DROP TABLE adb_external_db_iceberg.test_iceberg_tbl;

Non-Data Lake Storage tables

Drop the table from AnalyticDB for MySQL while keeping the data in OSS:

DROP TABLE adb_external_db_iceberg.test_iceberg_tbl;

Drop the table and permanently delete all data from OSS:

DROP TABLE adb_external_db_iceberg.test_iceberg_tbl purge;