AnalyticDB for MySQL allows you to asynchronously submit an import task.

Scenarios

When you execute the INSERT INTO or INSERT OVERWRITE INTO SELECT statement to import data, the statement is synchronously executed by default. If hundreds of gigabytes of data is imported, the connection between the client and the AnalyticDB for MySQL server may be closed, and the data import task fails. When a large amount of data is involved, we recommend that you asynchronously submit import tasks.

Asynchronously submit a task

  • Syntax
    SUBMIT JOB INSERT OVERWRITE INTO <target_table> 
SELECT <col_name> FROM <source_table>;

    After you execute the preceding statement, a job ID is returned.

  • Example
    SUBMIT JOB INSERT OVERWRITE INTO test SELECT * FROM test_external_table;
    +---------------------------------------+
| job_id                                |
+---------------------------------------+
| 2017112122202917203100908203303000715 |
  • Priority-based scheduling

    AnalyticDB for MySQL V3.1.3.6 and later support priority-based scheduling. You can use a hint to specify the priority of an asynchronous task. The default priority value is 1. A greater value specifies a higher priority for scheduling the task. 

    /*+async_job_priority=10*/ SUBMIT JOB INSERT OVERWRITE INTO test SELECT * FROM test_external_table;

Query the state of an asynchronous task

  • Syntax
    SHOW job STATUS WHERE job='job_id';
  • Example
    SHOW job STATUS WHERE job='2017112122202917203100908203303000715';
    The following information is returned:
    +---------------------------------------+-------------+---------+----------+-----------------------+-----------------------+----------------------------------------------------------------+
| job_id                                | schema_name | status  | fail_msg | create_time           | update_time           | definition                                                     |
+---------------------------------------+-------------+---------+----------+-----------------------+-----------------------+----------------------------------------------------------------+
| 2017112122202917203100908203303000715 | test        | RUNNING | NULL     | 2017-11-21 22:20:31.0 | 2017-11-21 22:20:40.0 |  INSERT overwrite INTO test SELECT * FROM test_external_table  |
  • Task state
    • INIT: indicates that the task is being initialized.
    • RUNNING: indicates that the task is running.
    • FINISH or FAILED: indicates that the task is successful or fails.

Terminate a task

  • Syntax
    CANCEL job 'job_id';
  • Example
    CANCEL job '2017112122202917203100908203303000715';
  • Description
    • Unscheduled, failed, and successful tasks are removed from the queue.
    • If a running task is terminated, imported data may be rolled back. We recommend that you manually delete the residual data.