hive array与struct使用

最近在处理流式数据在读取redis的时候很慢，主要原因是查redis的时候用的模糊查询，key是使用svn提交的代码路径，而每来一条记录都要取redis查一遍，这样性能就会很差，查询字段是类名（例如：SbRollBackHandle.java）,为了不使用模糊查询，线下基于hive svn log进行了分析寻找合适的key，驱动业务调整日志打印格式;在分析svn log的时候发现用了一些复杂的结构类型( 例如：struct，array)，基于hive sql进行了整理，表结构如下：
desc work_ods.ods_svn_log
±-------------------------±---------------------------------------------------
| col_name | data_type
±-------------------------±---------------------------------------------------
| svn_url | string
| ip | string
| repo | string
| revision | string
| author | string
| submit_time | string
| change_files | array<struct<change_type:string,change_file:string,change_line:string>>
| change_dir | string
| add_lines | string
| msg | string
| dt | string
| | NULL
| # Partition Information | NULL
| # col_name | data_type
| | NULL
| dt | string
±-------------------------±---------------------------------------------------

1、首先要array扁平话，
SELECT author,submit_time,adid
FROM work_ods.ods_svn_log LATERAL VIEW explode(change_files) change_file AS adid
where dt=201908

2、获取struct 结构里change_file路径
select t.author,
t.submit_time,
t.adid.change_file
from (SELECT author,submit_time,adid
FROM work_ods.ods_svn_log LATERAL VIEW explode(change_files) change_file AS adid
where dt=201908) t;
或者
SELECT author,submit_time,adid.change_file
FROM work_ods.ods_svn_log LATERAL VIEW explode(change_files) change_file AS adid
where dt=201908

3、获取提交人/提交时间/模块/类名
select author,
submit_time,
case when change_file like ‘%trunk%’ then split(change_file,’\/’)[3]
when change_file like ‘%branches%’ then split(change_file,’\/’)[4]
end model,
reverse(split(reverse(change_file),"\/")[0]) className,
reverse(substr(reverse(change_file),0,instr(reverse(change_file),’/’)-1)) className2,–hive查找字符第一次出现的位置
change_file
from (select t.author,
t.submit_time,
t.adid.change_file
from (SELECT author,submit_time,adid
FROM work_ods.ods_svn_log LATERAL VIEW explode(change_files) change_file AS adid
where dt=201908) t ) f
where change_file like ‘%.java’

4、综合分析模块/类名之类对应关系