MaxCompute:Data Lakehouse Solution 2.0 user guide

Step 1: Grant permissions

Step 2: Create a DLF+OSS foreign server

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

2. In the left navigation pane, choose Tenant Management > Foreign Server.

3. On the Foreign Server page, click Create Foreign Server.

4. In the Add Foreign Server dialog box, configure the parameters as described in the following table.

   Parameter	Description
   Foreign server type	Select DLF+OSS.
   Foreign server name	Enter a custom name. The naming conventions are as follows: It must start with a letter and can contain only lowercase letters, underscores (_), and digits. It cannot exceed 128 characters in length.
   Foreign server description	Enter a description as needed.
   Region	The current region is selected by default.
   DLF Endpoint	The DLF endpoint for the current region is used by default.
   OSS Endpoint	The OSS endpoint for the current region is used by default.
   RoleARN	The Alibaba Cloud Resource Name (ARN) of the RAM role. This role must have permissions to access both DLF and OSS services. You can log on to the RAM console. In the navigation pane on the left, choose Identity Management > Roles. Click the name of the target RAM role to get its ARN from the Basic Information section. Example: acs:ram::124****:role/aliyunodpsdefaultrole.
   Additional foreign server properties	Additional properties for the foreign server. After you specify them, tasks that use this foreign server can access the source system according to the behavior defined by the parameters. Note For information about supported parameters, see the official documentation for future updates. Specific parameters will be gradually released as the product evolves.

5. To create the foreign server, click Confirm.

Step 3: Create an external schema

The following command creates an external schema that references a DLF+OSS foreign server:

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

The following list describes the parameters.

• external_schema_name: The name of the external schema.

• dlfoss_foreign_server_name: The name of the foreign server you created. The project to which the external schema belongs must be in the same region as the foreign server.

• dlf_catalog_id: The ID of the DLF data catalog. For information about how to create a data catalog, see Create a data catalog.

• database_name: The name of the database under the specified data catalog in DLF. For more information, see Databases, tables, and functions.

Step 4: Use SQL to access the data source system

• Command format: List the table names from 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;

• Usage examples:

  1. Query all table names under the external schema named es_dlf.

     USE SCHEMA es_dlf;
     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

  2. Query data from the hellocsv table in the es_dlf schema of the lakehouse47_3 project.

     SELECT * FROM lakehouse47_3.es_dlf.hellocsv;

     Sample result:

     +------------+------------+------------+------------+
     | col1       | col2       | col3       | col4       |
     +------------+------------+------------+------------+
     | 1          | hello      | test       | world      |
     +------------+------------+------------+------------+

  3. Copy data from the federated foreign table hellocsv in the data source to the data warehouse.

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

     Sample result:

     +------------+------------+------------+------------+
     | col1       | col2       | col3       | col4       |
     +------------+------------+------------+------------+
     | 1          | hello      | test       | world      |
     +------------+------------+------------+------------+

Step 1: Create a Hive+HDFS foreign server

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

2. In the left navigation pane, choose Tenant Management > Foreign Server.

3. On the Foreign Server page, click Create Foreign Server.

4. In the Add Foreign Server dialog box, configure the parameters as described in the following table.

   Parameter	Description
   Foreign server type	Select Hive+HDFS.
   Foreign server name	Enter a custom name. The naming conventions are as follows: It must start with a letter and can contain only lowercase letters, underscores (_), and digits. It cannot exceed 128 characters in length.
   Foreign server description	Enter a description as needed.
   Networklink object	The name of the network connection. Select or create a connection from MaxCompute to the Alibaba Cloud E-MapReduce or Hadoop VPC network. For parameter details, see the Create a network connection between MaxCompute and the target VPC network step in VPC access solution (direct connection over a leased line). Note For information about the basic concepts of network connections, see Networklink. The VPC must be in the same region as the MaxCompute foreign server and the projects to which the foreign server is attached.
   Cluster name	The name used to refer to the NameNode in a high-availability Hadoop cluster environment. To get the cluster name for an EMR cluster, follow these steps: Log on to the EMR console and click the target Cluster ID to go to the cluster details page. On the Cluster Services tab, click Configuration for the HDFS service to go to the Configuration page. Switch to the hdfs-site.xml tab and search for dfs.nameservices in the Configuration Item Name column. The value of this configuration item is the cluster name.
   NameNode address	The service addresses and port numbers of the active and standby NameNodes of the target Hadoop cluster. The port number is usually 8020. To get the NameNode addresses for an EMR cluster, follow these steps: Log on to the EMR console and click the target Cluster ID to go to the cluster details page. On the Cluster Services tab, click the HDFS service to go to the Status page. In the Component List area, click the icon in front of NameNode to expand the topology list. Get the Internal IP corresponding to the master-1-1 node. The NameNode Address format is Internal IP:8020.
   HMS service address	The Hive metadata service addresses and port numbers of the active and standby NameNodes of the target Hadoop cluster. The port number is usually 9083. The format is Internal IP:9083.
   Authentication type	Currently, only No authentication is supported.
   vSwitch	MaxCompute accesses data sources through a VPC using the reverse access 2.0 solution by default. This solution requires configuring a vSwitch in a specific zone to establish the metadata access link. The available zones for vSwitches in each region are indicated in the interface prompts. You need to select an existing vSwitch or create a new one that meets the zone requirements in the VPC where the data source you want to access is located. Note Currently, this field is required only in the China (Shanghai) region.
   Additional foreign server properties	Additional properties for the foreign server. After you specify them, tasks that use this foreign server can access the source system according to the behavior defined by the parameters. Note For information about supported parameters, see the official documentation for future updates. Specific parameters will be gradually released as the product evolves.

