Process data for a user profile analysis use case based on new Data Development and StarRocks computing resources - DataWorks

Prerequisites

Before you begin, complete the steps in Synchronize data.

Step 1: Design the data workflow

After synchronizing data to StarRocks in the Synchronize data step, you can now process the data to generate basic user profile data.

In Data Studio, click the icon in the left-side navigation pane. In the Project Directory area, find and click the workflow that you created to open its canvas. Drag StarRocks nodes from the left pane to the canvas and name each node.

The following table provides the names and functions of the nodes in this tutorial.

Node type	Node name	Function
StarRocks	`dwd_log_info_di_starrocks`	Parses raw log data from `ods_raw_log_d_starrocks` using built-in functions and a UDF, then writes the results to the `dwd_log_info_di_starrocks` table.
StarRocks	`dws_user_info_all_di_starrocks`	Joins basic user information from `ods_user_info_d_starrocks` with processed log data from `dwd_log_info_di_starrocks` and writes the merged data to the `dws_user_info_all_di_starrocks` table.
StarRocks	`ads_user_info_1d_starrocks`	Further processes the data in the `dws_user_info_all_di_starrocks` table and writes it to the `ads_user_info_1d_starrocks` table to produce a basic user profile.

Manually drag lines to connect the nodes and configure their upstream dependencies. The final workflow should look like this:

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

Use functions to parse the synchronized log data into a tabular format.

Important

You must download these resources, upload them to Object Storage Service (OSS), and then register them as a function in DataWorks by following the steps below.
This function and the sample IP resources are for tutorial purposes only. For mapping IP addresses to geographical locations in a production environment, obtain a professional IP conversion service from a specialized IP database provider.

Upload the resource

Download ip2region-starrocks.jar.

Note
The sample ip2region-starrocks.jar resource is for tutorial use only.
Upload the resource to OSS.
1. Log on to the Object Storage Service (OSS) console. Navigate to the OSS bucket you created in the Prepare the environment step and create a directory named dataworks_starrocks.
2. Upload the ip2region-starrocks.jar resource to the dataworks_starrocks directory.
  
  Use this path format as a reference for your own OSS resource path. https://test.oss-cn-shanghai-internal.aliyuncs.com/dataworks_starrocks/ip2region-starrocks.jar.
  Note
  - In this tutorial, the bucket name is test.
  - Use the classic network (internal endpoint) for the bucket that contains the user-defined function.
  - When you use an internal endpoint, the OSS bucket and the DataWorks workspace must be in the same region. This tutorial uses the China (Shanghai) region for both.

Register the function

Create a node to register the function.

In Data Studio, click the icon in the left-side navigation pane. In the Project Directory area, click the icon and select New Node > Database > StarRocks to create a new StarRocks node.

Edit the node to register the function.

Register the function.

CREATE FUNCTION getregion(string)
RETURNS string
PROPERTIES ( 
    "symbol" = "com.starrocks.udf.sample.Ip2Region", 
    "type" = "StarrocksJar",
    "file" = "Replace this with the full path of the file stored in OSS. For information about how to obtain the file path, see Upload the resource."
);

Verify that the function is registered successfully.
```
SELECT getregion('your_local_ip');
```

Important

A function can be registered only once per environment. To register the function in the production environment, you must deploy the corresponding StarRocks registration node.

In the top toolbar, click Save and then click Publish. Follow the prompts in the deployment pane to deploy the function registration task to the corresponding StarRocks instances in the development and production environments. Then, backfill the task's data to register the function in the production environment. After registration, go to Operation Center and manually freeze the task to prevent repeated registration failures.

Step 3: Configure StarRocks nodes

Configure the data processing logic for each layer by using StarRocks nodes. The following sections provide the complete sample SQL code. Configure the dwd_log_info_di_starrocks, dws_user_info_all_di_starrocks, and ads_user_info_1d_starrocks nodes in sequence.

Configure the dwd_log_info_di_starrocks node

The sample code for this node uses the UDF you created to process fields from the upstream table ods_raw_log_d_starrocks and writes the results to the dwd_log_info_di_starrocks table.

On the workflow canvas, hover over the dwd_log_info_di_starrocks node and click Open Node.
On the node editor page, select the StarRocks computing resource that you prepared in the Prepare the environment step.

Paste the following code into the SQL editor.

Note

