Simple Log Service:Join query and analysis operations on a Logstore and a MySQL database

Procedure

1. Log on to the Simple Log Service console.

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

   

3. In the left-side navigation pane, click Log Storage. In the Logstores list, click the Logstore that you want to manage.

   

4. Execute a query statement.

   Simple 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 operation. For more information, see Associate a Logstore with a MySQL database to perform query and analysis.

   method:postlogstorelogs | select count(1) , histogram(logstore) from log  l join join_meta m on l.projectid = cast( m.ikey as varchar)

   Important

   • You can use the JOIN syntax only on a Logstore and a small table in a MySQL database. A small table can store 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.

   Note

   If the fields in the MySQL database table are of the varchar type, you must convert the fields in the Logstore into the fields of the varchar type. For example, the table contains a detail field whose type is defined as varchar(60), and the Logstore contains a content field, which is indexed. In this case, you must use the cast(content as varchar(60)) function to convert the type of the content field to varchar(60). For more information about the cast function, see cast function.

   Simple 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, '')
    # Create an external store. 
    res = client.create_external_store(project,
                                       ExternalStoreConfig(externalStoreName="rds_store", region="cn-chengdu",
                                                           storeType="rds-vpc",
                                                           vpcId="vpc-2vctx8reuqswmk********",
                                                           instanceId="",
                                                           host='rm-2vc6x67972iv********.mysql.cn-chengdu.rds.aliyuncs.com',
                                                           port="3306", username="root", password="Gs516945070",
                                                           database="gstest", table="test"))
    res.log_print()
    # Retrieve the details of the external store. 
    res = client.get_external_store(project,"rds_store")
    res.log_print()
    res = client.list_external_store(project,"")
    res.log_print()
    # Use the JOIN syntax to query data. 
    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()