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 management

Iceberg partitioning is fundamentally different from traditional Hive partitioning:

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.

The following examples show common partitioning scenarios for different data characteristics:

• Partition by a time dimension (day, most common): Partitioning time-series data by day is the most common choice, balancing query performance and management overhead. 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/';

• Partition a low-cardinality categorical column (identity): For columns with only a few distinct values (such as market segment or region), partition by the column value directly.

  -- Partition by mktsegment, which has only a few enumerated values. Identity partitioning is a good fit.
  CREATE TABLE lake_db.customer (
      c_custkey     BIGINT,
      c_name        STRING,
      c_acctbal     DECIMAL(15,2),
      c_mktsegment  STRING   -- Low cardinality: 'AUTOMOBILE', 'BUILDING', 'FURNITURE', and so on
  )
  PARTITIONED BY (c_mktsegment)
  STORED AS ICEBERG
  LOCATION 'oss://<your-bucket>/warehouse/lake_db/customer/';

• Partition high-frequency events by hour (hour): When data is written frequently and requires near real-time monitoring, partition by hour.

  -- Partition by the hour of the receipt timestamp to support near real-time monitoring.
  CREATE TABLE lake_db.lineitem_realtime (
      l_orderkey      BIGINT,
      l_partkey       BIGINT,
      l_quantity      DECIMAL(15,2),
      l_receipttime   TIMESTAMP   -- Receipt time with second-level precision
  )
  PARTITIONED BY (hour(l_receipttime))
  STORED AS ICEBERG
  LOCATION 'oss://<your-bucket>/warehouse/lake_db/lineitem_realtime/';

• Partition a high-cardinality column by hash bucket (bucket): Hashing a high-cardinality column (such as an ID) into buckets avoids small files and improves JOIN performance.

  -- Hash the high-cardinality foreign key l_partkey into 64 buckets for even data distribution.
  CREATE TABLE lake_db.lineitem (
      l_orderkey      BIGINT,
      l_partkey       BIGINT,      -- High cardinality (about 20 million distinct values)
      l_quantity      DECIMAL(15,2),
      l_shipdate      DATE
  )
  PARTITIONED BY (bucket(l_partkey, 64))
  STORED AS ICEBERG
  LOCATION 'oss://<your-bucket>/warehouse/lake_db/lineitem/';

• Partition by a string prefix (truncate): Group data by the first several characters of a string. This is useful when records are naturally grouped by a prefix.

  -- Partition by the first 3 characters of the phone number (for example, '13-' for a specific carrier prefix).
  CREATE TABLE lake_db.customer_by_phone (
      c_custkey     BIGINT,
      c_name        STRING,
      c_phone       STRING       -- Format: '13-888-999-1234'
  )
  PARTITIONED BY (truncate(c_phone, 3))
  STORED AS ICEBERG
  LOCATION 'oss://<your-bucket>/warehouse/lake_db/customer_by_phone/';

• Multi-level composite partitioning: Combine multiple partition transforms to balance the number of partitions against data distribution. This is suitable for querying large datasets.

  -- User event log: partition by day, hash-bucket the high-cardinality ID, and truncate the region prefix (three-level composite partitioning).
  CREATE TABLE lake_db.user_event_log (
      event_id      BIGINT,
      user_id       BIGINT,          -- High-cardinality user ID
      event_type    STRING,
      event_time    TIMESTAMP,       -- Timestamp with second-level precision
      country_code  STRING           -- Country code, such as 'CN', 'US', or 'DE'
  )
  PARTITIONED BY (
      day(event_time),            -- Level 1: partition by day for efficient time-based pruning
      bucket(user_id, 64),        -- Level 2: hash the high-cardinality user_id into 64 buckets to avoid small files
      truncate(country_code, 2)   -- Level 3: truncate the country code to the first 2 characters for regional aggregation
  )
  STORED AS ICEBERG
  LOCATION 'oss://<your-bucket>/warehouse/lake_db/user_event_log/';

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;

-- Drop a database (the database must contain no tables, otherwise an error is returned)
DROP DATABASE IF EXISTS lake_db;

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, set format_version to '3' when you create the table.

-- Create a table with format_version=3 to support row-level DELETE
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'
);

-- 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.

Predicate pushdown takes effect when the following conditions are met:

• The columns in the WHERE clause have statistics.

• The predicate is a simple comparison (=, <, >, <=, >=, IN, or BETWEEN).

• The column type matches the statistics type.

-- Predicate pushdown takes effect: order_id has min/max statistics
SELECT * FROM lake_db.orders
WHERE order_id = 1001;

-- Range predicate pushdown
SELECT * FROM lake_db.orders
WHERE total_amount BETWEEN 100 AND 500;

Query notes

• Partition pruning takes effect only when the WHERE clause directly references a partition column or a partition transform function.

• Complex predicates (such as function calls or OR conditions) may affect the pushdown result.

• The performance of JOINs across Iceberg tables depends on the data volume and partition design.