案例说明

本案例主要是介绍如何通过数加MaxCompute+大数据开发套件两个产品实现简单的网站数据统计分析。

适用人群

MaxCompute初学者，特别是无大数据开发基础但有数据库使用基础。

案例侧重

数据库工程师快速上手MaxCompute进行大数据开发，简单了解在MaxCompute做大数据ETL过程，同时了解一些MaxCompute SQL和常用数据库SQL的基本区别。

示例介绍

房产网上经常会看到一些排行榜，如最近30日签约的楼盘排行、签约金额的楼盘排行等，本示例我们简单介绍通过对二手房产数据信息表（house_basic_info）统计分析出每个城市二手房均价top 5的楼盘并且给出该楼盘所在城区，最后需要让这些数据在房产网上呈现。

数据说明

二手房网产品数据信息表house_basic_info存储于RDS-MySQL（区域：阿里云华南1可用区A；网络：专有网络），表数据每天全量更新。

“二手房网产品数据信息表”在数加平台公开数据集-二手房产数据集上有，可以直接使用,不过数据量会与本案例呈现的可能不完全一致。

具体表信息如下：

数据样例（英文逗号分隔）：

000404705c6add1dc08e54ba10720698,beijing,8000000,72717,3室1厅,低楼层/共24层,南,平层/精装,137,玺萌丽苑,丰台 草桥 三至四环,null,null,null,null,null,null,20170605

RDS-MySQL上house_basic_info表的建表语句，如：

CREATE TABLE `house_basic_info` (
    `house_id` varchar(1024) NOT NULL COMMENT '房产 ID',
    `house_city` varchar(1024) NULL COMMENT '房产所在城市',
    `house_total_price` double NULL COMMENT '房产总价',
    `house_unit_price` double NULL COMMENT '房产均价',
    `house_type` varchar(1024) NULL COMMENT '房产类型',
    `house_floor` varchar(1024) NULL COMMENT '房产楼层',
    `house_direction` varchar(1024) NULL COMMENT '房产方向',
    `house_deckoration` varchar(512) NULL COMMENT '房产装修',
    `house_area` double NULL COMMENT '房产面积',
    `house_community_name` varchar(1024) NULL COMMENT '房产所在小区',
    `house_region` varchar(1024) NULL COMMENT '房产所在地区',
    `proj_name` varchar(1024) NULL,
    `proj_addr` varchar(1024) NULL,
    `period` int(11) NULL,
    `property` varchar(1024) NULL,
    `greening_rate` varchar(1024) NULL,
    `property_costs` varchar(1024) NULL,
    `datetime` varchar(512) NULL COMMENT '数据日期'
) ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='二手房网产品数据信息表';

需求分析

核心目标：统计分析出每个城市二手房均价top 5的楼盘并且给出该楼盘所在城区，即[城市,楼盘、均价、排名、所在城区]。

数据现状：

• 信息表中，每个楼盘可能有多条记录，多个均价信息，本案例为简单起见我们只针对整个楼盘的均价求平均；
• 信息表中，house_region中包含城区、街道地址信息，需要拆分出城区信息。
• 每天都数据都有变化，每个数据日期的数据都是全量数据。

所需操作：

1. 数据从RDS导入MaxCompute；
2. MaxCompute上对数据进行统计分析，并得出结果表；
3. 将结果表回流到网站业务系统，以便网站直接调用数据进行前端显示。

需求实现

前置说明

大数据开发套件是架构在MaxCompute上的一站式大数据开发管理工具，大数据开发套件是免费使用；MaxComput主要提供计算和存储能力，关于MaxCompute入门通常需要了解的信息可参考该文章。

前提条件

开通MaxCompute，创建MaxCompute项目（如项目名：house_web)

RDS数据导入MaxCompute

步骤一 配置RDS数据源

前置条件：

因RDS数据安全限制，大数据开发套件的数据同步任务要与RDS数据库进行联通，必须将执行数据同步任务的机器ip添加到RDS的白名单中，或者在配置数据源界面有ip查看入口。

具体操作：

进入大数据开发套件-数据集成大数据开发套件-数据集成-数据源，点击新增数据源，弹框里配置数据源信息，测试连通性正常后，确定添加即可。

