数据库工程师快速上手MaxCompute进行ETL-阿里云开发者社区

开发者社区> 阿里巴巴大数据计算> 正文
登录阅读全文

数据库工程师快速上手MaxCompute进行ETL

简介: 案例说明 本案例主要是介绍如何通过数加MaxCompute+大数据开发套件两个产品实现简单的网站数据统计分析。 适用人群 MaxCompute初学者,特别是无大数据开发基础但有数据库使用基础。 案例侧重 数据库工程师快速上手MaxCompute进行大数据开发,简单了解在MaxCompute做大数据ETL过程,同时了解一些MaxCompute SQL和常用数据库SQL的基本区别。

案例说明

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

适用人群

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

案例侧重

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

示例介绍

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

数据说明

二手房网产品数据信息表house_basic_info存储于RDS-MySQL(区域:阿里云华南1可用区A;网络:专有网络),表数据每天全量更新。

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

具体表信息如下:

字段 字段类型 字段说明
house_id varchar 房产 ID
house_city varchar 房产所在城市
house_total_price Double 房产总价
house_unit_price Double 房产均价
house_type varchar 房产类型
house_floor varchar 房产楼层
house_direction varchar 房产方向
house_deckoration varchar 房产装修
house_area Double 房产面积
house_community_name varchar 房产所在小区
house_region varchar 房产所在地区
proj_name varchar 楼盘名称
proj_addr varchar 项目地址
period int 产权年限
property varchar 物业公司
greening_rate varchar 绿化率
property_costs varchar 物业费用
datetime varchar 数据日期

数据样例(英文逗号分隔):

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),具体操可参考通过大数据开发套件创建MaxCompute的介绍。

RDS数据导入MaxCompute

步骤一 配置RDS数据源

前置条件

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

具体操作

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

hw_001

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

hw_002

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

步骤二 配置数据同步任务

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

选择来源

hw_003

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

选择目标

本案例是将数据导入到本项目,所以目标选择默认的数据源odps_first(odps),这时发现一个问题,目标表没创建,所以我们需要先创建目标表,点击快速建表(更多建表方式可参考文档创建表)。

hw_004

弹框里显示系统自动根据源表结构生成对应的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);

配置目标如下:

hw_005

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

字段映射

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

通道控制

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

保存、提交

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

步骤三 执行数据导入任务

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

hw_006

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

hw_007

数据统计分析

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

创建目标表

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

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

hw_008

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任务

hw_009

编辑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"产出大数据,为了保证本任务执行时,数据导入已经完成,我们需要将导入任务设置为本任务的上游任务(即父任务)。

hw_010

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

保存提交

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

hw_012

执行任务

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

hw_013

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

数据回流

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

操作步骤

  1. RDS-MySQL中创建好对应的表,若需要保留每天都数据,可以加一个字段保存日期信息;
  2. 在导数据开发套件的数据集成里配置新数据源,参考前面数据导入时配置数据源的方式;
  3. 创建并配置数据同步任务,假设命名为 house_unit_price_top5_2_mysql,主要用MaxCompute读插件RDS-MySQL写插件,大致配置如下:

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

hw_015

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

hw_014

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

hw_016

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

总结

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

  • 大数据开发套件是架构在MaxCompute的web工具,提供界面操作以及数据集成和任务调度功能,而MaxCompute提供计算和存储服务。
  • MaxCompute SQL作业提交后会有几十秒到数分钟不等的排队调度,所以适合处理跑批作业,一次作业批量处理海量数据,不适合直接对接需要每秒处理几千至数万笔事务的前台业务系统。
  • MaxCompute SQL采用的是类似于SQL的语法,可以看作是标准SQL的子集,但不能因此简单的把MaxCompute 等价成一个数据库,它在很多方面并不具备数据库的特征,如事务、主键约束、索引等(更多差异请点击进入查看)。
  • 大数据开发套件里的数据同步可以实现跨region的RDS与MaxCompute的数据互传,无需特殊处理。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里巴巴大数据计算
使用钉钉扫一扫加入圈子
+ 订阅

阿里大数据官方技术圈

官方博客
链接