DataWorks：加工資料

1. 編輯代碼

在商務程序面板中，雙擊開啟dwd_log_info_di節點，並配置如下代碼，DataWorks通過${變數名}格式定義代碼變數。其中代碼中的${bizdate}為代碼變數，該變數將在後續步驟2中為其賦值。

-- 情境：以下SQL使用函數getregion對原始日誌資料中的ip進行解析，並通過正則等方式，將未經處理資料拆解為可分析欄位寫入並寫入dwd_log_info_di表。
--      本案例已為您準備好用於將IP解析為地區的函數getregion。
-- 補充：
--     1. 在DataWorks節點中使用函數前，您需要先將註冊函數所需資源上傳至DataWorks，再通過可視化方式使用該資源註冊函數，詳見：https://www.alibabacloud.com/help/zh/dataworks/user-guide/create-and-use-maxcompute-resources
--        本案例註冊函數getregion所用的資源為ip2region.jar。
--     2. DataWorks提供調度參數，可實現調度情境下，將每日增量資料寫入目標表對應業務分區。
--        在實際開發情境下，您可通過${變數名}格式定義代碼變數，並在調度配置頁面通過為變數賦值調度參數的方式，實現調度情境下代碼動態入參。
INSERT OVERWRITE TABLE dwd_log_info_di PARTITION (dt='${bizdate}')
SELECT ip 
  , uid
  , time
  , status
  , bytes 
  , getregion(ip) AS region --使用自訂UDF通過IP得到地區。
  , regexp_substr(request, '(^[^ ]+ )') AS method --通過正則把request差分為3個欄位。
  , regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') AS url
  , regexp_substr(request, '([^ ]+$)') AS protocol 
  , regexp_extract(referer, '^[^/]+://([^/]+){1}') AS referer --通過正則清晰refer，得到更精準的URL。
  , CASE
    WHEN TOLOWER(agent) RLIKE 'android' THEN 'android' --通過agent得到終端資訊和訪問形式。
    WHEN TOLOWER(agent) RLIKE 'iphone' THEN 'iphone'
    WHEN TOLOWER(agent) RLIKE 'ipad' THEN 'ipad'
    WHEN TOLOWER(agent) RLIKE 'macintosh' THEN 'macintosh'
    WHEN TOLOWER(agent) RLIKE 'windows phone' THEN 'windows_phone'
    WHEN TOLOWER(agent) RLIKE 'windows' THEN 'windows_pc'
    ELSE 'unknown'
  END AS device
  , CASE
    WHEN TOLOWER(agent) RLIKE '(bot|spider|crawler|slurp)' THEN 'crawler'
    WHEN TOLOWER(agent) RLIKE 'feed'
    OR regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') RLIKE 'feed' THEN 'feed'
    WHEN TOLOWER(agent) NOT RLIKE '(bot|spider|crawler|feed|slurp)'
    AND agent RLIKE '^[Mozilla|Opera]'
    AND regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') NOT RLIKE 'feed' THEN 'user'
    ELSE 'unknown'
  END AS identity
  FROM (
    SELECT SPLIT(col, '##@@')[0] AS ip
    , SPLIT(col, '##@@')[1] AS uid
    , SPLIT(col, '##@@')[2] AS time
    , SPLIT(col, '##@@')[3] AS request
    , SPLIT(col, '##@@')[4] AS status
    , SPLIT(col, '##@@')[5] AS bytes
    , SPLIT(col, '##@@')[6] AS referer
    , SPLIT(col, '##@@')[7] AS agent
  FROM ods_raw_log_d 
  WHERE dt ='${bizdate}'
) a;

2. 配置調度屬性

通過以下配置實現調度情境下，每日00:15待上遊ods_raw_log_d節點將儲存於OSS的user_log.txt資料同步至MaxCompute的ods_raw_log_d表後，可觸發當前dwd_log_info_di節點對ods_raw_log_d表資料進行加工，加工結果寫入dwd_log_info_di表對應業務時間分區。

• 配置調度參數：為代碼中的變數bizdate賦值$[yyyymmdd-1]，擷取前一天的日期。

• 配置定時調度時間：配置調度周期為日，無需單獨配置當前節點定時調度時間，當前節點每日調起時間由商務程序根節點WorkShop的定時調度時間控制，即每日00:15後才會調度。

