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;

推荐阅读

list
XNA 3.0 游戏编程：从 XML 文件加载数据

本文介绍如何在 XNA 3.0 游戏项目中从 XML 文件加载数据。我们将探讨如何将 XML 数据序列化为二进制文件，并通过内容管道加载到游戏中。此外，还会涉及自定义类型读取器和写入器的实现。 ... [详细]

蜡笔小新 2024-12-27 11:39:44
list
深入理解Java泛型：JDK 5的新特性

本文详细介绍了Java泛型的概念及其在JDK 5中的应用，通过具体代码示例解释了泛型的引入、作用和优势。同时，探讨了泛型类、泛型方法和泛型接口的实现，并深入讲解了通配符的使用。 ... [详细]

蜡笔小新 2024-12-26 11:15:56
tree
新浪笔试题

1:有如下一段程序：packagea.b.c;publicclassTest{privatestaticinti0;publicintgetNext(){return ... [详细]

蜡笔小新 2024-12-27 19:32:17
char
UNP 第9章：主机名与地址转换

本章探讨了用于在主机名和数值地址之间进行转换的函数，如gethostbyname和gethostbyaddr。此外，还介绍了getservbyname和getservbyport函数，用于在服务器名和端口号之间进行转换。 ... [详细]

蜡笔小新 2024-12-27 11:26:39
list
Unity 客户端框架设计：UI管理系统的构建

本文详细介绍了如何构建一个高效的UI管理系统，集中处理UI页面的打开、关闭、层级管理和页面跳转等问题。通过UIManager统一管理外部切换逻辑，实现功能逻辑分散化和代码复用，支持多人协作开发。 ... [详细]

蜡笔小新 2024-12-27 10:28:40
list
RecyclerView初步学习(一)

RecyclerView初步学习(一)ReCyclerView提供了一种插件式的编程模式，除了提供ViewHolder缓存模式，还可以自定义动画，分割符，布局样式，相比于传统的ListVi ... [详细]

蜡笔小新 2024-12-26 20:24:01
char
2023年京东Android面试真题解析与经验分享

本文由一位拥有6年Android开发经验的工程师撰写，详细解析了京东面试中常见的技术问题。涵盖引用传递、Handler机制、ListView优化、多线程控制及ANR处理等核心知识点。 ... [详细]

蜡笔小新 2024-12-26 17:45:48
random
从 .NET 转 Java 的自学之路：IO 流基础篇

本文详细介绍了 Java 中的 IO 流，包括字节流和字符流的基本概念及其操作方式。探讨了如何处理不同类型的文件数据，并结合编码机制确保字符数据的正确读写。同时，文中还涵盖了装饰设计模式的应用，以及多种常见的 IO 操作实例。 ... [详细]

蜡笔小新 2024-12-26 17:37:25
config
Scala 实现 UTF-8 编码属性文件读取与克隆

本文介绍如何使用 Scala 以 UTF-8 编码方式读取属性文件，并实现属性文件的克隆功能。通过这种方式，可以确保配置文件在多线程环境下的一致性和高效性。 ... [详细]

蜡笔小新 2024-12-26 08:25:19
list
POJ 3259 Bellman-Ford算法实现

本文提供了使用Java实现Bellman-Ford算法解决POJ 3259问题的代码示例，详细解释了如何通过该算法检测负权环来判断时间旅行的可能性。 ... [详细]

蜡笔小新 2024-12-25 20:03:22
list
CUGB图论专题：排水系统中的最大流问题 - EK与Dinic算法解析

本题探讨如何通过最大流算法解决农场排水系统的设计问题。题目要求计算从水源点到汇合点的最大水流速率，使用经典的EK（Edmonds-Karp）和Dinic算法进行求解。 ... [详细]

蜡笔小新 2024-12-25 17:47:23
list
VSCode 自定义代码片段配置：实现类似IDEA的快捷代码段（如sout或psvm）

本文详细介绍如何在VSCode中配置自定义代码片段，使其具备与IDEA相似的代码生成快捷键功能。通过具体的Java和HTML代码片段示例，展示配置步骤及效果。 ... [详细]

蜡笔小新 2024-12-25 17:10:13
list
dotnet 通过 Elmish.WPF 使用 F# 编写 WPF 应用

本文来安利大家一个有趣而且强大的库，通过F#和C#混合编程编写WPF应用，可以在WPF中使用到F#强大的数据处理能力在GitHub上完全开源Elmis ... [详细]

蜡笔小新 2024-12-25 16:06:42
python
Python编程进阶：高阶函数与Lambda表达式详解

本文深入探讨了Python中的高阶函数和Lambda表达式的使用方法，结合实际案例解析其应用场景，帮助开发者更好地理解和运用这些强大的工具。 ... [详细]

蜡笔小新 2024-12-25 11:58:39
range
深入理解Redis的数据结构与对象系统

本文详细探讨了Redis中的数据结构和对象系统的实现，包括字符串、列表、集合、哈希表和有序集合等五种核心对象类型，以及它们所使用的底层数据结构。通过分析源码和相关文献，帮助读者更好地理解Redis的设计原理。 ... [详细]

蜡笔小新 2024-12-25 04:11:22

何博弘朗

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

Tags | 热门标签

RankList | 热门文章