The sample code for the dwd_log_info_di_starrocks node uses the UDF you created to process fields from the upstream table ods_raw_log_d_starrocks and writes the processed data to the dwd_log_info_di_starrocks table.

Sample code for the dwd_log_info_di_starrocks node

CREATE TABLE IF NOT EXISTS dwd_log_info_di_starrocks (
    uid STRING COMMENT 'User ID',
    ip STRING COMMENT 'IP address',
    TIME STRING COMMENT 'Time in yyyymmddhh:mi:ss format',
    status STRING COMMENT 'Status code returned by the server',
    bytes STRING COMMENT 'Bytes returned to the client',
    region STRING COMMENT 'Region, derived from the IP address',
    method STRING COMMENT 'HTTP request type',
    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, or unknown',
    dt DATE NOT NULL COMMENT 'Date'
) DUPLICATE KEY(uid) 
COMMENT 'User behavior analysis tutorial - Website access log fact table' 
PARTITION BY(dt) 
PROPERTIES ("replication_num" = "1");

-- This example uses dynamic partitioning on the dt field. To prevent data duplication when re-running the node, the following command deletes the existing target partition before each run.
ALTER TABLE dwd_log_info_di_starrocks DROP PARTITION IF EXISTS p${var} FORCE;

-- Scenario: The following SQL uses the getregion function to parse the IP from the raw log data. It also uses regular expressions and other methods to break down the raw data into analyzable fields and writes them to the dwd_log_info_di_starrocks table.
-- Notes:
--     1. You must register the user-defined function before using it in a DataWorks node.
--     2. DataWorks provides scheduling parameters to write daily incremental data to the corresponding business partition of a target table.
--        In a real-world development scenario, you can define code variables using the ${variable_name} format and assign scheduling parameters to them on the scheduling configuration page to enable dynamic parameter passing.
INSERT INTO dwd_log_info_di_starrocks 
SELECT 
    uid
    , ip  
    , time
    , status
    , bytes 
    , getregion(ip) AS region-- Use the custom UDF to get the region from the IP address
    ,REGEXP_EXTRACT(request, '([^ ]+)', 1) AS method
    ,REGEXP_EXTRACT(request, '^[^ ]+ (.*) [^ ]+$', 1) AS url
    ,REGEXP_EXTRACT(request, '([^ ]+)$', 1) AS protocol
    ,REGEXP_EXTRACT(referer, '^[^/]+://([^/]+)', 1) AS referer
  , CASE
    WHEN LOWER(agent) REGEXP 'android' THEN 'android'
    WHEN LOWER(agent) REGEXP 'iphone' THEN 'iphone'
    WHEN LOWER(agent) REGEXP 'ipad' THEN 'ipad'
    WHEN LOWER(agent) REGEXP 'macintosh' THEN 'macintosh'
    WHEN LOWER(agent) REGEXP 'windows phone' THEN 'windows_phone'
    WHEN LOWER(agent) REGEXP 'windows' THEN 'windows_pc'
    ELSE 'unknown'
END AS device
  , CASE
    WHEN LOWER(agent) REGEXP '(bot|spider|crawler|slurp)' THEN 'crawler'
    WHEN LOWER(agent) REGEXP 'feed' OR REGEXP_EXTRACT(request, '^[^ ]+ (.*) [^ ]+$', 0) REGEXP 'feed' THEN 'feed'
    WHEN NOT (LOWER(agent) REGEXP '(bot|spider|crawler|feed|slurp)') 
         AND agent REGEXP '^(Mozilla|Opera)' 
         AND NOT (REGEXP_EXTRACT(request, '^[^ ]+ (.*) [^ ]+$', 0) REGEXP 'feed') THEN 'user'
    ELSE 'unknown'
END AS identity,
 cast('${var}' AS DATE )AS dt
  FROM (
    SELECT
      SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 1)  AS ip
    , SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 2)  AS uid
    , SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 3)  AS time
    , SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 4)  AS request
    , SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 5)  AS status
    , SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 6)  AS bytes
    , SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 7)  AS referer
    , SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 8)  AS agent
FROM
    ods_raw_log_d_starrocks
WHERE
    dt = '${var}'
) a;

Configure debugging parameters.

In the right-side pane of the StarRocks node editor, click Run Configuration and set the following parameters. These parameters are used for debugging and test runs in Step 4.

