DataWorks:Synchronize data

Create the MySQL data source

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

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

3. Click Add Data Source, then search for and select MySQL.

4. On the Add MySQL Data Source page, configure the following parameters. Use the same values for both the development and production environments.

   Parameter	Value
   Data source name	user_behavior_analysis_mysql
   Data source description	This data source is for DataWorks tutorials. Read data from this data source when you configure a Batch Synchronization task to access the test data provided by the platform. This data source can only be read in Data Integration scenarios. Other modules cannot use it.
   Configuration mode	Connection String Mode
   Connection address (host)	rm-bp1z69dodhh85z9qa.mysql.rds.aliyuncs.com
   Connection address (port)	3306
   Database name	workshop
   Username	workshop
   Password	workshop#2017
   Authentication method	No authentication

5. In the Connection Configuration section, click Test Network Connectivity for both environments and confirm the status shows Connected.

   Important

   Make sure the resource group is attached to your workspace and has public network access enabled. If no resource group is available, click Purchase and then Associated Purchased Resource Group in the connection configuration section. For details, see Prepare the environment.

6. Click Complete Creation.

Create the HttpFile data source

The website access logs are stored in OSS. Create an HttpFile data source to let the Batch Synchronization task read the log file.

1. In the left-side navigation pane, click Data Sources.

2. Click Add Data Source, then search for and select HttpFile.

3. On the Add HttpFile Data Source page, configure the following parameters. Use the same values for both environments.

   Parameter	Value
   Data source name	user_behavior_analysis_httpfile
   Data source description	This data source is for DataWorks tutorials. Read data from this data source when you configure a Batch Synchronization task to access the test data provided by the platform. This data source can only be read in Data Integration scenarios. Other modules cannot use it.
   URL	https://dataworks-workshop-2024.oss-cn-shanghai.aliyuncs.com

4. In the Connection Configuration section, click Test Network Connectivity for both environments and confirm the status shows Connected.

   Important

   Make sure the resource group is attached to your workspace and has public network access enabled. If no resource group is available, click Purchase and then Associated Purchased Resource Group in the connection configuration section. For details, see Prepare the environment.

5. Click Complete Creation.

Create a Workflow

1. Click the icon in the upper-left corner and select All Products > Data Development And Task Operation > DataStudio. Switch to the workspace created for this tutorial.

2. In the left-side navigation pane, click the icon. In the Workspace Directories section, click the icon, select Create Workflow, and name it user_profile_analysis_starrocks.

3. On the Workflow canvas, add the following nodes. Find each component in the left-side pane and drag it onto the canvas. When prompted for source and destination types, set Source Type to MySQL, Destination Type to OSS, and Specific Type to Batch Synchronization.

   Node type	Node name	Role
   Zero load node	workshop_start_starrocks	Ancestor node that manages the Workflow and clarifies the data forwarding path. Configured as a Dry-run node; no code editing required.
   StarRocks node	ddl_ods_user_info_d_starrocks	Creates the StarRocks table ods_user_info_d_starrocks for user data.
   StarRocks node	ddl_ods_raw_log_d_starrocks	Creates the StarRocks table ods_raw_log_d_starrocks for website access logs.
   Batch synchronization node	ods_user_info_d_starrocks	Syncs user data from MySQL into ods_user_info_d_starrocks.
   Batch synchronization node	ods_raw_log_d_starrocks	Syncs website access logs from OSS into ods_raw_log_d_starrocks.

4. Connect the nodes so that workshop_start_starrocks is the ancestor node for both Batch Synchronization nodes. The completed Workflow should look like this:

   

Configure Workflow scheduling

On the Workflow canvas, click Scheduling in the right-side pane and set the following parameters.

Configure the initial node

1. On the Workflow canvas, hover over workshop_start_starrocks and click Open Node.

2. Click Properties in the right-side pane and configure the following parameters.

   Parameter	Value
   Scheduling type	Dry-run
   Resource group for scheduling	Select the serverless resource group from Prepare the environment.
   Scheduling dependencies	Click Use Workspace Root Node so the workspace root node triggers this Workflow. The root node follows the format WorkspaceName_root.

Create the user table (ddl_ods_user_info_d_starrocks)

Create the ods_user_info_d_starrocks table in StarRocks to receive user data from the MySQL data source. You can also create the table manually in the Data Catalog.

1. On the Workflow canvas, hover over ddl_ods_user_info_d_starrocks and click Open Node.

2. Enter the following table creation 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 profile analysis use case - User information table'
   PARTITION BY(dt)
   PROPERTIES("replication_num" = "1");

3. Click Run Configuration in the right-side pane and set the following parameters for the test run.

   Parameter	Value
   Computing resource	Select the StarRocks compute resource bound in Prepare the environment.
   Resource group	Select the serverless resource group from Prepare the environment.

4. (Optional) Click Properties to review scheduling settings. Scheduling parameters are already configured at the Workflow level, so no node-level changes are needed. For details, see Configure node scheduling properties.

5. In the top toolbar, click Save.

Create the log table (ddl_ods_raw_log_d_starrocks)

Create the ods_raw_log_d_starrocks table in StarRocks to receive website access logs from the HttpFile data source. You can also create the table manually in the Data Catalog.

1. On the Workflow canvas, hover over ddl_ods_raw_log_d_starrocks and click Open Node.

2. Enter the following table creation 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 profile analysis use case - Raw website access log table'
   PARTITION BY(dt)
   PROPERTIES ("replication_num" = "1");

3. Click Run Configuration in the right-side pane and set the following parameters for the test run.

   Parameter	Value
   Computing resource	Select the StarRocks compute resource bound in Prepare the environment.
   Resource group	Select the serverless resource group from Prepare the environment.

