文档

数据开发

更新时间:

本文从需求分析、规划业务流程、规划表管理、同步数据以及加工数据几方面,为您介绍数据开发。

需求分析

将MySQL的数据采集到MaxCompute 贴源层表中,进行清洗和加工,产出最终的概览应用表。表血缘

规划业务流程

业务流程是DataWorks针对业务实体抽象出来的概念,帮助您从业务视角组织代码的开发,提高任务管理效率。

本实验中参考数据建模的分层划域,新建“零售数仓公共层、会员分析、交易分析、物流分析、信用风控分析、电商360”这6个业务流程。

3

(可选)规划表管理

DataWorks支持通过表管理功能实现目录文件夹展示表列表,使表列表呈现更清晰,不操作不影响后续实验。

  • 层级管理:用于对表的物理层级进行设计,用来定义和管理数据仓库分层。

    本实验中参考数据建模的分层划域,新建“ODS数据引入层、DIM维度层、DWD明细数据层、DWS汇总数据层、ADS应用数据层”。24

  • 主题管理:按照分析视角对表进行划分。

    本实验中参考数据建模的分层划域,按照下表,新建八个一级主题和若干个二级主题。25

    一级主题

    二级主题

    应用层_电商业务_电商集市

    活动、电商360

    应用层_电商业务_其他集市

    -

    公共层_电商业务_会员域

    注册、登录、会员域默认

    公共层_电商业务_商品域

    -

    公共层_电商业务_交易域

    下单、加购、支付、退款

    公共层_电商业务_物流域

    发货、接单、揽件、派送、签收、物流域默认

    公共层_电商业务_信用和风控域

    评价、信用和风控域默认

    贴源层

    -

    参照下表,将数据建模发布的表归到对应层级和主题下。26

    表名

    层级

    一级主题

    二级主题

    dim_ec_itm_item_info

    DIM

    公共层_电商业务_商品域

    -

    dim_ec_mbr_user_info

    DIM

    公共层_电商业务_会员域

    -

    dws_ec_trd_cate_commodity_gmv_kpi_fy

    DWS

    公共层_电商业务_交易域

    -

    dws_ec_mbr_cnt_std

    DWS

    公共层_电商业务_会员域

    -

    dwd_ec_trd_create_ord_di

    DWD

    公共层_电商业务_交易域

    下单

    ads_ec_ec360_gmv_kpi_overview

    ADS

    应用层_电商业务_电商集市

    电商360

    ods_mbr_user_info

    ODS

    贴源层

    -

    ods_item_info

    ODS

    贴源层

    -

    ods_t_area

    ODS

    贴源层

    -

    ods_trade_order

    ODS

    贴源层

    -

同步数据

将MySQL中的数据同步到MaxCompute ODS层。

数据集成是稳定高效、弹性伸缩的数据同步平台,致力于提供复杂网络环境下、丰富的异构数据源之间高速稳定的数据移动及同步能力。更多关于数据集成支持的数据源情况,请参见数据集成

步骤一:购买并配置独享数据集成资源组

本教程需将存储在MySQL中的数据同步至MaxCompute,同步任务需使用DataWorks的独享数据集成资源组,因此您需要先准备独享数据集成资源组,并完成前期的准备工作。

说明

DataWorks为您提供了独享数据集成资源组供您使用,公共数据集成(调试)资源组即将下线,详情请参见DataWorks公共数据集成(调试)资源组下线公告,建议数据同步任务均使用独享数据集成资源组。

  1. 购买独享数据集成资源组。

    登录并进入独享资源购买页面,选择地域和可用区华东2(上海)独享资源类型独享数据集成资源,其他参数可根据界面提示进行配置,完成后根据界面提示完成付款。独享数据集成资源组的计费说明请参见独享数据集成资源组计费说明:包年包月

    说明

    本教程将使用华东2(上海)地域的独享数据集成资源组进行示例演示,需注意独享数据集成资源组不支持跨地域操作。

  2. 配置独享数据集成资源组。

    1. 登录DataWorks控制台,切换至目标地域后,单击左侧导航栏的资源组列表,进入资源组列表页面。

    2. 独享资源组页签中,找到购买的独享数据集成资源组,单击操作列的修改归属工作空间,根据界面提示将资源组绑定至已创建的DataWorks工作空间。

更多新增和使用独享数据集成资源组的操作指导请参见新增和使用独享数据集成资源组

步骤二:新建数据源

  1. 参照下表,新建MySQL数据源。详细操作步骤请参见创建数据源

    参数

    描述

    数据源类型

    选择连接串模式

    数据源名称

    输入retail_e_commerce_read

    数据源描述

    零售电商贴源数据。

    适用环境

    选中开发生产

    JDBC URL

    输入jdbc:mysql://rm-bp1z69dodhh85z9qa.mysql.rds.aliyuncs.com:3306/retail_e_commerce

    重要

    本教程提供数据仅作为阿里云大数据开发治理平台DataWorks数据应用实操使用,所有数据均为人工Mock数据,并且仅支持在数据集成模块读取数据。

    用户名

    输入用户名,示例为workshop

    密码

    输入密码,示例为workshop#2017

    认证选项

    无认证。

    资源组连通性

    数据集成页签下,单击购买的独享数据集成资源组后的测试连通性,等待界面提示测试完成,连通状态为可连通

  2. 新增MaxCompute数据源。详情请参见创建MaxCompute数据源

步骤三:新建离线同步任务

