hive 列转行和行转列

最新推荐文章于 2023-01-19 10:57:06 发布

Super乐

最新推荐文章于 2023-01-19 10:57:06 发布

阅读量1k

点赞数 1

分类专栏： hive 文章标签： hive

本文链接：https://blog.csdn.net/wplblog/article/details/113745695

版权

hive 专栏收录该内容

3 篇文章 1 订阅

订阅专栏

案例分析一：

列转行

测试数据的格式如下：

hive> select * from col_lie limit 10;
OK
col_lie.user_id    col_lie.order_id
104399            1715131
104399            2105395
104399            1758844
104399            981085
104399            2444143
104399            1458638
104399            968412
104400            1609001
104400            2986088
104400            1795054

把相同user_id的order_id按照逗号转为一行

select user_id,
concat_ws(',',collect_list(order_id)) as order_value 
from col_lie
group by user_id
limit 10;

//结果(简写)
user_id    order_value
104399    1715131,2105395,1758844,981085,2444143

总结

使用函数：concat_ws(',',collect_set(column))

说明：collect_list 不去重，collect_set 去重。 column的数据类型要求是string

案例分析二：

行转列

测试数据格式如下：

hive> select * from lie_col;
OK
lie_col.user_id    lie_col.order_value
104408    2909888,2662805,2922438,674972,2877863,190237
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128
104406    1463273,2351480,1958037,2606570,3226561,3239512,990271,1436056,2262338,2858678
104405    153023,2076625,1734614,2796812,1633995,2298856,2833641,3286778,2402946,2944051,181577,464232
104404    1815641,108556,3110738,2536910,1977293,424564
104403    253936,2917434,2345879,235401,2268252,2149562,2910478,375109,932923,1989353
104402    3373196,1908678,291757,1603657,1807247,573497,1050134,3402420
104401    814760,213922,2008045,3305934,2130994,1602245,419609,2502539,3040058,2828163,3063469
104400    1609001,2986088,1795054,429550,1812893
104399    1715131,2105395,1758844,981085,2444143,1458638,968412
Time taken: 0.065 seconds, Fetched: 10 row(s)

将order_value的每条记录切割为单元素

select user_id,order_value,order_id from lie_col
lateral view explode(split(order_value,',')) num as order_id
limit 10;

//结果
user_id    order_value                                                                          order_id
104408    2909888,2662805,2922438,674972,2877863,190237                                         2909888
104408    2909888,2662805,2922438,674972,2877863,190237                                         2662805
104408    2909888,2662805,2922438,674972,2877863,190237                                         2922438
104408    2909888,2662805,2922438,674972,2877863,190237                                         674972
104408    2909888,2662805,2922438,674972,2877863,190237                                         2877863
104408    2909888,2662805,2922438,674972,2877863,190237                                         190237
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128   2982655
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128   814964
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128   1484250
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128   2323912
Time taken: 0.096 seconds, Fetched: 10 row(s)

mysql跟hive列转行/行转列一样,但是多行转一行,一行转多行就不太一样了

启动hive时 ./hive -S (去除MR打印日志)
hive命令行 set hive.exec.mode.local.auto=true; 设置本地模式
数据准备:

create table student_score(s_id int,s_name string,s_sub string,s_score bigint);
insert into student_score values
(1,'张三','数学',90),
(2,'张三','语文',85),
(3,'张三','英语',92),
(4,'李四','数学',88),
(5,'李四','语文',91),
(6,'李四','英语',99),
(7,'王五','数学',100),
(8,'王五','语文',82),
(9,'王五','英语',88);

行转列

思路,先使用case when 分成数学,语文,英语三列

--注意,hive不支持列名为中文
select s_name,
case s_sub when '数学' then s_score else 0 end shuxue,
case s_sub when '语文' then s_score else 0 end yuwen,
case s_sub when '英语' then s_score else 0 end yingyu
from student_score ;

在这里插入图片描述
再根据名字 group by

select s_name,
sum(case s_sub when '数学' then s_score else 0 end) shuxue,
sum(case s_sub when '语文' then s_score else 0 end) yuwen,
max(case s_sub when '英语' then s_score else 0 end) yingyu
from student_score group by s_name;

