实时即未来，大数据项目车联网之车辆常用部分字段明细数据ETL（11）

theme: smartblue

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第11天，点击查看活动详情

1. 车辆常用部分字段明细数据ETL

从原始数据中加载出车辆指标：电量百分比(currentElectricity)、当前电量(remainPower)、百公里油耗(fuelConsumption100km)、发动机速度(engineSpeed)、车辆速度(vehicleSpeed)

除此之外，最终数据落地hbase，与phoenix建立关联视图，通过phoenix查询明细，因此行主键(rowKey)、车架号(vin)、终端时间(terminalTime)、存储时间(processTime)

创建hbase车辆指标即席查询明细表：

create “itcastsrc_vehicle_detail”,“cf”

车辆明细数据ETL实现步骤：

• 在实时ETL主任务（*KafkaSourceDataTask\）中增加逻辑

• 写入etl后的数据，到hbase中，自定义hbase输出

主类实现

//TODO 13）将经常用于分析的字段提取出来保存到一个hbase独立的表中，这个表的字段要远远小于正常数据宽表的字段数量， // 将来与Phoenix整合以后，在可视化页面zepplin中分析查询hbase表数据 VehicleDetailSinkOptimizer vehicleDetailSinkFunction = new VehicleDetailSinkOptimizer(“itcastsrc_vehicle_detail”); srcDataStream.addSink(vehicleDetailSinkFunction);

1.1 自定义sink实现数据写入hbase

自定义sink，继承RichSinkFunction类，重写open、invoke、close方法（选中）

实现RichSinkFunction抽象类，重写父类方法

/
 * 自定义实现车辆明细表数据存储到hbase表中
 * 经常用于分析的字段存储到hbase独立的表中
 */

1.2 phoenix明细数据视图

创建phoenix关联车辆指标即席查询明细视图：

create view if not exists "itcastsrc_vehicle_detail"(
rowKey VARCHAR PRIMARY KEY,
"cf"."vin" VARCHAR,
"cf"."terminalTime" VARCHAR,
"cf"."currentElectricity" VARCHAR,
"cf"."remainPower" VARCHAR,
"cf"."fuelConsumption100km" VARCHAR,
"cf"."engineSpeed" VARCHAR,
"cf"."vehicleSpeed" VARCHAR,
"cf"."processTime" VARCHAR
);	

查询效果

确认hbase原始数据表中的数据总数与车辆明细表数据总数是否一致

n itcast_src表总数

n itcastsrc_vehicle_detail表总数

思考：

车辆明细数据经过实时ETL后，存入到itcastsrc_vehicle_detail\ 新表中，下一步根据计算指标，进行sql查询

2. 车辆明细数据统计

2.1 车辆总数统计

统计车辆明细总数(某天某时刻明细数据)

• 每辆车每天采集多条数据，因此需要对车辆进行去重，采集过数据的车辆只计数一次，车辆唯一标识为vin，因此对vin进行去重统计总数即可

select count(distinct(“vin”)) totalVehicleNum from “itcastsrc_vehicle_detail” where “terminalTime” like ‘2019-11-20 15%’;

统计每天在线的车辆总数

• 以天为维度统计车辆数，基于“总的采集数据车辆数”统计，增加按天分组

• 需要用到vin，terminalTime

select count(1) dayTotalNum, SUBSTR(“terminalTime”, 0,10) from “itcastsrc_vehicle_detail” group by SUBSTR(“terminalTime”, 0,10);

2.2 车辆电量统计

统计车辆电量百分比

• 统计车辆最大、最小电量百分比，需要用到vin,currentElectricity

• 单位：百分比

select “vin”, max(“currentElectricity”) as “maxValue”, min(“currentElectricity”) as “minValue” from “itcastsrc_vehicle_detail” where “currentElectricity” is not null group by “vin”;

统计当前电量

• 统计用到的字段remainPower

• 单位：百分比(已乘以100)

• 当前电量求平均

to_number(“value”) 把字符串类型或日期类型转换成数值类型

• 官方说明

select “vin”, avg(*to_number(“remainPower”)) avgRemainPower from “itcastsrc_vehicle_detail” where “terminalTime” like ‘2019-11-20%’ and “remainPower” is not null group by “vin”;

2.3 车辆油耗统计

统计车辆百公里油耗-ok

• 统计用到的字段fuelConsumption100km

• 统计每辆车的百公里油耗

电动车为何还有油耗

一辆普通的燃油汽车和一辆电动汽车行驶相同的距离，需要的能量是相同的,根据汽车动能计算出行驶百公里耗电量得出油耗

select distinct(“vin”) distinctVin, “fuelConsumption100km” from “itcastsrc_vehicle_detail” where “terminalTime” like ‘2019-11-20%’ and “fuelConsumption100km” is not null group by “vin”,“fuelConsumption100km”;

2.4 车辆速度统计

统计车辆发动机速度

• 统计使用字段engineSpeed

• 求最大、最小、平均车速

select “vin”, max(“engineSpeed”) maxEngineSpeed, min(“engineSpeed”) minEngineSpeed, avg(*to_number(“engineSpeed”)) avgEngineSpeed from “itcastsrc_vehicle_detail” where “terminalTime” like ‘2019-11-20%’ and “engineSpeed” is not null group by “vin”;

统计车辆速度vehicleSpeed

• 求最大、最小、平均车速

select “vin”, max(“vehicleSpeed”) maxVehicleSpeed, min(“vehicleSpeed”) minVehicleSpeed, avg(*to_number(“vehicleSpeed”)) avgVehicleSpeed from “itcastsrc_vehicle_detail” where “terminalTime” like ‘2019-11-20%’ and “vehicleSpeed” is not null group by “vin”;

思考：统计出以上电量、油耗、速度的意义，根据数据得出有价值的结论，如何直观查看到数据变化？