各同步场景的通用操作步骤指导请参见通过向导模式配置离线同步任务,核心配置要点如下。

  1. 同步会员信息源表 ods_mbr_user_info。

    1. 参照如下信息,新建MySQL到MaxCompute的离线同步任务,使用向导模式,将MySQL中数据同步到MaxCompute 表ods_mbr_user_info中。

      • 节点类型:离线同步。

      • 节点名:ods_mbr_user_info。

      • 路径:业务流程/零售数仓公共层/数据集成。

    2. 配置网络与资源。

      数据来源选择为创建的MySQL数据源,数据去向选择为MaxCompute数据源,我的资源组选择更多选项下的公共资源组(调试资源组),选择完成后单击下一步,一键测试网络连通性,连通后继续后续配置。

    3. 配置数据源。

      由于任务每天需要更新写入日期格式的分区,所以这里数据去向的分区信息使用调度参数代替,每天周期性执行任务的时候,调度参数会替换对应的日期值。

      • 数据来源

        参数

        数据源

        MySQL:retail_e_commerce_read

        mbr_user_info

        数据过滤

        -

        切分键

        id

      • 数据去向

        参数

        数据源

        ODPS:odps_source

        开发项目名

        retail_e_commerce_2_dev

        生产项目名

        retail_e_commerce_2

        ods_mbr_user_info

        说明
        • 在维度建模部分已经执行过文末附件的ODS层DDL语句,所以这里直接下拉选择表名即可。在实际开发时也可以直接使用一键生成目标表根据源端表结构快速生成MaxCompute表。

        • 如果仅创建了开发表,需要在数据开发 > 表管理中搜索一下这4张ODS层表,都执行一下提交到生产环境

        分区信息

        ds=${bizdate}

        清理规则

        写入前清理已有数据(Insert Overwrite)

        空字符串作为null

    4. 配置字段映射:选择同名映射,如下图。29

    5. 配置通道控制:保持默认配置即可,完成后单击下一步

    6. 调度配置。核心配置要点如下,其他参数保持默认即可。

      1. 参数配置:使用自动生成的参数配置结果,bizdate=$bizdate。$bizdate为前一天的日期,格式为yyyymmdd,更多关于调度参数的配置说明,请参见调度参数支持的格式

      2. 时间属性配置:当前任务需要每日周期性更新数据,所以这里将调度周期配置为“日调度”。重跑属性设置为运行成功或失败后皆可重跑。33

      3. 资源属性配置:任务由调度资源组调起运行,所以这里配置调度资源组为公共调度资源组

      4. 调度依赖配置:单击同周期页签中依赖的上游节点后的使用工作空间根节点,其他参数保持默认。

        配置调度依赖的目的是为了保障上游表产出完成才会触发下游读取数据,而当前节点上游的数据是静态的,不需要等待产出,所以本节点的上游依赖可配置为工作空间根节点。

        说明

        严格遵守以下节点开发规范更有利于调度依赖自动解析,如果对调度依赖不是非常熟悉的,更建议严格遵守。

        • 节点和产出表一对一关系。

        • 节点名命名与产出表名保持一致。

        更多关于调度依赖的原理,请参见调度依赖配置指引;若您需要调整调度依赖,请参考调度依赖配置指引以及配置依赖上一周期(跨周期依赖)。建议您在开发ODPS SQL任务的时候再了解,易于理解。

      5. 确认调度依赖配置完成后的配置结果信息。

        • 同周期 > 依赖的上游节点 > 输出名retail_e_commerce_2_root

        • 同周期 > 本节点的输出 > 输出名retail_e_commerce_2.502736866_outretail_e_commerce_2.ods_mbr_user_info

        说明

        输出名中的数字为系统随机生成。

    7. 保存并测试节点运行结果。

      键盘操作Ctrl+S保存配置结果后,单击带参带参运行,配置业务日期的值后运行节点,例如设置日期为“20220726”。35任务运行成功后可新建一个ODPS SQL临时查询校验导入的数据是否符合预期。临时查询的创建操作请参见创建临时查询

      SELECT * FROM  retail_e_commerce_2_dev.ods_mbr_user_info  where ds = '20220726' LIMIT 10;
      说明

      建议本实验中的任务高级运行时填写的日期值一致,比如都填写“20220726”,避免下游任务再测试时因参数取值不同取不到数据。不影响生产调度。

    8. 提交并发布离线同步节点ods_mbr_user_info。

      说明

      必须要提交了以后才可以在下游节点配置依赖关系的时候被搜索到,发布可以等到全部提交完后批量发布。

      36

  2. 同步地区源表 ods_t_area。

    1. 新建如下MySQL到MaxCompute的离线同步任务,使用向导模式。

      • 节点类型:离线同步。

      • 节点名:ods_t_area。

      • 路径:业务流程/零售数仓公共层/数据集成。

    2. 配置网络与资源。

      数据来源选择为创建的MySQL数据源,数据去向选择为MaxCompute数据源,我的资源组选择更多选项下的公共资源组(调试资源组),选择完成后单击下一步,一键测试网络连通性,连通后继续后续配置。

    3. 配置数据来源和去向。

      • 数据来源

        参数

        数据源

        MySQL:retail_e_commerce_read

        t_area

        数据过滤

        -

        切分键

        id

      • 数据去向

        参数

        数据源

        ODPS:odps_source

        开发项目名

        retail_e_commerce_2_dev

        生产项目名

        retail_e_commerce_2

        ods_t_area

        分区信息

        ds=${bizdate}

        清理规则

        写入前清理已有数据(Insert Overwrite)

        空字符串作为null

    4. 配置字段映射:使用同名映射。

    5. 配置通道控制:使用默认通道配置。

    6. 单击下一步

    7. 参数配置:使用自动生成的参数配置结果,bizdate=$bizdate。$bizdate为前一天的日期,格式为yyyymmdd,更多关于调度参数的配置说明,请参见调度参数支持的格式

    8. 时间属性与资源属性配置:当前任务需要每日周期性更新数据,所以这里将调度周期配置为“日调度”。任务由调度资源组调起运行,所以这里配置一下调度资源组“公共调度资源组”。时间属性和调度资源属性使用默认配置即可。33

    9. 调度依赖配置:配置调度依赖的目的是为了保障上游表产出完成才会触发下游读取数据,而当前节点上游的数据是静态的,不需要等待产出,所以本节点的上游依赖可配置为工作空间根节点,即周期依赖的上游节点配置为"使用工作空间根节点",其他参数保持默认。

      说明

      严格遵守以下节点开发规范更有利于调度依赖自动解析,如果对调度依赖不是非常熟悉的,更建议严格遵守。

      • 节点和产出表一对一关系。

      • 节点名命名与产出表名保持一致。

      更多关于调度依赖的原理,请参见调度依赖配置指引;若您需要调整调度依赖,请参考调度依赖配置指引以及配置依赖上一周期(跨周期依赖)。建议您在开发ODPS SQL任务的时候再了解,易于理解。

      调度依赖配置完成后,请确认如下信息:

      • 同周期 > 依赖的上游节点 > 输出名retail_e_commerce_2_root

      • 同周期 > 本节点的输出 > 输出名retail_e_commerce_2.502736901_outretail_e_commerce_2.ods_t_area

        说明

        输出名中的数字为系统随机生成。

    10. 保存、测试及提交、发布。操作与第一个同步任务操作相同。

  3. 同步商品信息源表 ods_item_info。

    1. 新建如下MySQL到MaxCompute的离线同步任务,使用向导模式。

      • 节点类型:离线同步。

      • 节点名:ods_item_info。

      • 路径:业务流程/零售数仓公共层/数据集成。

    2. 配置数据来源和去向。

      • 数据来源

        参数

        数据源

        MySQL:retail_e_commerce_read

        item_info

        数据过滤

        -

        切分键

        id

      • 数据去向

        参数

        数据源

        ODPS:odps_source

        开发项目名

        retail_e_commerce_2_dev

        生产项目名

        retail_e_commerce_2

        ods_item_info

        分区信息

        ds=${bizdate}

        清理规则

        写入前清理已有数据(Insert Overwrite)

        空字符串作为null

    3. 配置字段映射:使用同名映射。

    4. 配置通道控制:使用默认通道配置。

    5. 单击下一步。

    6. 参数配置:使用自动生成的参数配置结果,bizdate=$bizdate。$bizdate为前一天的日期,格式为yyyymmdd,更多关于调度参数的配置说明,请参见调度参数支持的格式

    7. 时间属性与资源属性配置:当前任务需要每日周期性更新数据,所以这里将调度周期配置为“日调度”。任务由调度资源组调起运行,所以这里配置一下调度资源组“公共调度资源组”。时间属性和调度资源属性使用默认配置即可。33

    8. 调度依赖配置:配置调度依赖的目的是为了保障上游表产出完成才会触发下游读取数据,而当前节点上游的数据是静态的,不需要等待产出,所以本节点的上游依赖可配置为工作空间根节点,即周期依赖的上游节点配置为"使用工作空间根节点",其他参数保持默认。

      说明

      严格遵守以下节点开发规范更有利于调度依赖自动解析,如果对调度依赖不是非常熟悉的,更建议严格遵守。

      • 节点和产出表一对一关系。

      • 节点名命名与产出表名保持一致。

      更多关于调度依赖的原理,请参见调度依赖配置指引;若您需要调整调度依赖,请参考调度依赖配置指引以及配置依赖上一周期(跨周期依赖)。建议您在开发ODPS SQL任务的时候再了解,易于理解。

      调度依赖配置完成后,请确认如下信息:

      • 同周期 > 依赖的上游节点 > 输出名retail_e_commerce_2_root

      • 同周期 > 本节点的输出 > 输出名retail_e_commerce_2.502736904_outretail_e_commerce_2.ods_item_info

        说明

        输出名中的数字为系统随机生成。

    9. 保存、测试及提交、发布。操作与第一个同步任务操作相同。

  4. 同步订单源表 ods_trade_order。

    1. 新建如下MySQL到MaxCompute的离线同步任务,使用向导模式。

      • 节点类型:离线同步。

      • 节点名:ods_trade_order。

      • 路径:业务流程/零售数仓公共层/数据集成。

    2. 配置数据来源和去向。

      • 数据来源

        参数

        数据源

        MySQL:retail_e_commerce_read

        trade_order

        数据过滤

        -

        切分键

        id

      • 数据去向

        参数

        数据源

        ODPS:odps_source

        开发项目名

        retail_e_commerce_2_dev

        生产项目名

        retail_e_commerce_2

        ods_trade_order

        分区信息

        ds=${bizdate}

        清理规则

        写入前清理已有数据(Insert Overwrite)

        空字符串作为null

    3. 配置字段映射:使用同名映射。

    4. 配置通道控制:使用默认通道配置。

    5. 单击下一步。

    6. 参数配置:使用自动生成的参数配置结果,bizdate=$bizdate。$bizdate为前一天的日期,格式为yyyymmdd,更多关于调度参数的配置说明,请参见调度参数支持的格式

    7. 时间属性与资源属性配置:当前任务需要每日周期性更新数据,所以这里将调度周期配置为“日调度”。任务由调度资源组调起运行,所以这里配置一下调度资源组“公共调度资源组”。时间属性和调度资源属性使用默认配置即可。33

    8. 调度依赖配置:配置调度依赖的目的是为了保障上游表产出完成才会触发下游读取数据,而当前节点上游的数据是静态的,不需要等待产出,所以本节点的上游依赖可配置为工作空间根节点,即周期依赖的上游节点配置为"使用工作空间根节点",其他参数保持默认。

      说明

      严格遵守以下节点开发规范更有利于调度依赖自动解析,如果对调度依赖不是非常熟悉的,更建议严格遵守。

      • 节点和产出表一对一关系。

      • 节点名命名与产出表名保持一致。

      更多关于调度依赖的原理,请参见调度依赖配置指引;若您需要调整调度依赖,请参考调度依赖配置指引以及配置依赖上一周期(跨周期依赖)。建议您在开发ODPS SQL任务的时候再了解,易于理解。

      调度依赖配置完成后,请确认如下信息:

      • 同周期 > 依赖的上游节点 > 输出名retail_e_commerce_2_root

      • 同周期 > 本节点的输出 > 输出名retail_e_commerce_2.502736916_outretail_e_commerce_2.ods_trade_order

        说明

        输出名中的数字为系统随机生成。

    9. 保存、测试及提交、发布。操作与第一个同步任务操作相同。

