关联外部数据

最近更新时间:2024-04-03 09:02:21

我的收藏

操作场景

对日志进行统计分析时,有时需要将日志与外部数据关联起来才能进行完整的业务统计,例如以下使用场景:
日志中仅保存了用户 ID,统计分析时需根据用户 ID 在用户信息数据库中查询用户的等级、地域和类型等信息,例如按用户性别分别统计用户访问次数。
日志中仅保存了业务系统 ID,根据 ID 很难快速对应到具体的业务名称,需要根据 ID 与名称的映射关系将业务系统 ID 翻译为业务名称。
在仪表盘中除了查看日志数据,还需要查看其它存储在数据库中的数据。
日志服务支持将 MySQL 及对象存储中的 CSV 文件作为数据库表关联至日志主题,然后使用 SQL 语句将两部分数据进行关联分析。
说明:
当前功能处于内测中,如需使用请单击 立即申请

前提条件

已开通日志服务并 创建日志主题,日志主题需为标准存储,低频存储不支持使用 SQL 进行统计分析。

注意事项

关联 MySQL 时,请注意网络/数据安全及 MySQL 数据库性能:
网络安全:建议使用内网地址访问 MySQL,由于日志服务无法直接访问您 VPC 内的资源,需由您在 VPC 内创建私有连接终端节点服务,日志服务通过该私有连接访问 MySQL,详见 操作步骤
数据安全:建议创建专门的数据库账号供 CLS 访问 MySQL,尽可能的缩小账号对应的资源及操作权限,CLS 仅需查询权限,无需编辑及删除权限。请妥善保管账号信息,不要泄露。
性能:如果 MySQL 内数据量较大,建议不要直接将生产环境主数据库关联至 CLS,避免通过 CLS 对数据库发起过于复杂的 SQL 查询,影响生产环境稳定性,建议使用 只读实例
关联对象存储 CSV 文件时,建议使用与日志主题相同地域的存储桶,跨地域访问存储桶内的文件将会在 COS 侧产生外网下行流量及相应费用。对象存储访问权限建议使用“私有读写”,避免数据被第三方访问。

操作步骤

说明:
仅需通过内网地址访问 MySQL 时需执行该步骤,其它访问方式及数据源类型可跳过该步骤。
私有连接(Private Link)提供腾讯云 VPC 通过内网访问同地域其他 VPC 的能力, 可以在同账号或者不同账户的跨 VPC 之间快速建立访问连接,相比公网服务,可以节约公网带宽,安全性更高。 

1. 登录 私有网络控制台,在左侧导航栏单击私有连接 > 终端节点服务,单击新建
2. 在弹出的新建终端节点服务对话框中,配置相关参数。

参数名称
描述
服务名称
自定义终端节点服务的名称,不能超过80个字符,只能使用中文、英文、数字、下划线、分隔符"-"、小数点。
所在地域
终端节点服务所在地域。
所属网络
选择需关联的 MySQL 所属的 VPC。
服务类型
终端节点服务的类型。
关联云数据库 MySQL 时,请选择 “MySQL”
关联部署在 CVM 上的 MySQL 时,请选择 “负载均衡”(终端节点服务不支持直接绑定CVM,需通过应用型内网4层 CLB 转发)。
后端实例
后端服务的实例 ID。
自动接受
注意:
请选择“是”,默认接受连接请求。
3. 单击确定,创建成功的终端节点服务展示在列表中。


4. 添加日志服务 CLS 平台账号至终点节点服务白名单。
4.1 单击上一步创建成功的终端节点服务名称,进入详情页面,再进入白名单页签,单击添加。


4.2 在弹出的添加白名单用户对话框中,输入白名单用户 UIN 及描述信息,UIN 为 438167613,该 UIN 为日志服务 CLS 平台账号。



4.3 单击确定完成白名单添加。

添加外部数据

