MQ message receipt notification - Serverless SQL| Alibaba Cloud Documentation Center

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

Prerequisites

OSS is activated.
Note
Asynchronous query cannot be implemented if OSS is not activated.

Create a directory
OSS data sources
Create istances, topics and groups
Note
MQ and DLA must be in the same region.

Procedures

Create a new RAM role.
1. Use your DLA account to log on to the Resource Access Management (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 AliyunOpenAnalyticsAccessingMQRole.
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.
```
{ "Statement": [ { "Action": "sts:AssumeRole", "Effect": "Allow", "Principal": { "Service": [ "openanalytics.aliyuncs.com" ] } } ], "Version": "1" }
```
Modify the permissions of the role.
In the RAM role list, locate the row that contains the role you created, and click Add Permissions in the Actions column. In the Add Permissions dialog box, select the policy AliyunMQFullAccess from the policy list and add it to the role.

Asynchronously execute an SQL statement and use MQ for message acknowledgment.

Syntax

/* + run_async=true, mq-notify-by=ons, mq-topic =${ topic of your mq}, mq-producer-id =${ your group Id}, mq-endpoint =${ one of your endpoint, which is the same as the region to which DLA belongs} * / select * from xxxx ....;

Examples

/*+ run_async=true, mq-notify-by=ons, mq-topic=dla_hangzhou_topic, mq-producer-id=GID_dla_hangzhou, mq-endpoint=http://MQ_INST_*****_BagJ6yLU.cn-hangzhou.mq-internal.aliyuncs.com:8080 */ select * from oss_schema.oss_json;

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

mysql> show query_task where id = 'q201903271454hze921092f0064543'\G *************************** 1. row *************************** id: q201903271454hze921092f0064543 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-topic=dla_hangzhou_topic, mq-producer-id=GID_dla_hangzhou, mq-endpoint=http://MQ_INST_*****_BagJ6yLU.cn-hangzhou.mqinternal.aliyuncs.com:8080 */ select * from oss_schema.oss_json creator_id: ${your dla account} create_time: 2019-03-27 14:55:09 update_time: 2019-03-27 14:55:09 connection_id: 49409305627697 message: row_count: 4 elapse_time: 490 scanned_row_count: 4 scanned_data_bytes: 230 result_file_oss_file: oss://aliyun-oa-query-results-***-oss-cn-hangzhou/DLA_Result/2019/03/27/q201903271454hze921092f0064543/result.csv cancellable_task: 0 mq_product: NULL mq_model: queue mq_topic: dla_hangzhou_topic mq_queue: NULL mq_producer_id: GID_dla_hangzhou mq_endpoint: http://MQ_INST_*****_BagJ6yLU.cn-hangzhou.mqinternal.aliyuncs.com:8080 mq_status: SUCCESS mq_error_msg: NULL mq_message_id: AC13140925E468C4039C6FFCFD50001B mq_total_time: 307

The value AC13140925E468C4039C6FFCFD50001B of mq_message_id is the ID that is returned by MQ. You can view the ID in the MQ console.

After MQ receives the message from DLA, you can log on to the MQ console to check the result of asynchronous SQL statement execution in DLA. For MQ, see What is Message Queue for Apache RocketMQ?.