DataWorks:Synchronize data

Create an HttpFile data source

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

   2. On the Workspace Management page, click Data Sources in the left-side navigation pane to open the data source page.

2. Click Add Connection. In the Add Connection dialog box, search for and select HttpFile.

3. On the Create HttpFile data source page, configure the parameters. In this tutorial, use the example values for both the development environment and the production environment.

   Parameter	Description
   Data Source Name	Enter a name for the data source. For this tutorial, enter user_behavior_analysis_httpfile.
   Description	Enter a description. This tutorial-specific data source provides test data for batch synchronization tasks. It is read-only and can be used only for Data Integration.
   URL domain	The URL domain for both the development and production environments is https://dataworks-workshop-2024.oss-cn-shanghai.aliyuncs.com.

4. Click Connection Status (Development Environment) and Connection Status (Production Environment) in the row of the target resource group, and then click Test Connectivity. Wait until the connectivity test is complete and the status shows Connectable.

   Important

   Make sure that at least one resource group shows the Connectable status. Otherwise, you cannot use wizard mode to create synchronization tasks for this data source.

5. Click Complete Creation.

Create a MySQL data source

1. On the Management Center page, go to the Data Source page and click Add Connection.

2. In the Add Connection dialog box, search for and select MySQL.

3. On the Create MySQL data source page, configure the parameters. In this tutorial, use the example values for both the development environment and the production environment.

   Parameter	Description
   Data Source Name	Enter a name for the data source. For this tutorial, enter user_behavior_analysis_mysql.
   Description	Enter a description. This tutorial-specific data source provides test data for batch synchronization tasks. It is read-only and can be used only for Data Integration.
   Configuration Mode	Select User-created Data Store with Public IP Addresses.
   Connection Address	Host IP address: rm-bp1z69dodhh85z9qa.mysql.rds.aliyuncs.com Port: 3306
   Database Name	Enter a database name. For this tutorial, enter workshop.
   Username	Enter a username. For this tutorial, enter workshop.
   Password	Enter a password. For this tutorial, enter workshop#2017.
   Authentication Method	No authentication.

4. Click Connection Status (Development Environment) and Connection Status (Production Environment) in the row of the target resource group, and then click Test Connectivity. Wait until the connectivity test is complete and the status shows Connectable.

5. Click Complete Creation.

Create an OSS data source

In this tutorial, the user information from the MySQL data source and the log data from the HttpFile data source are synchronized to an OSS data source.

1. On the Management Center page, go to the Data Source page and click Add Connection.

2. In the Add Connection dialog box, search for and select OSS.

3. On the Create OSS data source page, configure the parameters. In this tutorial, use the example values for both the development environment and the production environment.

   Parameter	Description
   Data Source Name	Enter a name for the data source. In this example, enter test_g.
   Description	Enter a brief description of the data source.
   Access mode	Select Access Key mode.
   AccessKey ID	The AccessKey ID of the current logon account. You can go to the AccessKey page to copy the AccessKey ID.
   AccessKey Secret	Enter the AccessKey Secret of the current logon account. Important The AccessKey Secret is displayed only when you create it. You cannot view it afterwards. Keep it secure. If the AccessKey is leaked or lost, delete it and create a new AccessKey.
   Endpoint	Enter http://oss-cn-shanghai-internal.aliyuncs.com.
   Bucket	The name of the OSS bucket that you configured when creating the EMR cluster during environment preparation. In this example, enter dw-emr-demo.

4. Click Connection Status (Development Environment) and Connection Status (Production Environment) in the row of the target resource group, and then click Test Connectivity. Wait until the connectivity test is complete and the status shows Connectable.

   Note

   Make sure that at least one resource group shows the Connectable status. Otherwise, you cannot use wizard mode to create synchronization tasks for this data source.

5. Click Complete Creation.

1. Click the icon in the upper-left corner and choose All Products > Data Development and O&M > DataStudio.

2. In the DataStudio panel, right-click Workflow and select Create Workflow.

3. In the Create Workflow dialog box, set Workflow Name to workshop_emr, and then click New.

4. Go to the workflow development panel and create a virtual node and batch synchronization nodes.

   1. Click New Node, select Zero-Load Node, and drag it to the editing canvas on the right. In the New Node dialog box, set Node Name to workshop_start_emr, and click Confirm.

   2. Click New Node, select Offline synchronization, and drag it to the editing canvas on the right. Set Node Name to ods_raw_log_d_2oss_emr and ods_user_info_d_2oss_emr respectively, which are used to synchronize the MySQL user information and the HttpFile log data. Then click Confirm.

5. In the workflow development panel, drag to connect the nodes and set workshop_start_emr as the upstream node of the two batch synchronization nodes.

Configure the workflow initial node

1. On the DataStudio page, double-click the virtual node under the workflow. On the node editing page, click Scheduling Configuration on the right side.

