MaxCompute:Read CSV data from a data lake using DLF 1.0 and OSS

Prerequisites

Before you begin, ensure that you have:

• An activated OSS service

• An activated DLF service

• A MaxCompute project with schema support for project-level metadata enabled

• MaxCompute, OSS, and DLF deployed in the same region

Supported regions:

Overview

The setup follows four stages:

1. Authorize — grant MaxCompute permissions to access DLF and OSS.

2. Prepare data — upload CSV files to OSS in a partitioned directory structure.

3. Extract metadata — use DLF to scan OSS directories and create table definitions automatically.

4. Connect and query — create an external data source and external schema in MaxCompute, then run SQL against the OSS data.

Step 1: Grant MaxCompute access to DLF and OSS

The account associated with your MaxCompute project needs permissions to access both DLF and OSS. Two authorization methods are available:

• One-click authorization — if you use the same Alibaba Cloud account for MaxCompute and DLF, click Authorize DLF to complete authorization in one step.

• Custom authorization — if you use different accounts, follow the custom authorization guide.

Step 2: Prepare the OSS data

This example uses a partitioned table where the partition key is direction. Each partition contains a CSV file with vehicle tracking data.

1. Log on to the OSS console and create a bucket. This example uses a bucket named mc-lakehouse-dlf-oss.

2. Create a root folder named mc_dlf_oss_pt/ in the bucket, then create four subdirectories and upload the corresponding files: DLF uses the root folder name (mc_dlf_oss_pt) as the table name and each direction=<value> subdirectory as a partition.

   Directory and table names can contain only letters, digits, and underscores, and must start with a letter.

   Subdirectory	File
   direction=N/	vehicle1.csv
   direction=NE/	vehicle2.csv
   direction=S/	vehicle3.csv
   direction=SW/	vehicle4.csv

Step 3: Extract OSS metadata using DLF 1.0

Step 4: Create a DLF 1.0 and OSS external data source

1. Log on to the MaxCompute console and select your region.MaxCompute console

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

3. Click Create External Data Source and configure the following parameters:

   Parameter	Required	Description
   External Data Source Type	Yes	Select DLF+OSS.
   External Data Source Name	Yes	A name that starts with a letter, contains only lowercase letters, underscores, and digits, and is no more than 128 characters. Example: dlf_oss_csv_new.
   Description	No	An optional description.
   Region	Yes	Defaults to the current region.
   DLF Endpoint	Yes	Defaults to the DLF endpoint for the current region.
   OSS Endpoint	Yes	Defaults to the OSS endpoint for the current region.
   RoleARN	Yes	The Alibaba Cloud Resource Name (ARN) of the RAM role that has permissions to access both DLF and OSS. To find the ARN, go to the RAM console, choose Identities > Roles, and copy the ARN from the Basic Information section. Example: acs:ram::124****:role/aliyunodpsdefaultrole.
   Foreign Server Supplemental Properties	No	Specify additional attributes for the external data source. After you specify the attributes, tasks that use this data source can access the source system based on the defined behavior. For information about supported parameters, see future updates in the official documentation. More parameters will be available as the product evolves.

4. Click OK.

5. On the External Data Source page, find the new data source and click Details in the Actions column to verify the configuration.

Step 5: Create an external schema

Connect to MaxCompute and run the following SQL:

SET odps.namespace.schema=true;

CREATE EXTERNAL SCHEMA IF NOT EXISTS <external_schema>
WITH <external_data_source>
ON '<dlf_data_catalogue>.dlf_database';

Replace the placeholders with your values:

Step 6: Query OSS data with SQL

1. List the tables available in the external schema:

   SET odps.namespace.schema=true;
   USE schema es_dlf_oss_csv;
   SHOW tables IN es_dlf_oss_csv;

   Expected output:

   ALIYUN$xxx:mc_dlf_oss_pt
   
   OK

2. Query data from a specific partition:

   SET odps.namespace.schema=true;
   SELECT * FROM <project_name>.es_dlf_oss_csv.mc_dlf_oss_pt WHERE direction='NE';

   Expected output:

   +------------+------------+------------+------------+------------+------------+----------------+------------+
   | _c0        | _c1        | _c2        | _c3        | _c4        | _c5        | _c6            | direction  |
   +------------+------------+------------+------------+------------+------------+----------------+------------+
   | 1          | 2          | 13         | 1          | 46.81006   | -92.08174  | 9/14/2014 0:00 | NE         |
   | 1          | 3          | 48         | 1          | 46.81006   | -92.08174  | 9/14/2014 0:00 | NE         |
   | 1          | 9          | 4          | 1          | 46.81006   | -92.08174  | 9/15/2014 0:00 | NE         |
   +------------+------------+------------+------------+------------+------------+----------------+------------+

Step 7: Copy data to a MaxCompute table

For better query performance on frequently accessed data, copy it to a MaxCompute internal table using CREATE TABLE AS SELECT (CTAS). Internal tables do not scan OSS files at query time.

1. Copy a partition to an internal table:

   CREATE TABLE vehicle_copy AS
   SELECT * FROM <project_name>.es_dlf_oss_csv.mc_dlf_oss_pt
   WHERE direction='NE';

2. Query the internal table to verify the data:

   SELECT * FROM vehicle_copy;

   Expected output:

   +------------+------------+------------+------------+------------+------------+----------------+------------+
   | _c0        | _c1        | _c2        | _c3        | _c4        | _c5        | _c6            | direction  |
   +------------+------------+------------+------------+------------+------------+----------------+------------+
   | 1          | 2          | 13         | 1          | 46.81006   | -92.08174  | 9/14/2014 0:00 | NE         |
   | 1          | 3          | 48         | 1          | 46.81006   | -92.08174  | 9/14/2014 0:00 | NE         |
   | 1          | 9          | 4          | 1          | 46.81006   | -92.08174  | 9/15/2014 0:00 | NE         |
   +------------+------------+------------+------------+------------+------------+----------------+------------+

Next steps

• Manage external schemas

• Databases, tables, and functions in DLF

• Create a data catalog in DLF