All Products
Search
Document Center

Use MNS for message acknowledgment

Last Updated: Apr 14, 2021

This topic describes how to use Message Service (MNS) for message acknowledgment in Data Lake Analytics (DLA) when you query data from Object Storage Service (OSS) in asynchronous mode.

Prerequisites

  1. Activate OSS

    To asynchronously execute SQL statements in DLA, you must activate OSS to store execution results of SQL statements.

  2. Create buckets

  3. Activate Message Service

    MNS and DLA must be deployed in the same region.

  4. Create a queue

  5. OSS data sources

Procedure

Step 1: Create a RAM user

  1. Use the Alibaba Cloud account to which DLA belongs to log on to the Resource Access Management (RAM) console.

  2. In the left-side navigation pane, click RAM Roles. On the RAM Roles page, click Create RAM Role. In the Create RAM Role panel, set the following parameters:

    • Trusted entity type: Select Alibaba Cloud Account.

    • Select Trusted Alibaba Cloud Account: Select Current Alibaba Cloud Account.

    • RAM Role Name: Enter AliyunOpenAnalyticsAccessingMNSRole.

Step 2: Modify the policy for the role

On the RAM Roles page, click the role that you created. On the page that appears, click the Trust Policy Management tab. Then, click Edit Trust Policy. In the Edit Trust Policy panel, use the following policy to replace the original policy.

{
    "Statement": [
        {
            "Action": "sts:AssumeRole",
            "Effect": "Allow",
            "Principal": {
                "Service": [
                    "openanalytics.aliyuncs.com"
                ]
            }
        }
    ],
    "Version": "1"
}

Step 3: Modify the permissions of the role

On the RAM Roles page, find the role that you created and click Add Permissions in the Actions column. In the Add Permissions panel, click the System Policy tab in Select Policy, add AliyunMNSFullAccess, and then click OK.

Step 4: Asynchronously execute an SQL statement and use MNS for message acknowledgment

Syntax

/*+ run_async=true, mq-notify-by=mns, mq-queue=${MNS queue} */ 
select * from xxxx .... ;

Example

/*+ run-async=true,mq-notify-by=mns,mq-queue=dla */ select * from oss_schema.oss_json;

Execute the preceding statement to obtain ASYNC_TASK_ID, which is q201903251653hze921092f0012820 in this example. Execute show query_task to obtain mq_message_id.

mysql> show query_task where id = 'q201903251653hze921092f0012820'\G
*************************** 1. row ***************************
                  id: q201903251653hze921092f0012820
        mpp_query_id: 20190322_120525_12951_rdxtt
              status: SUCCESS
           task_name: SELECT
        table_schema: oss_schema
             command: /*+ run-async=true,mq-notify-by=mns,mq-queue=dla */ select * from oss_schema.oss_json
          creator_id: ${Alibaba Cloud account that is used to access DLA}
         create_time: 2019-03-25 16:04:55
         update_time: 2019-03-25 16:04:55
       connection_id: 49409305027991
             message: 
           row_count: 4
         elapse_time: 351
   scanned_row_count: 4
  scanned_data_bytes: 230
result_file_oss_file: oss://aliyun-oa-query-results-****-oss-cn-hangzhou/DLA_Result/2019/03/25/q201903251653hze921092f0012820/result.csv
    cancellable_task: 0
          mq_product: mns
            mq_model: queue
            mq_topic: NULL
            mq_queue: dla
      mq_producer_id: NULL
         mq_endpoint: http://${UID}.mns.cn-hangzhou-internal.aliyuncs.com
           mq_status: SUCCESS
        mq_error_msg: NULL
       mq_message_id: 76218C7EAF7361357FA57F38FB3BD66F
       mq_total_time: 307
1 row in set (0.02 sec)

The value of mq_message_id is 76218C7EAF7361357FA57F38FB3BD66F that is returned by MNS. You can use mq_message_id to receive messages from DLA in the MNS console.

After MNS receives messages from DLA, you can use these messages to obtain the details of tasks that are used to asynchronously execute SQL statements for DLA in real time. For more information about MNS, see What is MNS?.