MaxCompute:Use the data lakehouse solution 2.0

Precautions

• You must enable the schema mode supported by project-level metadata for the MaxCompute project, which is the target project for creating the external schema. For more details, see Schema mode supported by project-level metadata.

• You must enable the schema mode supported by SQL syntax before performing schema operations or querying data from an external schema. For more details, see Schema mode supported by SQL syntax.

• When you execute theSHOW tables IN <external_schema_name>command for accessing data source system:

  • If you are running this command in the DataWorks Data Development IDE and the system fails to resolve the schema, you also need to ensure that the MaxCompute CMD integrated with the DataWorks resource group is version 0.46.8 or higher. If the CMD version is lower than 0.46.8, submit a ticket to contact MaxCompute technical support for an upgrade.

  • If you are running this command in the MaxCompute client (odpscmd), you also need to upgrade it to version 0.46.8 or higher.

  Note

  You can use theSHOW VERSION;command to view the current version.

• The external schema you create supports viewing the list of schemas, viewing schema information, and deleting schemas, with commands and usage methods identical to those for internal schemas. For more details, see Schema-related operations.

Step 1: Authorize MaxCompute to access your cloud resources

Step 2: Create a foreign server of the DLF+OSS type

1. Log on to the MaxCompute console. In the upper-left corner of the console, select a region.

2. In the left-side navigation pane, choose Tenants > Foreign Server.

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

4. In the Add Foreign Server dialog box, configure the parameters as prompted. The following table describes the parameters.

   Parameter	Description
   Foreign Server Type	The type of the foreign server. Select DLF+OSS.
   Foreign Server Name	The name of the foreign server. The name must meet the following requirements: The name contains lowercase letters, underscores (_), and digits and starts with a lowercase letter. The name can be up to 128 characters in length.
   Foreign Server Description	The description of the foreign server.
   Region	The region in which you want to create the foreign server. This parameter is automatically set to the current region.
   DLF Endpoint	The DLF endpoint of the current region.
   OSS Endpoint	The OSS endpoint of the current region.
   RoleARN	The Alibaba Cloud Resource Name (ARN) of a Resource Access Management (RAM) role. The RAM role must have the permissions to access DLF and OSS. You can log on to the RAM console, choose Identities > Roles in the left-side navigation pane, and then click the name of the RAM role to obtain the ARN in the Basic Information section. For example, acs:ram::124****:role/aliyunodpsdefaultrole.
   Foreign Server Supplemental Properties	The properties of the foreign server. After you specify this parameter, the tasks that use the foreign server can access the data source system based on the behavior specified by this parameter. Note More properties will be supported by this parameter as service capabilities evolve. For more information, see the official documentation.

5. Click OK.

Step 3: Create an external schema

Execute the following statement to create an external schema that references a foreign server of the DLF+OSS type:

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

Parameters:

• external_schema_name: the name of the external schema.

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

• dlf_calaog_id: the catalog ID of DLF. For information about how to create a catalog, see the "Create a data catalog" section of the Data catalog topic.

• database_name: the name of the database in the catalog of DLF. For more information, see the "Create a metadatabase" section of the Metadata management topic.

Step 4: Use SQL syntax to access the data source system

• SQL syntax: Show the names of tables in DLF and OSS schemas.

  • Method 1:

    SHOW tables IN <external_schema_name>; --The <external_schema_name> parameter specifies the name of the external schema.

  • Method 2:

    USE SCHEMA <external_schema_name>; --The <external_schema_name> parameter specifies the name of the external schema.
    SHOW tables;

• Examples:

  1. Query the names of all tables in the external schema whose name is 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 the data of the hellocsv table in the es_dlf schema in the lakehouse47_3 project.

     SELECT * FROM lakehouse47_3.es_dlf.hellocsv;

     Sample result:

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

  3. Copy the data of a federated external table whose name is hellocsv from the data source and paste the data to a data warehouse table.

     -- Copy the data of a federated external table and paste the data to a data warehouse table.
     CREATE TABLE hellocsv_copy AS SELECT * FROM lakehouse47_3.es_dlf.hellocsv;
     
     -- Query the copied data in the data warehouse table.
     SELECT * FROM hellocsv_copy;

     Sample result:

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

Step 1: Create a foreign server of the Hive+HDFS type

1. Log on to the MaxCompute console. In the upper-left corner of the console, select a region.

2. In the left-side navigation pane, choose Tenants > Foreign Server.

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

4. In the Add Foreign Server dialog box, configure the parameters as prompted. The following table describes the parameters.

   Parameter	Description
   Foreign Server Type	The type of the foreign server. Select Hive+HDFS.
   Foreign Server Name	The name of the foreign server. The name must meet the following requirements: The name contains lowercase letters, underscores (_), and digits and starts with a lowercase letter. The name can be up to 128 characters in length.
   Foreign Server Description	The description of the foreign server.
   Network Connection Object	The name of the network connection. You can select or establish a connection between MaxCompute and the VPC of an EMR or Hadoop cluster. For more information, see the Establish a network connection between MaxCompute and the destination VPC step in the "Access over a VPC (dedicated connection)" section of the Network connection process topic. Note For information about network connections, see the "Network connection" section of the Terms topic. The VPC must be deployed in the same region as the MaxCompute foreign server and the project to which the foreign server is mounted.
   Cluster Name	The name of the cluster. For a high-availability (HA) Hadoop cluster, the value of this parameter is the same as the name of a NameNode process. When you create a Hadoop cluster, you can obtain the cluster name by using the dfs.nameservices parameter in the hdfs-site.xml file.
   NameNode Address	The IP addresses and port numbers of the active and standby NameNode processes in the Hadoop cluster. In most cases, the port number is 8020. To obtain the addresses, contact the Hadoop cluster administrator.
   HMS Service Address	The HMS IP addresses and port numbers of the active and standby NameNode processes in the Hadoop cluster. In most cases, the port number is 9083. To obtain the addresses, contact the Hadoop cluster administrator.
   Authentication Type	The authentication type. Only No Authentication Method is supported.
   Foreign Server Supplemental Properties	The properties of the foreign server. After you specify this parameter, the tasks that use the foreign server can access the data source system based on the behavior specified by this parameter. Note More properties will be supported by this parameter as service capabilities evolve. For more information, see the official documentation.

5. Click OK.

Step 2: Create an external schema

An external schema is an object within a project and can be created by using SQL syntax. If your account does not enable the tenant-level Schema syntax switch, you need to add the statement SET odps.namespace.schema=true; before your SQL commands to facilitate the execution of Schema-related commands.

Execute the following statement to create an external schema that references a foreign server of the Hive+HDFS type:

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

Parameters:

• external_schema_name: the name of the external schema.

• hive_foreign_server_name: the name of the foreign server.

• database_name: the name of the Hive database.

Step 3: Use SQL syntax to access the data source system

• SQL syntax: Show the names of tables in Hive schemas.

  • Method 1:

    SHOW tables IN <external_schema_name>; --The <external_schema_name> parameter specifies the name of the external schema.

  • Method 2:

    USE SCHEMA <external_schema_name>; --The <external_schema_name> parameter specifies the name of the external schema.
    SHOW tables;

• Examples:

  1. Query the names of all tables in the external schema whose name is es_hive3.

     USE SCHEMA es_hive3;
     SHOW TABLES;

     Sample result:

     ALIYUN$xxx@test.aliyunid.com:t1

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

     SELECT * FROM lakehouse47_3.es_hive3.t1;

     Sample result:

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

  3. Copy the data of a federated external table whose name is hellocsv from the data source and paste the data to a data warehouse table.

     -- Copy the data of a federated external table and paste the data to a data warehouse table.
     CREATE TABLE t1_copy AS SELECT * FROM lakehouse47_3.es_hive3.t1;
     
     -- Query the copied data in the data warehouse table.
     SELECT * FROM t1_copy;

     Sample result:

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

Step 1: Create a foreign server of the Hologres type

1. Log on to the MaxCompute console. In the upper-left corner of the console, select a region.

2. In the left-side navigation pane, choose Tenants > Foreign Server.

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

4. In the Add Foreign Server dialog box, configure the parameters as prompted. The following table describes the parameters.

   Parameter	Description
   Foreign Server Type	The type of the foreign server. Select Hologres.
   Foreign Server Name	The name of the foreign server. The name must meet the following requirements: The name contains lowercase letters, underscores (_), and digits and starts with a lowercase letter. The name can be up to 128 characters in length.
   Foreign Server Description	The description of the foreign server.
   Connection Method	The connection method of the foreign server. Only cloud product interconnection for connecting cloud products is supported.
   Host	The hostname of the Hologres instance. You can log on to the Hologres console, click Instances in the left-side navigation pane, and then click the ID of the Hologres instance to obtain the hostname in the Network Information section of the Instance Details page. For example, hgpostcn-cn-3m***-cn-shanghai-internal.hologres.aliyuncs.com.
   Port	The port number of the Hologres instance. You can log on to the Hologres console, click Instances in the left-side navigation pane, and then click the ID of the Hologres instance to obtain the port number in the Network Information section of the Instance Details page. The port is generally80.
   DBNAME	The name of a database in the Hologres instance.
   Authentication Method	RAMRole: assumes a RAM role to perform authentication. This method allows you to access Hologres across Alibaba Cloud accounts. Federated external tables support only the RAMRole method. For example, RoleARN:acs:ram::uid:role/aliyunodpsholorole. For more information about the role configuration and authentication of thealiyunodpsholorole, see Create a Hologres external table in STS mode. ExecuteWithUserAuth: allows you to use the same Alibaba Cloud account or RAM user to access authorized tables and data in MaxCompute and Hologres. You can also execute the CALL EXEC_EXTERNAL_QUERY statement in MaxCompute and Hologres without the need to configure additional authentication information. Important The ExecuteWithUserAuth method is not supported for federated external tables.
   RoleARN	The ARN of the RAM role. The RAM role must have the permissions to access Hologres. This parameter is required when you set the Authentication Method parameter to RAMRole. You can log on to the RAM console, choose Identities > Roles in the left-side navigation pane, and then click the name of the RAM role to obtain the ARN in the Basic Information section.
   Foreign Server Supplemental Properties	The properties of the foreign server. After you specify this parameter, the tasks that use the foreign server can access the data source system based on the behavior specified by this parameter. Note More properties will be supported by this parameter as service capabilities evolve. For more information, see the official documentation.

