DataWorks:Synchronize data

Add MySQL data source ( user_behavior_analysis_mysql)

Basic user information is stored in a MySQL database. Create a MySQL data source to synchronize ods_user_info_d to StarRocks.

1. Go to the Data Sources page.

   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. In the left-side navigation pane, click Data Source.

2. Click Add Connection, and then search for and select MySQL as the data source type.

3. On the Create Mysql Data Source page, configure the parameters. For this tutorial, use the same example values for both the development and production environment.

   The following table describes the key parameters required for this tutorial. Leave all other parameters at their default values.

   Parameter	Description
   Data Source Name	Enter a data source name. For this tutorial, enter user_behavior_analysis_mysql.
   Description	This is a data source dedicated to DataWorks tutorial experiences. You can read test data provided by the platform when configuring a batch synchronization task. This data source supports read operations only in Data Integration scenarios and cannot be used in other modules.
   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 the database name. For this tutorial, enter workshop.
   Username	Enter the username. For this tutorial, enter workshop.
   Password	Enter the password. For this tutorial, enter workshop#2017.
   Authentication Method	No authentication.

4. In the Connection Configuration section, click Test Connectivity for both the production and development environment, and make sure that the connectivity status is Connectable.

   Important

   • Make sure that the resource group is associated with the workspace and has public network access configured. Otherwise, errors will occur during subsequent data synchronization. For configuration steps, see Prepare the environment.

   • If no resource group is available, follow the instructions in the connection configuration section, and click Go to Buy and Associate Purchased Resource Group.

5. Click Complete Creation.

Add HttpFile data source ( user_behavior_analysis_httpfile)

User website access log data is stored in OSS. Create an HttpFile data source to synchronize user_log.txt from OSS to StarRocks.

1. On the Management Center page, click Data Source in the left-side navigation pane.

2. Click Add Connection. In the Add Connection dialog, search for and select Httpfile as the data source type.

3. On the Create Httpfile Data Source page, configure the parameters. For this tutorial, use the same example values for both the development and production environment.

   The following table describes the key parameters required for this tutorial. Leave all other parameters at their default values.

   Parameter	Description
   Data Source Name	Enter a data source name. For this tutorial, enter user_behavior_analysis_httpfile.
   Description	This is a data source dedicated to DataWorks tutorial experiences. You can read test data provided by the platform when configuring a batch synchronization task. This data source supports read operations only in Data Integration scenarios and cannot be used in other modules.
   URL	Set the URL to https://dataworks-workshop-2024.oss-cn-shanghai.aliyuncs.com for both the development and production environment.

4. In the Connection Configuration section, click Test Connectivity for both the production and development environment, and make sure that the connectivity status is Connectable.

   Important

   • Make sure that the resource group is associated with the workspace and has public network access configured. Otherwise, errors will occur during subsequent data synchronization. For configuration steps, see Prepare the environment.

   • If no resource group is available, follow the instructions in the connection configuration section, and click Go to Buy and Associate Purchased Resource Group.

5. Click Complete Creation.

Configure the initial node

1. On the workflow orchestration page, hover over the workshop_start_starrocks node and click Open Node.

2. On the right side of the workshop_start_starrocks node editing page, click Scheduling Configuration and configure the relevant parameters. The following are the key parameters required for this tutorial. Retain default values for parameters not mentioned.

   Schedule settings parameter	Description
   Scheduling Type	In this tutorial, set to dry run.
   Scheduling Resource Groups	In this tutorial, select the serverless resource group created during the Prepare the environment step.
   Node dependency configuration	Because workshop_start_starrocks is the initial node without upstream dependencies, you can click Using the Workspace Root Node to trigger the workflow execution from the workspace root node. The workspace root node is named workspace name_root.

Create the user table ddl_ods_user_info_d_starrocks

Create the ddl_ods_user_info_d_starrocks table to receive basic user information from the MySQL data source before synchronization. You can create the table within the node as follows, or manually by using Data Catalog Management.

1. On the workflow orchestration page, hover over the ddl_ods_user_info_d_starrocks node and click Open Node.

