User profile analysis example: Synchronizing data with Data Studio and MaxCompute - DataWorks

This tutorial provides an example of using a Data Integration batch synchronization task to synchronize basic user information from the ods_user_info_d table in MySQL and website access log data from the user_log.txt file in OSS to the ods_user_info_d and ods_raw_log_d tables in MaxCompute, respectively. The goal is to demonstrate how to use DataWorks Data Integration to synchronize data between heterogeneous data sources and populate a data warehouse.

Prerequisite

Ensure the required work environment is ready. For detailed steps, see Prepare the environment.

1. Create data sources

Create the following data sources in your DataWorks workspace to obtain the raw data for this tutorial.

MySQL data source: In this tutorial, a MySQL data source named user_behavior_analysis_mysql is used to obtain the basic user information (ods_user_info_d) stored in MySQL.
HttpFile data source: In this tutorial, an HttpFile data source named user_behavior_analysis_httpfile is used to obtain user website access logs (user_log.txt) stored in Object Storage Service (OSS).

Note

If you have already created MySQL and HttpFile data sources while working on user profile analysis tutorials for other computing resources, you can skip this step.

Create a MySQL data source ( user_behavior_analysis_mysql)

Create a MySQL data source to synchronize the basic user information (ods_user_info_d) from the MySQL database to MaxCompute.

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.
Click Add Connection, and then search for and select MySQL as the data source type.

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.

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.
Click Complete Creation.

Create an HttpFile data source ( user_behavior_analysis_httpfile)

The user website access log data for this tutorial is stored in OSS. You need to create an HttpFile data source to synchronize the user website access logs (user_log.txt) from OSS to MaxCompute.

On the Management Center page, click Data Source in the left-side navigation pane.
Click Add Connection. In the Add Connection dialog, search for and select Httpfile as the data source type.

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.

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.
Click Complete Creation.

2. Build synchronization pipelines

In this step, you build synchronization pipelines to synchronize user basic information and website access log data to MaxCompute tables for subsequent data processing.

Click the icon in the upper-left corner, and choose All Products > Data Development and O&M > DataStudio. Then switch to the workspace created for this tutorial at the top of the page.
In the left-side navigation pane, click to go to the data development page. In the Project Directory section, click and select Create Workflow. Set the workflow name. For this tutorial, set it to user_profile_analysis.

On the workflow orchestration page, click New Node, and drag a Zero-Load Node and Data Integration nodes onto the canvas on the right. Configure the Data Integration nodes as follows:

Data Source Type: MySQL.
Data destination type: MaxCompute.
Specific Type: Single Table Batch Sync.

Set the node names. The following table lists the example node names and their purposes in this tutorial:

Node type	Node name	Purpose
Virtual node	`workshop_start`	Used to manage the entire user profile analysis workflow, making the data flow path clearer. This node is a Dry-Run Node and does not require any code.
Batch synchronization node	`ods_user_info_d`	Used to synchronize basic user information from MySQL to the `ods_user_info_d` table in MaxCompute.
Batch synchronization node	`ods_raw_log_d`	Used to synchronize user website access logs from OSS to the `ods_raw_log_d` table in MaxCompute.

Manually drag connections to set the workshop_start node as the upstream node of the two batch synchronization nodes. The final result is as follows:

Configure workflow scheduling.

On the right side of the workflow orchestration page, click Scheduling Configuration and configure the parameters. The following table describes the key parameters required for this tutorial. Leave all other parameters at their default values.

Schedule settings parameter	Description
Scheduling Parameters	Set a scheduling parameter for the entire workflow. Internal nodes in the workflow can use it directly. For this tutorial, set it to `bizdate=$[yyyymmdd-1]` to obtain the date of the previous day.
Scheduling period	For this tutorial, set this to `Day`.
Scheduling time	For this tutorial, set Scheduling time to `00:30`. The workflow runs daily at `00:30`.
Node dependency configuration	The workflow has no upstream dependency, so you do not need to configure one. For easier management, you can click Using the Workspace Root Node to mount the workflow under the workspace root node. The workspace root node is named in the format: `workspace name_root`.