2. Configure the schedule settings.

   Schedule settings	Description
   Schedule time	Set the schedule time of the virtual node to 00:30. The virtual node starts and runs the workflow every day at 00:30. Set RUN Attribute to Rerun on Success or Failure.
   Schedule dependency	Because the virtual node workshop_start_emr has no upstream dependency, you can directly set it to depend on the Workspace Root Node, so that the workspace root node triggers the execution of the workshop_start_emr node. The workspace root node is named in the format: WorkspaceName_root.

3. After the configuration is complete, click the icon in the upper-left corner.

Configure batch synchronization nodes

1. Synchronize the user information from the MySQL data source to the self-managed OSS.

   1. On the DataStudio page, double-click the ods_user_info_d_2oss_emr node to open the node configuration page.

   2. Configure the synchronization network connection.

      After you complete the following network and resource configurations, click The next Step and complete the connectivity test as prompted.

      Parameter	Description
      Data source	Source: MySQL. Data source name: user_behavior_analysis_mysql.
      My Resource Group	Select the purchased serverless resource group.
      Data going	Destination: OSS. Data source name: Select the private OSS data source you created earlier. In this example, it is test_g.

   3. Configure the synchronization task.

      Parameter	Description
      Data source	Table: Select ods_user_info_d from the data source. Shard Key: We recommend that you use a primary key or an indexed column as the split key. Only integer columns are supported. In this example, set the split key to uid.
      Data going	File Type: Select text. Object Name (Path Included): Enter the path based on the directory structure of your self-managed OSS. In this example, enter ods_user_info_d/user_${bizdate}/user_${bizdate}.txt, where ods_user_info_d is the directory name you created and $bizdate retrieves the date of the previous day. Field Delimiter: Enter | as the column delimiter.

   4. Configure the schedule settings.

      On the configuration page, click Scheduling Configuration on the right side to open the Scheduling Configuration panel and configure the scheduling and node information. The following describes the configurations.

      Configuration item	Configuration
      Scheduling Parameters	In the Scheduling Parameters section, add: Parameter name: bizdate Parameter value: $[yyyymmdd-1]
      Time attribute	Set RUN Attribute to Rerun on Success or Failure.
      Scheduling Dependency	In Scheduling Dependency, verify that the output table is set as the output of this node. The format is WorkSpaceName.NodeName.

   5. After the configuration is complete, click the icon on the toolbar.

2. Synchronize the log data from the HttpFile data source to the self-managed OSS.

   1. On the DataStudio page, double-click the ods_raw_log_d_2oss_emr node to open the node configuration page.

   2. Configure the synchronization network connection.

      After you complete the following network and resource configurations, click The next Step and complete the connectivity test as prompted.

      Parameter	Description
      Data source	Source: HttpFile. Data source name: user_behavior_analysis_httpfile.
      My Resource Group	Select the purchased serverless resource group.
      Data going	Destination: OSS. Data source name: Select the private OSS data source you created earlier. In this example, it is test_g.

   3. Configure the synchronization task.

      Parameter	Description
      Data source	File Path: /user_log.txt. File Type: Select text. Field Delimiter: Enter | as the column delimiter. Compression Format: Four types are available: None, Gzip, Bzip2, and Zip. In this example, select None. Skip Header: Select No.
      Data going	File Type: Select text. Object Name (Path Included): Enter the path based on the directory structure of your self-managed OSS. In this example, enter ods_raw_log_d/log_${bizdate}/log_${bizdate}.txt, where ods_raw_log_d is the directory name you created and $bizdate retrieves the date of the previous day. Field Delimiter: Enter | as the column delimiter.

   4. Configure the schedule settings.

      On the configuration page, click Scheduling Configuration on the right side to open the Scheduling Configuration panel and configure the scheduling and node information. The following describes the configurations.

      Configuration item	Configuration
      Scheduling Parameters	In the Scheduling Parameters section, add: Parameter name: bizdate Parameter value: $[yyyymmdd-1]
      Time attribute	Set RUN Attribute to Rerun on Success or Failure.
      Scheduling Dependency	In Scheduling Dependency, verify that the output table is set as the output of this node. The format is WorkSpaceName.NodeName.

   5. After the configuration is complete, click the icon on the toolbar to save the settings.

Create EMR tables to query synchronized data

Create two tables, ods_user_info_d_emr and ods_raw_log_d_emr, to query the synchronized raw MySQL user data and OSS log data respectively.

1. On the DataStudio page, open the workflow you created, right-click EMR, and choose New Node > EMR Hive.

2. In the New Node dialog box, enter a Node Name and click Confirm.

   You need to create two EMR Hive nodes (ods_user_info_d_emr and ods_raw_log_d_emr) to create the corresponding two tables. In the workflow development panel, drag to connect the nodes and configure the upstream and downstream dependencies.

   Set workshop_start_emr as the upstream node of ods_user_info_d_2oss_emr and ods_raw_log_d_2oss_emr. Then set these two synchronization nodes as the upstream nodes of ods_user_info_d_emr and ods_raw_log_d_emr respectively.

