This topic describes how to use Data Lake Analytics (DLA) to quickly analyze Object Storage Service (OSS) data across Alibaba Cloud accounts. In this topic, you can use Alibaba Cloud account A to query and analyze OSS data of Alibaba Cloud account B after you perform the following operations. Prerequisites:

  1. OSS is activated by using Alibaba Cloud Account B and the file that contains test data is uploaded. For more information, see Activate OSS.

    In this topic, the customer.tbl file that contains test data is uploaded to the TPC-H/customer directory in the OSS bucket.

  2. DLA is activated by using Alibaba Cloud account A. For more information, see Activate DLA.

Procedure

Step 1: Create a RAM user

  1. Use Alibaba Cloud account B to log on to the RAM console.

  2. In the left-side navigation pane, click RAM Roles.

  3. On the RAM Roles page, click Create RAM Role.

  4. In the Create RAM Role pane, select Alibaba Cloud Service for the Trusted entity type parameter, and then click Next.

  5. Specify RAM Role Name and Note as needed, and select IoT from the Select Trusted Service drop-down list.

    If you select IoT from the Select Trusted Service drop-down list, you must modify the trust policy in Step 2.

  6. Click OK.

Step 2: Modify the trust policy of the RAM user

  1. Use Alibaba Cloud account B to log on to the RAM console.

  2. In the left-side navigation pane, click RAM Roles.

  3. Find the RAM user that you created, and click its name in the RAM Role Name column.

  4. On the page that appears, click the Trust Policy Management tab and click Edit Trust Policy. In the script editor, replace "iot.aliyuncs.com" with <ID of Alibaba account A>@openanalytics.aliyuncs.com, for example, 111111111@openanalytics.aliyuncs.com. In this case, 111111111 indicates the ID of Alibaba account A.

  5. Click OK. On the page that appears, copy the ARN information in Basic Information. The ARN information is used to create a schema in Step 4.

Step 3: Grant OSS permissions to the RAM user

  1. Use Alibaba Cloud account B to log on to the RAM console.

  2. In the left-side navigation pane, click RAM Roles.

  3. Find the RAM user that you created, and click its name in the RAM Role Name column.

  4. On the page that appears, click the Permissions tab and click Add Permissions. In the Add Permissions pane, click the System policy tab in Select Policy. If you only need to access OSS data in read-only mode, add only the AliyunOSSReadOnlyAccess policy. If you want to execute data update statements such as INSERT OVERWRITE, add the AliyunOSSFullAccess policy and click OK.

After you complete this step, Alibaba Cloud account A is authorized to create a schema for OSS owned by Alibaba Cloud account B and create an external table in this schema.

Step 4: Create an OSS schema

  1. Use Alibaba Cloud account A to log on to the DLA console.

  2. In the left-side navigation pane, unfold Serverless SQL and click SQL access point. On the SQL access point page, find your VPC and click Log on in DMS in the Actions column. In the Login instance dialog box, specify Database account and Database password, and click Login. Then, use the following statement to create the tpch_schema1 schema.

    You can also use a MySQL CLI tool, a client, or code to connect to DLA and create the tpch_schema1 schema. For more information, see Connect to DLA.

    ​CREATE SCHEMA tpch_schema1 with DBPROPERTIES(
    catalog='oss',
    location = 'oss://your-oss-bucket-name/TPC-H/',
    cross_account_accessing_arn= 'acs:ram::14069264740****:role/for-dla-cross-account'  
    );​
  3. Use the following statement to create an external table for the CUSTOMER table in tpch_schema1:

    ​ CREATE EXTERNAL TABLE customer (
         `c_custkey` int,
         `c_name` string,
         `c_address` string,
         `c_nationkey` int,
         `c_phone` string,
         `c_acctbal` double,
         `c_mktsegment` string,
         `c_comment` string
     )
     ROW FORMAT DELIMITED
         FIELDS TERMINATED BY '|'
     STORED AS `TEXTFILE`
     LOCATION 'oss://your-oss-bucket-name/TPC-H/customer'​

    After the external table is created, you can query the data from this table by using Alibaba Cloud account A.