DataWorks:Process data

1. Configure the code

Double-click the dwd_log_info_di_starrocks node to open the node configuration page and enter the following statement.

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 'Number of bytes returned to the client',
    region STRING COMMENT 'Region, obtained from the IP address',
    method STRING COMMENT 'HTTP request type',
    url STRING COMMENT 'URL',
    protocol STRING COMMENT 'HTTP protocol version',
    referer STRING COMMENT 'Source URL',
    device STRING COMMENT 'Terminal type',
    identity STRING COMMENT 'Access type: crawler, feed, user, or unknown',
    dt DATE NOT NULL COMMENT 'Time'
) DUPLICATE KEY(uid) 
COMMENT 'User behavior analysis case - Detailed table for website access logs' 
PARTITION BY(dt) 
PROPERTIES ("replication_num" = "1");

-- This example uses dynamic partitioning on the dt field. To prevent data duplication if the node is re-run, the following command deletes the 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 IP addresses from 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 a UDF before using it in a DataWorks node.
--     2. DataWorks provides scheduling parameters to write daily incremental data to the corresponding business partition of a destination table.
--        In actual development, you can define code variables in the ${variable_name} format. On the scheduling properties page, you can then assign scheduling parameters as values to these variables to enable dynamic parameter passing in scheduling scenarios.
INSERT INTO dwd_log_info_di_starrocks 
SELECT 
    uid
    , ip  
    , time
    , status
    , bytes 
    , getregion(ip) as region -- Use a 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;

2. Configure scheduling properties

Click Scheduling Configuration on the right side of the edit page for the dwd_log_info_di_starrocks node. The configuration details are as follows:

3. Save the configuration

Configure other properties as needed. When you are finished, click the icon in the node code editor's toolbar to save the configuration.

Edit the code

Double-click the dws_user_info_all_di_starrocks node to open the node configuration page, and enter the following statement.

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, obtained from the IP address',
    device STRING COMMENT 'Terminal type',
    identity STRING COMMENT 'Access type: crawler, feed, user, or unknown',
    method STRING COMMENT 'HTTP request type',
    url STRING COMMENT 'URL',
    referer STRING COMMENT 'Source URL',
    TIME STRING COMMENT 'Time, in yyyymmddhh:mi:ss format',
    dt DATE NOT NULL COMMENT 'Time'
) DUPLICATE KEY(uid) 
COMMENT 'User behavior analysis case - Wide table for user website access information' 
PARTITION BY(dt) 
PROPERTIES ("replication_num" = "1");

-- This example uses dynamic partitioning on the dt field. To prevent data duplication if the node is re-run, the following command deletes the 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 and the basic user information data 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 destination table.
--      In actual development, you can define code variables in the ${variable_name} format. On the scheduling properties page, you can then assign scheduling parameters as values to these variables to enable dynamic parameter passing in scheduling scenarios.
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

Click Scheduling Configuration on the right side of the edit page for the dws_user_info_all_di_starrocks node. The configuration details are as follows:

3. Save the configuration

Configure other required properties as needed. After the configuration is complete, click the icon in the toolbar of the node code editor to save the current configuration.

1. Edit the code

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

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

-- Scenario: The following SQL is used to further process the dws_user_info_all_di_starrocks wide table of user access information to generate basic user profile data and write 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 destination table.
-- In actual development, you can define code variables in the ${variable_name} format. On the scheduling properties page, you can then assign scheduling parameters as values to these variables to enable dynamic parameter passing in scheduling scenarios.
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}';

2. Configure scheduling properties

Click Scheduling Configuration on the right side of the ads_user_info_1d_starrocks node's edit page. The configuration details are as follows:

3. Save the configuration

Configure other required properties as needed. After the configuration is complete, click the icon in the toolbar of the node code editor to save the current configuration.