在这里插入图片描述

列转行

先创建上图中的表

create table student_score2 as 
select s_name,
sum(case s_sub when '数学' then s_score else 0 end) shuxue,
sum(case s_sub when '语文' then s_score else 0 end) yuwen,
max(case s_sub when '英语' then s_score else 0 end) yingyu
from student_score group by s_name;

列转行sql:

select s_name,'数学' as s_sub, shuxue as s_score from student_score2
union all select s_name,'语文' as s_sub, yuwen as s_score from student_score2
union all select s_name,'英语' as s_sub, yingyu as s_score from student_score2;

结果
在这里插入图片描述

多行转一行

生成格式:

张三           数学:90 语文:85 英语:92

先说函数:
1 collect_xx函数:
collect_set(column)
collect_list(column)
在这里插入图片描述
set去重,list不去重,通过group by,将相通s_name的s_sub列转成数组.

2 concat_ws函数
单行中的列聚合:concat_ws(sp,str1,str2,…) sp是分隔符
在这里插入图片描述
ps:单行列聚合还有一个函数式concat(str1,str2,…) 跟concat_ws区别是前者如果有一个null,就后返回null并且只是字符串拼接,后者会把null去除,可以设定分隔符拼接.
多行中的列聚合 concat_ws(sp,arr1,arr2,…) 结合数组参数collect_set/collect_list使用,并且只能是字符串数组,所以其他类型要转化成string.
完整sql:

select s_name,
concat_ws('|',collect_set(concat(s_sub,cast(s_score as string)))) as all_score
from student_score group by s_name;

在这里插入图片描述

ps:mysql是使用group_concat函数完成多行转一行的,hive中有些版本没有group_concat函数

一行转多行:

先创建上图中的表

create table studnent_score3 as 
select s_name,
concat_ws('|',collect_set(concat(s_sub,cast(s_score as string)))) as all_score
from student_score group by s_name;

lateral view 侧视图
lateral view + UDTF 使用 UDTF一进多出函数
explode() 函数是UDTF函数,接收一个数组或者map函数
split(column,sp) split 接收一个字符串函数,指定分隔符返回一个数组
这里使用 lateral view explode(split(列名,分隔符)) 表名(随便写) as 列名(新列名)

sql:

select s_name,score from student_score3 
lateral view explode(split(all_score,'\\|')) a as score;

在这里插入图片描述
详解一下,lateral view 相当于开了一个视图,explode()函数只有查一个列时候才有效,比如

只查explode(split(all_score,’\|’)) 是可以的,但是加上s_name是不行的,因为s_name不知道如何处理.
lateral veiw explode(split(all)_score,’\|’’) a 就相当于开了一个a视图,只有一列 as score.
后面视图中列score 与前面的s_name 再进行笛卡尔积,就展现出了正确的结果,并且lateral view 和可以连着用,lateral veiw 1 lateral view 2
如

select name ,alie,blie from student 
lateral view explode(split(a,',')) a as alie 
lateral view explode(split(b,',')) b as blie;

案例分析三：

explode 和 lateral view

为什么把这连个放一块呢，因为这两个经常放在一起用啊

explode与lateral view在关系型数据库中本身是不该出现的，因为他的出现本身就是在操作不满足第一范式的数据（每个属性都不可再分），本身已经违背了数据库的设计原理（不论是业务系统还是数据仓库系统），不过大数据技术普及后，很多类似pv，uv的数据，在业务系统中是存贮在非关系型数据库中，用json存储的概率比较大，直接导入hive为基础的数仓系统中，就需要经过ETL过程解析这类数据，explode与lateral view在这种场景下大显身手。

explode用法

在介绍如何处理之前，我们先来了解下Hive内置的 explode 函数，官方的解释是：explode() takes in an array (or a map) as an input and outputs the elements of the array (map) as separate rows. UDTFs can be used in the SELECT expression list and as a part of LATERAL VIEW. 意思就是 explode() 接收一个 array 或 map 类型的数据作为输入，然后将 array 或 map 里面的元素按照每行的形式输出。其可以配合 LATERAL VIEW 一起使用。光看文字描述很不直观，咱们来看看几个例子吧。

