Use DataWorks to schedule AnalyticDB for MySQL tasks

Purpose

An important function of task scheduling is dependencies between tasks. This section uses two AnalyticDB for MySQL tasks created in DataWorks to demonstrate this function. The following figure shows the relationship between tables and tasks.

• Task 1

  AnalyticDB for MySQL executes the O_ORDERSTATUS='F' statement to filter finished orders in the orders table and then writes the orders into the finished_orders table.

• Task 2

  AnalyticDB for MySQL executes the O_TOTALPRICE > 10000 statement to filter orders whose total price is greater than 10,000 from the finished_orders table, and then writes the orders into the high_value_finished_orders table.

Prerequisites

1. An AnalyticDB for MySQL cluster is created and a whitelist is configured. For more information, see Limits.
   Note The AnalyticDB for MySQL cluster is in the same region as DataWorks.
   • If you want to access AnalyticDB for MySQL from DataWorks by using a public endpoint, you must apply for a public endpoint, and then add the IP addresses in the DataWorks whitelist to the AnalyticDB for MySQL whitelist.
   • If you want to access AnalyticDB for MySQL from DataWorks by using a VPC, you must add the exclusive resource group in DataWorks.The VPC of the exclusive resource group is the same as that of AnalyticDB for MySQL.
   • This section uses the ORDERS table in the TPC-H dataset as test data. After you access an AnalyticDB for MySQL database, you must execute the following SQL statements to create a test table:

     CREATE TABLE orders(
           o_orderkey bigint NOT NULL COMMENT'',
           o_custkey int NOT NULL COMMENT'',
           o_orderstatus varchar NOT NULL COMMENT'',
           o_totalprice decimal(15,2) NOT NULL COMMENT'',
           o_orderdate date NOT NULL COMMENT'',
           o_orderpriority varchar NOT NULL COMMENT'',
           o_clerkvar char NOT NULL COMMENT'',
           o_shippriority int NOT NULL COMMENT'',
           o_comment varchar NOT NULL COMMENT'',
           dummy varchar
       )DISTRIBUTED BY HASH(o_orderkey);
     CREATE TABLE finished_orders (
            o_orderkey bigint NOT NULL,
            o_totalprice decimal(15,2) NOT NULL,
            primary key (o_orderkey)
        )DISTRIBUTE BY HASH(o_orderkey);
     
     CREATE TABLE high_value_finished_orders (
           o_orderkey bigint NOT NULL,
           o_totalprice decimal(15,2) NOT NULL,
           PRIMARY KEY (`o_orderkey`)
       )
       DISTRIBUTED BY HASH(o_orderkey);

2. A workspace is created in DataWorks. For more information, see Create a workspace.
   Note Select standard mode for the workspace.

Step 1. Create an AnalyticDB for MySQL connection in DataWorks

For more information, see Configure an AnalyticDB for MySQL connection.

DataWorks implements security control on connected services. You must add endpoints and ports of AnalyticDB for MySQL databases to the whitelist of DataWorks.

Step 2. Create an AnalyticDB for MySQL task in DataWorks

1. Log on to the DataWorks console. In the left-side navigation pane, click Workspaces. Click Data Analytics in the Actions column corresponding to the created workspace.
2. Right-click Business Flow and select Create Workflow to create a workflow named adb_task.
   
3. On the adb_task tab, choose Custom > AnalyticDB for MySQL from the navigation tree to create a data analytics node named finished_orders in the workflow.
   
4. Click finished_orders, set Select a connection to adb_source, and execute the following SQL statements:

   Task 1: AnalyticDB for MySQL uses O_ORDERSTATUS='F' to filter finished orders in the orders table and then writes the orders into the finished_orders table.

    use adb_demo;
    insert into finished_orders
    select O_ORDERKEY, O_TOTALPRICE
    from orders 
    where O_ORDERSTATUS = 'F';

   
5. Follow the preceding steps to create a data analytics node whose name is high_value_finished_orders.

   Task 2: AnalyticDB for MySQL uses O_TOTALPRICE > 10000 to filter orders whose total price is greater than 10,000 from the finished_orders table and writes the orders into the high_value_finished_orders table.

    use adb_demo;
    insert into high_value_finished_orders
    select * from finished_orders
    where O_TOTALPRICE > 10000;

   

Step 3: Configure task dependencies

In DataWorks, tasks can be triggered by scheduling a configuration based on time or dependencies. Multiple tasks are executed at a specified time based on specified dependencies. For more information, see Scheduling properties and Dependencies.

For example, you can execute the finished_orders task at 02:00 every day.

Execute the high_value_finished_orders task after the finished_orders task is executed.

Step 4. Publish a task

After you configure tasks, you can publish and maintain these tasks. For more information, see Deploy nodes.

After you publish a task, you can view tasks to be published.

After a task is published, you can view and maintain the task on the task O&M page.