所需RDS-MySQL实例ID即下图中的实例名称：

注意：本示例RDS实例所在区域为华南1，网络类型为专有网络，通过大数据开发套件进行数据同步时，属于跨region走专有网络方式导数据。大数据开发套件的数据集成针对RDS通过反向代理自动检测使得网络能够互通，无需其他特殊处理即可保证数据同步正常联通。

步骤二 配置数据同步任务

进入大数据开发套件-数据集成大数据开发套件-数据集成-数据同步，工作台上点击“向导模式”新建一个同步任务。

选择来源：

表每天全量更新，每次统计的数据时只需统计数据日期为昨天完整一天数据即可，因此数据过滤时，每天自动调度取datatime为昨天日期，可以使用系统参数${bdp.system.bizdate}代替，使得任务每天调度执行自动替换字段值，系统参数说明请看系统调度参数文档说明。

选择目标：

本案例是将数据导入到本项目，所以目标选择默认的数据源odps_first(odps)，这时发现一个问题，目标表没创建，所以我们需要先创建目标表。

弹框里显示系统自动根据源表结构生成对应的MaxCompute建表语句：

CREATE TABLE IF NOT EXISTS   your_table_name   (
house_id                      STRING   COMMENT '*',
house_city                    STRING   COMMENT '*',
house_total_price             DOUBLE   COMMENT '*',
house_unit_price              DOUBLE   COMMENT '*',
house_type                    STRING   COMMENT '*',
house_floor                   STRING   COMMENT '*',
house_direction               STRING   COMMENT '*',
house_deckoration             STRING   COMMENT '*',
house_area                    DOUBLE   COMMENT '*',
house_community_name          STRING   COMMENT '*',
house_region                  STRING   COMMENT '*',
proj_name                     STRING   COMMENT '*',
proj_addr                     STRING   COMMENT '*',
period                        BIGINT   COMMENT '*',
property                      STRING   COMMENT '*',
greening_rate                 STRING   COMMENT '*',
property_costs                STRING   COMMENT '*',
datetime                      STRING   COMMENT '*'
)
COMMENT '*'
PARTITIONED BY (pt STRING);

注意：

• 自动生成的代码里，表名需要修改成真正的目标表表名，可以与源表表名一致house_basic_info；
• 自动生成的代码里，源表中varchar类型会对应string类型，int类型会对应bigint类型。MaxCompute目前只支持6种数据类型，与常用数据库数据类型有所差异。
• 自动生成的代码里，字段不能指定默认值、不能指定是否非空默认都是可空、不能指定长度默认每个字段长度上限为8M。
• 自动生成的代码会是创建分区表，且分区名称为pt。MySQL数据库中没有分区概念，MaxCompute的分区概念与hadoop分区概念类似，具体可以参考分区介绍。本案例中目标表可以保留分区设置，以时间作为分区。
• 既然已经有时间分区，那么源表的datetime字段可以不需要同步到目标表，表也可以不需要创建该字段。
• 常用数据库sql与MaxCompute sql更多差异请看文档——与主流SQL差异。

综上所述，修改后的建表语句,并点击提交：

CREATE TABLE IF NOT EXISTS   house_basic_info   (
house_id                      STRING   COMMENT '*',
house_city                    STRING   COMMENT '*',
house_total_price             DOUBLE   COMMENT '*',
house_unit_price              DOUBLE   COMMENT '*',
house_type                    STRING   COMMENT '*',
house_floor                   STRING   COMMENT '*',
house_direction               STRING   COMMENT '*',
house_deckoration             STRING   COMMENT '*',
house_area                    DOUBLE   COMMENT '*',
house_community_name          STRING   COMMENT '*',
house_region                  STRING   COMMENT '*',
proj_name                     STRING   COMMENT '*',
proj_addr                     STRING   COMMENT '*',
period                        BIGINT   COMMENT '*',
property                      STRING   COMMENT '*',
greening_rate                 STRING   COMMENT '*',
property_costs                STRING   COMMENT '*'
)
COMMENT '*'
PARTITIONED BY (pt STRING);

配置目标如下：

