Hologres external projects

Supported data types

• DECIMAL type limitations:

  For a Hologres external table created in MaxCompute, the DECIMAL data type is fixed to decimal(38,18) and cannot be modified. If the source table has fewer decimal places, you can define the column as the STRING type in the MaxCompute external table and use the CAST function to explicitly cast the data when it is used.

• Complex type limitations:

  Hologres external tables created in MaxCompute do not support complex data types such as Map and Struct. The Array type is supported with limitations, as detailed in the table that follows.

• Other incompatible types:

  Hologres data types such as MONEY are not supported because they lack a corresponding type in MaxCompute.

For more information, see Hologres and MaxCompute data type mapping.

Authorization

• Method 1: If you use an Alibaba Cloud RAM role for authentication and authorization, see Grant permissions by using a standard RAM role for Hologres.

• Method 2: If you use the task executor identity for authentication and authorization, see Grant permissions by using a service-linked role for Hologres (identity passthrough).

Create a Hologres external data source

1. Activate Hologres service, and MaxCompute and Hologres must be deployed in the same region.

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

3. In the left-side navigation pane, choose Manage Configurations > External Data Source .

4. On the External Data Source page, click Create External Data Source.

5. 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 Hologres.
   External Data Source Name	Yes	The external data source name. It must meet the following requirements: It must start with a letter and can contain only lowercase letters, digits, and underscores (_). It must be 128 characters or fewer. Example: paimon_dlf.
   Description	No	The description of the external data source.
   Connection Method	Yes	The default value is classic network access (internal network).
   instance ID	Yes	Select the Hologres instance in the current region to connect to.
   Host	Yes	The system automatically populates this parameter.
   Port	Yes	The system automatically populates this parameter.
   DBNAME	Yes	The name of the Hologres database to connect to.
   Authentication and authorization	Yes	RAM Role: Select this method to use the external data source with Hologres external schemas. Task executor: External data sources for Hologres external projects must use the Task executor authentication mode.
   role ARN	Yes	The Alibaba Cloud Resource Name (ARN) of the RAM role. To create a RAM role and grant permissions, see Grant permissions by using a standard RAM role for Hologres. 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.
   Service-linked Role	Yes	If you select Task executor, the system automatically sets the Service-linked Role to acs:ram::124****:role/aliyunserviceroleformaxcomputeidentitymgmt.
   Foreign Server Supplemental Properties	No	Supplemental properties that define how tasks access the external data source. Note For supported parameters, see the official documentation. More parameters may be added as capabilities evolve.

6. Click OK to create the external data source.

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

Create a Hologres external project

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 > Projects.

3. On the External Project tab, click Create Project.

4. In the Create Project dialog box, configure the settings and click OK.

   Parameter	Required	Description
   Project Type	Yes	Defaults to external project.
   Region	Yes	The current region is selected by default and cannot be changed.
   Project Name (Globally Unique)	Yes	The name must be 3 to 28 characters long, start with a letter, and contain only letters, digits, and underscores (_).
   MaxCompute Foreign Server Type	No	Select Hologres.
   MaxCompute Foreign Server	No	Use Existing: Displays a list of existing external data sources. Create Foreign Server: Lets you create a new external data source.
   MaxCompute Foreign Server Name	Yes	If you select Select Existing, choose an external data source from the drop-down list. If you select Create New External Data Source, the name of the new external data source is automatically used.
   Billing Method	Yes	Valid values are Subscription and Pay-as-you-go.
   Default Quota	Yes	Select an existing quota.
   Description	No	Enter a description for the project.

Access a data source using SQL

1. Choose a connection tool to log on to the external project.

   The following example uses an SQL node for data development in a DataWorks workspace (new version).

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

   2. In the navigation pane on the left, choose Workspace.

   3. On the Workspaces page, click Create Workspace or click the name of an existing workspace.

   4. On the Workspace Details page, click Computing Resource in the navigation pane on the left.

   5. On the Computing Resource page, click Associate Computing Resources, and select MaxCompute.

   6. Configure the Basic Information for Associate MaxCompute Computing Resource.

      For the MaxCompute project, select the external project.

2. List the schemas in the external project.

   -- Enable session-level schema syntax.
   SET odps.namespace.schema=true;
   SHOW schemas;
   
   -- Output:
   ID = 20250922****g27aqqcnyn2
   hg_internal
   hg_recyclebin
   public
   
   
   OK

3. List the tables in a schema.

   -- <schema_name> is the name of a schema returned by the `SHOW schemas` command.
   USE schema <schema_name>;
   SHOW tables;

4. Create a schema in the external project.

   -- In this example, <schema_name> is schema_demo_test.
   CREATE schema <schema_name>;

5. Use the new schema.

   USE schema <schema_name>;

6. Create a table in the schema and insert data into it.

   • Command format:

     -- Create a table.
     CREATE TABLE [IF NOT EXISTS] <table_name> 
     (
       <col_name> <data_type>,
       ...
     )
     [COMMENT <table_comment>]
     [PARTITIONED BY (<col_name> <data_type>, ...)] 
     ;
     
     -- Insert data. The INSERT OVERWRITE operation is not supported.
     INSERT INTO <table_name> [PARTITION (<pt_spec>)] [(<col_name> [,<col_name> ...)]]
     <select_statement>
     FROM <from_statement>

   • Example:

     CREATE TABLE schema_table(id int,name string);
     
     INSERT INTO schema_table VALUES (101,'Zhang San'),(102,'Li Si');
     
     -- Query the schema_table table.
     SELECT * FROM schema_table;
     
     -- Output:
     +------------+------------+
     | id         | name       | 
     +------------+------------+
     | 101        | Zhang San  | 
     | 102        | Li Si      | 
     +------------+------------+

7. Use the existing public schema.

   use schema public;
   show tables;
   
   -- Output:
   ID = 20250922065846301g50iovzbx8g
   1221220769279014	test1
   
   
   OK
   
   -- Read data from the table.
   SELECT * FROM test1;
   
   -- Output:
   +------------+------------+
   | id         | name       | 
   +------------+------------+
   | 101        | Zhang San  | 
   | 102        | Li Si      | 
   +------------+------------+
   
   -- Write data to the table and then query it to verify the write operation.
   INSERT INTO test1 VALUES (103,'Test');
   
   SELECT * FROM test1;
   
   -- Output:
   +------------+------------+
   | id         | name       | 
   +------------+------------+
   | 102        | Li Si      | 
   | 103        | Test       | 
   | 101        | Zhang San  | 
   +------------+------------+

Hologres and MaxCompute data type mapping

FAQ