加工数据

产出“dim_ec_mbr_user_info 会员基础信息维度表”

  1. 创建调度任务。

    新建ODPS SQL节点,在零售数仓公共层业务流程MaxCompute引擎模块下,并命名为“dim_ec_mbr_user_info”。新建ODPS SQL的详细操作步骤请参见开发ODPS SQL任务37

  2. 开发SQL。

    由于任务每天需要从日期格式的分区读取,清理后写入日期格式的分区,所以这里Insert的分区信息和From的分区使用调度参数代替,每天周期性执行任务的时候,调度参数会替换对应的业务日期值。

    INSERT OVERWRITE TABLE dim_ec_mbr_user_info PARTITION(ds='${biz_date}')
    SELECT  user.user_id AS user_id
            ,user.nick AS nick
            ,user.gmt_create AS gmt_modified
            ,user.gmt_modified AS gmt_create
            ,user.reg_fullname AS reg_fullname
            ,user.reg_mobile_phone AS reg_mobile_phone
            ,user.reg_email AS reg_email
            ,user.reg_gender AS reg_gender
            ,user.reg_gender_name AS reg_gender_name
            ,user.reg_birthdate AS reg_birthdate
            ,user.reg_address AS reg_address
            ,user.reg_nation_id AS reg_nation_id
            ,user.reg_nation_name AS reg_nation_name
            ,user.reg_prov_id AS reg_prov_id
            ,area_prov.name AS reg_prov_name
            ,user.reg_city_id AS reg_city_id
            ,area_city.name AS reg_city_name
            ,user.user_regip AS user_regip
            ,user.id_card_type AS id_card_type
            ,user.id_card_type_name AS id_card_type_name
            ,user.id_card_number AS id_card_number
            ,null as id_gender
            ,null as id_bday
            ,null as id_age
            ,user.user_regdate AS user_regdate
            ,user.user_active_type AS user_active_type
            ,user.user_active_name AS user_active_name
            ,user.user_active_time AS user_active_time
            ,user.vip_level AS vip_level
            ,user.vip_level_name AS vip_level_name
            ,user.is_delete AS is_delete
    --,id_region
    FROM    (
                SELECT  id    -- 主键
                        ,gmt_create    -- 创建时间
                        ,gmt_modified    -- 修改时间
                        ,user_id    -- 会员数字ID
                        ,nick    -- 会员NICK。会员昵称
                        ,reg_fullname    -- 个人认证表示真实姓名,企业认证表示企业名称
                        ,reg_mobile_phone    -- 注册时绑定手机号码
                        ,reg_email    -- 注册填写EMAIL(用户可以修改)
                        ,reg_gender    -- 注册填写性别(F女,M男,不是这两个就是未知的,说明性别保密)
                        ,CASE    WHEN reg_gender='F' THEN '女'
                                 WHEN reg_gender='M' THEN '男'
                                 ELSE '未知'
                         END AS reg_gender_name    -- 注册填写性别(F女,M男,不是这两个就是未知的,说明性别保密)
                        ,reg_birthdate    -- 注册填写生日(用户可以修改)
                        ,reg_address    -- 注册填写地址(用户可以修改)
                        ,reg_nation_id    -- 注册填写国家ID(暂时为空)
                        ,CASE    WHEN reg_nation_id='cn' THEN '中国'
                                 ELSE '海外'
                         END AS reg_nation_name
                        ,reg_prov_id    -- 注册填写省ID
                        ,reg_city_id    -- 注册填写城市ID
                        ,user_regip    -- 注册IP
                        ,id_card_type    -- 会员认证证件类型 0:未知 1:身份证 2:企业营业执照号
                        ,CASE    WHEN id_card_type=0 THEN '未知'
                                 WHEN id_card_type=1 THEN '身份证'
                                 WHEN id_card_type=2 THEN '企业营业执照号'
                                 ELSE '异常'
                         END AS id_card_type_name
                        ,id_card_number    -- 个人认证表示身份证号,企业认证表示企业的营业执照号,没有认证不保证准确性
                        ,user_regdate    -- 注册时间
                        ,user_active_type    -- 用户激活方式
                        ,CASE    WHEN user_active_type='email' THEN '邮箱'
                                 WHEN user_active_type='mobile_phone' THEN '手机'
                                 ELSE '异常'
                         END AS user_active_name    -- 用户激活方式
                        ,user_active_time    -- 激活时间
                        ,cast(vip_level AS BIGINT) AS vip_level    -- VIP等级
                        ,CASE    WHEN vip_level>0 AND vip_level<=3 THEN '初级'
                                 WHEN vip_level>3 AND vip_level<=6 THEN '中级'
                                 WHEN vip_level>6 AND vip_level<=10 THEN '高级'
                                 WHEN vip_level>10  THEN '特级'
                        ELSE '异常'
                         END  AS vip_level_name
                        ,is_delete    -- 是否删除
                FROM    ods_mbr_user_info
                WHERE   ds = '${biz_date}'
            ) AS USER
    LEFT JOIN (
                  SELECT  id
                          ,pid
                          ,name
                          ,shortname
                          ,longitude
                          ,latitude
                          ,level
                          ,sort
                  FROM    ods_t_area
                  WHERE   ds = ${biz_date}
              ) AS area_prov
    ON      user.reg_prov_id = area_prov.id LEFT
    JOIN    (
                SELECT  id
                        ,pid
                        ,name
                        ,shortname
                        ,longitude
                        ,latitude
                        ,level
                        ,sort
                FROM    ods_t_area
                WHERE   ds = ${biz_date}
            ) AS area_city
    ON      user.reg_city_id = area_city.id
    ;
  3. 调度配置。在ODPS SQL节点右侧导航栏,单击调度配置

    1. 参数:新增参数biz_date=${yyyymmdd}。更多关于调度参数的配置说明,请参见调度参数支持的格式38

    2. 时间属性:当前任务需要每天执行一次,因此调度周期选择。时间属性和调度资源属性使用默认配置即可。39

    3. 调度依赖:单击从代码解析输入输出

      由于我们insert当前表dim_ec_mbr_user_info需要from表ods_mbr_user_info和ods_t_area,所以从调度上需要等待两张上游表更新完成,才能开始运当前任务,因此将产出两张上游表的任务作为当前任务的上游。开启代码解析,使用默认解析结果就可以挂在上游表产出任务下。更多关于调度依赖的内容,请参见调度依赖配置指引;若您需要调整调度依赖,请参见配置同周期调度依赖配置依赖上一周期(跨周期依赖)

      请确认以下信息:

      • 同周期 > 依赖的上游节点 > 上游节点输出名retail_e_commerce_2.ods_mbr_user_inforetail_e_commerce_2.ods_t_area

      • 同周期 > 本节点输出 > 输出名retail_e_commerce_2.502637287_outretail_e_commerce_2.dim_ec_mbr_user_info

      40

