Account A creates a Hologres external table in MaxCompute to access a Hologres instance owned by Account B.
Prerequisites
You have activated Hologres and used CREATE TABLE to create a table in a Hologres instance.
You have created a MaxCompute project.
Different cloud accounts own the MaxCompute project and the Hologres instance.
Step 1: Account B creates a custom role
Create a custom role.
Log on to the RAM access control console using Account B.
In the left navigation bar, select .
On the Roles page, click Create Role.
On the Create Role page, set Principal Type to Alibaba Cloud Account.
Click OK. In the Create Role dialog box, enter a custom Role Name (required) and add a Tag (optional).
Configure the trust policy for the custom role.
On the Roles page, click the target Role Name to go to the role details page.
On the Trust Policy tab, click Edit Trust Policy, and on the Edit Trust Policy page, select the JSON Editor tab.
This policy allows the MaxCompute service of Account A to assume this role. You must use the UID of Account A.
{ "Statement": [ { "Action": "sts:AssumeRole", "Effect": "Allow", "Principal": { "Service": [ "<UID of Account A>@odps.aliyuncs.com" ] } } ], "Version": "1" }
Add a permissions policy for the custom role only if it requires permissions to perform operations in the Hologres management console.
Step 2: Grant permissions to the custom role
Add the custom role as a user.
Log on to the Hologres management console using Account B and select a region in the upper-left corner.
In the navigation pane on the left, click Instances.
On the instance details page, click Connect to Instance.
Click the Security Center tab.
In the navigation pane on the left, click Users.
On the Users page, click Add User in the upper-right corner. In the Add User dialog box, add a RAM role to the Hologres instance.
Grant the custom role permissions on databases and tables.
On the Instances page, click the name of the target instance.
On the instance details page, click Connect to Instance.
Click the Security Center tab.
In the navigation pane on the left, click DB authorization.
On the DB authorization page, find the target database and click Authorize User in the Actions column.
In the upper-right corner, click Create Authorization. In the Create Authorization dialog box, configure the User and User Group.
Step 3: Create a Hologres external table
Account A creates the Hologres external table by assuming the role from Account B.
To create the Hologres external table, log on to an odpscmd client or a DataWorks SQL node with Account A. You must use RAM role authorization for this cross-account operation. For the complete procedure, see Create a Hologres external table.
CREATE EXTERNAL TABLE ext_holo_holo_test_tb ( id bigint, name string ) STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler' WITH serdeproperties ( 'odps.properties.rolearn'='acs:ram::<UID of Account B>:role/<role_name>') LOCATION 'jdbc:postgresql://<endpoint>:80/<database_name>?ApplicationName=MaxCompute&table=holo_test_tb/' TBLPROPERTIES ( 'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver', 'odps.federation.jdbc.target.db.type'='holo' );Read data from the Hologres external table.
SET odps.table.api.enable.holo.table=true; SELECT * FROM ext_holo_holo_test_tb limit 10; -- Returned result: +------------+------+ | id | name | +------------+------+ | 9 | hanmeimei | | 4 | tom | | 7 | haward | | 2 | mary | | 5 | lulu | | 8 | lilei | | 10 | lily | | 1 | kate | | 6 | mark | | 11 | lucy | +------------+------+