Query CSV data in OSS with DLF

Step 1: Grant MaxCompute permissions

Your MaxCompute project account must be authorized to access DLF and OSS. You can grant the required permissions in one of the following ways:

• One-click authorization: If your MaxCompute project and DLF service are under the same account, click Authorize DLF for one-click authorization.

• Custom authorization: You can use custom authorization regardless of whether the accounts for the MaxCompute project and DLF service are the same.

Step 2: Prepare the OSS data

1. Log on to the OSS console and create a bucket. In this example, the bucket is named mc-lakehouse-dlf-oss. For more information, see Create buckets.

2. This example uses a partitioned table. In the bucket, create a root directory named mc_dlf_oss_pt/. In this directory, create four subdirectories and upload the corresponding files as follows:

   • direction=N/: vehicle1.csv

   • direction=NE/: vehicle2.csv

   • direction=S/: vehicle3.csv

   • direction=SW/: vehicle4.csv

Step 3: Discover OSS metadata with DLF

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

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

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

   Under the default Catalog List, click Create Database. Configure the parameters as follows:

   On the metadata management page, click the Database tab. In the default data catalog, click Create Database. Configure the following parameters:

   Parameter	Required	Description
   Catalog	Yes	In this example, the default data catalog is used.
   Database Name:	Yes	The custom database name must start with a letter, be 1 to 128 characters long, and contain only lowercase letters (a-z), uppercase letters (A-Z), digits (0-9), and underscores (_). For example, dlf_oss_csv.
   Database Description:	No	Enter a custom description.
   Select Path:	Yes	Database storage location, such as oss://mc-lakehouse-dlf-oss/metadb_save.

4. In the navigation pane on the left, choose Metadata > Metadata Discovery.

   On the Metadata Discovery page, click Create Extraction Task to open the Create Extraction Task page. Configure the parameters as follows:

   1. Set Extraction Source:

      Parameter	Required	Description
      Extraction Task Name	Yes	Enter a custom name for the crawler task.
      Select OSS Path	Yes	DLF automatically creates tables and partitions based on the OSS directory that you specified. Directory structure: oss://Selected path/Table name (Optional)/Partition name (Optional)/File name. For example, the data.csv file is used to extract metadata. If this file is saved to the oss://my-bucket/my-path/my-table/dt=1/ directory and the oss://my-bucket/my-path/ directory is selected, DLF uses my_table as the table name, dt as a partition, and the data.csv file as the table schema. All directory names that include the table name can contain letters, digits, and underscores (_), and must start with a letter.
      Exclusion Mode	No	Enter a regular expression to match the OSS directory that needs to be excluded
      Parse Format	Yes	Supported formats include Automatic Recognition, JSON, CSV, Parquet, ORC, Hudi, Delta Lake, and Avro.

   2. Set Extraction Target:

      Parameter	Required	Description
      Catalog	Yes	Select the target data catalog.
      Destination Database	Yes	Select the created database, for example, dlf_oss_csv.
      Destination Table Prefix	No	Enter the prefix of the destination table. It must be 1 to 128 characters in length, and can contain letters, digits, and underscores (_). It must start with a letter.
      Method to Handle Table Field Update	Yes	Add Columns and Retain Existing Columns: New columns are added, but existing columns cannot be deleted. Update Table Schema and Generate Table Results Based on the last detected table schema: A new table schema is generated based on the latest discovery. If you select this option, the discovery result overwrites any existing table with the same name, which may cause the original table to be lost or become unavailable. Ignore Updates and Not Modify Table: No tables are modified.
      Method to Process Deleted OSS Objects	Yes	Delete Metadata. Ignore Updates and Not Delete Tables: No tables are deleted.

   3. Set Extraction Task:

      Parameter	Required	Description
      RAM Role	Yes	Data Lake Formation assumes this role to access your resources. You can select the default system role AliyunDLFWorkFlowDefaultRole.
      Execution Policy	No	The execution policy for the job. Manual Scheduling
      Extraction Policy	Yes	Partial Data Extraction: Scans only a part of each file to discover metadata. The job takes less time, but the result is less accurate than that of a full discovery. You can modify the metadata information later. Extract All: Scans all data files to discover metadata. If the data volume is large, the job takes a long time, but the result is more accurate.

   4. Click Save and Run.

5. View the discovered tables.

   1. In the navigation pane on the left, choose Metadata > Metadata.

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

   3. Click the name of the target database to go to its details page. Click the Table List tab to view the crawled tables.

      In this example, the extracted table is mc_dlf_oss_pt.

6. If you add data to a new partition, you must run the crawler task again.

Step 4: Create a DLF+OSS data source

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

2. In the left-side 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 parameters. The following tables describe the parameters.

   Parameter	Required	Description
   External Data Source Type	Yes	Select DLF+OSS.
   External Data Source Name	Yes	Enter a custom name. The naming conventions are as follows: The name must start with a letter and can contain only lowercase letters, digits, and underscores (_). The name can be up to 128 characters in length. For example, dlf_oss_csv_new.
   Description	No	Enter a description as needed.
   Region	Yes	The current region is used by default.
   DLF endpoint	Yes	The DLF endpoint of the current region is used by default.
   OSS endpoint	Yes	The OSS endpoint of the current region is used by default.
   Role ARN	Yes	The Alibaba Cloud Resource Name (ARN) of the RAM role. This role must have permissions to access both DLF and OSS. Log in to the RAM console. In the left navigation bar, select Identities > Roles. In the Basic Information section, you can obtain the ARN. Example: acs:ram::124****:role/aliyunodpsdefaultrole.
   Foreign Server Supplemental Properties	No	Additional properties for the external data source. After you specify the properties, tasks that use this external data source can access the source system based on the defined behavior. Note For more information about the supported parameters, refer to future updates in the official documentation. More parameters will become available as the product evolves.

5. Click OK to create the external data source.

6. On the External Data Source page, find the data source and click Details in the Actions column to view its details.

Step 5: Create an external schema

Connect to MaxCompute and run the following command:

SET odps.namespace.schema=true;

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

The following table describes the parameters.

• external_schema: The custom external schema name. For example, es_dlf_oss_csv.

• external_data_source: The name of the external data source that you created. The project that contains the external schema must be in the same region as the external data source. For example, dlf_oss_csv.

• dlf_data_catalogue: DLF data catalog ID. To create a data catalog, see Create a data catalog. For example, 122****.

• dlf_database: The name of the database under the specified data catalog in DLF. For more information, see Database tables and functions. For example, dlf_oss_csv.

Step 6: Query OSS data with SQL

1. Log on to the MaxCompute client and query the tables in the external schema.

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

-- The following result is returned:
ALIYUN$xxx:mc_dlf_oss_pt

OK

2. Query the details of the mc_dlf_oss_pt table in the external schema.

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

-- The following result is returned:
+------------+------------+------------+------------+------------+------------+----------------+------------+
| _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 8: Copy data to data warehouse

1. Copy data from the external table to a data warehouse table.

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

2. Query the data in the copied table in the data warehouse.

   SELECT * FROM vehicle_copy;
   
   -- The following result is returned:
   +------------+------------+------------+------------+------------+------------+----------------+------------+
   | _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         | 
   +------------+------------+------------+------------+------------+------------+----------------+------------+