Hive-行转列，列转行，相关函数

缺乏、安全感 2023-03-04 13:28 13阅读 0赞

# 行转列，列转行 #

### 文章目录 ###

*  行转列，列转行
 *   *  行转列
     *  列转行

## 行转列 ##

**1）函数说明(可以是一行转一列，多行转一列)**

`CONCAT(string A, string B...)`：返回输入字符串连接后的结果，支持任意个输入字符串;

`CONCAT_WS(separator, str1, str2,...)`：它是一个特殊形式的 `concat()`。第一个参数是其余参数，的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。

*  注意：CONCAT\_WS must be “string or array<string>”
 *  COLLECT\_SET(col)：只接受基本数据类型，主要作用是将某字段的值进行去重汇总，产生array类型字段。注：一般需要配合group by一起使用，聚合非分组字段
 *  COLLECT\_LIST(col)：只接受基本数据类型，主要作用是将某字段的值进行不去重汇总，产生array类型字段。

**2）数据准备**

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>constellation</th> 
   <th>blood_type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>关胜</td> 
   <td>白羊座</td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>林冲</td> 
   <td>射手座</td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>宋江</td> 
   <td>白羊座</td> 
   <td>B</td> 
  </tr> 
  <tr> 
   <td>卢俊义</td> 
   <td>白羊座</td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>公孙胜</td> 
   <td>射手座</td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>柴进</td> 
   <td>白羊座</td> 
   <td>B</td> 
  </tr> 
 </tbody> 
</table>

**3）需求**

把星座和血型一样的人归类到一起。结果如下：

射手座,A            林冲|公孙胜
    白羊座,A            关胜|卢俊义
    白羊座,B            宋江|柴进

**4）创建本地constellation.txt，导入数据**

[qinjl@hadoop102 datas]$ vi constellation.txt
    关胜	白羊座	A
    林冲	射手座	A
    宋江	白羊座	B
    卢俊义	白羊座	A
    公孙胜	射手座	A
    柴进	白羊座	B

**5）创建hive表并导入数据**

create table person_info(
    name string, 
    constellation string, 
    blood_type string
    ) 
    row format delimited fields terminated by "\t";
    
    load data local inpath "/opt/module/hive/datas/constellation.txt" into table person_info;

**6）按需求查询数据**

SELECT t1.c_b , CONCAT_WS("|",collect_set(t1.name))
    FROM (
        SELECT `name`,CONCAT_WS(',',constellation,blood_type) c_b
        FROM person_info
    ) t1 
    GROUP BY t1.c_b

## 列转行 ##

**1）函数说明**

`SPLIT(str, separator)`：将字符串按照后面的分隔符切割，转换成字符array。

`EXPLODE(col)`：将hive一列中复杂的array或者map结构拆分成多行。

`LATERAL VIEW`:（一进多出）

*  用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。

*  注意：一个FROM语句后可以跟多个lateral view语句，后面的lateral view语句能够引用它前面的所有表和列名

Array<int> col1		Array<string> col2
    [1, 2]				[a", "b", "c"]
    [3, 4]				[d", "e", "f"]
    
    SELECT myCol1, myCol2 FROM baseTable
    LATERAL VIEW explode(col1) myTable1 AS myCol1
    LATERAL VIEW explode(col2) myTable2 AS myCol2;
    
    结果：
    myCol1		myCol2
    1			"a"
    1			"b"
    1			"c"
    2			"a"
    2			"b"
    2			"c"
    3			"d"
    3			"e"
    3			"f"
    4			"d"
    4			"e"
    4			"f"

**2）数据准备**

<table> 
 <thead> 
  <tr> 
   <th>movie</th> 
   <th>category</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>《疑犯追踪》</td> 
   <td>悬疑,动作,科幻,剧情</td> 
  </tr> 
  <tr> 
   <td>《Lie to me》</td> 
   <td>悬疑,警匪,动作,心理,剧情</td> 
  </tr> 
  <tr> 
   <td>《战狼2》</td> 
   <td>战争,动作,灾难</td> 
  </tr> 
 </tbody> 
</table>

**3）需求**

将电影分类中的数组数据展开。结果如下：

《疑犯追踪》      悬疑
    《疑犯追踪》      动作
    《疑犯追踪》      科幻
    《疑犯追踪》      剧情
    《Lie to me》   悬疑
    《Lie to me》   警匪
    《Lie to me》   动作
    《Lie to me》   心理
    《Lie to me》   剧情
    《战狼2》        战争
    《战狼2》        动作
    《战狼2》        灾难

**4）创建本地movie.txt，导入数据**

[qinjl@hadoop102 datas]$ vi movie_info.txt
    《疑犯追踪》	悬疑,动作,科幻,剧情
    《Lie to me》	悬疑,警匪,动作,心理,剧情
    《战狼2》	战争,动作,灾难

**5）创建hive表并导入数据**

create table movie_info(
        movie string, 
        category string) 
    row format delimited fields terminated by "\t";
    
    load data local inpath "/opt/module/hive/datas/movie_info.txt" into table movie_info;

**6）按需求查询数据**

SELECT movie,category_name 
    FROM movie_info 
    lateral VIEW explode(split(category,",")) movie_info_tmp  AS category_name;