Simple Log Service:Associate a Logstore with a MySQL database to perform query and analysis

Procedure

1. Create a user property table in the MySQL database.

   Create a data table named join_meta to store user IDs, usernames, genders, ages, account balance, registration time, and registration regions.

   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. Create a whitelist for the MySQL database.

   • If you use a MySQL database created on an RDS instance, add the following CIDR blocks to the whitelist: 100.104.0.0/16, 11.194.0.0/16, and 11.201.0.0/16. For more information, see Configure an IP address whitelist.

   • If you use a self-managed MySQL database hosted on a VPC-type ECS instance and the ECS instance is added to a security group, configure security group rules to allow access from the following CIDR blocks: 100.104.0.0/16, 11.194.0.0/16, and 11.201.0.0/16. For more information, see Add a security group rule.

3. Create an external store.

   1. Install Simple Log Service CLI. For more information, see Overview of Simple Log Service CLI.

   2. Create a configuration file named /home/config.json.

   3. Add the following script to the /home/config.json file. Replace the parameter values based on your business requirements.

      {
          "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"
          }
      }

      Parameter	Description
      externalStoreName	The name of the external store. The name must be in lowercase.
      storeType	The type of the data source. Set the value to rds-vpc.
      region	The region. If you use a MySQL database created on an RDS instance, set region to the region where the RDS instance resides. If you use a self-managed MySQL database hosted on a VPC-type ECS instance, set region to the region where the ECS instance resides. Important The RDS instance or the ECS instance must reside in the same region as your Simple Log Service project.
      vpc-id	The ID of the VPC. If you use a MySQL database created on a VPC-type RDS instance, set vpc-id to the ID of the VPC to which the RDS instance belongs. If you use a MySQL database created on a VPC-type AnalyticDB instance, set vpc-id to the ID of the VPC to which the AnalyticDB instance belongs. If you use a self-managed MySQL database hosted on a VPC-type ECS instance, set vpc-id to the ID of the VPC to which the ECS instance belongs. If your MySQL database can be accessed over the Internet, you do not need to configure vpc-id.
      host	The address of your MySQL database. If you use a MySQL database created on a VPC-type RDS instance, set host to an internal endpoint or the private IP address of the RDS instance. If you use a MySQL database created on a VPC-type AnalyticDB instance, set host to an internal endpoint or the private IP address of the AnalyticDB instance. If you use a self-managed MySQL database hosted on a VPC-type ECS instance, set host to the private IP address of the ECS instance. If your MySQL database can be accessed over the Internet, set host to a public endpoint or the public IP address of your database.
      port	The port number. If you use a MySQL database created on an RDS instance, set port to the port of the RDS instance. If you use a self-managed MySQL database hosted on a VPC-type ECS instance, set port to the MySQL service port of the ECS instance.
      username	The username of the account that you use to log on to your MySQL database.
      password	The password of the account that you use to log on to your MySQL database.
      db	The name of your MySQL database.
      table	The name of the table that you want to use in your MySQL database.

   4. Create an external store.

      Replace the value of project_name with the name of the project that you want to use.

      aliyunlog log create_external_store --project_name="log-rds-demo" --config="file:///home/config.json" 

   5. Query the information about the external store.

      If the creation command is successfully run, no responses are returned. You can run the aliyunlog log get_external_store --project_name="log-rds-demo" --store_name="sls_join_meta_store" --format-output=json command to query the details of the created external data store. In the following example, the external data store is a MySQL database created on an RDS instance.

      {
       "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. Use the SQL JOIN syntax to perform association query and analysis.

   1. Log on to the Simple Log Service console.

   2. In the Projects section, click the project that you want to manage.

   3. On the Log Storage > Logstores tab, click the Logstore that you want to manage.

   4. Execute a query statement.

      Specify the userid field in the logs and the uid field in the database table in the query statement.

      • Analyze the distribution of active users by gender.

        * | 
        select 
          case gender when 1 then 'Male' else 'Female' 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

        

      • Analyze the user engagement in different regions.

        * | 
        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

        

      • Analyze the consumption trends of users by gender.

        * | 
        select 
          case gender when 1 then 'Male' else 'Female' 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. Save the query and analysis results to the MySQL database.

   1. Create a data table named report in the MySQL database to store the number of page views (PVs) per minute.

      CREATE TABLE `report` ( 
        `minute` bigint(20) DEFAULT NULL, 
        `pv` bigint(20) DEFAULT NULL 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8

   2. Create an external store for the report table. For more information, see Step 3.

   3. On the query and analysis page of the Logstore, execute the following query statement to save the results to the report table. sls_report_store indicates the name of the external store.

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

      After the results are saved, you can view the results in the MySQL database.