All Products
Search
Document Center

Simple Log Service:Join queries on a Logstore and a MySQL database

Last Updated:Aug 02, 2023

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

Procedure

  1. Log on to the 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.

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

    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, 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();