5. Click OK.

Step 2: Create an external schema

An external schema is an object within a project and can be created by using SQL syntax. If your account does not enable the tenant-level Schema syntax switch, you need to add the statement SET odps.namespace.schema=true; before your SQL commands to facilitate the execution of Schema-related commands.

Execute the following statement to create an external schema that references a foreign server of the Hologres type:

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

Parameters:

• external_schema_name: the name of the external schema.

• holo_foreign_server_name: the name of the foreign server.

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

Step 3: Use SQL syntax to access the data source system

• SQL syntax: Show the names of tables in Hologres schemas.

  • Method 1:

    SHOW tables IN <external_schema_name>; --The <external_schema_name> parameter specifies the name of the external schema.

  • Method 2:

    USE SCHEMA <external_schema_name>; --The <external_schema_name> parameter specifies the name of the external schema.
    SHOW tables;

• Examples:

  1. Query the names of all tables in the external schema whose name is 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 the data of the mc_holo_external table in the es_holo_rolearn_nonl schema in 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. Insert data into the table.

     INSERT INTO lakehouse47_3.es_holo_rolearn_nonl.mc_holo_external VALUES(2,'hello','test','holo');
     
     SELECT * FROM lakehouse47_3.es_holo_rolearn_nonl.mc_holo_external;

     Sample result:

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

  4. Copy the data of a federated external table whose name is hellocsv from the data source and paste the data to a data warehouse table.

     -- Copy the data of a federated external table and paste the data to a data warehouse table.
     CREATE TABLE mc_holo_external_copy AS SELECT * FROM lakehouse47_3.es_holo_rolearn_nonl.mc_holo_external;
     
     -- Query the copied data in the data warehouse table.
     SELECT * FROM mc_holo_external_copy;

     Sample result:

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

Use the current user identity to submit an executable statement

MaxCompute allows you to use a CALL statement that contains the EXEC_EXTERNAL_QUERY() function to submit an executable statement to Hologres.

• Syntax

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

  • holo_ExecuteWithUserAuth_foreign_server_name: the name of the foreign server that you created in ExecuteWithUserAuth mode.

  • r: the executable statement.

  • holo_query: the Hologres statement that is entered within the boundary.

  Note

  Similar to the raw string method in C++, double quotation marks (""), parentheses (), and delimiters form a boundary to prevent conflicts between characters before and after the boundary and special characters in the holo_query parameter. You can configure custom delimiters and make sure that delimiters are used in pairs in the "[delimiter]( )[delimiter]" format. The boundary cannot contain angle brackets (<>). We recommend that you use "###(<holo_query>)###".

• Examples

  • Example 1: Submit an INSERT OVERWRITE statement on the Hologres internal table whose name is public.current_user_test in MaxCompute. The data to be inserted comes from the SELECT current_user; statement that is executed to query the current UID in Hologres.

    1. Execute the following statement in MaxCompute:

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

    2. Query the data of the public.current_user_test table in Hologres.

       SELECT * FROM current_user_test;

       The same UID that is used to execute the preceding statements is returned. Example: 1117xxxxxx519.

  • Example 2: Submit an INSERT OVERWRITE statement on partition 2020 of the Hologres internal table whose name is public.hologres_parent_insert1 in MaxCompute. The data to be inserted comes from the SELECT * FROM mc_external_table WHERE a='2020'; statement that is executed on a MaxCompute external table in Hologres.

    1. Execute the following statement 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. Query the data of the public.hologres_parent_insert1 table in Hologres.

       -- A row of data is inserted into the Hologres internal table.
       SELECT * FROM hologres_parent_insert1;

       Sample result:

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