DataWorks：加工資料

配置代碼

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

-- 情境：以下SQL為Spark SQL，通過Spark SQL函數將載入至Spark中的ods_raw_log_d_spark按"##@@"進行切分後產生多個欄位，並寫入新表dwd_log_info_di_spark。
-- 補充：
--      DataWorks提供調度參數，可實現調度情境下，將每日增量資料寫入目標表對應的業務分區內。
--      在實際開發情境下，您可通過${變數名}格式定義代碼變數，並在調度配置頁面通過變數賦值調度參數的方式，實現調度情境下代碼動態入參。  

CREATE TABLE IF NOT EXISTS dwd_log_info_di_spark (
  ip STRING COMMENT 'ip地址',
  uid STRING COMMENT '使用者ID',
  tm STRING COMMENT '時間yyyymmddhh:mi:ss',
  status STRING COMMENT '伺服器返回狀態代碼',
  bytes STRING COMMENT '返回給用戶端的位元組數',
  method STRING COMMENT'要求方法',
  url STRING COMMENT 'url',
  protocol STRING COMMENT '協議',
  referer STRING ,
  device STRING,
  identity STRING
  )

PARTITIONED BY (
  dt STRING
);

ALTER TABLE dwd_log_info_di_spark ADD IF NOT EXISTS PARTITION (dt = '${bizdate}');

INSERT OVERWRITE TABLE dwd_log_info_di_spark PARTITION (dt='${bizdate}')
SELECT ip, 
       uid, 
       tm, 
       status, 
       bytes, 
       regexp_extract(request, '(^[^ ]+) .*', 1) AS method,
       regexp_extract(request, '^[^ ]+ (.*) [^ ]+$', 1) AS url,
       regexp_extract(request, '.* ([^ ]+$)', 1) AS protocol,
       regexp_extract(referer, '^[^/]+://([^/]+){1}', 1) AS referer,
       CASE 
           WHEN lower(agent) RLIKE 'android' THEN 'android' 
           WHEN lower(agent) RLIKE 'iphone' THEN 'iphone' 
           WHEN lower(agent) RLIKE 'ipad' THEN 'ipad' 
           WHEN lower(agent) RLIKE 'macintosh' THEN 'macintosh' 
           WHEN lower(agent) RLIKE 'windows phone' THEN 'windows_phone' 
           WHEN lower(agent) RLIKE 'windows' THEN 'windows_pc' 
           ELSE 'unknown' 
       END AS device, 
       CASE 
           WHEN lower(agent) RLIKE '(bot|spider|crawler|slurp)' THEN 'crawler' 
           WHEN lower(agent) RLIKE 'feed' OR regexp_extract(request, '^[^ ]+ (.*) [^ ]+$', 1) RLIKE 'feed' THEN 'feed' 
           WHEN lower(agent) NOT RLIKE '(bot|spider|crawler|feed|slurp)' AND agent RLIKE '^(Mozilla|Opera)' AND regexp_extract(request, '^[^ ]+ (.*) [^ ]+$', 1) 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 tm, 
        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_spark
    WHERE dt = '${bizdate}'
) a;

配置調度屬性

儲存配置

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

驗證日誌表拆分情況

在確保上遊節點以及本節點運行成功的情況下，在左側導覽列的臨時查詢中建立EMR Spark SQL臨時查詢，編寫SQL查看EMR Spark SQL節點建立的表是否正常產出。

-- 您需要將分區過濾條件更新為您當前操作的實際業務日期。例如，任務啟動並執行日期為20230222，則業務日期為20230221，即任務運行日期的前一天。
SELECT * FROM dwd_log_info_di_spark WHERE dt ='業務日期';

編輯代碼

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

