Simple Log Service:Associate a Logstore with a MySQL database for 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.

   RDS for MySQL database

   Add the CIDR blocks 100.104.0.0/16, 11.194.0.0/16, and 11.201.0.0/16 to the whitelist. For more information, see Configure an IP address whitelist.

   Self-managed MySQL database on an ECS instance

   Add security group rules to allow access from the 100.104.0.0/16, 11.194.0.0/16, and 11.201.0.0/16 CIDR blocks. For more information, see Add a security group rule.

   AnalyticDB for MySQL database

   Add the CIDR blocks 100.104.0.0/16, 11.194.0.0/16, and 11.201.0.0/16 to the whitelist. For more information, see Configure a whitelist.

3. Create an external store.

   1. Log on to the server where the CLI is installed. Run the touch command to create the /home/shell/config.json configuration file. Add the following script to the config.json file. Replace the values of the region, vpc-id, host, port, username, password, db, and table parameters as needed.

      1. Parameter description	Example { "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 The name of the ExternalStore. The name must be in lowercase.
         storeType The type of the data source. Set this parameter to rds-vpc.
         region The region where the database instance resides. The details are as follows: If you use an ApsaraDB RDS for MySQL database, set the region parameter to the region of the RDS instance. If you use an AnalyticDB for MySQL database, set the region parameter to the region of the AnalyticDB for MySQL instance. If you use a self-managed MySQL database on an ECS instance, set the region parameter to the region of the ECS instance. Important The RDS instance, AnalyticDB for MySQL instance, or ECS instance must be in the same region as the SLS project.
         vpc-id The ID of the VPC where the database instance resides. The details are as follows: If your RDS PostgreSQL instance is in a virtual private cloud (VPC), set vpc-id to the ID of the VPC. For an ADB PostgreSQL database in a virtual private cloud (VPC), set vpc-id to the ID of that VPC. If the Alibaba Cloud Hologres database is in a virtual private cloud (VPC), set vpc-id to the ID of the VPC.
         host   The address of the database. The details are as follows: In a VPC, if the IP address of a database instance changes after you create an external table, access to the external table is affected. For example, this can happen if the database instance is migrated. This issue occurs even if you use an internal endpoint in the configuration. When the external table is created, the backend resolves the domain name to an IP address and saves the IP address to the backend configuration. The IP address that corresponds to the domain name is not automatically refreshed. In this case, you must update or re-create the external table. If you use an ApsaraDB RDS for MySQL database, set the host parameter to the internal address of the RDS instance. The internal address can be an internal endpoint or a private IP address. If you use an AnalyticDB for MySQL database, set the host parameter to the internal address of the AnalyticDB for MySQL instance. The internal address can be an internal endpoint or a private IP address. If you use a self-managed MySQL database on an ECS instance, set the host parameter to the private IP address of the ECS instance. If the database is accessible over the Internet, set this parameter to the public domain name or public IP address.
         port The port number. The details are as follows: If you use an ApsaraDB RDS for MySQL database, set the port parameter to the port number of the RDS instance. If you use an AnalyticDB for MySQL database, set the port parameter to the port number of the AnalyticDB for MySQL instance. If you use a self-managed MySQL database on an ECS instance, set the port parameter to the service port of MySQL on the ECS instance.
         username The username of the database account.
         password The password of the database account.
         db The name of the database.
         table The name of the database table. The following formats are supported: `table_name`, such as `test`. `schema_name.table_name`, such as `public.test`.

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 you want.

   3. On the Log Storage > Logstores tab, click the logstore you want.

   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, view the results in the MySQL database.