本文转载于 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;