DataWorks:Connect to a self-managed data source on an ECS instance

If your data source meets the following condition, we recommend that you use this solution:

• The data source is hosted on an Elastic Compute Service (ECS) instance.

Same Alibaba Cloud account and same region

If the ECS instance that hosts the data source and the DataWorks workspace are in the same Alibaba Cloud account and region, you can use a virtual private cloud (VPC) connection. To establish a network connection, deploy the resource group for the DataWorks workspace and the ECS instance in the same VPC.

Different Alibaba Cloud accounts, or same account but different regions

If the ECS instance that hosts the data source and the DataWorks workspace are in different Alibaba Cloud accounts or in the same Alibaba Cloud account but different regions, we recommend that you use a VPC network (intranet) connection. You can use a network connectivity tool, such as Cloud Enterprise Network or VPC Peering Connection, to establish network connectivity between the resource group for the DataWorks workspace and the VPC of the ECS instance.

Step 1: Obtain basic information

Step 2: Establish a network connection

Step 3: Add a route for the DataWorks resource group

If the ECS instance and the DataWorks resource group are in the same account but different regions, or in different accounts, you must add a route for the DataWorks resource group. The route must point to the CIDR block of the vSwitch where the ECS instance is located.

1. Go to the Resource Groups page in the DataWorks console. Find the target resource group and click Network Settings in the Actions column.

2. In the corresponding feature module, find the attached VPC and click Custom Route in the Actions column.

3. Click Add Route. For Connection Method, select CIDR Block. Set Destination CIDR Block to the vSwitch CIDR block of the ECS instance.

Step 4: (Optional) Enable remote access for the database

For some databases, you must enable remote access in their configuration files to allow specified users to access the databases from an external source using an IP address and port. The configuration methods vary for different databases. For more information, see the official documentation for your database.

For more information about how to enable remote access for a MySQL database, see 4. Enable remote access for the MySQL database.

Step 5: Configure the ECS security group

Alibaba Cloud ECS uses security groups to provide firewall capabilities. You must open the database port in the security group of the ECS instance to the VPC of the DataWorks resource group. This allows the DataWorks resource group to access the services that are deployed on the ECS instance.

1. Go to the ECS console. In the top navigation bar, select the region where the ECS instance is located.

2. In the navigation pane on the left, choose Instances & Images > Instances. Find the ECS instance where the MySQL database is deployed and click the instance name to go to the Instance Details page.

3. Click the Security Groups tab and then click the name of a security group to go to the Security Group Details page.

4. In the Access Rule section, click Add Rule and configure the following key parameters. You can use the default values for the other parameters.

   • Source: Enter the CIDR block of the vSwitch that is bound to the DataWorks resource group.

   • Port: Select the corresponding port for the database deployed on the ECS instance. For example, port 3306 must be open for MySQL.

1. Basic information

2. Establish a network connection

This section describes how to use a VPC peering connection to configure network connectivity between an ECS instance and DataWorks.

1. Log on to Alibaba Cloud Account A. Go to the VPC Peering Connection console, switch the region to China (Hangzhou) at the top of the page, and then click Create Peering Connection and configure the relevant parameters.

   The following table describes the key parameters. You can use the default values for the other parameters.

   Parameter	Configuration and example
   Peering Connection Name	The custom name of the peering connection. In this example, Account_A to Account_B is used.
   Requester VPC Instance	The VPC with which the ECS instance within Alibaba Cloud Account A is associated. In this example, select Account_A_hangzhou_VPC.
   Accepter Account Type	In this example, select Cross-account.
   Accepter Main Account UID	Enter the UID of Account B.
   Accepter Region Type	In this example, select Cross-region.
   Accepter Region	The region in which the DataWorks workspace and resource group reside within Alibaba Cloud Account B. In this example, select China (Shanghai).
   Accepter VPC	Enter the ID of the VPC with which the DataWorks resource group is associated within Alibaba Cloud Account B. In this example, the VPC is Account_B_shanghai_VPC.

2. Click OK to complete the peering connection configuration. You are automatically redirected to the basic information page of the peering connection. On this page, the Status is Peering Accepting.

3. Log on to Alibaba Cloud Account B and go to the VPC Peering Connection console. At the top of the page, switch the region to China (Shanghai). Find the peering connection record from Alibaba Cloud Account A and click Accept in the Actions column. After the request is accepted, the Status of the peering connection changes to Activated.

4. Click Configure Route Entry in the Accepter VPC column. In the Configure Route Entry dialog box, enter a custom Name and set Destination CIDR Block to the CIDR block of the requester VPC that contains the ECS instance. In this example, the CIDR block is set to 192.168.0.0/16.

   

