DataWorks:Synchronize data

You can perform the following operations to synchronize the basic user information in the MySQL data source to the OSS data source.

1. Open the batch synchronization node.

   In the canvas, move the pointer over the synchronization node ods_user_info_d_2oss_emr and click Open Node. The Configure Network Connections and Resource Group page appears.

2. Configure network connections and a resource group.

   Configure network connections and a resource group. The following table describes the parameters. After the network connectivity test is complete, click Next. The Configure tasks page appears.

   Parameter	Description
   Source	Source: Select MySQL. Data Source Name: Select user_behavior_analysis_mysql.
   Resource Group	In the Resource Group step, select the serverless resource group that you create when you prepare environments.
   Destination	Destination: Select OSS. Data Source Name: Set the value to test_g, which specifies the name of the OSS data source that you added.

3. Configure information about the source and destination.

   1. The following table describes the key parameters that you must configure in this tutorial. You can retain default values for other parameters.

      Parameter	Description
      Source	Table: Select the ods_user_info_d table in the data source. Split key: The shard key for the data to be read. We recommend that you use the primary key or an indexed column as the shard key. You can use the shard key to shard data only of the INTEGER type. In this example, uid is used.
      Destination	Text type: Set the value to text. Object Name (Path Included): The path of the OSS object. Configure this parameter based on the folder that you created in the OSS bucket. In this example, ods_user_info_d/user_${bizdate}/user_${bizdate}.txt is entered. ods_user_info_d is the name of the folder that you created in the OSS bucket. $bizdate indicates the date of the previous day. Column Delimiter: Enter |.

   2. Confirm field mappings in the Field Mapping section and configure channel control settings in the Channel Control section.

      DataWorks allows you to establish mappings between source fields and destination fields to synchronize data from specified source fields to specified destination fields. DataWorks also allows you to configure parameters such as Task Expected Maximum Concurrency and Policy for Dirty Data Records. In this tutorial, the Policy for Dirty Data Records parameter is set to Disallow Dirty Data Records, and default values are retained for the remaining parameters. For more information, see Configure a batch synchronization task by using the codeless UI.

4. Configure debugging parameters.

   In the right-side navigation pane of the configuration tab of the node, click Debugging Configurations. On the Debugging Configurations tab, configure the following parameters. These parameters are used to test the workflow in Step 4.

   Parameter	Description
   Resource Group	Select the serverless resource group that you create when you prepare environments. For more information, see Prepare environments.
   Script Parameters	You do not need to configure this parameter. In the sample code provided in this tutorial, the ${bizdate} variable is used to represent the data timestamp. When you debug the workflow by following Step 4, you can set the value of the variable to a constant, such as 20250223. When the workflow is run, the variables defined for the inner nodes of the workflow are replaced with the constant.

5. Save the batch synchronization node.

   After the configuration is complete, click the icon in the top toolbar to save the node.

You can perform the following operations to synchronize the user website access logs in the HttpFile data source to the OSS data source.

1. Open the batch synchronization node.

   In the canvas, move the pointer over the batch synchronization node ods_raw_log_d_2oss_emr and click Open Node. The Configure Network Connections and Resource Group page appears.

2. Configure network connections and a resource group.

   Configure network connections and a resource group. The following table describes the parameters. After the network connectivity test is complete, click Next. The Configure tasks page appears.

   Parameter	Description
   Source	Source: Select HttpFile. Data Source Name: Select user_behavior_analysis_httpfile.
   Resource Group	In the Resource Group step, select the serverless resource group that you create when you prepare environments.
   Destination	Destination: Select OSS. Data Source Name: Set the value to test_g, which specifies the name of the private OSS data source that you added.

3. Configure information about the source and destination.

   1. The following table describes the key parameters that you must configure in this tutorial. You can retain default values for other parameters.

      Parameter	Description
      Source	File Path: Set the value to /user_log.txt. Text type: Set the value to text. Column Delimiter: Enter |. Compression format: The compression format of the OSS object. Valid values: None, Gzip, Bzip2, and Zip. Select None. Skip Header: Set the value to No.
      Destination	Text type: Set the value to text. Object Name (Path Included): The path of the OSS object. Configure this parameter based on the folder that you created in the OSS bucket. In this example, ods_raw_log_d/log_${bizdate}/log_${bizdate}.txt is entered. ods_raw_log_d is the name of the folder that you created in the OSS bucket. $bizdate indicates the date of the previous day. Column Delimiter: Enter |.

   2. Configure debugging parameters.

      In the right-side navigation pane of the configuration tab of the node, click Debugging Configurations. On the Debugging Configurations tab, configure the following parameters. These parameters are used to test the workflow in Step 4.

      Parameter	Description
      Resource Group	Select the serverless resource group that you create when you prepare environments. For more information, see Prepare environments.
      Script Parameters	You do not need to configure this parameter. In the sample code provided in this tutorial, the ${bizdate} variable is used to represent the data timestamp. When you debug the workflow by following Step 4, you can set the value of the variable to a constant, such as 20250223. When the workflow is run, the variables defined for the inner nodes of the workflow are replaced with the constant.

4. Save the batch synchronization node.

   After the configuration is complete, click the icon in the top toolbar to save the node.

1. Develop the code.

   In the canvas, move the pointer over the EMR Hive node ods_user_info_d_emr and click Open Node to go to the configuration tab of the node. Enter the following sample SQL statement:

   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

   In the preceding code, a sample path is used for LOCATION. The path is based on the value of the Object Name (Path Included) parameter when you configure the related batch synchronization node. You must set the LOCATION parameter to the path of the created folder. dw-emr-demo is the domain name of the OSS bucket that you created when you prepared the environment.

