DataWorks:Process data

Upload ip2region-starrocks.jar to OSS

1. Download ip2region-starrocks.jar.

2. Log on to the OSS console. Open the bucket you created when preparing environments and create a directory named dataworks_starrocks.

3. Upload ip2region-starrocks.jar to the dataworks_starrocks directory. The full internal path follows this pattern: https://<bucket-name>.oss-<region>-internal.aliyuncs.com/dataworks_starrocks/ip2region-starrocks.jar In this tutorial, the bucket is named test and is in the China (Shanghai) region, so the path is: https://test.oss-cn-shanghai-internal.aliyuncs.com/dataworks_starrocks/ip2region-starrocks.jar

   - Use the internal endpoint (access from ECS over the classic network). The OSS bucket must be in the same region as the DataWorks workspace. - Copy this path — you need it in the next step when registering the function.

Register the getregion function

1. In Data Studio, click and choose Create Node > Database > StarRocks to create a new StarRocks node for function registration.

2. Paste the following SQL into the node editor. Replace the file value with the OSS path from the previous step.

   CREATE FUNCTION getregion(string)
   RETURNS string
   PROPERTIES (
       "symbol" = "com.starrocks.udf.sample.Ip2Region",
       "type" = "StarrocksJar",
       "file" = "<full-oss-internal-path>/dataworks_starrocks/ip2region-starrocks.jar"
   );

   To verify the function works, run:

   SELECT getregion('<your-ip-address>');

3. Click Save, then Deploy to publish the node to the development and production environments. After deployment, backfill data for the node to complete registration in production.

4. After registration, go to Operation Center and manually freeze this node in the production environment to prevent failures from repeated registration attempts.

Configure dwd_log_info_di_starrocks

This node parses raw log fields from ods_raw_log_d_starrocks. Each raw row is a single delimited string (##@@ separator). The SQL splits it into fields, applies REGEXP_EXTRACT to parse the HTTP request, and calls getregion(ip) to resolve the region.

1. In the workflow canvas, hover over the dwd_log_info_di_starrocks node and click Open Node.

2. From the Select DataSource drop-down list, select the StarRocks computing resource associated with your workspace.

3. Paste the following SQL into the code editor:

   CREATE TABLE IF NOT EXISTS dwd_log_info_di_starrocks (
       uid STRING COMMENT 'The user ID',
       ip STRING COMMENT 'The IP address',
       TIME STRING COMMENT 'The time in the 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 based on the IP address',
       method STRING COMMENT 'The HTTP request type',
       url STRING COMMENT 'url',
       protocol STRING COMMENT 'The HTTP version number',
       referer STRING COMMENT 'The source URL',
       device STRING COMMENT 'The terminal type',
       identity STRING COMMENT 'The access type: crawler, feed, user, or unknown',
       dt DATE NOT NULL COMMENT 'The time'
   ) DUPLICATE KEY(uid)
   COMMENT 'User behavior analysis case - The fact table for the user website access logs'
   PARTITION BY(dt)
   PROPERTIES ("replication_num" = "1");
   
   -- Drop the destination partition before each run to prevent duplicate writes on rerun.
   ALTER TABLE dwd_log_info_di_starrocks DROP PARTITION IF EXISTS p${var} FORCE;
   
   -- Parse raw log data: split delimited fields, extract HTTP request components,
   -- resolve region from IP using the getregion UDF, and classify device and identity.
   -- ${var} is replaced at runtime by the scheduling parameter (e.g., 20250223).
   INSERT INTO dwd_log_info_di_starrocks
   SELECT
       uid,
       ip,
       time,
       status,
       bytes,
       getregion(ip) AS region,
       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;

4. In the right-side navigation pane, click Run Configuration and set the following parameters. These are used when you run the workflow in Step 4.

   Parameter	Description
   Computing Resource	Select the StarRocks computing resource associated with your workspace
   Resource Group	Select the serverless resource group you purchased
   Script Parameters	For var, enter a date in yyyymmdd format — for example: var=20250223

5. (Optional) Click Scheduling Configuration to review scheduling settings. For this tutorial, leave them at their defaults. Scheduling parameters are inherited from the workflow and apply to all nodes automatically.

6. Click Save.

Configure dws_user_info_all_di_starrocks

This node joins the parsed log data with basic user information using a LEFT JOIN on uid, then writes the merged result to a wide table.

1. In the workflow canvas, hover over dws_user_info_all_di_starrocks and click Open Node.

2. From Select DataSource, select the StarRocks computing resource.

3. Paste the following SQL into the code editor:

   CREATE TABLE IF NOT EXISTS dws_user_info_all_di_starrocks (
       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 based on the IP address',
       device STRING COMMENT 'The terminal type',
       identity STRING COMMENT 'The access type: crawler, feed, user, or unknown',
       method STRING COMMENT 'The HTTP request type',
       url STRING COMMENT 'url',
       referer STRING COMMENT 'The source URL',
       TIME STRING COMMENT 'The time in the yyyymmddhh:mi:ss format',
       dt DATE NOT NULL COMMENT 'The time'
   ) DUPLICATE KEY(uid)
   COMMENT 'User behavior analysis case - Wide table for the website access information about users'
   PARTITION BY(dt)
   PROPERTIES ("replication_num" = "1");
   
   -- Drop the destination partition before each run to prevent duplicate writes on rerun.
   ALTER TABLE dws_user_info_all_di_starrocks DROP PARTITION IF EXISTS p${var} FORCE;
   
   -- Merge enriched log data with basic user information (gender, age range, zodiac sign).
   -- Uses LEFT JOIN so log records without a matching user profile are still included.
   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}';

4. Configure the same debugging parameters as in the previous node (Computing Resource, Resource Group, Script Parameters).

5. (Optional) Review scheduling settings under Scheduling Configuration. Leave them at their defaults.

6. Click Save.

Configure ads_user_info_1d_starrocks

This node aggregates the wide table by uid to produce one row per user per day. It uses MAX() to collapse multiple log entries and COUNT(*) for page view (PV) count.

1. In the workflow canvas, hover over ads_user_info_1d_starrocks and click Open Node.

2. From Select DataSource, select the StarRocks computing resource.

3. Paste the following SQL into the code editor:

   CREATE TABLE IF NOT EXISTS ads_user_info_1d_starrocks (
       uid STRING COMMENT 'The user ID',
       region STRING COMMENT 'The region, obtained based on the IP address',
       device STRING COMMENT 'The terminal type',
       pv BIGINT COMMENT 'pv',
       gender STRING COMMENT 'The gender',
       age_range STRING COMMENT 'The age range',
       zodiac STRING COMMENT 'The zodiac sign',
       dt DATE NOT NULL COMMENT 'The time'
   ) DUPLICATE KEY(uid)
   COMMENT 'User behavior analysis case - User profile data'
   PARTITION BY(dt)
   PROPERTIES ("replication_num" = "1");
   
   -- Drop the destination partition before each run to prevent duplicate writes on rerun.
   ALTER TABLE ads_user_info_1d_starrocks DROP PARTITION IF EXISTS p${var} FORCE;
   
   -- Aggregate the wide table by user: one row per user per day.
   -- COUNT(*) captures total page views; MAX() collapses multi-value fields into a single representative value.
   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}';

4. Configure the same debugging parameters as in the previous nodes (Computing Resource, Resource Group, Script Parameters).

5. (Optional) Review scheduling settings under Scheduling Configuration. Leave them at their defaults.

6. Click Save.