Delta external tables (XIHE SQL)

Capability overview

Prerequisites

• The cluster edition is Enterprise Edition, Basic Edition, or Data Lakehouse Edition.

• The kernel version of the cluster is 3.2.8 or later.

• An external database is created. For more information, see CREATE EXTERNAL DATABASE.

• A Delta table directory (containing _delta_log) that was created by an engine such as Spark, Flink, or Databricks already exists on OSS.

Create a table

CREATE EXTERNAL TABLE [IF NOT EXISTS] <db>.<table> (
    <col1>  <type1>,
    <col2>  <type2>,
    ...
)
STORED AS DELTA
LOCATION '<delta_table_location>';

-- Create an external database
CREATE EXTERNAL DATABASE IF NOT EXISTS ext_delta_db;

-- Register a Delta external table
CREATE EXTERNAL TABLE ext_delta_db.delta_orders (
    order_id     BIGINT,
    user_id      BIGINT,
    order_status STRING,
    total_price  DECIMAL(18, 2),
    order_date   DATE
)
STORED AS DELTA
LOCATION 'oss://<your-bucket>/warehouse/delta_orders/';

-- View column structure
DESCRIBE ext_delta_db.delta_orders;

-- Drop an external table
DROP TABLE IF EXISTS ext_delta_db.delta_orders;

Query data

-- Query all data from the table
SELECT * FROM ext_delta_db.delta_orders LIMIT 10;

-- Aggregate query
SELECT order_status, COUNT(*) AS order_count, SUM(total_price) AS total
FROM ext_delta_db.delta_orders
GROUP BY order_status;

-- Conditional filtering
SELECT order_id, order_status, total_price
FROM ext_delta_db.delta_orders
WHERE order_status IN ('PAID', 'SHIPPED') AND total_price >= 100
ORDER BY total_price DESC;

-- Single-value filtering
SELECT * FROM ext_delta_db.delta_orders
WHERE order_date = DATE '2026-06-11';

-- Range filtering
SELECT order_date, COUNT(*) AS cnt, SUM(total_price) AS total
FROM ext_delta_db.delta_orders
WHERE order_date >= DATE '2026-06-01' AND order_date < DATE '2026-07-01'
GROUP BY order_date;

-- Read only the order_id and total_price columns
SELECT order_id, total_price
FROM ext_delta_db.delta_orders
WHERE total_price BETWEEN 100 AND 500;

-- JOIN a Delta external table with another table
SELECT o.order_id, o.total_price, u.user_name
FROM ext_delta_db.delta_orders o
JOIN dim_db.users u ON o.user_id = u.user_id
WHERE o.order_date = DATE '2026-06-11';

Supported data types

Limits

• Delta external tables are primarily used to query existing Delta tables. The table structure, partitions, and snapshots are maintained by _delta_log.

• Only querying existing Delta tables is supported. INSERT, UPDATE, DELETE, and MERGE operations through XIHE SQL are not supported.

• Only Delta tables whose underlying data format is Parquet are supported. Non-Parquet formats are not supported.

• Reading Delta tables with Deletion Vectors enabled is supported. Delete markers are automatically applied during queries.

• LOCATION must point to the Delta table root directory (the directory that contains _delta_log). It cannot point to the _delta_log directory itself or a partition subdirectory.

• We recommend that the columns in the external table DDL be consistent with the underlying Delta schema. The final query schema is determined by _delta_log. Type mismatches may cause query failures.

• DROP TABLE only drops the external table definition in AnalyticDB. It does not delete the Delta table directory or data files on OSS.

• ALTER TABLE only modifies the external table definition in AnalyticDB without affecting the underlying Delta schema. The query schema is always determined by _delta_log. To evolve the Delta schema, make changes in the upstream production pipeline.

• Decimal partition columns can be read, but partition pruning may have limited effect when these columns are used as filter conditions.

• Creating new Delta partitioned tables through external table DDL is not supported. Existing partitioned tables can be read and benefit from partition pruning.

• Complex types (ARRAY, MAP) can be read. Complex filter conditions on complex types are typically applied by the execution engine.

• If the Delta log schema and the underlying Parquet file schema are inconsistent, a schema mismatch error may occur during queries.

• Partition values are derived from the string representation in the Delta log. An error may occur during partition value conversion if the partition column type does not match expectations.

Related documentation

• To write to Delta tables, use the Spark SQL engine. For more information, see Read and write data to Delta external tables.

• For more information about how to create data lake tables, see Create data lake tables.

• For more information about the CREATE EXTERNAL TABLE syntax, see CREATE EXTERNAL TABLE.