Hive实战技巧：数组转字符串与列转行、行转列详解（含教学视频）

作者：何博弘朗 | 来源：互联网 | 2024-10-23 16:31

1行转列1.1函数CONCAT(stringAcol,stringBcol…)：返回输入字符串连接后的结果，支持任意个输入字符串;CONCAT_WS(s

1 行转列

1.1 函数

CONCAT(string A/col, string B/col…)&＃xff1a;返回输入字符串连接后的结果&＃xff0c;支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,...)&＃xff1a;它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL&＃xff0c;返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

COLLECT_SET(col)&＃xff1a;函数只接受基本数据类型&＃xff0c;它的主要作用是将某字段的值进行去重汇总&＃xff0c;产生array类型字段。将某列数据转换成数组

1.1.1 concat字符串的拼接

0: jdbc:hive2://linux01:10000> desc fromatted concat ;

FAILED: SemanticException [Error 10001]: Table not found fromatted

Error: Error while compiling statement: FAILED: SemanticException [Error 10001]: Table not found fromatted (state&＃61;42S02,code&＃61;10001)

0: jdbc:hive2://linux01:10000> desc formatted concat ;

FAILED: SemanticException [Error 10001]: Table not found concat

Error: Error while compiling statement: FAILED: SemanticException [Error 10001]: Table not found concat (state&＃61;42S02,code&＃61;10001)

0: jdbc:hive2://linux01:10000> desc function concat ;

&＃43;----------------------------------------------------&＃43;

| tab_name |

&＃43;----------------------------------------------------&＃43;

| concat(str1, str2, ... strN) - returns the concatenation of str1, str2, ... strN or concat(bin1, bin2, ... binN) - returns the concatenation of bytes in binary data bin1, bin2, ... binN |

&＃43;----------------------------------------------------&＃43;

示例实现字符串的拼接

0: jdbc:hive2://linux01:10000> select concat("a" , "-->","b","-->","c")

. . . . . . . . . . . . . . .> ;

&＃43;------------&＃43;

| _c0 |

&＃43;------------&＃43;

| a-->b-->c |

&＃43;------------&＃43;

concat的执行时机时行数据将查询的表中的一行中的多个字段拼接

0: jdbc:hive2://linux01:10000> select concat(ename,":",job,":",sal) from tb_emp;

&＃43;--------------------------&＃43;

| _c0 |

&＃43;--------------------------&＃43;

| SMITH:CLERK:800.0 |

| ALLEN:SALESMAN:1600.0 |

| WARD:SALESMAN:1250.0 |

| JONES:MANAGER:2975.0 |

| MARTIN:SALESMAN:1250.0 |

| BLAKE:MANAGER:2850.0 |

| CLARK:MANAGER:2450.0 |

| SCOTT:ANALYST:3000.0 |

| KING:PRESIDENT:5000.0 |

| TURNER:SALESMAN:1500.0 |

| ADAMS:CLERK:1100.0 |

| JAMES:CLERK:950.0 |

| FORD:ANALYST:3000.0 |

| MILLER:CLERK:1300.0 |

| HUGUANYU:HANGGE:18000.0 |

&＃43;--------------------------&＃43;

1.1.2 CONCAT_WS(separator, str1, str2,...)

concat_ws 比 concat 可以自定字段的分隔符

concat_ws (参数一(分隔符) , str1 , str2....)

concat_ws (参数一(分隔符) , 数组)

示例

0: jdbc:hive2://linux01:10000> select concat_ws("_" , "tom","cat" ,"jim" ,"jerry") ;

&＃43;--------------------&＃43;

| _c0 |

&＃43;--------------------&＃43;

| tom_cat_jim_jerry |

&＃43;--------------------&＃43;

0: jdbc:hive2://linux01:10000> select concat_ws(":" , ename ,job , sal) from tb_emp ;

FAILED: SemanticException [Error 10016]: Line 1:36 Argument type mismatch &＃39;sal&＃39;: Argument 4 of function CONCAT_WS must be "string or array", but "double" was found.

Error: Error while compiling statement: FAILED: SemanticException [Error 10016]: Line 1:36 Argument type mismatch &＃39;sal&＃39;: Argument 4 of function CONCAT_WS must be "string or array", but "double" was found. (state&＃61;42000,code&＃61;10016)

需要类型转换将double转换成string 语法

cast(变量 AS 数据类型) 强制类型转换

cast(sal as string)

