AnalyticDB:Use Azkaban to schedule XIHE SQL jobs

Prerequisites

• A MySQL client is installed on the server where Azkaban is installed. For more information, see MySQL documentation.

• Azkaban is installed. For more information, see Azkaban documentation.

• The IP address of the server that runs Azkaban is added to an IP address whitelist of an AnalyticDB for MySQL cluster. For more information, see Configure a whitelist.

Data preparations

In the AnalyticDB for MySQL cluster, create a database named azkaban_test. In the database, create a table named azkaban_test.names and insert test data into the table. Example:

-- Create a database.
CREATE DATABASE azkaban_test;
-- Create a table.
CREATE TABLE azkaban_test.names (
  id BIGINT,
  name STRING
);
-- Insert data into the table.
INSERT INTO azkaban_test.names VALUES(1, 'Li'), (2, 'Yang');

Procedure

1. Write a workflow file.

   1. Create a workflow folder. Create scripts to start, query, and end jobs in the folder.

      • Script to start jobs:

        #start
        type=command
        command=echo 'job start'

      • Script to query jobs:

        type=command
        dependencies=start
        command=mysql -hamv-bp165vrm0g8s****.ads.aliyuncs.com -utest -padb14**** -P3306 -e "create table azkaban_test.duplicated_names as select * from azkaban_test.names"

        Parameters:

        • -h: the endpoint of the AnalyticDB for MySQL cluster. You can log on to the AnalyticDB for MySQL console and go to the Cluster Information page to view the endpoint.

        • -u: the name of the database account of the AnalyticDB for MySQL cluster.

        • -p: the name of the database of the AnalyticDB for MySQL cluster.

        • -P: the port number of the AnalyticDB for MySQL cluster. Set the value to 3306.

        • -e: the SQL statement.

      • Script to end jobs:

        #end
        type=command
        dependencies=exec_query
        command=echo 'job end'

   2. Compress the workflow folder in the ZIP format.

2. Create a project and upload the workflow file that is created in Step 1.

   1. Log on to the Azkaban console. In the top navigation bar, click Projects.

   2. In the upper-right corner of the page, click Create Project.

   3. In the Create Project dialog box, configure the Name and Description parameters and click Create Project.

   4. In the upper-right corner of the page, click Upload.

   5. In the Upload Project Flies dialog box, select the workflow file and click Upload.

3. Execute the workflow.

   1. On the Projects page, click the Flows tab.

   2. Click Execute Flow.

   3. Click Execute.

   4. In the Flow submitted message, click Continue.

4. View the details about the workflow.

   1. In the top navigation bar, click Executing.

   2. Click the Recently Finished tab.

   3. Click the execution ID of the workflow. Click the Job List tab to view details about each job.

   4. Click Logs to view the job logs.