2. Configure debugging parameters.

   In the right-side navigation pane of the configuration tab of the node, click Debugging Configurations. On the Debugging Configurations tab, configure the following parameters. These parameters are used to test the workflow.

   Parameter	Description
   Resource Group	Select the serverless resource group that you create when you prepare environments. For more information, see Prepare environments.
   Script Parameters	You do not need to configure this parameter. In the sample code provided in this tutorial, the ${bizdate} variable is used to represent the data timestamp. When you debug the node, you can set the value of the variable to a constant, such as 20250223. When the node is run, the variables defined for the inner nodes of the node are replaced with the constant.

3. Save and run the node.

   After the configuration is complete, you can click Save and Run to save and run the node.

   • If nodes are in the state, the synchronization process is normal.

   • If nodes are in the state and the error message "java.net.ConnectException: Connection timed out (Connection timed out)" appears, you must add a security group rule in the ECS console, use Port 10000 of the ECS instance to connect to DataWorks and set Authorization Object to the CIDR block of the vSwitch with which the resource group is associated. You can perform the following steps to obtain the vSwitch CIDR block: Go to the Resource Groups page, find the desired resource group, and click Network Settings in the Actions column. On the VPC Binding tab of the page that appears, obtain the vSwitch CIDR block. For more information about how to add security group rules, see Add a security group rule.

4. Query data synchronization results.

   1. Go to the Workspaces page in the DataWorks console. In the top navigation bar, select a desired region. Find the desired workspace and choose Shortcuts > Data Studio in the Actions column.

   2. In the left-side navigation pane of the Data Studio page, click the icon to go to the DATASTUDIO pane. The Workspace Directories section appears.

   3. Right-click the work workspace directory that you created and choose Create Node > EMR > EMR Hive. In the Create Node dialog box, enter the name of the EMR Hive node and click OK to create the node.

      Go to the configuration tab of the EMR Hive node, replace the data timestamp in the following script with the current data timestamp, and confirm the data write results. View the number of records that are imported into the ods_raw_log_d_emr and ods_user_info_d_emr tables.

      Note

      In the query statement, change the partition key value to the data timestamp of the node. For example, if a node is scheduled to run on February 23, 2025, the data timestamp of the node is 20250222, which is one day earlier than the scheduling time of the node.

      SELECT * FROM ods_user_info_d_emr WHERE dt=Data timestamp;

      • If the results returned after you execute the preceding statements show that data exists in the destination, the data process is complete.

      • If the results returned after you execute the preceding statements show that data does not exist in the destination, you must make sure that the values specified for the scheduling parameters defined for the inner nodes of the workflow in this run are the same as the value of the dt field in the preceding statements when you run the workflow. You can click Running History in the right-side navigation pane of the configuration tab of the workflow, and then click View in the Actions column of the running record generated for this run to view the data timestamp that is used when the workflow is run in the run logs of the workflow. The data timestamp is in the partition=[pt=xxx] format.

1. Develop the code.

   In the canvas, move the pointer over the EMR Hive node ods_raw_log_d_emr and click Open Node to go to the configuration tab of the node. Enter the following sample SQL statement:

   -- Create the table used to store user 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

   In the preceding code, a sample path is used for LOCATION. The path is based on the value of the Object Name (Path Included) parameter when you configure the related batch synchronization node. You must set the LOCATION parameter to the path of the created folder. dw-emr-demo is the domain name of the OSS bucket that you created when you prepared the environment.

2. Configure debugging parameters.

   In the right-side navigation pane of the configuration tab of the node, click Debugging Configurations. On the Debugging Configurations tab, configure the following parameters. These parameters are used to test the workflow.

   Parameter	Description
   Resource Group	Select the serverless resource group that you create when you prepare environments. For more information, see Prepare environments.
   Script Parameters	You do not need to configure this parameter. In the sample code provided in this tutorial, the ${bizdate} variable is used to represent the data timestamp. When you debug the node, you can set the value of the variable to a constant, such as 20250223. When the node is run, the variables defined for the inner nodes of the node are replaced with the constant.

3. Save and run the node.

   After the configuration is complete, you can click Save and Run to save and run the node.

4. Query data synchronization results.

   1. Go to the Workspaces page in the DataWorks console. In the top navigation bar, select a desired region. Find the desired workspace and choose Shortcuts > Data Studio in the Actions column.

   2. In the left-side navigation pane of the Data Studio page, click the icon to go to the DATASTUDIO pane. The Workspace Directories section appears.

   3. Right-click the work workspace directory that you created and choose Create Node > EMR > EMR Hive. In the Create Node dialog box, enter the name of the EMR Hive node and click OK to create the node.

      Go to the configuration tab of the EMR Hive node, replace the data timestamp in the following script with the current data timestamp, and confirm the data write results. View the number of records that are imported into the ods_raw_log_d_emr and ods_user_info_d_emr tables.

      Note

      In the query statement, change the partition key value to the data timestamp of the node. For example, if a node is scheduled to run on February 23, 2025, the data timestamp of the node is 20250222, which is one day earlier than the scheduling time of the node.

      SELECT * FROM ods_raw_log_d_emr WHERE dt=Data timestamp; 

      • If the results returned after you execute the preceding statements show that data exists in the destination, the data process is complete.

      • If the results returned after you execute the preceding statements show that data does not exist in the destination, you must make sure that the values specified for the scheduling parameters defined for the inner nodes of the workflow in this run are the same as the value of the dt field in the preceding statements when you run the workflow. You can click Running History in the right-side navigation pane of the configuration tab of the workflow, and then click View in the Actions column of the running record generated for this run to view the data timestamp that is used when the workflow is run in the run logs of the workflow. The data timestamp is in the partition=[pt=xxx] format.