All Products
Search
Document Center

Use MNS for message acknowledgment

Last Updated: May 23, 2019

This topic takes asynchronous query of data in OSS as an example to describe how to use MNS for message acknowledgment in DLA.

Prerequisites

  1. You have activated OSS.

    Asynchronous query cannot be implemented if OSS is not activated.

  2. You have activated MNS.

    MNS and DLA must be in the same region.

  3. You have created a queue.

  4. You have created an OSS Schema.

  5. You have created OSS tables.

Procedure

Step 1: Create an RAM role

  1. Use your DLA account to log on to the RAM console.

  2. In the left-side navigation pane, choose RAM Roles. On the page that appears, click Create RAM Role. In the Create RAM Role dialog box, configure the parameters as follows:

    • Select type of trusted entity: Select Alibaba Cloud Account.

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

    • RAM Role Name: Enter AliyunOpenAnalyticsAccessingMNSRole.

      1

Step 2: Modify the authorization policy for the role

In the RAM role list, click the role that you created. On the page that appears, click the Trust Policy Management tab. On the tab page that appears, click Edit Trust Policy, and then use the following policy to replace the original one.

  1. {
  2. "Statement": [
  3. {
  4. "Action": "sts:AssumeRole",
  5. "Effect": "Allow",
  6. "Principal": {
  7. "Service": [
  8. "openanalytics.aliyuncs.com"
  9. ]
  10. }
  11. }
  12. ],
  13. "Version": "1"
  14. }

2

Step 3: Modify the permissions of the role

In the RAM role list, locate the row that contains the role you just created, and click Add Permissions in the Actions column. In the Add Permissions dialog box, select the policy AliyunMNSFullAccess from the policy list and add it to the role.

3

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

Syntax

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

Example

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

The preceding SQL statement returns an ASYNC_TASK_ID, which is q201903251653hze921092f0012820 in this example.

  1. mysql> show query_task where id = 'q201903251653hze921092f0012820'\G
  2. *************************** 1. row ***************************
  3. id: q201903251653hze921092f0012820
  4. mpp_query_id: 20190322_120525_12951_rdxtt
  5. status: SUCCESS
  6. task_name: SELECT
  7. table_schema: oss_schema
  8. command: /*+ run-async=true,mq-notify-by=mns,mq-queue=dla */ select * from oss_schema.oss_json
  9. creator_id: ${your DLA account}
  10. create_time: 2019-03-25 16:04:55
  11. update_time: 2019-03-25 16:04:55
  12. connection_id: 49409305027991
  13. message:
  14. row_count: 4
  15. elapse_time: 351
  16. scanned_row_count: 4
  17. scanned_data_bytes: 230
  18. result_file_oss_file: oss://aliyun-oa-query-results-****-oss-cn-hangzhou/DLA_Result/2019/03/25/q201903251653hze921092f0012820/result.csv
  19. cancellable_task: 0
  20. mq_product: mns
  21. mq_model: queue
  22. mq_topic: NULL
  23. mq_queue: dla
  24. mq_producer_id: NULL
  25. mq_endpoint: http://${your UID}.mns.cn-hangzhou-internal.aliyuncs.com
  26. mq_status: SUCCESS
  27. mq_error_msg: NULL
  28. mq_message_id: 76218C7EAF7361357FA57F38FB3BD66F
  29. mq_total_time: 307
  30. 1 row in set (0.02 sec)

The value 76218C7EAF7361357FA57F38FB3BD66F of mq_message_id is the ID that is returned by MNS. You can view this ID in the MNS console.

4

After MNS receives the message from DLA, you can log on to the MNS console to check the result of asynchronous SQL statement execution in DLA.