• 分区值保留默认的${bdp.system.bizdate}，与来源表的过滤条件取的datetime数据日期对应，表示该分区存放的数据为源表中datetime=${bdp.system.bizdate}的数据。
• 清理规则保留默认选项，写入前清理已有数据（分区表时只清理（若有）当前分区数据）。

字段映射

直接保留默认即可。源表和目标表字段名都一致会自动对应好，源表datetime字段无对应目标字段且不用同步因而无需任何处理。

通道控制

本案例中都保留默认即可，具体通道控制各项配置说明请看文档，"数据同步通道控制参数设置")。

保存、提交

• 保存任务时可以创建专门的目录存放，本案例我们接用目标表名称作为任务名称；
• 提交任务主要是将任务提交到调度系统，使得任务可以按照调度配置进行自动运行。本案例调度配置保留默认配置，调度周期为“天”调度。

步骤三 执行数据导入任务

在大数据开发套件中，切换到“运维中心-任务管理”找到任务house_basic_info，在任务视图上右键-测试节点：

等待任务执行成功后，可以到“大数据开发套件——数据开发”的"脚本开发"中创建一个sql脚本文件，执行select语句查看表house_basic_info数据是否真的同步成功：

数据统计分析

数据导入到MaxCompute后，我们将通过MaxCompute SQL、MR等对数据进行加工处理。

创建目标表

前面“需求分析”的目标分析（统计分析出每个城市二手房均价top 5的楼盘并且给出该楼盘所在城区，即[城市,楼盘、均价、排名、所在城区])得出表5个字段。

进入“大数据开发套件——数据开发”，工作区的工具栏上点击“新建”选择新建表，输入建表语句并提交。

CREATE TABLE IF NOT EXISTS house_unit_price_top5 (
    house_city STRING,
    house_community_name STRING,
    house_unit_price_all DOUBLE,
    area STRING,
    tops BIGINT
)
PARTITIONED BY (
    pt STRING
);

创建任务进行数据统计分析

进入“大数据开发套件——数据开发”的"任务开发"中创建一个sql任务

编辑SQL代码

--产出每个城市每个楼盘的均价临时表
--分区值是对应数据导入任务配置的分区值，保证每天运行都是取当天导入的最新分区。
DROP TABLE IF EXISTS t_house_unit_price_info;
CREATE TABLE IF NOT EXISTS t_house_unit_price_info
AS
SELECT house_city
    , house_community_name
    , AVG(house_unit_price) AS house_unit_price_all
FROM house_basic_info
WHERE pt = '${bdp.system.bizdate}'
GROUP BY house_city, 
    house_community_name;

--拆分house_region字段只取城区名称输出字段为area，并存储到一个临时表。
--分区值是对应数据导入任务配置的分区值，保证每天运行都是取当天导入的最新分区。
DROP TABLE IF EXISTS t_house_area;
CREATE TABLE IF NOT EXISTS t_house_area
AS
SELECT distinct  house_city
    ,house_community_name
    ,split_part(house_region, ' ', 1) AS area
FROM house_basic_info
WHERE pt = '${bdp.system.bizdate}';

--产出最终目标表：每天每个城市二手房均价top 5的楼盘并且给出该楼盘所在城区。
--分区值是对应数据导入任务配置的分区值，保证每天运行产出的日期分区值与源表数据日期一致。
INSERT OVERWRITE TABLE house_unit_price_top5 PARTITION (pt='${bdp.system.bizdate}')
SELECT a.house_city
    , a.house_community_name
    , a.house_unit_price_all
    , b.area
    , a.tops
FROM (
    SELECT house_city
        , house_community_name
        , house_unit_price_all
        , ROW_NUMBER() OVER (PARTITION BY house_city ORDER BY house_unit_price_all DESC) AS tops
    FROM t_house_unit_price_info
) a
JOIN t_house_area b
ON a.house_city = b.house_city
    AND a.house_community_name = b.house_community_name
    AND a.tops < 6;

注意

MaxCompoute SQL语法采用类似于常用SQL语法，可以看作是标准SQL的子集，但MaxCompute在很多方面并不具备常用数据库的特征，如事务、主键约束、索引等都不支持，因而SQL也有一定的差异。前面介绍数据导入创建目标表时已经简单的介绍了一些DDL语法的差异，针对这里DML语句，我们也做简单介绍：

