hive中的in查询是什么

inline

前情提要：inline无法作用于map,array(map)

关于inline：在横表纵表转换一节已经试过，map无法使用inline；

在这里将map转成array，发现还是无法用inline，看来inline只适用array(struct)格式；

# map转array，还是不能用lateral view inline；inline只适用于array(struct)格式
sc.sql(''' select id
,array(str_to_map(concat_ws(',',collect_set(concat_ws(':',prod_nm,cast(bal as string))))))
from test_youhua.zongbiao 
group by id ''')
# 查询结果已经转成了ARRAY
1    [{"baoxian":"1.2","cunkuan":"1.3","jijin":"1.1"}]
2    [{"baoxian":"2.34","cunkuan":"2.1","jijin":"2.67"}]

# 还是不能用inline
sc.sql(''' select map_tmp_tbl.id,c1 from  (
    select id
            ,array(str_to_map(concat_ws(',',collect_set(concat_ws(':',prod_nm,cast(bal as string)))))) as array_map_col
        from test_youhua.zongbiao 
        group by id
) as map_tmp_tbl  lateral view inline(map_tmp_tbl.array_map_col) t1 as c1  ''').show()

# 报错，inline使用的格式为array(struct)，这里格式array(map)不匹配
AnalysisException: "cannot resolve 'inline(map_tmp_tbl.`array_map_col`)' due to data type mismatch: input to function inline should be array of struct type, not ArrayType(MapType(StringType,StringType,true),false);

看了这篇怎么感觉可以应用于array(map)???

不一样的，这个示例named_struct_1字段事先就存成了struct类型。

那接下来老老实实建一个array(struct)格式字段来处理吧

（1）数据准备-建表insert-select：直接将map转array后不能用inline的数据存成array(struct)：不能，会报错字段类型不匹配。

这里有点奇怪，hive是schema on read，insert的时候会检查字段格式是否一致吗？？

比如parquet不支持date格式，insert进去也只是显示空字段，而不是一开始就insert报错

# 建表
create table if not exists test_youhua.test_array_struct_inline(
custom_id int comment "客户id",
all_bal array<struct<baoxian:float, cunkuan:float, jijin:float>> comment '资产配置'
) 
comment "array_struct_客户资产配置表"
;
# 插入数据
insert overwrite test_youhua.test_array_struct_inline
select id
,array(str_to_map(concat_ws(',',collect_set(concat_ws(':',prod_nm,cast(bal as string))))))
from test_youhua.zongbiao 
group by id

# 报错：字段类型不一致
FAILED: SemanticException [Error 10044]: Line 1:23 Cannot insert into target table 
because column number/types are different 'test_array_struct_inline': 
Cannot convert column 1 from array<map<string,string>> to array<struct<baoxian:float,cunkuan:float,jijin:float>>.

（2）数据准备-直接load文件到test_youhua.test_array_struct_inline

# 文件准备 test_array_struct_inline, xftp到Linux
1    [{"baoxian":"1.2","cunkuan":"1.3","jijin":"1.1"}]
2    [{"baoxian":"2.34","cunkuan":"2.1","jijin":"2.67"}]

# load 到HDFS
hdfs dfs -put /opt/module/hive/my_input/test_array_struct_inline  hdfs:///user/hive/warehouse/test_youhua.db/test_array_struct_inline

# 查询hive数据，数据确实已经load上去了，但是读不出来
sc.sql(""" select * from test_youhua.test_array_struct_inline""").show()
+---------+-------+
|custom_id|all_bal|
+---------+-------+
|     null|   null|
|     null|   null|
+---------+-------+

# 猜测是分隔符的原因，重新指定一下分隔符
sc.sql(""" drop table test_youhua.test_array_struct_inline""")
sc.sql("""create table if not exists test_youhua.test_array_struct_inline(
custom_id int comment "客户id",
all_bal array<struct<baoxian:float, cunkuan:float, jijin:float>> comment '资产配置'
) 
comment "array_struct_客户资产配置表"
row format delimited fields terminated by ','
collection items terminated by '_'
""")

!hdfs dfs -put /opt/module/hive/my_input/test_array_struct_inline  hdfs:///user/hive/warehouse/test_youhua.db/test_array_struct_inline

sc.sql(""" select * from test_youhua.test_array_struct_inline""").show()

#无论怎样改都不行，读不出来，可能是array嵌套struct，影响了分隔符指定的缘故(其实是因为json格式需要导入serde包)

 （3）数据准备-用json包指定row format读文件

其实之前数据无法正常read是因为json的分隔符的原因，需要导入jsonserde包

参考：Hive学习小记-（16）hive加载解析json文件 稍微修改了一下文件：

{"custom_id":"1","all_bal":[{"baoxian":"1.2","cunkuan":"1.3","jijin":"1.1"}]}
{"custom_id":"2","all_bal":[{"baoxian":"2.34","cunkuan":"2.1","jijin":"2.67"}]}

hive> add jar /opt/module/hive/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.1.jar
hive> create table if not exists test_youhua.test_array_struct_inline(
> custom_id string comment "客户id",
> all_bal array<struct<baoxian:string, cunkuan:string, jijin:string>> comment '资产配置'
> ) 
> comment "array_struct_客户资产配置表"
> row format serde 'org.apache.hive.hcatalog.data.JsonSerDe';
OK
Time taken: 0.09 seconds
hive> select * from test_youhua.test_array_struct_inline;
OK
Time taken: 0.089 seconds

