0
0

Hive中的行转列和列转行

一、背景

在使用Hive的数据开发工作中，为了处理复杂的业务需求，经常要用到行转列或列转行的操作。为了节省以后处理这类工作的时间，提高工作效率，现将Hive行列互转的操作方法总结如下。

二、列转行

列转行，顾名思义，将原本某列中一行的数据拆分为多行，该操作会使得数据行数增多。我们采用的方法是使用LATERAL VIEW语法，配合explode函数（或其他UDTF）

2.1 对于ARRAY类型或MAP类型的字段

Hive中是有可以直接将一行输出为多行的函数的（即UDTF），比如explode函数，举一个例子来说明这个函数的用法，数据集如下：

hive (article)>SELECT*FROM test_liezhuanhang_01 AS p;
OK
p.name  p.class p.province  p.score p.info
小明  1   山东  [99,86,100] {"身高":177,"体重":60,"年龄":18}
小王  2   北京  [97,77,80]  {"身高":180,"体重":70,"年龄":17}
小赵  2   广东  [77,89,90]  {"身高":170,"体重":50,"年龄":17}
小明  1   山东  [80,76,79]  {"身高":185,"体重":72,"年龄":18}
Time taken: 1.014 seconds, Fetched: 4row(s)

其中score字段为数组ARRAY(BIGINT)类型，info字段为MAP<STRING,BIGINT>类型，假设我们想根据score字段将一行拆成多行，代码如下：

hive (article)>SELECT explode(score)AS score FROM test_liezhuanhang_01;
OK
score
9986100977780778990807679Time taken: 0.291 seconds, Fetched: 12row(s)

可以看到原本的4行数据按数组顺序拆成了12行。explode函数同样也可以用在MAP类型上，代码如下：

hive (article)>SELECT explode(info)AS(info_key,info_value)FROM test_liezhuanhang_01;
OK
info_key    info_value
身高  177
体重  60
年龄  18
身高  180
体重  70
年龄  17
身高  170
体重  50
年龄  17
身高  185
体重  72
年龄  18Time taken: 0.07 seconds, Fetched: 12row(s)

但是这类函数有如下三个限制：
（1）SELECT 中不能存在其他列：
比如

SELECT name,explode(score) AS score FROM test_liezhuanhang_01

是不允许的
（2）不能嵌套使用：
比如

SELECT explode(explode(score)) AS score FROM test_liezhuanhang_01

是不允许的
（3）不能和

group by / cluster by / distribute by / sort by

等一起使用。

如果想要解除上面的限制，就要配合LATERAL VIEW一起使用，代码如下：

hive (article)>SELECT  name
              >,class
              >,province
              >,score
              >,info
              >,score_01
              >FROM test_liezhuanhang_01 LATERAL VIEW explode(score) table1 AS score_01;
OK
name    class   province    score   info    score_01
小明  1   山东  [99,86,100] {"身高":177,"体重":60,"年龄":18}  99
小明  1   山东  [99,86,100] {"身高":177,"体重":60,"年龄":18}  86
小明  1   山东  [99,86,100] {"身高":177,"体重":60,"年龄":18}  100
小王  2   北京  [97,77,80]  {"身高":180,"体重":70,"年龄":17}  97
小王  2   北京  [97,77,80]  {"身高":180,"体重":70,"年龄":17}  77
小王  2   北京  [97,77,80]  {"身高":180,"体重":70,"年龄":17}  80
小赵  2   广东  [77,89,90]  {"身高":170,"体重":50,"年龄":17}  77
小赵  2   广东  [77,89,90]  {"身高":170,"体重":50,"年龄":17}  89
小赵  2   广东  [77,89,90]  {"身高":170,"体重":50,"年龄":17}  90
小明  1   山东  [80,76,79]  {"身高":185,"体重":72,"年龄":18}  80
小明  1   山东  [80,76,79]  {"身高":185,"体重":72,"年龄":18}  76
小明  1   山东  [80,76,79]  {"身高":185,"体重":72,"年龄":18}  79Time taken: 0.451 seconds, Fetched: 12row(s)

可以看到数据变成了12行，而最后一列正是score字段拆分后的结果。我们可以理解为，Hive先对字段score执行explode函数，再将执行后的结果与原表做笛卡尔积的join，最后形成一个虚拟表table1，虚拟表中包含score拆分后的字段score_01。

另外，LATERAL VIEW可以嵌套使用，代码如下：

hive (article)>SELECT  name
              >,class
              >,province
              >,score
              >,info
              >,score_01
              >,info_key
              >,info_value
              >FROM test_liezhuanhang_01
              > LATERAL VIEW explode(score) table1 AS score_01
              > LATERAL VIEW explode(info) table2 AS info_key,info_value;