• "产出每个城市每个楼盘的均价临时表"语句,整个语句只需要修改where条件中pt条件，即可直接在mysql上执行。

• “拆分house_region字段”的语句中“split_part()”函数是MaxCompute内置的字符串函数，可以直接在SQL中使用，对应MySQL上substring_index()或其他。

• 产出目标表语句中，ROW_NUMBER()是MaxCompute内置的窗口函数，在本案例中主要作用于计算排行,可在SQL中直接使用，MySQL上没有可直接对应的函数。

• 产出目标表语句中，insert overwrite（或insert into） 后要加 table 关键字，MySQL或Oracle不需要table关键字。

• MaxCompute SQL和常用SQl更多差异请看文档——与主流SQL差异。

调度配置和参数配置

代码编辑好后，可以点击工具栏执行按钮执行sql语句，对sql进行探查。确定无误后进行调度配置主要包括调度属性和依赖属性：

• 调度属性：由于每天调度一次，直接保留默认配置即可。
• 依赖属性：由于本任务处理的数据来源是数据导入任务"house_basic_info"产出大数据，为了保证本任务执行时，数据导入已经完成，我们需要将导入任务设置为本任务的上游任务（即父任务）。

至于“参数配置”由于本任务中只用到系统参数${bdp.system.bizdate},这个参数在系统调度任务时会自动替换，所以无需再进行其他配置。

保存提交

所有配置项都配置完成后点击工具栏上的“保存”、“提交”按钮，将任务提交到调度系统。点击工作区右上角“前往运维”按钮可以到运维中心查看工作流形态:

执行任务

与前面数据导入任务执行操作类似。执行成功后可以在“数据开发”模块sql脚本中查看目标表数据：

到目前为止，我们的目标表已经正常产出了，但是MaxCompute SQL在执行时会有一定的等待调度时间，适合做大数据批处理，网站前端读取数据就不适合直接读MaxCompute的数据，所以接下来我们需要把目标表回流到网站业务库。

数据回流

与数据导入一样，需要配置数据同步任务，不一样的是回流任务来源是MaxCompute的表，目标库是业务库，如还是用本示例中的RDS-MySQL的house_web_master 数据库中。

操作步骤：

1. RDS-MySQL中创建好对应的表,若需要保留每天都数据，可以加一个字段保存日期信息；
2. 在导数据开发套件的数据集成里配置新数据源，参考前面数据导入时配置数据源的方式；

3. 创建并配置数据同步任务，假设命名为 house_unit_price_top5_2_mysql，主要用MaxCompute读插件和RDS-MySQL写插件，大致配置如下：

   字段配置如果想直接把源表的分区字段同步到MySQL的日期信息字段

   

   依赖属性中，为了保证每次回流都是最新的数据，将数据加工任务house_unit_price_top5设置为父任务

   

   保存提交任务后在运维管理可以看到工作流形态：

   

4. 执行回流任务，参考前面的任务执行方式。执行成功后，可以到RDS-MySQL上查看表数据是否正常导入。

总结

到此，我们整个需求目标都完成了，本案例在MaxCompute只是实现一个非常简单的统计分析，更多的高级功能组件（MapReduce、Graph等）没有用到。通过本案例我们可以了解到：

• 大数据开发套件是架构在MaxCompute的web工具，提供界面操作以及数据集成和任务调度功能，而MaxCompute提供计算和存储服务。

• MaxCompute SQL作业提交后会有几十秒到数分钟不等的排队调度，所以适合处理跑批作业，一次作业批量处理海量数据，不适合直接对接需要每秒处理几千至数万笔事务的前台业务系统。

• MaxCompute SQL采用的是类似于SQL的语法，可以看作是标准SQL的子集，但不能因此简单的把MaxCompute 等价成一个数据库，它在很多方面并不具备数据库的特征，如事务、主键约束、索引等（更多差异请点击进入查看）。

• 大数据开发套件里的数据同步可以实现跨region的RDS与MaxCompute的数据互传，无需特殊处理。