All Products
Search

This Product

    Data Lake Analytics - Deprecated:Background

    Document Center

    Data Lake Analytics - Deprecated:Background

    Last Updated:May 09, 2019

    In Online Analytical Processing (OLAP) scenarios, the time-consuming execution of complex SQL statements may cause congestion in program running, which affects business operation. To prevent this problem, DLA allows you to insert hints into SQL statements for asynchronous SQL statement execution. The query result of an SQL statement is exported to your OSS directory by default, and the SQL statement ID is returned to you for later use.

    For example, asynchronously execute the following SQL statement in DLA:

    1. mysql> /*+ run-async=true */select * from test_table1;
    2. +--------------------------------+
    3. | ASYNC_TASK_ID                  |
    4. +--------------------------------+
    5. | q201811021109sh8d1a0b750000182 |
    6. +--------------------------------+
    7. 1 row in set (0.04 sec)

    To check the result of asynchronous SQL statement execution, execute the following SQL statement:

    1. ysql> show query_task where id = 'q201811021109sh8d1a0b750000182'\G
    2. *************************** 1. row ***************************
    3.                   id: q201811021109sh8d1a0b750000182
    4.         mpp_query_id: 20181102_030939_149_svnhw
    5.               status: SUCCESS
    6.            task_name: SELECT
    7.         table_schema: sh_tpch
    8.              command: /*+ run-async=true */select * from test_table1
    9.           creator_id: ${your DLA account}
    10.          create_time: 2018-11-02 11:09:40.0
    11.          update_time: 2018-11-02 11:09:40.0
    12.        connection_id: 693929276088405
    13.              message: 
    14.            row_count: 2
    15.          elapse_time: 692
    16.   scanned_data_bytes: 147
    17. result_file_oss_file: oss://aliyun-oa-query-results-${your UID}-oss-cn-shanghai/DLA_Result/2018/11/02/q201811021109sh8d1a0b750000182/result.csv
    18.     cancellable_task: 0
    19.           mq_product: NULL
    20.             mq_topic: NULL
    21.       mq_producer_id: NULL
    22.             mq_model: NULL
    23.            mq_status: NULL
    24.         mq_error_msg: NULL
    25.        mq_message_id: NULL
    26.        mq_total_time: NULL
    27. 1 row in set (0.02 sec)

    Parameter description:

    • status: The value SUCCESS indicates that the SQL statement is executed successfully.

      In addition, RUNNING indicates that the statement is being executed, FAILURE indicates that the statement failed to be executed, and CANCELLED indicates that you have canceled the execution.

    • result_file_oss_file: The value oss://aliyun-oa-query-results-... indicates the file that stores the query result of the SQL statement in your OSS bucket.

      Note: Before you asynchronously execute SQL statements in DLA, activate OSS to store asynchronous execution results.

    • elapse_time: The value 692 indicates the time that the SQL job takes.

    • scanned_data_bytes: The value 147 indicates the amount of data that is scanned when the SQL statement is executed (basis for billing).

    The preceding method implements asynchronous SQL statement execution and obtains details about the execution. However, the program needs to run show query_task in polling mode to determine whether the SQL statement state has changed from RUNNING to SUCCESS. This polling process greatly affects the performance of the program. Therefore, timely notification is required.

    DLA provides message acknowledgments to help you quickly obtain the asynchronous job execution status. DLA can write messages to Alibaba Cloud Message Queue (MQ) and Message Service (MNS, originally known as MQS).