产出“dim_ec_itm_item_info 商品基础信息维度表”

  1. 创建调度任务:方法同上,参照如下信息。

    • 节点类型:ODPS SQL。

    • 节点名:dim_ec_itm_item_info。

    • 业务流程:业务流程/零售数仓公共层/MaxCompute。

  2. 开发SQL:方法同上,您也可以使用维度建模的模型开发功能,参考如下脚本。

    --模型名称:dim_ec_itm_item_info(商品基础信息维度表)
    --业务分类:电商业务;   数据域:商品域;   业务过程:商品域_默认;   生命周期:365天;
    --模型描述:商品基础信息维度表
    
    INSERT OVERWRITE TABLE dim_ec_itm_item_info PARTITION(ds='${biz_date}')
    SELECT t1.gmt_create AS gmt_create --创建时间
    ,t1.gmt_modified AS gmt_modified --修改时间
    ,t1.item_id AS item_id --商品数字ID
    ,t1.title AS title --商品标题
    ,t1.sub_title AS sub_title --商品子标题
    ,t1.pict_url AS pict_url --主图URL
    ,t1.desc_path AS desc_path --商品描述的路径
    ,t1.item_status AS item_status --商品状态1:确认通过0:未确认通过
    ,t1.last_online_time AS last_online_time --最近一次开始销售时间,商品上架时间
    ,t1.last_offline_time AS last_offline_time --销售结束时间,表示一个销售周期的结束,仅作用于拍卖商品
    ,t1.duration AS duration --有效期,销售周期,只有两个值,7天或14天
    ,cast(t1.reserve_price as DOUBLE) AS reserve_price --当前价格
    ,cast(t1.secure_trade_ordinary_post_fee as DOUBLE)  AS secure_trade_ordinary_post_fee --平邮费用
    ,cast(t1.secure_trade_fast_post_fee as DOUBLE)  AS secure_trade_fast_post_fee --快递费用
    ,cast(t1.secure_trade_ems_post_fee as DOUBLE)  AS secure_trade_ems_post_fee --EMS邮费
    ,t1.last_online_quantity AS last_online_quantity --商品最近一次上架时的库存数量
    ,t1.features AS features --商品特征
    ,t1.cate_id AS cate_id --商品叶子类目ID
    ,t1.cate_name AS cate_name --商品叶子类目名称
    ,t1.commodity_id AS commodity_id --品类ID
    ,t1.commodity_name AS commodity_name --品类名称
    ,t1.is_virtual AS is_virtual --是否虚拟商品
    ,t1.shop_id AS shop_id --商家ID
    ,t1.shop_nick AS shop_nick --商家NICK
    ,t1.is_deleted AS is_deleted --类目是否删除
    FROM ods_item_info t1
    -- FROM retail_e_commerce_2.default.ods_item_info t1
    -- 注意:模型开发自动生成的代码FROM后的表名使用的是“项目名.schema名.表名”,这里的sql中我们仅保留了表名。
    -- 原因一:不指定项目名前缀,在DataStudio运行时会默认访问开发表,发布生产环境自动调度会默认访问生产表。
    -- 原因二:本实验中的sql基本都没有带上schema名,所以这里也不指定schema。使用自动解析时,在对调度依赖还不是很熟的情况下,我们建议都保持一致,所有sql都不带上schema或都带上schema。
    WHERE ds = '${biz_date}';
    ;
  3. 调度配置。

    1. 调度参数:方法同上,新增参数biz_date=${yyyymmdd}

    2. 时间属性:方法同上,使用默认的配置,日调度。

    3. 调度依赖:方法同上,使用自动解析,不需要手动修改。

      请确认以下信息:

      • 同周期 > 依赖的上游节点 > 输出名retail_e_commerce_2.ods_item_info

      • 同周期 > 本节点输出 > 输出名retail_e_commerce_2.502637814_outretail_e_commerce_2.dim_ec_itm_item_info