2. Edit the CREATE TABLE statement.

   CREATE TABLE IF NOT EXISTS ods_user_info_d_starrocks (
       uid STRING COMMENT 'User ID',
       gender STRING COMMENT 'Gender',
       age_range STRING COMMENT 'Age range',
       zodiac STRING COMMENT 'Zodiac sign',
       dt STRING not null COMMENT 'Date'
   ) 
   DUPLICATE KEY(uid)
   COMMENT 'User behavior analysis case - user basic information table'
   PARTITION BY(dt) 
   PROPERTIES("replication_num" = "1");

3. Configure debug parameters.

   On the right side of the StarRocks node configuration page, click Run Configuration and configure the following parameters. These parameters are used for test runs in Step 4 with the Run Configuration settings.

   Parameter	Description
   Computing Resources	Select the StarRocks compute resource associated during the Prepare the environment step.
   Resource Group	Select the serverless resource group purchased during the Prepare the environment step.

4. (Optional) Configure schedule settings.

   For this tutorial, retain the default values for schedule settings. You can click Scheduling Configuration on the right side of the node editing page. For more information about the schedule settings parameters, see Configure node scheduling.

   • Scheduling Parameters: In this tutorial, scheduling parameters are configured uniformly in the workflow schedule settings. Nodes within the workflow do not require separate configuration and can directly use the parameters in tasks or code.

   • Scheduling Policy: You can use the Delayed execution time parameter to specify how long a child node waits before running after the workflow starts execution. This tutorial does not configure this parameter.

5. Click Save on the top toolbar to save the current node.

Create the log table ddl_ods_raw_log_d_starrocks

Create the ddl_ods_raw_log_d_starrocks table to receive user website access log data from the HttpFile data source before synchronization. You can create the table within the node as follows, or manually by using Data Catalog Management.

1. On the workflow orchestration page, hover over the ddl_ods_raw_log_d_starrocks node and click Open Node.

2. Edit the CREATE TABLE statement.

   CREATE TABLE IF NOT EXISTS ods_raw_log_d_starrocks (
       col STRING COMMENT 'Log',
       dt DATE  not null COMMENT 'Date'
   ) DUPLICATE KEY(col) 
   COMMENT 'User behavior analysis case - website access log raw data table' 
   PARTITION BY(dt) 
   PROPERTIES ("replication_num" = "1");

3. Configure debug parameters.

   On the right side of the single-table batch synchronization task configuration page, click Run Configuration and configure the following parameters. These parameters are used for test runs in Step 4 with the Run Configuration settings.

   Parameter	Description
   Computing Resources	Select the StarRocks compute resource associated during the Prepare the environment step.
   Resource Group	Select the serverless resource group purchased during the Prepare the environment step.

4. (Optional) Configure schedule settings.

   For this tutorial, retain the default values for schedule settings. You can click Scheduling Configuration on the right side of the node editing page. For more information about the schedule settings parameters, see Configure node scheduling.

   • Scheduling Parameters: In this tutorial, scheduling parameters are configured uniformly in the workflow schedule settings. Nodes within the workflow do not require separate configuration and can directly use the parameters in tasks or code.

   • Scheduling Policy: You can use the Delayed execution time parameter to specify how long a child node waits before running after the workflow starts execution. This tutorial does not configure this parameter.

5. Click Save on the top toolbar to save the current node.

Configure the user data synchronization pipeline (ods_user_info_d_starrocks)

1. On the workflow orchestration page, hover over the ods_user_info_d_starrocks node and click Open Node.

2. Configure the network and resources for the synchronization pipeline.

   Parameter	Description
   Data source	Data source: MySQL Data Source Name: user_behavior_analysis_mysql
   My Resource Group	Select the serverless resource group created during the Prepare the environment step.
   Data going	Data going: StarRocks Data Source Name: doc_starrocks_storage_compute_tightly_01

3. Configure the task.

   • Configure Source and Destination.

     Section	Parameter	Configuration
     Data source	Table	Select the MySQL table ods_user_info_d.
     	Shard Key	Use a primary key or an indexed column as the split key. Only integer columns are supported. In this tutorial, set the split key to the uid column.
     Data going	Table	Select the StarRocks table ods_user_info_d_starrocks.
     	Statement Run Before Writing	Data is dynamically partitioned by the dt column. To avoid duplicate data from node reruns, the following SQL statement drops the existing target partition before each synchronization. ALTER TABLE ods_user_info_d_starrocks DROP PARTITION IF EXISTS p${var} FORCE In this statement, ${var} is a parameter that will be assigned a scheduling parameter value during the schedule settings stage to enable dynamic input in scheduling scenarios. For more information, see Schedule settings.
     	Streamload Request Parameters	Request parameters for StreamLoad in JSON format. { "row_delimiter": "\\x02", "column_separator": "\\x01" }

   • Field Mapping.

     Define the mapping between source and destination columns, and use variable assignment with scheduling parameters to dynamically assign values to the StarRocks partition column. Daily data is then written to the corresponding business partition in StarRocks.

     1. Click Map Fields with the Same Name. Source MySQL columns are automatically mapped to destination columns with the same name. By default, source column data is written to the destination column with the same name.

     2. Click Add Row, enter '${var}', and manually map this column to the dt column in StarRocks.

   • Channel.

     In this tutorial, set Policy for Dirty Data Records to Disallow Dirty Data Records, and retain the default values for other settings. For more information, see Codeless UI configuration.

4. Configure debug parameters.

   On the right side of the single-table batch synchronization task configuration page, click Run Configuration and configure the following parameters. These parameters are used for test runs in Step 4 with the Run Configuration settings.

   Parameter	Description
   Resource Group	Select the serverless resource group purchased during the Prepare the environment step.
   Script Parameters	Click Add parameter and set the value to var=a specific constant in yyyymmdd format (for example, var=20250223). During debugging, Data Studio uses this constant to replace the variable defined in the task.

5. (Optional) Configure schedule settings.

   For this tutorial, retain the default values for schedule settings. You can click Scheduling Configuration on the right side of the node editing page. For more information about the schedule settings parameters, see Configure node scheduling.

   • Scheduling Parameters: In this tutorial, scheduling parameters are configured uniformly in the workflow schedule settings. Nodes within the workflow do not require separate configuration and can directly use the parameters in tasks or code.

   • Scheduling Policy: You can use the Delayed execution time parameter to specify how long a child node waits before running after the workflow starts execution. This tutorial does not configure this parameter.

6. Click Save on the top toolbar to save the current node.

Configure the log data synchronization pipeline (ods_raw_log_d_starrocks)

1. On the workflow orchestration page, hover over the ods_raw_log_d_starrocks node and click Open Node.

2. Configure the network and resources for the synchronization pipeline.

   After configuring Data source, My Resource Group, and Data going, click The next Step and complete the network connectivity test as prompted. The detailed configuration is as follows.

   Parameter	Configuration
   Data source	Data source: HttpFile Data Source Name: user_behavior_analysis_HttpFile
   My Resource Group	Select the serverless resource group created during the Prepare the environment step.
   Data going	Data going: StarRocks Data Source Name: doc_starrocks_storage_compute_tightly_01

