Hive建表，字段中使用集合map、array、struct类型时，建表语句应如何指定集合数据中的分隔符？_hive建表字段为array

Hive中建表，存在map、array、和struct集合类型，那如果同时存在这三种格式时，建表语句的分隔符应该怎么指定呢？

一、实例：

1、建表语句

create external table test.class_info(
	class string,
	student array<string>,
	user_info map<string, int>,
	position struct<province:string, city:string, district:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
location '/big-data/test/user_info';
1
2
3
4
5
6
7
8
9
10
11
12

2、参数解析

row format delimited fields terminated by ','   /* 列分隔符 */
collection items terminated by '_'         /*  MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)  */
map keys terminated by ':'    /* MAP 中的 key 与 value 的分隔符 */
lines terminated by '\n';       /* 行分隔符  */
1
2
3
4

  其中需要解释的地方其实只有两个：
  ①. collection items terminated by ‘_’ ，在hive中，map、array、struct都使用collection items terminated by来指定，所以只能共用一个分隔符。
  ②. lines terminated by ‘\n’， 不写也行，行分隔符默认就是 \n
1
2
3

3、实例数据

{
	"class": "高三",
	"student": ["李四" , "王五"] , //列表 Array,
	"user_info": { //键值 Map,
		"李四": 18 ,
		"王五": 19
	}
	"position": { //结构 Struct,
	    "province": "山东省",
	    "city": "临沂市",
		"district": "莒南县"
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13

整理为hdfs数据如下：

高三,李四_王五,李四:18_王五:19,山东省_临沂市_莒南县
1

加载数据到hive 表中:

load data local inpath "/home/hadoop/wangtongbo/class_info.txt" into table test.class_info;
1

访问方式,访问map：

select 
	student[0], /* 这是访问array */
	user_info['李四'], /* 这是访问map */
	position.city  /* 这是访问struct */
from test.class_info;
1
2
3
4
5

二、复杂集合类型组合使用

(一)、 array<struct<字段1:字段1类型,字段2:字段2类型…>>

1.创建测试表

CREATE TABLE IF NOT EXISTS TEST.array_struct ( 
    id string,
    values ARRAY<STRUCT<k1:String,
                        k2:String,
                        k3:String
                       >
                > 
) COMMENT '组合类型测试表' 
ROW FORMAT delimited fields terminated BY '\t' 
collection items terminated BY ',' 
MAP KEYS TERMINATED BY '-' 
STORED AS TEXTFILE;
1
2
3
4
5
6
7
8
9
10
11
12

2.插入数据

新建文本数据：

1	a1-a2-a3,a11-a22-a33,a111-a222-a333
2	a1-a2-a3,a11-a22-a33
3	a1-a2,a11--a33,-a222-
1
2
3

加载数据：

LOAD DATA [LOCAL] inpath '/root/arr_struct.txt' INTO TABLE test.array_struct;
1

SQL插入单条数据4 a1--,-a22-,--

INSERT INTO TABLE test.array_struct 
SELECT '4',array(named_struct('k1','a1','k2','','k3',''),named_struct('k1','','k2','a22','k3',''),named_struct('k1','','k2','','k3','')) AS values;
1
2

查询结果:

SELECT id,values[0],values[0].k1,values[1],values[1].k2,values[2],values[2].k3,values FROM test.array_struct;	
1

（二）、map和struct 结合

1.建表语句

create external table test.map_struct (
`borrow_repay_record` map<string, struct<duedate: timestamp, 	
	              	                   status :string, 
	              	                   repaid_type:string, 
	              	                   principal :string, 
	              	                   interest:string, 
	              	                   repaidtime:string, 
	              	                   amount:string
            	                       epay0verDueInterest:string, 
	              	                   inRepayFee:string, 
	              	                   finalprincipal:string, 
	              	                   initialprincipal:string, 
	              	                   repayoverduemgmtfee:double>> COMMENT '还款计划',
`repaid_principal` struct<common_lrepay_principal: double, 
                        in_repay_principal: double, 
                        bad_repay_principal : double, 
                        over Ldue_ repay_ principal:double> COMMENT '已还本金(异常标的未剔除)',
`repaid_interest` struct<common_repay_linterest: double,  
                       bad_repay_interest: double,
                       over_due_repay_interest : double, 
                       repaid_by_lguarantor_interest: double, 	 
                       in_repay_interest:double> COMMENT '已还款利息(异常标的未剔除)',
`unrepay_principal` struct<common_unrepay_principal:double,
						 over_due_unrepay_principal:double,
						 bad_unrepay_principal:double> COMMENT '已还款利息(异常标的未剔除)',
unrepay_interest` struct<common_unrepay_interest:double,
						over_due_unrepay_interest:double,
						bad_unrepay_interest:double> COMMENT '待还款利息(异常标的未剔除)'
)
STORED AS PARQUERT
LOCATION '/big-data/test/map_struct';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

以borrow_repay_record为例： 其key为phaseNumber,value为一个struct。
map(string,struct<…>) 显然，value的类型可以是复杂数据类型，这就形成了复杂数据类型的嵌套。其语法仍然符合各个基本类型的语法规则如，取出其对应的map 的key 为load 的value中对应 duedate的值
语法为:

select borrow_repay_recore[‘load’].duedate from dw_kuanbiao where dt=‘2019-02-12’
1

当不知道key（或者不关心key），如何来取出满足需求的value？ 这就用到了map的展开（将一行变为多行）
我们取出user_id为100000的记录对应的borrow_repay_record(注意user_id取出的值存在多行情况 )
结果结构类似

{
	"19": {
			"duedate": "2015-04-23 14:51:42",
			"repayoverduemgmtfee": null
			},
	"18": {
			"duedate": "2015-03-23 14:51:42",
			"repayoverduemgmtfee": null
			},
	"15": {
			"duedate": "2014-12-23 14:51:42",
			"repayoverduemgmtfee":null
			},
	"14": {
			"duedate": "2014-11-23 14:51:42",
			"repayoverduemgmtfee": null
			}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

以看到，这一行当中，其实包含了相当多的信息。
为了能够获取任意一行中的任意一个字段，而不是通过key索引来寻找该字段，我们需要将上述一行，按照key ，value的形式打散，化为多行，并能够与表中的其他字段进行融合。而hive则提供了相关函数。explode()函数，能够将一行打散为多行，但该函数无法将打散出来的行与表的其他字段进行融合。LATERAL VIEW 则能够弥补这一缺点，二者一般配合使用。
举例如下：

SELECT user_id,phaseNumber,value from dw_loan LATERAL VIEW explode(borrow_repay_record) adTable AS phaseNumber,value where dt = ‘2019-01-29’ and user_id = ‘100000’
1

在这里插入图片描述

通过LATERAL VIEW explode(borrow_repay_record) adTable AS phaseNumber,value 可以将map中的数据按行切分，并与原来的行中连接，形成多行。可以认为， from后面就是一个表，和平常用的表并无区别。
那么，如果要算某个字段的和的时候，则直接使用就ok：
如，要计算本金的和，map 中value的某个字段值的情况：

select sum(value.principal) from dw_kubiao LATERAL VIEW explode(borrow_repay_recore) adTable As phaseNumeber,value where user_id =‘100000’
1

注：在宽表建设过程中，使用了hive的复杂数据类型，如map, struct,以及复杂数据类型的嵌套，如map<string, struct> 等，

虽然hive复杂数据类型能够让单行记录容纳更多的信息，但也导致了加载过程的复杂。为了简化这些包含复杂数据类型的表的加载过程，采用了中间表。 即先把数据按照最终表的数据结构导入到中间表，再利用MR清洗一遍中间表，使其满足复杂数据类型的要求。
（即先将数据导入到 tmep_kubiao中----》 dw_kubiao中）

其中map<string,struct> 由原来string类型替换而来

temp_kubiao 表定义

…
COMMENT ‘标的信息表’
ROW FORMAT SERDE
‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’
WITH SERDEPROPERTIES (
‘field.delim’=’,’,
‘serialization.format’=’,’)
…
1
2
3
4
5
6
7
8

dw_kubiao 表定义

…
ROW FORMAT SERDE
‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’
WITH SERDEPROPERTIES (
‘colelction.delim’=’|’,
‘field.delim’=’,’,
‘mapkey.delim’=’:’,
‘serialization.format’=’,’
)
1
2
3
4
5
6
7
8
9

string 对应字段以| 分隔成 不同 key : struct（让后将 struct 中的分隔符 由原来的@换成\004–>由 单独mr实现）

注：map中多个元素的分隔符以及struct多个元素的分隔 符，目前hive提供的语法是无法都更改的，只能够更改一个。 剩下的分隔符则按照 ascii码 1- 8的顺序进行使用。 当指定 colection的分隔符为 ’ | ', 实际上是指定了 map 结构的元素分隔 符，那么 struct元素的分隔符则默认为‘\004’, 因此，只需要把 struct的分隔符改为 ‘\004’ 即 可。

注: map 中分隔方式 暂时没找到修改语句。采用修改元数据然后 重新加载hive 分区的方式实现