基于日志服务数据加工与RDS MySQL做数据富化以及数据分析

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
简介: 准备基于sls日志服务对共享单车租赁信息进行加工分析。sls日志服务上记录2019年8月上海地区某共享单车的数据,已脱敏处理,供研究之用。因RDS数据库里保存的是每辆自行车的编号、品牌以及投放批次。因此需要使用日志服务数据加工将单车实时动态记录日志与保存在RDS上的静态数据做富化和数据分析处理。

本文要点(res_local()、res_rds_mysql()和e_table_map()专题):

  • 如何使用阿里云日志服务数据加工与RDS MySQL做数据富化
  • 如何配置数据加工使用RDS内网地址访问数据库
  • 如何使用富化后的数据做数据分析

背景

业务背景: 准备基于sls日志服务对共享单车租赁信息进行加工分析。sls日志服务上记录2019年8月上海地区某共享单车的数据,已脱敏处理,供研究之用。因RDS数据库里保存的是每辆自行车的编号、品牌以及投放批次。因此需要使用日志服务数据加工将单车实时动态记录日志与保存在RDS上的静态数据做富化和数据分析处理。
业务需求: 需要使用数据加工VPC反向代理,以便能够使用RDS内网访问数据库。因为RDS外网连接会降低实例的安全性,并且链接不稳定。因此需要使用内网访问的方式来保证传输速率和安全性

原始日志数据样例

undefined
以上logstore字段具体信息如下:
orderid表示订单号
bikeid表示自行车编号id
userid表示骑车用户的id
location_x表示骑车用户的地理位置经度
location_y表示骑车用户的地理位置维度
action表示该用户骑车行为,分为骑车开始时间和骑车结束时间
temp表示该用户骑车时温度大小
atemp表示该用户骑车时体感温度
humidity表示该用户骑车时的相对湿度
windspeed表示该用户骑车时风速大小

而具体共享单车信息在RDS MySQL中保存着,具体数据形式如下:
undefined
以上字段信息具体如下:
bikeid表示自行车编号id
brand表示该自行车品牌
batch表示投放市场批次。1表示2019年1月份投放市场批次,2表示2019年6月份投放市场批次

加工需求

  1. 需要根据logstore中的bikeid与RDS数据库中的bikeid做join富化。
  2. 然后根据处理后的日志做数据分析,分析共享单车最优调度问题。

数据加工使用RDS内网地址访问数据库做数据富化处理

加工流程

undefined

基本配置

注:只有全部符合以下条件才能正常使用数据加工使用RDS内网访问数据库功能。

  1. 该功能只允许准备链接的RDS实例和SLS的Project在同一区域region内,才能访问成功,否则会访问不通。
  2. 在使用RDS内网地址访问数据库功能时候,需要设定IP段白名单,请将100.104.0.0/16该IP段设置到白名单中(该IP段也包含登录数据DMS系统的IP段),具体设置步骤如下:

进入RDS实例详情页面,在右上角有设置白名单选项按钮,具体如下:
undefined
进入设置白名单设置页面:
undefined

加工配置

数据加工前端控制台,高级参数配置需要输入云数据库RDS的vpc_id,instance_id和实例链接端口instance_port信息,以下是参数配置:

config.vpc.vpc_id.{name}
config.vpc.instance_id.{name}
config.vpc.instance_port.{name}
# 例子:
config.vpc.vpc_id.test1: vpc-uf6mskb0b****n9yj
config.vpc.instance_id.test1: rm-uf6e61k****ahd7
config.vpc.instance_port.test1: 3306

使用该功能需要了解以下三个相关信息基本概念:

  1. vpc_id表示的是准备使用内网访问数据库的RDS实例所属于的网络类型ID,专有网络相关文档请参考什么是专有网络专有网络控制台
  2. instance_id表示准备使用内网访问数据库的RDS实例ID,相关RDS介绍请参考什么是云数据库RDS
  3. 实例链接端口instance_port表示的是准备使用内网访问数据库的RDS链接地址端口,详细请参考内网地址和外网地址介绍查看内外网地址端口

以上RDS的vpc_id、instance_id和instance_port具体可以在RDS控制台中找到,具体如下图所示:
undefined
端口信息:
undefined
具体高级参数配置示例图:
undefined

加工操作

经过上述步骤之后,此时我们可以真正的对数据做富化加工处理。以下是具体的加工语法:

# 使用RDS内网地址访问数据库,并且富化数据
e_table_map(res_rds_mysql(str_format("{}:{}",res_local("config.vpc.instance_id.test1"),res_local("config.vpc.instance_port.test1")), "your rds username", "your rds password", "you database",table="your table",primary_keys="bikeid"), "bikeid",["brand","batch"])

