DataWorks:Process data

1. Configure the node code

Double-click the dwd_log_info_di_starrocks node to go to the configuration tab of the node. Write the following statements:

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");

-- In this example, field data is dynamically partitioned based on the dt field. To prevent the node from rerunning and repeatedly writing data, the following SQL statement is used to delete the existing destination partitions before each processing operation. 
ALTER TABLE dwd_log_info_di_starrocks DROP PARTITION IF EXISTS p${var} FORCE;

-- Scenario: The following SQL statements use the getregion function to parse the IP address in the raw log data, and use regular expressions to split the raw data to analyzable fields and write the fields to the dwd_log_info_di_starrocks table. 
-- Note:
--     1. Before you can use a UDF on a DataWorks node, you must register a function. 
--     2. You can configure scheduling parameters for nodes in DataWorks to write incremental data to the related partition in the desired table every day in the scheduling scenario. 
--        In actual development scenarios, you can define variables in the code of a node in the ${Variable name} format and assign scheduling parameters to the variables on the Properties tab of the configuration tab of the node. This way, the values of scheduling parameters can be dynamically replaced in the node code based on the configurations of the scheduling parameters. 
INSERT INTO dwd_log_info_di_starrocks 
SELECT 
    uid
    , ip  
    , time
    , status
    , bytes 
    , getregion(ip) as region-- Obtain the region by using the UDF based on 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 for the node

In the right-side navigation pane of the configuration tab of the dwd_log_info_di_starrocks node, click General. The following table describes the parameters.

3. Save the configurations

In this example, other required configuration items can be configured based on your business requirements. After the configuration is complete, click the icon in the top toolbar on the configuration tab of the node to save the node configurations.

Configure the node code

Double-click the dws_user_info_all_di_starrocks node to go to the configuration tab of the node. Write the following statements:

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");

-- In this example, field data is dynamically partitioned based on the dt field. To prevent the node from rerunning and repeatedly writing data, the following SQL statement is used to delete the existing destination partitions before each processing operation. 
ALTER TABLE dws_user_info_all_di_starrocks DROP PARTITION IF EXISTS p${var} FORCE;


-- Scenario: Merge the processed log data stored in the dwd_log_info_di_starrocks table and the basic user information stored in the ods_user_info_d_starrocks table and write the merged data to the dws_user_info_all_di_starrocks table. 
-- Note: You can configure scheduling parameters for nodes in DataWorks to write incremental data to the related partition in the desired table every day in the scheduling scenario. 
-- In actual development scenarios, you can define variables in the code of a node in the ${Variable name} format and assign scheduling parameters to the variables on the Properties tab of the configuration tab of the node. This way, the values of scheduling parameters can be dynamically replaced in the node code based on the configurations of the scheduling parameters. 
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 for the node

In the right-side navigation pane of the configuration tab of the dws_user_info_all_di_starrocks node, click General. The following table describes the parameters.

3. Save the configurations

In this example, other required configuration items can be configured based on your business requirements. After the configuration is complete, click the icon in the top toolbar on the configuration tab of the node to save the node configurations.

1. Configure the node code

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");

-- In this example, field data is dynamically partitioned based on the dt field. To prevent the node from rerunning and repeatedly writing data, the following SQL statement is used to delete the existing destination partitions before each processing operation. 
ALTER TABLE ads_user_info_1d_starrocks DROP PARTITION IF EXISTS p${var} FORCE;

-- Scenario: The following SQL statements are used to further process data in the dws_user_info_d_all_di_starrocks wide table that stores the website access information about users to generate basic user profile data, and write the data to the ads_user_info_1d_starrocks table. 
-- Note: You can configure scheduling parameters for nodes in DataWorks to write incremental data to the related partition in the desired table every day in the scheduling scenario. 
-- -- In actual development scenarios, you can define variables in the code of a node in the ${Variable name} format and assign scheduling parameters to the variables on the Properties tab of the configuration tab of the node. This way, the values of scheduling parameters can be dynamically replaced in the node code based on the configurations of the scheduling parameters. 
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 for the node

In the right-side navigation pane of the configuration tab of the ads_user_info_1d_starrocks node, click General. The following table describes the parameters.

3. Save the configurations

In this example, other required configuration items can be configured based on your business requirements. After the configuration is complete, click the icon in the top toolbar on the configuration tab of the node to save the node configurations.