5. Log on to Alibaba Cloud Account A and go to the VPC Peering Connection console. At the top of the page, switch the region to China (Hangzhou) and find the created peering connection.

6. In the Requester VPC Instance section, click Configure Route Entry. In the Configure Route Entry dialog box, enter a custom Name and set Destination CIDR Block to the VPC CIDR block of the accepter (DataWorks resource group). In this example, the CIDR block is 172.16.0.0/12.

   

3. Add a route for the DataWorks resource group

1. Log on to Alibaba Cloud Account B. Go to the Resource Groups page of the DataWorks console, find the target resource group, and click Network Settings in the Actions column.

2. In the corresponding feature module, find the attached VPC and click Custom Route in the Actions column.

3. Click Add Route. For Connection Method, select Specified CIDR Block. Set Destination CIDR Block to the vSwitch CIDR block of the ECS instance, for example, 192.168.6.0/24.

4. Enable remote access for the MySQL database

Connect to the ECS instance where the MySQL database is deployed to enable remote access for the database.

1. Find the my.cnf configuration file. For a default installation, the file is typically located at /etc/my.cnf.

   find / -name my.cnf

2. Run the vim /etc/my.cnf command to edit the configuration file. Replace the path to my.cnf with the actual path that you found in the previous step.

3. At the end of the configuration file, press i to add the following configuration in the [mysqld] section:

   bind-address=0.0.0.0

4. Press Esc and then enter :wq! to save and exit.

5. Run the systemctl restart mysqld command to restart the service.

6. Create a user that can be used to remotely connect to the MySQL database when you configure the data source in DataWorks.

   1. Run the mysql -u root -p command to log on to the database as an administrator.

   2. Create a user and set a password.

      -- "dataworks_user" is the username. You can customize it. -- "%" indicates that the user can access from any IP address. You can also specify an IP address for fine-grained control. -- "StrongPassword123!" is the password. You can customize it. CREATE USER 'dataworks_user'@'%' IDENTIFIED BY 'StrongPassword123!';

   3. Grant database permissions to the user.

      -- Run one of the following commands. -- Grant all privileges to the user. Use with caution. GRANT ALL PRIVILEGES ON *.* TO 'dataworks_user'@'%' WITH GRANT OPTION; -- Grant all privileges on a specific database, such as mydatabase, to the user. GRANT ALL PRIVILEGES ON mydatabase.* TO 'dataworks_user'@'%' WITH GRANT OPTION;

   4. Run the FLUSH PRIVILEGES; command to refresh permissions and then run the exit command to exit the database.

   5. Verify the remote connection.

      mysql -u dataworks_user -h <Primary private IP address of the ECS instance> -p

5. Configure the ECS security group

1. Log on to Alibaba Cloud Account A. Go to the ECS console and select China (Hangzhou) in the top navigation bar.

2. In the navigation pane on the left, choose Instances & Images > Instances. Find the ECS instance where the MySQL database is deployed and click the instance name to go to the Instance Details page.

3. Click the Security Group tab and then click the security group name to go to the Security Group Details page.

4. In the Access Rules section, click Add Rule and configure the following key parameters. You can use the default values for parameters that are not specified.

   • Source: Enter the vSwitch CIDR block associated with the DataWorks resource group. In this example, use 172.16.66.0/24.

   • Port: Select the port for the database that is deployed on the ECS instance. In this example, the port is 3306.

6. Test the network connection

1. Log on with Account B.

2. Log on to the DataWorks console. In the top navigation bar, select the desired region. In the left-side navigation pane, choose Data Integration > Data Integration. On the page that appears, select the desired workspace from the drop-down list and click Go to Data Integration.

3. In the navigation pane on the left, click Data Source. On the Data Sources page, click Add Data Source.

4. Select the MySQL data source type and configure its parameters.

   • Configuration Mode: Select Connection String Mode.

   • Host IP Address: Enter the private IP address of the ECS instance. In this example, 192.168.6.172 is used.

   • Port Number: Set to 3306.

   • Database Name: Enter the name of an existing database.

   • Username and Password: Enter the username and password of the dataworks_user account that you created in the 4. Enable remote access for the MySQL database step.

5. In the Connection Configuration section, click Test Connectivity for the resource group that is bound to the workspace and check whether the result is Connected.

   

   Note

   If the test fails, you can click Self-service Troubleshoot to resolve the issue. If the test still fails after troubleshooting, submit a ticket.