以上语法具体详见,res_locale_table_mapres_rds_mysql
第一次点击的预览界面,需要AccessKeyId,AccessKey相关信息请参考访问秘钥配置子账号授权
点击预览可以查看加工结果:

如上图所示,目前的数据基本上都是正常数据。接下来是对加工的数据进行加工配置,以下图中的右侧图示表示将加工的数据分发到一个的lshare_bike_target的logstore中。
undefined

数据加工诊断

undefined
点击上图中提示按钮,可进入数据加工任务详情页面,在这里可以看加工出错日志、加工消费记录等重要信息。具体如下图:
undefined

undefined
undefined
从上图中可以看到具体加工仪表大盘,还有加工出错信息等。以上图中报错信息是因为未在rds实例中添加白名单导致的。
保存数据加工之后,可能在自己目标的logstore中不能及时看到加工后的数据,是因为使用数据加工会有一个延时速率,具体信息参考数据加工仪表盘创建告警参考状态监控与告警

对富化后的数据进行数据分析

注意:在使用SQL分析的时候,需要将对应的字段建立好索引。在此案例中我们需要提前建立好orderid、bikeid、userid、location_x、location_y、action、temp、atemp、humidity、windspeed这些字段索引,具体如下图所示:
undefined
从以上数据中我们能发现什么,分析什么,统计什么,得出什么样的结论都是需要提前想好的。在这里,我们主要从以下几个方面做分析:
1、分析温度、体感温度、相对湿度、风速对共享单车租赁影响(外界环境气候因素)
2、自行车品牌对共享单车租赁影响(供应商品牌因素)
3、自行车投放市场批次对共享单车租赁影响(自行车质量因素)
4、统计每小时用车人数
5、统计一周内每星期的用车人数
6、上班或者上课因素(轨迹分析)
7、下班或者下课因素(轨迹分析)

外界环境因素

以上以分析温度对共享单车租赁影响为例:

* |SELECT COUNT(orderid) as count, temp, action WHERE action LIKE 'start_time' group by temp,action ORDER BY temp DESC

上述SQL语句表示的是当action是start_time的情况下,统计各个温度的骑车人数
undefined
通过上图可以得出在温度大概在10~18、20~29区间的时候骑车人数最多。所以当天气温度大概是这个范围的时候,可以适当增加单车数量。

供应商品牌因素

* |SELECT COUNT(orderid) as count, brand, action WHERE action LIKE 'start_time' group by brand,action ORDER BY brand DESC

上述SQL语句表示的是当action是start_time的情况下,统计品牌的总骑车人数
undefined
通过上图分析可以得知,Forever品牌自行车更受用户喜爱,可以在此后的单车供应的时候多考虑Forever品牌自行车。这样或许可以提高用户粘度。

自行车质量因素

* |SELECT COUNT(orderid) as count, batch, action WHERE action LIKE 'start_time' group by batch,action ORDER BY batch DESC

上述SQL语句表示的是当action是start_time的情况下,统计各个批次的总骑车人数
undefined
通过上图分析可以得知,6月份投放市场的单车更受欢迎,可能因为1月份投放的单车可能质量方面有所下滑导致用户不愿意使用老旧的单车,从上图中可以大概单车淘汰率基本上单车存活周期是八个月左右,需要更换单车批次。

统计每小时用车数量

*| select date_format (__time__, '%H') as Hour, COUNT(orderid) as count,action WHERE action LIKE 'start_time' group by Hour,action ORDER By Hour ASC

undefined
从上图可分析出上午6点到10点这个时间段是用车需求高峰,8点是最高峰,因此需要在制定好此事件段内的调度优先策略规划,才能更好的服务用户

统计一周各个星期的用车数量

*| select date_format (__time__, '%W') as Week, COUNT(orderid) as count,action WHERE action LIKE 'start_time' group by Week,action

undefined
周六用车数量达到最大20102,其次是周四18592,周五18386次。因此可以重点关注这三天的单车调度问题。

上班或者上课因素

*| select __time__,location_x,location_y,action,orderid,cast(date_format(__time__, '%H') as bigint) as Hour WHERE action LIKE 'start_time' and cast(date_format(__time__, '%H') as bigint)<10 and cast(date_format(__time__, '%H') as bigint)>5 group by action,__time__,location_x,location_y,orderid,Hour

