hive复杂数据类型嵌套

淩亂°似流年 2022-02-16 08:02 334阅读 0赞

需求：在创建表的时候有一列类型为array<struct:”id”:string,”l”:string>想要自己造数据向表里insert格式应该怎么写呢？  
建表语句如下：

CREATE EXTERNAL TABLE \`test.test\_imp06\`(  
\`exps\` array<struct<id:string,l:string>> COMMENT ‘ceshi')  
1  
2  
insert into 的select语句

insert into test.test\_imp06  
select array(named\_struct('id','exp6','l','layer\_3'),named\_struct('id','exp7','l','layer\_6')) as exps  
1  
2  
结果：

还有一种写法如下：

select array(struct('id','exp6','l','layer\_3'),struct('id','exp7','l','layer\_6')) as exps  
1  
结果：

两种方法，但是如果使用struct会导致和之前的列名定义的列名无法对应。。最后还是使用name\_struct喽

\-------------------------

Hive   
hive 除了支持基本的数据结构  
hive 复杂的数据结构:

{
     array
     map
     struct
    }

数据格式

221190xxx9	0401:0.30 0402:0.81 1303:0.23 1502:0.21 1503:0.11 0307:0.17 1003:0.35 	
    22119xxxx2	0508:0.58 0402:0.25 0403:0.24 0405:0.30 0503:0.55 1502:0.26 0208:0.29 	
    22119xxx63	0405:0.18 0408:0.22 0905:0.89 1503:0.16 0301:0.14 0307:0.12 0208:0.17

hive 表结构设计：

create table genomeplatform.user_tags (uid string ,tag array<struct<tagid:string,weight:string>>)
    
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '\t'
    COLLECTION ITEMS TERMINATED BY ' '
    MAP KEYS TERMINATED BY ':'
    STORED AS TEXTFILE;

select ：

100xxxx0	[{"tagname":"0101","weight":"0.44"},{"tagname":"1103","weight":"0.90"},{"tagname":"","weight":null}]
    100xxxx7	[{"tagname":"0508","weight":"1.00"},{"tagname":"","weight":null}]
    100xxxx9	[{"tagname":"1701","weight":"0.79"},{"tagname":"1606","weight":"0.62"},{"tagname":"","weight":null}]
    130xxxx9	[{"tagname":"1503","weight":"1.00"},{"tagname":"","weight":null}]

[https://wwangcg.iteye.com/blog/1829402][https_wwangcg.iteye.com_blog_1829402]

[https://www.cnblogs.com/leodaxin/p/9898080.html][https_www.cnblogs.com_leodaxin_p_9898080.html]

[https://blog.csdn.net/lykke2012/article/details/84407971][https_blog.csdn.net_lykke2012_article_details_84407971]

[https_wwangcg.iteye.com_blog_1829402]: https://wwangcg.iteye.com/blog/1829402
[https_www.cnblogs.com_leodaxin_p_9898080.html]: https://www.cnblogs.com/leodaxin/p/9898080.html
[https_blog.csdn.net_lykke2012_article_details_84407971]: https://blog.csdn.net/lykke2012/article/details/84407971