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

系列文章：

• 游戏日志分析（1）：概览
• 游戏日志分析（2）：全方位数据采集
• 游戏日志分析（3）：程序日志规范与埋点
• 游戏日志分析（4）：线上问题定位与排查
• 游戏日志分析（5）：数据库与日志关联分析
• 游戏日志分析（6）：CDN/对象存储日志分析
• 游戏日志分析（7）：网络日志查询与分析
• 游戏日志分析（8）：数据库日志分析
• 游戏日志分析（9）：安全日志分析
• 游戏日志分析（10）：数据可视化与报表
• 游戏日志分析（11）：数仓建设
• 游戏日志分析（12）：实时数据处理与流计算

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

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

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

用户日志数据

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

用户元信息

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

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

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

日志服务和MySQL关联分析

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

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

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

如何使用？

1. 采集日志到日志服务

1. 移动端采集

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

收集的日志样例：

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

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

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表的结果：

总结

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