hive (default)> select explode(array('A','B','C'));
OK
A
B
C
Time taken: 4.188 seconds, Fetched: 3 row(s)
 
hive (default)> select explode(map('a', 1, 'b', 2, 'c', 3));
OK
key	value
a	1
b	2
c	3

explode函数接收一个数组或者map类型的数据，通常需要用split函数生成数组。

explode 配合解析Json 数组

这里有数据：

{"info":[
	{"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
	{"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
	{"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
	{"AppName":"2345Explorer_embedupdate","plugin":"-1"},
	{"AppName":"SogouExplorer_embedupdate","pepper":"-1"}
]}

现在需要将AppName和pepper提取出来，然后按行存放，一行一个,首先我们按照上一节我们学习的Json 处理的函数进行尝试

select
get_json_object(
  '{"info":[
    {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
    {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
    {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
    {"AppName":"2345Explorer_embedupdate","plugin":"-1"},
    {"AppName":"SogouExplorer_embedupdate","pepper":"-1"}
  ]}',
  "$.info[*].AppName"
);

但是我们注意到这里虽然提取出来了但是返回值是一个字符串啊,我为啥知道它是字符串，但是看起来像是一个数组啊，因为我用explode 函数试过了，那接下来怎么处理呢，这个时候就可以需要配合split 处理了，为了方便操作我直接用上么的结果进行操作

["SogouExplorer_embedupdate","SogouExplorer_embedupdate","SogouExplorer_embedupdate","2345Explorer_embedupdate","SogouExplorer_embedupdate"]

然我我们尝试处理一下上面这个字符串，首先我们需要split 一下，但是在此之前我们需要将两边的中括号去掉，否则到时候我们的数据会包含这个两个符号的

select regexp_replace('["SogouExplorer_embedupdate","SogouExplorer_embedupdate","SogouExplorer_embedupdate","2345Explorer_embedupdate","SogouExplorer_embedupdate"]',"[\\[\\]]",'')

然后我们就可以split和explode 的了

select explode(split(regexp_replace('["SogouExplorer_embedupdate","SogouExplorer_embedupdate","SogouExplorer_embedupdate","2345Explorer_embedupdate","SogouExplorer_embedupdate"]',"[\\[\\]]",''),','));

这里解析json数组，我们本质上还是使用regexp_replace替换掉中括号，然后再使用split函数拆分为数据，给explode去分裂成多行。上面的这种写法有问题吗，功能是可以完成，但是这里只是提出来了AppName 这个字段，还有一个字段没有提取出来呢，要是想把它提取出来，上面的步骤你还得再来一遍才可以，接下来我们尝试引入json_tuple来简化一下我们的操作,我们先将其explode 成多行简单json 字符串，然后再使用json_tuple 进行处理

select
explode(
  split(
     regexp_replace(
        regexp_replace(
          get_json_object(
            '{"info":[
              {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
              {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
              {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
              {"AppName":"2345Explorer_embedupdate","plugin":"-1"},
              {"AppName":"SogouExplorer_embedupdate","pepper":"-1"}
            ]}',"$.info")
       ,'[\\[\\]]' ,'')
     ,'(},\\{)','}#\\{')
    ,'#')
 );

这里两次调用了regexp_replace，第一次是为了去掉两边的中括号，第二次是为了将,jons 里面的逗号和分割json 的逗号进行区分，因为我们按照数组内容之间的分隔符进行split ，所以这里可以看做是将数组字符串的分隔符有逗号换成了# 号，然后就按照# split 了

接下来就可以调用json_tuple 函数了

select
	json_tuple(data,'AppName','pepper')
from(
  select
  explode(
    split(
       regexp_replace(
          regexp_replace(
            get_json_object(
              '{"info":[
                {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
                {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
                {"AppName":"SogouExplorer_embedupdate","pepper":"-1"},
                {"AppName":"2345Explorer_embedupdate","plugin":"-1"},
                {"AppName":"SogouExplorer_embedupdate","pepper":"-1"}
              ]}',"$.info")
         ,'[\\[\\]]' ,'')
       ,'(},\\{)','}#\\{')
      ,'#')
   ) as data
) json_table;

这样我们就将我们需要的字段解析出来了

lateral view

开始之前我们先说一下它的用法 LATERAL VIEW udtf(expression) tableAlias AS columnAlias，你可以将lateral view翻译为侧视图

我们有这样的一份样本数据(

刘德华	演员,导演,制片人
李小龙	演员,导演,制片人,幕后,武术指导
李连杰	演员,武术指导
刘亦菲	演员

这里我们希望转换成下面这样的格式

刘德华 演员
刘德华 导演
刘德华 制片人
李小龙 演员
李小龙 导演
李小龙 制片人
李小龙 幕后
李小龙 武术指导

create table ods.ods_actor_data(
    username string,
    userrole string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data local inpath "/Users/liuwenqiang/workspace/hive/lateral.data" overwrite into table ods.ods_actor_data;

从我们前面的学习，我们知道这里应该用explode函数

select explode(split(userrole,',')) from  ods.ods_actor_data;

理论上我们这下只要把username 也选出来就可以了

select username,explode(split(userrole,',')) from  ods.ods_actor_data;

Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)

因为explode 是一个UDTF，所以你不能直接和其他字段一起使用，那应该怎么做呢在

select
   username,role
from
    ods.ods_actor_data
LATERAL VIEW
    explode(split(userrole,',')) tmpTable as role
;

看起来到这里我们的实现就结束了

lateral view outer

为什么会多了一个 OUTER 关键字呢，其实你也可以猜到了outer join 有点像，就是为了避免explode 函数返回值是null 的时候，影响我们主表的返回，注意是null 而不是空字符串

select
   username,role
from
    ods.ods_actor_data
LATERAL VIEW
     explode(array()) tmpTable as role
;

加上outer 关键字之后

select
   username,role
from
    ods.ods_actor_data
LATERAL VIEW outer
    explode(array()) tmpTable as role
;

其实一个SQL你可以多次使用lateral view也是可以的,就像下面这样

SELECT * FROM exampleTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(myCol1) myTable2 AS myCol2;

lateral view 的实现原理是什么

首先我们知道explode()是一个UDTF 就是一个输入进去，多个输出出来，或者是进去一行，出来一列(多行)

lateral view 关键字就是将每一行的特定字段交给explode 函数的表达式，然后将输出结果和当前行做笛卡尔积,然后重复，直到循环完表里的全部数据，然后就变成下面装了(图中省略了传给explode 字段的那一列)

但其实到这里我就产生了一个疑问，为啥要这样设计，直接将普通字段和UDTF 的函数的返回值一起查询不好吗，然后将原始字段和UDTF 的返回值做笛卡尔积就行了啊，为啥还要lateral view 呢，哈哈。

lateral view 中where 的使用

你可能会说where 不就那么用吗，还有啥不一样的，还真有，例如我上面的信息只要刘德华的，那你肯定会写出下面的SQL

select
    username,role
from
    ods.ods_actor_data
        LATERAL VIEW
    explode(split(userrole,',')) tmpTable as role
where
    username='刘德华'
;

要是我只要导演的呢，但是我们知道userrole 这个字段是包没有直接是导演的，但是又包含导演的演员,导演,制片人,幕后,武术指导,其实这个时候你可以用下面的别名字段role

select
    username,role
from
    ods.ods_actor_data
        LATERAL VIEW
    explode(split(userrole,',')) tmpTable as role
where
    role="导演"
;

总结

一个SQL 里lateral view 你可以多次使用，就会多次做笛卡尔积
UDTF 要配合lateral view 一起使用才可以

Super乐

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
hive 列转行和行转列

列转行测试数据的格式如下：hive> select * from col_lie limit 10;OKcol_lie.user_id col_lie.order_id104399 1715131104399 2105395104399 1758844104399 981085104399 2444143104399 1458638104
复制链接

扫一扫