赞
踩
了解更多2017云栖大会·北京峰会 TechInsight & Workshop.
本手册为云栖大会Workshop《云数据·大计算:快速搭建互联网在线运营分析平台》的《数据处理:数据建模与加工》篇而准备。主要阐述在使用DataWorks/MaxCompute过程中如何直读TableStore中的日志数据并进行加工、用户画像,学员可以根据本实验手册,去学习如何创建外部表、编写SQL。
必备条件:
确保阿里云账号处于登录状态。
本示例通过新建脚本文件的方式来创建外部表和内部表两大部分。MaxCompute计算服务访问 TableStore 数据需要有一个安全的授权通道。在这个问题上,MaxCompute结合了阿里云的访问控制服务(RAM)和令牌服务(STS)来实现对数据的安全访问。
当MaxCompute和TableStore的Owner是同一个账号时,登录阿里云账号后 单击此处完成一键授权。
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中可以忽略。
创建表方法同上,本小节附建表语句:
- 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
- );
创建表方法同上,本小节附建表语句:
- 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
- );
创建表方法同上,本小节附建表语句:
- 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
- );
上述三张表创建成功后,保存脚本文件。
向画布中拖入三个ODPS SQL节点,依次命名为ods_user_trace_log、dw_user_trace_log、rpt_user_trace_log,并配置依赖关系如下:
将ip地址转化为地域的自定义函数,有兴趣同学可以点击查看详情。
ip.dat文件的上传方法同上,只是在选择类型为file。
配置项说明如下:
附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};
附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};
附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;
提交成功后工作流任务处于只读状态,如下:
附录:SQL语句如下。
- ---查看rpt_user_trace_log数据情况
- select * from rpt_user_trace_log limit 10;
进入>>数据分析教程
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。