行轉列聚合
一、開始之前
在進行操作之前，你需要知道以下的幾個函數：
1、CONCAT(string A/col, string B/col…)：
傳回輸入字元串連接配接後的結果，支援任意個輸入字元串。
2、CONCAT_WS(separator, str1, str2,...)：
它是一個特殊形式的 CONCAT()。第一個參數剩餘參數間的分隔符。分隔符可以是與剩餘參數一樣的字元串。如果分隔符是 NULL，傳回值也将為 NULL。這個函數會跳過分隔符參數後的任何 NULL 和空字元串。分隔符将被加到被連接配接的字元串之間。
3、COLLECT_SET(col)：
函數隻接受基本資料類型，它的主要作用是将某字段的值進行去重彙總，産生 array 類型字段。
二、準備資料
假設你擁有如下的使用者資料
| name | sex | age |
|---|---|---|
| Titan | 男 | 18 |
| Goodman | 男 | 18 |
| Cooper | 男 | 10 |
| Missy | 女 | 10 |
| Penny | 女 | 27 |
三、需求分析
把相同性别的同齡人歸類到一起，輸出如下的結果
| info | name |
|---|---|
| 女-10 | Missy |
| 女-27 | Penny |
| 男-10 | Cooper |
| 男-18 | Titan,Goodman |
四、建立Hive表并導入資料
1、建立user_info表，将資料導入到表中
CREATE table user_info(
name string,
sex string,
age string
)
row format delimited fields terminated by "\t";
load data local inpath './users.txt' into table user_info; 複制
2、根據需求，查詢資料，使用CONCAT拼接字段，用COLLECT_SET将多行轉為去重清單
SELECT
t1.info,
CONCAT_WS(',', COLLECT_SET(t1.name)) name
FROM
(
SELECT
name, CONCAT_WS('-', sex, age) info
FROM
user_info ) t1
GROUP BY
t1.info; 複制
最後，檢視輸出結果
| info | name |
|---|---|
| 女-10 | Missy |
| 女-27 | Penny |
| 男-10 | Cooper |
| 男-18 | Titan,Goodman |
列轉行拆分
一、開始之前
在進行操作之前，你需要知道以下的幾個函數：
1、EXPLODE(col)：
将 hive 一列中複雜的 array 或者 map 結構拆分成多行。
2、LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解釋：用于和 split, explode 等 UDTF 一起使用，它能夠将一列資料拆成多行資料，在此基礎上可以對拆分後的資料進行聚合。
二、準備資料
假設你擁有這樣的電影資料
| movie | category |
|---|---|
| 《疑犯追蹤》 | ["懸疑","動作","科幻","劇情"] |
| 《Lie to me》 | ["懸疑","警匪","動作","心理","劇情"] |
| 《戰狼 2》 | ["戰争","動作","災難"] |
三、需求分析
将每個電影的分類拆分出來，展開資料
| movie | category_name |
|---|---|
| 《疑犯追蹤》 | 懸疑 |
| 《疑犯追蹤》 | 動作 |
| 《疑犯追蹤》 | 科幻 |
| 《疑犯追蹤》 | 劇情 |
| 《Lie to me》 | 懸疑 |
| 《Lie to me》 | 警匪 |
| 《Lie to me》 | 動作 |
| 《Lie to me》 | 心理 |
| 《Lie to me》 | 劇情 |
| 《戰狼 2》 | 戰争 |
| 《戰狼 2》 | 動作 |
| 《戰狼 2》 | 災難 |
四、建立 movie_info 表并導入資料
create table movie_info(
movie string,
category array<string>
)
row format delimited fields terminated by "\t"
collection items terminated by ",";
hive (db_titan)> load data local inpath 'movies.txt' into table movie_info;
Loading data to table db_titan.movie_info
Table db_titan.movie_info stats: [numFiles=1, totalSize=135]
OK
Time taken: 0.556 seconds 複制
五、根據需求，将分類清單拆分，實作列轉行
select
movie,
category_name
from
movie_info lateral view explode(category) table_tmp as category_name; 複制
| movie | category_name |
|---|---|
| 《疑犯追蹤》 | 懸疑 |
| 《疑犯追蹤》 | 動作 |
| 《疑犯追蹤》 | 科幻 |
| 《疑犯追蹤》 | 劇情 |
| 《Lie to me》 | 懸疑 |
| 《Lie to me》 | 警匪 |
| 《Lie to me》 | 動作 |
| 《Lie to me》 | 心理 |
| 《Lie to me》 | 劇情 |
| 《戰狼 2》 | 戰争 |
| 《戰狼 2》 | 動作 |
| 《戰狼 2》 | 災難 |