【Hive】Hive表创建练习

基于上述数据结构，我们在 Hive 里创建对应的表，并导入数据。 创建本地测试文件 test.txt

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

解析上述数据:大致分为name、friends、children和address四列，每列的分割符是 , friends列是array练习，children是map类型，address是struct类型
Hive 上创建测试表 test

create table test(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';

字段解释

row format delimited fields terminated by ','	--  列分隔符
collection items terminated by '_'	--MAP STRUCT  和 ARRAY  的分隔符(数据分割符号) map keys terminated by ':'	-- MAP 中的 key 与 value 的分隔符
lines terminated by '\n';	--  行分隔符

导入数据

 load data local inpath '/home/data/hive/test' into table test;

查询

# 设置显示列名,只是单次有效
hive> set hive.cli.print.header=true;
hive> select  * from test;
OK
test.name	test.friends	test.children	test.address
songsong	["bingbing","lili"]	{"xiao song":"18","xiaoxiao song":"19"}	{"street":"hui long guan","city":"beijing"}
yangyang	["caicai","susu"]	{"xiao yang":"18","xiaoxiao yang":"19"}	{"street":"chao yang","city":"beijing"}
Time taken: 0.078 seconds, Fetched: 2 row(s)