This topic takes asynchronous query of data in OSS as an example to describe how to use MQ for message acknowledgment in DLA.
Prerequisites
You have activated OSS.
Asynchronous query cannot be implemented if OSS is not activated.
You have created an OSS Schema.
You have created OSS tables.
You have created instances, topics, and groups in the MQ console. For more information, see Quick start for primary accounts and Quick start for sub-accounts in the Aliware RocketMQ documentation.
MQ and DLA must be in the same region.
Procedure
Step 1: Create an RAM role
Use your DLA account to log on to the RAM console.
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.
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.
{
"Statement": [
{
"Action": "sts:AssumeRole",
"Effect": "Allow",
"Principal": {
"Service": [
"openanalytics.aliyuncs.com"
]
}
}
],
"Version": "1"
}
Step 3: 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.
Step 4: Asynchronously execute an SQL statement and use MQ for message acknowledgment
Syntax
/*+ run_async=true, mq-notify-by=ons, mq-topic=${your MQ topic},
mq-producer-id=${your group Id}, mq-endpoint=${your endpoint in the same region as DLA} */
select * from xxxx .... ;
Example
/*+ 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.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.