hive string, map, struct类型的建表和导入数据语句

本文转载于 https://blog.51cto.com/u_14405/6419362，https://blog.csdn.net/tototuzuoquan/article/details/115493697 和 https://blog.csdn.net/weixin_43597208/article/details/117450579。

今天要用到hive的string相关的数据类型和数据，直接附链接和sql语句

Hive的String类型的datatype如下
https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=82706456#LanguageManualTypes-string

 创建表和插入数据的sql语句，map类型的数据插入用到了str_to_map和map, struct类型的数据插入用到了named_struct.

-- create table
-- 创建表语句请在dbeaver中运行。如果在dbvisualizer中，会把<street:string, city:string>中的冒号string当成参数
drop table joe.type_complex01;
create table if not exists joe.type_complex01(
name string,
friends array<string>,
children map<string, string>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ","
collection items terminated by "_"
map keys terminated by ":"
lines terminated by "\n";

-- data
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing
-- load data 因为远端无法load data，只能用insert 语句插入数据
load data local inpath "/data/type_complex01.txt" into table joe.type_complex01;
-- load data local inpath "/data/type_complex01.txt": No files matching path file:/opt/hive/bin/type_complex01.txt
-- load data inpath "type_complex01.txt": No files matching path hdfs://namenode:8020/user/root/type_complex01.txt

-- insert data 最好在dbvisualizer中运行，可以连续运行多条语句
insert into joe.type_complex01 select 'name01',array('friend01','xiaoli'),str_to_map('xiao song:18, xiaoxiao song:19',',',':'),named_struct('street','hui long', 'city','guan_beijing');

-- 如果children 的类型为：map<string, int>,
insert into joe.t_complex02 select 'name13',array('friend12','xiaoli'),map('child01',18,'child02',8),named_struct('street','hui long', 'city','guan_beijing');

-- 
select * from joe.type_complex01;

-- 通过索引进行查询
select name, friends[0] from joe.type_complex01;

---- Map中数据查询
-- 查询Map中所有key
select name,map_keys(children) from joe.type_complex01;
-- 指定key查询value
select name,children["xiao song"] from joe.type_complex01;
-- 查询指定key,value
select name,map_keys(children)[0],map_values(children)[0] from joe.type_complex01;
---- Struct中数据查询
-- "点"语法查询struct中属性
select name,address.street from joe.type_complex01;