Process data for user profiling analysis - based on the new Data Studio and MaxCompute compute resources - DataWorks

This topic explains how to use MaxCompute nodes in DataWorks to process data from the user information table ods_user_info_d and the access log table ods_raw_log_d to generate user profile data. You will learn to use DataWorks and MaxCompute to compute and analyze synchronized data, completing a simple data processing task in a data warehouse.

Prerequisite

Before you begin, make sure you have completed the steps in Synchronize data.

1. Build the data processing pipeline

In the Synchronize data step, data was synchronized to MaxCompute. You must now process this data to generate basic user profile data.

In the left-side navigation pane of Data Studio, click the icon to open the Data Development page. In the Project Directory section, find the workflow you created and click it to open the workflow canvas.

The following table describes the nodes used in this tutorial.

Node type	Node name	Node function
MaxCompute SQL	`dwd_log_info_di`	Splits raw log data from the `ods_raw_log_d` table into multiple columns in the `dwd_log_info_di` table using built-in functions and a user-defined function (UDF) named `getregion`.
MaxCompute SQL	`dws_user_info_all_di`	Aggregates data from the user information table (`ods_user_info_d`) and the processed log table (`dwd_log_info_di`), and then writes the results to the `dws_user_info_all_di` table.
MaxCompute SQL	`ads_user_info_1d`	Further processes data from the `dws_user_info_all_di` table and writes the results to the `ads_user_info_1d` table to generate basic user profiles.

Manually drag to draw lines between nodes to configure upstream nodes. The final result is as follows:

Note
You can set upstream and downstream dependencies between nodes by manually drawing lines in the workflow. You can also use code parsing in child nodes to automatically identify node dependencies. This tutorial uses the manual approach. For more information about code parsing, see Automatic parsing mechanism.

2. Register a UDF

To ensure that subsequent data processing tasks run properly, you must register a MaxCompute UDF (getregion) to parse the log data structure synchronized to MaxCompute in the Synchronize data step into a table.

Important

This tutorial provides the resource required by the function that resolves IP addresses to regions. You only need to download the resource to your computer and upload it to the DataWorks workspace before you register the function.
This function is intended only for this tutorial (sample IP resource). To implement IP-to-region mapping in a production environment, obtain a professional IP lookup service from a dedicated provider.

Upload the resource (ip2region.jar)

Download ip2region.jar.

Note
The ip2region.jar resource is a sample intended only for this tutorial.
In the left-side navigation pane of Data Studio, click to go to the resource management page. Click > New Resource > Maxcompute Jar, specify the resource name, and then go to the resource upload page.

Note
The resource name does not need to match the uploaded file name.
Set Document Source to Local, click Click Upload next to the file content field, and then select the ip2region.jar file that you downloaded.
Set Data Source to the MaxCompute compute resource that you associated in the Prepare the environment step.
On the node toolbar, click Save, and then click Publish. Follow the instructions in the deployment panel to deploy the resource to the MaxCompute projects in the development environment and the production environment.

Register a function (getregion)

On the resource management page, click > New Function > Maxcompute Function, specify the function name, and then go to the function registration page. In this tutorial, the function is named getregion.

On the Register Function page, configure the required parameters. The following table describes only the key parameters for this tutorial. Retain the default values for parameters that are not listed.

Parameter	Description
Function type	Select `OTHER`.
Data Source	Select the MaxCompute compute resource that you associated in the Prepare the environment step.
Class Name	Enter `org.alidata.odps.udf.Ip2Region`.
Resource List	Select `ip2region.jar`.
Description	Converts an IP address to a region.
Command Format	Enter `getregion('ip')`.
Parameter Description	An IP address.

On the node toolbar, click Save, and then click Publish. Follow the instructions in the deployment panel to deploy the function to the MaxCompute projects in the development environment and the production environment.

3. Configure data processing nodes

Data processing requires implementing the processing logic for each layer through MaxCompute SQL scheduling. This tutorial provides complete sample SQL code for data processing. You must configure the following nodes in sequence: dwd_log_info_di, dws_user_info_all_di, and ads_user_info_1d.

Configure the dwd_log_info_di node

The sample code for this node uses the function you created to process columns from the upstream table ods_raw_log_d and writes the results to the dwd_log_info_di table.

In the left-side navigation pane of Data Studio, click the icon to open the Data Development page. In the Project Directory section, find the workflow you created and click it to open the workflow canvas.
On the workflow canvas, hover over the dwd_log_info_di node and click Open Node.

Paste the following code into the node editor.

Sample code for the dwd_log_info_di node

