Hive-特殊数据类型(ARRAY+MAP+STRUCT)+行转列&列转行

最新推荐文章于 2025-05-12 09:00:24 发布
最新推荐文章于 2025-05-12 09:00:24 发布
阅读量1.8k 收藏 6
点赞数
分类专栏： Hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/nituoge/article/details/103612697
版权

1. DQL

1.1 join
  • inner join : 只返回两边连接条件匹配上的数据
  • outer join
    • left outer join 以左边表为基准
    • right outer join 以右边表为基准
    • full outer join (可以用于数据质量核查)

join 的 on 条件中支持AND多条件，不支持OR

1.2 复杂数据类型

对于复杂数据类型，需要掌握的是如何存(建表时定义，数据导入)和如何取(掌握常用的查询方法)

  • ARRAY<T> 泛型，数据类型要一样

数据内容如下：

[ruoze@rzdata001 data]$ cat hive_array.txt 
pk      beijing,shanghai,tianjin,hangzhou
jepson  changchu,chengdu,wuhan,beijing
[ruoze@rzdata001 data]$

简单使用：

0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> create table hive_array(name string, work_locations array<string>)
. . . . . . . . . . . . . . . . . . . . . . > row format delimited
. . . . . . . . . . . . . . . . . . . . . . > fields terminated by '\t'
. . . . . . . . . . . . . . . . . . . . . . > collection items terminated by ',';
No rows affected (0.13 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> load data local inpath '/home/ruoze/data/hive_array.txt' overwrite into table hive_array; 
No rows affected (0.357 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select * from hive_array limit 3;
+------------------+----------------------------------------------+--+
| hive_array.name  |          hive_array.work_locations           |
+------------------+----------------------------------------------+--+
| pk               | ["beijing","shanghai","tianjin","hangzhou"]  |
| jepson           | ["changchu","chengdu","wuhan","beijing"]     |
+------------------+----------------------------------------------+--+
2 rows selected (0.066 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
2 rows selected (0.066 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select name ,work_locations[0] 
. . . . . . . . . . . . . . . . . . . . . . > from hive_array limit 3;
+---------+-----------+--+
|  name   |    _c1    |
+---------+-----------+--+
| pk      | beijing   |
| jepson  | changchu  |
+---------+-----------+--+
2 rows selected (0.078 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select name ,size(work_locations) 
. . . . . . . . . . . . . . . . . . . . . . > from hive_array limit 3;
+---------+------+--+
|  name   | _c1  |
+---------+------+--+
| pk      | 4    |
| jepson  | 4    |
+---------+------+--+
2 rows selected (0.077 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select * from hive_array where array_contains(work_locations,'beijing') limit 5;
+------------------+----------------------------------------------+--+
| hive_array.name  |          hive_array.work_locations           |
+------------------+----------------------------------------------+--+
| pk               | ["beijing","shanghai","tianjin","hangzhou"]  |
| jepson           | ["changchu","chengdu","wuhan","beijing"]     |
+------------------+----------------------------------------------+--+
2 rows selected (0.236 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb>
  • MAP<primitive_type,data_type> k-v类型

数据内容如下：

[ruoze@rzdata001 data]$ cat hive_map.txt 
1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26
[ruoze@rzdata001 data]$

简单使用：

0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> create table hive_map(
. . . . . . . . . . . . . . . . . . . . . . > id int,
. . . . . . . . . . . . . . . . . . . . . . > name string,
. . . . . . . . . . . . . . . . . . . . . . > members map<string,string>,
. . . . . . . . . . . . . . . . . . . . . . > age int
. . . . . . . . . . . . . . . . . . . . . . > )
. . . . . . . . . . . . . . . . . . . . . . > row format delimited 
. . . . . . . . . . . . . . . . . . . . . . > fields terminated by ','
. . . . . . . . . . . . . . . . . . . . . . > collection items terminated by '#'
. . . . . . . . . . . . . . . . . . . . . . > map keys terminated by ':';
No rows affected (0.083 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> load data local inpath '/home/ruoze/data/hive_map.txt' overwrite into table hive_map;
No rows affected (0.222 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select * from hive_map limit 3;
+--------------+----------------+----------------------------------------------------+---------------+--+
| hive_map.id  | hive_map.name  |                  hive_map.members                  | hive_map.age  |
+--------------+----------------+----------------------------------------------------+---------------+--+
| 1            | zhangsan       | {"father":"xiaoming","mother":"xiaohuang","brother":"xiaoxu"} | 28            |
| 2            | lisi           | {"father":"mayun","mother":"huangyi","brother":"guanyu"} | 22            |
| 3            | wangwu         | {"father":"wangjianlin","mother":"ruhua","sister":"jingtian"} | 29            |
+--------------+----------------+----------------------------------------------------+---------------+--+
3 rows selected (0.073 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select id,name,members['father'] as father,members['mother'] as mother,age 
. . . . . . . . . . . . . . . . . . . . . . > from hive_map;
+-----+-----------+--------------+-------------+------+--+
| id  |   name    |    father    |   mother    | age  |
+-----+-----------+--------------+-------------+------+--+
| 1   | zhangsan  | xiaoming     | xiaohuang   | 28   |
| 2   | lisi      | mayun        | huangyi     | 22   |
| 3   | wangwu    | wangjianlin  | ruhua       | 29   |
| 4   | mayun     | mayongzhen   | angelababy  | 26   |
+-----+-----------+--------------+-------------+------+--+
4 rows selected (0.079 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select id,name,map_keys(members) as relation, age 
. . . . . . . . . . . . . . . . . . . . . . > from hive_map;
+-----+-----------+--------------------------------+------+--+
| id  |   name    |            relation            | age  |
+-----+-----------+--------------------------------+------+--+
| 1   | zhangsan  | ["father","mother","brother"]  | 28   |
| 2   | lisi      | ["father","mother","brother"]  | 22   |
| 3   | wangwu    | ["father","mother","sister"]   | 29   |
| 4   | mayun     | ["father","mother"]            | 26   |
+-----+-----------+--------------------------------+------+--+
4 rows selected (0.061 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select id,name,map_values(members) as names, age 
. . . . . . . . . . . . . . . . . . . . . . > from hive_map;
+-----+-----------+-------------------------------------+------+--+
| id  |   name    |                names                | age  |
+-----+-----------+-------------------------------------+------+--+
| 1   | zhangsan  | ["xiaoming","xiaohuang","xiaoxu"]   | 28   |
| 2   | lisi      | ["mayun","huangyi","guanyu"]        | 22   |
| 3   | wangwu    | ["wangjianlin","ruhua","jingtian"]  | 29   |
| 4   | mayun     | ["mayongzhen","angelababy"]         | 26   |
+-----+-----------+-------------------------------------+------+--+
4 rows selected (0.056 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select id,name,size(members) as msize 
. . . . . . . . . . . . . . . . . . . . . . > from hive_map;
+-----+-----------+--------+--+
| id  |   name    | msize  |
+-----+-----------+--------+--+
| 1   | zhangsan  | 3      |
| 2   | lisi      | 3      |
| 3   | wangwu    | 3      |
| 4   | mayun     | 2      |
+-----+-----------+--------+--+
4 rows selected (0.068 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb>  # 查询出有兄弟的人以及他的兄弟的名字    方式1
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select id,name,members['brother'] as brother  
. . . . . . . . . . . . . . . . . . . . . . > from hive_map  
. . . . . . . . . . . . . . . . . . . . . . > where array_contains(map_keys(members),'brother') ;
+-----+-----------+---------+--+
| id  |   name    | brother |
+-----+-----------+---------+--+
| 1   | zhangsan  | xiaoxu  |
| 2   | lisi      | guanyu  |
+-----+-----------+---------+--+
2 rows selected (0.07 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb>     
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb>  # 查询出有兄弟的人以及他的兄弟的名字(直接查)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select id,name,members['brother'] as brother  
. . . . . . . . . . . . . . . . . . . . . . > from hive_map  
. . . . . . . . . . . . . . . . . . . . . . > where members['brother'] is not null;
+-----+-----------+----------+--+
| id  |   name    | brother  |
+-----+-----------+----------+--+
| 1   | zhangsan  | xiaoxu   |
| 2   | lisi      | guanyu   |
+-----+-----------+----------+--+
2 rows selected (0.056 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> # 查询出有兄弟的人以及他的兄弟的名字(使用子查询实现)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select id,name,members['brother'] as brother
. . . . . . . . . . . . . . . . . . . . . . > from (
. . . . . . . . . . . . . . . . . . . . . . > select id,name,members from hive_map where members['brother'] is not null
. . . . . . . . . . . . . . . . . . . . . . > ) t;
+-----+-----------+----------+--+
| id  |   name    | brother  |
+-----+-----------+----------+--+
| 1   | zhangsan  | xiaoxu   |
| 2   | lisi      | guanyu   |
+-----+-----------+----------+--+
2 rows selected (0.049 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb>
  • STRUCT<col_name : data_type> 内部的数据类型可以不同
    数据内容如下：
[ruoze@rzdata001 data]$ cat hive_struct.txt 
192.168.1.1#zhangsan:40
192.168.1.2#lisi:50
192.168.1.3#wangwu:60
192.168.1.4#zhaoliu:70
[ruoze@rzdata001 data]$

简单使用：

0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> create table hive_struct(
. . . . . . . . . . . . . . . . . . . . . . > ip string,
. . . . . . . . . . . . . . . . . . . . . . > info struct<name:string,age:int>
. . . . . . . . . . . . . . . . . . . . . . > )
. . . . . . . . . . . . . . . . . . . . . . > row format delimited
. . . . . . . . . . . . . . . . . . . . . . > fields terminated by '#'
. . . . . . . . . . . . . . . . . . . . . . > collection items terminated by ':';
No rows affected (0.937 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> load data local inpath '/home/ruoze/data/hive_struct.txt' overwrite into table hive_struct;
No rows affected (0.778 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select * from hive_struct;
+-----------------+-------------------------------+--+
| hive_struct.ip  |       hive_struct.info        |
+-----------------+-------------------------------+--+
| 192.168.1.1     | {"name":"zhangsan","age":40}  |
| 192.168.1.2     | {"name":"lisi","age":50}      |
| 192.168.1.3     | {"name":"wangwu","age":60}    |
| 192.168.1.4     | {"name":"zhaoliu","age":70}   |
+-----------------+-------------------------------+--+
4 rows selected (0.246 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select ip, info.name, info.age 
. . . . . . . . . . . . . . . . . . . . . . > from hive_struct;
+--------------+-----------+------+--+
|      ip      |   name    | age  |
+--------------+-----------+------+--+
| 192.168.1.1  | zhangsan  | 40   |
| 192.168.1.2  | lisi      | 50   |
| 192.168.1.3  | wangwu    | 60   |
| 192.168.1.4  | zhaoliu   | 70   |
+--------------+-----------+------+--+
4 rows selected (0.13 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb>
1.3 查询过程中生成特殊类型

数据样例：

[ruoze@rzdata001 data]$ cat ad_list.txt 
ad_101  http://www.google.com   catalog8|catalog1
ad_102  http://www.sohu.com     catalog6|catalog3
ad_103  http://www.baidu.com    catalog7
ad_104  http://www.qq.com       catalog5|catalog1|catalog4|catalog9
ad_105  http://sina.com
[ruoze@rzdata001 data]$
[ruoze@rzdata001 data]$ cat click_log.txt 
11      ad_101  2014-05-01 06:01:12.334+01
22      ad_102  2014-05-01 07:28:12.342+01
33      ad_103  2014-05-01 07:50:12.33+01
11      ad_104  2014-05-01 09:27:12.33+01
22      ad_103  2014-05-01 09:03:12.324+01
33      ad_102  2014-05-02 19:10:12.343+01
11      ad_101  2014-05-02 09:07:12.344+01
35      ad_105  2014-05-03 11:07:12.339+01
22      ad_104  2014-05-03 12:59:12.743+01
77      ad_103  2014-05-03 18:04:12.355+01
99      ad_102  2014-05-04 00:36:39.713+01
33      ad_101  2014-05-04 19:10:12.343+01
11      ad_101  2014-05-05 09:07:12.344+01
35      ad_102  2014-05-05 11:07:12.339+01
22      ad_103  2014-05-05 12:59:12.743+01
77      ad_104  2014-05-05 18:04:12.355+01
99      ad_105  2014-05-05 20:36:39.713+01
[ruoze@rzdata001 data]$

建表，导入数据：

0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> create table ad_list(
. . . . . . . . . . . . . . . . . . . . . . > ad_id string,
. . . . . . . . . . . . . . . . . . . . . . > url string,
. . . . . . . . . . . . . . . . . . . . . . > catalogs string
. . . . . . . . . . . . . . . . . . . . . . > )
. . . . . . . . . . . . . . . . . . . . . . > row format delimited 
. . . . . . . . . . . . . . . . . . . . . . > fields terminated by '\t';
No rows affected (0.169 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> load data local inpath '/home/ruoze/data/ad_list.txt' overwrite into table ad_list;
No rows affected (0.135 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select * from ad_list;
+----------------+------------------------+--------------------------------------+--+
| ad_list.ad_id  |      ad_list.url       |           ad_list.catalogs           |
+----------------+------------------------+--------------------------------------+--+
| ad_101         | http://www.google.com  | catalog8|catalog1                    |
| ad_102         | http://www.sohu.com    | catalog6|catalog3                    |
| ad_103         | http://www.baidu.com   | catalog7                             |
| ad_104         | http://www.qq.com      | catalog5|catalog1|catalog4|catalog9  |
| ad_105         | http://sina.com        | NULL                                 |
+----------------+------------------------+--------------------------------------+--+
5 rows selected (0.18 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> create table click_log (
. . . . . . . . . . . . . . . . . . . . . . > cookie_id string,
. . . . . . . . . . . . . . . . . . . . . . > ad_id string,
. . . . . . . . . . . . . . . . . . . . . . > time string
. . . . . . . . . . . . . . . . . . . . . . > )
. . . . . . . . . . . . . . . . . . . . . . > row format delimited 
. . . . . . . . . . . . . . . . . . . . . . > fields terminated by '\t';
No rows affected (0.08 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> load data local inpath '/home/ruoze/data/click_log.txt' overwrite into table click_log;
No rows affected (0.16 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select * from click_log limit 5;
+----------------------+------------------+-----------------------------+--+
| click_log.cookie_id  | click_log.ad_id  |       click_log.time        |
+----------------------+------------------+-----------------------------+--+
| 11                   | ad_101           | 2014-05-01 06:01:12.334+01  |
| 22                   | ad_102           | 2014-05-01 07:28:12.342+01  |
| 33                   | ad_103           | 2014-05-01 07:50:12.33+01   |
| 11                   | ad_104           | 2014-05-01 09:27:12.33+01   |
| 22                   | ad_103           | 2014-05-01 09:03:12.324+01  |
+----------------------+------------------+-----------------------------+--+
5 rows selected (0.064 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb>

Examples

0: jdbc:hive2://rzdata001:10086/ruoze_hivedb>  # 每个人访问每个广告的次数以及对应广告的类目
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select click.cookie_id,click.ad_id,click.amount,ad_list.catalogs
. . . . . . . . . . . . . . . . . . . . . . > from (
. . . . . . . . . . . . . . . . . . . . . . > select cookie_id, ad_id, count(1) amount from click_log group by cookie_id, ad_id
. . . . . . . . . . . . . . . . . . . . . . > ) click join ad_list
. . . . . . . . . . . . . . . . . . . . . . > on ad_list.ad_id = click.ad_id;
2019-12-19 05:07:03     Dump the side-table for tag: 1 with group count: 5 into file: file:/tmp/ruoze/5353762b-cd3b-415c-9fc7-58e47c9350e2/hive_2019-12-19_17-06-42_607_4705675263615256455-2/-local-10004/HashTable-Stage-4/MapJoin-mapfile01--.hashtable
2019-12-19 05:07:03     Uploaded 1 File to: file:/tmp/ruoze/5353762b-cd3b-415c-9fc7-58e47c9350e2/hive_2019-12-19_17-06-42_607_4705675263615256455-2/-local-10004/HashTable-Stage-4/MapJoin-mapfile01--.hashtable (466 bytes)
2019-12-19 05:07:03     End of local task; Time Taken: 0.899 sec.
+------------------+--------------+---------------+--------------------------------------+--+
| click.cookie_id  | click.ad_id  | click.amount  |           ad_list.catalogs           |
+------------------+--------------+---------------+--------------------------------------+--+
| 11               | ad_101       | 3             | catalog8|catalog1                    |
| 11               | ad_104       | 1             | catalog5|catalog1|catalog4|catalog9  |
| 22               | ad_102       | 1             | catalog6|catalog3                    |
| 22               | ad_103       | 2             | catalog7                             |
| 22               | ad_104       | 1             | catalog5|catalog1|catalog4|catalog9  |
| 33               | ad_101       | 1             | catalog8|catalog1                    |
| 33               | ad_102       | 1             | catalog6|catalog3                    |
| 33               | ad_103       | 1             | catalog7                             |
| 35               | ad_102       | 1             | catalog6|catalog3                    |
| 35               | ad_105       | 1             | NULL                                 |
| 77               | ad_103       | 1             | catalog7                             |
| 77               | ad_104       | 1             | catalog5|catalog1|catalog4|catalog9  |
| 99               | ad_102       | 1             | catalog6|catalog3                    |
| 99               | ad_105       | 1             | NULL                                 |
+------------------+--------------+---------------+--------------------------------------+--+
14 rows selected (33.439 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb>
1.4 行转列&列转行

行转列

0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select * from click_log ;
+----------------------+------------------+-----------------------------+--+
| click_log.cookie_id  | click_log.ad_id  |       click_log.time        |
+----------------------+------------------+-----------------------------+--+
| 11                   | ad_101           | 2014-05-01 06:01:12.334+01  |
| 22                   | ad_102           | 2014-05-01 07:28:12.342+01  |
| 33                   | ad_103           | 2014-05-01 07:50:12.33+01   |
| 11                   | ad_104           | 2014-05-01 09:27:12.33+01   |
| 22                   | ad_103           | 2014-05-01 09:03:12.324+01  |
| 33                   | ad_102           | 2014-05-02 19:10:12.343+01  |
| 11                   | ad_101           | 2014-05-02 09:07:12.344+01  |
| 35                   | ad_105           | 2014-05-03 11:07:12.339+01  |
| 22                   | ad_104           | 2014-05-03 12:59:12.743+01  |
| 77                   | ad_103           | 2014-05-03 18:04:12.355+01  |
| 99                   | ad_102           | 2014-05-04 00:36:39.713+01  |
| 33                   | ad_101           | 2014-05-04 19:10:12.343+01  |
| 11                   | ad_101           | 2014-05-05 09:07:12.344+01  |
| 35                   | ad_102           | 2014-05-05 11:07:12.339+01  |
| 22                   | ad_103           | 2014-05-05 12:59:12.743+01  |
| 77                   | ad_104           | 2014-05-05 18:04:12.355+01  |
| 99                   | ad_105           | 2014-05-05 20:36:39.713+01  |
+----------------------+------------------+-----------------------------+--+
17 rows selected (0.054 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb>
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb>    # 每个人访问的所有ad_id 去重
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select  cookie_id,collect_set(ad_id)  
. . . . . . . . . . . . . . . . . . . . . . > from click_log group by cookie_id;
+------------+-------------------------------+--+
| cookie_id  |              _c1              |
+------------+-------------------------------+--+
| 11         | ["ad_101","ad_104"]           |
| 22         | ["ad_102","ad_103","ad_104"]  |
| 33         | ["ad_103","ad_102","ad_101"]  |
| 35         | ["ad_105","ad_102"]           |
| 77         | ["ad_103","ad_104"]           |
| 99         | ["ad_102","ad_105"]           |
+------------+-------------------------------+--+
6 rows selected (19.221 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb>   # 每个人访问的所有ad_id  不去重
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb>  select  cookie_id,collect_list(ad_id)  
. . . . . . . . . . . . . . . . . . . . . . >  from click_log group by cookie_id;
+------------+----------------------------------------+--+
| cookie_id  |                  _c1                   |
+------------+----------------------------------------+--+
| 11         | ["ad_101","ad_104","ad_101","ad_101"]  |
| 22         | ["ad_102","ad_103","ad_104","ad_103"]  |
| 33         | ["ad_103","ad_102","ad_101"]           |
| 35         | ["ad_105","ad_102"]                    |
| 77         | ["ad_103","ad_104"]                    |
| 99         | ["ad_102","ad_105"]                    |
+------------+----------------------------------------+--+
6 rows selected (17.263 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> 
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select  cookie_id,concat_ws(',',collect_list(ad_id))  as ad_str
. . . . . . . . . . . . . . . . . . . . . . > from click_log group by cookie_id;
+------------+------------------------------+--+
| cookie_id  |            ad_str            |
+------------+------------------------------+--+
| 11         | ad_101,ad_104,ad_101,ad_101  |
| 22         | ad_102,ad_103,ad_104,ad_103  |
| 33         | ad_103,ad_102,ad_101         |
| 35         | ad_105,ad_102                |
| 77         | ad_103,ad_104                |
| 99         | ad_102,ad_105                |
+------------+------------------------------+--+
6 rows selected (19.675 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb>

列转行

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView

0: jdbc:hive2://rzdata001:10086/ruoze_hivedb> select ad_id, catalog
. . . . . . . . . . . . . . . . . . . . . . > from ad_list
. . . . . . . . . . . . . . . . . . . . . . > lateral view outer explode(split(catalogs,'\\|')) t as catalog;
+---------+-----------+--+
|  ad_id  |  catalog  |
+---------+-----------+--+
| ad_101  | catalog8  |
| ad_101  | catalog1  |
| ad_102  | catalog6  |
| ad_102  | catalog3  |
| ad_103  | catalog7  |
| ad_104  | catalog5  |
| ad_104  | catalog1  |
| ad_104  | catalog4  |
| ad_104  | catalog9  |
| ad_105  | NULL      |
+---------+-----------+--+
10 rows selected (0.063 seconds)
0: jdbc:hive2://rzdata001:10086/ruoze_hivedb>
Hive 行列转换
Byyyi耀的博客
01-23 1026
Hive行列转换方式汇总
Hive 复杂数据类型
weixin_45524021的博客
11-27 2579
复杂数据类型一共有四种： ARRAY：数组 MAP：键值对 STRUCT：命名字段集合 UNION：从几种数据类型中指明选择一种,UNION的值必须于这些数据类型之一完全匹配； ARRAY 创建名为 t2的表,只有 person 和 friends 两个字段, person 是字符串类型, friends 是数组类型,通过文本文件导入数据时,person 和 friends之间的分隔符是竖线,friends 内部的多个元素之间的分隔符是逗号, 注意分隔符的语法： create t..
【SQL】HiveSQL 转置/列转行/行转列 map/case when
PersephoneSu
10-10 2335
EXCEL的转置功能 列转行 SELECT `项目`,`类型`,`值` FROM ( select `项目` ,map(‘申请率’,`申请率`,‘审批率’,`审批率`) tmp from tmp_table ) LATERAL VIEW EXPLODE(tmp) explode_table AS `类型`,`值` 转置前 转置后 行转列 SELECT `类型` ,sum( case when `项目` = ‘甲’ then `值` else 0 end ) `甲` ,sum( case when `
Hive 特殊的数据类型 Array、Map、Struct
JIE的博客 --- moon_coder
05-11 2064
获取数组值的方式和我们在 Java 中一样，采用下标的方式访问，当访问的下标超过数组的长度时，并不会报错，它会返回。这里值 Value 中存在整型和字符型数据类型不统一的情况，它会统一将值 Value 都转换成字符型。Map 是 KV 键值对类型，其中存储的字段数量可多可少，但是 KV 键值对的数据类型必须提前确定。和 Map 一样，它会将前面的数据当作 Key，后面的数据当作 Value，两两组合，形成结构体。它会将前面的数据当作 Key，后面的数据当作 Value，两两组合，形成 Map 集合。
Hive : 行列转换
最新发布
Suzy_Leili的博客
05-12 277
方法一非常耗时，每一行都生成action字段，通过rn取一行；方法二是多行并一行，执行较快。
Hive特殊的数据类型：Array，Map，Struct
11号车厢
08-12 447
1、Array #创建一张包含array字段的表，array字段的分割符采用的是逗号 create table hive_array( name string, work_locations array&lt;string&gt; ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY ',';...
hive中复杂的数据类型array与map
welcome to zdsg's csdn
09-11 749
1.array //创建表 create table if not exists arr2( name string, score array&lt;String&gt; ) row format delimited fields terminated by '\t' //字段之间的分隔符 collection items terminated by ',' //arr...
《Hive 6》--hive的3中特殊数据类型、ORDER BY和SORT BY、索引、存储格式、UDF
yk_3215123的博客
07-20 1217
hive中比关系型数据库中多了一些特殊的类型： 1)array:数组是相同类型的，下标从0开始 array01('英语','化学','物理') array01[1] ---&gt;'化学' 传统关系型数据库中： name like1 like2 like3 like4 like5 张三 足球 汽车 读书 喝茶...
HIVE复杂数据类型与行转列、列转行的使用
Im ok的博客哦
12-23 980
一、复杂数据类型 在hive中使用复杂数据类型的优缺点 好处：由于复杂数据类型的存储数据比基本数据类型要多，在存盘上存储可以连续存储，在查询等操作时可以减少磁盘IO。 坏处：复杂数据类型可能会存在着数据的重复，而且有更大的导致数据不一致的风险。 hive中的复杂数据类型主要有array、map、struct三种，三种数据类型有其独特的使用场景。 1、ARRAY array即数组，array中的数据类型都是统一的，比如全为int，或者全为string。 使用array中元素,访问数组中的某一
hive复杂类型数据详解—array,map,struct
lbg20211023的博客
07-28 1788
hive复杂类型数据详解—array,map,struct
hive中容易忽略的知识点--分隔符&类型转换&udf&udtf&udaf等等
weixin_43548518的博客
04-09 1670
前提：这里不详细讲解hive 指讲最容易忽略的一些细节 hive中表默认分隔符 hive建立的表默认分隔符为‘001’ 如果想指定其他分隔符使用row format delimited fields terminated by ‘\t’ hive中的数据类型 hive hql 语句和mysql 类似，但是数据类型不太一样 Hive数据类型 TINYINT SMALINT INT BIGINT B...
Hive中复合数据类型Array Map相关操作
qq_654603797
05-14 2362
需求背景： 求：如何判断字段B中包含23（其中字段A的数据类型为String,字段B的数据类型为Array） FieldA FieldB 1 [52,12,23,11,25,11] 2 [28,41,25,254,36] 3 [60,78,48,481,236,56] 4 [239,108524,14] 5 [52,36,96,42,33] 思路梳理： 1.第一感觉可能会考虑到模糊匹配likeinstr等等。但 239 236这样的...
hive 列转行
root_one的博客
12-16 2868
SELECT concat_ws(',', collect_set(param_code)) from rca.yyd_bairong_interface_result  WHERE dt &amp;lt; '2018-09-27'  AND borrow_nid ='2017000000xxxxxx'  AND update_time ='2017-05-01 00:11:37.0' ;
Hive复杂数据类型(array类型、map类型、struct类型)
2202_75347029的博客
09-06 4382
一、array类型建表语句：create table 表名(要素1 类型，要素2 类型，要素3 array) row format delimited fileds terminated by '\t' collection items terminated by ',';
java map 行转列_hive中array嵌套map以及行转列的使用
weixin_42657024的博客
02-16 222
1. 数据源信息{"student": {"name":"king","age":11,"sex":"M"},"sub_score":[{"subject":"语文","score":80},{"subject":"数学","score":80},{"subject":"英语","score":80}]}{"student": {"name":"king1","age":11,"sex":"M"}...
hive中复杂数据类型(array，map，struct)讲解
m0_65095185的博客
05-08 2837
hive复杂数据类型的创建、查询与使用
将Hive中的ARRAY＜＞字段的每一行转换为一列
北凉徐凤年的博客
03-10 1221
将Hive中的ARRAY字段的每一行转换为一列
hive表中array类型数据处理（行列转换）
Marsm的博客
11-23 9331
hive表中array类型数据处理 hive表中数据如下： 1 [&quot;20170101&quot;,&quot;20170102&quot;,&quot;20170201&quot;,&quot;20170203&quot;] 2 [&quot;20170102&quot;,&quot;20170102&quot;,&quot;20170201&quot;] 3 [&quot;20170104&quot;] 2 [&quot;20170201&quot
hive 一行转一列
03-08
&lt;think&gt;好的，我现在需要解决用户的问题：&ldquo;hive 一行转一列&rdquo;。用户可能是在处理Hive中的数据转换时遇到了问题，想将一行数据转换为多行，每列变成单独的行。首先，我得理解&ldquo;一行转一列&rdquo;具体指的是什么。根据常见的Hive数据处理场景，通常会有两种可能的情况：一种是行转列，也就是将多行合并成一行，比如使用collect_list或者concat_ws；另一种是列转行，即将一行中的多个列拆分成多行，这时可能需要使用explode函数或者LATERAL VIEW。 用户提到的是&ldquo;一行转一列&rdquo;，这里的表述可能存在歧义。假设用户的意思是将一行中的多个列转换成多行，也就是列转行，常见的例子是将一个包含多个字段的行，每个字段拆分成单独的行，比如姓名、年龄、性别等字段，转换成多行，每行一个属性。这时候需要使用Hive的LATERAL VIEW和explode函数组合来实现。 接下来，我需要回忆Hive中相关的函数和语法。Hive中的explode函数可以将数组或者map类型的列拆分成多行。但如果数据是结构化的列，比如多个普通的列，可能需要先将这些列转换成一个数组或者map，然后再使用explode。例如，假设有一个表，有多个属性列，需要将每个属性作为一行输出，可能需要将这些列组合成一个array&lt;string&gt;，然后进行explode。 例如，原始数据可能是这样的： | id | name | age | gender | |----|-------|-----|--------| | 1 | Alice | 25 | F | 想要转换成： | id | key | value | |----|-------|-------| | 1 | name | Alice | | 1 | age | 25 | | 1 | gender| F | 这时候，需要将每个列名作为key，值作为value，拆分成多行。在Hive中，可以通过map和explode结合实现。首先使用map函数将多个键值对组合成一个map类型，然后使用explode将其拆分。 具体的HiveQL可能如下： SELECT id, key, value FROM original_table LATERAL VIEW explode(map( &#39;name&#39;, name, &#39;age&#39;, age, &#39;gender&#39;, gender )) exploded_table AS key, value; 这里，map函数将多个列组合成一个map，然后通过LATERAL VIEW explode将其转换为多行。每个键值对变成一行，保留原始的id字段。 不过需要注意的是，map的键值对需要类型匹配，如果原始列有不同的类型，可能需要转换为string类型，例如使用cast(age as string)来统一类型。 另一种情况是，如果用户的数据是数组类型，那么可以直接使用explode函数。例如，如果有一个列是数组，包含多个元素，explode会将其拆分成多行。 此外，如果用户的数据是复杂的结构，比如struct类型，可能需要结合其他函数进行处理。但根据常见的情况，用户的问题更可能是将多个普通列拆分成多行，这时候使用map和explode的组合是合适的。 另外，需要注意LATERAL VIEW的使用，它允许在SELECT子句中使用UDTF（如explode），并将结果展开成多行。同时，需要为生成的列指定别名，如上面的AS key, value。 总结步骤： 1. 确定需要转换的列，将它们组合成一个map或array。 2. 使用explode函数配合LATERAL VIEW进行拆分。 3. 确保数据类型的一致性，必要时进行类型转换。 4. 处理结果，可能需要过滤NULL值或其他异常情况。 可能的错误点包括：未正确使用LATERAL VIEW语法，未处理数据类型不一致，或者map的键值对数量不匹配。需要提醒用户检查这些方面。 最后，需要给出一个完整的示例，帮助用户更好地理解如何实现一行转多行的操作。同时，可能需要提到其他相关函数，如stack，但Hive中可能不支持，或者版本差异需要注意。&lt;/think&gt;在Hive中将一行数据转换为多行（即列转行）通常使用`LATERAL VIEW`和`explode()`函数。以下是具体实现方法及示例： --- ### **核心语法** ```sql SELECT 原表字段, 拆分后的key, 拆分后的value FROM 原始表 LATERAL VIEW explode(map( &#39;列名1&#39;, 列1, &#39;列名2&#39;, 列2, ... )) 别名 AS key, value ``` --- ### **完整示例** #### 1. 原始数据表 `user_info` | id | name | age | gender | |-----|-------|-----|--------| | 1 | Alice | 25 | F | --- #### 2. 目标结果 将每个字段转为键值对形式： | id | key | value | |-----|--------|-------| | 1 | name | Alice | | 1 | age | 25 | | 1 | gender | F | --- #### 3. HiveSQL实现 ```sql SELECT id, key, value FROM user_info LATERAL VIEW explode(map( &#39;name&#39;, name, &#39;age&#39;, cast(age as string), -- 统一类型为string &#39;gender&#39;, gender )) exploded_table AS key, value; ``` --- ### **关键步骤解释** 1. **构造Map** 使用`map()`函数将多列转为键值对： ```sql map(&#39;name&#39;, name, &#39;age&#39;, age, &#39;gender&#39;, gender) ``` 生成类似：`{&#39;name&#39;:&#39;Alice&#39;, &#39;age&#39;:&#39;25&#39;, &#39;gender&#39;:&#39;F&#39;}` 2. **拆分Map** `explode()`将Map拆分为多行： ```sql explode(...) -- 输出多行 (key, value) ``` 3. **横向展开** `LATERAL VIEW`允许在SELECT语句中展开UDTF（如`explode()`）的结果。 --- ### **注意事项** - **类型一致性**：确保所有值字段类型相同，必要时用`cast()`转换。 - **空值处理**：若原始列有空值，需用`COALESCE()`或`NVL()`处理。 - **复杂场景**：若需处理数组列，直接`explode(数组字段)`即可。 --- ### **其他方案** 若使用Hive 0.12+，可用`stack`函数： ```sql SELECT id, stack(3, &#39;name&#39;, name, &#39;age&#39;, age, &#39;gender&#39;, gender) AS (key, value) FROM user_info; ``` 但需注意版本兼容性。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值