MaxCompute:DLF+OSS external schema

Step 1: Enable services and grant permissions

1. Enable Data Lake Formation (DLF).

2. Enable OSS.

3. Grant MaxCompute access to DLF and OSS. By default, the MaxCompute project account has no permissions to access DLF or OSS. Grant permissions using one of the following methods: After completing authorization, record the ARN of the RAM role. You will enter it as the RoleARN in Step 2. To find the ARN: Example ARN: acs:ram::124****:role/aliyunodpsdefaultrole

   1. Log on to the Resource Access Management (RAM) console.

   2. In the left navigation pane, choose Identities > Roles.

   3. Click the role name. The ARN is shown in the Basic Information section.

   Method	When to use
   One-click authorization	The account that created the MaxCompute project is the same account used to deploy DLF and OSS. Click Authorize DLF and OSS to complete authorization in one step.
   Custom authorization	The accounts differ, or you need fine-grained control. See Custom authorization for DLF.

Step 2: Create a DLF+OSS external data source

1. Log on to the MaxCompute console and select a region in the upper-left corner.MaxCompute console

2. In the left navigation pane, choose Manage Configurations > External Data Source.

3. On the External Data Source page, click Create External Data Source.

4. In the Create External Data Source dialog box, configure the following parameters:

   Parameter	Required	Description
   External Data Source Type	Required	Select DLF+OSS.
   External Data Source Name	Required	Enter a custom name. Must start with a letter and contain only lowercase letters, underscores (_), and digits. Maximum 128 characters. Example: dlf_oss_schema.
   Description	Optional	Enter a description.
   Region	Required	Defaults to the current region.
   DLF Endpoint	Required	Defaults to the DLF endpoint for the current region.
   OSS Endpoint	Required	Defaults to the OSS endpoint for the current region.
   RoleARN	Required	The ARN of the RAM role from Step 1 that has permissions to access both DLF and OSS. Example: acs:ram::124****:role/aliyunodpsdefaultrole.
   Foreign Server Supplemental Properties	Optional	Additional properties for the external data source. After you specify these properties, tasks that use this data source can access the source system based on the defined behavior. Note For information about supported parameters, see the official documentation. More parameters will be supported as the product evolves.

5. Click OK. Record the External Data Source Name you entered (for example, dlf_oss_schema). You will use it as the dlfoss_foreign_server_name parameter in Step 4.

Step 3: Create a DLF catalog and database

1. Create a DLF data catalog. Record the Catalog ID (for example, external_schema_dlf_catalog). You will use it as the dlf_catalog_id parameter in Step 4.

   1. Log on to the Data Lake Formation (DLF) console and select a region in the upper-left corner.Data Lake Formation (DLF) console

   2. In the left navigation pane, choose Metadata > Metadata.

   3. Click the Catalog List tab, then click New Catalog.

   4. On the New Catalog page, set the following:

      • Catalog ID: A unique identifier for this catalog. Example: external_schema_dlf_catalog.

      • Location: The OSS bucket path where catalog data is stored. Example: oss://external-schema/dlf-oss.

2. Create a database in the catalog. Record the Database Name (for example, dlf_oss_db). You will use it as the database_name parameter in Step 4.

   1. On the Metadata page, click the Database tab.

   2. In the Catalog List section, click Create Database and configure the following parameters:

      Parameter	Required	Description
      Catalog	Required	Select the catalog you created in the previous step. Example: external_schema_dlf_catalog.
      Database Name	Required	1–128 characters, starting with a letter. Can contain letters, digits, and underscores. Example: dlf_oss_db.
      Database Description	Optional	Enter a description.
      Select Path	Required	The OSS path for this database. Example: oss://external-schema/dlf-oss/database.

Step 4: Create an external schema

1. Enable schema support for your MaxCompute project. External schema operations require a MaxCompute project with schema support enabled.

   1. In the MaxCompute console, choose Manage Configurations > Projects.

   2. Find your target project and click Enable Schema in the Actions column.

2. Before running SQL operations on an external schema, add the following statement before your SQL:

   SET odps.namespace.schema=true;

3. In the MaxCompute client, run the following command to create the external schema. Use the values you recorded in Steps 2 and 3:

   • dlfoss_foreign_server_name: the external data source name from Step 2 (for example, dlf_oss_schema)

   • dlf_catalog_id: the DLF catalog ID from Step 3 (for example, external_schema_dlf_catalog)

   • database_name: the DLF database name from Step 3 (for example, dlf_oss_db)

   CREATE EXTERNAL SCHEMA IF NOT EXISTS <external_schema_name>
   WITH <dlfoss_foreign_server_name>
   ON '<dlf_catalog_id>.<database_name>';

   Example:

   CREATE EXTERNAL SCHEMA IF NOT EXISTS test_schema
   WITH dlf_oss_schema
   ON 'external_schema_dlf_catalog.dlf_oss_db';

Step 5: Query data from the external schema

List tables and query data using standard SQL. Schema operations such as listing and deleting follow the same commands as internal schemas. For a full list of schema operations, see Schema operations.

List tables in the external schema

-- Method 1: List tables directly
SHOW TABLES IN test_schema;

-- Method 2: Switch to the schema first, then list tables
USE SCHEMA test_schema;
SHOW TABLES;

Sample output:

ALIYUN$xxx@test.aliyunid.com:hellocsv
ALIYUN$xxx@test.aliyunid.com:t1
ALIYUN$xxx@test.aliyunid.com:t2
ALIYUN$xxx@test.aliyunid.com:t3

Query a table

SELECT * FROM test_project.test_schema.hellocsv;

Copy external data into MaxCompute

CREATE TABLE AS SELECT cannot run directly on external schema objects. Switch to the default schema first:

-- Copy data from the external schema into a MaxCompute table
CREATE TABLE hellocsv_copy AS SELECT * FROM test_project.test_schema.hellocsv;

-- Query the copied data
SELECT * FROM hellocsv_copy;