MaxCompute:Delta Lake external tables

Prerequisites

• The Alibaba Cloud account or RAM user is granted permissions to access OSS. Alibaba Cloud accounts, RAM users, and RAM roles can access OSS external tables. For more information about authorization, see STS authorization.

• A MaxCompute project is created.

  MaxCompute is deployed only in specific regions. To prevent a cross-region data connectivity issue, we recommend that you use a bucket in the same region as your MaxCompute project.

• The Alibaba Cloud account or RAM user is granted the CreateTable permission on your project. For more information about table operation permissions, see MaxCompute permissions.

• (Optional) An OSS bucket, OSS directories, and OSS data files are prepared. For more information, see Create buckets, Manage directories, and Simple upload.

  MaxCompute can automatically create an OSS directory in OSS. For SQL statements that include external tables and UDFs, you can execute operations to read and write external tables and UDFs with a single SQL statement. You can also manually create an OSS directory.

Limits

• OSS external tables do not support the clustering.

• A single file cannot exceed 3 GB. If one does, we recommend that you split it into multiple files.

Notes

• Delta Lake external tables only support reading data from all files that are mapped to the external table. They do not support automatic hiding of system columns, incremental reads, snapshot reads, or write operations. To implement atomicity, consistency, isolation, and durability (ACID) for read and write operations, use MaxCompute Delta tables or Paimon external tables.

• The Delta Lake software development kit (SDK) version that is integrated into MaxCompute by default is io.delta:delta-core_2.11:0.2.0.5. MaxCompute does not guarantee forward or backward compatibility of the Delta Lake SDK. The open source community ensures compatibility.

• MaxCompute supports only the released external table features that are integrated with the specified Delta Lake SDK version. MaxCompute will not update the versions or evolve the features. We recommend that you use Paimon external tables to read data lake table formats.

Supported data types

For more information about MaxCompute data types, see MaxCompute V1.0 data types and MaxCompute V2.0 data types.

Create an external table

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
(
   <col_name> <data_type>,
   ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS
INPUTFORMAT 'io.delta.hive.DeltaInputFormat'
OUTPUTFORMAT 'io.delta.hive.DeltaOutputFormat'
LOCATION 'oss_location';

Query analysis

• For more information about the SELECT syntax, see Read OSS data.

• For more information about optimizing query plans, see Query optimization.

Examples

This example shows how to create a Delta Lake external table using the built-in open source data parser and query data from OSS.

1. Prepare data.

   Log on to the OSS console and upload the Delta Lake test data files to the oss-mc-test/Demo_delta_pt/dt=20250612/ directory in your OSS bucket. For more information, see Simple upload.

2. Create a Delta Lake external table.

   CREATE EXTERNAL TABLE test_delta_pt (
       id INT
   )
   PARTITIONED BY (dt STRING )
   ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
   STORED AS
   INPUTFORMAT 'io.delta.hive.DeltaInputFormat'
   OUTPUTFORMAT 'io.delta.hive.DeltaOutputFormat'
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo_delta_pt/';

3. Import partition data. If the OSS foreign table that you create is a partitioned table, you must also import the partition data. For more information, see Syntax for adding partition data to an OSS foreign table.

   MSCK REPAIR TABLE test_delta_pt ADD PARTITIONS;

4. Query the Delta Lake external table.

   SELECT * FROM test_delta_pt WHERE dt='20250612';