DataWorks：加工資料

建立dwd_log_info_di_odps表

雙擊dwd_log_info_di_odps表，在右側的編輯頁面單擊DDL，輸入下述建表語句。

CREATE TABLE IF NOT EXISTS dwd_log_info_di_odps (
 ip STRING COMMENT 'ip地址',
 uid STRING COMMENT '使用者ID',
 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'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;

建立dws_user_info_all_di_odps表

雙擊dws_user_info_all_di_odps表，在右側的編輯頁面單擊DDL，輸入下述建表語句。

CREATE TABLE IF NOT EXISTS dws_user_info_all_di_odps (
 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'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;

建立ads_user_info_1d_odps表

雙擊ads_user_info_1d_odps表，在右側的編輯頁面單擊DDL，輸入下述建表語句。

CREATE TABLE IF NOT EXISTS ads_user_info_1d_odps (
 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 '星座'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;    

配置dwd_log_info_di_odps節點

在商務程序面板，雙擊dwd_log_info_di_odps節點，利用建立的函數處理上遊表ods_raw_log_d_odps欄位的SQL代碼，並將其寫入dwd_log_info_di_odps表中，資料加工展示可參考：附錄：加工樣本。

1. 編輯代碼。

   -- 情境：以下SQL使用函數getregion對原始日誌資料中的ip進行解析，並通過正則等方式，將未經處理資料拆解為可分析欄位寫入並寫入dwd_log_info_di_odps表。
   --      本案例已為您準備好用於將IP解析為地區的函數getregion。
   -- 補充：
   --     1. 在DataWorks節點中使用函數前，您需要先將註冊函數所需資源上傳至DataWorks，再通過可視化方式使用該資源註冊函數。
   --        本案例註冊函數getregion所用的資源為ip2region.jar。
   --     2. DataWorks提供調度參數，可實現調度情境下，將每日增量資料寫入目標表對應業務分區。
   --        在實際開發情境下，您可通過${變數名}格式定義代碼變數，並在調度配置頁面通過為變數賦值調度參數的方式，實現調度情境下代碼動態入參。
   INSERT OVERWRITE TABLE dwd_log_info_di_odps 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_odps  
     WHERE dt ='${bizdate}'
   ) a;

2. 配置調度屬性。

   配置頁面單擊右側調度配置，可進入調度配置面板配置調度與節點資訊，詳情可參見節點調度配置。以下為配置的內容：

   配置項	配置內容	圖示
   調度參數	在調度參數配置參數： 參數名：bizdate。 參數值：$[yyyymmdd-1]。
   時間屬性	調度周期：日。 定時調度時間：00:30。 重跑屬性：運行成功或失敗後皆可重跑。 其餘參數保持預設即可。 說明 當前節點每日起調時間由商務程序虛擬節點workshop_start的定時調度時間控制，即每日00:30後才會調度。
   資源屬性	選擇準備環境階段建立的Serverless資源群組。
   調度依賴	通過代碼自動解析自動將產出ods_raw_log_d_odps表資料的ods_raw_log_d_odps節點設定為當前節點dwd_log_info_di_odps的上遊依賴。將dwd_log_info_di_odps表作為節點輸出，方便下遊查詢該表資料時自動掛上當前節點依賴。

   說明

   依賴的上遊節點：單擊代碼解析輸入輸出按鈕，會根據代碼中的表名解析到上遊節點配置的MaxCompute生產專案名稱.表名的輸出節點名作為上遊節點輸出名。

   本節點輸出名稱：單擊代碼解析輸入輸出按鈕，會根據代碼中的表名解析產生本節點產生MaxCompute生產專案名稱.表名格式的輸出名，輸出給下遊節點。

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

配置dws_user_info_all_di_odps節點

在商務程序面板中，雙擊dws_user_info_all_di_odps節點，編寫合并上遊表dwd_log_info_di_odps和ods_user_info_d_odps的SQL代碼，寫入dws_user_info_all_di_odps表中。

1. 編輯代碼。

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

2. 配置調度屬性。

   配置頁面單擊右側調度配置，可進入調度配置面板配置調度與節點資訊，詳情可參見節點調度配置。以下為配置的內容：

   配置項	配置內容	圖示
   調度參數	在調度參數配置參數： 參數名：bizdate 參數值：$[yyyymmdd-1]
   時間屬性	調度周期：日。 定時調度時間：00:30。 重跑屬性：運行成功或失敗後皆可重跑。 其餘參數保持預設即可。 說明 當前節點每日起調時間由商務程序虛擬節點workshop_start的定時調度時間控制，即每日00:30後才會調度。
   資源屬性	選擇準備環境階段建立的Serverless資源群組。
   調度依賴	通過代碼自動解析自動將產出dwd_log_info_di_odps和ods_user_info_d_odps表資料的節點dwd_log_info_di_odps、ods_user_info_d_odps作為當前節點dws_user_info_all_di_odps的上遊依賴。將節點產出表dws_user_info_all_di_odps作為節點輸出，方便下遊查詢該表資料時自動掛上當前節點依賴。

   說明

   依賴的上遊節點：單擊代碼解析輸入輸出按鈕，會根據代碼中的表名解析到上遊節點配置的MaxCompute生產專案名稱.表名的輸出節點名作為上遊節點輸出名。

   本節點輸出名稱：單擊代碼解析輸入輸出按鈕，會根據代碼中的表名解析產生本節點產生MaxCompute生產專案名稱.表名格式的輸出名，輸出給下遊節點。

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

配置ads_user_info_1d_odps節點

在商務程序面板中，雙擊ads_user_info_1d_odps節點，編寫SQL代碼，對上遊的dws_user_info_all_di_odps表進行加工處理，並將結果寫入ads_user_info_1d_odps表中。

1. 編輯代碼。

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

2. 配置調度屬性。

   配置頁面單擊右側調度配置，可進入調度配置面板配置調度與節點資訊，詳情可參見節點調度配置。以下為配置的內容：

   配置項	配置內容	圖示
   調度參數	在調度參數配置參數： 參數名：bizdate。 參數值：$[yyyymmdd-1]。
   時間屬性	調度周期：日。 定時調度時間：00:30。 重跑屬性：運行成功或失敗後皆可重跑。 其餘參數保持預設即可。 說明 當前節點每日起調時間由商務程序虛擬節點workshop_start的定時調度時間控制，即每日00:30後才會調度。
   資源屬性	選擇準備環境階段建立的Serverless資源群組。
   調度依賴	通過代碼自動解析自動根據節點血緣關係配置節點上下遊依賴關係，即將產出dws_user_info_all_1d_odps表資料的dws_user_info_all_1d_odps節點設定為當前節點ads_user_info_1d_odps的上遊。將節點產出表ads_user_info_1d作為節點輸出，方便下遊查詢該表資料時自動掛上當前節點依賴。

   說明

   依賴的上遊節點：單擊代碼解析輸入輸出按鈕，會根據代碼中的表名解析到上遊節點配置的MaxCompute生產專案名稱.表名的輸出節點名作為上遊節點輸出名。

   本節點輸出名稱：單擊代碼解析輸入輸出按鈕，會根據代碼中的表名解析產生本節點產生MaxCompute生產專案名稱.表名格式的輸出名，輸出給下遊節點。

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