Parameter	Description
Computing Resources	Select the StarRocks computing resource that you bound in the Prepare the environment step.
Resource Group	Select the serverless resource group you purchased in the Prepare the environment step.
Script Parameters	Click Add parameter and configure a specific constant in the format `var=yyyymmdd`, such as `var=20250223`. During debugging, Data Studio will replace the variable defined in the task with this constant.

(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.
In the top toolbar, click Save.

Configure the dws_user_info_all_di_starrocks node

This node aggregates the user information table (ods_user_info_d_starrocks) and the processed log data table (dwd_log_info_di_starrocks), and writes the result to the dws_user_info_all_di_starrocks table.

On the workflow canvas, hover over the dws_user_info_all_di_starrocks node and click Open Node.
On the node editor page, select the StarRocks computing resource that you prepared in the Prepare the environment step.

Paste the following code into the SQL editor.

Note

On the editor page for the dws_user_info_all_di_starrocks node, write SQL to merge the upstream tables dwd_log_info_di_starrocks and ods_user_info_d_starrocks and write the result to the dws_user_info_all_di_starrocks table.

Sample code for the dws_user_info_all_di_starrocks node

CREATE TABLE IF NOT EXISTS dws_user_info_all_di_starrocks (
    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 the IP address',
    device STRING COMMENT 'Device type',
    identity STRING COMMENT 'Access type: crawler, feed, user, or unknown',
    method STRING COMMENT 'HTTP request type',
    url STRING COMMENT 'URL',
    referer STRING COMMENT 'Referrer URL',
    TIME STRING COMMENT 'Time in yyyymmddhh:mi:ss format',
    dt DATE NOT NULL COMMENT 'Date'
) DUPLICATE KEY(uid) 
COMMENT 'User behavior analysis tutorial - User website access information wide table' 
PARTITION BY(dt) 
PROPERTIES ("replication_num" = "1");

-- This example uses dynamic partitioning on the dt field. To prevent data duplication when re-running the node, the following command deletes the existing target partition before each run.
ALTER TABLE dws_user_info_all_di_starrocks DROP PARTITION IF EXISTS p${var} FORCE;


-- Scenario: Aggregate the processed log data from dwd_log_info_di_starrocks with the basic user information from ods_user_info_d_starrocks and write the result to the dws_user_info_all_di_starrocks table.
-- Note: DataWorks provides scheduling parameters to write daily incremental data to the corresponding business partition of a target table.
--      In a real-world development scenario, you can define code variables using the ${variable_name} format and assign scheduling parameters to them on the scheduling configuration page to enable dynamic parameter passing.
INSERT INTO dws_user_info_all_di_starrocks 
SELECT 
    IFNULL(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,
    a.dt
FROM dwd_log_info_di_starrocks a
LEFT JOIN ods_user_info_d_starrocks b
ON a.uid = b.uid
WHERE a.dt = '${var}';

Configure debugging parameters.

In the right-side pane of the StarRocks node editor, click Run Configuration and set the following parameters. These parameters are used for debugging and test runs in Step 4.

Parameter	Description
Computing Resources	Select the StarRocks computing resource that you bound in the Prepare the environment step.
Resource Group	Select the serverless resource group you purchased in the Prepare the environment step.
Script Parameters	Click Add parameter and configure a specific constant in the format `var=yyyymmdd`, such as `var=20250223`. During debugging, Data Studio will replace the variable defined in the task with this constant.

(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.
In the top toolbar, click Save.

Configure the ads_user_info_1d_starrocks node

This node further processes the data in the dws_user_info_all_di_starrocks table and writes it to the ads_user_info_1d_starrocks table to produce a basic user profile.

On the workflow canvas, hover over the ads_user_info_1d_starrocks node and click Open Node.
On the node editor page, select the StarRocks computing resource that you prepared in the Prepare the environment step.

Paste the following code into the SQL editor.

Sample code for the ads_user_info_1d_starrocks node

CREATE TABLE IF NOT EXISTS ads_user_info_1d_starrocks (
uid STRING COMMENT 'User ID',
region STRING COMMENT 'Region, derived from the IP address',
device STRING COMMENT 'Device type',
pv BIGINT COMMENT 'PV',
gender STRING COMMENT 'Gender',
age_range STRING COMMENT 'Age range',
zodiac STRING COMMENT 'Zodiac sign',
dt DATE NOT NULL COMMENT 'Date'
) DUPLICATE KEY(uid) 
COMMENT 'User behavior analysis tutorial - User profile data' 
PARTITION BY(dt) 
PROPERTIES ("replication_num" = "1");

-- This example uses dynamic partitioning on the dt field. To prevent data duplication when re-running the node, the following command deletes the existing target partition before each run.
ALTER TABLE ads_user_info_1d_starrocks DROP PARTITION IF EXISTS p${var} FORCE;

-- Scenario: The following SQL further processes the user access information wide table dws_user_info_all_di_starrocks to produce basic user profile data and writes it to the ads_user_info_1d_starrocks table.
-- Note: DataWorks provides scheduling parameters to write daily incremental data to the corresponding business partition of a target table.
-- In a real-world development scenario, you can define code variables using the ${variable_name} format and assign scheduling parameters to them on the scheduling configuration page to enable dynamic parameter passing.
INSERT INTO ads_user_info_1d_starrocks 
SELECT 
uid,
MAX(region) AS region,
MAX(device) AS device,
COUNT(*) AS pv,
MAX(gender) AS gender,
MAX(age_range) AS age_range,
MAX(zodiac) AS zodiac,
dt
FROM dws_user_info_all_di_starrocks
WHERE dt = '${var}'
GROUP BY uid, dt;

SELECT * FROM dws_user_info_all_di_starrocks
WHERE dt = '${var}';

Configure debugging parameters.

In the right-side pane of the StarRocks node editor, click Run Configuration and set the following parameters. These parameters are used for debugging and test runs in Step 4.

Parameter	Description
Computing Resources	Select the StarRocks computing resource that you bound in the Prepare the environment step.
Resource Group	Select the serverless resource group you purchased in the Prepare the environment step.
Script Parameters	Click Add parameter and configure a specific constant in the format `var=yyyymmdd`, such as `var=20250223`. During debugging, Data Studio will replace the variable defined in the task with this constant.

(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.
In the top 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 results.

Go to the SQL Query page.
Log on to the DataWorks console. In the target region, click Data Analysis and Service > DataAnalysis in the left-side navigation pane. Click Go to DataAnalysis to open the Data Analysis page. In the left-side navigation pane, click SQL Query.

Configure the SQL query file.

Click the icon next to Show My Nodes Only to create a new file and name it.
Click the newly created file to open the file editor.

On the file editor page, click the icon in the upper-right corner to configure the workspace and other information for the SQL query. The configuration details are as follows:

Parameter	Description
Work space	Select the workspace that contains the `user_profile_analysis_starrocks` workflow.
Data Source Type	Select `StarRocks` from the drop-down list.
Data Source Name	Select the StarRocks development environment that you bound in the Prepare the environment step.

Click Confirm to complete the data source configuration.

Edit the query SQL.

After all nodes run successfully, execute the following SQL query to verify that the StarRocks nodes created the output table as expected.

-- Replace the date in the WHERE clause with the data timestamp you used to run the workflow (for example, 20250223).
SELECT * FROM ads_user_info_1d_starrocks  WHERE dt='';

Step 5: Deploy the workflow

Deploy the workflow to the production environment so its tasks can run automatically on a schedule.

Note

In this tutorial, scheduling parameters have been uniformly configured in the workflow scheduling configuration. You do not need to configure scheduling parameters for each node individually 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 tasks are deployed successfully, 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-side navigation pane, choose Auto Triggered Task O&M > Auto Triggered Node. On the Auto Triggered Node page, click the workshop_start_starrocks zero load node.
In the directed acyclic graph (DAG) on the right, right-click the workshop_start_starrocks node and choose Supplementary data > Current and Descendant Nodes Retroactively.
Select the tasks to 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

To avoid incurring further charges after completing the tutorial, you can set an effective period for the nodes or freeze the workshop_start_starrocks zero load node.

Next steps

Visualize data: Use the DataAnalysis module to display processed data in charts, helping you extract key information and gain insights into business trends.
Monitor data quality: Configure data quality monitoring for the tables generated during data processing to identify and intercept dirty data before it affects downstream processes.
Manage data: View the data tables created by the workflow in the Data Map module and examine their relationships using data lineage.
Provide API-based data services: Use DataService Studio to create standardized APIs that allow other business modules to share and consume the processed data.