北京雲棲大會workshop：《資料處理：資料建模與加工》篇

實驗背景介紹

瞭解更多2017雲棲大會·北京峰會 TechInsight & Workshop.

本手冊為雲棲大會Workshop《雲資料·大計算：快速搭建網際網路線上運營分析平臺》的《資料處理：資料建模與加工》篇而準備。主要闡述在使用DataWorks/MaxCompute過程中如何直讀TableStore中的日誌資料並進行加工、使用者畫像，學員可以根據本實驗手冊，去學習如何建立外部表、編寫SQL。

實驗涉及大資料產品

• 大資料計算服務 MaxCompute
• DataWorks DataWorks

實驗環境準備

必備條件：

• 開通大資料計算服務MaxCompute
• 建立DataWorks專案空間

進入DataWorks專案

確保阿里雲賬號處於登入狀態。

• step1：點選進入DataWorks/MaxCompute管理控制檯，選擇華北2 Region，點選已經建立的專案空間名稱，進入工作區。

新建資料表

本示例通過新建指令碼檔案的方式來建立外部表和內部表兩大部分。MaxCompute計算服務訪問 TableStore 資料需要有一個安全的授權通道。在這個問題上，MaxCompute結合了阿里雲的訪問控制服務（RAM）和令牌服務（STS）來實現對資料的安全訪問。

STS模式授權

當MaxCompute和TableStore的Owner是同一個賬號時，登入阿里雲賬號後 單擊此處完成一鍵授權。

1.建立外部表ots_user_trace_log表

• step1：點選資料開發，進入資料開發首頁中點選新建指令碼。

• step2：配置檔名稱為create_table_ddl，型別選擇為ODPS SQL，點選提交。

• step3：編寫DDL建立表語句。

DDL建表語句如下：

DROP TABLE IF EXISTS ots_user_trace_log;
CREATE EXTERNAL TABLE IF NOT EXISTS ots_user_trace_log 
(
    md5 STRING COMMENT `使用者uid的md5值前8位`
    ,uid STRING COMMENT `使用者uid`
    ,ts BIGINT COMMENT `使用者操作時間戳`
    ,ip STRING COMMENT `ip地址`
    ,status BIGINT COMMENT `伺服器返回狀態碼`
    ,bytes BIGINT COMMENT `返回給客戶端的位元組數`
    ,device STRING COMMENT `終端型號`
    ,system STRING COMMENT `系統版本ios xxx/android xxx`
    ,customize_event STRING COMMENT `自定義事件：登入/退出/購買/註冊/點選/後臺/切換使用者/瀏覽`
    ,use_time BIGINT COMMENT `APP單次使用時長，當事件為退出、後臺、切換使用者時有該項`
    ,customize_event_content STRING COMMENT `使用者關注內容資訊，在customize_event為瀏覽和評論時 包含該列`
)
STORED BY `com.aliyun.odps.TableStoreStorageHandler` 
WITH SERDEPROPERTIES ( 
--`odps.properties.rolearn`=`acs:ram::1604337383174619:role/aliyunodpsroleforyixiu`,
`tablestore.columns.mapping`=`:md5,:uid,:ts, ip,status,bytes,device,system,customize_event,use_time,customize_event_content`, 
`tablestore.table.name`=`user_trace_log` ) 
LOCATION `tablestore://<自己的TableStore例項名稱>.cn-beijing.ots-internal.aliyuncs.com` ;

LOCATION中的例項訪問地址可以在上一實驗中的TableStore管控臺>例項列表>管理>例項詳情 中獲取，進入TableStore管控臺。

彈出關於SQL費用預估的彈窗，在本workshop中可以忽略。

• step4：選擇需要執行的SQL語句，點選執行，直至日誌資訊返回成功表示表建立成功。

• step4：可以使用desc語法來確認建立表是否成功。

• step5：點選儲存，儲存編寫的SQL建表語句。

2.新建ods_user_trace_log表

建立表方法同上，本小節附建表語句:

DROP TABLE IF EXISTS ods_user_trace_log;
CREATE TABLE IF NOT EXISTS ods_user_trace_log (
    md5 STRING COMMENT `使用者uid的md5值前8位`,
    uid STRING COMMENT `使用者uid`,
    ts BIGINT COMMENT `使用者操作時間戳`,
    ip STRING COMMENT `ip地址`,
    status BIGINT COMMENT `伺服器返回狀態碼`,
    bytes BIGINT COMMENT `返回給客戶端的位元組數`,
    device STRING COMMENT `終端型號`,
    system STRING COMMENT `系統版本ios xxx/android xxx`,
    customize_event STRING COMMENT `自定義事件：登入/退出/購買/註冊/點選/後臺/切換使用者/瀏覽`,
    use_time BIGINT COMMENT `APP單次使用時長，當事件為退出、後臺、切換使用者時有該項`,
    customize_event_content STRING COMMENT `使用者關注內容資訊，在customize_event為瀏覽和評論時 包含該列`
)
PARTITIONED BY (
    dt STRING
);

3.新建dw_user_trace_log表

建立表方法同上，本小節附建表語句:

DROP TABLE IF EXISTS dw_user_trace_log;
CREATE TABLE IF NOT EXISTS dw_user_trace_log (
    uid STRING COMMENT `使用者uid`,
    region STRING COMMENT `地域，根據ip得到`,
    device_brand string comment `裝置品牌`,
    device STRING COMMENT `終端型號`,
    system_type STRING COMMENT `系統型別，Android、IOS、ipad、Windows_phone`,
    customize_event STRING COMMENT `自定義事件：登入/退出/購買/註冊/點選/後臺/切換使用者/瀏覽`,
    use_time BIGINT COMMENT `APP單次使用時長，當事件為退出、後臺、切換使用者時有該項`,
    customize_event_content STRING COMMENT `使用者關注內容資訊，在customize_event為瀏覽和評論時 包含該列`
)
PARTITIONED BY (
    dt STRING
);

4.新建rpt_user_trace_log表

建立表方法同上，本小節附建表語句:

DROP TABLE IF EXISTS rpt_user_trace_log;
CREATE TABLE IF NOT EXISTS rpt_user_trace_log (
    country STRING COMMENT `國家`,
    province STRING COMMENT `省份`,
    city STRING COMMENT `城市`,
    device_brand string comment `裝置品牌`,
    device STRING COMMENT `終端型號`,
    system_type STRING COMMENT `系統型別，Android、IOS、ipad、Windows_phone`,
    customize_event STRING COMMENT `自定義事件：登入/退出/購買/註冊/點選/後臺/切換使用者/瀏覽`,
    use_time BIGINT COMMENT `APP單次使用時長，當事件為退出、後臺、切換使用者時有該項`,
    customize_event_content STRING COMMENT `使用者關注內容資訊，在customize_event為瀏覽和評論時 包含該列`,
    pv bigint comment `瀏覽量`,
    uv bigint comment `獨立訪客`
)
PARTITIONED BY (
    dt STRING
);

上述三張表建立成功後，儲存指令碼檔案。

工作流設計

• step1：點選新建 > 新建任務。
  
• step2：在新建任務彈出框中，選擇任務型別工作流任務。
  

向畫布中拖入三個ODPS SQL節點，依次命名為ods_user_trace_log、dw_user_trace_log、rpt_user_trace_log，並配置依賴關係如下：

新增資源並建立自定義函式

將ip地址轉化為地域的自定義函式，有興趣同學可以點選檢視詳情。

• step1：點選下載getaddr.jar和ip.dat.
• step2：切換至資源管理tab頁，點選上傳按鈕，分別將上述兩個資原始檔進行上傳。

• step3：點選選擇檔案，選擇已經下載到本地的getaddr.jar。

• step4：點選提交。

ip.dat檔案的上傳方法同上，只是在選擇型別為file。

• step5：切換至函式管理tab，點選建立函式按鈕。

• step6：資源選擇getaddr.jar和ip.dat，其他配置項如下所示。

配置項說明如下：

• 函式名：getregion
• 類名：odps.test.GetAddr
• 資源：getaddr.jar和ip.dat
• step7：點選提交。

配置ODPS SQL節點

1）配置ods_user_trace_log節點：

