hive的复杂数据类型

最新推荐文章于 2025-06-24 18:25:33 发布
最新推荐文章于 2025-06-24 18:25:33 发布
阅读量1.3k 收藏 3
点赞数
CC 4.0 BY-SA版权
分类专栏： Hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/whiteblacksheep/article/details/96737559

hive的复杂数据类型：

  • Array(1,2,3,4) 装的数据类型是一样的
  • Map(‘a’,1,‘b’,c) key的类型一样
  • Struct(‘a’,1,2,34,4)装的数据是完全混乱的

Array数据类型

1.存放

创建一张存放array数据类型的表

[hadoop@hadoop001 data]$ cat hive_array.txt 
zhangsan        beijing,shanghai,tianjin,hangzhou
lisi    changchu,chengdu,wuhan,beijing
[hadoop@hadoop001 data]$ 

0: jdbc:hive2://hadoop001:10000/default> create table hive_array(name string,work_locations array<string>)
0: jdbc:hive2://hadoop001:10000/default> row format delimited fields terminated by '\t'
0: jdbc:hive2://hadoop001:10000/default> collection items terminated by ',';  //创建一张hive_array表
INFO  : Compiling command(queryId=hadoop_20190721172424_0b897d7d-2785-48a1-9067-b7e7e322da25): create table hive_array(name string,work_locations array<string>)
row format delimited fields terminated by '\t'
collection items terminated by ','
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO  : Completed compiling command(queryId=hadoop_20190721172424_0b897d7d-2785-48a1-9067-b7e7e322da25); Time taken: 0.006 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=hadoop_20190721172424_0b897d7d-2785-48a1-9067-b7e7e322da25): create table hive_array(name string,work_locations array<string>)
row format delimited fields terminated by '\t'
collection items terminated by ','
INFO  : Starting task [Stage-0:DDL] in serial mode
INFO  : Completed executing command(queryId=hadoop_20190721172424_0b897d7d-2785-48a1-9067-b7e7e322da25); Time taken: 0.303 seconds
INFO  : OK
No rows affected (0.315 seconds)
0: jdbc:hive2://hadoop001:10000/default> load data local inpath '/home/hadoop/data/hive_array.txt' overwrite into table hive_array;     //向表中存放array数据
INFO  : Compiling command(queryId=hadoop_20190721172525_eb920e8d-3099-4ddd-9b0f-81615557122c): load data local inpath '/home/hadoop/data/hive_array.txt' overwrite into table hive_array
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO  : Completed compiling command(queryId=hadoop_20190721172525_eb920e8d-3099-4ddd-9b0f-81615557122c); Time taken: 0.015 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=hadoop_20190721172525_eb920e8d-3099-4ddd-9b0f-81615557122c): load data local inpath '/home/hadoop/data/hive_array.txt' overwrite into table hive_array
INFO  : Starting task [Stage-0:MOVE] in serial mode
INFO  : Loading data to table default.hive_array from file:/home/hadoop/data/hive_array.txt
INFO  : Starting task [Stage-1:STATS] in serial mode
INFO  : Table default.hive_array stats: [numFiles=1, numRows=0, totalSize=81, rawDataSize=0]
INFO  : Completed executing command(queryId=hadoop_20190721172525_eb920e8d-3099-4ddd-9b0f-81615557122c); Time taken: 0.35 seconds
INFO  : OK
No rows affected (0.371 seconds)
0: jdbc:hive2://hadoop001:10000/default> select * from hive_array;         //查询数据是否导入成功
INFO  : Compiling command(queryId=hadoop_20190721172525_5e5fb5c2-fe06-4655-ba16-efdddf11a535): select * from hive_array
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:hive_array.name, type:string, comment:null), FieldSchema(name:hive_array.work_locations, type:array<string>, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=hadoop_20190721172525_5e5fb5c2-fe06-4655-ba16-efdddf11a535); Time taken: 0.034 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=hadoop_20190721172525_5e5fb5c2-fe06-4655-ba16-efdddf11a535): select * from hive_array
INFO  : Completed executing command(queryId=hadoop_20190721172525_5e5fb5c2-fe06-4655-ba16-efdddf11a535); Time taken: 0.0 seconds
INFO  : OK
+------------------+----------------------------------------------+--+
| hive_array.name  |          hive_array.work_locations           |
+------------------+----------------------------------------------+--+
| zhangsan         | ["beijing","shanghai","tianjin","hangzhou"]  |
| lisi             | ["changchu","chengdu","wuhan","beijing"]     |
+------------------+----------------------------------------------+--+
2 rows selected (0.052 seconds)
2.查询