3. Click The next Step and configure the synchronization task.

   • Configure Source and Destination.

     Section	Parameter	Configuration
     Data source	File Path	/user_log.txt
     	File Type	text
     	Field Delimiter	|
     	Advanced Settings > Skip Header	No
     	After completing the source configuration, click Confirm Data Structure.
     Data going	Table	Select the StarRocks table ods_raw_log_d_starrocks.
     	Statement Run Before Writing	Data is dynamically partitioned by the dt column. To avoid duplicate data from node reruns, the following SQL statement drops the existing target partition before each synchronization. ALTER TABLE ods_user_info_d_starrocks DROP PARTITION IF EXISTS p${var} FORCE In this statement, ${var} is a parameter that will be assigned a scheduling parameter value during the schedule settings stage to enable dynamic input in scheduling scenarios. For more information, see Schedule settings.
     	Streamload Request Parameters	Request parameters for StreamLoad in JSON format. { "row_delimiter": "\\x02", "column_separator": "\\x01" }

   • Channel.

     In this tutorial, set Policy for Dirty Data Records to Disallow Dirty Data Records, and retain the default values for other settings. For more information, see Codeless UI configuration.

   • Field Mapping.

     Click the button on the node page toolbar to switch the task configuration mode from Wizard Mode to Script Mode. Complete the column mapping for the HttpFile data source and the dynamic value assignment for the StarRocks partition column dt.

     • Add the following to the source HttpFile Column configuration:

        {
                  "type": "STRING",
                 "value": "${var}"
               }

     • Complete script example for the ods_raw_log_d_starrocks node:

       {
           "type": "job",
           "version": "2.0",
           "steps": [
               {
                   "stepType": "httpfile",
                   "parameter": {
                       "fileName": "/user_log.txt",
                       "nullFormat": "",
                       "compress": "",
                       "requestMethod": "GET",
                       "connectTimeoutSeconds": 60,
                       "column": [
                           {
                               "index": 0,
                               "type": "STRING"
                           },
                           {
                               "type": "STRING",
                               "value": "${var}"
                           }
                       ],
                       "skipHeader": "false",
                       "encoding": "UTF-8",
                       "fieldDelimiter": "|",
                       "fieldDelimiterOrigin": "|",
                       "socketTimeoutSeconds": 3600,
                       "envType": 0,
                       "datasource": "user_behavior_analysis",
                       "bufferByteSizeInKB": 1024,
                       "fileFormat": "text"
                   },
                   "name": "Reader",
                   "category": "reader"
               },
               {
                   "stepType": "starrocks",
                   "parameter": {
                       "loadProps": {
                           "row_delimiter": "\\x02",
                           "column_separator": "\\x01"
                       },
                       "envType": 0,
                       "datasource": "Doc_StarRocks_Storage_Compute_Tightly_01",
                       "column": [
                           "col",
                           "dt"
                       ],
                       "tableComment": "",
                       "table": "ods_raw_log_d_starrocks",
                       "preSql": "ALTER TABLE ods_raw_log_d_starrocks DROP PARTITION IF EXISTS  p${var} FORCE ; "
                   },
                   "name": "Writer",
                   "category": "writer"
               },
               {
                   "copies": 1,
                   "parameter": {
                       "nodes": [],
                       "edges": [],
                       "groups": [],
                       "version": "2.0"
                   },
                   "name": "Processor",
                   "category": "processor"
               }
           ],
           "setting": {
               "errorLimit": {
                   "record": "0"
               },
               "locale": "zh",
               "speed": {
                   "throttle": false,
                   "concurrent": 2
               }
           },
           "order": {
               "hops": [
                   {
                       "from": "Reader",
                       "to": "Writer"
                   }
               ]
           }
       }

4. Configure debug parameters.

   On the right side of the single-table batch synchronization task configuration page, click Run Configuration and configure the following parameters. These parameters are used for test runs in Step 4 with the Run Configuration settings.

   Parameter	Description
   Resource Group	Select the serverless resource group purchased during the Prepare the environment step.
   Script Parameters	Click Add parameter and set the value to var=a specific constant in yyyymmdd format (for example, var=20250223). During debugging, Data Studio uses this constant to replace the variable defined in the task.

5. (Optional) Configure schedule settings.

   For this tutorial, retain the default values for schedule settings. You can click Scheduling Configuration on the right side of the node editing page. For more information about the schedule settings parameters, see Configure node scheduling.

   • Scheduling Parameters: In this tutorial, scheduling parameters are configured uniformly in the workflow schedule settings. Nodes within the workflow do not require separate configuration and can directly use the parameters in tasks or code.

   • Scheduling Policy: You can use the Delayed execution time parameter to specify how long a child node waits before running after the workflow starts execution. This tutorial does not configure this parameter.

6. Click Save on the top toolbar to save the current node.