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

2021-09-04 01:51:44

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

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

背景

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

原始日志数据样例

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

而具体共享单车信息在RDS MySQL中保存着，具体数据形式如下:

以上字段信息具体如下：
bikeid表示自行车编号id
brand表示该自行车品牌
batch表示投放市场批次。1表示2019年1月份投放市场批次，2表示2019年6月份投放市场批次

加工需求

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

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

加工流程

基本配置

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

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

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

进入设置白名单设置页面：

加工配置

数据加工前端控制台，高级参数配置需要输入云数据库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

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

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

以上RDS的vpc_id、instance_id和instance_port具体可以在RDS控制台中找到，具体如下图所示：

端口信息：

具体高级参数配置示例图：

加工操作

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

# 使用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_local，e_table_map和res_rds_mysql
第一次点击的预览界面，需要AccessKeyId，AccessKey相关信息请参考访问秘钥配置和子账号授权。
点击预览可以查看加工结果：

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

数据加工诊断

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

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

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

注意：在使用SQL分析的时候，需要将对应的字段建立好索引。在此案例中我们需要提前建立好orderid、bikeid、userid、location_x、location_y、action、temp、atemp、humidity、windspeed这些字段索引，具体如下图所示：

从以上数据中我们能发现什么，分析什么，统计什么，得出什么样的结论都是需要提前想好的。在这里，我们主要从以下几个方面做分析：
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的情况下，统计各个温度的骑车人数

通过上图可以得出在温度大概在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的情况下，统计品牌的总骑车人数

通过上图分析可以得知，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的情况下，统计各个批次的总骑车人数

通过上图分析可以得知，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

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

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

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

周六用车数量达到最大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点之间，用户使用单车出行的起始位置

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

下班或者下课因素

*| 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

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

结论

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

码农公寓