hive複雜結構之array,map,struct

hive提供了複合數據類型array,map,struct,下面來依次介紹

一.array介紹

語法：array 例如：[1,2,3]

array<string> 例如 ['a','b','c']/<string>

描述：數組是一組具有相同類型和名稱的變量的集合。這些變量成為數組的元素，每個數組元素都有一個編號，編號從0開始。例如，數組['Jack','David'],那麼第二個元素可以用數組名[1]進行引用。

創建數組

<code>create table array_test
(
  name string
  ,dept_id array
  ,status array<string>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\t'
COLLECTION ITEMS TERMINATED BY ','
;/<string>/<code>

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\t' 設置字段間的分隔符為tab

COLLECTION ITEMS TERMINATED BY ',' 設置字段中每個元素間的分隔符為逗號，數組中必須使用該設置

準備數據：array_test.txt

<code>zs\t1,2,3,4\t\tok,return,reject,fail
ls\t11,22,33,41\t\tok,reject,fail
wl\t100/<code>

加載數據

<code>LOAD DATA LOCAL INPATH '/Users/admin/Documents/bigdata/test_file/array_test.txt' OVERWRITE INTO TABLE array_test;/<code>

查詢數據

<code>hive (test)> select * from array_test;
array_test.name\tarray_test.dept_id\tarray_test.status
zs\t[1,2,3,4]\t["ok","return","reject","fail"]
ls\t[11,22,33,41]\t["ok","reject","fail"]
wl\t[100]\tNULL/<code>

查詢數組字段的長度：size(array)

<code>hive (test)> select name,size(dept_id),size(status) from array_test;
name\t_c1\t_c2
zs\t4\t4
ls\t4\t3
wl\t1\t-1/<code>

注意：由上面的結果可看出當數組為null時，查出的長度為-1

查詢數組字段中某一位置的元素：array[index]

<code>hive (test)> select name,dept_id[0],status[2] from array_test;
name\t_c1\t_c2
zs\t  1\t   reject 

ls\t  11\t fail
wl\t  100\t NULL/<code>

查詢數組中是否包含某元素：array_contains(數組名，值)

array_contains方法返回一個Boolean對象，可以配合if函數使用，例如 if(array_contains(cloumn,'A'),'包含A','不包含A')

<code>hive (test)> select name,array_contains(status,'ok') from array_test;
name\t_c1
zs\ttrue
ls\ttrue
wl\tfalse
hive (test)> select name,if(array_contains(status,'ok'),'yes','no') from array_test;
name\t_c1
zs\tyes
ls\tyes
wl\tno/<code>

查詢status中包含'ok'的數據

<code>hive (test)> select * from array_test where array_contains(status,'ok');
array_test.name\tarray_test.dept_id\tarray_test.status
zs\t[1,2,3,4]\t["ok","return","reject","fail"]
ls\t[11,22,33,41]\t["ok","reject","fail"]/<code>

此處注意，如果不使用該函數，而直接使用 “值 in/not in 數組”，會報錯，會報類型不匹配的錯誤

<code>hive (test)> select * from array_test where status in ('ok');
FAILED: SemanticException Line 0:-1 Wrong arguments ''ok'': The arguments for IN should be the same type! Types are: {array<string> IN (string)}/<string>/<code>

查詢數組中的每一個元素：explode()

注意：explode()函數只是生成了一個數據的展示方式，無法在表中產生一個新的數據列，即select name,explode(dept_id) from array_test; 會報錯的

<code>col
1
2
3
4
11
22
33
41
100
hive (test)> select name,explode(dept_id) from array_test;
FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions/<code>

要想展示其他列，可以使用下面的lateral view explode(array)來實現

一行轉多行（行轉列）：lateral view explode(array)

lateral view 會將explode生成的結果放到一個虛擬表中，然後這個虛擬表會和輸入行即每個name進行join,來達到數據聚合的目的。

<code>hive (test)> select  name ,a_dept_id from array_test
           > lateral view explode(dept_id) a as a_dept_id  -- 要進行聚合的虛擬表，lateral view explode(字段) 虛擬表名 as 虛擬表字段  
           > ;
name\ta_dept_id
zs\t1
zs\t2
zs\t3
zs\t4
ls\t11
ls\t22
ls\t33
ls\t41
wl\t100/<code>

split()將字符串轉成字符串數組

語法: split(string str, string pat)
返回值: array
說明: 按照pat字符串分割str，會返回分割後的字符串數組

<code>hive (test)> select * from split_test;
OK
split_test.id\tsplit_test.info
1\ta,b,c
2\td,e,f
3
4\tNULL/<code>

將split_test表中的info字段轉成數組

<code>hive (test)> select id,split(info,',') as array_info, size(split(info,',')) as array_info_size   from split_test;
id\tarray_info\tarray_info_size
1\t["a","b","c"]\t3
2\t["d","e","f"]\t3
3\t[""]\t        1
4\tNULL\t       -1/<code>

二.map介紹

語法：

map(k1,v1,k2,v2,...)

描述：

map是一組鍵-值對元組集合,map類型的數據可以通過'列名['key']的方式訪問

創建map

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

MAP KEYS TERMINATED BY ：key value分隔符

準備數據: map_test.txt

<code>1\tchinese:80,math:90,english:100
2\tchinese:95,math:70
3\t/<code>

<code>LOAD DATA LOCAL INPATH '/Users/admin/Documents/bigdata/test_file/map_test.txt' OVERWRITE INTO TABLE map_test;
/<code>

查詢數據

<code>hive (test)> select * from map_test;
map_test.id\tmap_test.course
1\t{"chinese":80,"math":90,"english":100}
2\t{"chinese":95,"math":70}
3\t{}/<code>

查詢map的長度：size(map)

<code>hive (test)> select id,size(course) from map_test;
id\t_c1
1\t  3
2\t  2
3\t  0/<code>

查詢map字段中的某個元素：map['parame']

<code>hive (test)> select id,course['math'] as math from map_test;
OK
id\tmath
1\t90
2\t70
3\tNULL/<code>

查詢map中所有的key， 返回值類型為 array：map_keys(map)

<code>hive (test)> select id,map_keys(course)  as key from map_test;
id\tkey
1\t["chinese","math","english"]
2\t["chinese","math"]
3\t[]/<code>

查詢map中所有的value，返回值類型為array：map_values(map)

<code>hive (test)> select id,map_values(course)  as value from map_test;
id\tvalue
1\t[80,90,100]
2\t[95,70]
3\t[]/<code>

判斷map中是否包含某個key值：array_contains(map_keys(map))

<code>hive (test)> select * from map_test where array_contains(map_keys(course),'math');
map_test.id\tmap_test.course
1\t{"chinese":80,"math":90,"english":100}
2\t{"chinese":95,"math":70}/<code>

一行轉多行（行轉列）：lateral view explode(map)

<code>hive (test)> select id,subject,score from map_test
           > lateral view explode(course) a as subject,score;
id\tsubject\tscore
1\tchinese\t80
1\tmath\t  90
1\tenglish\t100
2\tchinese\t95
2\tmath\t  70/<code>

三.struct介紹

語法：

struct(val1, val2, val3, ...) ,struct內部的元素類型可以為array,map,struct複雜結構

描述：

和C語言中的struct或者對象類似，都可以通過"點"符號訪問元素內容。例如，如果某個列的數據類型為struct{first string,last string},那麼第一個元素可以通過字段名.first來引用

創建struct表

<code>create table struct_test
(
  id int
  ,info struct<string>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
;/<string>/<code>

準備數據：

<code>LOAD DATA LOCAL INPATH '/Users/admin/Documents/bigdata/test_file/struct_test.txt' OVERWRITE INTO TABLE struct_test;/<code>

查詢數據

<code>hive (test)> select * from struct_test;;
struct_test.id\tstruct_test.info
1\t{"name":"zs","age":18}
2\t{"name":"ls","age":22}
3\t{"name":"ww","age":30}/<code>

struct 無size()函數

<code>hive (test)> select id,size(info) from struct_test;
FAILED: SemanticException [Error 10016]: Line 1:15 Argument type mismatch 'info': "map" or "list" is expected at function SIZE, but "struct<string>" is found/<string>/<code>

查詢struct中的元素：struct.元素名稱

<code>hive (test)> select id,info.name,info.age from struct_test;
id\tname\tage
1\t  zs\t18
2\t  ls\t22
3\t  ww\t30/<code>

閱讀更多 大數據之路 的文章

關鍵字: 結構 Hive 複雜