DataWorks:Process data

Configure the node code

Double-click dwd_log_info_di_starrocks on the workflow canvas to open the configuration tab. Enter the following SQL.

Each run deletes the destination partition before inserting data (ALTER TABLE ... DROP PARTITION IF EXISTS p${var} FORCE). This prevents duplicate rows when a node reruns.

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 that is returned by the server',
    bytes STRING COMMENT 'The number of bytes that are returned to the client',
    region STRING COMMENT 'The region, which is obtained based on the IP address',
    method STRING COMMENT 'The HTTP request type',
    url STRING COMMENT 'url',
    protocol STRING COMMENT 'The version number of HTTP',
    referer STRING COMMENT 'The source URL',
    device STRING COMMENT 'The terminal type',
    identity STRING COMMENT 'The access type, which can be 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 website access logs of users'
PARTITION BY(dt)
PROPERTIES ("replication_num" = "1");

-- Delete 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 each raw log entry (delimiter: ##@@) into separate fields.
-- getregion() resolves the IP address to a region using the registered UDF.
-- REGEXP_EXTRACT splits the HTTP request field into method, URL, and protocol.
-- Define ${var} as a scheduling parameter to process incremental data by date partition.
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;

Configure scheduling properties

In the right-side navigation pane of the node's configuration tab, click General and set the following parameters.

In the Schedule section, set Scheduling Cycle to Day. Leave Scheduled time at its default — the workshop_start_starrocks zero load node controls when the workflow runs (after 00:30 every day).

Save the node

Click the icon in the top toolbar to save.

Configure the node code

Double-click dws_user_info_all_di_starrocks on the canvas to open the configuration tab. Enter the following SQL.

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, which is obtained based on the IP address',
    device STRING COMMENT 'The terminal type',
    identity STRING COMMENT 'The access type, which can be 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");

-- Delete 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;

-- LEFT JOIN preserves all log records even if a uid has no matching user info row.
-- ${var} is replaced at runtime by the scheduling parameter (yesterday's date).
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 scheduling properties

Set Scheduling Cycle to Day. The workshop_start_starrocks zero load node controls run time (after 00:30 every day).

Save the node

Click the icon to save.

Configure the node code

Double-click ads_user_info_1d_starrocks on the canvas to open the configuration tab. Enter the following SQL.

CREATE TABLE IF NOT EXISTS ads_user_info_1d_starrocks (
    uid STRING COMMENT 'The user ID',
    region STRING COMMENT 'The region, which is 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");

-- Delete 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;

-- GROUP BY uid and dt to produce one profile row per user per day.
-- COUNT(*) gives the page view count (pv) for the day.
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;

-- Verification query: inspect the wide table for the processed date.
SELECT * FROM dws_user_info_all_di_starrocks
WHERE dt = '${var}';

Configure scheduling properties

Set Scheduling Cycle to Day. The workshop_start_starrocks zero load node controls run time (after 00:30 every day).

Save the node

Click the icon to save.