需求:在创建表的时候有一列类型为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