祎休 2017-12-13 3948浏览量
了解更多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;
进入>>数据分析教程
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。
阿里大数据官方技术圈