# 数据导入并且读取成功
hive> select * from test_youhua.test_array_struct_inline;
OK
1    [{"baoxian":"1.2","cunkuan":"1.3","jijin":"1.1"}]
2    [{"baoxian":"2.34","cunkuan":"2.1","jijin":"2.67"}]
Time taken: 0.107 seconds, Fetched: 2 row(s)

#注意这里字段类型全部改为string，否则select会报错：
Failed with exception java.io.IOException:org.apache.hadoop.hive.serde2.SerDeException: org.codehaus.jackson.JsonParseException: Current token (VALUE_STRING) not numeric, can not use numeric value accessors
at [Source: java.io.ByteArrayInputStream@ab327c; line: 1, column: 41]

（4）用inline可以打开array(struct)，对比explode只是打开array

参考：

# 原始数据
hive> select * from test_youhua.test_array_struct_inline;
OK
1    [{"baoxian":"1.2","cunkuan":"1.3","jijin":"1.1"}]
2    [{"baoxian":"2.34","cunkuan":"2.1","jijin":"2.67"}]
Time taken: 0.103 seconds, Fetched: 2 row(s)

#这个时候如果想读取jijin、baoxian、cunkuan的余额，需要：
#这是在array的元素只有一条struct数据的情况，如果有多条struct元素，通过array[i]的形式来寻找某个key的value会比较困难，这时候就要借助inline和explode来将多个struct的某个key对应的value转到一列
hive> select all_bal[0].jijin,all_bal[0].baoxian,all_bal[0].cunkuan from test_youhua.test_array_struct_inline;
OK
1.1    1.2    1.3
2.67    2.34    2.1
Time taken: 0.587 seconds, Fetched: 2 row(s)

#用 inline 将多个struct的某个key对应的value转到一列
hive> select tmp.custom_id,c1,c2,c3 from test_youhua.test_array_struct_inline  as tmp lateral view inline(tmp.all_bal) t1 as c1,c2,c3;
OK
1    1.2    1.3    1.1
2    2.34    2.1    2.67
Time taken: 0.093 seconds, Fetched: 2 row(s)

#对比用explode来转，explode只能打开一层,即去掉了array的[]
hive> select tmp.custom_id,c1 from test_youhua.test_array_struct_inline as tmp lateral view explode(tmp.all_bal) t1 as c1;
OK
1    {"baoxian":"1.2","cunkuan":"1.3","jijin":"1.1"}
2    {"baoxian":"2.34","cunkuan":"2.1","jijin":"2.67"}

#explode还需再加上struct.key来进一步取key对应的value值；这么看inline能够比explode打开更深一层，inline可以直接取到value，explode还要再通过struct.key形式来取value
hive> select tmp.custom_id,c1.jijin from test_youhua.test_array_struct_inline as tmp lateral view explode(tmp.all_bal) t1 as c1; 
OK
1    1.1
2    2.67
Time taken: 0.122 seconds, Fetched: 2 row(s)

#参考链接里这种写法不行，需要数据明确key、value
select tmp.custom_id,c1.value from test_youhua.test_array_struct_inline as tmp lateral view explode(tmp.all_bal) t1 as c1 where c1.key="jijin";
# 报错：
RuntimeException cannot find field key(lowercase form: key) in [baoxian, cunkuan, jijin]

 

tips

（1）org.apache.hive.hcatalog.data.JsonSerDe 对复杂类型支持不足

参考：

（2）insert数据到array(struct)-用named_struct

参考这个：

以及报错解决：

insert into test_youhua.test_array_struct_inline 
select "4",array(named_struct('baoxian','1.46','cunkuan','1.46','jijin','1.46'));
# 报错
ParseException line 1:124 Failed to recognize predicate '<EOF>'. Failed rule: 'regularBody' in statement

#报错解决，建临时表tmp,加from改写，这里要注意struct里面字段顺序
with tmp as 
(select "3",array(named_struct('baoxian','1.45','cunkuan','1.45','jijin','1.45')))
insert into test_youhua.test_array_struct_inline
select * from tmp;

select * from test_youhua.test_array_struct_inline
#数据插入成功
3    [{"baoxian":"1.45","cunkuan":"1.45","jijin":"1.45"}]
1    [{"baoxian":"1.2","cunkuan":"1.3","jijin":"1.1"}]
2    [{"baoxian":"2.34","cunkuan":"2.1","jijin":"2.67"}]

# 用struct来select可以，但是无法insert到指定的array(struct)中
with tmp as 
(select "6",array(struct('1.45','1.45','1.45')))
insert into test_youhua.test_array_struct_inline
select * from tmp;
# struct报错，列名无法对应
Cannot insert into target table because column number/types are different 'test_array_struct_inline': Cannot convert column 1 from array<struct<col1:string,col2:string,col3:string>> to array<struct<baoxian:string,cunkuan:string,jijin:string>>.

 

（3）map、array、struct读数据的方式

select array[1],map['xiao song'],struct.city from test