All Products
Search

This Product

    Use MQ for message acknowledgment

    Document Center
    Product Details

    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.

    Free Trial Free Trial