1.查询属组的第一个数时什么

	0: jdbc:hive2://hadoop001:10000/default> select  name,work_locations[0] from hive_array;
INFO  : Compiling command(queryId=hadoop_20190721173131_2eafd3be-b9f2-4627-b784-c931a899ee4e): select  name,work_locations[0] from hive_array
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:name, type:string, comment:null), FieldSchema(name:_c1, type:string, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=hadoop_20190721173131_2eafd3be-b9f2-4627-b784-c931a899ee4e); Time taken: 0.053 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=hadoop_20190721173131_2eafd3be-b9f2-4627-b784-c931a899ee4e): select  name,work_locations[0] from hive_array
INFO  : Completed executing command(queryId=hadoop_20190721173131_2eafd3be-b9f2-4627-b784-c931a899ee4e); Time taken: 0.0 seconds
INFO  : OK
+-----------+-----------+--+
|   name    |    _c1    |
+-----------+-----------+--+
| zhangsan  | beijing   |
| lisi      | changchu  |
+-----------+-----------+--+
2 rows selected (0.065 seconds)
0: jdbc:hive2://hadoop001:10000/default>

2.查询表中每个人的工作地点数（size（）函数的用法）

0: jdbc:hive2://hadoop001:10000/default> select  name,size(work_locations) from hive_array;
INFO  : Compiling command(queryId=hadoop_20190721173636_d1aabbe0-bb8b-4116-89bc-6c3b23081e2e): select  name,size(work_locations) from hive_array
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:name, type:string, comment:null), FieldSchema(name:_c1, type:int, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=hadoop_20190721173636_d1aabbe0-bb8b-4116-89bc-6c3b23081e2e); Time taken: 0.027 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=hadoop_20190721173636_d1aabbe0-bb8b-4116-89bc-6c3b23081e2e): select  name,size(work_locations) from hive_array
INFO  : Completed executing command(queryId=hadoop_20190721173636_d1aabbe0-bb8b-4116-89bc-6c3b23081e2e); Time taken: 0.0 seconds
INFO  : OK
+-----------+------+--+
|   name    | _c1  |
+-----------+------+--+
| zhangsan  | 4    |
| lisi      | 4    |
+-----------+------+--+
2 rows selected (0.053 seconds)
3.在北京上班的有哪些人（array_contains数组包含函数的用法）

0: jdbc:hive2://hadoop001:10000/default> select  * from hive_array where array_contains(work_locations,'beijing');
INFO  : Compiling command(queryId=hadoop_20190721174343_4c574fc3-cab5-4b1d-8e28-c81782af3044): select  * from hive_array where array_contains(work_locations,'beijing')
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:hive_array.name, type:string, comment:null), FieldSchema(name:hive_array.work_locations, type:array<string>, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=hadoop_20190721174343_4c574fc3-cab5-4b1d-8e28-c81782af3044); Time taken: 0.036 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=hadoop_20190721174343_4c574fc3-cab5-4b1d-8e28-c81782af3044): select  * from hive_array where array_contains(work_locations,'beijing')
INFO  : Completed executing command(queryId=hadoop_20190721174343_4c574fc3-cab5-4b1d-8e28-c81782af3044); Time taken: 0.0 seconds
INFO  : OK
+------------------+----------------------------------------------+--+
| hive_array.name  |          hive_array.work_locations           |
+------------------+----------------------------------------------+--+
| zhangsan         | ["beijing","shanghai","tianjin","hangzhou"]  |
| lisi             | ["changchu","chengdu","wuhan","beijing"]     |
+------------------+----------------------------------------------+--+
2 rows selected (0.054 seconds)
0: jdbc:hive2://hadoop001:10000/default>