select concat_ws(":" , ename ,job , cast(sal as string)) from tb_emp ;

&＃43;--------------------------&＃43;

| _c0 |

&＃43;--------------------------&＃43;

| SMITH:CLERK:800.0 |

| ALLEN:SALESMAN:1600.0 |

| WARD:SALESMAN:1250.0 |

| JONES:MANAGER:2975.0 |

| MARTIN:SALESMAN:1250.0 |

| BLAKE:MANAGER:2850.0 |

| CLARK:MANAGER:2450.0 |

| SCOTT:ANALYST:3000.0 |

| KING:PRESIDENT:5000.0 |

| TURNER:SALESMAN:1500.0 |

| ADAMS:CLERK:1100.0 |

| JAMES:CLERK:950.0 |

| FORD:ANALYST:3000.0 |

| MILLER:CLERK:1300.0 |

| HUGUANYU:HANGGE:18000.0 |

&＃43;--------------------------&＃43;

1.1.3 COLLECT_SET(col) 将内容收集成set集合

desc function collect_set ;

&＃43;----------------------------------------------------&＃43;

| tab_name |

&＃43;----------------------------------------------------&＃43;

| collect_set(x) - Returns a set of objects with duplicate elements eliminated |

&＃43;----------------------------------------------------&＃43;

对表中的某个字段列操作

select deptno from tb_emp ;

&＃43;---------&＃43;

| deptno |

&＃43;---------&＃43;

| 20 |

| 30 |

| 20 |

| 30 |

| 10 |

| 20 |

| 10 |

| 30 |

| 20 |

| 30 |

| 20 |

| 10 |

| 50 |

&＃43;---------&＃43;

select conllect_set(deptno) from tb_emp ;--->去重重复元素的数组

&＃43;----------------&＃43;

| _c0 |

&＃43;----------------&＃43;

| [20,30,10,50] |

&＃43;----------------&＃43;

collect_list(col) 不会去重数据

select collect_list(deptno) as deptno_list from tb_emp ;

&＃43;-------------------------------------------------&＃43;

| deptno_list |

&＃43;-------------------------------------------------&＃43;

| [20,30,30,20,30,30,10,20,10,30,20,30,20,10,50] |

&＃43;-------------------------------------------------&＃43;

1.2 行转列

结果如下&＃xff1a;

射手座,A 娜娜|凤姐

白羊座,A 孙悟空|猪八戒

白羊座,B 宋宋

数据

孙悟空白羊座 A

娜娜射手座 A

宋宋白羊座 B

猪八戒白羊座 A

凤姐射手座 A

1.2.1 建表导入数据

create table if not exists tb_star(

name string ,

star string ,

dname string

)

row format delimited fields terminated by "\t" ;

load data local inpath "/hive/data/star.txt" into table tb_star ;

0: jdbc:hive2://linux01:10000> select * from tb_star ;

&＃43;---------------&＃43;---------------&＃43;----------------&＃43;

| tb_star.name | tb_star.star | tb_star.dname |

&＃43;---------------&＃43;---------------&＃43;----------------&＃43;

| 孙悟空 | 白羊座 | A |

| 娜娜 | 射手座 | A |

| 宋宋 | 白羊座 | B |

| 猪八戒 | 白羊座 | A |

| 凤姐 | 射手座 | A |

&＃43;---------------&＃43;---------------&＃43;----------------&＃43;

1.2.2 代码实现方式一

1) 先将星座和部门拼接 concat_ws

select

concat_ws("," , star , dname) as star_and_dname ,

name

from

tb_star ;

&＃43;-----------------&＃43;-------&＃43;

| star_and_dname | name |

&＃43;-----------------&＃43;-------&＃43;

| 白羊座,A | 孙悟空 |

| 射手座,A | 娜娜 |

| 白羊座,B | 宋宋 |

| 白羊座,A | 猪八戒 |

| 射手座,A | 凤姐 |

&＃43;-----------------&＃43;-------&＃43;

2) 分组收集姓名

with t1 as

(select

concat_ws("," , star , dname) as star_and_dname ,

name

from

tb_star)

select

star_and_dname,

collect_set(name)

from

group by star_and_dname ;

&＃43;-----------------&＃43;----------------&＃43;

| star_and_dname | _c1 |

&＃43;-----------------&＃43;----------------&＃43;

| 射手座,A | ["娜娜","凤姐"] |

| 白羊座,A | ["孙悟空","猪八戒"] |

