User profile analysis tutorial: Process data with DataStudio and MaxCompute - DataWorks

Prerequisites

Before you begin, complete the steps in Synchronize data.

Step 1: Build a data processing workflow

After synchronizing your data to MaxCompute in the Synchronize data step, you will now process it to generate a basic user profile.

In the left-side navigation pane of DataStudio, click the icon to open the data development page. In the Project Directory section, find the workflow that you created and click its name to open the workflow canvas.

The following table describes the example nodes and their functions in this tutorial.

Type	Name	Description
MaxCompute SQL	`dwd_log_info_di`	Use built-in functions and UDFs such as `getregion` to split raw log data from `ods_raw_log_d` and write it to the `dwd_log_info_di` table across multiple fields.
MaxCompute SQL	`dws_user_info_all_di`	Summarize the user basic information table (`ods_user_info_d`) and the initially processed log data table (`dwd_log_info_di`), and write the data to the `dws_user_info_all_di` table.
MaxCompute SQL	`ads_user_info_1d`	Further process the data from the `dws_user_info_all_di` table and write the results to the `ads_user_info_1d` table to generate basic user profiles.

Note
In a workflow, you can manually draw lines to set the scheduling dependency between nodes. You can also let the system automatically identify dependencies by parsing the code in the child nodes. This tutorial uses the manual connection method. For more information about code parsing, see Automatic dependency parsing.

Step 2: Register a UDF

To ensure that subsequent data processing tasks run smoothly, you need to register the MaxCompute UDF (getregion) to parse the log data structure synchronized to MaxCompute during the Data Synchronization stage into a table.

Important

This tutorial provides the necessary resource for the function that maps IP addresses to regions. Download the provided resource to your local machine and upload it to your DataWorks workspace before you register the function.
The function and sample IP resources are for tutorial purposes only. For production use cases that require mapping IP addresses to geographical locations, you must obtain IP conversion services from a professional provider.

Upload a resource (`ip2region.jar`)

Download the ip2region.jar package.

Note
The ip2region.jar resource sample is for tutorial use only.
In the left-side navigation pane of the DataStudio page, click the icon to open the RESOURCE MANAGEMENT page. Click > New Resource > MaxCompute Jar. After you set a name for the resource, the resource upload page appears.

Note
The resource name does not need to match the name of the uploaded file.
For Document Source, select Local, click Click Upload, and select the ip2region.jar file that you have downloaded locally.
For Data Source, select the MaxCompute computing resource that you bound in the Prepare environment stage.
In the node toolbar, click Save and then click Publish. Follow the prompts in the publish panel to publish the resource to the MaxCompute projects for the development and production environments.

Register the UDF (`getregion`)

On the Resource Management page, click > New Function > MaxCompute Function, set a name for the resource, and you are taken to the Register Function page (in this tutorial, the function is named getregion).

On the Create Function page, configure the parameters described in the following table. You can keep the default values for all other parameters.

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

Click Save in the node toolbar, and then click Publish. In the publishing panel, follow the prompts to publish the function to the MaxCompute projects for the development and production environments.

Step 3: Configure the data processing nodes

Data processing requires you to implement the logic for each layer by scheduling MaxCompute SQL jobs. This tutorial provides the complete sample SQL code for you to configure the dwd_log_info_di, dws_user_info_all_di, and ads_user_info_1d nodes in sequence.

Configure the `dwd_log_info_di` node

In the sample code for this node, SQL code uses the created function to process fields 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 DataStudio, click the icon to open the data development page. In the Project Directory section, find the workflow that you created and click its name to open the workflow canvas.
On the workflow orchestration page, hover over the dwd_log_info_di node, and click Open Node.

Paste the following code into the node's code editor.

Sample code for the dwd_log_info_di node

-- Create the dwd_log_info_di table.
CREATE TABLE IF NOT EXISTS dwd_log_info_di (
 ip STRING COMMENT 'The IP address.',
 uid STRING COMMENT 'The user ID.',
 time STRING COMMENT 'The time in yyyymmddhh:mi:ss format.',
 status STRING COMMENT 'The status code returned by the server.',
 bytes STRING COMMENT 'The number of bytes returned to the client.',
 region STRING COMMENT 'The region, obtained from the IP address.',
 method STRING COMMENT 'The HTTP request type.',
 url STRING COMMENT 'The URL.',
 protocol STRING COMMENT 'The HTTP protocol version.',
 referer STRING COMMENT 'The referrer URL.',
 device STRING COMMENT 'The client type.',
 identity STRING COMMENT 'The access type. Valid values: crawler, feed, user, and unknown.'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;

-- Process the data.
-- Scenario: The following SQL statements use the getregion function to parse the IP address in the raw log data. 
-- It also uses methods like regular expressions to split the raw data into analyzable fields and writes them to the dwd_log_info_di table.
-- The getregion function, which is used to resolve IP addresses into regions, is provided for this tutorial.
-- Note:
--     1. Before you use a UDF in a DataWorks node, you must upload the required resource to DataWorks and then register the function by using that resource.
--        The resource used to register the getregion function in this tutorial is ip2region.jar.
--     2. DataWorks provides scheduling parameters that allow you to write daily incremental data to the corresponding data timestamp partition of a destination table.
--        In a real-world development scenario, you can define code variables in the ${variable_name} format and assign scheduling parameters as values on the Properties tab. This enables dynamic parameter substitution when the task is run.
INSERT OVERWRITE TABLE dwd_log_info_di PARTITION (dt='${bizdate}')
SELECT ip 
  , uid
  , time
  , status
  , bytes 
  , getregion(ip) AS region -- Use the UDF to obtain the region from the IP address.
  , regexp_substr(request, '(^[^ ]+ )') AS method -- Use a regular expression to split the request into three fields.
  , regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') AS url
  , regexp_substr(request, '([^ ]+$)') AS protocol 
  , regexp_extract(referer, '^[^/]+://([^/]+){1}') AS referer -- Use a regular expression to clean the referer and get a more precise URL.
  , CASE
    WHEN TOLOWER(agent) RLIKE 'android' THEN 'android' -- Obtain client and access type information from the 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 debugging parameters.

On the right side of the MaxCompute SQL node editor, click Run Configuration and configure the following parameters to run a test with the relevant parameters of Run Configuration during debugging in Step 4.

Parameter	Description
Computing Resources	Select the MaxCompute computing resource and its corresponding quota that you associated in the Prepare the environment step.
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 provided in this tutorial uses `${bizdate}` to represent the data timestamp. In Step 4, when you debug and run the workflow, set This operation value to a specific constant, such as `20250223`. The task will then replace the variable with this constant during runtime.

(Optional) Configure scheduling properties.

For this tutorial, you can keep the default values for the scheduling configuration parameters. On the right side of the MaxCompute SQL page, click Scheduling Configuration. For more information about the scheduling configuration parameters, see Node Scheduling Configuration.
- Scheduling Parameters: These parameters are configured at the workflow level in this tutorial. You do not need to configure them for each node in the workflow. You can use them directly in your tasks or code.
- Scheduling Policy: In the Delayed execution time parameter, you can specify how long to delay the execution of a child node after the workflow runs. This setting is not configured in this tutorial.
In the node toolbar, click Save.

Configure the `dws_user_info_all_di` node

This node summarizes the basic user information table (ods_user_info_d) and the initially processed log data table (dwd_log_info_di), and writes the data to the dws_user_info_all_di table.

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

Paste the following code into the node's code editor.

Sample code for the dws_user_info_all_di node

-- Create the dws_user_info_all_di table.
CREATE TABLE IF NOT EXISTS dws_user_info_all_di (
 uid STRING COMMENT 'The user ID.',
 gender STRING COMMENT 'The gender.',
 age_range STRING COMMENT 'The age range.',
 zodiac STRING COMMENT 'The zodiac sign.',
 region STRING COMMENT 'The region, obtained from the IP address.',
 device STRING COMMENT 'The client type.',
 identity STRING COMMENT 'The access type. Valid values: crawler, feed, user, and unknown.',
 method STRING COMMENT 'The HTTP request type.',
 url STRING COMMENT 'The URL.',
 referer STRING COMMENT 'The referrer URL.',
 time STRING COMMENT 'The time in yyyymmddhh:mi:ss format.'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;

-- Process the data.
-- Scenario: Aggregates the processed log data from dwd_log_info_di and the user information from ods_user_info_d, and writes the results to the dws_user_info_all_di table.
-- Note: DataWorks provides scheduling parameters that allow you to write daily incremental data to the corresponding data timestamp partition of a destination table.
--       In a real-world development scenario, you can define code variables in the ${variable_name} format and assign scheduling parameters as values on the Properties tab. This enables dynamic parameter substitution when the task is run.
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 debugging parameters.

On the right side of the MaxCompute SQL node editor, click Run Configuration and configure the following parameters to run a test with the relevant parameters of Run Configuration during debugging in Step 4.

Parameter	Description
Computing Resources	Select the MaxCompute computing resource and its corresponding quota that you associated in the Prepare the environment step.
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 provided in this tutorial uses `${bizdate}` to represent the data timestamp. In Step 4, when you debug and run the workflow, set This operation value to a specific constant, such as `20250223`. The task will then replace the variable with this constant during runtime.

(Optional) Configure scheduling properties.

For this tutorial, you can keep the default values for the scheduling configuration parameters. On the right side of the MaxCompute SQL page, click Scheduling Configuration. For more information about the scheduling configuration parameters, see Node Scheduling Configuration.
- Scheduling Parameters: These parameters are configured at the workflow level in this tutorial. You do not need to configure them for each node in the workflow. You can use them directly in your tasks or code.
- Scheduling Policy: In the Delayed execution time parameter, you can specify how long to delay the execution of a child node after the workflow runs. This setting is not configured in this tutorial.
In the node toolbar, click Save.

Configure the `ads_user_info_1d` node

This node further processes the data from the dws_user_info_all_di table, writes the data to the ads_user_info_1d table, and generates a basic user profile.

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

Paste the following code into the node's code editor.

Sample code for the ads_user_info_1d node

-- Create the ads_user_info_1d table.
CREATE TABLE IF NOT EXISTS ads_user_info_1d (
 uid STRING COMMENT 'The user ID.',
 region STRING COMMENT 'The region, obtained from the IP address.',
 device STRING COMMENT 'The client type.',
 pv BIGINT COMMENT 'The number of page views.',
 gender STRING COMMENT 'The gender.',
 age_range STRING COMMENT 'The age range.',
 zodiac STRING COMMENT 'The zodiac sign.'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;    

-- Process the data.
-- Scenario: Further processes the user access information from the wide table dws_user_info_all_di to generate basic user profile data and writes it to the ads_user_info_1d table.
-- Note: DataWorks provides scheduling parameters that allow you to write daily incremental data to the corresponding data timestamp partition of a destination table.
--       In a real-world development scenario, you can define code variables in the ${variable_name} format and assign scheduling parameters as values on the Properties tab. This enables dynamic parameter substitution when the task is run.
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 debugging parameters.

On the right side of the MaxCompute SQL node editor, click Run Configuration and configure the following parameters to run a test with the relevant parameters of Run Configuration during debugging in Step 4.

Parameter	Description
Computing Resources	Select the MaxCompute computing resource and its corresponding quota that you associated in the Prepare the environment step.
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 provided in this tutorial uses `${bizdate}` to represent the data timestamp. In Step 4, when you debug and run the workflow, set This operation value to a specific constant, such as `20250223`. The task will then replace the variable with this constant during runtime.

(Optional) Configure scheduling properties.

For this tutorial, you can keep the default values for the scheduling configuration parameters. On the right side of the MaxCompute SQL page, click Scheduling Configuration. For more information about the scheduling configuration parameters, see Node Scheduling Configuration.
- Scheduling Parameters: These parameters are configured at the workflow level in this tutorial. You do not need to configure them for each node in the workflow. You can use them directly in your tasks or code.
- Scheduling Policy: In the Delayed execution time parameter, you can specify how long to delay the execution of a child node after the workflow runs. This setting is not configured in this tutorial.
In the node toolbar, click Save.

Step 4: Process data

Process the 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.
Query the data processing result.
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 SQL editor, enter the following SQL statement to check the number of records in the final result table ads_user_info_1d and confirm whether the data was processed.
```
-- You need to change the partition filter condition to the actual data timestamp for your run. 
-- In this tutorial, the bizdate (data timestamp) debugging parameter was set to 20250223.
SELECT count(*) FROM ads_user_info_1d WHERE dt='<your_data_timestamp>';
```
  - If the query returns data, the data has been processed successfully.
  - 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.

Step 5: Deploy the workflow

A task can be scheduled automatically only after it is deployed to the production environment. You can follow these steps to deploy the workflow.

Note

In this tutorial, scheduling parameters are configured in the workflow scheduling properties. You do not need to configure them for each node before deployment.

In the left navigation bar of Data Studio, click to go to the DataStudio page. Then, in the Project Directory area, find the created workflow and click the workflow to open the workflow orchestration page.
Click Publish in the node toolbar to open the Publish panel.
Click Start Release Production. In the confirmation dialog box that appears, select a deployment method based on your requirements:
- Full deployment: Deploys the current workflow and all its internal task nodes.
- Incremental deployment: Deploys only the current workflow and the internal task nodes that have been modified since the last deployment. This is suitable for iterative optimizations and minor updates.
After you confirm the deployment method, the system automatically executes the deployment process, deploying the workflow and selected task nodes to the development and production environments in sequence. To complete the deployment to the production environment, you must click Confirm Release.

Step 6: Run tasks in production

After a task is deployed, an instance is generated to run on the next day. You can use Supplementary data to backfill data for the deployed workflow and check whether the task can run in the production environment. For more information, see Data Backfill Instance O&M.

After the task is successfully deployed, click Operation and Maintenance Center in the upper-right corner.

Alternatively, 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 navigation bar, click Auto Triggered Task O&M > Auto Triggered Node. On the Auto Triggered Node page, click the workshop_start virtual node.
In the DAG on the right, right-click the workshop_start node, and select Supplementary data > Current and Descendant Nodes Retroactively.
Select the tasks that require data backfill, set the data timestamp, and click Submit and Redirect.
On the data backfill page, click Refresh until all SQL tasks have run successfully.

Note

After you complete the tutorial, to avoid incurring further costs, you can set the node's scheduling validity period or freeze the root node of the business process (the virtual node workshop_start).

Next steps

Visualize data: After the user profile analysis is complete, use the data analysis module to display the processed data in charts. This helps you quickly extract key information and gain insights into business trends.
Monitor data quality: Configure data quality monitoring rules for the tables generated during data processing. This allows you to identify and block dirty data in advance to prevent its impact from propagating.
Manage data: After the user profile analysis workflow is complete, the corresponding data tables are created in MaxCompute. You can view these tables in Data Map and check the lineage to understand the relationships between them.
API data service: After you obtain the final processed data, use the data service module to share data through standardized APIs. This allows you to provide data to other business modules that consume data through APIs.

Prerequisites

Step 1: Build a data processing workflow

Step 2: Register a UDF

Upload a resource (ip2region.jar)

Register the UDF (getregion)

Step 3: Configure the data processing nodes

Configure the dwd_log_info_di node

Configure the dws_user_info_all_di node

Configure the ads_user_info_1d node

Step 4: Process data

Step 5: Deploy the workflow

Step 6: Run tasks in production

Next steps

Upload a resource (`ip2region.jar`)

Register the UDF (`getregion`)

Configure the `dwd_log_info_di` node

Configure the `dws_user_info_all_di` node

Configure the `ads_user_info_1d` node