MaxCompute实践分析

  1. 云栖社区>
  2. 博客列表>
  3. 正文

MaxCompute实践分析

重楼123 2017-07-30 22:56:54 浏览1154 评论0

摘要: 概述本文主要是记录如何通过数加MaxCompute和大数据开发套件两个产品实现简单的电影数据分析。目标希望了解每天市场上最火爆的电影是哪些,表现如何。通过对影视及票房数据集(dwd_product_movie_basic_info电影基本信息 和 ods_product_movie_box票房基本信息)统计分析出每天每个国家/地区累计票房最高的10个电影并且展现这些电影的导演、主演和累计票房。

概述
本文主要是记录如何通过数加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;

1

  • 票房基本信息 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;

2


目标分析

  • 需要统计出每个国家/地区累计票房(万)最高的10个电影并且展现这些电影的导演和主演
  • 包括的字段是 [ 制片地区/国家 、电影名称、 排名、累计票房(万)、导演、主演]


现状分析

  • 电影基本信息表中包含了 [ 制片地区/国家、电影名称、 导演、主演 ]
    -票房基本信息表中包含了 [ 影片名、累计票房(万)], 其中的 [ 排名 ] 字段不是票房排名

-两个表中没有类似于 [ 影片ID ] 的字段

  • 数据源中的数据每天都进行全量更新


实现路径

1 创建目标表

2 创建任务,通过 [ 电影名称 ] 和 [ 影片名 ] 建立两张表的连接,并按 [ 制片地区/国家 ] 进行分组,对 [ 累计票房(万)] 进行排名

3 配置调度,该任务每天调度一次


实现步骤

1 创建目标表

大数据开发套件数据开发标签的工具栏中,点击新建,在下拉菜单中选择新建表
3

输入建表语句

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类型和周期调度
5

编辑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后进行分区裁切,造成不必要的性能下降。

得到的结果如下(截图中只显示了一部分)

8

最大日期是2017年6月30日,最小日期是2017年1月13日,其中还有不连续的现象。

所以现在看来每天有新的分析报告是不行了,那就对历史数据进行一下分析。
需要调整一下系统参数,设置为其中较早的一天,比如2017年1月19日。

9

再次运行,得到的结果如下
10

看来是数据没有处理干净,存在属于同一部电影但主演名字不一样的情况。
虽然这属于数据清洗的范畴,但是可以简单进行一下去重处理。

--用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; 

去重后结果如下
11

现在可以继续编辑第三段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 从大到小进行排名

最终得到结果如下
12
只有这么可怜的几条数据,选的日子不够好。
而且为什么排名第二的票房是6291?感觉似乎少了1个0,感兴趣的同学可以研究一下为什么,或者留言指出我的错误。

3 配置调度

虽然数据不完整,但是还是可以配置一下调度
由于本例中没有依赖其他任务,所以只需要配置调度周期,而且由于是每天调度一次,直接保留默认配置即可。
13


总结
整个任务基本上完成,虽然数据不太完整。
从去年接触MaxCompute到现在,虽然使用的频率还不够多,但是能感受到功能越来越完善。比如费用预估就是一个非常赞的功能,用户体验大大上升,跑代码的时候心理压力小了很多。
公司的OLTP目前是建立在SQL Server 上, 未来可能有迁移数据仓库到MaxCompute的打算。如果有机会的话,到时候再来跟大家分享,包括一些MaxCompute更高级的功能。

用云栖社区APP,舒服~

【云栖快讯】诚邀你用自己的技术能力来用心回答每一个问题,通过回答传承技术知识、经验、心得,问答专家期待你加入!  详情请点击

网友评论

重楼123
文章1篇 | 关注0
关注
快速、完全托管的TB/PB级数据仓库解决方案,向用户提供了完善的数据导入方案以及多种经典的分... 查看详情
阿里云推出的一款移动App数据统计分析产品,为开发者提供一站式数据化运营服务 查看详情
阿里巴巴自主研发的海量数据实时高并发在线分析云计算服务,使得您可以在毫秒级针对千亿级数据进行... 查看详情
为您提供简单高效、处理能力可弹性伸缩的计算服务,帮助您快速构建更稳定、安全的应用,提升运维效... 查看详情
阿里云总监课正式启航

阿里云总监课正式启航