-- 情境：以下SQL為Spark SQL，通過uid將dwd_log_info_di_spark和ods_user_info_d_spark進行關聯，並寫入對應的dt分區。
-- 補充：
--      DataWorks提供調度參數，可實現調度情境下，將每日增量資料寫入目標表對應的業務分區內。
--      在實際開發情境下，您可通過${變數名}格式定義代碼變數，並在調度配置頁面通過變數賦值調度參數的方式，實現調度情境下代碼動態入參。
CREATE TABLE IF NOT EXISTS dws_user_info_all_di_spark (
    uid        STRING COMMENT '使用者ID',
    gender     STRING COMMENT '性別',
    age_range  STRING COMMENT '年齡段',
    zodiac     STRING COMMENT '星座',
    device     STRING COMMENT '終端類型 ',
    method     STRING COMMENT 'http請求類型',
    url        STRING COMMENT 'url',
    `time`     STRING COMMENT '時間yyyymmddhh:mi:ss'
)
PARTITIONED BY (dt STRING);

--添加分區
ALTER TABLE dws_user_info_all_di_spark ADD IF NOT EXISTS PARTITION (dt = '${bizdate}');

--插入user表與日誌表資料
INSERT OVERWRITE TABLE dws_user_info_all_di_spark PARTITION (dt = '${bizdate}')
SELECT 
    COALESCE(a.uid, b.uid) AS uid,
    b.gender AS gender,    
    b.age_range AS age_range,
    b.zodiac AS zodiac,
    a.device AS device,
    a.method AS method,
    a.url AS url,
    a.tm
FROM (
  SELECT * 
  FROM dwd_log_info_di_spark 
  WHERE dt='${bizdate}'
) a
LEFT OUTER JOIN (
  SELECT * 
  FROM ods_user_info_d_spark 
  WHERE dt='${bizdate}'
) b
ON 
    a.uid = b.uid;

配置調度屬性

儲存配置

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

驗證使用者日誌明細表資料情況

在確保上遊節點以及本節點運行成功的情況下，在左側導覽列的臨時查詢中建立EMR Spark SQL臨時查詢，編寫SQL查看EMR Spark SQL節點建立的表是否正常產出。

-- 您需要將分區過濾條件更新為您當前操作的實際業務日期。例如，任務啟動並執行日期為20240808，則業務日期為20240807，即任務運行日期的前一天。
SELECT * FROM dws_user_info_all_di_spark WHERE dt ='業務日期';

編輯代碼

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

-- 情境：以下SQL為Spark SQL，通過Spark SQL函數將Spark中的dws_user_info_all_di_spark表進一步的加工，並寫入新表ads_user_info_1d_spark。
-- 補充：
--      DataWorks提供調度參數，可實現調度情境下，將每日增量資料寫入目標表對應的業務分區內。
--      在實際開發情境下，您可通過${變數名}格式定義代碼變數，並在調度配置頁面通過變數賦值調度參數的方式，實現調度情境下代碼動態入參。
CREATE TABLE IF NOT EXISTS ads_user_info_1d_spark (
  uid STRING COMMENT '使用者ID',
  device STRING COMMENT '終端類型 ',
  pv BIGINT COMMENT 'pv',
  gender STRING COMMENT '性別',
  age_range STRING COMMENT '年齡段',
  zodiac STRING COMMENT '星座'
)
PARTITIONED BY (
  dt STRING
);
ALTER TABLE ads_user_info_1d_spark ADD IF NOT EXISTS PARTITION (dt='${bizdate}');
INSERT OVERWRITE TABLE ads_user_info_1d_spark PARTITION (dt='${bizdate}')
SELECT uid
  , MAX(device)
  , COUNT(0) AS pv
  , MAX(gender)
  , MAX(age_range)
  , MAX(zodiac)
FROM dws_user_info_all_di_spark
WHERE dt = '${bizdate}'
GROUP BY uid;

調度配置

儲存配置

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

驗證使用者畫像表資料情況

在確保上遊節點以及本節點運行成功的情況下，在左側導覽列的臨時查詢中建立EMR Spark SQL臨時查詢，編寫SQL查看該節點建立的表是否正常產出。

-- 您需要將分區過濾條件更新為您當前操作的實際業務日期。例如，任務啟動並執行日期為20230222，則業務日期為20230221，即任務運行日期的前一天。
SELECT * FROM ads_user_info_1d_spark WHERE dt ='業務日期';