Iceberg external tables (XIHE SQL)

Syntax

CREATE TABLE [IF NOT EXISTS] <db>.<table> (
    <col1>  <type1>  [COMMENT '<comment>'],
    <col2>  <type2>  [COMMENT '<comment>'],
    ...
)
[COMMENT '<table_comment>']
[PARTITIONED BY (<partition_expr1>[, <partition_expr2>, ...])]
STORED AS ICEBERG
[LOCATION '<oss_path>']
[TBLPROPERTIES (
    '<key1>' = '<value1>',
    ...
)];

The following table properties can be configured in TBLPROPERTIES:

External lake table example

-- Create a database
CREATE DATABASE IF NOT EXISTS lake_db;

-- Create an external lake Iceberg table
CREATE TABLE lake_db.orders (
    order_id     BIGINT       COMMENT 'Order ID',
    user_id      BIGINT       COMMENT 'User ID',
    status       STRING       COMMENT 'Order status',
    total_amount DECIMAL(18, 2) COMMENT 'Order amount',
    created_at   TIMESTAMP    COMMENT 'Order time'
)
COMMENT 'Orders table'
PARTITIONED BY (dt DATE)
STORED AS ICEBERG
LOCATION 'oss://<your-bucket>/warehouse/lake_db/orders/';

Internal lake table example

CREATE TABLE lake_db.orders (
    order_id     BIGINT       COMMENT 'Order ID',
    user_id      BIGINT       COMMENT 'User ID',
    status       STRING       COMMENT 'Order status',
    total_amount DECIMAL(18, 2) COMMENT 'Order amount',
    created_at   TIMESTAMP    COMMENT 'Order time'
)
COMMENT 'Orders table'
PARTITIONED BY (dt DATE)
STORED AS ICEBERG
TBLPROPERTIES (
    'catalog_type'    = 'ADB',
    'adb_lake_bucket' = 'adb-lake-cn-<region>-xxxx'
);

CTAS (CREATE TABLE AS SELECT)

CTAS creates a table and writes the query results at the same time. The column names and types are inferred from the SELECT statement.

CREATE TABLE lake_db.orders_copy
PARTITIONED BY (dt DATE)
STORED AS ICEBERG
LOCATION 'oss://<your-bucket>/warehouse/lake_db/orders_copy/'
AS SELECT * FROM lake_db.orders;

Partition transform functions

In addition to identity partitioning (using column values directly), Iceberg supports transform function partitioning based on existing columns. You do not need to specify partition values during writes. The engine automatically routes data based on column values.

When you use day(created_at) for partitioning, you do not need to maintain a separate dt column. Iceberg automatically extracts the date from created_at for partitioning:

CREATE TABLE lake_db.orders_by_day (
    order_id     BIGINT,
    user_id      BIGINT,
    status       STRING,
    total_amount DECIMAL(18, 2),
    created_at   TIMESTAMP
)
PARTITIONED BY (day(created_at))
STORED AS ICEBERG
LOCATION 'oss://<your-bucket>/warehouse/lake_db/orders_by_day/';

Other DDL operations

-- View the complete CREATE TABLE statement
SHOW CREATE TABLE lake_db.orders;

-- View the column structure
DESCRIBE lake_db.orders;

-- Drop a table
DROP TABLE IF EXISTS lake_db.orders;

INSERT INTO (append)

INSERT INTO lake_db.orders
SELECT * FROM VALUES
    (1001, 501, 'paid', 299.90, TIMESTAMP '2026-06-11 10:00:00', DATE '2026-06-11'),
    (1002, 502, 'pending', 158.00, TIMESTAMP '2026-06-11 10:05:00', DATE '2026-06-11'),
    (1003, 503, 'shipped', 450.00, TIMESTAMP '2026-06-11 10:10:00', DATE '2026-06-12')
AS t(order_id, user_id, status, total_amount, created_at, dt);

INSERT OVERWRITE (overwrite)

On a partitioned table, INSERT OVERWRITE uses a dynamic partition overwrite strategy: only partitions present in the SELECT result are replaced, leaving other partitions unchanged.

-- Only overwrite the data in the dt='2026-06-11' partition. Other partitions are not affected.
INSERT OVERWRITE lake_db.orders
SELECT * FROM VALUES
    (2001, 601, 'paid', 999.00, TIMESTAMP '2026-06-11 12:00:00', DATE '2026-06-11')
AS t(order_id, user_id, status, total_amount, created_at, dt);

DELETE (row-level delete)

Iceberg tables support row-level deletion. To use DELETE, the following requirements must be met:

• format_version is set to '3' when the table is created.

• identifier-fields (identifier columns, similar to primary keys) is specified in TBLPROPERTIES when the table is created.

-- Create a table: specify format_version=3 and identifier-fields
CREATE TABLE lake_db.orders_v3 (
    order_id     BIGINT,
    user_id      BIGINT,
    status       STRING,
    total_amount DECIMAL(18, 2),
    created_at   TIMESTAMP
)
PARTITIONED BY (day(created_at))
STORED AS ICEBERG
LOCATION 'oss://<your-bucket>/warehouse/lake_db/orders_v3/'
TBLPROPERTIES (
    'format-version'    = '3',
    'identifier-fields' = '[order_id]'
);

-- Delete rows that meet the condition
DELETE FROM lake_db.orders_v3 WHERE status = 'cancelled';

Basic queries

SELECT * FROM lake_db.orders WHERE dt = DATE '2026-06-11';

SELECT dt, COUNT(*) AS cnt, SUM(total_amount) AS total
FROM lake_db.orders
GROUP BY dt;

Partition pruning

When the WHERE clause references partition columns or transform functions, the engine automatically prunes irrelevant partitions and scans only matching data files.

-- Identity partitioning: match the partition column directly
SELECT * FROM lake_db.orders WHERE dt = DATE '2026-06-11';

-- day(col) partitioning: match the time range
SELECT * FROM lake_db.orders_by_day
WHERE created_at >= TIMESTAMP '2026-06-11 00:00:00'
  AND created_at <  TIMESTAMP '2026-06-12 00:00:00';

Predicate pushdown

Iceberg pushes WHERE predicates down to the data file level and uses Parquet file statistics (min/max/null count) to skip row groups that do not match. This reduces the amount of data read. Supported predicates include equality (=), range (<, >, BETWEEN), and IN.

Add columns

ALTER TABLE lake_db.orders ADD COLUMNS (
    region STRING COMMENT 'Order region'
);

-- Verify
DESCRIBE lake_db.orders;

The new column is appended to the end of the column list. The value of the new column is NULL for existing rows.

Type promotion

Safe upward type promotion is supported. For example, INT to BIGINT:

ALTER TABLE lake_db.orders CHANGE COLUMN order_id order_id BIGINT;