产出“dwd_ec_trd_create_ord_di 交易下单明细事实表”

  1. 创建调度任务。

    • 节点类型:ODPS SQL。

    • 节点名:dwd_ec_trd_create_ord_di。

    • 业务流程:业务流程/零售数仓公共层/MaxCompute。

  2. 开发SQL。

    INSERT OVERWRITE TABLE dwd_ec_trd_create_ord_di PARTITION(ds='${biz_date}')
    SELECT
      id,
      gmt_create,
      gmt_modified,
      sub_order_id,
      parent_order_id,
      buyer_id,
      buyer_nick,
      item_id,
      item_price,
      buy_amount,
      biz_type,
      memo,
      pay_status,
      logistics_status,
      status,
      seller_memo,
      buyer_memo,
      ip,
      end_time,
      pay_time,
      is_sub,
      is_parent,
      shop_id,
      total_fee
     FROM ods_trade_order
     WHERE   ds = '${biz_date}';
  3. 调度配置。

    1. 调度参数:新增参数biz_date=${yyyymmdd}

    2. 时间属性:使用默认的配置,日调度。

    3. 调度依赖:使用自动解析,不需要手动修改。

      请确认以下信息:

      • 同周期 > 依赖的上游节点 > 输出名retail_e_commerce_2.ods_trade_order

      • 同周期 > 本节点输出 > 输出名retail_e_commerce_2.502638246_outretail_e_commerce_2.dwd_ec_trd_create_ord_di

