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';
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'; /* 行分隔符 */
其中需要解释的地方其实只有两个：
①. collection items terminated by ‘_’ ，在hive中，map、array、struct都使用collection items terminated by来指定，所以只能共用一个分隔符。
②. lines terminated by ‘\n’， 不写也行，行分隔符默认就是 \n
3、实例数据
{
"class": "高三",
"student": ["李四" , "王五"] , //列表 Array,
"user_info": { //键值 Map,
"李四": 18 ,
"王五": 19
}
"position": { //结构 Struct,
"province": "山东省",
"city": "临沂市",
"district": "莒南县"
}
}
整理为hdfs数据如下：
高三,李四_王五,李四:18_王五:19,山东省_临沂市_莒南县
加载数据到hive 表中:
load data local inpath "/home/hadoop/wangtongbo/class_info.txt" into table test.class_info;
访问方式,访问map：
select
student[0], /* 这是访问array */
user_info['李四'], /* 这是访问map */
position.city /* 这是访问struct */
from test.class_info;
二、复杂集合类型组合使用
(一)、 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;
2.插入数据
新建文本数据：
1 a1-a2-a3,a11-a22-a33,a111-a222-a333
2 a1-a2-a3,a11-a22-a33
3 a1-a2,a11--a33,-a222-
加载数据：
LOAD DATA [LOCAL] inpath '/root/arr_struct.txt' INTO TABLE test.array_struct;
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;
查询结果:
SELECT id,values[0],values[0].k1,values[1],values[1].k2,values[2],values[2].k3,values FROM test.array_struct;
（二）、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';
以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’
当不知道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
}
}
以看到，这一行当中，其实包含了相当多的信息。
为了能够获取任意一行中的任意一个字段，而不是通过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’
在这里插入图片描述
通过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’
注：在宽表建设过程中，使用了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’=’,’)
…
dw_kubiao 表定义
…
ROW FORMAT SERDE
‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’
WITH SERDEPROPERTIES (
‘colelction.delim’=’|’,
‘field.delim’=’,’,
‘mapkey.delim’=’:’,
‘serialization.format’=’,’
)
string 对应字段以| 分隔成 不同 key : struct（让后将 struct 中的分隔符 由原来的@换成\004–>由 单独mr实现）
注：map中多个元素的分隔符以及struct多个元素的分隔 符，目前hive提供的语法是无法都更改的，只能够更改一个。 剩下的分隔符则按照 ascii码 1- 8的顺序进行使用。 当指定 colection的分隔符为 ’ | ', 实际上是指定了 map 结构的元素分隔 符，那么 struct元素的分隔符则默认为‘\004’, 因此，只需要把 struct的分隔符改为 ‘\004’ 即 可。
注: map 中分隔方式 暂时没找到修改语句。采用修改元数据然后 重新加载hive 分区的方式实现
5016
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
