Hive中的集合資料型別

柯廣發表於2020-12-20
Hive資料型別

除了使用礎的資料型別string等，Hive中的列支援使用struct, map, array集合資料型別。

資料型別 描述 語法示例
STRUCT 和C語言中的struct或者"物件"類似，都可以通過"點"符號訪問元素內容。 struct{'John', 'Doe'}
MAP MAP是一組鍵-值對元素集合，使用key可以訪問元素。 map('fisrt', 'John', 'last', 'Doe')
ARRAY 陣列是一組具有相同資料型別和名稱的變數的集合。 Array('John', 'Doe')

1. Array的使用

建立資料庫表，以array作為資料型別

create table  person(name string,work_locations array<string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';

資料

biansutao beijing,shanghai,tianjin,hangzhou
linan changchu,chengdu,wuhan

入庫資料

LOAD DATA LOCAL INPATH '/home/hadoop/person.txt' OVERWRITE INTO TABLE person;

查詢

hive> select * from person;
biansutao       ["beijing","shanghai","tianjin","hangzhou"]
linan   ["changchu","chengdu","wuhan"]
Time taken: 0.355 seconds
hive> select name from person;
linan
biansutao
Time taken: 12.397 seconds
hive> select work_locations[0] from person;
changchu
beijing
Time taken: 13.214 seconds
hive> select work_locations from person;   
["changchu","chengdu","wuhan"]
["beijing","shanghai","tianjin","hangzhou"]
Time taken: 13.755 seconds
hive> select work_locations[3] from person;
NULL
hangzhou
Time taken: 12.722 seconds
hive> select work_locations[4] from person;
NULL
NULL
Time taken: 15.958 seconds

2. Map 的使用

建立資料庫表

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

要入庫的資料

biansutao '數學':80,'語文':89,'英語':95
jobs '語文':60,'數學':80,'英語':99

入庫資料

LOAD DATA LOCAL INPATH '/home/hadoop/score.txt' OVERWRITE INTO TABLE score;

查詢

hive> select * from score;
biansutao       {"數學":80,"語文":89,"英語":95}
jobs    {"語文":60,"數學":80,"英語":99}
Time taken: 0.665 seconds
hive> select name from score;
jobs
biansutao
Time taken: 19.778 seconds
hive> select t.score from score t;
{"語文":60,"數學":80,"英語":99}
{"數學":80,"語文":89,"英語":95}
Time taken: 19.353 seconds
hive> select t.score['語文'] from score t;
60
89
Time taken: 13.054 seconds
hive> select t.score['英語'] from score t;
99
95
Time taken: 13.769 seconds

修改map欄位的分隔符

Storage Desc Params:	 	 
	colelction.delim    	##                  
	field.delim         	\t                  
	mapkey.delim        	=                   
	serialization.format	\t

可以通過desc formatted tableName檢視錶的屬性。
hive-2.1.1中，可以看出colelction.delim，這裡是colelction而不是collection，hive裡面這個單詞寫錯了，所以還是要按照錯誤的來。

alter table t8 set serdepropertyes('colelction.delim'=',');

3. Struct 的使用

建立資料表

CREATE TABLE test(id int,course struct<course:string,score:int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';

資料

1 english,80
2 math,89
3 chinese,95

入庫

LOAD DATA LOCAL INPATH '/home/hadoop/test.txt' OVERWRITE INTO TABLE test;

查詢

hive> select * from test;
OK
1       {"course":"english","score":80}
2       {"course":"math","score":89}
3       {"course":"chinese","score":95}
Time taken: 0.275 seconds
hive> select course from test;
{"course":"english","score":80}
{"course":"math","score":89}
{"course":"chinese","score":95}
Time taken: 44.968 seconds
select t.course.course from test t; 
english
math
chinese
Time taken: 15.827 seconds
hive> select t.course.score from test t;
80
89
95
Time taken: 13.235 seconds

4. 不支援組合的複雜資料型別

我們有時候可能想建一個複雜的資料集合型別，比如下面的a欄位，本身是一個Map，它的key是string型別的，value是Array集合型別的。

建表

create table test1(id int,a MAP<STRING,ARRAY<STRING>>)
row format delimited fields terminated by '\t' 
collection items terminated by ','
MAP KEYS TERMINATED BY ':';

匯入資料

1 english:80,90,70
2 math:89,78,86
3 chinese:99,100,82

LOAD DATA LOCAL INPATH '/home/hadoop/test1.txt' OVERWRITE INTO TABLE test1;

這裡查詢出資料：

hive> select * from test1;
OK
1	{"english":["80"],"90":null,"70":null}
2	{"math":["89"],"78":null,"86":null}
3	{"chinese":["99"],"100":null,"82":null}

可以看到，已經出問題了，我們意圖是想"english":["80", "90", "70"]，實際上把90和70也當作Map的key了，value值都是空的。分析一下我們的建表語句，collection items terminated by ','制定了集合型別（map, struct, array）資料元素之間分隔符是", "，實際上map也是屬於集合的，那麼也會按照逗號分出3個key-value對；由於MAP KEYS TERMINATED BY ':'定義了map中key-value的分隔符是":"，第一個“english”可以準確識別，後面的直接把value置為"null"了。

相關文章