DataWorks：加工資料

1. 配置代碼

雙擊dwd_log_info_di_starrocks節點，進入節點配置頁面，編寫如下語句。

CREATE TABLE IF NOT EXISTS dwd_log_info_di_starrocks (
    uid STRING COMMENT '使用者ID',
    ip STRING COMMENT 'ip地址',
    TIME STRING COMMENT '時間yyyymmddhh:mi:ss',
    status STRING COMMENT '伺服器返回狀態代碼',
    bytes STRING COMMENT '返回給用戶端的位元組數',
    region STRING COMMENT '地區，根據ip得到',
    method STRING COMMENT 'http請求類型',
    url STRING COMMENT 'url',
    protocol STRING COMMENT 'http協議版本號碼',
    referer STRING COMMENT '來源url',
    device STRING COMMENT '終端類型 ',
    identity STRING COMMENT '訪問類型 crawler feed user unknown',
    dt DATE NOT NULL COMMENT '時間'
) DUPLICATE KEY(uid) 
COMMENT '使用者行為分析案例-網站訪問日誌明細表' 
PARTITION BY(dt) 
PROPERTIES ("replication_num" = "1");

-- 本樣本按欄位dt動態分區，為避免節點重跑資料重複寫入，通過以下命令實現每次加工前刪除已有目標資料分割。
ALTER TABLE dwd_log_info_di_starrocks DROP PARTITION IF EXISTS p${var} FORCE;

--情境：以下SQL使用函數getregion對原始日誌資料中的ip進行解析，並通過正則等方式，將未經處理資料拆解為可分析欄位寫入並寫入dwd_log_info_di_starrocks表。
--補充：
--     1. 在DataWorks節點中使用自訂函數前，您需要先註冊函數。
--     2. DataWorks提供調度參數，可實現調度情境下，將每日增量資料寫入目標表對應業務分區。
--        在實際開發情境下，您可通過${變數名}格式定義代碼變數，並在調度配置頁面通過為變數賦值調度參數的方式，實現調度情境下代碼動態入參。
INSERT INTO dwd_log_info_di_starrocks 
SELECT 
    uid
    , ip  
    , time
    , status
    , bytes 
    , getregion(ip) as region--使用自訂UDF通過IP得到地區
    ,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. 配置調度

單擊dwd_log_info_di_starrocks節點編輯頁右側調度配置。配置詳情如下：

3. 儲存配置

本案例其他必填配置項，您可按需自行配置，配置完成後，在節點代碼編輯頁面，單擊工具列中的按鈕，儲存當前配置。

編輯代碼

雙擊dws_user_info_all_di_starrocks節點，進入節點配置頁面，編寫如下語句。

CREATE TABLE IF NOT EXISTS dws_user_info_all_di_starrocks (
    uid STRING COMMENT '使用者ID',
    gender STRING COMMENT '性別',
    age_range STRING COMMENT '年齡段',
    zodiac STRING COMMENT '星座',
    region STRING COMMENT '地區，根據ip得到',
    device STRING COMMENT '終端類型 ',
    identity STRING COMMENT '訪問類型 crawler feed user unknown',
    method STRING COMMENT 'http請求類型',
    url STRING COMMENT 'url',
    referer STRING COMMENT '來源url',
    TIME STRING COMMENT '時間yyyymmddhh:mi:ss',
    dt DATE NOT NULL COMMENT '時間'
) DUPLICATE KEY(uid) 
COMMENT '使用者行為分析案例-使用者網站訪問資訊寬表' 
PARTITION BY(dt) 
PROPERTIES ("replication_num" = "1");

-- 本樣本按欄位dt動態分區，為避免節點重跑資料重複寫入，通過以下命令實現每次加工前刪除已有目標資料分割。
ALTER TABLE dws_user_info_all_di_starrocks DROP PARTITION IF EXISTS p${var} FORCE;


-- 情境：將加工後的日誌資料dwd_log_info_di_starrocks 與使用者基本資料資料ods_user_info_d_starrocks匯總寫入dws_user_info_all_di_starrocks表。
-- 補充：DataWorks提供調度參數，可實現調度情境下，將每日增量資料寫入目標表對應業務分區。
--      在實際開發情境下，您可通過${變數名}格式定義代碼變數，並在調度配置頁面通過為變數賦值調度參數的方式，實現調度情境下代碼動態入參。
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}';

配置調度

單擊dws_user_info_all_di_starrocks節點編輯頁面右側的調度配置。配置詳情如下：

3. 儲存配置

本案例其他必填配置項，您可按需自行配置，配置完成後，在節點代碼編輯頁面，單擊工具列中的按鈕，儲存當前配置。

1. 編輯代碼

CREATE TABLE IF NOT EXISTS ads_user_info_1d_starrocks (
uid STRING COMMENT '使用者ID',
region STRING COMMENT '地區，根據ip得到',
device STRING COMMENT '終端類型 ',
pv BIGINT COMMENT 'pv',
gender STRING COMMENT '性別',
age_range STRING COMMENT '年齡段',
zodiac STRING COMMENT '星座',
dt DATE NOT NULL COMMENT '時間'
) DUPLICATE KEY(uid) 
COMMENT '使用者行為分析案例-使用者畫像資料' 
PARTITION BY(dt) 
PROPERTIES ("replication_num" = "1");

-- 本樣本按欄位dt動態分區，為避免節點重跑資料重複寫入，通過以下命令實現每次加工前刪除已有目標資料分割。
ALTER TABLE ads_user_info_1d_starrocks DROP PARTITION IF EXISTS p${var} FORCE;

-- 情境：以下SQL用於對使用者訪問資訊寬表dws_user_info_d_all_di_starrocks 進一步加工產出基本的使用者畫像資料寫入ads_user_info_1d_starrocks 表。
-- 補充：DataWorks提供調度參數，可實現調度情境下，將每日增量資料寫入目標表對應業務分區。
-- 在實際開發情境下，您可通過${變數名}格式定義代碼變數，並在調度配置頁面通過為變數賦值調度參數的方式，實現調度情境下代碼動態入參。
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. 配置調度

單擊ads_user_info_1d_starrocks節點編輯頁右側的調度配置。配置詳情如下：

3. 儲存配置

本案例其他必填配置項，您可按需自行配置，配置完成後，在節點代碼編輯頁面，單擊工具列中的按鈕，儲存當前配置。