基于 Scheduled SQL 对 VPC FlowLog 实现细粒度时间窗口分析

本文涉及的产品
对象存储 OSS,20GB 3个月
阿里云盘企业版 CDE,企业版用户数5人 500GB空间
云备份 Cloud Backup,100GB 3个月
简介: 针对VPC FlowLog的五元组和捕获窗口信息,在分析时使用不同时间窗口精度,可能得到不一样的流量特征,本文介绍一种方法将原始采集日志的时间窗口做拆分,之后重新聚合为新的日志做分析,达到更细粒度的分析效果。

背景

阿里云专有网络(VPC)提供流日志功能,支持VPC网络中弹性网卡流量、VPC流量及交换机流量的记录与存储。对流日志分析可以监控访问控制规则、监控网络流量和排查网络故障。

流日志功能捕获的流量信息以日志方式写入SLS(阿里云日志服务)中。每条日志会捕获特定捕获窗口中的特定五元组网络流,捕获窗口大约为10分钟,该段时间内流日志功能先聚合数据,再发布日志。

在 SLS 上可以通过关键词搜索对指定目标地址被拒绝的请求:

image-20210601103155245.png

也可以通过 SLS 的 SQL 进行统计分析,但这里涉及一个捕获窗口的问题,例如下面两条流日志(字段做了简化):

Log#1
start: 2021-05-31 00:00:00 
end: 2021-05-31 00:08:30
bytes: 9000
packets: 18
Log#2
start: 2021-05-31 00:02:30 
end: 2021-05-31 00:03:15
bytes: 5000
packets: 10

采集窗口内产生的 bytes,落到 start 时间点上去或是平均落到整个采集窗口,对于流量分析结果会产生明显的差异:

image-20210601105445552.png

根据不同的业务背景,可以有不同的选择:

一种方法是按采集窗口开始时间计算,方法简单,select from_unixtime(start - start % 60) as dt, sum(bytes) as total_bytes group by dt order by dt asc limit 1000

另一种较为复杂,拆分采集窗口后计算,本文介绍基于 SLS SQL 拆分日志后重新聚合的分析实践。

方案

如下是一条 start 与 end 相差501的日志,表示采集窗口横跨了 502 个秒级时间段(start、end 是左闭右闭区间):

image-20210601120251104.png

利用数据函数 sequence 可以生成一个时间序列到 ta 字段:

image-20210601120434728.png

接着将 ta 序列做 unest 展开,得到 502 条日志:

image-20210601120741412.png

到这里,基本思路就有了。但一定请注意:

  1. packets、bytes 字段是在一个捕获窗口中获得的,所以展开后的每条日志,应该将指标值均分到每个拆分后的时间段。
  2. 窗口数据展开后,意味着日志量会膨胀,可能产生很大的计算压力与存储成本,建议减少聚合指标分组数目。

为了减少日志条数,我们将拆分后的秒级日志再按照10秒级粒度重新聚合,502 条秒级日志变为 51 条十秒级日志:

image-20210601121556501.png

Scheduled SQL 实践

将以上方案常驻执行,就可以实现对于新日志的增量处理,如果将预处理结果保存到 Logstore,我们就可以在新的 Logstore 上做分析,可以做到更低的延迟。

Scheduled SQL 是一项由 SLS 全托管的功能,主要的场景包括:

  • 定时分析数据:根据业务需求设置 SQL 语句或查询分析语句,定时执行数据分析,并将分析结果存储到目标库中。
  • 全局聚合:对全量、细粒度的数据进行聚合存储,汇总为存储大小、精度适合的数据,相当于一定程度的有损压缩数据。

image-20210601122007945.png

执行如下 SQL 预览并确认结果符合预期(如果希望预处理后的数据量更少一些,可以按照分钟粒度做聚合,将 10 替换为 60),SQL 代码:

* | select (t.time - t.time % 10) as __time__, srcaddr, srcport, dstaddr, dstport, action, protocol,
  sum(bytes * 1.0 / ("end"-start + 1)) as bytes, sum(packets * 1.0 / ("end"-start + 1)) as packets
  from (select start, "end", srcaddr, srcport, dstaddr, dstport, action, protocol, bytes, packets,
  sequence(start, "end", 1) as ta from log), unnest(ta) as t(time)
  group by time, srcaddr, srcport, dstaddr, dstport, action, protocol order by __time__ asc limit 1000000

紧接着创建 Scheduled SQL 作业:

image-20210601165542431.png

保存作业,选择”增强型资源池“(收费、但资源可扩展,适用于有 SLA 要求的业务场景),设置存储预处理结果到目标 Logstore aligned_vpc_flowlog。

image-20210601122647089.png

接下来,设置 SQL 作业从 5/28 日的数据开始处理,在存量数据追上进度后,新数据每 5 分钟执行一次,每次查询 5 分钟的数据做处理。

注意延迟执行参数,如果上游 Logstore 的数据到来可能延迟,建议设置大一些的值来保证计算数据的完整性。

