关联MySQL数据源进行JOIN查询与数据回写-日志服务-阿里云

本文介绍如何创建外部存储，建立日志服务与MySQL数据库的关联。

前提条件

已采集数据到日志服务。具体操作，请参见数据采集。
已经安装日志服务CLI并且配置服务入口和访问密钥。
已将数据存储到阿里云RDS MySQL数据库、阿里云ADB MySQL数据库或阿里云ECS上自建的MySQL数据库。
MySQL数据库在阿里云VPC内网域内，RDS实例、ADB实例或ECS实例必须与日志服务Project处于同一地域。
重要
不支持直接使用公网地址连接MySQL数据库。

背景信息

日志服务外部存储功能支持日志服务与阿里云RDS MySQL数据库、阿里云ADB MySQL数据库以及在阿里云ECS上自建的MySQL数据库关联，您还可以将查询分析结果写入MySQL数据库中，便于进一步处理结果。

操作步骤

1. 设置白名单

RDS MySQL数据库

需添加白名单地址100.104.0.0/16、11.194.0.0/16和11.201.0.0/16。更多信息，请参见设置IP白名单。

ECS上自建的MySQL数据库

需设置安全组规则，允许100.104.0.0/16、11.194.0.0/16和11.201.0.0/16网段访问。具体操作，请参见添加安全组规则。

ADB MySQL数据库

需添加白名单地址100.104.0.0/16、11.194.0.0/16和11.201.0.0/16。更多信息，请参见设置白名单。

2. 创建ExternalStore

登录安装CLI所在的服务器，在命令行执行touch命令，创建配置文件/home/shell/config.json。在config.json中添加如下脚本。根据实际情况您需要替换region、vpc-id、host、port、username、password、db和table的值。

参数说明

示例

{
    "externalStoreName":"sls_join_meta_store",
    "storeType":"rds-vpc",
    "parameter":{
        "region":"cn-qingdao",
        "vpc-id":"vpc-m5eq4irc1pucp*******",
        "host":"rm-bp1******rm76.mysql.rds.aliyuncs.com",
        "port":"3306",
        "username":"user",
        "password":"****",
        "db":"scmc",
        "table":"join_meta"
    }
}

externalStoreName

ExternalStore名称，必须小写。

storeType

数据源类型，固定为rds-vpc。

region

地域。详细说明如下：

如果是RDS MySQL数据库，则配置region为RDS实例所在地域。
如果是ADB MySQL数据库，则配置region为ADB实例所在地域。
如果是专有网络下ECS上自建的MySQL数据库，则配置region为ECS实例所在地域。

重要

RDS实例、ADB实例或ECS实例必须与日志服务Project处于同一地域。

vpc-id

VPC ID。详细说明如下：

如果是专有网络下的RDS PostgreSQL数据库，则配置vpc-id为RDS实例所属专有网络的ID。
如果是专有网络下的ADB PostgreSQL数据库，则配置vpc-id为ADB实例所属专有网络的ID。
如果是专有网络下阿里云Hologres数据库，则配置vpc-id为阿里云Hologres所属专有网络的ID。

host

数据库地址。详细说明如下：

在专有网络下，外表创建后，如果数据库的实例IP发生变化（比如数据库实例发生迁移），则会影响数据库外表的访问（即使配置中采用了内网域名，因为创建外表时，后端会自动基于域名解析IP，并保存到后端配置中，当前暂不支持自动刷新域名对应的IP）。这种情况下，需要对外表进行更新或者重建。

如果是专有网络下的RDS MySQL数据库，则配置host为RDS实例的内网地址（内网域名或内网IP地址）。
如果是专有网络下的ADB MySQL数据库，则配置host为ADB实例的内网地址（内网域名或内网IP地址）。
如果是专有网络下ECS上自建的MySQL数据库，则配置host为ECS的私网IP地址。
如果数据库通过公网可以访问，则配置对应的公网域名或公网IP地址。

port

端口号。详细说明如下：

如果是RDS MySQL数据库，则配置port为RDS实例的端口号。
如果是ADB MySQL数据库，则配置port为ADB实例的端口号。
如果是专有网络下ECS上自建的MySQL数据库，则配置port为ECS上MySQL的服务端口。

username

数据库用户名。

password

数据库密码。

db

数据库名。

table

数据库表名，支持两种格式：

table_name，例如test。
schema_name.table_name，例如public.test。

在命令行执行如下命令，创建ExternalStore。其中project_name为日志服务Project名称，请根据实际情况替换，本文以log-rds-demo为例。
```
aliyunlog log create_external_store --project_name="log-rds-demo" --config="file:///home/shell/config.json" 
```

查询ExternalStore信息。命令执行成功后，无响应消息。您可以执行aliyunlog log get_external_store --project_name="log-rds-demo" --store_name="sls_join_meta_store" --format-output=json查询RDS MySQL外部数据源详细信息。查询信息如下：

{
    "externalStoreName":"sls_join_meta_store",
    "storeType":"rds-vpc",
    "parameter":{
         "db": "scmc",
         "host": "rm-bp1******rm76.mysql.rds.aliyuncs.com",
         "port": "3306",
         "region": "cn-wulanchabu",
         "table": "test",
         "username": "user",
         "vpc-id": "vpc-m5eq4irc1pucp*******"
    }
}

3. 执行外部存储

执行查询分析

使用外部存储
关联外部数据源后，您可以在日志服务控制台，名为log-rds-demo的Project下，单击任意Logstore，通过查询分析语句* | select * from sls_join_meta_store查询关联的MySQL表数据。
使用JOIN关联日志服务与MySQL表
- 仅支持Logstore与MySQL数据库小表（数据量小于20 MB）进行联合查询。
- 查询和分析语句中，Logstore必须写在join关键字前面，ExternalStore写在join关键字后面。
- 查询和分析语句中，必须写ExternalStore名称，系统自动替换成MySQL数据库名+表名。请勿直接填写MySQL表名。
支持的Join语法有INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN。
```
[ INNER ] JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
FULL [ OUTER ] JOIN
```
JOIN语法样例如下所示。
```
method:postlogstorelogs | select count(1) , histogram(logstore) from log  l join sls_join_meta_store m on l.projectid = cast( m.ikey as varchar)
```

保存查询分析结果到MySQL数据库

MySQL数据库表的字段是varchar，需要将日志库的字段转换为MySQL表的字段。例如MySQL表的字段detail为varchar(60)，在Logstore中将索引content用cast函数转换：cast(content as varchar(60))。cast函数请参见类型转换函数。

日志服务支持通过Insert语法将查询结果插入到MySQL数据库中。Insert语法样例如下所示：

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

SDK操作

Python程序样例

# 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, '')
    #创建ExternalStore。
    res = client.create_external_store(project,
                                       ExternalStoreConfig(externalStoreName="rds_store", region="cn-chengdu",
                                                           storeType="rds-vpc",
                                                           vpcId="vpc-2vctx8reuqswmk********",
                                                           host='rm-2vc6x67972iv********.mysql.cn-chengdu.rds.aliyuncs.com',
                                                           port="3306", username="root", password="123456",
                                                           database="test_database", table="test"))
    res.log_print()
    #获取ExternalStore详情。
    res = client.get_external_store(project,"rds_store")
    res.log_print()
    res = client.list_external_store(project,"")
    res.log_print()
    # JOIN查询。
    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()
     # 将查询和分析结果写入MySQL数据库。
    req = GetLogsRequest(project,logstore,From,To,"","* |  insert into rds_store select count(1) from  "+ logstore )
    res = client.get_logs(req)
    res.log_print()

关联MySQL数据源