系列文章：
- 游戏日志分析（1）：概览
- 游戏日志分析（2）：全方位数据采集
- 游戏日志分析（3）：程序日志规范与埋点
- 游戏日志分析（4）：线上问题定位与排查
- 游戏日志分析（5）：数据库与日志关联分析
- 游戏日志分析（6）：CDN/对象存储日志分析
- 游戏日志分析（7）：网络日志查询与分析
- 游戏日志分析（8）：数据库日志分析
- 游戏日志分析（9）：安全日志分析
- 游戏日志分析（10）：数据可视化与报表
- 游戏日志分析（11）：数仓建设
- 游戏日志分析（12）：实时数据处理与流计算
在日志分析场景中，我们经常会遇到数据分散在各地场景，例如：
- 用户操作、行为相关的数据在日志中
- 用户属性、注册信息，资金、道具等状态存在DB中
- 根据1和2我们需要对用户进行分层统计，将最后的计算结果写入DB中供报表系统查询
因此为了做分析，我们要在日志服务Logstore和其他数据源中进行关联查询。以下我们就来看一个例子
用户日志数据
下图是一条经典的游戏日志样例，包括操作、目标、血、魔法值、网络、支付手段、点击位置、状态码、用户id。日志数据量一般规模会随着用户数目+活动频率相关，对一个大型游戏而言，一天会有几百G-几TB规模。
用户元信息
日志表示是增量的事件，一些静态的用户信息，例如用户的性别、注册时间、地区等等是固定不变的，或者在客户端很难获取，不能够打印到日志里。我们把这些信息称为用户元信息。元数据一般和用户数目相关，一般在几百MB-十几GB之间。
在分析日志的时候，需要结合事件日志和用户元信息才能拿到结果，例如性别对支付行为的影响。性别只保存在用户元信息中，在日志中则没有。我们需要通过用户id把日志和用户信息关联起来分析。
下图是用户元信息样例，包括了用户的id、昵称、性别、年龄、注册时间、账户余额、省份。
日志服务和MySQL关联分析
- 日志服务LogStore，提供日志的收集、存储、查询分析。
- 日志服务ExternalStore，映射到RDS表。开发者把用户信息放到rds表中。
日志服务查询分析引擎，提供跨LogStore和ExternalStore的查询分析功能，使用SQL的join语法把日志和用户元信息关联起来。用户可以用来分析跟用户属性相关的指标。
除在查询过程中引用ExternalStore之外，日志服务还支持将计算结果直接写入ExternalStore中（例如MySQL），方便结果的进一步处理。
如何使用？
1. 采集日志到日志服务
收集的日志样例：
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
AI 代码解读
3. 创建ExternalStore
创建ExternalStore需要使用日志服务cli，首先安装cli：
pip install -U aliyun-log-cli
AI 代码解读
创建ExternalStore，指定所属的project，以及ExternalStore的配置文件/root/config.json
aliyunlog log create_external_store --project_name="log-rds-demo" --config="file:///root/config.json"
AI 代码解读
/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"
}
}
AI 代码解读
在配置文件中，指定外部存储的名称，以及外部存储的参数。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
AI 代码解读
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
AI 代码解读
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
AI 代码解读
5. 保存查询分析结果
- 首先创建结果表，该表存储每分钟的PV值：
CREATE TABLE `report` (
`minute` bigint(20) DEFAULT NULL,
`pv` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
AI 代码解读
- 同样的，参考上文创建ExternalStore的步骤，给report表撞见ExternalStore。
编写SQL把结果保存到report：
* | insert into report select __time__- __time__ % 300 as min, count(1) as pv group by min
AI 代码解读
SQL返回的结果是最终输出到rds中的行数。最终report表的结果：
总结
日志服务强大的查询分析能力，帮助游戏开发者去分析用户的习惯。同时，日志服务提供的ExternalStore功能，帮助用户扩展日志的信息。通过关联日志和用户属性，获取更多有价值的信息。
