All Products
Search
Document Center

Logstore and RDS join query

Last Updated: Sep 29, 2018

Log Service supports Logstore and RDS databases joint queries, and the query results are saved in RDS.

Step 1. Create an RDS VPC and set a whitelist

  1. Create an RDS and specify the VPC environment. Then, get VPC ID and RDS instance ID.

  2. Set an RDS whitelist: 100.104.0.0/16. For more information, see Set a whitelist

Step 2. Create an external store

You can create an external store based on the following example, but you must replace the example parameters with your actual parameter values.

  1. {
  2. "externalStoreName":"storeName",
  3. "storeType":"rds-vpc",
  4. "parameter":
  5. {
  6. "region":"cn-qingdao",
  7. "vpc-id":"vpc-m5eq4irc1pucp*******"
  8. "instance-id":"i-m5eeo2whsn*******"
  9. "host":"localhost",
  10. "port":"3306",
  11. "username":"root",
  12. "password":"****",
  13. "db":"scmc"
  14. "table":"join_meta"
  15. }
  16. }

Parameters description is as following.

Parameter Description
region The region where your service is located.
vpc-id The ID of the VPC.
instance-id The ID of the RDS instance.
host The ID of the ECS instance.
port The ECS instance port.
username The name of the user.
password The password.
db The database.
table The data table.

Note:
Currently, function is supported in Beijing (cn-beijing), Qingdao (cn-qingdao), and Hangzhou (cn-hangzhou) regions.

Step 3. Join query

In the Log Service console, go to the Query page, and run the Join statement.

Join syntax format is as follows:

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

Precautions

  • Join only supports Logstore Join for small tables.
  • In Join, Logstore must be written in the front of external store.
  • The name of the external store must be written in Join, it can be automatically replaced with the RDS database and table name. You cannot directly write the RDS table name.

Join syntax

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  1. [ INNER ] JOIN
  2. LEFT [ OUTER ] JOIN
  3. RIGHT [ OUTER ] JOIN
  4. FULL [ OUTER ] JOIN

Step 4. Save the query results in RDS

Use Insert syntax to save query results in RDS, for example:

  1. method:postlogstorelogs | insert into method_output select cast(method as varchar(65535)) , count(1) from log group by method

Python example

  1. # encoding: utf-8
  2. from __future__ import print_function
  3. from aliyun.log import *
  4. from aliyun.log.util import base64_encodestring
  5. from random import randint
  6. import time
  7. import os
  8. from datetime import datetime
  9. endpoint = os.environ.get('ALIYUN_LOG_SAMPLE_ENDPOINT', 'cn-chengdu.log.aliyuncs.com')
  10. accessKeyId = os.environ.get('ALIYUN_LOG_SAMPLE_ACCESSID', '')
  11. accessKey = os.environ.get('ALIYUN_LOG_SAMPLE_ACCESSKEY', '')
  12. logstore = os.environ.get('ALIYUN_LOG_SAMPLE_LOGSTORE', '')
  13. project = "ali-yunlei-chengdu"
  14. client = LogClient(endpoint, accessKeyId, accessKey, token)
  15. #Create external store
  16. res = client.create_external_store(project,ExternalStoreConfig("rds_store","region","rds-vpc","vpc id","instance id","instance ip","instance port","username","paassword","database","data table"));
  17. res.log_print()
  18. #Get external store details
  19. res = client.get_external_store(project,"rds_store");
  20. res.log_print()
  21. res = client.list_external_store(project,"");
  22. res.log_print();
  23. # JOIN query
  24. req = GetLogStoreLogsRequest(project,logstore,From,To,"","select count(1) from "+ logstore +" s join meta m on s.projectid = cast(m.ikey as varchar)");
  25. res = client.get_logs(req)
  26. res.log_print();
  27. # Query results insert into RDS
  28. req = GetLogStoreLogsRequest(project,logstore,From,To,""," insert into rds_store select count(1) from "+ logstore );
  29. res = client.get_logs(req)
  30. res.log_print();