| 白羊座,B | ["宋宋"] |

&＃43;-----------------&＃43;----------------&＃43;

3) 获取结果

select

star_and_dname ,

concat_ws("|" ,name_arr )

from

(select

star_and_dname,

collect_set(name) as name_arr

from

(select

concat_ws("," , star , dname) as star_and_dname ,

name

from

tb_star) t

group by star_and_dname) t2 ;

&＃43;-----------------&＃43;----------&＃43;

| star_and_dname | _c1 |

&＃43;-----------------&＃43;----------&＃43;

| 射手座,A | 娜娜|凤姐 |

| 白羊座,A | 孙悟空|猪八戒 |

| 白羊座,B | 宋宋 |

&＃43;-----------------&＃43;----------&＃43;

1.2.3 代码实现方式二

select

concat(star , "," , dname) ,

concat_ws("|" , collect_set(name))

from

tb_star

group by star , dname

;

&＃43;--------&＃43;----------&＃43;

| _c0 | _c1 |

&＃43;--------&＃43;----------&＃43;

| 射手座,A | 娜娜|凤姐 |

| 白羊座,A | 孙悟空|猪八戒 |

| 白羊座,B | 宋宋 |

&＃43;--------&＃43;----------&＃43;

2 列转行

2.1 关键函数

split(str , 分隔符) 返回一个数组

0: jdbc:hive2://linux01:10000> select split("hello,jim,yonggge,tom,cat" , ",") ;

&＃43;----------------------------------------&＃43;

| _c0 |

&＃43;----------------------------------------&＃43;

| ["hello","jim","yonggge","tom","cat"] |

&＃43;----------------------------------------&＃43;

explode() 炸裂函数将数组中的每个元素显示在每行中

0: jdbc:hive2://linux01:10000> select explode (split("hello,jim,yonggge,tom,cat" , ",")) ;

&＃43;----------&＃43;

| col |

&＃43;----------&＃43;

| hello |

| jim |

| yonggge |

| tom |

| cat |

&＃43;----------&＃43;

lateral view 侧窗口函数

2.2 示例

2.2.1 数据和需求

数据

《疑犯追踪》悬疑,动作,科幻,剧情

《Lie to me》悬疑,警匪,动作,心理,剧情

《战狼2》战争,动作,灾难

需求获取如下结构

《疑犯追踪》悬疑

《疑犯追踪》动作

《疑犯追踪》科幻

《疑犯追踪》剧情

《Lie to me》悬疑

《Lie to me》警匪

《Lie to me》动作

《Lie to me》心理

《Lie to me》剧情

《战狼2》战争

《战狼2》动作

《战狼2》灾难

2.2.2 建表导入数据

create table tb_movie(

name string ,

types string

)

row format delimited fields terminated by "\t" ;

load data local inpath "/hive/data/movie.txt" into table tb_movie ;

&＃43;----------------&＃43;-----------------&＃43;

| tb_movie.name | tb_movie.types |

&＃43;----------------&＃43;-----------------&＃43;

| 《疑犯追踪》 | 悬疑,动作,科幻,剧情 |

| 《Lie to me》 | 悬疑,警匪,动作,心理,剧情 |

| 《战狼2》 | 战争,动作,灾难 |

&＃43;----------------&＃43;-----------------&＃43;

2.2.3 实现

1 将类型转换成数组

select

split(types , ",") types_arr

from

tb_movie ;

2 炸裂

select

explode(split(types , ",")) f_type

from

tb_movie ;

&＃43;---------&＃43;

| f_type |

&＃43;---------&＃43;

| 悬疑 |

| 动作 |

| 科幻 |

| 剧情 |

| 悬疑 |

| 警匪 |

| 动作 |

| 心理 |

| 剧情 |

| 战争 |

| 动作 |

| 灾难 |

&＃43;---------&＃43;

3 使用侧窗口函数拼接

select

name ,

f_type

from

tb_movie

lateral view

explode(split(types ,",")) t as f_type ;

&＃43;--------------&＃43;---------&＃43;

| name | f_type |

&＃43;--------------&＃43;---------&＃43;

| 《疑犯追踪》 | 悬疑 |

| 《疑犯追踪》 | 动作 |

| 《疑犯追踪》 | 科幻 |

| 《疑犯追踪》 | 剧情 |

| 《Lie to me》 | 悬疑 |

| 《Lie to me》 | 警匪 |

| 《Lie to me》 | 动作 |

