起因
最近收到一个需求,做一张宽表的清洗处理,基于一张表格内条件做清洗,于是便把表格直接抽为一张字典表,用于对基础数据做match匹配。表格数据如下:
整理后表数据如下(仅作演示,省略了其他条件字段):
hive> select arch_value, industry from dim.company_arch_value_condition_tag limit 10;
OK
高 IT互联网_IT信息技术
高 金融_银行业|金融_保险业|金融_资本市场_证券期货
高 教育
高 商贸
高 (除上述的其他行业分类)
中 IT互联网_IT信息技术
中 金融_银行业|金融_保险业|金融_资本市场_证券期货
中 教育
中 商贸
中 (除上述的其他行业分类)
这样便简化了大量的开发工作,直接基于SQL便可以做匹配处理了,但是发现行业字段含有多个行业的情况,同时行业也存在细分行业,于是,便有了下面我们要介绍的,列转行,同时,行转列以及一列转多列,下面做简单的记录:
介绍
列转行(一列转多行)
SQL操作都是固定函数,... lateral view explode(split(处理字段,'分隔符')) ,这里先基于行业字段industry,存在多个行业时,我们转化为多行。如下:
select 字段1,字段2,待转行字段 from 表名 lateral view explode(split(处理字段,'分隔符')) a as 别名;
我们的SQL,执行后的效果:
hive> select
> arch_value, industry_type
> from dim.company_arch_value_condition_tag
> lateral view explode(split(industry,'\\|')) a as industry_type limit 10;
.....
...
高 IT互联网_IT信息技术
高 金融_银行业
高 金融_保险业
高 金融_资本市场_证券期货
高 教育
高 商贸
高 (除上述的其他行业分类)
中 IT互联网_IT信息技术
中 金融_银行业
中 金融_保险业
行转列(多行转一列)
下面,对于存在细分行业的字段,进行行转列操作,操作也是固定的函数 ... concat_ws('分隔符', collect_set(待处理字段))...,如下:
select 字段1,字段2, concat_ws('分隔符', collect_set(待处理字段)) as 别名 from 表名 group by 字段1,字段2
我们的SQL,如下:
hive> select
> arch_value, concat_ws('|', collect_set(industry)) as industry
> from
> dim.company_arch_value_condition_tag
> group by arch_value limit 10;
...
....
- 商贸_居民服务_餐饮|商贸_居民服务_零售|商贸_居民服务_住宿|商贸_商业服务|(除上述的其他行业分类)|金融|商贸_居民服务_文旅_文化传媒|商贸_居民服务_零售_商业百货|商贸_居民服务_住宿_酒店|医疗|教育|金融_银行业_综合型银行|-|交通_水路运输_海运服务_客运游轮|交通_航空运输_航空客运服务_航空客运服务|商贸_居民服务_住宿|商贸_居民服务_零售_商品零售|教育_培训机构_线下培训|医疗_医疗保健机构|商贸_专业服务_专业服务_科学及市场、行业研究|医疗|教育|IT互联网|金融_资本市场|金融_金融交易数据|IT互联网_IT信息技术_互联网服务|医疗_医疗保健机构|商贸_居民服务_零售_互联网电商|商贸_住建|医疗_医疗保健机构|医疗_疗养康复机构|教育|商贸_住建_建筑_物业管理|商贸_居民服务_文旅_旅游景区|商贸_居民服务_文旅_文博场馆|商贸_居民服务_文旅_文化传媒|物流_物流服务_货运物流_货运物流|教育_培训机构|金融_银行业|金融_保险业|交通_航空运输_航空客运服务_航空客运服务|商贸_居民服务_文旅_旅行社|商贸_居民服务_零售_商业百货|商贸_居民服务_文旅_旅游景区|交通_公路运输_公路客运_公交运输|物流_物流服务_货运物流_货运物流|物流_其他物流_物流相关_物流相关|商贸_商业服务_仓储服务_仓储服务|工业_制造加工|工业_园区厂房_工业园区_工业园区|商贸_居民服务_餐饮_正餐快餐|商贸_居民服务_住宿
中 IT互联网_IT信息技术|金融_银行业|金融_保险业|金融_资本市场_证券期货|教育|商贸|(除上述的其他行业分类)|-|工业|物流
低 IT互联网_IT信息技术|金融_银行业|金融_保险业|金融_资本市场_证券期货|教育|商贸|(除上述的其他行业分类)|-|工业|物流
高 IT互联网_IT信息技术|金融_银行业|金融_保险业|金融_资本市场_证券期货|教育|商贸|(除上述的其他行业分类)|-|工业|物流
当然,按我们的业务,这里不需要行转列。我们需要对一列转多列。这里只是做一个行转列的演示。
一列转多列
我们需要在列转行的基础上,再进行一列转多列(细分行业,按我的业务最多四级,不够四级的我们直接补null,方便后面处理)。具体操作如下:
hive>
>
> select a.arch_value,
> (case when size(split(a.industry_type, '_'))>0 then split(a.industry_type, '_')[0] else 'null' end) as industry1,
> (case when size(split(a.industry_type, '_'))>1 then split(a.industry_type, '_')[1] else 'null' end) as industry2,
> (case when size(split(a.industry_type, '_'))>2 then split(a.industry_type, '_')[2] else 'null' end) as industry3,
> (case when size(split(a.industry_type, '_'))>3 then split(a.industry_type, '_')[3] else 'null' end) as industry4
> from
> (select
> arch_value, industry_type
> from dim.company_arch_value_condition_tag
> lateral view explode(split(industry,'\\|')) a as industry_type) a limit 10;
...
....
高 IT互联网 IT信息技术 null null
高 金融 银行业 null null
高 金融 保险业 null null
高 金融 资本市场 证券期货 null
高 教育 null null null
高 商贸 null null null
高 (除上述的其他行业分类) null null null
中 IT互联网 IT信息技术 null null
中 金融 银行业 null null
中 金融 保险业 null null
上面这个是我所需要的,到这里,我将一张业务表格转化为了一张用于匹配需要的字典表,后面直接使用SparkSQL按不同的条件,规则,和对应的基础表进行匹配处理即可,这里会出现数据膨胀的问题,处理时需要注意!
ok,如果你有好的方法,也可以留言分享!