DataWorks:Synchronize data

Add the HttpFile data source

1. Log on to the DataWorks console. In the top navigation bar, select your region. In the left-side navigation pane, choose More > Management Center. Select your workspace from the drop-down list and click Go to Management Center.

2. In the left-side navigation pane of the SettingCenter page, click Data Sources.

3. In the upper-left corner of the Data Sources page, click Add Data Source. In the dialog box, click HttpFile.

4. On the Add HttpFile Data Source page, configure the following parameters:

   Parameter	Value
   Data Source Name	user_behavior_analysis_httpfile
   Data Source Description	Used as the source of a batch synchronization task to access website access log test data. Read-only.
   URL	https://dataworks-workshop-2024.oss-cn-shanghai.aliyuncs.com (same for development and production environments)

5. Find the resource group you want to use. Click Test Network Connectivity in both the Connection Status (Development Environment) and Connection Status (Production Environment) columns, then confirm that Connected appears in both columns.

   Important

   At least one resource group must show Connected. Otherwise, you cannot use the codeless UI to configure a data synchronization task for this data source.

6. Click Complete Creation.

Add the MySQL data source

1. In the left-side navigation pane of the SettingCenter page, click Data Sources. In the upper-left corner, click Add Data Source.

2. In the dialog box, select MySQL.

3. On the Add MySQL Data Source page, configure the following parameters:

   Parameter	Value
   Data Source Name	user_behavior_analysis_mysql
   Data Source Description	Used as the source of a batch synchronization task to access basic user information test data. Read-only.
   Configuration Mode	Connection String Mode
   Host IP Address	rm-bp1z69dodhh85z9qa.mysql.rds.aliyuncs.com
   Port Number	3306
   Database Name	workshop
   Username	workshop
   Password	workshop#2017
   Authentication Method	No Authentication

4. Find the resource group you want to use. Click Test Network Connectivity in both the Connection Status (Development Environment) and Connection Status (Production Environment) columns, then confirm that Connected appears in both columns.

5. Click Complete Creation.

Add the OSS data source

The MySQL data and the HttpFile access logs are both synced to a private OSS data source.

1. In the left-side navigation pane of the SettingCenter page, click Data Sources. In the upper-left corner, click Add Data Source.

2. In the dialog box, select OSS.

3. On the Add OSS Data Source page, configure the following parameters:

   Important

   The AccessKey secret is displayed only when you create it. Store it securely. If the AccessKey pair is leaked or lost, delete it and create a new AccessKey pair.

   Parameter	Value
   Data Source Name	test_g
   Data Source Description	The description of the data source.
   Access Mode	AccessKey Mode
   AccessKey ID	Your AccessKey ID. Go to the AccessKey page to copy it.
   AccessKey Secret	Your AccessKey secret.
   Endpoint	http://oss-cn-shanghai-internal.aliyuncs.com
   Bucket	dw-emr-demo (the OSS bucket you created during environment preparation)

4. Find the resource group you want to use. Click Test Network Connectivity in both the Connection Status (Development Environment) and Connection Status (Production Environment) columns, then confirm that Connected appears in both columns.

   At least one resource group must show Connected. Otherwise, you cannot use the codeless UI to configure a data synchronization task for this data source.

5. Click Complete Creation.

Configure the initial node

The zero-load node workshop_start_emr controls when the workflow runs. It triggers the entire workflow daily at 00:30.

1. In the Scheduled Workflow pane, double-click the workshop_start_emr node. In the right-side navigation pane, click Properties.

2. Configure the scheduling properties:

   Section	Setting
   Schedule	Set the scheduling time to 00:30. Set Rerun to Allow Regardless of Running Status — this lets you re-run the workflow manually without waiting for it to finish or fail.
   Scheduling dependencies	Configure workshop_start_emr as a descendant of the workspace root node (named in the Workspace name_root format). The root node triggers this workflow to run.

3. Click the icon to save.

Configure the batch synchronization nodes

Create EMR Hive tables

Create two EMR Hive external tables — ods_user_info_d_emr and ods_raw_log_d_emr — that point to the synced data in OSS. These tables let you query the synchronized data directly.

1. In the Scheduled Workflow pane, click the workshop_emr workflow, right-click EMR, and then choose Create Node > EMR Hive.

2. Create two nodes: ods_user_info_d_emr and ods_raw_log_d_emr. Then draw dependency lines on the workflow configuration tab so both EMR Hive nodes depend on the corresponding batch synchronization nodes.

   

3. Configure the ods_user_info_d_emr node:

   1. Double-click the node. On the configuration tab, enter the following SQL:

      CREATE EXTERNAL TABLE IF NOT EXISTS ods_user_info_d_emr
      (
          `uid` STRING COMMENT 'The user ID',
          `gender` STRING COMMENT 'The gender',
          `age_range` STRING COMMENT 'The age range',
          `zodiac` STRING COMMENT 'The 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 path matches the Object Name (Path Included) value you set in the ods_user_info_d_2oss_emr synchronization node. dw-emr-demo is the OSS bucket you created during environment preparation.

   2. Click Properties and configure the scheduling settings:

      Section	Setting
      Scheduling Parameter	Set Parameter Name to bizdate, Parameter Value to $[yyyymmdd-1].
      Schedule	Set Rerun to Allow Regardless of Running Status.
      Dependencies	Set the output table to this node. The output table name follows the Workspace name.Node name format.
      Resource Group	Select your serverless resource group.

   3. Click the icon to save and run the node.

4. Configure the ods_raw_log_d_emr node:

   1. Double-click the node. On the configuration tab, enter the following SQL:

      -- Create the table used to store website access 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 path matches the Object Name (Path Included) value you set in the ods_raw_log_d_2oss_emr synchronization node. dw-emr-demo is the OSS bucket you created during environment preparation.

   2. Click Properties and configure the scheduling settings:

      Section	Setting
      Scheduling Parameter	Set Parameter Name to bizdate, Parameter Value to $[yyyymmdd-1].
      Schedule	Set Rerun to Allow Regardless of Running Status.
      Dependencies	Set the output table to this node. The output table name follows the Workspace name.Node name format.
      Resource Group	Select your serverless resource group.

   3. Click the icon to save and run the node.

Run the workflow

On the DataStudio page, double-click the workshop_emr workflow under Business Flow. On the workflow configuration tab, click the icon in the toolbar to run all nodes in dependency order.

Check node status

• Nodes in the state are running normally.

• If a node shows and the error "java.net.ConnectException: Connection timed out (Connection timed out)" appears, add a security group rule in the ECS console. Use port 10000 of your ECS instance to connect to DataWorks, and set Authorization Object to the CIDR block of the vSwitch associated with your resource group. To find the vSwitch CIDR block, go to the Resource Groups page, find your resource group, click Network Settings, and check the VPC Binding tab. For details, see Add a security group rule.

Verify the sync results

To confirm data was written successfully, right-click the ods_user_info_d_emr or ods_raw_log_d_emr node and select View Logs. If the sync completed, the logs show a result similar to:

Then create an ad hoc query to count the imported records.

1. In the left-side navigation pane of the DataStudio page, click the icon. In the Ad Hoc Query pane, right-click Ad Hoc Query and choose Create Node > EMR Hive.

2. Run the following queries to confirm the record counts. Replace Data timestamp with the actual partition date — if the workflow ran on November 7, 2019, the data timestamp is 20191106 (one day before).

   SELECT * FROM ods_user_info_d_emr WHERE dt = Data timestamp;
   SELECT * FROM ods_raw_log_d_emr WHERE dt = Data timestamp;