image-20210601122946540.png

Scheduled SQL 作业每 5 分钟一次的实例,可以在控制台上查看到。对于 SQL 执行失败(权限、SQL 语法等原因)或者数据迟到导致空跑情况,可以对指定实例做重试运行。

image-20210601123149488.png

效果

在 SLS 上制作一个仪表盘对比两种计算方式的流量特征。

  • 10秒聚合-原始窗口

* | select from_unixtime(start - start % 10) as dt, sum(packets) as packets, round(sum(bytes)/1024.0/1024.0, 3) as MB group by dt order by dt asc limit 10000

  • 10秒聚合-拆分窗口数据

* | select from_unixtime(__time__ - __time__ % 10) as dt, sum(packets) as packetes, round(sum(bytes)/1024.0/1024.0, 3) as MB group by dt order by dt asc limit 10000

通过对比可以看到,拆分窗口后的数据统计更加均匀。

image-20210601131252012.png

更多其它内容,请参考:

相关实践学习
使用ROS创建VPC和VSwitch
本场景主要介绍如何利用阿里云资源编排服务,定义资源编排模板,实现自动化创建阿里云专有网络和交换机。
阿里云专有网络VPC使用教程
专有网络VPC可以帮助您基于阿里云构建出一个隔离的网络环境,并可以自定义IP 地址范围、网段、路由表和网关等;此外,也可以通过专线/VPN/GRE等连接方式实现云上VPC与传统IDC的互联,构建混合云业务。 产品详情:https://www.aliyun.com/product/vpc
目录
相关文章
|
5月前
|
SQL 索引
19. 一个SQL语句执行很慢, 如何分析
该内容介绍了如何分析执行慢的SQL语句。首先启用慢查询日志或使用命令获取慢查询的SQL。然后利用`EXPLAIN`命令分析,关注其中的`select_type`, `type`, 和 `extra`字段。`select_type`涉及子查询和联合查询的类型,`type`表示查询优化器使用的访问类型,性能从上到下递减,`extra`字段提供额外信息,如是否使用索引等。
40 0
|
2月前
|
SQL 存储 Unix
Flink SQL 在快手实践问题之设置 Window Offset 以调整窗口划分如何解决
Flink SQL 在快手实践问题之设置 Window Offset 以调整窗口划分如何解决
38 2
|
5月前
|
SQL 数据可视化 算法
SQL Server聚类数据挖掘信用卡客户可视化分析
SQL Server聚类数据挖掘信用卡客户可视化分析
|
2月前
|
SQL 流计算
Flink SQL 在快手实践问题之CUMULATE窗口的划分逻辑如何解决
Flink SQL 在快手实践问题之CUMULATE窗口的划分逻辑如何解决
45 2
|
2月前
|
SQL 流计算
Flink SQL 在快手实践问题之Window TVF改进窗口聚合功能如何解决
Flink SQL 在快手实践问题之Window TVF改进窗口聚合功能如何解决
18 1
|
2月前
|
SQL 数据采集 数据挖掘
为什么要使用 SQL 函数?详尽分析
【8月更文挑战第31天】
22 0
|
2月前
|
SQL 数据挖掘 BI
【超实用技巧】解锁SQL聚合函数的奥秘:从基础COUNT到高级多表分析,带你轻松玩转数据统计与挖掘的全过程!
【8月更文挑战第31天】SQL聚合函数是进行数据统计分析的强大工具,可轻松计算平均值、求和及查找极值等。本文通过具体示例,展示如何利用这些函数对`sales`表进行统计分析,包括使用`COUNT()`、`SUM()`、`AVG()`、`MIN()`、`MAX()`等函数,并结合`GROUP BY`和`HAVING`子句实现更复杂的数据挖掘需求。通过这些实践,你将学会如何高效地应用SQL聚合函数解决实际问题。
42 0
|
2月前
|
网络协议 NoSQL 网络安全
【Azure 应用服务】由Web App“无法连接数据库”而逐步分析到解析内网地址的办法(SQL和Redis开启private endpoint,只能通过内网访问,无法从公网访问的情况下)
【Azure 应用服务】由Web App“无法连接数据库”而逐步分析到解析内网地址的办法(SQL和Redis开启private endpoint,只能通过内网访问,无法从公网访问的情况下)
|
3月前
|
SQL 存储 大数据
SQL中DISTINCT关键字的使用与性能影响分析
SQL中DISTINCT关键字的使用与性能影响分析
|
4月前
|
SQL 缓存 数据管理
数据管理DMS产品使用合集之打开多个SQL窗口,在关闭浏览器重新登录只剩第一个窗口且部分脚本丢失,是什么导致的
阿里云数据管理DMS提供了全面的数据管理、数据库运维、数据安全、数据迁移与同步等功能,助力企业高效、安全地进行数据库管理和运维工作。以下是DMS产品使用合集的详细介绍。
47 0
下一篇
无影云桌面