This topic describes how to use the SQL JOIN statement to query data from Logstores and Relational Database Service (RDS) databases. It also describes how to store the results of the queries in RDS databases.

Prerequisites

Logs are collected. For more information, see Log collection.

Background information

The query and analysis engine of Log Service allows you to query and analyze data from Logstores and RDS databases. You can use an SQL JOIN statement to associate logs with metadata of users. You can also write the computing results to an RDS database. This topic takes ApsaraDB RDS for MySQL as an example.

Procedure

  1. Create an ApsaraDB RDS for MySQL instance in a virtual private cloud (VPC). For more information, see A new version is available..
  2. Configure a whitelist for the RDS instance.
    Configure a whitelist to allow external devices to access the RDS instance. Add one of the following CIDR blocks to the whitelist: 100.104.0.0/16, 11.194.0.0/16 or 11.201.0.0/16. For more information, see Configure a whitelist for an ApsaraDB RDS for MySQL instance.
  3. Create an ApsaraDB RDS for MySQL table. For more information, visit CREATE TABLE Statement.
  4. Create an ExternalStore.
    1. Install Log Service CLI. For more information, see Command line interface.
    2. Create a configuration file named /root/config.json and add the following script to the file.
      Replace the value of each parameter in the script with the actual value.
      {
      "externalStoreName":"storeName",
      "storeType":"rds-vpc",
      "parameter":
         {
         "region":"cn-qingdao",
         "vpc-id":"vpc-m5eq4irc1pucp*******",
         "instance-id":"i-m5eeo2whsn*******",
         "host":"localhost",
         "port":"3306",
         "username":"root",
         "password":"****",
         "db":"scmc",
         "table":"join_meta"
         }
      }
      Parameter Description
      region The region where the RDS instance resides.
      vpc-id The ID of the VPC.
      instance-id The ID of the RDS instance.
      host The domain name of the RDS instance.
      port The port of the RDS instance.
      username The username of the RDS database.
      password The password of the RDS database.
      db The name of the RDS database.
      table The name of the table.
    3. Create an ExternalStore.
      Run the following code. Replace the project_name parameter with the actual name of the project.
      aliyunlog log create_external_store --project_name="log-rds-demo" --config="file:///root/config.json" 
  5. Use a JOIN statement to query logs.
    1. Log on to the Log Service console.
    2. Click the destination project in the Projects section.
    3. Choose Log Management > Logstores, and then click the destination Logstore.
    4. Run a query statement.
      You can run the following JOIN statements: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
      [ INNER ] JOIN
      LEFT [ OUTER ] JOIN
      RIGHT [ OUTER ] JOIN
      FULL [ OUTER ] JOIN
      The following example shows a JOIN statement:
      method:postlogstorelogs | select count(1) , histogram(logstore) from log  l join join_meta m on l.projectid = cast( m.ikey as varchar)
      Note
      • JOIN statements are limited in Logstores and RDS tables whose data volume is less than 20 MB.
      • In the query statements, the Logstore must be written before the join parameter, and the ExternalStore must be written after the join parameter.
      • The name of the ExternalStore must be specified in the query statements. The system replaces the name with the combination of the RDS database name and the name of the RDS table that you want to join with the Logstore.
  6. Save the query results to the ApsaraDB RDS for MySQL database.
    Log Service allows you to insert the query results into the ApsaraDB RDS for MySQL database by using the INSERT statement. The following example shows an INSERT statement:
    method:postlogstorelogs | insert into method_output  select cast(methodasvarchar(65535)),count(1)fromloggroupbymethod

Example

Sample Python script:
# encoding: utf-8
from __future__ import print_function
from aliyun.log import *
from aliyun.log.util import base64_encodestring
from random import randint
import time
import os
from datetime import datetime
    endpoint = os.environ.get('ALIYUN_LOG_SAMPLE_ENDPOINT', 'cn-chengdu.log.aliyuncs.com')
    accessKeyId = os.environ.get('ALIYUN_LOG_SAMPLE_ACCESSID', '')
    accessKey = os.environ.get('ALIYUN_LOG_SAMPLE_ACCESSKEY', '')
    logstore = os.environ.get('ALIYUN_LOG_SAMPLE_LOGSTORE', '')
    project = "ali-yunlei-chengdu"
    client = LogClient(endpoint, accessKeyId, accessKey, token)
    # Create an ExternalStore.
    res = client.create_external_store(project,ExternalStoreConfig("rds_store","region","rds-vpc","vpc id","instance id","instance ip","instance port","username","password","database","table"));
    res.log_print()
    # Retrieve the details of the ExternalStore.
    res = client.get_external_store(project,"rds_store");
    res.log_print()
    res = client.list_external_store(project,"");
    res.log_print();
    # Run the JOIN statement.
    req = GetLogsRequest(project,logstore,From,To,"","select count(1) from  "+ logstore +"  s join  meta m on  s.projectid = cast(m.ikey as varchar)");
    res = client.get_logs(req)
    res.log_print();
     # Save the query results to the RDS database.
    req = GetLogsRequest(project,logstore,From,To,""," insert into rds_store select count(1) from  "+ logstore );
    res = client.get_logs(req)
    res.log_print();