• 配置依賴關係：通過代碼自動解析自動將產出ods_raw_log_d表資料的ods_raw_log_d節點設定為當前節點dwd_log_info_di的上遊依賴。將dwd_log_info_di表作為節點輸出，方便下遊查詢該表資料時自動掛上當前節點依賴。

  說明

  DataWorks節點輸出是其他節點與當前節點建立依賴關係的媒介。DataWorks通過將上遊節點的節點輸出作為下遊節點的節點輸入，形成節點間的依賴關係。

  • 系統會為每個節點自動產生兩個輸出名，格式分別為：projectName.randomNumber_out、projectName.nodeName_out。

  • 如果使用自動解析功能，系統將根據代碼解析結果為節點產生輸出名，格式為：projectName.tableName。

3. 儲存配置

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

1. 編輯代碼

在商務程序面板中，雙擊開啟dws_user_info_all_di節點，並配置如下代碼，DataWorks通過${變數名}格式定義代碼變數。其中代碼中的${bizdate}為代碼變數，該變數將在後續步驟2中為其賦值。

-- 情境：將加工後的日誌資料dwd_log_info_di與使用者基本資料資料ods_user_info_d匯總寫入dws_user_info_all_di表。
-- 補充：DataWorks提供調度參數，可實現調度情境下，將每日增量資料寫入目標表對應業務分區。
--      在實際開發情境下，您可通過${變數名}格式定義代碼變數，並在調度配置頁面通過為變數賦值調度參數的方式，實現調度情境下代碼動態入參。
INSERT OVERWRITE TABLE dws_user_info_all_di PARTITION (dt='${bizdate}')
SELECT COALESCE(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
FROM (
  SELECT *
  FROM dwd_log_info_di 
  WHERE dt = '${bizdate}'
) a
LEFT OUTER JOIN (
  SELECT *
  FROM ods_user_info_d
  WHERE dt = '${bizdate}'
) b
ON a.uid = b.uid;

2. 配置調度屬性

通過以下配置實現調度情境下，每日00:15待上遊MySQL使用者基本資料通過Data Integration同步至MaxCompute的ods_user_info_d表，以及dwd_log_info_di節點對ods_raw_log_d表加工完成後，將其匯總寫入dws_user_info_all_di表對應業務分區。

• 配置調度參數：為代碼中的變數bizdate賦值$[yyyymmdd-1]，擷取前一天的日期。

  

• 配置定時調度時間：配置調度周期為日，無需單獨配置當前節點定時調度時間，當前節點每日起調時間由商務程序根節點WorkShop的定時調度時間控制，即每日00:15分後才會調度。

• 配置依賴關係：通過代碼自動解析自動將產出dwd_log_info_di和ods_user_info_d表資料的節點dwd_log_info_di、ods_user_info_d作為當前節點dws_user_info_all_di的上遊依賴。將節點產出表dws_user_info_all_di作為節點輸出，方便下遊查詢該表資料時自動掛上當前節點依賴。

  

3. 儲存配置

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

1. 編輯代碼

在商務程序面板中，雙擊開啟ads_user_info_1d節點，並配置如下代碼，DataWorks通過${變數名}格式定義代碼變數。其中代碼中的${bizdate}為代碼變數，該變數將在後續步驟2中為其賦值。

-- 情境：以下SQL用於對使用者訪問資訊寬表dws_user_info_all_di進一步加工產出基本的使用者畫像資料寫入ads_user_info_1d表。
-- 補充：DataWorks提供調度參數，可實現調度情境下，將每日增量資料寫入目標表對應業務分區。
--      在實際開發情境下，您可通過${變數名}格式定義代碼變數，並在調度配置頁面通過為變數賦值調度參數的方式，實現調度情境下代碼動態入參。
INSERT OVERWRITE TABLE ads_user_info_1d PARTITION (dt='${bizdate}')
SELECT uid
  , MAX(region)
  , MAX(device)
  , COUNT(0) AS pv
  , MAX(gender)
  , MAX(age_range)
  , MAX(zodiac)
FROM dws_user_info_all_di
WHERE dt = '${bizdate}'
GROUP BY uid; 

2. 配置調度屬性

為實現周期調度，我們需要定義任務周期調度的相關屬性。

• 配置調度參數：為代碼中的變數bizdate賦值$[yyyymmdd-1]，擷取前一天的日期。

  

• 配置定時調度時間：無需單獨配置當前節點定時調度時間，當前節點每日起調時間由商務程序根節點WorkShop的定時調度時間控制，即每日00:15分後才會調度。

• 配置依賴關係：通過代碼自動解析自動根據節點血緣關係配置節點上下遊依賴關係，即將產出dws_user_info_all_1d表資料的dws_user_info_all_1d節點設定為當前節點ads_user_info_1d的上遊。將節點產出表ads_user_info_1d作為節點輸出，方便下遊查詢該表資料時自動掛上當前節點依賴。

3. 儲存配置

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