Hive 建立 最简单的table 从 文件 导入数据 带 分区 参数 partitioned

表定义文件

create table people(
id STRING,
name STRING,
likes ARRAY<string>,
addr MAP<string,string>
)
ROW FORMAT DELIMITED
 FIELDS TERMINATED BY "	"
 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 "	"
 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