• step1：雙擊ods_user_trace_log節點，進入節點配置介面，編寫處理邏輯。

附SQL邏輯如下：

insert overwrite table ods_user_trace_log partition (dt=${bdp.system.bizdate})
select 
    md5,
    uid ,
    ts,
    ip,
    status,
    bytes,
    device,
    system,
    customize_event,
    use_time,
    customize_event_content
    from ots_user_trace_log
    where to_char(FROM_UNIXTIME(ts),`yyyymmdd`)=${bdp.system.bizdate};

• step2：點選儲存。

• step3：點選返回，返回至工作流開發皮膚。

2）配置dw_user_trace_log節點：

• step1：雙擊dw_user_trace_log節點，進入節點配置介面，編寫處理邏輯。

附SQL語句如下：

INSERT OVERWRITE TABLE dw_user_trace_log PARTITION (dt=${bdp.system.bizdate})
SELECT uid, getregion(ip) AS region
    , CASE 
        WHEN TOLOWER(device) RLIKE `xiaomi` THEN `xiaomi`
        WHEN TOLOWER(device) RLIKE `meizu` THEN `meizu`
        WHEN TOLOWER(device) RLIKE `huawei` THEN `huawei`
        WHEN TOLOWER(device) RLIKE `iphone` THEN `iphone`
        WHEN TOLOWER(device) RLIKE `vivo` THEN `vivo`
        WHEN TOLOWER(device) RLIKE `honor` THEN `honor`
        WHEN TOLOWER(device) RLIKE `samsung` THEN `samsung`
        WHEN TOLOWER(device) RLIKE `leeco` THEN `leeco`
        WHEN TOLOWER(device) RLIKE `ipad` THEN `ipad`
        ELSE `unknown`
    END AS device_brand, device
    , CASE 
        WHEN TOLOWER(system) RLIKE `android` THEN `android`
        WHEN TOLOWER(system) RLIKE `ios` THEN `ios`
        ELSE `unknown`
    END AS system_type, customize_event, use_time, customize_event_content
FROM ods_user_trace_log
WHERE dt = ${bdp.system.bizdate};

• step2：點選儲存。
• step3：點選返回，返回至工作流開發皮膚。

配置rpt_user_trace_log節點

• step1：雙擊進入rpt_user_trace_log節點進入配置介面。

附SQL程式碼如下：

INSERT OVERWRITE TABLE rpt_user_trace_log PARTITION (dt=${bdp.system.bizdate})
SELECT split_part(split_part(region, `,`, 1),`[`,2) AS country
    , trim(split_part(region, `,`, 2)) AS province
    , trim(split_part(region, `,`, 3)) AS city
    , MAX(device_brand), MAX(device)
    , MAX(system_type), MAX(customize_event)
    , FLOOR(AVG(use_time / 60))
    , MAX(customize_event_content), COUNT(uid) AS pv
    , COUNT(DISTINCT uid) AS uv
FROM dw_user_trace_log
WHERE dt = ${bdp.system.bizdate}
GROUP BY uid, 
    region;

• step2：點選儲存。
• step3：點選返回，返回至工作流開發皮膚。

提交工作流任務

• step1：點選提交，提交已配置的工作流任務。

• step2：在變更節點列表彈出框中點選確定提交。

提交成功後工作流任務處於只讀狀態，如下：

測試執行任務

• step1：進入運維中心 > 任務列表 > 週期任務，找到你提交的workshop工作流任務並展開。

• step2：右鍵工作流任務名稱，並選擇點選測試，選擇需要執行的業務日期，點選確認。

• step3：跳轉至測試例項頁面，點選例項名稱並展開測試的DAG圖。

• step4：可以點選右上角的重新整理按鈕進行檢視節點執行情況，直至所有節點為綠色（執行成功）。

確認資料是否成功寫入MaxCompute相關表

• step1：返回到create_table_ddl指令碼檔案中。
• step2：編寫並執行sql語句檢視rpt_user_trace_log資料情況。。

附錄：SQL語句如下。

---檢視rpt_user_trace_log資料情況
select * from rpt_user_trace_log limit 10;

進入>>資料分析教程