北京云栖大会workshop：《数据处理：数据建模与加工》篇-阿里云开发者社区

实验背景介绍

了解更多2017云栖大会·北京峰会 TechInsight & Workshop.

本手册为云栖大会Workshop《云数据·大计算：快速搭建互联网在线运营分析平台》的《数据处理：数据建模与加工》篇而准备。主要阐述在使用DataWorks/MaxCompute过程中如何直读TableStore中的日志数据并进行加工、用户画像，学员可以根据本实验手册，去学习如何创建外部表、编写SQL。

实验涉及大数据产品

大数据计算服务 MaxCompute
DataWorks DataWorks

实验环境准备

必备条件：

开通大数据计算服务MaxCompute
创建DataWorks项目空间

进入DataWorks项目

确保阿里云账号处于登录状态。

step1：点击进入DataWorks/MaxCompute管理控制台，选择华北2 Region，点击已经创建的项目空间名称，进入工作区。

项目列表

进入DataWorks

新建数据表

本示例通过新建脚本文件的方式来创建外部表和内部表两大部分。MaxCompute计算服务访问 TableStore 数据需要有一个安全的授权通道。在这个问题上，MaxCompute结合了阿里云的访问控制服务（RAM）和令牌服务（STS）来实现对数据的安全访问。

STS模式授权

当MaxCompute和TableStore的Owner是同一个账号时，登录阿里云账号后单击此处完成一键授权。

1.创建外部表ots_user_trace_log表

step1：点击数据开发，进入数据开发首页中点击新建脚本。

新建脚本

step2：配置文件名称为create_table_ddl，类型选择为ODPS SQL，点击提交。

配置脚本

step3：编写DDL创建表语句。

DDL建表语句如下：

DROP TABLE IF EXISTS ots_user_trace_log;
CREATE EXTERNAL TABLE IF NOT EXISTS ots_user_trace_log 
(
    md5 STRING COMMENT '用户uid的md5值前8位'
    ,uid STRING COMMENT '用户uid'
    ,ts BIGINT COMMENT '用户操作时间戳'
    ,ip STRING COMMENT 'ip地址'
    ,status BIGINT COMMENT '服务器返回状态码'
    ,bytes BIGINT COMMENT '返回给客户端的字节数'
    ,device STRING COMMENT '终端型号'
    ,system STRING COMMENT '系统版本ios xxx/android xxx'
    ,customize_event STRING COMMENT '自定义事件：登录/退出/购买/注册/点击/后台/切换用户/浏览'
    ,use_time BIGINT COMMENT 'APP单次使用时长，当事件为退出、后台、切换用户时有该项'
    ,customize_event_content STRING COMMENT '用户关注内容信息，在customize_event为浏览和评论时 包含该列'
)
STORED BY 'com.aliyun.odps.TableStoreStorageHandler' 
WITH SERDEPROPERTIES ( 
--'odps.properties.rolearn'='acs:ram::1604337383174619:role/aliyunodpsroleforyixiu',
'tablestore.columns.mapping'=':md5,:uid,:ts, ip,status,bytes,device,system,customize_event,use_time,customize_event_content', 
'tablestore.table.name'='user_trace_log' ) 
LOCATION 'tablestore://<自己的TableStore实例名称>.cn-beijing.ots-internal.aliyuncs.com' ;

LOCATION中的实例访问地址可以在上一实验中的TableStore管控台>实例列表>管理>实例详情中获取，进入TableStore管控台。

弹出关于SQL费用预估的弹窗，在本workshop中可以忽略。

step4：选择需要执行的SQL语句，点击运行，直至日志信息返回成功表示表创建成功。

运行DDL

step4：可以使用desc语法来确认创建表是否成功。

DESC

step5：点击保存，保存编写的SQL建表语句。

保存DDL

2.新建ods_user_trace_log表

创建表方法同上，本小节附建表语句:

DROP TABLE IF EXISTS ods_user_trace_log;
CREATE TABLE IF NOT EXISTS ods_user_trace_log (
    md5 STRING COMMENT '用户uid的md5值前8位',
    uid STRING COMMENT '用户uid',
    ts BIGINT COMMENT '用户操作时间戳',
    ip STRING COMMENT 'ip地址',
    status BIGINT COMMENT '服务器返回状态码',
    bytes BIGINT COMMENT '返回给客户端的字节数',
    device STRING COMMENT '终端型号',
    system STRING COMMENT '系统版本ios xxx/android xxx',
    customize_event STRING COMMENT '自定义事件：登录/退出/购买/注册/点击/后台/切换用户/浏览',
    use_time BIGINT COMMENT 'APP单次使用时长，当事件为退出、后台、切换用户时有该项',
    customize_event_content STRING COMMENT '用户关注内容信息，在customize_event为浏览和评论时 包含该列'
)
PARTITIONED BY (
    dt STRING
);

3.新建dw_user_trace_log表

创建表方法同上，本小节附建表语句:

DROP TABLE IF EXISTS dw_user_trace_log;
CREATE TABLE IF NOT EXISTS dw_user_trace_log (
    uid STRING COMMENT '用户uid',
    region STRING COMMENT '地域，根据ip得到',
    device_brand string comment '设备品牌',
    device STRING COMMENT '终端型号',
    system_type STRING COMMENT '系统类型，Android、IOS、ipad、Windows_phone',
    customize_event STRING COMMENT '自定义事件：登录/退出/购买/注册/点击/后台/切换用户/浏览',
    use_time BIGINT COMMENT 'APP单次使用时长，当事件为退出、后台、切换用户时有该项',
    customize_event_content STRING COMMENT '用户关注内容信息，在customize_event为浏览和评论时 包含该列'
)
PARTITIONED BY (
    dt STRING
);

4.新建rpt_user_trace_log表

创建表方法同上，本小节附建表语句:

DROP TABLE IF EXISTS rpt_user_trace_log;
CREATE TABLE IF NOT EXISTS rpt_user_trace_log (
    country STRING COMMENT '国家',
    province STRING COMMENT '省份',
    city STRING COMMENT '城市',
    device_brand string comment '设备品牌',
    device STRING COMMENT '终端型号',
    system_type STRING COMMENT '系统类型，Android、IOS、ipad、Windows_phone',
    customize_event STRING COMMENT '自定义事件：登录/退出/购买/注册/点击/后台/切换用户/浏览',
    use_time BIGINT COMMENT 'APP单次使用时长，当事件为退出、后台、切换用户时有该项',
    customize_event_content STRING COMMENT '用户关注内容信息，在customize_event为浏览和评论时 包含该列',
    pv bigint comment '浏览量',
    uv bigint comment '独立访客'
)
PARTITIONED BY (
    dt STRING
);

上述三张表创建成功后，保存脚本文件。

工作流设计

step1：点击新建 > 新建任务。
step2：在新建任务弹出框中，选择任务类型工作流任务。

向画布中拖入三个ODPS SQL节点，依次命名为ods_user_trace_log、dw_user_trace_log、rpt_user_trace_log，并配置依赖关系如下：

SQL依赖关系

添加资源并创建自定义函数

将ip地址转化为地域的自定义函数，有兴趣同学可以点击查看详情。

step1：点击下载getaddr.jar和ip.dat.
step2：切换至资源管理tab页，点击上传按钮，分别将上述两个资源文件进行上传。

进入资源管理

step3：点击选择文件，选择已经下载到本地的getaddr.jar。

资源上传

step4：点击提交。

ip.dat文件的上传方法同上，只是在选择类型为file。

资源上传

step5：切换至函数管理tab，点击创建函数按钮。

进入函数管理

step6：资源选择getaddr.jar和ip.dat，其他配置项如下所示。

新建函数

配置项说明如下：

函数名：getregion
类名：odps.test.GetAddr
资源：getaddr.jar和ip.dat
step7：点击提交。

配置ODPS SQL节点

1）配置ods_user_trace_log节点：

step1：双击ods_user_trace_log节点，进入节点配置界面，编写处理逻辑。

ODS

附SQL逻辑如下：