3. Configure synchronization tasks

Configure the initial node

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

On the right side of the workshop_start node editing page, click Scheduling Configuration and configure the parameters. The following table describes the key parameters required for this tutorial. Leave all other parameters at their default values.

Schedule settings parameter	Description
Scheduling Type	For this tutorial, set this to `Dry Run`.
Resource Group	For this tutorial, select the serverless resource group created in the Prepare the environment step.
Node dependency configuration	Because `workshop_start` is the initial node and has no upstream dependency, 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`.

Configure the user data synchronization pipeline (ods_user_info_d)

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

Configure the synchronization pipeline network and resources.

Parameter	Description
Data source	Data source: `MySQL`. Data source name: `user_behavior_analysis_mysql`.
My Resource Group	Select the serverless resource group purchased in the Prepare the environment step.
Data going	Data destination: `MaxCompute(ODPS)`. Data source name: Select the MaxCompute compute resource associated in the Prepare the environment step. In this example, it is `MaxCompute_Source`.

Click The next Step to configure the synchronization task.

Configure Source and Destination

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

Section	Parameter	Description
Data source	Table	Select `ods_user_info_d`.
Data source	Shard Key	Set the split key to the `uid` column. The uid column is the primary key of the `ods_user_info_d` table in MySQL.
Data going	Tunnel Resource Group	The default value for this tutorial is Public Transfer Resource. If you have an exclusive Tunnel Quota, you can select it from the drop-down list. For more information about exclusive Tunnel Quota, see Tunnel Quota.
	Table	Click Generate Destination Table Schema to quickly create a MaxCompute table. Paste the following DDL statement into the Table Creation Statement section and click New Table. This table is used to receive user data from the source. `CREATE TABLE IF NOT EXISTS ods_user_info_d ( uid STRING COMMENT 'User ID', gender STRING COMMENT 'Gender', age_range STRING COMMENT 'Age range', zodiac STRING COMMENT 'Zodiac sign' ) PARTITIONED BY ( dt STRING ) LIFECYCLE 7;`
	Partition Information	For this tutorial, enter `${bizdate}`. This is used to assign a constant value to the `bizdate` parameter during debugging, and to dynamically assign values to the `bizdate` parameter during scheduled execution. For more information about variable formats and configuration methods supported by Data Studio, see Scheduling parameters.

Confirm Field Mapping and Channel.
DataWorks maps columns between the source and destination to write data from specified source columns to specified destination columns. It also supports configuring task concurrency and dirty data handling policies. For this tutorial, set Policy for Dirty Data Records to Disallow Dirty Data Records and leave other settings at their defaults. For more information, see Configure channel control.

Configure the debug parameters.

On the right side of the batch synchronization task configuration page, click Run Configuration and configure the following parameters. These parameters are used for debugging in Step 4 with the Run Configuration parameters.

Parameter	Description
Resource Group	Select the serverless resource group purchased in the Prepare the environment step.
Script Parameters	No configuration is needed. The example code in this tutorial uses `${bizdate}` to represent the business date. When debugging the workflow in Step 4, set This operation value to a specific constant (for example, `20250223`). The task will use this constant to replace the variable defined in the task during execution.

(Optional) Configure schedule settings.
For this tutorial, leave the schedule settings at their default values. You can click Scheduling Configuration on the right side of the batch synchronization task configuration page. For detailed descriptions of schedule settings parameters, see Schedule settings.
- Scheduling Parameters: For this tutorial, these have already been configured at the workflow level. Internal nodes in the workflow do not need to configure them separately and can use them directly in tasks or code.
- Scheduling Policy: You can specify the Delayed execution time parameter to delay child node execution after the workflow starts. For this tutorial, leave this unset.
On the node toolbar, click Save.

Configure the user log synchronization pipeline (ods_raw_log_d)

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

Configure the synchronization pipeline network and resources.

Parameter	Description
Data source	Data source: `HttpFile`. Data source name: `user_behavior_analysis_httpfile`.
My Resource Group	Select the serverless resource group purchased in the Prepare the environment step.
Data going	Data destination: `MaxCompute(ODPS)`. Data source name: Select the MaxCompute compute resource associated in the Prepare the environment step. In this example, it is `MaxCompute_Source`.

Click The next Step to configure the synchronization task.

Configure Source and Destination

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

Section	Parameter	Description
Data source	File Path	For this tutorial, enter `/user_log.txt`.
	File Type	For this tutorial, select the `text` type.
	Field Delimiter	For this tutorial, enter `\|`.
	Advanced Configuration > Skip Header	For this tutorial, select `No` to not skip the header.
Important After configuring the data source, click Confirm Data Structure to verify that the log file can be read properly.
Data going	Tunnel Resource Group	The default value for this tutorial is Public Transfer Resource. If you have an exclusive Tunnel Quota, you can select it from the drop-down list. For more information about exclusive Tunnel Quota, see Tunnel Quota.
	Table	Click Generate Destination Table Schema to quickly create a MaxCompute table. Paste the following DDL statement into the Table Creation Statement section and click New Table. This table is used to receive user log data from the source. `CREATE TABLE IF NOT EXISTS ods_raw_log_d ( col STRING ) PARTITIONED BY ( dt STRING ) LIFECYCLE 7;`
	Partition Information	For this tutorial, enter `${bizdate}`. This is used to assign a constant value to the `bizdate` parameter during debugging, and to dynamically assign values to the `bizdate` parameter during scheduled execution. For more information about variable formats and configuration methods supported by Data Studio, see Scheduling parameters.

Confirm Field Mapping and Channel.
DataWorks maps columns between the source and destination to write data from specified source columns to specified destination columns. It also supports configuring task concurrency and dirty data handling policies. For this tutorial, set Policy for Dirty Data Records to Disallow Dirty Data Records and leave other settings at their defaults. For more information, see Configure channel control.

Configure the debug parameters.

Parameter	Description
Resource Group	Select the serverless resource group purchased in the Prepare the environment step.
Script Parameters	No configuration is needed. The example code in this tutorial uses `${bizdate}` to represent the business date. When debugging the workflow in Step 4, set This operation value to a specific constant (for example, `20250223`). The task will use this constant to replace the variable defined in the task during execution.

(Optional) Configure schedule settings.
For this tutorial, leave the schedule settings at their default values. You can click Scheduling Configuration on the right side of the batch synchronization task configuration page. For detailed descriptions of schedule settings parameters, see Schedule settings.
- Scheduling Parameters: For this tutorial, these have already been configured at the workflow level. Internal nodes in the workflow do not need to configure them separately and can use them directly in tasks or code.
- Scheduling Policy: You can specify the Delayed execution time parameter to delay child node execution after the workflow starts. For this tutorial, leave this unset.
On the node toolbar, click Save.

Step 4: Synchronize data

Synchronize data.
On the workflow toolbar, click Run. Set the values for the parameter variables defined in each node for this run (this tutorial uses 20250223; you can modify as needed). Click OK and wait for the run to complete.
Verify the data synchronization results.
1. In the left-side navigation pane of Data Studio, click to go to the data development page. In the personal directory section, click to create a file with the .sql suffix (you can customize the file name).
2. At the bottom of the page, verify that the language mode is set to MaxCompute SQL as shown below.
3. In the node editing window, enter the following SQL statements to check the number of records imported into the ods_raw_log_d and ods_user_info_d tables, confirming that the synchronized data was written to the destination.
```
-- You need to change the partition filter condition to the actual business date of your current operation. In this tutorial, the debug parameter bizdate (business date) configured above is 20250223.
SELECT count(*) FROM ods_user_info_d WHERE dt='business date'; 
SELECT count(*) FROM ods_raw_log_d WHERE dt='business date';
```
  - If the preceding query returns data, the data synchronization is complete.
  - If no data is returned, make sure that the This operation value configured when running the workflow matches the business date specified in dt in the query. You can click the workflow, click Runtime Logs on the right side, click View in the Operation column of the run history, and then check the business date value (partition=[pt=xxx]) in the workflow runtime log.

Next step

After completing the data synchronization configuration, you can proceed to the next tutorial to learn how to process and analyze the synchronized data. For details, see Process data.