-- Create table dwd_log_info_di
CREATE TABLE IF NOT EXISTS dwd_log_info_di (
 ip STRING COMMENT 'IP address',
 uid STRING COMMENT 'User ID',
 time STRING COMMENT 'Time yyyymmddhh:mi:ss',
 status STRING COMMENT 'Server response status code',
 bytes STRING COMMENT 'Bytes returned to client',
 region STRING COMMENT 'Region, derived from IP',
 method STRING COMMENT 'HTTP request method',
 url STRING COMMENT 'url',
 protocol STRING COMMENT 'HTTP protocol version',
 referer STRING COMMENT 'Referrer URL',
 device STRING COMMENT 'Device type ',
 identity STRING COMMENT 'Access type: crawler feed user unknown'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;

-- Data processing
-- Scenario: The following SQL uses the getregion function to parse IP addresses from the raw log data, and splits the raw data into analyzable fields using regex and other methods, then writes the results into the dwd_log_info_di table.
--      This tutorial has prepared the getregion function for parsing IPs into regions.
-- Notes:
--     1. Before using a function in a DataWorks node, you need to upload the resource required for function registration to DataWorks, and then register the function using the visual interface.
--        The resource used to register the getregion function in this tutorial is ip2region.jar.
--     2. DataWorks provides scheduling parameters to write daily incremental data into the corresponding business partition of the target table in scheduling scenarios.
--        In actual development, you can define code variables in the ${variable_name} format, and assign scheduling parameters to the variables on the scheduling configuration page to enable dynamic parameter passing in scheduling scenarios.
INSERT OVERWRITE TABLE dwd_log_info_di PARTITION (dt='${bizdate}')
SELECT ip 
  , uid
  , time
  , status
  , bytes 
  , getregion(ip) AS region --Use the custom UDF to derive the region from the IP address.
  , regexp_substr(request, '(^[^ ]+ )') AS method --Split the request into 3 fields using regex.
  , regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') AS url
  , regexp_substr(request, '([^ ]+$)') AS protocol 
  , regexp_extract(referer, '^[^/]+://([^/]+){1}') AS referer --Clean the referer using regex to get a more precise URL.
  , CASE
    WHEN TOLOWER(agent) RLIKE 'android' THEN 'android' --Derive device type and access type from agent.
    WHEN TOLOWER(agent) RLIKE 'iphone' THEN 'iphone'
    WHEN TOLOWER(agent) RLIKE 'ipad' THEN 'ipad'
    WHEN TOLOWER(agent) RLIKE 'macintosh' THEN 'macintosh'
    WHEN TOLOWER(agent) RLIKE 'windows phone' THEN 'windows_phone'
    WHEN TOLOWER(agent) RLIKE 'windows' THEN 'windows_pc'
    ELSE 'unknown'
  END AS device
  , CASE
    WHEN TOLOWER(agent) RLIKE '(bot|spider|crawler|slurp)' THEN 'crawler'
    WHEN TOLOWER(agent) RLIKE 'feed'
    OR regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') RLIKE 'feed' THEN 'feed'
    WHEN TOLOWER(agent) NOT RLIKE '(bot|spider|crawler|feed|slurp)'
    AND agent RLIKE '^[Mozilla|Opera]'
    AND regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') NOT RLIKE 'feed' THEN 'user'
    ELSE 'unknown'
  END AS identity
  FROM (
    SELECT SPLIT(col, '##@@')[0] AS ip
    , SPLIT(col, '##@@')[1] AS uid
    , SPLIT(col, '##@@')[2] AS time
    , SPLIT(col, '##@@')[3] AS request
    , SPLIT(col, '##@@')[4] AS status
    , SPLIT(col, '##@@')[5] AS bytes
    , SPLIT(col, '##@@')[6] AS referer
    , SPLIT(col, '##@@')[7] AS agent
  FROM ods_raw_log_d  
  WHERE dt ='${bizdate}'
) a;

Configure the debug parameters.

On the right side of the MaxCompute SQL node editor, click Run Configuration and configure the following parameters. These parameters are used during the debug run in Step 4 to test the task with the Run Configuration parameters.

Configuration item	Description
Computing Resources	Select the MaxCompute compute resource that you associated in the Prepare the environment step and its corresponding compute quota.
Resource Group	Select the serverless resource group that you purchased in the Prepare the environment step.
Script Parameters	No configuration is required. The sample code in this tutorial uses `${bizdate}` to represent the business date. When you debug 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 code.

(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 MaxCompute SQL page. For more information about schedule settings parameters, see Schedule settings.
- Scheduling Parameters: This tutorial has configured scheduling parameters at the workflow level. No additional configuration is required for individual nodes within the workflow. You can use these parameters directly in tasks or code.
- Scheduling Policy: You can use the Delayed execution time parameter to specify how long a child node should wait before running after the workflow starts running. This tutorial does not set this parameter.
On the node toolbar, click Save.

Configure the dws_user_info_all_di node

This node aggregates data from the user information table (ods_user_info_d) and the processed log table (dwd_log_info_di), and writes the results to the dws_user_info_all_di table.

On the workflow canvas, hover over the dws_user_info_all_di node and click Open Node.

Paste the following code into the node editor.

Sample code for the dws_user_info_all_di node

-- Create table dws_user_info_all_di
CREATE TABLE IF NOT EXISTS dws_user_info_all_di (
 uid STRING COMMENT 'User ID',
 gender STRING COMMENT 'Gender',
 age_range STRING COMMENT 'Age range',
 zodiac STRING COMMENT 'Zodiac sign',
 region STRING COMMENT 'Region, derived from IP',
 device STRING COMMENT 'Device type ',
 identity STRING COMMENT 'Access type: crawler feed user unknown',
 method STRING COMMENT 'HTTP request method',
 url STRING COMMENT 'url',
 referer STRING COMMENT 'Referrer URL',
 time STRING COMMENT 'Time yyyymmddhh:mi:ss'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;

-- Data processing
-- Scenario: Join the processed log data dwd_log_info_di with the user basic information data ods_user_info_d and write the results into the dws_user_info_all_di table.
-- Notes: DataWorks provides scheduling parameters to write daily incremental data into the corresponding business partition of the target table in scheduling scenarios.
--      In actual development, you can define code variables in the ${variable_name} format, and assign scheduling parameters to the variables on the scheduling configuration page to enable dynamic parameter passing in scheduling scenarios.
INSERT OVERWRITE TABLE dws_user_info_all_di  PARTITION (dt='${bizdate}')
SELECT COALESCE(a.uid, b.uid) AS uid
  , b.gender
  , b.age_range
  , b.zodiac
  , a.region
  , a.device
  , a.identity
  , a.method
  , a.url
  , a.referer
  , a.time
FROM (
  SELECT *
  FROM dwd_log_info_di  
  WHERE dt = '${bizdate}'
) a
LEFT OUTER JOIN (
  SELECT *
  FROM ods_user_info_d
  WHERE dt = '${bizdate}'
) b
ON a.uid = b.uid;

Configure the debug parameters.

Configuration item	Description
Computing Resources	Select the MaxCompute compute resource that you associated in the Prepare the environment step and its corresponding compute quota.
Resource Group	Select the serverless resource group that you purchased in the Prepare the environment step.
Script Parameters	No configuration is required. The sample code in this tutorial uses `${bizdate}` to represent the business date. When you debug 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 code.

(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 MaxCompute SQL page. For more information about schedule settings parameters, see Schedule settings.
- Scheduling Parameters: This tutorial has configured scheduling parameters at the workflow level. No additional configuration is required for individual nodes within the workflow. You can use these parameters directly in tasks or code.
- Scheduling Policy: You can use the Delayed execution time parameter to specify how long a child node should wait before running after the workflow starts running. This tutorial does not set this parameter.
On the node toolbar, click Save.

Configure the ads_user_info_1d node

This node further processes data from the dws_user_info_all_di table and writes the results to the ads_user_info_1d table to generate basic user profiles.

On the workflow canvas, hover over the ads_user_info_1d node and click Open Node.

Paste the following code into the node editor.

Sample code for the ads_user_info_1d node

-- Create table ads_user_info_1d
CREATE TABLE IF NOT EXISTS ads_user_info_1d (
 uid STRING COMMENT 'User ID',
 region STRING COMMENT 'Region, derived from IP',
 device STRING COMMENT 'Device type ',
 pv BIGINT COMMENT 'pv',
 gender STRING COMMENT 'Gender',
 age_range STRING COMMENT 'Age range',
 zodiac STRING COMMENT 'Zodiac sign'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;    

-- Data processing
-- Scenario: The following SQL further processes the user access information wide table dws_user_info_all_di to produce basic user profile data and writes it into the ads_user_info_1d table.
-- Notes: DataWorks provides scheduling parameters to write daily incremental data into the corresponding business partition of the target table in scheduling scenarios.
--      In actual development, you can define code variables in the ${variable_name} format, and assign scheduling parameters to the variables on the scheduling configuration page to enable dynamic parameter passing in scheduling scenarios.
INSERT OVERWRITE TABLE ads_user_info_1d  PARTITION (dt='${bizdate}')
SELECT uid
  , MAX(region)
  , MAX(device)
  , COUNT(0) AS pv
  , MAX(gender)
  , MAX(age_range)
  , MAX(zodiac)
FROM dws_user_info_all_di
WHERE dt = '${bizdate}'
GROUP BY uid;

Configure the debug parameters.

Configuration item	Description
Computing Resources	Select the MaxCompute compute resource that you associated in the Prepare the environment step and its corresponding compute quota.
Resource Group	Select the serverless resource group that you purchased in the Prepare the environment step.
Script Parameters	No configuration is required. The sample code in this tutorial uses `${bizdate}` to represent the business date. When you debug 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 code.

(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 MaxCompute SQL page. For more information about schedule settings parameters, see Schedule settings.
- Scheduling Parameters: This tutorial has configured scheduling parameters at the workflow level. No additional configuration is required for individual nodes within the workflow. You can use these parameters directly in tasks or code.
- Scheduling Policy: You can use the Delayed execution time parameter to specify how long a child node should wait before running after the workflow starts running. This tutorial does not set this parameter.
On the node toolbar, click Save.

4. Process data

Process data.

In the workflow toolbar, click Run, set the values of the parameter variables defined in each node for this run (this tutorial uses 20250223, and you can change the value as needed), click OK, and wait for the run to complete.
Query the data processing results.
1. In the left-side navigation pane of DataStudio, click to open the Data Development page. Then, in the personal folder, click to create a .sql file. Use any file name.
2. At the bottom of the page, confirm that the language mode is MaxCompute SQL.
3. In the SQL editor, enter the following SQL statement to query the record count of the final result table ads_user_info_1d and verify whether data processing results have been generated.
```
-- You need to modify the partition filter condition to the actual business date of your current operation. In this tutorial, the debug parameter bizdate (business date) configured earlier is 20250223.
SELECT count(*) FROM ads_user_info_1d WHERE dt='business date';
```
  - If the query returns data, the data processing is complete.
  - If a query returns a count of zero, ensure the This operation value matches the business date in your query's dt partition. To check the value, open the Runtime Logs pane on the right side of the workflow and click View in the Operation column for the run. The run log shows the business date value, for example, partition=[dt=20250223].

5. Deploy the workflow

Tasks must be deployed to the production environment before they can be automatically scheduled. Follow these steps to deploy the workflow to the production environment.

Note

This tutorial has configured scheduling parameters at the workflow level in Workflow schedule settings. You do not need to configure scheduling parameters for each node individually before deployment.

In the left-side navigation pane of Data Studio, click the icon to open the Data Development page. In the Project Directory section, find the workflow you created and click it to open the workflow canvas.
On the node toolbar, click Publish to open the deployment panel.
Click Start Release Production. In the deployment method confirmation dialog, select a deployment method based on your needs:
- Full deployment: Deploys the current workflow and all its tasks.
- Incremental deployment: Deploys only the current workflow and the internal task nodes whose saved content differs from the current baselines in all deployment environments. This method is suitable for iterative optimization and small-scale updates.
After you confirm the deployment method, the system automatically deploys the workflow and the selected task nodes to the development environment and the production environment in sequence. When deploying to the production environment, click Confirm Release to complete the deployment.

6. Run tasks in the production environment

After tasks are deployed, instances are generated and run on the next day. You can use Supplementary data to backfill data for the deployed workflow and verify whether the tasks can run in the production environment. For more information, see Backfill data.

After the tasks are deployed, click Operation and Maintenance Center in the upper-right corner.

You can also click the icon in the upper-left corner and choose All Products > Data Development and O&M > Operation and Maintenance Center (Workflow).
In the left-side navigation pane, choose Auto Triggered Task O&M > Auto Triggered Node to go to the Auto Triggered Node page, and then click the workshop_start virtual node.
In the DAG on the right side, right-click the workshop_start node and choose Supplementary data > Current and Descendant Nodes Retroactively.
Select the tasks for which you want to backfill data, set the business date, and then click Submit and Redirect.
On the backfill data page, click Refresh until all SQL tasks have run successfully.

Note

After you complete this tutorial, to avoid incurring additional costs, you can set the scheduling validity period for the nodes or freeze the root node of the workflow (virtual node workshop_start).

Next steps

Visualize data: After user profile analysis is complete, use the Data Analysis module to visualize the processed data as charts for quick insight into key information and business trends.
Monitor data quality: Configure data quality monitoring for tables generated by data processing to identify and block dirty data early and prevent the spread of data issues.
View metadata: After the user profile analysis workflow is complete, corresponding data tables are created in MaxCompute. You can view the generated tables in the Data Map module and use lineage to view the relationships between tables.
Share data through APIs: After the final processed data is available, use the Data Service module to share and apply data through standardized API endpoints, providing data to other business modules that consume data through APIs.