产出“dws_ec_mbr_cnt_std 历史截至当日存量会员数cube统计表”

  1. 创建调度任务。

    • 节点类型:ODPS SQL。

    • 节点名:dws_ec_mbr_cnt_std。

    • 业务流程:业务流程/零售数仓公共层/MaxCompute。

  2. 开发SQL。

    -- 维度建模建表,这里不需要再创建
    CREATE TABLE IF NOT EXISTS dws_ec_mbr_cnt_std
    (
       reg_prov_id      STRING COMMENT '注册填写省ID',
       reg_prov_name    STRING COMMENT '注册填写省名称',
       reg_gender       STRING COMMENT '身份证解析性别(F女,M男,unkown表示身份证为空或格式不对)',
       reg_gender_name  STRING COMMENT '身份证解析性别(F女,M男,unkown表示身份证为空或格式不对)',
       age_tag          STRING COMMENT '出生年代',
       user_active_type STRING COMMENT '用户激活方式',
       user_active_name STRING COMMENT '激活方式名称',
       vip_level        BIGINT COMMENT 'VIP等级',
       vip_level_name   STRING COMMENT 'VIP等级名称。v1,v2,v3等',
       mbr_cnt          BIGINT COMMENT '存量会员数'
    )
    COMMENT '历史截至当日_存量会员数_cube统计表'
    PARTITIONED BY
    (
       ds STRING COMMENT '业务日期, yyyymmdd'
    )
    LIFECYCLE 365;
    
    INSERT OVERWRITE TABLE dws_ec_mbr_cnt_std PARTITION(ds = '${biz_date}')
    
    SELECT    if(grouping(reg_prov_id) == 0, reg_prov_id, '-9999') as reg_prov_id
            , if(grouping(reg_prov_name) == 0, reg_prov_name, '全部') as reg_prov_name
            , if(grouping(reg_gender) == 0, reg_gender, '-9999') as reg_gender
            , if(grouping(reg_gender_name) == 0, reg_gender_name, '全部') as reg_gender_name
            , if(grouping(age_tag) == 0, age_tag, '-9999') as age_tag
            , if(grouping(user_active_type) == 0, user_active_type, '-9999') as user_active_type
            , if(grouping(user_active_name) == 0, user_active_name, '全部') as user_active_name
            , if(grouping(vip_level) == 0, vip_level, -9999) as vip_level
            , if(grouping(vip_level_name) == 0, vip_level_name, '全部') as vip_level_name
            , count(distinct user_id) as mbr_cnt
    
    from (
        select    reg_prov_id
                , reg_prov_name
                , reg_gender
                , reg_gender_name
                ,(case when substr(reg_birthdate,1,4)>=2010 and substr(reg_birthdate,1,4)<2020 then '10后'
                        when substr(reg_birthdate,1,4)>=2000 and substr(reg_birthdate,1,4)<2010 then '00后'
                        when substr(reg_birthdate,1,4)>=1990 and substr(reg_birthdate,1,4)<2000 then '90后'
                        when substr(reg_birthdate,1,4)>=1980 and substr(reg_birthdate,1,4)<1990 then '80后'
                        when substr(reg_birthdate,1,4)>=1970 and substr(reg_birthdate,1,4)<1980 then '70后'
                        else '其他'
                  end)as age_tag
                , user_active_type
                , user_active_name
                , vip_level
                , vip_level_name
                , user_id
        from    dim_ec_mbr_user_info
        where   ds = max_pt('dim_ec_mbr_user_info')
    ) _main
    
    group by reg_prov_id
            , reg_prov_name
            , reg_gender
            , reg_gender_name
            , age_tag
            , user_active_type
            , user_active_name
            , vip_level
            , vip_level_name
    grouping sets(
        (reg_prov_id, reg_prov_name)
       ,(reg_gender, reg_gender_name)
       ,(age_tag)
       ,(user_active_type, user_active_name)
       ,(vip_level, vip_level_name)
       ,()
    );
  3. 调度配置。

    1. 调度参数:新增参数biz_date=${yyyymmdd}

    2. 时间属性:使用默认的配置,日调度。

    3. 调度依赖:使用自动解析,不需要手动修改。

      请确认以下信息:

      • 同周期 > 依赖的上游节点 > 输出名retail_e_commerce_2.dim_ec_mbr_user_info

      • 同周期 > 本节点输出 > 输出名retail_e_commerce_2.502638770_outretail_e_commerce_2.dws_ec_mbr_cnt_std

