Firefox 编程 wordpress 微软 开源 mysql shell apache php linux 程序员 Android centos Python Ubuntu nginx 云计算 google java Windows

遊戲日誌分析5:數據庫與日誌關聯分析

在日誌分析場景中,我們經常會遇到數據分散在各地場景,例如:

用戶操作、行為相關的數據在日誌中
用戶屬性、註冊信息,資金、道具等狀態存在DB中
根據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. 采集日誌到日誌服務

移動端采集

Android
iOS
服務器日誌采集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功能,幫助用戶擴展日誌的信息。通過關聯日誌和用戶屬性,獲取更多有價值的信息。

本文為雲棲社區原創內容,未經允許不得轉載,如需轉載請發送郵件至yqeditor@list.alibaba-inc.com;如

延伸阅读

    评论