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;

推荐阅读

get
深入解析 Bootstrap Table 的使用技巧

本文详细介绍了如何利用 Bootstrap Table 实现数据展示与操作，包括数据加载、表格配置及前后端交互等关键步骤。 ... [详细]

蜡笔小新 2024-11-20 17:21:26
ip
Qt中信号与槽机制对比传统回调函数的优势

在Qt框架中，信号与槽机制是一种独特的组件间通信方式。本文探讨了这一机制相较于传统的C风格回调函数所具有的优势，并分析了其潜在的不足之处。 ... [详细]

蜡笔小新 2024-11-20 10:48:37
get
如何使用 org.apache.tinkerpop.gremlin.structure.VertexProperty 的 key 方法

本文详细介绍了 `org.apache.tinkerpop.gremlin.structure.VertexProperty` 类中的 `key()` 方法，并提供了多个实际应用的代码示例。通过这些示例，读者可以更好地理解该方法在图数据库操作中的具体用途。 ... [详细]

蜡笔小新 2024-11-21 17:38:10
get
IC卡操作功能实现

本文介绍了如何通过C#语言调用动态链接库（DLL）中的函数来实现IC卡的基本操作，包括初始化设备、设置密码模式、获取设备状态等，并详细展示了将TextBox中的数据写入IC卡的具体实现方法。 ... [详细]

蜡笔小新 2024-11-21 11:02:19
get
深入理解Java SE 8新特性：Lambda表达式与函数式编程

本文作为‘Java SE 8新特性概览’系列的一部分，将详细探讨Lambda表达式。通过多种示例，我们将展示Lambda表达式的不同应用场景，并解释编译器如何处理这些表达式。 ... [详细]

蜡笔小新 2024-11-20 14:19:27
char
Oracle 11g 创建表空间与基础配置

本文详细介绍了Oracle 11g中的创建表空间的方法，以及如何设置客户端和服务端的基本配置，包括用户管理、环境变量配置等。 ... [详细]

蜡笔小新 2024-11-21 18:54:39
get
ABAP开发者需关注的几大关键问题

长期从事ABAP开发工作的专业人士，在面对行业新趋势时，往往需要重新审视自己的发展方向。本文探讨了几位资深专家对ABAP未来走向的看法，以及开发者应如何调整技能以适应新的技术环境。 ... [详细]

蜡笔小新 2024-11-21 18:21:06
get
深入解析JQuery Mobile特有的事件与方法

本文详细介绍了JQuery Mobile框架中特有的事件和方法，帮助开发者更好地理解和应用这些特性，提升移动Web开发的效率。 ... [详细]

蜡笔小新 2024-11-21 14:24:21
get
spring boot使用jetty无法启动

spring boot使用jetty无法启动 ... [详细]

蜡笔小新 2024-11-21 10:15:52
instance
入门指南：使用FastRPC技术连接Qualcomm Hexagon DSP

本文旨在为初学者提供关于如何使用FastRPC技术连接Qualcomm Hexagon DSP的基础知识。FastRPC技术允许开发者在本地客户端实现远程调用，从而简化Hexagon DSP的开发和调试过程。 ... [详细]

蜡笔小新 2024-11-21 10:03:34
get
理解浏览器历史记录（2）hashchange、pushState

阅读目录1.hashchange2.pushState本文也是一篇基础文章。继上文之后，本打算去研究pushState，偶然在一些信息中发现了锚点变 ... [详细]

蜡笔小新 2024-11-20 20:05:37
get
深入解析SpringMVC中的HandlerMapping机制

本文将从基础概念入手，详细探讨SpringMVC框架中DispatcherServlet如何通过HandlerMapping进行请求分发，以及其背后的源码实现细节。 ... [详细]

蜡笔小新 2024-11-20 19:24:42
get
[OReilly_Learning_Perl_5th_Edition]_Chap06_Exercises

3.[15]Writeaprogramtolistallofthekeysandvaluesin%ENV.PrinttheresultsintwocolumnsinASCIIbet ... [详细]

蜡笔小新 2024-11-20 18:28:56
ip
解决Ajax POST请求数据映射问题

本文介绍了如何正确配置Ajax POST请求，以确保前端发送的数据能够被后端正确解析。重点在于前端JSON对象的键名需要与后端实体类的字段名严格匹配。 ... [详细]

蜡笔小新 2024-11-20 16:56:50
get
循环双链表中指定位置的元素插入方法

本文详细介绍了如何在循环双链表的指定位置插入新元素的方法，包括必要的步骤和代码示例。 ... [详细]

蜡笔小新 2024-11-20 06:48:26

何博弘朗

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

Tags | 热门标签

RankList | 热门文章