All Products
Document Center

Data Lake Analytics:Background

Last Updated:May 19, 2022

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