产出“dws_ec_trd_cate_commodity_gmv_kpi_fy 财年KPI类目品类GMV统计”

  1. 创建调度任务。

    • 节点类型:ODPS SQL。

    • 节点名:dws_ec_trd_cate_commodity_gmv_kpi_fy。

    • 业务流程:业务流程/零售数仓公共层/MaxCompute。

  2. 开发SQL。

    -- 建模发布建的表,这里不需要再重新创建
    CREATE TABLE IF NOT EXISTS dws_ec_trd_cate_commodity_gmv_kpi_fy
    (
       cate_id         BIGINT COMMENT '商品叶子类目ID',
       cate_name       STRING COMMENT '商品叶子类目名称',
       commodity_id    BIGINT COMMENT '品类ID',
       commodity_name  STRING COMMENT '品类名称',
       pay_ord_amt_fy  DECIMAL COMMENT '财年_订单支付成功金额',
       kpi_gmv_rate_fy DECIMAL COMMENT '财年_成交金额完成度'
    )
    COMMENT '财年KPI_类目_品类_GMV统计'
    PARTITIONED BY
    (
       ds STRING COMMENT '业务日期, yyyymmdd'
    )
    LIFECYCLE 365;
    
    INSERT OVERWRITE TABLE dws_ec_trd_cate_commodity_gmv_kpi_fy PARTITION(ds = '${biz_date}')
    
    select   t1.cate_id
            ,t1.cate_name
            ,t1.commodity_id
            ,t1.commodity_name
            ,round(10*sum(t1.total_fee),4) as pay_ord_amt_fy
            ,round((10*sum(t1.total_fee)/30000000),4) as kpi_gmv_rate_fy
    from    (
                select  to_char(a.gmt_create,'yyyymmdd') as stat_date
                        ,a.sub_order_id
                        ,a.buyer_id
                        ,a.item_id
                        ,a.biz_type
                        ,a.pay_status
                        ,a.total_fee/100 as total_fee
                        ,b.cate_id
                        ,b.cate_name
                        ,b.commodity_id
                        ,b.commodity_name
                from    dwd_ec_trd_create_ord_di a
                left outer join (
                                    select  distinct item_id
                                            ,cate_id
                                            ,cate_name
                                            ,commodity_id
                                            ,commodity_name
                                            ,shop_id
                                            ,shop_nick
                                    from    dim_ec_itm_item_info
                                    where   ds = max_pt('dim_ec_itm_item_info')
                                ) b
                on      a.item_id = b.item_id
                and     a.shop_id = b.shop_id
                where   a.ds = max_pt('dwd_ec_trd_create_ord_di')
            ) t1
    where   t1.pay_status in ('2')
    and     t1.biz_type in ('2','3','4')
    group by   t1.cate_id
             , t1.cate_name
             , t1.commodity_id
             , t1.commodity_name
    ;
  3. 调度配置。

    • 调度参数:新增参数biz_date=${yyyymmdd}

    • 时间属性:使用默认的配置,日调度。

    • 调度依赖:使用自动解析,不需要手动修改。

      请确认以下信息:

      • 同周期 > 依赖的上游节点 > 输出名retail_e_commerce_2.dwd_ec_trd_create_ord_diretail_e_commerce_2.dim_ec_itm_item_info

      • 同周期 > 本节点输出 > 输出名retail_e_commerce_2.502638774_outretail_e_commerce_2.dws_ec_trd_cate_commodity_gmv_kpi_fy

产出“ads_ec_ec360_gmv_kpi_overview 电商360KPI概览”

  1. 创建调度任务。

    • 节点类型:ODPS SQL。

    • 节点名:ads_ec_ec360_gmv_kpi_overview。

    • 业务流程:业务流程/零售数仓公共层/MaxCompute。

  2. 开发SQL。

    -- 维度建模建表,这里不需要再创建。
    CREATE TABLE IF NOT EXISTS ads_ec_ec360_gmv_kpi_overview
    (
        pay_ord_amt_fy DECIMAL COMMENT '财年_订单支付成功金额'
        ,mbr_cnt_std BIGINT COMMENT '历史截至当日_存量会员数'
        ,kpi_gmv_rate_fy DECIMAL COMMENT '财年_成交金额完成度'
    )
    COMMENT '电商360KPI概览'
    PARTITIONED BY
    (
        ds STRING COMMENT '业务日期, yyyymmdd'
    )
    LIFECYCLE 365
    ;
    
    -- 指标计算口径:
    -- 1. pay_ord_amt_fy 财年订单支付成功金额。订单类型包含:等待卖家发货、等待买家确认收货、交易成功;订单支付状态包含付款成功。
    -- 2. mbr_cnt_std 历史截止当日存量会员数。存量会员数:注册且激活的会员总量。
    -- 3. kpi_gmv_rate_fy 成交金额目标完成度=成交金额实际完成值/成交金额目标值;成交金额目标值为3000万。
    INSERT OVERWRITE TABLE ads_ec_ec360_gmv_kpi_overview PARTITION(ds = '${biz_date}')
    SELECT  pay_ord_amt_fy
            ,mbr_cnt_std
            ,round(pay_ord_amt_fy/30000000, 4) AS kpi_gmv_rate_fy
    FROM    (
                SELECT  max(pay_ord_amt_fy) AS pay_ord_amt_fy
                        ,max(mbr_cnt_std) AS mbr_cnt_std
                FROM    (
                            SELECT  1 AS id
                                    ,sum(pay_ord_amt_fy) AS pay_ord_amt_fy
                                    ,null AS mbr_cnt_std
                            FROM    dws_ec_trd_cate_commodity_gmv_kpi_fy
                            WHERE   ds = max_pt('dws_ec_trd_cate_commodity_gmv_kpi_fy')
                            UNION
                            SELECT  1 AS id
                                    ,null AS pay_ord_amt_fy
                                    ,SUM(mbr_cnt) AS mbr_cnt_std
                            FROM    dws_ec_mbr_cnt_std
                            WHERE   ds = max_pt('dws_ec_mbr_cnt_std')
                            AND     reg_prov_id = '-9999'
                            AND     reg_gender = '-9999'
                            AND     age_tag = '-9999'
                            AND     user_active_type = '-9999'
                            AND     vip_level =  - 9999
                        ) t1
                GROUP BY id
            ) t2
    ;
                        
  3. 调度配置。

    1. 新增参数:biz_date=${yyyymmdd}

    2. 时间属性:使用默认的配置,日调度。

    3. 调度依赖:使用自动解析,不需要手动修改。

      请确认以下信息:

      • 同周期 > 依赖的上游节点 > 输出名retail_e_commerce_2.dws_ec_trd_cate_commodity_gmv_kpi_fyretail_e_commerce_2.dws_ec_mbr_cnt_std

      • 同周期 > 本节点输出 > 输出名retail_e_commerce_2.502643753_outretail_e_commerce_2.ads_ec_ec360_gmv_kpi_overview