2. 在左侧导航栏中,单击日志主题,进入日志主题列表页面。
3. 单击需要关联外部数据的日志主题 ID/名称,进入日志主题管理页面。
4. 选择关联外部数据页签,单击添加外部数据,在弹出对话框中配置相关参数。
MySQL
对象存储 COS(CSV 格式文件)
添加 MySQL 时配置如下参数:
参数名称
描述
名称
CLS SQL 中的表名,支持小写字母、数字和_,且不能以_开头和结尾,长度为3 - 60字符,在所属地域内名称不能重复。
备注
可选,不超过255个字符
数据源类型
MySQL
访问方式
内网地址 或 外网地址
注意:
使用内网地址访问时,请在安全组中添加入站规则,允许通过私有连接访问MySQL端口,来源为 11.163.0.0/16,协议端口根据实际情况填写,例如 TCP:3306。
使用外网地址访问时,请在 MySQL 实例绑定的安全组中允许所有来源访问 MySQL 端口,该方式安全性不足,请尽量不要在生产环境使用外网地址。
私有连接终端节点服务 ID
使用内网地址时需填写,即 创建私有连接终端服务节点 步骤中创建的资源 ID,例如 vpcsvc-xxxxxx
访问地址
使用外网地址时需填写,例如 gz-cdb-xxxxx.sql.tencentcdb.com
端口
数据库端口,例如3306
账户名
访问 MySQL 使用的账户名
密码
访问 MySQL 使用的密码
数据库名
需要关联的 MySQL 数据库名称
表名
需要关联的 MySQL 数据库中的表名称
使用范围
仅当前日志主题可访问:仅当前日志主题可通过 SQL 访问该 MySQL 的数据
当前日志集内日志主题可访问:当前日志主题所属日志集内的日志主题都可以通过 SQL 访问该 MySQL 的数据
单机验证访问配置,验证上述配置是否正确。验证通过后单击确定完成添加。
添加对象存储 COS(CSV 格式文件)时配置如下参数:
参数名称
描述
名称
CLS SQL中的表名,支持小写字母、数字和_,且不能以_开头和结尾,长度为3至60字符。
备注
可选,不超过255个字符
数据源类型
对象存储 COS(CSV 格式文件)
存储桶地域
选择 COS 文件所属地域
COS 存储桶
选择 COS 文件所属存储桶
文件名称
填写 COS 文件名称
压缩模式
暂时仅支持不压缩
单击预览数据,验证上述配置是否正确,同时从CSV文件中获取前5行数据(含表头)作为样例,进一步配置如下参数:
参数名称
描述
字段类型
支持 text、long 及 double,请根据实际数据类型选择
访问范围
仅当前日志主题可访问:仅当前日志主题可通过 SQL 访问该 MySQL 的数据
当前日志集内日志主题可访问:当前日志主题所属日志集内的日志主题都可以通过 SQL 访问该 MySQL 的数据
单击确定完成添加。

使用 SQL 关联查询外部数据及日志数据

从 SQL 角度来看,日志主题内的日志数据及外部数据均为数据库表,日志数据对应的表名为 log ,外部数据对应的表名为添加外部数据时设置的名称。因此可分别使用如下 SQL 单独查询日志数据及外部数据:
日志数据:* | select * from log,其中from log可以省略,即* | select *,同时查询日志数据及外部数据时,建议您不要省略,以提高 SQL 可读性。
外部数据:假设外部数据名称为userinfo,则对应 SQL 为* | select * from userinfo
同时查询日志数据及外部数据需使用 JOINUNION 等语法,例如:
* | select * from log left join userinfo on log.user_id=userinfo.id
注意:
查询外部数据时,| 前的检索条件及本次查询指定的时间范围对外部数据不生效,仅对当前日志主题的日志数据生效。

使用案例

某日志主题为请求日志,包含如下字段:
"status_code": "404"
"local_time": "2023-06-05 19:59:01"
"refer": "_",
"user_id": "15"
"ip": "66.131.53.125"
"url": "\\"GET /class/111.html HTTP/1.1\\""
统计分析该日志时,期望能够按照访问用户性别分别统计访问次数,但访问日志中并没有用户性别字段,无法直接统计,需要在用户信息数据库(MySQL)中根据用户 ID(user_id)查询。
用户信息数据库表如下:
id
Name
Gender
Age
Email
Phone
Address
1
John Doe
Male
32
johndoe@example.com
1234567890
123 Main St
2
Jane Smith
Female
28
janesmith@example.com
9876543210
456 Elm St
3
Michael Johnson
Male
45
michaeljohnson@example.com
5551234567
789 Oak St
4
Sarah Davis
Female
38
sarahdavis@example.com
7894561230
321 Pine St
5
David Wilson
Male
51
davidwilson@example.com
1237894560
654 Maple St
6
Emily Anderson
Female
29
emilyanderson@example.com
4567890123
987 Cherry St
7
Matthew Thompson
Male
37
matthewthompson@example.com
7890123456
321 Plum St
8
Olivia Martinez
Female
26
oliviamartinez@example.com
2345678901
654 Orange St
9
Alexander Taylor
Male
42
alexandertaylor@example.com
9012345678
987 Grape St
10
Emma Clark
Female
31
emmaclark@example.com
3456789012
123 Lemon St
将该数据库表关联至日志主题后,即可使用如下语句将日志中的user_id转换为用户性别Gender
* | select ip,url,user_id,Name,Gender from log left join userinfo on log.user_id=userinfo.id
查询结果如下图,其中 ip、url、user_id 来源于日志,Name、Gender 则来源于用户信息库表。

在此基础上,进一步编写 SQL,即可按照访问用户性别分别统计访问次数:
* | select count(*) as pv,Gender from (select ip,url,user_id,Name,Gender from log left join userinfo on log.user_id=userinfo.id) group by Gender
查询结果如下图,其中 Gender 列中的 null 表示部分日志没有查询到用户性别,可能是日志中无 user_id,也可能为 user_id 在用户信息库中无记录。


规格限制

关联外部数据库的规格与限制,请参见 规格与限制

费用说明

关联对象存储 CSV 文件时,如果对象存储桶与日志主题不在同一个地域,每次查询该数据都会在 COS 侧产生外网下行流量及相应费用,CLS 侧无费用。
关联 MySQL 时,如果使用外网连接,每次查询该数据都会在对应的资源侧(例如云数据库 MySQL 或 CVM)产生外网访问流量,可能产生相应费用(取决于您购买的实例配置),CLS 侧无费用。
关联 MySQL 时,如果使用内网连接,由于该内网连接实际使用的是私有连接,存在一定成本,日志服务侧将会产生内网读流量费用,内测期间不收取该费用。