关联Logstore与MySQL数据库进行查询分析

操作步骤

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数据库

   需设置安全组规则，允许100.104.0.0/16、11.194.0.0/16和11.201.0.0/16网段访问。具体操作，请参见添加安全组规则。

   ADB MySQL数据库

   需添加白名单地址100.104.0.0/16、11.194.0.0/16和11.201.0.0/16。更多信息，请参见设置白名单。

3. 创建ExternalStore。

   1. 登录安装CLI所在的服务器，在命令行执行touch命令，创建配置文件/home/shell/config.json。在config.json中添加如下脚本。根据实际情况您需要替换region、vpc-id、host、port、username、password、db和table的值。

      1. 参数说明	示例 { "externalStoreName":"sls_join_meta_store", "storeType":"rds-vpc", "parameter":{ "region":"cn-qingdao", "vpc-id":"vpc-m5eq4irc1pucp*******", "host":"rm-bp1******rm76.mysql.rds.aliyuncs.com", "port":"3306", "username":"user", "password":"****", "db":"scmc", "table":"join_meta" } }
         externalStoreName ExternalStore名称，必须小写。
         storeType 数据源类型，固定为rds-vpc。
         region 地域。详细说明如下： 如果是RDS MySQL数据库，则配置region为RDS实例所在地域。 如果是ADB MySQL数据库，则配置region为ADB实例所在地域。 如果是专有网络下ECS上自建的MySQL数据库，则配置region为ECS实例所在地域。 重要 RDS实例、ADB实例或ECS实例必须与日志服务Project处于同一地域。
         vpc-id VPC ID。详细说明如下： 如果是专有网络下的RDS PostgreSQL数据库，则配置vpc-id为RDS实例所属专有网络的ID。 如果是专有网络下的ADB PostgreSQL数据库，则配置vpc-id为ADB实例所属专有网络的ID。 如果是专有网络下阿里云Hologres数据库，则配置vpc-id为阿里云Hologres所属专有网络的ID。
         host   数据库地址。详细说明如下： 在专有网络下，外表创建后，如果数据库的实例IP发生变化（比如数据库实例发生迁移），则会影响数据库外表的访问（即使配置中采用了内网域名，因为创建外表时，后端会自动基于域名解析IP，并保存到后端配置中，当前暂不支持自动刷新域名对应的IP）。这种情况下，需要对外表进行更新或者重建。 如果是专有网络下的RDS MySQL数据库，则配置host为RDS实例的内网地址（内网域名或内网IP地址）。 如果是专有网络下的ADB MySQL数据库，则配置host为ADB实例的内网地址（内网域名或内网IP地址）。 如果是专有网络下ECS上自建的MySQL数据库，则配置host为ECS的私网IP地址。 如果数据库通过公网可以访问，则配置对应的公网域名或公网IP地址。
         port 端口号。详细说明如下： 如果是RDS MySQL数据库，则配置port为RDS实例的端口号。 如果是ADB MySQL数据库，则配置port为ADB实例的端口号。 如果是专有网络下ECS上自建的MySQL数据库，则配置port为ECS上MySQL的服务端口。
         username 数据库用户名。
         password 数据库密码。
         db 数据库名。
         table 数据库表名，支持两种格式： table_name，例如test。 schema_name.table_name，例如public.test。

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数据库中查看保存结果。