5. To create the foreign server, click Confirm.

Step 2: Create an external schema

An external schema is an object within a project and can be manipulated using SQL. If the schema syntax switch has not been enabled for your current account at the tenant level, you must add the SET odps.namespace.schema=true; statement before the SQL statements to execute schema-related commands.

The following command creates an external schema that references a Hive+HDFS foreign server:

CREATE EXTERNAL SCHEMA IF NOT EXISTS <external_schema_name>
WITH  <hive_foreign_server_name>
ON '<database_name>' ;

The following table describes the parameters.

• external_schema_name: The name of the external schema.

• hive_foreign_server_name: The name of the foreign server you created.

• database_name: The name of the Hive database.

Step 3: Use SQL to access the data source system

• Command format: List the table names from Hive.

  • 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;

• Usage examples:

  1. Query all table names under the external schema named es_hive3.

     USE SCHEMA es_hive3;
     SHOW TABLES;

     Sample result:

     ALIYUN$xxx@test.aliyunid.com:t1

  2. Query data from the t1 table in the es_hive3 schema of the lakehouse47_3 project.

     SELECT * FROM lakehouse47_3.es_hive3.t1;

     Sample result:

     +------------+
     | id         |
     +------------+
     | 1          |
     +------------+

  3. Copy data from the federated foreign table hellocsv in the data source to the data warehouse.

     -- Copy data from the federated foreign table to the data warehouse
     CREATE TABLE t1_copy AS SELECT * FROM lakehouse47_3.es_hive3.t1;
     
     -- Query the copied table data in the data warehouse
     SELECT * FROM t1_copy;

     Sample result:

     +------------+
     | id         |
     +------------+
     | 1          |
     +------------+

Step 1: Create a Hologres foreign server

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

2. In the left navigation pane, choose Tenant Management > Foreign Server.

3. On the Foreign Server page, click Create Foreign Server.

4. In the Add Foreign Server dialog box, configure the parameters as described in the following table.

   Parameter	Description
   Foreign server type	Select Hologres.
   Foreign server name	Enter a custom name. The naming conventions are as follows: It must start with a letter and can contain only lowercase letters, underscores (_), and digits. It cannot exceed 128 characters in length.
   Foreign server description	Enter a description as needed.
   Connection method	Currently, only classic network access (internal network) is supported.
   Host	The host information of the Hologres instance. You can log on to the Hologres Management Console. In the navigation pane on the left, choose Instance List. Click the corresponding instance ID to get the host from the Network Information section of the Instance Details page. Example: hgpostcn-cn-3m***-cn-shanghai-internal.hologres.aliyuncs.com. Important Only access through a classic network domain name is supported. Accessing Hologres through a VPC network domain name is not supported.
   Port	The port information of the Hologres instance. You can log on to the Hologres Management Console. In the navigation pane on the left, choose Instance List. Click the corresponding instance ID to get the port from the Network Information section of the Instance Details page. The port is generally 80.
   DBNAME	The database name of the Hologres instance.
   Authentication and authorization	Alibaba Cloud RAM Role: Uses RAM role-based authentication and authorization, which supports accessing Hologres across different Alibaba Cloud accounts. Federated foreign tables currently only support the RAMRole method. For RoleARN, an example is acs:ram::uid:role/aliyunodpsholorole. For information about how to configure and authorize the aliyunodpsholorole role, see Create a Hologres external table (STS mode). Task Executor Identity: A user identity recognition mode between MaxCompute and Hologres. It allows the current user to see tables and data they have access to in both systems based on the same account. You can directly execute commands using the CALL EXEC_EXTERNAL_QUERY function without setting additional authentication information. Important The ExecuteWithUserAuth mode does not currently support usage with federated foreign tables.
   RoleARN	The ARN of the RAM role. This role must have permissions to access the Hologres service. This parameter is required when you select RAMRole as the authentication method. You can log on to the RAM console. In the navigation pane on the left, choose Identity Management > Roles. Click the name of the target RAM role to get its ARN from the Basic Information section.
   Add Additional Foreign Server Data	Additional properties for the foreign server. After you specify them, tasks that use this foreign server can access the source system according to the behavior defined by the parameters. Note For information about supported parameters, see the official documentation for future updates. Specific parameters will be gradually released as the product evolves.

