AnalyticDB:Asynchronously submit an export job

Scenarios

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

Asynchronously submit a job

Syntax

SUBMIT JOB INSERT OVERWRITE table_name 
select_statement;

Examples

SUBMIT JOB INSERT OVERWRITE INTO courses_external_table SELECT * FROM courses;

After you execute the preceding statement, a job ID is returned. You can use the job ID to query the state of the asynchronous job.

+---------------------------------------+
| job_id                                |
+---------------------------------------+
| 2017112122202917203100908203303000715 |

Configure priority-based scheduling

AnalyticDB for MySQL V3.1.3.6 and later support priority-based scheduling. You can add the /*+async_job_priority=n*/ hint to the beginning of the job submission statement to specify the priority for an asynchronous job. The default priority value is 1. A greater value specifies a higher priority for scheduling the job.

/*+async_job_priority=10*/ SUBMIT JOB INSERT OVERWRITE INTO courses_external_table SELECT * FROM courses;

Query the state of an asynchronous job

Syntax

SHOW JOB STATUS WHERE job='job_id';

Examples

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 courses_external_table SELECT * FROM courses  |

Job states and mappings

Terminate a job

Description

• Unscheduled, failed, and successful jobs are removed from the queue.

• If a running job is terminated, the data exported by the job may be rolled back. We recommend that you manually delete the residual data.

Syntax

CANCEL JOB 'job_id';

Examples

CANCEL JOB '2017112122202917203100908203303000715';