1. 日志信息不够丰富,怎么破
在日志分析场景中,我们经常遇到这样的问题,日志中的信息不完善。例如,日志中包含了用户的点击行为,但是却缺少用户的属性,例如注册信息、资金、道具等信息。
而产品PD、运营同学分析日志的时候,往往需要这种联合分析用户的属性和行为,例如分析用户地域对付费习惯的影响。
想要推动开发同学在日志里边添加一些必要字段?这几乎是不可能的,一方面,跨团队沟通困难,让开发修改程序可能要拖很久;另一方面,有些属性是动态,需要经常改变,例如用户的账户状况。
2. 引入外表,补充日志信息
那么,为了解决信息短缺的问题,我们有什么办法呢?利用SQL中外表概念,和Join语法,把日志抽象成一张表。属性抽象成一张表,两张叫利用外键Join分析。那么属性信息放在哪里呢?
那么属性放在哪里呢?根据信息修改的频率,我们可以选择把数据放在MySQL或者OSS:
对于更新不频繁的数据。我们可以周期性的写一份数据到OSS对象存储上。那么,只需要支付OSS存储的钱即可,不需要支付MySQL保留的计算实例的钱,可以节省下很大一部分开支。
3. 日志服务(SLS)分析平台,提供日志️OSS(MySQL)的交叉分析能力
日志服务提供的这种跨数据源(OSS)的分析能力,可以帮助用户解决以下问题:
节省费用:
- 异构数据,根据数据的特性选择合适的存储系统,最大限度的节省成本。。 对于更新少的数据,选择存放在OSS上,只需要支付少量的存储费用。如果存放在MySQL上,还要支付计算实例的费用。
- OSS是阿里云的存储系统,可以走内网读取数据,免去了流量费用。
节省精力
- 不需要搬迁数据到同一个存储系统中。大家都知道,不同的存储系统,格式和API都不同,要把数据搬迁到同一个系统中,涉及到复杂的数据转换;数据一旦有更新,还要经常维护。在我们轻量级的运行时联合分析平台中,避免数据搬迁,节省了用户精力,解放用户生产力,可以把有限的开发人力投入到主营业务中。
所见即所得
- 当用户需要分析数据时,使用一条SQL,在秒级别即可获得结果。
- 把常用的视图,定义成报表。需要的时候,打开即可看到结果。
使用这种交叉分析,需要通过这几步操作:
- 上传CSV格式文件到OSS。
- 使用API,在日志服务(SLS)定义虚拟表,定义csv文件格式,映射到OSS文件。
- 在日志服务(SLS)控制台,使用SQL引用oss文件。
4. 使用样例
接下来,我们将以实际样例来演示OSS外部存储的使用。
4.1 上传csv文件到OSS
我们定义一份属性文件,在文件中,包含了5列,分别是用户id,用户昵称,性别,省份,年龄
userid,nick,gender,province,age
1,阳光男孩,male,上海,18
2,么么茶,female,浙江,19
3,刀锋1937,male,广东,18
把这份文件,保存成文本user.csv
,使用osscmd上传到OSS上(也可以在OSS控制台上传):
osscmd put ~/user.csv oss://testossconnector/user.csv
4.2 在日志服务(SLS)定义外部存储
SLS可以通过SQL定义虚拟外部表,映射到OSS文件。在SQL中,需要指定三部分信息:
- 表的schema:包含哪些列,每一列的属性是什么。
- OSS访问信息: oss的域名,oss的access id 和access key。
- OSS文件信息:文件在哪个bucket下,文件的object路径是什么。
使用这条SQL,定义一个外部存储名为user_meta:
* | create table user_meta ( userid bigint, nick varchar, gender varchar, province varchar, gender varchar,age bigint) with ( endpoint='oss-cn-hangzhou-internal.aliyuncs.com',accessid='LTA288dDkllsjdsalcxaeewiak',accesskey ='EjsowAkDiq22Ak$kjdskkalclaK',bucket='testossconnector',objects=ARRAY['user.csv'],type='oss')
在SQL中,分别指定了表的属性:
- userid为 bigint类型。
- nick为varchar类型。
- gender为varchar类型。
- province为varchar类型。
- age为bigint类型。
在SQL的with语法中,分别指定endpoint, accessid, accesskey, bucket, objects。 其中,objects是一个array,在array中,可以是多个OSS文件。
执行结果result为true,表示执行成功。
为了验证数据是否正确,我们执行SQL : select * from user_meta
查看结果:
可以看到,在日志服务中,已经成功的定义了外部存储,接下来,我们就可以在SQL中引用该外部存储的信息了。
4.3 日志和OSS文件联合分析
在原始日志中,包含了用户的id信息,那么我们可以通过关联日志中的id和oss文件中的userid,补全日志的信息。
4.3.1 在SQL中关联日志和OSS,输入SQL :
* | select * from chiji_accesslog l join user_meta1 u on l.userid = u.userid
可以看到,结果中,已经包含了OSS文件的内容。
4.3.2 接下来,我们可以统计,用户性别的访问情况:
* | select u.gender, count(1) from chiji_accesslog l join user_meta1 u on l.userid = u.userid group by u.gender
4.3.3 也可以统计年龄的访问情况:
* | select u.age, count(1) from chiji_accesslog l join user_meta1 u on l.userid = u.userid group by u.age
4.3.4 统计不同年龄段在时间维度上的访问趋势:
* | select date_trunc('minute',__time__) as minute, count(1) ,u.age from chiji_accesslog l join user_meta1 u on l.userid = u.userid group by u.age,minute