上述SQL语句表示统计早上5点到10点之间,用户使用单车出行的起始位置
undefined
通过以上图可知,早上骑车出行初始位置一般在学校,住宅小区等地方,说明早上可能有些学生上课或者工作者上班会选择骑车出行方式,并且该时间点单车需求量较大,可以优先考虑在将旁边其他地区的单车调度到住宅、学校等地方。以保证单车供给平衡

下班或者下课因素

*| select __time__,location_x,location_y,action,orderid,cast(date_format(__time__, '%H') as bigint) as Hour WHERE action LIKE 'start_time' and cast(date_format(__time__, '%H') as bigint)<23 and cast(date_format(__time__, '%H') as bigint)>17 group by action,__time__,location_x,location_y,orderid,Hour

undefined
从上图仔细分析可以看出,在18点-20点这个范围内,大部分都是从工作场所(大厦、研究所等)、学校等地方出发,此时间段是下班高峰期,可以优先将其他地区单车调度到这些地区。以缓解高峰期带来的单车数量少,而用户使用单车需求激增,带来的体验不友好情况。
而在21点-22点左右,用户使用单车大多数在公园、初高中学校附近使用单车。但是总体数量比在18点到20点之间使用单车少的多。因此可以大概猜测下21点-22点这个时间段人们喜欢去一些休闲的地方散散步。

结论

经过以上实战分析,可以使用日志服务+数据加工+SQL分析做很多有实际意义的事情,比如使用日志服务数据加工做数据异常数量,将处理好的数据在进行SQL和可视化分析并制定相应的商业策略,和发现定位当前策略的不足。

相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
目录
相关文章
|
21天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
130 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
21天前
|
缓存 NoSQL 关系型数据库
Redis和Mysql如何保证数据⼀致?
在项目中,为了解决Redis与Mysql的数据一致性问题,我们采用了多种策略:对于低一致性要求的数据,不做特别处理;时效性数据通过设置缓存过期时间来减少不一致风险;高一致性但时效性要求不高的数据,利用MQ异步同步确保最终一致性;而对一致性和时效性都有高要求的数据,则采用分布式事务(如Seata TCC模式)来保障。
54 14
|
24天前
|
SQL 前端开发 关系型数据库
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
49 9
|
1月前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
46 1
|
1月前
|
SQL 关系型数据库 MySQL
mysql数据误删后的数据回滚
【11月更文挑战第1天】本文介绍了四种恢复误删数据的方法:1. 使用事务回滚,通过 `pymysql` 库在 Python 中实现;2. 使用备份恢复,通过 `mysqldump` 命令备份和恢复数据;3. 使用二进制日志恢复,通过 `mysqlbinlog` 工具恢复特定位置的事件;4. 使用延迟复制从副本恢复,通过停止和重启从库复制来恢复数据。每种方法都有详细的步骤和示例代码。
186 2
|
21天前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的联机重做日志文件与数据写入过程
在Oracle数据库中,联机重做日志文件记录了数据库的变化,用于实例恢复。每个数据库有多组联机重做日志,每组建议至少有两个成员。通过SQL语句可查看日志文件信息。视频讲解和示意图进一步解释了这一过程。
|
26天前
|
XML 安全 Java
【日志框架整合】Slf4j、Log4j、Log4j2、Logback配置模板
本文介绍了Java日志框架的基本概念和使用方法,重点讨论了SLF4J、Log4j、Logback和Log4j2之间的关系及其性能对比。SLF4J作为一个日志抽象层,允许开发者使用统一的日志接口,而Log4j、Logback和Log4j2则是具体的日志实现框架。Log4j2在性能上优于Logback,推荐在新项目中使用。文章还详细说明了如何在Spring Boot项目中配置Log4j2和Logback,以及如何使用Lombok简化日志记录。最后,提供了一些日志配置的最佳实践,包括滚动日志、统一日志格式和提高日志性能的方法。
180 30
【日志框架整合】Slf4j、Log4j、Log4j2、Logback配置模板
|
2月前
|
XML JSON Java
Logback 与 log4j2 性能对比:谁才是日志框架的性能王者?
【10月更文挑战第5天】在Java开发中,日志框架是不可或缺的工具,它们帮助我们记录系统运行时的信息、警告和错误,对于开发人员来说至关重要。在众多日志框架中,Logback和log4j2以其卓越的性能和丰富的功能脱颖而出,成为开发者们的首选。本文将深入探讨Logback与log4j2在性能方面的对比,通过详细的分析和实例,帮助大家理解两者之间的性能差异,以便在实际项目中做出更明智的选择。
257 3
|
2月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1659 14
|
2月前
|
Python
log日志学习
【10月更文挑战第9天】 python处理log打印模块log的使用和介绍
35 0

相关产品

  • 日志服务