Map数据类型

1.存放
0: jdbc:hive2://hadoop001:10000/default> 
0: jdbc:hive2://hadoop001:10000/default> create table hive_map(id int,name string,members map<string,string>,age int)
0: jdbc:hive2://hadoop001:10000/default> row format delimited fields terminated by ','
0: jdbc:hive2://hadoop001:10000/default> collection items terminated by '#'
0: jdbc:hive2://hadoop001:10000/default> map keys terminated by ':'; //在默认的default库里创建一张存放可以存放map的表
No rows affected (0.622 seconds)
0: jdbc:hive2://hadoop001:10000/default> load data local inpath '/home/hadoop/data/hive_map.txt' overwrite into table hive_map;   //从本地导入数据到该表
No rows affected (0.387 seconds)
0: jdbc:hive2://hadoop001:10000/default> select * from hive_map;   //查询是否导入成功
+--------------+----------------+----------------------------------------------------------------+---------------+--+
| 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            |
| 4            | mayun          | {"father":"mayongzhen","mother":"angelababy"}                  | 26            |
+--------------+----------------+----------------------------------------------------------------+---------------+--+
4 rows selected (0.051 seconds)
0: jdbc:hive2://hadoop001:10000/default>
2.查询

1.只需要map中的其中一个key的值

0: jdbc:hive2://hadoop001:10000/default> select id,name,members["father"] from hive_map;
+-----+-----------+--------------+--+
| id  |   name    |     _c2      |
+-----+-----------+--------------+--+
| 1   | zhangsan  | xiaoming     |
| 2   | lisi      | mayun        |
| 3   | wangwu    | wangjianlin  |
| 4   | mayun     | mayongzhen   |
+-----+-----------+--------------+--+
4 rows selected (0.045 seconds)
0: jdbc:hive2://hadoop001:10000/default> 
0: jdbc:hive2://hadoop001:10000/default> select id,name,members["brother"] from hive_map;
+-----+-----------+---------+--+
| id  |   name    |   _c2   |
+-----+-----------+---------+--+
| 1   | zhangsan  | xiaoxu  |
| 2   | lisi      | guanyu  |
| 3   | wangwu    | NULL    |
| 4   | mayun     | NULL    |
+-----+-----------+---------+--+
4 rows selected (0.044 seconds)
0: jdbc:hive2://hadoop001:10000/default>

2.查出每个人有哪些家庭成员（map_keys()函数的使用）

0: jdbc:hive2://hadoop001:10000/default>  select id,name,map_keys(members) as map_keys from hive_map;
+-----+-----------+--------------------------------+--+
| id  |   name    |            map_keys            |
+-----+-----------+--------------------------------+--+
| 1   | zhangsan  | ["father","mother","brother"]  |
| 2   | lisi      | ["father","mother","brother"]  |
| 3   | wangwu    | ["father","mother","sister"]   |
| 4   | mayun     | ["father","mother"]            |
+-----+-----------+--------------------------------+--+
4 rows selected (0.04 seconds)
0: jdbc:hive2://hadoop001:10000/default>

3.只需要家庭成员的名字（map_values（）函数的使用）

0: jdbc:hive2://hadoop001:10000/default>  select id,name,map_values(members) as map_values from hive_map;
+-----+-----------+-------------------------------------+--+
| id  |   name    |              map_values                |
+-----+-----------+-------------------------------------+--+
| 1   | zhangsan  | ["xiaoming","xiaohuang","xiaoxu"]   |
| 2   | lisi      | ["mayun","huangyi","guanyu"]        |
| 3   | wangwu    | ["wangjianlin","ruhua","jingtian"]  |
| 4   | mayun     | ["mayongzhen","angelababy"]         |
+-----+-----------+-------------------------------------+--+
4 rows selected (0.039 seconds)
0: jdbc:hive2://hadoop001:10000/default>

4.查询所有人的家庭成员人数

