All Products
Search
Document Center

Use MQ for message acknowledgment

Last Updated: May 13, 2019

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

Prerequisites

  1. You have activated OSS.

    Asynchronous query cannot be implemented if OSS is not activated.

  2. You have created an OSS Schema.

  3. You have created OSS tables.

  4. 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

  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 AliyunOpenAnalyticsAccessingMQRole.

      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 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.

3

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

Syntax

  1. /*+ run_async=true, mq-notify-by=ons, mq-topic=${your MQ topic},
  2. mq-producer-id=${your group Id}, mq-endpoint=${your endpoint in the same region as DLA} */
  3. select * from xxxx .... ;

Example

  1. /*+ run_async=true,
  2. mq-notify-by=mns,
  3. mq-topic=dla_hangzhou_topic,
  4. mq-producer-id=GID_dla_hangzhou,
  5. 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.

  1. mysql> show query_task where id = 'q201903271454hze921092f0064543'\G
  2. *************************** 1. row ***************************
  3. id: q201903271454hze921092f0064543
  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-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
  9. creator_id: ${your DLA account}
  10. create_time: 2019-03-27 14:55:09
  11. update_time: 2019-03-27 14:55:09
  12. connection_id: 49409305627697
  13. message:
  14. row_count: 4
  15. elapse_time: 490
  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/27/q201903271454hze921092f0064543/result.csv
  19. cancellable_task: 0
  20. mq_product: NULL
  21. mq_model: queue
  22. mq_topic: dla_hangzhou_topic
  23. mq_queue: NULL
  24. mq_producer_id: GID_dla_hangzhou
  25. mq_endpoint: http://MQ_INST_*****_BagJ6yLU.cn-hangzhou.mqinternal.aliyuncs.com:8080
  26. mq_status: SUCCESS
  27. mq_error_msg: NULL
  28. mq_message_id: AC13140925E468C4039C6FFCFD50001B
  29. 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.