hive 的 struct、map與array 類型

hive支援struct,map,array三種集合類型

1、struct

與C語言、golang中的struct類似,可以通過.文法來通路

定義一個包含struct字段的表

create table test2(
	field1 struct<name:string,age:int> comment "test field"
) 
row format delimited fields terminated by "," 
collection items terminated by ":";
           

其中

row format delimited fields terminated by ","

表示字段之間用",“分割

collection items terminated by ":"

; 字段中各個元素項之間用”:"分割

然後導入資料,可以使用sql語句和檔案的方式,首先是sql語句注意: 這裡使用的是named_struct函數

insert into test2(field1)values(named_struct("name",'zhangsan',"age",25));
insert into test2(field1)values(named_struct("name","lisi","age",23));
           

從檔案導入,建立一個test.txt,内容如下

name:wangwu|age:27
name:zhaoliu|age:28
           

使用load data語句從本地導入

使用.文法查詢資料

-- 查詢所有資料
select * from test2;
-- 查詢name字段
select field1.name from test2;
-- 查詢name為zhangsan的記錄
select field1 from test2 where field1.name = "zhangsan";
           

2、map

同struct,map的用法基本相似,以下為建立一個map類型字段

create table test3(
	field2 map<string,string>
) row format delimited fields terminated by ',' 
collection items terminated by "|" map keys terminated by ":";
           

map keys terminated by ":"

表示鍵值對之間用":"來分割

同樣的,先使用sql方式插入

再導入檔案,檔案内容如下:

map的通路通過map[key]的形式進行,測試一下

select * from test3;
select field2["name"] as name,field2["age"] as age from test3;
select * from test3 where field2["age"] > 25;
           

3、array

與所有支援數組的程式設計語言一緻,使用array[下标]的方式通路,建立一個array類型字段

插入資料

insert into test4(field4)values(array("zhangsan","lisi","wangwu"));
insert into test4(field4)values(`array`("lily","bob","alice"));
insert into test4(field4)values(`array`("A","B","C"));
           

查詢測試

select * from test4;
select field4[0] from test4;
select * from test4 where field4[0]="zhangsan";