Hive 复杂数据类型实战

复杂数据类型实战

• 在 hive 中使用 array 、 map 、 struct

Array

建表

create table test_a (name string, stu_id_list array<INT>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY ':' ;

'FIELDS TERMINATED BY' ：字段与字段之间的分隔符
'COLLECTION ITEMS TERMINATED BY' ：一个字段各个 item 的分隔符 

查看数据

# 注意 : 每一行的最后不要有空格
hadoop0@hadoop:~$ cat test_a.txt
0601,1:2:3:4
0602,5:6
0603,7:8:9:10
0604,11:12

-- 切换到 hive 中导入数据
load data local inpath '/home/hadoop0/test_a.txt' overwrite into table test_a;

查询

select stu_id_list[3] from test_a;

---

Map

建表

create table test_m (id int, unit map<string, int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';

‘MAP KEYS TERMINATED BY’： key value 分隔符

查看数据

# 特别注意 : 这里的 '\t' 请在 Linux 中重新输入一次
hadoop0@hadoop:~$ cat test_m.txt
0       Chinese:100,English:80,math:59
1       Chinese:80,English:90
2       Chinese:100,English:100,math:60

-- 切换到 hive 中导入数据
!pwd; -- 查看当前目录
load data local inpath './test_m.txt' overwrite into table test_m;

查询

select id,unit['math'] from test_m;
select unit['math'] from test_m;
select unit from test_m;

• 过滤 null 值

  select id,unit['math'] from test_m where unit['math'] is not null;
  
  -- 查看**数学**及格的人
  select unit from test_m where unit['math'] like '60';
  

---

Struct

建表

create table test_s(id int, info struct<name:string, age:int, height:float>)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','                         
COLLECTION ITEMS TERMINATED BY ':';

查看数据

hadoop0@hadoop:~$ cat test_s.txt
0,zhao:18:178
1,qian:30:173
2,sun:20:180
3,li:23:183

-- 切换到 hive 中导入数据
!pwd; -- 查看当前目录
load data local inpath 'test_s.txt' overwrite into table test_s;

查询

select id, info.age from test_s;

-------------本文结束 感谢您的阅读-------------