OK
name    class   province    score   info    score_01    info_key    info_value
小明  1   山东  [99,86,100] {"身高":177,"体重":60,"年龄":18}  99  身高  177
小明  1   山东  [99,86,100] {"身高":177,"体重":60,"年龄":18}  99  体重  60
小明  1   山东  [99,86,100] {"身高":177,"体重":60,"年龄":18}  99  年龄  18
小明  1   山东  [99,86,100] {"身高":177,"体重":60,"年龄":18}  86  身高  177
小明  1   山东  [99,86,100] {"身高":177,"体重":60,"年龄":18}  86  体重  60
小明  1   山东  [99,86,100] {"身高":177,"体重":60,"年龄":18}  86  年龄  18
小明  1   山东  [99,86,100] {"身高":177,"体重":60,"年龄":18}  100 身高  177
小明  1   山东  [99,86,100] {"身高":177,"体重":60,"年龄":18}  100 体重  60
小明  1   山东  [99,86,100] {"身高":177,"体重":60,"年龄":18}  100 年龄  18
小王  2   北京  [97,77,80]  {"身高":180,"体重":70,"年龄":17}  97  身高  180
小王  2   北京  [97,77,80]  {"身高":180,"体重":70,"年龄":17}  97  体重  70
小王  2   北京  [97,77,80]  {"身高":180,"体重":70,"年龄":17}  97  年龄  17
小王  2   北京  [97,77,80]  {"身高":180,"体重":70,"年龄":17}  77  身高  180
小王  2   北京  [97,77,80]  {"身高":180,"体重":70,"年龄":17}  77  体重  70
小王  2   北京  [97,77,80]  {"身高":180,"体重":70,"年龄":17}  77  年龄  17
小王  2   北京  [97,77,80]  {"身高":180,"体重":70,"年龄":17}  80  身高  180
小王  2   北京  [97,77,80]  {"身高":180,"体重":70,"年龄":17}  80  体重  70
小王  2   北京  [97,77,80]  {"身高":180,"体重":70,"年龄":17}  80  年龄  17
小赵  2   广东  [77,89,90]  {"身高":170,"体重":50,"年龄":17}  77  身高  170
小赵  2   广东  [77,89,90]  {"身高":170,"体重":50,"年龄":17}  77  体重  50
小赵  2   广东  [77,89,90]  {"身高":170,"体重":50,"年龄":17}  77  年龄  17
小赵  2   广东  [77,89,90]  {"身高":170,"体重":50,"年龄":17}  89  身高  170
小赵  2   广东  [77,89,90]  {"身高":170,"体重":50,"年龄":17}  89  体重  50
小赵  2   广东  [77,89,90]  {"身高":170,"体重":50,"年龄":17}  89  年龄  17
小赵  2   广东  [77,89,90]  {"身高":170,"体重":50,"年龄":17}  90  身高  170
小赵  2   广东  [77,89,90]  {"身高":170,"体重":50,"年龄":17}  90  体重  50
小赵  2   广东  [77,89,90]  {"身高":170,"体重":50,"年龄":17}  90  年龄  17
小明  1   山东  [80,76,79]  {"身高":185,"体重":72,"年龄":18}  80  身高  185
小明  1   山东  [80,76,79]  {"身高":185,"体重":72,"年龄":18}  80  体重  72
小明  1   山东  [80,76,79]  {"身高":185,"体重":72,"年龄":18}  80  年龄  18
小明  1   山东  [80,76,79]  {"身高":185,"体重":72,"年龄":18}  76  身高  185
小明  1   山东  [80,76,79]  {"身高":185,"体重":72,"年龄":18}  76  体重  72
小明  1   山东  [80,76,79]  {"身高":185,"体重":72,"年龄":18}  76  年龄  18
小明  1   山东  [80,76,79]  {"身高":185,"体重":72,"年龄":18}  79  身高  185
小明  1   山东  [80,76,79]  {"身高":185,"体重":72,"年龄":18}  79  体重  72
小明  1   山东  [80,76,79]  {"身高":185,"体重":72,"年龄":18}  79  年龄  18Time taken: 0.04 seconds, Fetched: 36row(s)

最后三列即为我们想要的结果，后一个LATERAL VIEW可以看作在前一个LATERAL VIEW的结果上执行，于是原本的4行拆分成为12行，再拆分成为36行。
注意：LATERAL VIEW还可以配合其他UDTF一起使用，而不是只能与explode函数一起使用。

2.2 对于STRING类型的字段

在工作中，我们并不总是能够碰到ARRAY,MAP字段这样的理想情况，很多时候，字段是以STRING的形式出现，那么这时候我们怎么进行列转行呢？数据集如下：