| 《Lie to me》 | 心理 |

| 《Lie to me》 | 剧情 |

| 《战狼2》 | 战争 |

| 《战狼2》 | 动作 |

| 《战狼2》 | 灾难 |

&＃43;--------------&＃43;---------&＃43;

推荐阅读

filter
探索基础路径分析与优化策略

探索基础路径分析与优化策略 ... [详细]

蜡笔小新 2024-10-22 01:35:31
int
Understanding the Concept and Usage of Null in Java Programming

https:www.hollischuang.comarchives74 对于Java程序员来说，null是令人头痛的东西。时常会受到空指针异常（NPE ... [详细]

蜡笔小新 2024-10-22 12:45:48
int
深入解析 List.toArray() 方法的应用与实现

List.toArray()用法：1）java中的强制类型转换只能转换单个对象，所以不能使用这样的代码将 toArray返回的数组强转为String[]String[]strs( ... [详细]

蜡笔小新 2024-10-22 20:46:37
int
Java集合框架面试问题精编与解析

简介java集合类是java.util包中的重要内容。java集合框架包含了大量集合接口以及这些接口的实现类和操作他们的算法。java集合框架图主要提供的数据结构+List又称有序 ... [详细]

蜡笔小新 2024-10-22 18:22:37
int
优化后的标题：在Android中利用GridView实现动态视觉效果

项目中用到的一些动画，GridView的Item依次从屏幕外飞入到相应位置，附上相关代码： MainActivity.java packa ... [详细]

蜡笔小新 2024-10-22 17:30:58
int
深入解析 errno.h：C语言错误处理的关键头文件

errno.h--查看错误代码errno是调试程序的一个重要方法。当linucCapi函数发生异常时,一般会将errno变量(需includeerrno.h)赋一个整数值,不同的值 ... [详细]

蜡笔小新 2024-10-22 09:52:58
int
ArrayList/LinkedList/Vector源码分析

ArrayListpublicclassArrayList&amp;lt;E&amp;gt;extendsAbstractList& ... [详细]

蜡笔小新 2024-10-20 02:14:21
ip
js学习笔记JavaScript中DOM扩展的那些事

什么都不说，先上总结的图~ SelectorsAPI(选择符API)querySelector()方法接收一个css选择符，返回与该模式匹配的第一个元素，如果没有找到匹配的元素，返 ... [详细]

蜡笔小新 2024-10-19 17:59:06
int
关注、取消关注（二十一）

需求开发关注、取消关注功能。统计用户的关注数、粉丝数。关键若A关注了B，则A是B的Follower(粉丝)，B是A的Followee(目标)。关注的目标可以是用户、帖子、题目等，在 ... [详细]

蜡笔小新 2024-10-19 17:53:00
scala
PHP编程语言中的标量数据类型及其种类详解

PHP编程语言中的标量数据类型及其种类详解 ... [详细]

蜡笔小新 2024-10-22 14:06:25
int
errno in Linux

Debug时不时要用到，保存一份方便查看。[1~34]usrincludeasm-genericerrno-base.h#ifndef_ASM_GENERIC_ERRNO_BASE ... [详细]

蜡笔小新 2024-10-21 16:17:17
int
Java的Synchronized原理与Callable接口实例分析

这篇“Java的Synchronized原理与Callable接口实例分析”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具 ... [详细]

蜡笔小新 2024-10-21 15:27:02
int
JAVA中的email正则表达式

说到正则表达式,网上有很多的通用的表达式,可是事实上说来,一般人的都不愿意去拿来研究,就是拿来就直接用就行了.可是,事实上,可能有些时候,项目中或公司里的实际情况不一样,得要修改一 ... [详细]

蜡笔小新 2024-10-20 19:32:11
int
[python标准库]Re模块

本文主要讲述以下几个方面:　　1.元字符　　2.贪婪匹配　　3.实例1.元字符.匹配任意一个字符，除换行符^匹配以一个字符开头的字符串‘$’ ... [详细]

蜡笔小新 2024-10-20 18:51:45
filter
无所不知的BroadcastReceiver

**广播接收器是一种用于响应系统范围广播通知的组件。许多广播都是由系统发起的—例如，通知屏幕已关闭、电池电量不足或已拍摄照片的广播应用也可以发起广播—例如，通知其他应用某些数据已下载至设备，并且 ... [详细]

蜡笔小新 2024-10-19 17:22:53

何博弘朗

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章