表定义文件
create table people(
id STRING,
name STRING,
likes ARRAY<string>,
addr MAP<string,string>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE;
下面是放入文本文件里的数据,文件名和路径也可以随便取,我这里是放在/tmp/test.txt,注意这里文本直接使用tab来分割的,不是空格
1 zs game,girl,money stuAddr:changsha,workAddr:beijing
1 ls game,girl,money stuAddr:changsha,workAddr:beijing
在hive里导入数据
load data local inpath '/tmp/test.txt' into table people;
验证数据 select * from people
hive> select * from people;
OK
1 zs ["game","girl","money"] {"stuAddr":"changsha","workAddr":"beijing"}
1 ls ["game","girl","money"] {"stuAddr":"changsha","workAddr":"beijing"}
Time taken: 0.158 seconds, Fetched: 2 row(s)
hive> select addr['stuAddr'] from people where name='zs';
OK
changsha
Time taken: 0.188 seconds, Fetched: 1 row(s)
带分区的表定义语句
create table people(
id STRING,
name STRING,
likes ARRAY<string>,
addr MAP<string,string>
)
partitioned by (dt string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE;
同时需要变更对应的数据文件
1 zs game,girl,money stuAddr:changsha,workAddr:beijing 2016-1-1
1 ls game,girl,money stuAddr:changsha,workAddr:beijing 2016-1-1
最后导入的命令也不一样,这里其实就是按照DT这个字段来分区的
load data local inpath '/tmp/test.txt' into table people PARTITION (dt='2016-1-1') ;
更多例子,可以参考官方文档
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL