在日志分析场景中,我们经常会遇到数据分散在各地的场景。而我们需要用日志和数据库中的数据对用户进行分层统计,将最后的计算结果写入数据库中供报表系统查询,因此,我们要在日志服务Logstore和其他数据源中进行关联查询。

背景信息

  • 用户日志数据:以游戏日志为例,一条经典的游戏日志,包括操作、目标、血、魔法值、网络、支付手段、点击位置、状态码、用户id等。
  • 用户元数据:日志表示的是增量的事件,一些静态的用户信息,例如用户的性别、注册时间、地区等是固定不变的,在客户端很难获取,不能够打印到日志里。我们把这些信息称为用户元信息。
  • 日志服务和MySQL关联分析:日志服务查询分析引擎,提供跨Logstore和ExternalStore的查询分析功能,使用SQL的join语法把日志和用户元信息关联起来,用来分析跟用户属性相关的指标。除在查询过程中引用ExternalStore,日志服务还支持将计算结果直接写入ExternalStore中(例如MySQL),方便结果的进一步处理。
    • 日志服务Logstore:提供日志的收集、存储、查询分析。
    • 日志服务ExternalStore:映射到RDS表,开发者把用户信息放到RDS表中。


操作步骤

  1. 采集日志到日志服务。
  2. 创建用户属性表。
    创建一张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。
    1. 创建ExternalStore需要使用日志服务CLI,因此需要安装CLI。
      pip install -U aliyun-log-cli
    2. 创建ExternalStore,指定所属的Project,以及ExternalStore的配置文件/root/config.json。
      aliyunlog log create_external_store --project_name="log-rds-demo" --config="file:///root/config.json" 
    3. 在配置文件中,指定外部存储的名称,参数。RDS VPC需要指定的参数有:vpc-id ,RDS实例id,域名、端口、用户名、密码、数据库和表名、RDS所属region。
      { 
           "externalStoreName": "chiji_user", 
           "storeType": "rds-vpc", 
           "parameter": { 
           "vpc-id": "vpc-m5eq4irc1pucpk85f****", 
           "instance-id": "rm-m5ep2z57814qs****", 
           "host": "example.com", 
           "port": "3306", 
           "username": "testroot", 
           "password": "123456789", 
           "db": "chiji", 
           "table": "chiji_user", 
           "region": "cn-qingdao" 
        } 
      }
  4. 添加白名单。
    • 在RDS中,添加白名单地址100.104.0.0/16
    • 如果是MySQL,请添加该地址到安全组。
  5. 关联分析。
    • 分析活跃用户的性别分布。
      使用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

      关联分析
    • 分析不同省份活跃度。
      * | 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

      活跃度
    • 分析不同性别的消费情况。
      * | 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
  6. 保存查询分析结果。
    1. 创建结果表,该表存储每分钟的PV值。
      CREATE TABLE `report` ( 
        `minute` bigint(20) DEFAULT NULL, 
        `pv` bigint(20) DEFAULT NULL 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    2. 参考上述创建ExternalStore的方法给report表创建ExternalStore,然后将结果保存到report。
      * | insert into report select __time__- __time__ % 300 as min, count(1) as pv group by min
      SQL返回的结果是最终输出到RDS中的行数。
      SQL结果