3. Enter the table creation statements in the respective EMR Hive nodes. In Scheduling Configuration, set Scheduling Resource Groups to the purchased serverless resource group. Then click Save and Run the table creation statements.

   • Create the ods_user_info_d_emr table.

     Double-click the ods_user_info_d_emr node to open the editing page and edit the task for creating the ods_user_info_d_emr table.

     1. Edit the SQL code.

        CREATE EXTERNAL TABLE IF NOT EXISTS ods_user_info_d_emr
        (
            `uid` STRING COMMENT 'User ID',
            `gender` STRING COMMENT 'Gender',
            `age_range` STRING COMMENT 'Age range',
            `zodiac` STRING COMMENT 'Zodiac sign'
        ) PARTITIONED BY (
          dt STRING
        )
        ROW FORMAT  delimited fields terminated by '|'
        LOCATION 'oss://dw-emr-demo/ods_user_info_d/';
        
        ALTER TABLE ods_user_info_d_emr ADD IF NOT EXISTS PARTITION (dt='${bizdate}')
        LOCATION 'oss://dw-emr-demo/ods_user_info_d/user_${bizdate}/';

        Note

        The location in the preceding code is an example path. It corresponds to the destination configured in the batch synchronization task. Replace it with the file path you created, where dw-emr-demo is the OSS bucket domain name you created during environment preparation.

     2. Configure the schedule settings.

        Configuration item	Configuration
        Scheduling Parameters	In the Scheduling Parameters section, add: Parameter name: bizdate Parameter value: $[yyyymmdd-1]
        Time attribute	Set RUN Attribute to Rerun on Success or Failure.
        Scheduling Dependency	In Scheduling Dependency, verify that the output table is set as the output of this node. The format is WorkSpaceName.NodeName.

     3. After the configuration is complete, click to save the node.

   • Create the ods_raw_log_d_emr table.

     Double-click the ods_raw_log_d_emr node and edit the task for creating the ods_raw_log_d_emr table.

     1. Edit the SQL code.

        --Create target table for OSS logs
        CREATE EXTERNAL TABLE IF NOT EXISTS ods_raw_log_d_emr
        (
          `col` STRING
        ) PARTITIONED BY (
          dt STRING
        );
        ALTER TABLE ods_raw_log_d_emr ADD IF NOT EXISTS PARTITION (dt='${bizdate}')
        LOCATION 'oss://dw-emr-demo/ods_raw_log_d/log_${bizdate}/';

        Note

        The location in the preceding code is an example path. It corresponds to the destination configured in the batch synchronization task. Replace it with the file path you created, where dw-emr-demo is the name of the OSS bucket you created during environment preparation.

     2. Configure the schedule settings.

        Configuration item	Configuration
        Scheduling Parameters	In the Scheduling Parameters section, add: Parameter name: bizdate Parameter value: $[yyyymmdd-1]
        Time attribute	Set RUN Attribute to Rerun on Success or Failure.
        Scheduling Dependency	In Scheduling Dependency, verify that the output table is set as the output of this node. The format is WorkSpaceName.NodeName.

     3. After the configuration is complete, click to save the node.

Run the workflow

1. On the Data Studio page, double-click workshop_emr under Workflow to open the workflow panel. Then click the icon on the toolbar to run the workflow based on the upstream and downstream dependencies.

2. Verify the execution results.

   • View the task running status:

     • If a node is in the status, the synchronization is executed without issues.

     • If a node is in the status and the error java.net.ConnectException: Connection timed out (Connection timed out) occurs, add a security group rule in the ECS console to allow inbound traffic on port 10000 and set the authorization object to the vSwitch CIDR block of the resource group's VPC. You can click Network Settings for the corresponding resource group in the resource group list to obtain the vSwitch CIDR block. For more information about how to add a security group rule, see Add a security group rule.

   • View the task execution logs: Right-click the ods_user_info_d_emr or ods_raw_log_d_emr node and select View Log. If the following message appears in the logs, the synchronization node ran successfully and the data was synchronized.

     State:3(RUN) China Standard Time 2019
     Logview: China Standard Time 2019
     
     State:4(SUCCESS) China Standard Time 2019
     
     Total:20028R  442.8KB
     Exit SUCCESS
     
     INFO  Shell run successfully!
     Current task status: FINISH
     Cost Time: 48.309s

Query the synchronization results

1. Create an ad hoc query file.

   In the left-side navigation pane of the DataStudio page, click to go to the ad hoc query panel. Right-click Ad Hoc Query and choose New Node > EMR Hive.

2. Query the synchronization result tables.

   Execute the following SQL statements to verify the synchronized data. Check the number of records imported into the ods_raw_log_d_emr and ods_user_info_d_emr tables.

   --The partition column in the query must be updated to the business date. For example, if the task runs on 20191107, the business date is 20191106, which is the day before the task run date.
   SELECT * from ods_user_info_d_emr where dt=business_date; 
   SELECT * from ods_raw_log_d_emr where dt=business_date;