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

本文涉及的产品
对象存储 OSS,20GB 3个月
日志服务 SLS,月写入数据量 50GB 1个月
阿里云盘企业版 CDE,企业版用户数5人 500GB空间
简介: 在日志分析场景中,我们经常会遇到数据分散在各地场景,例如: 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功能,帮助用户扩展日志的信息。通过关联日志和用户属性,获取更多有价值的信息。

相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
目录
相关文章
|
1月前
|
存储 SQL Apache
Apache Doris 开源最顶级基于MPP架构的高性能实时分析数据库
Apache Doris 是一个基于 MPP 架构的高性能实时分析数据库,以其极高的速度和易用性著称。它支持高并发点查询和复杂分析场景,适用于报表分析、即席查询、数据仓库和数据湖查询加速等。最新发布的 2.0.2 版本在性能、稳定性和多租户支持方面有显著提升。社区活跃,已广泛应用于电商、广告、用户行为分析等领域。
Apache Doris 开源最顶级基于MPP架构的高性能实时分析数据库
|
27天前
|
存储 SQL 监控
|
27天前
|
运维 监控 安全
|
23天前
|
存储 人工智能 关系型数据库
拥抱Data+AI|玩家去哪儿了?解码Data+AI如何助力游戏日志智能分析
本文为阿里云瑶池数据库「拥抱Data+AI」系列连载第2篇,基于真实客户案例和最佳实践,探讨如何利用阿里云Data+AI解决方案应对游戏行业挑战,通过AI为游戏行业注入新的活力。文章详细介绍了日志数据的实时接入、高效查询、开源开放及AI场景落地,展示了完整的Data+AI解决方案及其实际应用效果。
|
24天前
|
存储 人工智能 关系型数据库
拥抱Data+AI|解码Data+AI助力游戏日志智能分析
「拥抱Data+AI」系列第2篇:阿里云DMS+AnalyticDB助力游戏日志数据分析与预测
拥抱Data+AI|解码Data+AI助力游戏日志智能分析
|
1月前
|
监控 关系型数据库 MySQL
分析慢查询日志
【10月更文挑战第29天】分析慢查询日志
37 3
|
1月前
|
监控 关系型数据库 数据库
怎样分析慢查询日志?
【10月更文挑战第29天】怎样分析慢查询日志?
35 2
|
2月前
|
SQL 关系型数据库 MySQL
Vanna使用ollama分析本地数据库
这篇文章详细介绍了如何使用Vanna和Ollama框架来分析本地数据库,实现自然语言查询转换为SQL语句并与数据库交互的过程。
265 7
Vanna使用ollama分析本地数据库
|
1月前
|
存储 Java 关系型数据库
在Java开发中,数据库连接是应用与数据交互的关键环节。本文通过案例分析,深入探讨Java连接池的原理与最佳实践
在Java开发中,数据库连接是应用与数据交互的关键环节。本文通过案例分析,深入探讨Java连接池的原理与最佳实践,包括连接创建、分配、复用和释放等操作,并通过电商应用实例展示了如何选择合适的连接池库(如HikariCP)和配置参数,实现高效、稳定的数据库连接管理。
56 2
|
2月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1663 14

相关产品

  • 日志服务