DLF+OSS external schema

Step 1: Enable services and grant permissions

1. Enable Data Lake Formation (DLF)

2. Activate OSS

3. Grant permissions

   When you build a data lakehouse with MaxCompute, DLF, and OSS, you must grant permissions. This is because the MaxCompute project account does not have permissions to access DLF or OSS by default. You can grant permissions in one of the following ways:

   • One-click authorization: Use this method if the account used to create the MaxCompute project is the same as the account used to deploy DLF and OSS. Click Authorize DLF and OSS to grant the required permissions.

   • Custom authorization: Use this method regardless of whether the account used to create the MaxCompute project is the same as the account used to deploy DLF and OSS. For more information, see Custom authorization for DLF.

Step 2: Create a DLF+OSS external 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	Required	Select DLF+OSS.
   External Data Source Name	Required	Enter a custom name. The name must follow these rules: It must start with a letter and can contain only lowercase letters, underscores (_), and digits. It cannot exceed 128 characters in length. Example: dlf_oss_schema.
   Description	Optional	Enter a description as needed.
   Region	Required	The current region is used by default.
   DLF endpoint	Required	The DLF Endpoint of the current region is used by default.
   OSS endpoint	Required	The OSS Endpoint of the current region is used by default.
   Role ARN	Required	The Alibaba Cloud Resource Name (ARN) of the RAM role. This role must have permissions to access both the DLF and OSS services. 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	Optional	Specifies 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 the supported parameters, see the official documentation. More parameters will be supported as the product evolves.

5. Click OK to create the external data source.

Step 3: Create a DLF data catalog and a database

1. Create a DLF data catalog

   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 Catalog List tab.

   4. On the New Catalog page, enter a custom Catalog ID and set the Location to an OSS bucket directory.

      1. Example Catalog ID: external_schema_dlf_catalog

      2. Example Location: oss://external-schema/dlf-oss

2. Create a database

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

   2. Under the newly created Catalog List, click Create Database. Configure the following parameters:

      Parameter	Required	Description
      Catalog	Required	Example: external_schema_dlf_catalog.
      Database Name:	Required	Enter a custom database name. The name must be 1 to 128 characters in length, start with a letter, and can contain letters, digits, and underscores (_). Example: dlf_oss_db.
      Database Description:	Optional	Enter a custom description.
      Select Path:	Required	The storage location of the database. Example: oss://external-schema/dlf-oss/database.

Step 4: Create an external schema

1. You can perform external schema operations only in a MaxCompute project that supports schemas.

   1. In the left-side navigation pane, choose Manage Configurations > Projects.

   2. On the Projects page, click Enable Schema in the Actions column for the target project.

2. Before you perform schema operations or query data from an external schema, you must enable schema support for SQL syntax. To do this, add the SET odps.namespace.schema=true; statement before your SQL statements.

3. In the MaxCompute client, run the following command to create an external schema that references the DLF and OSS external data source:

   CREATE EXTERNAL SCHEMA IF NOT EXISTS <external_schema_name>
   with <dlfoss_foreign_server_name>
   ON '<dlf_calaog_id>.<database_name>';

   The following table describes the parameters.

   • external_schema_name: The name of the external schema. Example: test_schema.

   • dlfoss_foreign_server_name: The name of the external data source that you created. The project to which the external schema belongs must be in the same region as the external data source. Example: dlf_oss_schema.

   • dlf_calaog_id: The ID of the DLF data catalog. Example: external_schema_dlf_catalog

   • database_name: The name of the database in the specified DLF data catalog. Example: dlf_oss_db.

Step 5: Use SQL to access the data source

You can list, view, and delete external schemas by using the same commands as for internal schemas. For more information, see Schema operations.

• List the names of tables in DLF and OSS

  • Method 1:

    SHOW tables IN <external_schema_name>; -- external_schema_name is the name of the external schema.

  • Method 2:

    USE SCHEMA <external_schema_name>; -- external_schema_name is the name of the external schema.
    SHOW tables;

• Examples

  • Query the names of all tables in the test_schema external schema.

    USE SCHEMA test_schema;
    SHOW TABLES;

    Sample result:

    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 data from the hellocsv table in the test_schema schema of the test_project project.

    SELECT * FROM test_project.test_schema.hellocsv;

  • Copy data from the federated foreign table hellocsv to a data warehouse.

    MaxCompute does not allow you to run CREATE TABLE AS operations on external schema objects. You must switch to the default schema before you run the following statements:

    -- Copy data from the federated foreign table to the data warehouse.
    CREATE TABLE hellocsv_copy AS SELECT * FROM test_project.test_schema.hellocsv;
    
    -- Query the copied data in the data warehouse.
    SELECT * FROM hellocsv_copy;