MySQL node

Prerequisites

• The business process has been created.

  Data Studio performs engine-specific development operations based on business flows. Before creating a node, create a business flow first. For more information, see Create a business flow.

• A MySQL data source has been added.

  Before you can access data from your MySQL database, you must add the database as a MySQL data source in DataWorks. For more information, see MySQL data source.

  Note

  MySQL nodes only support MySQL data sources that are added in connection string mode.

• (Optional; required for Resource Access Management (RAM) users) The RAM user used for task development has been added to the target workspace and assigned either the Development or Workspace Administrator role (which grants broad permissions—assign with caution). For more information about adding members and granting permissions, see Add members to a workspace.

Limitations

• MySQL nodes support task development only for MySQL data sources that are added in connection string mode in the production environment. To check the mode of a data source, go to the Data source management page as described in Configure a MySQL data source, find the data source, and then click Edit in the Operation column.

• MySQL nodes do not support MySQL 8.0 or later.

• Supported regions: China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Shenzhen), China (Chengdu), China (Hong Kong), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), and US (Virginia).

Network connectivity

• Network connectivity

  Your MySQL database must have network connectivity to the serverless resource group (recommended) or the exclusive resource group for scheduling. For more information, see Network connectivity solutions.

• Configure an IP address whitelist

  If your MySQL database is protected by an IP address whitelist, you must add the VPC CIDR block and public IP addresses of the serverless resource group (recommended) or the exclusive resource group for scheduling to the IP address whitelist of the database. For more information, see Configure an IP address whitelist.

• Test network connectivity

  You can test the network connectivity when you add a DataWorks data source. For more information, see Test network connectivity.

MySQL task development

1. Go to the Data Studio page.

   Log on to the DataWorks console. In the target region, click Data Integration > Data Integration in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Integration.

2. Create a Workflow.

   If you have an existing Workflow, skip this step.

   1. Hover over the icon and select Create Workflow.

   2. In the Create Workflow dialog box, enter a Workflow Name.

   3. Click Create.

3. Create a MySQL node.

   1. Hover over the icon and choose Create Node > Database > MySQL.

      You can also find your workflow, right-click its name, and choose Create Node > Database > MySQL.

   2. In the Create Node dialog box, enter a Name and select a Node Type and Path.

      Note

      The node name can contain only uppercase and lowercase letters, Chinese characters, digits, underscores (_), and periods (.). The name cannot exceed 128 characters in length.

   3. Click OK to open the MySQL node editor.

4. Use the MySQL node to develop a MySQL task.

   1. Select a data source.

      From the Select DataSource drop-down list, select the target data source. If the data source you need is not in the list, click New data source on the right. Then, create a data source on the Data source management page. For more information, see Configure a MySQL data source.

      Note

      • If your workspace is in standard mode, the drop-down list shows only MySQL data sources configured for both the development and production environments.

      • MySQL nodes support task development only for MySQL data sources that are added in connection string mode in the production environment. To check the mode of a data source, go to the Data source management page as described in Configure a MySQL data source, find the data source, and then click Edit in the Operation column.

   2. Select a resource group.

      In the toolbar, click the icon. In the Parameter dialog box, select a resource group for scheduling.

      Note

      • To access a data source over the public internet or a VPC, you must use the resource group for scheduling that passed the connectivity test with the data source. For more information, see Network connectivity solutions.

      • To change the resource group for a specific run, click Run with Parameters and select a different resource group for scheduling.

   3. Use SQL statements to create a task.

      Write your SQL statements in the SQL editor.

      For example, the following statement queries data in the xc_emp table.

      select * from xc_emp;

      After the query is executed, the results are returned in a table on the Results tab. The table includes the following columns: empno, aname, job, mgr, hiredate, sal, comm, and deptno.

      If the task fails to run, you can view the error message and troubleshoot the issue by referring to FAQ: Task fails with "JDBC driver not supported" error.

   4. Save and run the SQL statements.

      In the toolbar, click the icon to save the SQL statements, and then click the icon to run the task.

5. Configure scheduling properties for the task.

   To run the node periodically, click Scheduling in the right-side pane and configure its scheduling properties:

   • Configure the basic scheduling properties for the task. For more information, see Configure basic properties.

   • Configure the scheduling cycle, rerun properties, and dependencies. For more information, see Configure time properties and Configure same-cycle scheduling dependencies.

     Note

     You must set the Rerun attribute and Parent Nodes for the node before you can commit it.

   • Configure resource properties. For more information, see Configure resource properties. To access a MySQL data source over the public internet or a VPC, select a resource group for scheduling that has network connectivity to the MySQL data source. For more information, see Network connectivity solutions.

6. Commit and deploy the task.

   1. In the toolbar, click the icon to save the node.

   2. In the toolbar, click the icon to commit the task.

   3. In the Commit Node dialog box, enter a Change Description.

   4. Click Determine.

   In a standard mode workspace, you must deploy the task to the production environment after you commit it. In the top menu bar, click Deploy. For more information, see Deploy tasks.

7. View scheduled tasks.

   1. In the upper-right corner of the editor, click O&M Personnel to open the production environment's Operation Center.

   2. View the scheduled tasks that are running. For more information, see Manage scheduled tasks.

   To view more details about scheduled tasks, click Operation Center in the top menu bar. For more information, see Operation Center overview.

FAQ: Task fails with "JDBC driver not supported" error

• Problem description

  The task fails with the error message: sql execute failed! JDBC driver not supported.

• Cause

  This error occurs if you select a MySQL data source that was not created in connection string mode.

• Solution

  Select a data source that was created in connection string mode. To check the mode of a data source, go to the Data source management page as described in Configure a MySQL data source, find the data source, and then click Edit in the Operation column.

FAQ: Connectivity test passes, but task fails

• Possible cause 1: You selected the wrong resource group for the connectivity test. Ensure that you used the resource group for scheduling for the test. For more information, see Step 4: Test network connectivity.

• Possible cause 2: You used one resource group for scheduling for the connectivity test but selected a different one to run the task. To resolve this, click Run with Parameters to select the correct resource group for scheduling.