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
To asynchronously execute SQL statements in DLA, you must activate OSS to store execution results of SQL statements.
MNS and DLA must be deployed in the same region.
Procedure
Step 1: Create a RAM user
Use the Alibaba Cloud account to which DLA belongs to log on to the Resource Access Management (RAM) console.
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?.