This topic describes how to join Logstores created in Log Service to ApsaraDB for RDS instances for queries and store the query results in the ApsaraDB for RDS instances.

Procedure

  1. Create an ApsaraDB for RDS instance in a VPC. Configure a whitelist for the instance.
    Create an ApsaraDB for RDS instance. After the ApsaraDB for RDS instance and VPC are created, you can obtain the VPC ID and the ApsaraDB for RDS instance ID. For more information, see Create an ApsaraDB RDS for MySQL instance.
  2. Configure a whitelist.
    Configure a whitelist for the ApsaraDB for RDS instance: 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 RDS MySQL instance.
  3. Create an external table.
    The following statement provides an example on how to create an external table. Replace the parameter values as needed:
    {
    "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"
       }
    }
    Table 1. Parameters
    Parameter Description
    region The region where your service is located.
    vpc-id The ID of the VPC.
    instance-id The ID of the ApsaraDB for RDS instance.
    host The ID of the ECS instance.
    port The port for the ECS instance.
    username The username used to log on to the instance.
    password The password used to log on to the instance.
    db The name of the database.
    table The name of the table.
    Note You can only join a Logstore to an ApsaraDB for RDS instance that resides in the China (Beijing), China (Qingdao), or China (Hangzhou) region.
  4. Perform the JOIN operation.
    Log on to the Log Service console and run a JOIN clause on the Search & Analysis page.
    Supported JOIN syntax:
    • INNER JOIN
    • LEFT JOIN
    • RIGHT JOIN
    • FULL JOIN
    [ INNER ] JOIN
    LEFT [ OUTER ] JOIN
    RIGHT [ OUTER ] JOIN
    FULL [ OUTER ] JOIN
    Note
    • You can join Logstores only to external tables.
    • In the JOIN clause, you must specify a Logstore before specifying an external table.
    • You must specify the name of the external table instead of the name of the table in the ApsaraDB for RDS instance. The name of the external table is automatically replaced with the combination of the ApsaraDB for RDS database name and the name of the table in the database that you want to join to the Logstore.
    Examples of JOIN clauses
    method:postlogstorelogs | select count(1) , histogram(logstore) from log  l join join_meta m on l.projectid = cast( m.ikey as varchar)
  5. Save the query results to the external table.
    You can use the INSERT statement to insert the query results into the external table.
    method:postlogstorelogs | insert into method_output  select cast(methodasvarchar(65535)),count(1)fromloggroupbymethod

Example

Examples
# 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 table.
    res = client.create_external_store(project,ExternalStoreConfig("rds_store","region","rds-vpc","vpc id","instance id","instance IP address","port for the instance","username","password","database","table"));
    res.log_print()
    # Obtain external table details.
    res = client.get_external_store(project,"rds_store");
    res.log_print()
    res = client.list_external_store(project,"");
    res.log_print();
    # Perform the JOIN operation.
    req = GetLogStoreLogsRequest(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();
     # Write the query results to the external table.
    req = GetLogStoreLogsRequest(project,logstore,From,To,""," insert into rds_store select count(1) from  "+ logstore );
    res = client.get_logs(req)
    res.log_print();