Log Service allows you to use the JOIN syntax to query data from a Logstore and a MySQL database. The query results are saved to the database.

Prerequisites

An external store is created. For more information, see Associate a Log Service Logstore with a MySQL database.

Procedure

  1. Log on to the Log Service console.
  2. In the Projects section, click the name of the project that you want to view.
  3. Choose Log Storage > Logstores. On the Logstores tab, click the Logstore that you want to view.
  4. Execute a query statement.
    Log Service supports the following JOIN syntax:
    [ INNER ] JOIN
    LEFT [ OUTER ] JOIN
    RIGHT [ OUTER ] JOIN
    FULL [ OUTER ] JOIN
    The following sample code provides an example of a join query. For more information, see Perform an association query and analysis on the data of a Logstore and a MySQL database.
    method:postlogstorelogs | select count(1) , histogram(logstore) from log  l join join_meta m on l.projectid = cast( m.ikey as varchar)
    Notice
    • You can use the JOIN syntax only on a Logstore and a small table in a MySQL database. A small table contains less than 20 MB of data.
    • In a query statement, the name of the Logstore must precede the join keyword, and the name of the external store must follow the join keyword.
    • You must specify the name of the external store in a query statement. When the system executes the statement, the system replaces the name with a combination of the database name and the table name. Do not enter only the table name.
  5. Save the query results to the MySQL database.
    Log Service allows you to insert the query results into the database by using an INSERT statement. The following sample code provides an example of an INSERT statement:
    method:postlogstorelogs | insert into method_output  select cast(method as varchar(65535)),count(1) from log group by method

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 external store. 
    res = client.create_external_store(project,ExternalStoreConfig("rds_store","region","rds-vpc","vpc id","Instance ID","Instance IP address","Instance port","Username","Password","Database name","Table name"));
    res.log_print()
    # Retrieve the details about the external store. 
    res = client.get_external_store(project,"rds_store");
    res.log_print()
    res = client.list_external_store(project,"");
    res.log_print();
    # Execute a join query. 
    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 MySQL database. 
    req = GetLogsRequest(project,logstore,From,To,""," insert into rds_store select count(1) from  "+ logstore );
    res = client.get_logs(req)
    res.log_print();