hive (article)>SELECT*FROM test_liezhuanhang_02 AS p;
OK
p.name  p.class p.province  p.score p.info
小明  1   山东  99,86,100   身高:177,体重:60,年龄:18
小王  2   北京  97,77,80    身高:180,体重:70,年龄:17
小赵  2   广东  77,89,90    身高:170,体重:50,年龄:17
小明  1   山东  80,76,79    身高:185,体重:72,年龄:18Time taken: 0.073 seconds, Fetched: 4row(s)

其中，score与info都是STRING类型，这时候我们可以采用split函数与str_to_map函数，将STRING类型转化为ARRAY和MAP类型。代码如下：

hive (article)>SELECT  name
              >,class
              >,province
              >,split(score,',')AS score
              >,str_to_map(info,',',':')AS info
              >FROM test_liezhuanhang_02;
OK
name    class   province    score   info
小明  1   山东  ["99","86","100"]   {"身高":"177","体重":"60","年龄":"18"}
小王  2   北京  ["97","77","80"]    {"身高":"180","体重":"70","年龄":"17"}
小赵  2   广东  ["77","89","90"]    {"身高":"170","体重":"50","年龄":"17"}
小明  1   山东  ["80","76","79"]    {"身高":"185","体重":"72","年龄":"18"}

接下来，我们就可以用上一节中的LATERAL VIEW语法，配合explode函数进行列转行啦。

三、行转列

行转列分为两种，一种是将某列中多行的数据合并为一行，另一种是将某列中的值转化为列名，我们分别来看。

3.1 将某列中多行的数据合并为一行

数据集如下：

hive (article)>SELECT*FROM test_hangzhuanlie_01 AS p; 
OK
p.name  p.province  p.score
小明  山东  99
小明  山东  90
小明  江苏  97
小明  江苏  88
小王  山东  98
小王  山东  95
小王  江苏  89
小王  江苏  83
小王  山西  87
小王  山西  86
小赵  山东  77
小赵  山东  79
小赵  江苏  92
小赵  江苏  77
小赵  山西  69
小赵  山西  79

我们想要把名字和省份相同的score值合并到一起，用逗号分隔。应该怎么做呢？可以使用concat_ws函数配合collect_list函数，代码如下：

hive (article)>SELECT name
              >,province
              >,concat_ws(',', collect_list(score))AS score
              >FROM test_hangzhuanlie_01
              >GROUPBY name, province;
Total MapReduce CPU Time Spent: 6 seconds 430 msec
OK
name    province    score
小明  山东  99,90
小明  江苏  97,88
小王  山东  98,95
小王  山西  87,86
小王  江苏  89,83
小赵  山东  77,79
小赵  山西  69,79
小赵  江苏  92,77

如果是在Mysql中，我们可以直接使用group_concat函数代替上面两个函数的组合。如果是在Oracle中，我们可以用wm_concat。

3.2 将某列中的值转化为列名

数据集如下：

hive (article)>SELECT*FROM test_hangzhuanlie_02 AS p; 
OK
p.name  p.province  p.score
小明  山东  99,90
小明  江苏  97,88
小王  山东  98,95
小王  山西  87,86
小王  江苏  89,83
小赵  山东  77,79
小赵  山西  69,79
小赵  江苏  92,77

没错，这里偷个懒，直接拿上一节的结果数据作为这一节的数据集。
这次我们想将省份名作为列值，即第一列为这个同学在山东得到的分数，第二列为这个同学在江苏得到的分数，等等。实现方式如下：

hive (article)>SELECT  name
              >,max(CASEWHEN province='山东'THEN score ELSENULLEND)AS shandong_score
              >,max(CASEWHEN province='江苏'THEN score ELSENULLEND)AS jiangsu_score
              >,max(CASEWHEN province='山西'THEN score ELSENULLEND)AS shanxi_score
              >FROM test_hangzhuanlie_02
              >GROUPBY  name;
Total MapReduce CPU Time Spent: 5 seconds 230 msec
OK
name    shandong_score  jiangsu_score   shanxi_score
小明  99,9097,88NULL
小王  98,9589,8387,86
小赵  77,7992,7769,79Time taken: 17.584 seconds, Fetched: 3row(s)

以上，结束！

标签： hive 数据仓库
本文转载自: https://blog.csdn.net/zhr2415658919/article/details/134811896
版权归原作者 耗子背刀PK猫 所有， 如有侵权，请联系我们删除。
登录后发布评论

“Hive中的行转列和列转行”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

在sql中，使用时间范围来筛选数据

大数据新视界 -- Hive 数据仓库：架构深度剖析与核心组件详解（上）（1 / 30）

CertiK安全调研报告：Web3.0桌面钱包的初步安全评估

CentOS 7 系统中安装与配置 Telnet 服务详解（使用非root用户登录）

【Java知识】Java性能测试工具JMeter

Scenarigo：端到端的HTTP/gRPC场景测试工具

IDEA中通义灵码的使用技巧

文章导航