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 结构 复杂