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

开发者社区> 祎休> 正文

北京云栖大会workshop:《数据处理:数据建模与加工》篇

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

实验背景介绍

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

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

实验涉及大数据产品

实验环境准备

必备条件:

  • 开通大数据计算服务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节点,进入节点配置界面,编写处理逻辑。

DW

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

进入>>数据分析教程

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

附件下载:https://developer.aliyun.com/topic/download?id=510

相关文章
云栖大会DataWorks专场,5位嘉宾都讲了啥?
9月20日上午,在云栖大会DataWorks专场,除了阿里巴巴嘉宾外,也邀请了世纪联华共同分享大数据研发平台的方向和应用,让我们一起看一下 DataWorks有哪些过人之处。
1547 0
2017云栖大会·杭州峰会:《在线用户行为分析:基于流式计算的数据处理及应用》Workshop-入口
2017云栖大会·杭州峰会:《在线用户行为分析:基于流式计算的数据处理及应用》Workshop-入口
3099 0
2017云栖大会·杭州峰会:《在线用户行为分析:基于流式计算的数据处理及应用》实验环境准备
2017云栖大会·杭州峰会:《在线用户行为分析:基于流式计算的数据处理及应用》实验环境准备
5209 0
云栖大会马云演讲:以前制造业靠电,未来靠数据
9月19日,2018杭州云栖大会在云栖小镇开幕。本次云栖大会以“驱动数字中国”为主题,关注政务创新。阿里称有来自60多个国家的CEO、CTO参会,超过12万人现场参会。 长达近四个小时的主论坛上有不少新发布,先来看几个亮点: 阿里巴巴城市大脑2.
18891 0
U-Time巡回完美收官 演讲嘉宾干货分享:数据篇
导读:&ldquo;如何开展更贴近业务的数据分析,如何更高效的进行垂直化运营&rdquo;这是很多企业面临的问题,绕不开的一道坎。日前,【友盟+】U-Time巡回沙龙于7月12日落下帷幕,解答了该问题。&nbsp;5位【友盟+】产品专家、9位知名企业嘉宾、2000+位参会者,相聚上海、杭州、深圳、广州四座城市,共同探讨了&ldquo;更懂业务的数据运营新玩法&rdquo;。 来源:【友盟+】
1425 0
201604深圳云栖大会Workshop - 阿里容器服务与持续集成
目标 熟悉容器服务的基本概念 练习通过容器服务实现持续集成的过程 准备工作 容器服务控制台 容器服务控制台 镜像管理平台 阿里Code 帮助文档 产品概念解释 注意事项:预计耗费的费用 容器服务暂时不收费;演示过程中会创建ECS实例和SLB,按量收费。 步骤 1. 创建集群
5454 0
GPS轨迹数据集免费下载资源整理
本文为转载文章 转载请注明出处: https://blog.csdn.net/liangyihuai/article/details/58335510#comments 本文主要是整理了GPS轨迹数据集免费资源库,从这些库中能够免费下载到GPS数据,同时还整理出了这些数据的格式,数据集的简单描述等等。
5541 0
连载:阿里巴巴大数据实践—数据建模综述
数据模型就是数据组织和存储方法,它强调从业务、数据存取和使用角度合理存储数据。
3884 0
2017云栖大会·杭州峰会:《在线用户行为分析:基于流式计算的数据处理及应用》之《流数据处理:通过StreamSQL分析视频日志》篇
2017云栖大会·杭州峰会:《在线用户行为分析:基于流式计算的数据处理及应用》之《流数据处理:通过StreamSQL分析视频日志》篇
2532 0
Contoso 大学 - 4 - 创建更加复杂的数据模型
原文 Contoso 大学 - 4 - 创建更加复杂的数据模型 原文地址:http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/creating-a-more-complex-data-model-for-an-asp-net-mvc-application 全文目录:Contoso 大学 - 使用 EF Code First 创建 MVC 应用 4 – 创建更加复杂的数据模型 在前面的课程中,你已经创建了一个简单的由三个实体组成的数据模型。
969 0
+关注
祎休
阿里云MaxCompute产品经理,帮助每一个想使用大数据计算服务的同学轻松上云。
41
文章
0
问答
来源圈子
更多
MaxCompute(原ODPS)是一项面向分析的大数据计算服务,它以Serverless架构提供快速、全托管的在线数据仓库服务,消除传统数据平台在资源扩展性和弹性方面的限制,最小化用户运维投入,使您经济并高效的分析处理海量数据。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载