HiveSQL需求记录

场景1Hive解析棘手的json数据：

如题，在Hive中导入S3中的CSV文件，CSV文件中有某些字段又是json串，而且还有数组，Hive将S3桶中的CSV文件导入表就不在这里说了，参照我另一篇就行，Hive将CSV文件导入表。
导入表以后我们可以得到囊括了所有CSV字段数据的一张大表，而我们需要解析json字段将其放到一张新表里，哦对了，先把数据拿出来一条给大家看看。。。。

LVYPDBAA0KP078772, ,“359315071222755 “,2019-12-04 11:07:35,车辆启动状态,未充电状态,纯电,9.0,7301.0,337.2,49.8,25,工作,有,无,自动D档,3693,28%,制动关,1, ,耗电, , , , , , ,关闭状态,0,0.00,无效定位,北纬,东经,104.065550,30.617226,1,14,3.514,1,32,3.505,1,2,18,1,6,16,无故障,0,0,0,0, , , , ,正常,正常,正常,正常,正常,正常,正常,正常,正常,正常,正常,正常,正常,正常,正常,正常,正常,正常,正常,1, , , ,“序号：1,驱动电机状态:耗电,驱动电机控制器温度:23,驱动电机转速:77.0,驱动电机转矩:920.0,驱动电机温度:19,电机控制器输入电压:337.8,电机控制器直流母线电流:22.6”,1, ,”[{”“currentcharging_device_current”":"“49.8"”,"“frameNum”":"“1"”,"“totalCells”":"“96"”,"“units”":""[{"“value”":"“3.508"”},{"“value”":"“3.510"”},{"“value”":"“3.512"”},{"“value”":"“3.512"”},{"“value”":"“3.513"”},{"“value”":"“3.511"”},{"“value”":"“3.511"”},{"“value”":"“3.513"”},{"“value”":"“3.513"”},{"“value”":"“3.512"”},{"“value”":"“3.511"”},{"“value”":"“3.511"”},{"“value”":"“3.513"”},{"“value”":"“3.514"”},{"“value”":"“3.512"”},{"“value”":"“3.507"”},{"“value”":"“3.507"”},{"“value”":"“3.509"”},{"“value”":"“3.511"”},{"“value”":"“3.511"”},{"“value”":"“3.510"”},{"“value”":"“3.510"”},{"“value”":"“3.508"”},{"“value”":"“3.508"”},{"“value”":"“3.509"”},{"“value”":"“3.511"”},{"“value”":"“3.511"”},{"“value”":"“3.510"”},{"“value”":"“3.512"”},{"“value”":"“3.510"”},{"“value”":"“3.509"”},{"“value”":"“3.505"”},{"“value”":"“3.507"”},{"“value”":"“3.510"”},{"“value”":"“3.510"”},{"“value”":"“3.511"”},{"“value”":"“3.509"”},{"“value”":"“3.510"”},{"“value”":"“3.508"”},{"“value”":"“3.509"”},{"“value”":"“3.510"”},{"“value”":"“3.508"”},{"“value”":"“3.509"”},{"“value”":"“3.509"”},{"“value”":"“3.510"”},{"“value”":"“3.510"”},{"“value”":"“3.510"”},{"“value”":"“3.505"”},{"“value”":"“3.507"”},{"“value”":"“3.509"”},{"“value”":"“3.510"”},{"“value”":"“3.510"”},{"“value”":"“3.509"”},{"“value”":"“3.510"”},{"“value”":"“3.509"”},{"“value”":"“3.509"”},{"“value”":"“3.509"”},{"“value”":"“3.509"”},{"“value”":"“3.509"”},{"“value”":"“3.509"”},{"“value”":"“3.510"”},{"“value”":"“3.510"”},{"“value”":"“3.510"”},{"“value”":"“3.505"”},{"“value”":"“3.509"”},{"“value”":"“3.512"”},{"“value”":"“3.512"”},{"“value”":"“3.512"”},{"“value”":"“3.511"”},{"“value”":"“3.514"”},{"“value”":"“3.510"”},{"“value”":"“3.512"”},{"“value”":"“3.512"”},{"“value”":"“3.512"”},{"“value”":"“3.512"”},{"“value”":"“3.512"”},{"“value”":"“3.513"”},{"“value”":"“3.512"”},{"“value”":"“3.513"”},{"“value”":"“3.507"”},{"“value”":"“3.508"”},{"“value”":"“3.510"”},{"“value”":"“3.511"”},{"“value”":"“3.510"”},{"“value”":"“3.511"”},{"“value”":"“3.510"”},{"“value”":"“3.509"”},{"“value”":"“3.510"”},{"“value”":"“3.510"”},{"“value”":"“3.509"”},{"“value”":"“3.510"”},{"“value”":"“3.512"”},{"“value”":"“3.511"”},{"“value”":"“3.511"”},{"“value”":"“3.509"”},{"“value”":"“3.506"”}]"","“vol_package”":"“1"”,"“voltage”":"“337.2"”}]",“序号：1,温度探针总数：12,温度可充电储能子系统个数：1”,“序号：1,可充电储能装置电流：49.8,单体电池总数：96,本帧单体电池总数：96,可充电储能装置电压：337.2,电压可充电储能子系统个数：1”,"[{"“tem_package”":"“1"”,"“totalPBProbes”":"“12"”,"“units”":""[{"“value”":"“17.000"”},{"“value”":"“18.000"”},{"“value”":"“17.000"”},{"“value”":"“18.000"”},{"“value”":"“17.000"”},{"“value”":"“16.000"”},{"“value”":"“17.000"”},{"“value”":"“17.000"”},{"“value”":"“17.000"”},{"“value”":"“18.000"”},{"“value”":"“18.000"”},{"“value”":"“18.000"”}]""}]",

没错，一个逗号分割一个字段，因为json串里面也有逗号，所以分割的时候要注意设置一下引号参数，分割这块的话有兴趣的可以去我另一篇博客看看Hive分割字段数据中带有特殊分割字符的csv表。当然了，我这是用notepad++打开的，然后里面不知怎么的就显示的\",反正用EXCEL打开就没有，但没关系，我Hive读数据的时候\没有，双引号倒是有俩。。。我们只拿其中一个字段举例说明解析过程。

就拿这个字段吧。

[{“tem_package”:“1”,“totalPBProbes”:“12”,“units”:"[{"“value”":"“15.000"”},{"“value”":"“16.000"”},{"“value”":"“15.000"”},{"“value”":"“16.000"”},{"“value”":"“16.000"”},{"“value”":"“15.000"”},{"“value”":"“16.000"”},{"“value”":"“16.000"”},{"“value”":"“16.000"”},{"“value”":"“16.000"”},{"“value”":"“15.000"”},{"“value”":"“14.000"”}]"}]

没错，就是这么神奇，里面有两个双引号，而且里面有个数组units，最最关键的是数组里面还有很多小的KV对，关键还tm全是value。数据是电池包的温度信息，因为电池里面有很多小电池，所以客户那边给整成了这个吊样，连tm名字都不舍得起不一样的，tmd。
接下来开始解析。
关于解析json数据，Hive内置函数有两个可以解析，一个是get_json_object(json,’$.json里面的key’),另一个是json_tuple(json,‘json里面的key’)，这里我们先来测试一下。

看到我们查该字符串中的units返回为NULL，因为这玩意现在还是一个数组，压根就不是条json，函数压根不认识，你看这数据难受的。
既然数据不是json，那就需要把它改造成我们需要的数据格式，
Hive中有regexp_extract和regexp_replace，这里用前者去掉数组两端的[]，用后者去替换掉一些我们不想要的阻碍解析的特殊符号。
正常情况下该数据对应的json格式应该是这样的
{“key1”:“value1”,“key2”:“value2”,“key3”:[{“key3.11”:“value3.11”,“key3.12”:“value3.12”},{“key3.21”:“value3.21”,“key3.22”:“value3.22”}]}
可以看到json内部的数组两端并没有双引号，所以源数据这里要去掉，源数据数组内部有两个双引号，这里要改成一个双引号，hivesql为：

select regexp_replace(regexp_replace(regexp_replace(regexp_extract(UNITS_TEMPERATURE,'^\\[(.+)\\]$',1),'\"\\[\\{','\\[\\{'),'\\}\\]\"','\\}\\]'),'\"\"','\"') from volvo_sftp_utf82 limit 1;

修改之后的数据为：

这个时候在去调用函数获取json中的值：为了方便，使后面的sql看起来简短点，先把这些数据插到json_test表里，当然只要一个json string字段就行。

然后使用get_json_object()和json_tuple()分别获取json中的数据。

我们可以看到，get_json_object()获取的数据有是有了，但是units还是个数组，不慌，get_json_object()认识(点调用).和(中括号)[]，我们可以通过数组的索引获取需要的数据。

有一点需要注意的是，units这个字段的数据暂且不提，如果只是一般的json数据，我们可以通过前面两个字段不难看出，相对于get_json_object()而言，json_tuple()获取数据更加的简洁。但是要注意json_tuple并不认识(点调用).和(中括号)[]。简而言之就是get_json_object()一次可以也只能得到一个字段，因为这个函数只能接受两个参数。而json_tuple()可以一次性获取很多字段的数据。
单独拿出来给观众老爷们对比一下：

所以不得不说这源数据真tm坑人，这个字段还好，只有12个value。。。。。。一个一个的get_json_object()就过去了。可是上面还一个字段有96个。。。。。。WCTNND。。。。
最后的演示。。。。。。

场景二：同比环比报表展示

在该场景中，需要将所提供的源数据表中的数据进行关联并聚合之后，将所有的经销商（最新更改日期对应的经销商）在每个月所举办的所有活动的类型以及对应的数目都要展示出来，还要展示出对应的上个月的数据以及去年同期的当月数据值。（因为有上月和去年同期数据需求，所以即使在某月份不存在的活动数据也要显示出来，即：为0，而明细数据则正常显示）。明细如下（中间隐藏了n行明细）：

源数据表可以分为两部分，一部分是A区，只有一张表，是经销商对应的明细表；另一部分是B区，通过一些关联关系获取对应经销商在某个月的活动数据以及可以对活动数据进行聚合取值等等。
那么这个需求的主要的点有如下几点：

1. 所有的