418      阿裏雲  技術社區[雲棲]

MaxCompute實踐分析

概述
本文主要是記錄如何通過數加MaxCompute和大數據開發套件兩個產品實現簡單的電影數據分析。

目標
希望了解每天市場上最火爆的電影是哪些，表現如何。通過對影視及票房數據集（dwd_product_movie_basic_info電影基本信息 和 ods_product_movie_box票房基本信息）統計分析出每天每個國家/地區累計票房最高的10個電影並且展現這些電影的導演、主演和累計票房。

數據說明
直接使用了數加平台公開數據集上的影視及票房數據集。該數據集從2016 年12 月13 日開始，每日早10 點前進行全量更新。

兩個表的具體信息如下：

• 電影基本信息 dwd_product_movie_basic_info

數據樣例

Select * from public_data.dwd_product_movie_basic_info where ds ='20170119' limit 10;

• 票房基本信息 ods_product_movie_box

數據樣例

Select * from public_data.ods_product_movie_box where ds ='20170119' limit 10;

** 目標分析**

• 需要統計出每個國家/地區累計票房（萬）最高的10個電影並且展現這些電影的導演和主演
• 包括的字段是 [ 製片地區/國家 、電影名稱、 排名、累計票房（萬）、導演、主演]

** 現狀分析**

• 電影基本信息表中包含了 [ 製片地區/國家、電影名稱、 導演、主演 ] -票房基本信息表中包含了 [ 影片名、累計票房（萬）], 其中的 [ 排名 ] 字段不是票房排名 -兩個表中沒有類似於 [ 影片ID ] 的字段
• 數據源中的數據每天都進行全量更新

** 實現路徑**

1 創建目標表

2 創建任務，通過 [ 電影名稱 ] 和 [ 影片名 ] 建立兩張表的連接，並按 [ 製片地區/國家 ] 進行分組，對 [ 累計票房（萬）] 進行排名

3 配置調度，該任務每天調度一次

** 實現步驟**

1 創建目標表

在**大數據開發套件**的**數據開發**標簽的**工具欄**中，點擊**新建**，在下拉菜單中選擇**新建表**

輸入建表語句

CREATE TABLE IF NOT EXISTS movie_sumboxoffice_top10 (
    area STRING,
    movie_name STRING,
    movie_rank STRING,
    sumboxoffice STRING,
    dirctor STRING,
    actors STRING
)
PARTITIONED BY (
    ds STRING
);

點擊**確定**提交

2 創建查詢任務進行分析

這次在工具欄中選擇**新建任務**，選擇**節點任務**，**ODPS_SQL**類型和**周期調度**

編輯SQL代碼，先測試一下。

--連接兩張原表
--每天任務運行時都是取當天數據源的最新分區
DROP TABLE IF EXISTS t_movie_sumboxoffice_info;

CREATE TABLE IF NOT EXISTS t_movie_sumboxoffice_info
AS
SELECT a.area
    , a.movie_name
    , b.sumboxoffice
    , a.dirctor
    , a.actors
FROM public_data.dwd_product_movie_basic_info a
JOIN (SELECT * FROM public_data.ods_product_movie_box WHERE ds = '${bdp.system.bizdate}')b
ON a.movie_name = b.moviename
WHERE a.ds = '${bdp.system.bizdate}';

發現產生的表 t_movie_sumboxoffice_info是空的，原因是數據源的兩張表裏麵並不是每一天都有數據的，做一下數據探查。

SELECT DISTINCT a.ds AS dt
FROM public_data.dwd_product_movie_basic_info a
JOIN public_data.ods_product_movie_box b
ON a.ds = b.ds
    AND b.ds IS NOT NULL
WHERE a.ds IS NOT NULL
ORDER BY dt DESC
LIMIT 365;

• 注意order by必須與limit 聯用，且order bykey必須是select語句的輸出列，即列的別dt。
• 當兩個表進行join的時候，主表的Where限製可以寫在最後，但從表分區限製條件要寫在ON條件或者子查詢，否則會先Join後進行分區裁切，造成不必要的性能下降。

得到的結果如下（截圖中隻顯示了一部分）

最大日期是2017年6月30日，最小日期是2017年1月13日，其中還有不連續的現象。

所以現在看來每天有新的分析報告是不行了，那就對曆史數據進行一下分析。
需要調整一下係統參數，設置為其中較早的一天，比如2017年1月19日。

再次運行，得到的結果如下

看來是數據沒有處理幹淨，存在屬於同一部電影但主演名字不一樣的情況。
雖然這屬於數據清洗的範疇，但是可以簡單進行一下去重處理。

--用distinct和wm_concat聚合函數進行簡單去重
DROP TABLE IF EXISTS tmp_movie_sumboxoffice_info;

CREATE TABLE IF NOT EXISTS tmp_movie_sumboxoffice_info
AS
SELECT a.area
    , a.movie_name
    , a.sumboxoffice
    , a.dirctor
    , wm_concat(',', a.actors) as actors
FROM (
    SELECT distinct area
        , movie_name
        , sumboxoffice
        , dirctor
        , actors
    FROM t_movie_sumboxoffice_info
) a
group by a.area
    , a.movie_name
    , a.sumboxoffice
    , a.dirctor; 

去重後結果如下

現在可以繼續編輯第三段SQL代碼

--產出最終目標表：每個國家/地區總票房最高的10個電影，並展示導演和主演
--每天任務運行產出的日期分區值與源表數據日期一致
INSERT OVERWRITE TABLE movie_sumboxoffice_top10 PARTITION (ds = '${bdp.system.bizdate}')
SELECT a.area
        , a.movie_name
        , a.movie_rank
        , a.sumboxoffice
        , a.dirctor
        , a.actors
FROM (
    SELECT area
        , movie_name
        , ROW_NUMBER() OVER (PARTITION BY area ORDER BY sumboxoffice DESC) AS movie_rank
        , sumboxoffice
        , dirctor
        , actors
    FROM tmp_movie_sumboxoffice_info
) a
WHERE a.movie_rank < 11;

• 這裏用一個窗口函數Row_number 對 area 開窗, 按照 sumboxoffice 從大到小進行排名

最終得到結果如下

隻有這麼可憐的幾條數據，選的日子不夠好。
而且為什麼排名第二的票房是6291？感覺似乎少了1個0，感興趣的同學可以研究一下為什麼，或者留言指出我的錯誤。

3 配置調度

雖然數據不完整，但是還是可以配置一下調度
由於本例中沒有依賴其他任務，所以隻需要配置調度周期，而且由於是每天調度一次，直接保留默認配置即可。

總結
整個任務基本上完成，雖然數據不太完整。
從去年接觸MaxCompute到現在，雖然使用的頻率還不夠多，但是能感受到功能越來越完善。比如費用預估就是一個非常讚的功能，用戶體驗大大上升，跑代碼的時候心理壓力小了很多。
公司的OLTP目前是建立在SQL Server 上, 未來可能有遷移數據倉庫到MaxCompute的打算。如果有機會的話，到時候再來跟大家分享，包括一些MaxCompute更高級的功能。

最後更新：2017-08-13 22:51:00

  上一篇：  高性能時序數據庫 HiTSDB 啟動公測，為物聯網而生的數據庫！

  下一篇：  MaxCompute 入門