MaxCompute实践分析

概述
本文主要是记录如何通过数加MaxCompute和大数据开发套件两个产品实现简单的电影数据分析。

目标
希望了解每天市场上最火爆的电影是哪些，表现如何。通过对影视及票房数据集（dwd_product_movie_basic_info电影基本信息 和 ods_product_movie_box票房基本信息）统计分析出每天每个国家/地区累计票房最高的10个电影并且展现这些电影的导演、主演和累计票房。

数据说明
直接使用了数加平台公开数据集上的影视及票房数据集。该数据集从2016 年12 月13 日开始，每日早10 点前进行全量更新。

两个表的具体信息如下：

• 电影基本信息 dwd_product_movie_basic_info

字段英文名	字段类型	描述	是否是分区列
movie_ name	STRING	电影名称
dirctor	STRING	导演
scriptwriter	STRING	编剧
area	STRING	制片地区/国家
actors	STRING	主演
type	STRING	类型
movie_length	STRING	电影长度
movie_date	STRING	上映日期
movie_language	STRING	语言
imdb_ url	STRING	imdb号
ds	STRING	日期	分区列

数据样例

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

• 票房基本信息 ods_product_movie_box

字段英文名	字段类型	描述	是否是分区列
rank	STRING	排名
avgprice	STRING	平均票价
avppeople	STRING	场均人次
boxoffice	STRING	单日票房（万）
boxoffice_ up	STRING	环比变化 （%）
irank	STRING	排名
movieday	STRING	上映天数
moviename	STRING	影片名
sumboxoffice	STRING	累计票房（万）
womindex	STRING	口碑指数
ds	STRING	日期	分区列

数据样例

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更高级的功能。