5. To create the foreign server, click Confirm.

Step 2: Create an external schema

An external schema is an object within a project and can be manipulated using SQL. If the schema syntax switch has not been enabled for your current account at the tenant level, you must add the SET odps.namespace.schema=true; statement before the SQL statements to execute schema-related commands.

The following command creates an external schema that references a Hologres foreign server:

CREATE EXTERNAL SCHEMA IF NOT EXISTS <external_schema_name>
with  <holo_foreign_server_name>
ON '<holoschema_name>' ;

The following table describes the parameters.

• external_schema_name: The name of the external schema.

• holo_foreign_server_name: The name of the foreign server you created.

• holoschema_name: The name of the Hologres schema to be mapped.

Step 3: Use SQL to access the data source system

• Command format: List the table names in the Hologres schema.

  • 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;

• Usage examples:

  1. Query all table names under the external schema named es_holo_rolearn_nonl:

     SET odps.namespace.schema=true;
     USE SCHEMA es_holo_rolearn_nonl;
     SHOW TABLES;

     Sample result:

     ALIYUN$xxx@test.aliyunid.com:mc_holo_external

  2. Query data from the mc_holo_external table in the es_holo_rolearn_nonl schema of the lakehouse47_3 project.

     SELECT * FROM lakehouse47_3.es_holo_rolearn_nonl.mc_holo_external;

     Sample result:

     +------------+------------+------------+------------+
     | col1       | col2       | col3       | col4       |
     +------------+------------+------------+------------+
     | 1          | hello      | test       | world      |
     +------------+------------+------------+------------+

  3. Copy data from the federated foreign table hellocsv in the data source to the data warehouse.

     -- Copy data from the federated foreign table to the data warehouse
     CREATE TABLE mc_holo_external_copy AS SELECT * FROM lakehouse47_3.es_holo_rolearn_nonl.mc_holo_external;
     
     -- Query the copied table data in the data warehouse
     SELECT * FROM mc_holo_external_copy;

     Sample result:

     +------------+------------+------------+------------+
     | col1       | col2       | col3       | col4       |
     +------------+------------+------------+------------+
     | 1          | hello      | test       | world      |
     +------------+------------+------------+------------+

Submit commands to Hologres using the current user's identity

MaxCompute supports using the CALL command to run the EXEC_EXTERNAL_QUERY function, which submits an executable SQL command to Hologres.

• Command syntax

  CALL EXEC_EXTERNAL_QUERY (
    '<holo_ExecuteWithUserAuth_foreign_server_name>',
    r"###(
      <holo_query>)###");

  • holo_ExecuteWithUserAuth_foreign_server_name: The name of the Hologres foreign server created in ExecuteWithUserAuth mode.

  • r: Specifies the command to execute.

  • holo_query: The specific Hologres SQL command written between the delimiters.

  Note

  This function uses a delimiter syntax similar to C++ raw strings, which combines a delimiter string with "" and (). This syntax avoids conflicts between the delimiters and special characters in holo_query. The delimiter string can be modified, but the delimiters in the "[delimiter]( )[delimiter]" format must match. The delimiter string cannot contain the characters <>. A recommended example is "###(<holo_query>)###".

• Usage examples

  • Example 1: In MaxCompute, submit an SQL statement to perform an INSERT OVERWRITE operation on the Hologres internal table public.current_user_test. The data source is a Hologres query that retrieves the current user's UID: SELECT current_user;

    1. Execute the following SQL in MaxCompute.

       CALL EXEC_EXTERNAL_QUERY (
         'fs_holo_ExecuteWithUserAuth_nonl_y',
         r"###(
           CALL hg_insert_overwrite(
             'public.current_user_test',
             $$SELECT current_user$$
       );)###");

    2. In Hologres, query the data of the internal table public.current_user_test.

       SELECT * FROM current_user_test;

       Result: Returns the UID of the user identity that is the same on both the MaxCompute and Hologres sides, such as 1117xxxxxx519.

  • Example 2: In MaxCompute, submit an SQL statement to perform an INSERT OVERWRITE operation on the Hologres internal table public.hologres_parent_insert1, with the partition sub-table being 2020. The data source is an external table in Hologres based on a MaxCompute table: SELECT * FROM mc_external_table WHERE a='2020';

    1. Execute the following SQL in MaxCompute.

       CALL EXEC_EXTERNAL_QUERY (
         'fs_holo_ExecuteWithUserAuth_nonl_y',
         r"###(
           CALL hg_insert_overwrite(
             'public.hologres_parent_insert1',
             '2020',
             $$SELECT * FROM mc_external_table WHERE a='2020'$$
       );)###");

    2. In Hologres, query the data of the internal table public.hologres_parent_insert1.

       -- Query the table in Hologres, one more row will be added
       SELECT * FROM hologres_parent_insert1;

       Result:

       a			b	c														d
       2020	1	2024-06-19 10:27:46.201636	a