4. (Optional) Click Properties to review scheduling settings. Scheduling parameters are already configured at the Workflow level. For details, see Configure node scheduling properties.

5. In the top toolbar, click Save.

Configure the user data sync task (ods_user_info_d_starrocks)

1. On the Workflow canvas, hover over ods_user_info_d_starrocks and click Open Node.

2. Set the source, resource group, and destination:

   Parameter	Value
   Source	MySQL — data source user_behavior_analysis_mysql
   Resource group	Select the serverless resource group from Prepare the environment.
   Destination	StarRocks — data source doc_starrocks_storage_compute_tightly_01

3. Configure the source and destination settings:

   Module	Parameter	Value
   Source	Table	ods_user_info_d
   Source	Split key	uid — use a primary key or indexed integer column as the split key. Only integer-type fields are supported.
   Destination	Table	ods_user_info_d_starrocks
   Destination	Statement run before writing	ALTER TABLE ods_user_info_d_starrocks DROP PARTITION IF EXISTS p${var} FORCE — drops the target partition before each sync to prevent duplicate data. ${var} is replaced by the scheduling parameter at runtime. For more information, see Configure scheduling.
   Destination	StreamLoad request parameters	{"row_delimiter": "\\x02", "column_separator": "\\x01"}

4. Configure field mapping:

   1. Click Map Fields with Same Name to automatically map matching source and destination fields.

   2. Click Add and enter '${var}'. Map this field to the dt column in StarRocks. The scheduling parameter dynamically assigns the partition date at runtime.

5. In Channel Control, set Policy for dirty data to Disallow Dirty Data. For more information, see Configure a task in wizard mode.

6. Click Run Configuration in the right-side pane and set the following parameters for the test run.

   Parameter	Value
   Resource group	Select the serverless resource group from Prepare the environment.
   Script parameters	Click Add Parameter and enter var=20250223. DataStudio replaces ${var} with this value during the test run.

7. (Optional) Click Properties to review scheduling settings. Scheduling parameters are inherited from the Workflow level. For details, see Configure node scheduling properties.

8. In the top toolbar, click Save.

Configure the log data sync task (ods_raw_log_d_starrocks)

The HttpFile data source requires Script Mode to configure field mapping with a dynamic partition variable. Set up the basic configuration in Wizard Mode first, then switch to Script Mode for the field mapping.

1. On the Workflow canvas, hover over ods_raw_log_d_starrocks and click Open Node.

2. Set the source, resource group, and destination, then click Next to complete the connectivity test.

   Parameter	Value
   Source	HttpFile — data source user_behavior_analysis_httpfile
   Resource group	Select the serverless resource group from Prepare the environment.
   Destination	StarRocks — data source doc_starrocks_storage_compute_tightly_01

3. Click Next and configure the source and destination settings:

   Module	Parameter	Value
   Source	File path	/user_log.txt
   Source	Text type	text
   Source	Column delimiter	|
   Source	Advanced configuration > Skip header	No
   Source	—	After configuring the source, click Confirm Data Structure.
   Destination	Table	ods_raw_log_d_starrocks
   Destination	Statement run before writing	ALTER TABLE ods_raw_log_d_starrocks DROP PARTITION IF EXISTS p${var} FORCE — drops the target partition before each sync to prevent duplicate data. For more information, see Configure scheduling.
   Destination	StreamLoad request parameters	{"row_delimiter": "\\x02", "column_separator": "\\x01"}

4. In Channel Control, set Policy for dirty data to Disallow Dirty Data. For more information, see Configure a task in wizard mode.

5. Configure field mapping in Script Mode. The HttpFile data source does not support dynamic variables in Wizard Mode. Switch to Script Mode by clicking the icon in the toolbar. In the source HttpFile configuration, add the following object to the column array to pass the scheduling parameter as the dt partition value:

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

   The complete script for this node is:

   {
       "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_httpfile",
                   "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"
               }
           ]
       }
   }

6. Click Run Configuration in the right-side pane and set the following parameters for the test run.

   Parameter	Value
   Resource group	Select the serverless resource group from Prepare the environment.
   Script parameters	Click Add Parameter and enter var=20250223. DataStudio replaces ${var} with this value during the test run.

7. (Optional) Click Properties to review scheduling settings. Scheduling parameters are inherited from the Workflow level. For details, see Configure node scheduling properties.

8. In the top toolbar, click Save.

Run the Workflow

In the Workflow toolbar, click Run. When prompted, set the parameter variable for this run — this tutorial uses 20250223, but adjust the value as needed. Click OK and wait for all nodes to complete.

Verify the results

1. Log on to the DataWorks console. In the left-side navigation pane, choose Data Analysis and Service > DataAnalysis, then click Go to DataAnalysis. In the left navigation pane, click SQL Query.

2. Click the icon next to My Files to create a new SQL query file and enter a name.

3. Open the file, then click the icon in the upper-right corner to configure the query settings:

   Parameter	Value
   Workspace	Select the workspace containing the user_profile_analysis_starrocks Workflow.
   Data source type	StarRocks
   Data source name	Select the StarRocks development environment bound in Prepare the environment.

4. Click OK to apply the query data source.

5. Run the following queries to confirm that data was written to both StarRocks tables. Replace your_business_date with the Data Timestamp for your run — for a task run on 20250223, the Data Timestamp is 20250222 (the previous day).

   SELECT * FROM ods_raw_log_d_starrocks WHERE dt=your_business_date;
   SELECT * FROM ods_user_info_d_starrocks WHERE dt=your_business_date;