insert overwrite table ods_user_trace_log partition (dt=${bdp.system.bizdate})
select 
    md5,
    uid ,
    ts,
    ip,
    status,
    bytes,
    device,
    system,
    customize_event,
    use_time,
    customize_event_content
    from ots_user_trace_log
    where to_char(FROM_UNIXTIME(ts),'yyyymmdd')=${bdp.system.bizdate};

step2：点击保存。

保存ODS

step3：点击返回，返回至工作流开发面板。

返回工作流任务

2）配置dw_user_trace_log节点：

step1：双击dw_user_trace_log节点，进入节点配置界面，编写处理逻辑。

附SQL语句如下：

INSERT OVERWRITE TABLE dw_user_trace_log PARTITION (dt=${bdp.system.bizdate})
SELECT uid, getregion(ip) AS region
    , CASE 
        WHEN TOLOWER(device) RLIKE 'xiaomi' THEN 'xiaomi'
        WHEN TOLOWER(device) RLIKE 'meizu' THEN 'meizu'
        WHEN TOLOWER(device) RLIKE 'huawei' THEN 'huawei'
        WHEN TOLOWER(device) RLIKE 'iphone' THEN 'iphone'
        WHEN TOLOWER(device) RLIKE 'vivo' THEN 'vivo'
        WHEN TOLOWER(device) RLIKE 'honor' THEN 'honor'
        WHEN TOLOWER(device) RLIKE 'samsung' THEN 'samsung'
        WHEN TOLOWER(device) RLIKE 'leeco' THEN 'leeco'
        WHEN TOLOWER(device) RLIKE 'ipad' THEN 'ipad'
        ELSE 'unknown'
    END AS device_brand, device
    , CASE 
        WHEN TOLOWER(system) RLIKE 'android' THEN 'android'
        WHEN TOLOWER(system) RLIKE 'ios' THEN 'ios'
        ELSE 'unknown'
    END AS system_type, customize_event, use_time, customize_event_content
FROM ods_user_trace_log
WHERE dt = ${bdp.system.bizdate};

step2：点击保存。
step3：点击返回，返回至工作流开发面板。

配置rpt_user_trace_log节点

step1：双击进入rpt_user_trace_log节点进入配置界面。

rpt

附SQL代码如下：

INSERT OVERWRITE TABLE rpt_user_trace_log PARTITION (dt=${bdp.system.bizdate})
SELECT split_part(split_part(region, ',', 1),'[',2) AS country
    , trim(split_part(region, ',', 2)) AS province
    , trim(split_part(region, ',', 3)) AS city
    , MAX(device_brand), MAX(device)
    , MAX(system_type), MAX(customize_event)
    , FLOOR(AVG(use_time / 60))
    , MAX(customize_event_content), COUNT(uid) AS pv
    , COUNT(DISTINCT uid) AS uv
FROM dw_user_trace_log
WHERE dt = ${bdp.system.bizdate}
GROUP BY uid, 
    region;

step2：点击保存。
step3：点击返回，返回至工作流开发面板。

提交工作流任务

step1：点击提交，提交已配置的工作流任务。

提交工作流

step2：在变更节点列表弹出框中点击确定提交。

变更节点列表

提交成功后工作流任务处于只读状态，如下：

只读状态

测试运行任务

step1：进入运维中心 > 任务列表 > 周期任务，找到你提交的workshop工作流任务并展开。

运维中心

step2：右键工作流任务名称，并选择点击测试，选择需要执行的业务日期，点击确认。

测试任务

step3：跳转至测试实例页面，点击实例名称并展开测试的DAG图。

测试实例

step4：可以点击右上角的刷新按钮进行查看节点执行情况，直至所有节点为绿色（执行成功）。

刷新按钮

运行成功

确认数据是否成功写入MaxCompute相关表

step1：返回到create_table_ddl脚本文件中。
step2：编写并执行sql语句查看rpt_user_trace_log数据情况。。

数据预览

附录：SQL语句如下。

---查看rpt_user_trace_log数据情况
select * from rpt_user_trace_log limit 10;

进入>>数据分析教程