(可选) 建MaxCompute用户自定义函数

对dim_ec_mbr_user_info表的user_regip通过IP2Region函数解析成地域,最后可以可视化展示注册地分布。

  1. 上传资源。

    零售数仓公共层业务流程,MaxCompute引擎模块下新建jar资源,选中上传为ODPS资源,提交并发布。您可以使用Demo文件IP2Region.jar。更多关于UDF的开发,请参见MaxCompute UDF概述41

  2. 注册函数。

    参照如下配置,在零售数仓公共层业务流程,MaxCompute引擎模块下新建GET_REGION函数。42

    函数类型

    其他函数

    MaxCompute引擎实例

    retail_e_commerce_2

    函数名

    GET_REGION

    责任人

    DataWorks

    类名

    org.alidata.odps.udf.Ip2Region

    资源列表

    ip2region.jar

    描述

    IP地址转换地域

    命令格式

    string get_region(string <str>)

    参数说明

    str:必填。STRING类型。待转换成地域的IP字符串。

    返回值

    返回string类型。返回地域名。

    示例

    SELECT GET_REGION('139.209.57.***');-- 吉林省

  3. 使用函数。

    使用如下脚本,新建一个odps sql节点。

    SELECT  user_id
            ,user_regip
            ,GET_REGION(user_regip) AS region
    FROM    dim_ec_mbr_user_info
    WHERE   ds LIKE '%%'
    LIMIT   10

    运行成功后返回结果如下:

    -- 返回结果
    -- user_id    user_regip    region
    -- 17088752    139.209.57.XXX    吉林省
    -- 78468007    106.90.172.XXX    重庆市
    -- 42307553    121.76.42.XXX    上海市
    -- 48261858    182.83.94.XXX    陕西省
    -- 10124462    139.199.116.XX   未知
    -- 52070735    123.232.226.XX   山东省
    -- 55604197    222.46.88.XX    浙江省
    -- 13785369    36.58.241.XX    安徽省
    -- 38112363    210.43.197.XXX    湖南省
    -- 60806246    36.61.121.XX    安徽省

(可选)建Hologres外表,加速分析“ads_ec_ec360_gmv_kpi_overview 电商360KPI概览”

说明

您需要先创建Hologres数据源,并将其绑定至数据开发才可参考下文进行创建Hologres外表的操作步骤。

  1. 创建调度任务。

    • 节点类型:Hologres SQL。

    • 节点名:ads_ec_ec360_gmv_kpi_overview_bi。

    • 业务流程:业务流程、零售数仓公共层、Hologres、数据开发。

    43

  2. 开发SQL。

    BEGIN;
    CREATE FOREIGN TABLE IF NOT EXISTS ads_ec_ec360_gmv_kpi_overview_bi
    (
        pay_ord_amt_fy DECIMAL(38,18)
        ,mbr_cnt_std BIGINT
        ,kpi_gmv_rate_fy DECIMAL(38,18)
        ,ds TEXT
    )
    SERVER odps_server
    OPTIONS (project_name 'retail_e_commerce_2', table_name 'ads_ec_ec360_gmv_kpi_overview');
    
    COMMENT ON FOREIGN TABLE "ads_ec_ec360_gmv_kpi_overview_bi" IS '电商360KPI概览';
    COMMENT ON COLUMN  "ads_ec_ec360_gmv_kpi_overview_bi".pay_ord_amt_fy IS '财年_订单支付成功金额';
    COMMENT ON COLUMN  "ads_ec_ec360_gmv_kpi_overview_bi".mbr_cnt_std IS '历史截至当日_存量会员数';
    COMMENT ON COLUMN  "ads_ec_ec360_gmv_kpi_overview_bi".kpi_gmv_rate_fy IS '财年_成交金额完成度';
    COMMENT ON COLUMN  "ads_ec_ec360_gmv_kpi_overview_bi".ds IS '业务日期, yyyymmdd';
    
    COMMIT;
    
    
    -- 注意当前执行账号要申请源odps生产表权限
    SELECT * FROM ads_ec_ec360_gmv_kpi_overview_bi  WHERE ds like '%' limit 10;
  3. 调度配置。

    1. 调度参数:无。

    2. 时间属性:使用默认的配置,日调度。

    3. 调度依赖:

      • 同周期 > 依赖的上游节点 > 输出名retail_e_commerce_2.ads_ec_ec360_gmv_kpi_overview

      • 同周期 > 本节点输出 > 输出名retail_e_commerce_2.502654249_outretail_e_commerce_2.ads_ec_ec360_gmv_kpi_overview_bi

      44

(可选)建EMR Hive内表

说明

您需要先注册EMR集群至DataWorks才可参考下文进行创建EMR Hive内表的操作步骤,本操作步骤为能力演示,不操作不影响后续实验。

  1. 创建调度任务。

    • 节点类型:EMR hHive。

    • 节点名:demo_emr_hive。

    • 业务流程:业务流程、DEMO_EMR_DLA、EMR、数据开发。

    45

  2. 开发SQL。

    CREATE TABLE IF NOT EXISTS demo_emr_hive
    (
        id STRING COMMENT 'ID'
        ,name STRING COMMENT '姓名'
    )
    PARTITIONED BY
    (
        pt STRING
    )
    ;
    
    INSERT INTO TABLE demo_emr_hive PARTITION(pt = '${bizdate}') VALUES ('001','张三'),('002','李四')
    ;
  3. 调度配置。

    仅演示,不发布调度。

发布节点

将以上所有节点依次发布到生产环境,如果已经发布的则不需要再发布。46