游戏日志分析5:数据库与日志关联分析-阿里云开发者社区

开发者社区> 阿里云存储服务> 正文

游戏日志分析5:数据库与日志关联分析

简介: 在日志分析场景中,我们经常会遇到数据分散在各地场景,例如: 1. 用户操作、行为相关的数据在日志中 2. 用户属性、注册信息,资金、道具等状态存在DB中 3. 根据1和2我们需要对用户进行分层统计,将最后的计算结果写入DB中供报表系统查询 因此为了做分析,我们要在日志服务Logstore.

系列文章:

在日志分析场景中,我们经常会遇到数据分散在各地场景,例如:

  1. 用户操作、行为相关的数据在日志中
  2. 用户属性、注册信息,资金、道具等状态存在DB中
  3. 根据1和2我们需要对用户进行分层统计,将最后的计算结果写入DB中供报表系统查询

因此为了做分析,我们要在日志服务Logstore和其他数据源中进行关联查询。以下我们就来看一个例子

用户日志数据

下图是一条经典的游戏日志样例,包括操作、目标、血、魔法值、网络、支付手段、点击位置、状态码、用户id。日志数据量一般规模会随着用户数目+活动频率相关,对一个大型游戏而言,一天会有几百G-几TB规模。

image.png | left

用户元信息

日志表示是增量的事件,一些静态的用户信息,例如用户的性别、注册时间、地区等等是固定不变的,或者在客户端很难获取,不能够打印到日志里。我们把这些信息称为用户元信息。元数据一般和用户数目相关,一般在几百MB-十几GB之间。

在分析日志的时候,需要结合事件日志和用户元信息才能拿到结果,例如性别对支付行为的影响。性别只保存在用户元信息中,在日志中则没有。我们需要通过用户id把日志和用户信息关联起来分析。

下图是用户元信息样例,包括了用户的id、昵称、性别、年龄、注册时间、账户余额、省份。

image.png | left

日志服务和MySQL关联分析

  • 日志服务LogStore,提供日志的收集、存储、查询分析。
  • 日志服务ExternalStore,映射到RDS表。开发者把用户信息放到rds表中。

日志服务查询分析引擎,提供跨LogStore和ExternalStore的查询分析功能,使用SQL的join语法把日志和用户元信息关联起来。用户可以用来分析跟用户属性相关的指标。

image.png | left

除在查询过程中引用ExternalStore之外,日志服务还支持将计算结果直接写入ExternalStore中(例如MySQL),方便结果的进一步处理。

如何使用?

1. 采集日志到日志服务

  1. 移动端采集

    1. Android
    2. iOS
  2. 服务器日志采集ilogtail

收集的日志样例:

image.png | left

2. 创建MySQL表,保存用户属性

创建一张chiji_user表,保存用户的id、昵称、性别、年龄、注册时间、账户余额、注册省份。

CREATE TABLE `chiji_user` (
  `uid` int(11) NOT NULL DEFAULT '0',
  `user_nick` text,
  `gender` tinyint(1) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `register_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `balance` float DEFAULT NULL,
  `province` text,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3. 创建ExternalStore

创建ExternalStore需要使用日志服务cli,首先安装cli:

pip install -U aliyun-log-cli

创建ExternalStore,指定所属的project,以及ExternalStore的配置文件/root/config.json

aliyunlog log create_external_store --project_name="log-rds-demo" --config="file:///root/config.json" 

/root/config.json的内容是:

{
    "externalStoreName": "chiji_user",
    "storeType": "rds-vpc",
    "parameter": {
        "vpc-id": "vpc-m5eq4irc1pucpk85frr5j",
        "instance-id": "rm-m5ep2z57814qsn97b",
        "host": "rm-m5ep2z57814qsn97b.mysql.rds.aliyuncs.com",
        "port": "3306",
        "username": "testroot",
        "password": "123456789",
        "db": "chiji",
        "table": "chiji_user",
        "region": "cn-qingdao"
    }
}

在配置文件中,指定外部存储的名称,以及外部存储的参数。rds vpc需要指定的参数有:vpc-id ,rds实例id,内网域名、端口、用户名、密码、数据库和表名、rds所属region。

添加白名单

在rds中,添加白名单地址100.104.0.0/16
如果是mysql,请添加该地址到安全组

4. 关联分析

4.1 分析活跃用户的性别分布

使用join语法,通过指定日志中的userid和rds中的uid相等来关联日志和用户属性。

* | select  case gender  when 1 then '男性'  else  '女性'  end as gender , count(1) as pv 
  from log l join chiji_user u on l.userid = u.uid  group by gender   order by pv desc

image.png | left

4.2 分析不同省份的越活度

* | select  province , count(1) as pv   from log l join chiji_user u on l.userid = u.uid  group by province   order by pv desc

image.png | left

4.3 分析不同性别的消费情况

* | select  case gender  when 1 then '男性'  else  '女性'  end as gender , sum(money)  as  money  from log l join chiji_user u on l.userid = u.uid  group by gender   order by money  desc

5. 保存查询分析结果

  • 首先创建结果表,该表存储每分钟的PV值:
CREATE TABLE `report` (
  `minute` bigint(20) DEFAULT NULL,
  `pv` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  • 同样的,参考上文创建ExternalStore的步骤,给report表撞见ExternalStore。

编写SQL把结果保存到report:

* |  insert into report select __time__- __time__ % 300 as min, count(1) as pv group by min

SQL返回的结果是最终输出到rds中的行数。最终report表的结果:

image.png | left

总结

日志服务强大的查询分析能力,帮助游戏开发者去分析用户的习惯。同时,日志服务提供的ExternalStore功能,帮助用户扩展日志的信息。通过关联日志和用户属性,获取更多有价值的信息。

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

分享:

阿里云存储基于飞天盘古2.0分布式存储系统,产品多种多样,充分满足用户数据存储和迁移上云需求。

官方博客
链接