0: jdbc:hive2://hadoop001:10000/default>  select id,name,size(members) as members_sum from hive_map;
+-----+-----------+--------------+--+
| id  |   name    | members_sum  |
+-----+-----------+--------------+--+
| 1   | zhangsan  | 3            |
| 2   | lisi      | 3            |
| 3   | wangwu    | 3            |
| 4   | mayun     | 2            |
+-----+-----------+--------------+--+
4 rows selected (0.04 seconds)
0: jdbc:hive2://hadoop001:10000/default>

5.仅仅只需要显示每人的father

0: jdbc:hive2://hadoop001:10000/default> select id,name,members["father"] as father from hive_map where  array_contains(map_keys(members),"father");
+-----+-----------+--------------+--+
| id  |   name    |    father    |
+-----+-----------+--------------+--+
| 1   | zhangsan  | xiaoming     |
| 2   | lisi      | mayun        |
| 3   | wangwu    | wangjianlin  |
| 4   | mayun     | mayongzhen   |
+-----+-----------+--------------+--+
4 rows selected (0.035 seconds)
0: jdbc:hive2://hadoop001:10000/default>

struct(结构化)数据类型

1.存放
0: jdbc:hive2://hadoop001:10000/default> create table hive_struct(ip string,userinfo struct<name:string,age:int>)
0: jdbc:hive2://hadoop001:10000/default> row format delimited fields terminated by '#'
0: jdbc:hive2://hadoop001:10000/default> collection items terminated by ':';  //在默认defualt库里创建一个能够存放struct数据的表
No rows affected (0.392 seconds)
0: jdbc:hive2://hadoop001:10000/default> load data local inpath '/home/hadoop/data/hive_struct.txt' overwrite into table hive_struct;  //从本地向该表中导入数据 
No rows affected (0.831 seconds)
0: jdbc:hive2://hadoop001:10000/default> select * from hive_struct;  //查询数据是否导入成功
+-----------------+-------------------------------+--+
| hive_struct.ip  |     hive_struct.userinfo      |
+-----------------+-------------------------------+--+
| 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.032 seconds)
0: jdbc:hive2://hadoop001:10000/default>
2.查询

从struct数据类型中获取数据只需 userinfo.xxx
1.查询每个ip的用户名和年龄

