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: 4 row(s)

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

hive (article)> SELECT  explode(score) AS score
              > FROM test_liezhuanhang_01;
OK
score
99
86
100
97
77
80
77
89
90
80
76
79
Time taken: 0.291 seconds, Fetched: 12 row(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
年龄  18
Time taken: 0.07 seconds, Fetched: 12 row(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}  79
Time taken: 0.451 seconds, Fetched: 12 row(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  年龄  18
Time taken: 0.04 seconds, Fetched: 36 row(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,年龄:18
Time taken: 0.073 seconds, Fetched: 4 row(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
              > GROUP BY  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(CASE WHEN province='山东' THEN score ELSE NULL END) AS shandong_score
              >        ,max(CASE WHEN province='江苏' THEN score ELSE NULL END) AS jiangsu_score
              >        ,max(CASE WHEN province='山西' THEN score ELSE NULL END) AS shanxi_score
              > FROM test_hangzhuanlie_02
              > GROUP BY  name;
Total MapReduce CPU Time Spent: 5 seconds 230 msec
OK
name    shandong_score  jiangsu_score   shanxi_score
小明  99,90   97,88   NULL
小王  98,95   89,83   87,86
小赵  77,79   92,77   69,79
Time taken: 17.584 seconds, Fetched: 3 row(s)

搞定收工！

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 224,383评论 6赞 522
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 96,028评论 3赞 402
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 171,486评论 0赞 366
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 60,786评论 1赞 300
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 69,793评论 6赞 399
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 53,297评论 1赞 314
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 41,685评论 3赞 428
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 40,655评论 0赞 279
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 47,192评论 1赞 324
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 39,223评论 3赞 345
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 41,343评论 1赞 354
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 36,966评论 5赞 350
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 42,656评论 3赞 336
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 33,130评论 0赞 25
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 34,257评论 1赞 275
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 49,869评论 3赞 381
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 46,407评论 2赞 365

推荐阅读更多精彩内容

  • Hive基础
    一、Hive基本概念 Hive 是基于 Hadoop 的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提...
    CJ21阅读 1,745评论 0赞 13
  • Hive的常用函数
    1、系统内置函数1．查看系统自带的函数hive> show functions;2．显示自带的函数的用法hive>...
    我还不够强阅读 846评论 0赞 0
  • Hive学习二
    六、hive 的函数 查看有多少函数 show functions; 查看函数 substr 的详细信息 desc...
    大石兄阅读 785评论 0赞 3
  • HQL操作之DQL命令
    题记 本文部分资料来源于拉钩大数据高薪训练营 select 语法： SQL语句书写注意事项： SQL语句对大小写不...
    一拳超疼阅读 1,120评论 0赞 0
  • 搭建Hive3.x并整合MySQL8.x存储元数据
    Hive 简介 Hive 官方文档： https://cwiki.apache.org/confluence/di...
    端碗吹水阅读 1,819评论 0赞 0