全部产品
Search
文档中心

日志服务:关联Logstore与MySQL数据库进行查询分析

更新时间:Feb 26, 2024

本文以游戏公司数据分析场景为例,介绍日志服务Logstore与MySQL数据库关联分析功能。

前提条件

背景信息

某游戏公司,主要包括两大类数据:用户游戏日志数据和用户元数据。日志服务可实时采集用户游戏日志数据,包括操作、目标、血、魔法值、网络、支付手段、点击位置、状态码、用户ID等信息。然而用户元数据,包括用户的性别、注册时间、地域等信息,不能打印到日志中,所以一般存储到数据库中。现在该公司希望将用户游戏日志与用户元数据进行联合分析,获得最佳的游戏运营方案。

针对上述需求,日志服务查询分析引擎,提供Logstore和外部数据源(ExternalStore,例如MySQL数据库、OSS等)联合查询分析功能。您可以使用SQL的JOIN语法把用户游戏日志和用户元数据关联起来,分析与用户属性相关的指标。除此之外,您还可以将计算结果直接写入外部数据源中,便于结果的进一步处理。背景信息

操作步骤

  1. 在MySQL数据库中,创建用户属性表。

    创建一张名为join_meta的数据表,用于保存用户ID、昵称、性别、年龄、注册时间、账户余额和注册地域。

    CREATE TABLE `join_meta` ( 
      `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, 
      `region` text, PRIMARY KEY (`uid`) 
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  2. 添加白名单。

    • 如果是RDS MySQL数据库,需添加白名单地址100.104.0.0/16、11.194.0.0/16和11.201.0.0/16。更多信息,请参见设置IP白名单

    • 如果是专有网络下ECS上自建的MySQL数据库且ECS上设置了安全组,需设置安全组规则,允许100.104.0.0/16、11.194.0.0/16和11.201.0.0/16网段访问。具体操作,请参见添加安全组规则

  3. 创建ExternalStore。

    1. 安装日志服务CLI。更多信息,请参见CLI概述

    2. 创建配置文件/home/config.json

    3. /home/config.json文件中添加如下脚本,并根据实际情况替换参数配置。

      {
          "externalStoreName":"sls_join_meta_store",
          "storeType":"rds-vpc",
          "parameter":{
              "region":"cn-qingdao",
              "vpc-id":"vpc-m5eq4irc1pucp*******",
              "host":"localhost",
              "port":"3306",
              "username":"user",
              "password":"****",
              "db":"scmc",
              "table":"join_meta"
          }
      }

      参数

      说明

      externalStoreName

      ExternalStore名称,必须小写。

      storeType

      数据源类型,固定为rds-vpc。

      region

      地域。详细说明如下:

      • 如果是RDS MySQL数据库,则配置region为RDS实例所在地域。

      • 如果是专有网络下ECS上自建的MySQL数据库,则配置region为ECS实例所在地域。

      重要

      RDS实例或ECS实例必须与日志服务Project处于同一地域。

      vpc-id

      VPC ID。详细说明如下:

      • 如果是专有网络下的RDS MySQL数据库,则配置vpc-id为RDS实例所属专有网络的ID。

      • 如果是专有网络下的ADB MySQL数据库,则配置vpc-id为ADB实例所属专有网络的ID。

      • 如果是专有网络下ECS上自建的MySQL数据库,则配置vpc-id为ECS实例所属专有网络的ID。

      • 如果数据库host可以通过公网访问,则vpc-id无需提供

      host

      数据库地址。详细说明如下:

      • 如果是专有网络下的RDS MySQL数据库,则配置host为RDS实例的内网地址(内网域名或内网IP地址)。

      • 如果是专有网络下的ADB MySQL数据库,则配置host为ADB实例的内网地址(内网域名或内网IP地址)。

      • 如果是专有网络下ECS上自建的MySQL数据库,则配置host为ECS的私网IP地址。

      • 如果数据库通过公网可以访问,则配置对应的公网域名或公网IP地址。

      port

      端口号。详细说明如下:

      • 如果是RDS MySQL数据库,则配置port为RDS实例的端口号。

      • 如果是专有网络下ECS上自建的MySQL数据库,则配置port为ECS上MySQL的服务端口。

      username

      数据库用户名。

      password

      数据库密码。

      db

      数据库。

      table

      数据库表。

    4. 创建ExternalStore。

      其中project_name为日志服务Project名称,请根据实际情况替换。

      aliyunlog log create_external_store --project_name="log-rds-demo" --config="file://./home/config.json" 
    5. 查询ExternalStore信息

      命令执行成功后,无响应消息。您可以执行aliyunlog log get_external_store --project_name="log-rds-demo" --store_name="sls_join_meta_store" --format-output=json查询RDS MySQL外部数据源详细信息。

      {
       "externalStoreName": "sls_join_meta_store",
       "parameter": {
       "db": "scmc",
       "host": "rm-bp1******rm76.mysql.rds.aliyuncs.com",
       "instance-id": "",
       "port": "3306",
       "region": "cn-qingdao",
       "table": "join_meta",
       "timezone": "",
       "username": "user",
       "vpc-id": "vpc-m5eq4irc1pucp*******"
       },
       "storeType": "rds-vpc"
      }
  4. 使用JOIN语法进行联合查询分析。

    1. 登录日志服务控制台

    2. 在Project列表区域,单击目标Project。

    3. 日志存储 > 日志库页签中,单击目标Logstore。

    4. 执行查询分析语句。

      指定日志中的userid字段和数据库表中的uid字段关联Logstore和MySQL数据库。

      • 分析活跃用户的性别分布。

        * | 
        select 
          case gender when 1 then '男性' else '女性' end as gender, 
          count(1) as pv 
        from log l join sls_join_meta_store u on l.userid = u.uid 
        group by gender 
        order by pv desc

        关联分析

      • 分析不同地域活跃度。

        * | 
        select region , count(1) as pv 
        from log l join sls_join_meta_store u on l.userid = u.uid 
        group by region 
        order by pv desc

        活跃度

      • 分析不同性别的消费情况。

        * | 
        select 
          case gender when 1 then '男性' else '女性' end as gender, 
          sum(money) as money 
        from log l join sls_join_meta_store u on l.userid = u.uid 
        group by gender 
        order by money desc
  5. 保存查询分析结果到MySQL数据库中。

    1. 在MySQL数据库中,创建名为report的数据表,该表存储每分钟的PV值。

      CREATE TABLE `report` ( 
        `minute` bigint(20) DEFAULT NULL, 
        `pv` bigint(20) DEFAULT NULL 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    2. 参见步骤3为report表创建ExternalStore。

    3. 在日志服务Logstore的查询分析页面中,执行如下查询语句将分析结果保存到report表,其中sls_report_store对应的是sls的外表名称。

      * | insert into sls_report_store select __time__- __time__ % 300 as min, count(1) as pv group by min

      保存成功后,您可以在MySQL数据库中查看保存结果。SQL结果