0: jdbc:hive2://hadoop001:10000/default> select userinfo.name,userinfo.age from hive_struct;
+-----------+------+--+
|   name    | age  |
+-----------+------+--+
| zhangsan  | 40   |
| lisi      | 50   |
| wangwu    | 60   |
| zhaoliu   | 70   |
+-----------+------+--+
4 rows selected (0.045 seconds)
0: jdbc:hive2://hadoop001:10000/default>
hive 复杂数据类型的插入
yy的博客
04-04 535
insert 指定列名 不传入列名就是null。
hive复杂数据类型使用
一只小黑
08-19 561
hive中复杂数据类型的使用
Hive复杂数据类型使用介绍
08-01
Hive 做大数据分析，Hive的数据类型大多与Mysql相似，但是它存在自己独特的复杂类型。
Hive的三种复杂数据类型
边看边学
07-03 1660
Hive的复杂数据类型主要分为3类：array、map 和 struct。 1. array 测试数据: 列间用 \t 分隔，第二列用逗号分隔。 zhangsan chengdu,shanghai,beijing lisi tianjin,taiyuan,chongqing wangwu xian,nanning,beijing 建表： create table hive_array( name string, locations array<string> ) row format del
Hive（基于 Hadoop 的数据仓库工具）
最新发布
ctlongs的博客
06-24 705
本文详细介绍了Apache Hive的安装配置与基础操作指南。主要内容包括：Hive依赖的环境准备（Hadoop、MySQL、Java），安装MySQL和Hive的具体步骤，配置Hive连接MySQL存储元数据的方法，以及初始化元数据的操作。文章还提供了Hive客户端启动、数据库/表创建、数据查询等基本操作示例，并介绍了分区表、外部表、分桶表等核心概念。最后涵盖高级功能（Beeline使用、计算引擎集成、ETL任务）和性能优化技巧，为大数据分析提供完整的技术支持方案。
Hive 数据类型
weixin_71667809的博客
04-07 1275
Hive 中的基本数据类型也称为原始类型，包括整数、小数、文本、布尔、二进及时间类型。上述数据类型都是对Java中的接口的实现，所以类型的具体行为细节和Java中对应的类型完全一致。比如STRING 类型实现的是Java中的String,FLOAT类型实现的是Java中的float。
Hive中的复杂类型
ShaneHolmes
03-11 443
Hive专栏 上一篇 主目录 下一篇 目录 https://blog.youkuaiyun.com/wf1982/article/details/7474601 【前言】
hive复杂数据类型之 map映射
wowulita123的博客
05-08 2170
好了，今天的map映射介绍就分享到这里，已经给大家讲的非常详细了，大家在实际的工作中可能不太能用得上，但是作为一个优秀的程序员，很多东西你可以不用，不掌握，但是你不能不知道有这么一个东西！！知道才是王道，万一在代码开发中需要用呢？？？你可以想起这篇文章。欢迎一键三连，您的鼓励是我创作的动力。
hive复杂数据类型 UDF UDAF学习
m0_70160355的博客
09-29 414
hive复杂数据类型 UDF UDAF学习
Hive 复杂数据类型之 STRUCT/MAP/JSON
实践出真知
08-02 860
在 Hive 中，除了常见的基本数据类型（如 INT、STRING、FLOAT 等），还支持一些特殊的数据类型，如 STRUCT、MAP 和 JSON，这些数据类型允许处理更复杂和嵌套的数据结构。
Hive复杂数据类型(array类型、map类型、struct类型)
2202_75347029的博客
09-06 4449
一、array类型建表语句：create table 表名(要素1 类型，要素2 类型，要素3 array) row format delimited fileds terminated by '\t' collection items terminated by ',';
Hive的复杂数据类型
爱偷吃香蕉的萌猴子
03-13 953
Hive复杂数据类型1、Array数据类型的使用1.1、创建数据库表，以array作为数据类型0: jdbc:hive2://192.168.230.10:10000> create table person(name string,worklocations array<string>) row format delimited fields terminated by '\t' collec
SQL中concat函数和concat_ws函数的区别与联系
liusuoyin的博客
08-19 1458
SQL中concat函数和concat_ws函数的区别与联系  concat函数   CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串。例如：  concat_ws函数   CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL
hive的几种复杂数据类型
小 tom的博客
02-07 1273
这些复杂数据类型在 Hive 中非常有用，特别是在处理大数据和半结构化数据时。它们可以帮助存储更复杂的、嵌套的数据结构，使得数据查询更加灵活和高效。ARRAY：适用于存储同类数据集合。STRUCT：适用于存储多个字段的组合。MAP：适用于存储键值对数据。UNIONTYPE：适用于存储多种类型中的任意一个。
hive中复杂数据类型(array，map，struct)讲解
m0_65095185的博客
05-08 2910
hive复杂数据类型的创建、查询与使用
Hive中的复杂数据类型
weixin_33672109的博客
11-24 771
在Hive中如何使用符合数据结构 maps，array，structs1 Array的使用创建数据库表，以array作为数据类型create table person(name string,work_locations array<string>)ROW FORMAT DELIMITEDFIELDS TERMINATED BY '...
Hive的复杂数据类型和时间类型
实践求真知
11-21 759
一 复杂数据类型 Array：数组类型，由一系列相同类型的元素组成 Map：集合类型，包括key->value键值对，可以通过key来访问元素。 Struct：结构类型，可以包含不同类型的元素。这些元素可以通过“点语法”的方式来得到所需要的元素。 二 实战 1、数组 hive> create table student > (sid int, > sname string,
Hive复杂数据类型
wzj_wp的博客
12-30 210
array类型 建表并加载数据 创建表时候指定字段为array类型 location array 指定array中每个的分隔符COLLECTION ITEMS TERMINATED BY ‘,’ hive (wzj)> create table hive_array( > name string, > loaction array&lt...
hive复杂数据类型
09-14
Hive中的复杂数据类型包括map、array和struct。这三种类型可以用于建表、查询以及与其他数据类型的相互转换。 在Hive中，map类型表示键值对的集合，可以使用CREATE TABLE语句来建表并指定map类型的列。查询map类型...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值