###3.9.3 双分区

建表脚本：

xxxxxxxxxx
create table person6(
id int comment "唯一标识id",
name string comment "名称",
likes array<string> comment "爱好",
address map<string,string> comment "地址"
)
partitioned by(age int,sex string)
row format delimited
fields terminated by ","
collection items terminated by "-"
map keys terminated by ":"
lines terminated by "\n";

创建表，并查看表信息：

xxxxxxxxxx
hive> create table person6(
    > id int comment "唯一标识id",
    > name string comment "名称",
    > likes array<string> comment "爱好",
    > address map<string,string> comment "地址"
    > )
    > partitioned by(age int,sex string)
    > row format delimited
    > fields terminated by ","
    > collection items terminated by "-"
    > map keys terminated by ":"
    > lines terminated by "\n";
OK
Time taken: 0.204 seconds
hive> desc formatted person6;
OK
# col_name              data_type               comment             
id                      int                     唯一标识id              
name                    string                  名称                  
likes                   array<string>           爱好                  
address                 map<string,string>      地址                  
         
# Partition Information      
# col_name              data_type               comment             
age                     int                                         
sex                     string   

查看元数据表：

xxxxxxxxxx
mysql> select * from PARTITION_KEYS where TBL_ID=28;
+--------+--------------+-----------+-----------+-------------+
| TBL_ID | PKEY_COMMENT | PKEY_NAME | PKEY_TYPE | INTEGER_IDX |
+--------+--------------+-----------+-----------+-------------+
|     28 | NULL         | age       | int       |           0 |
|     28 | NULL         | sex       | string    |           1 |
+--------+--------------+-----------+-----------+-------------+

加载数据时，分区先写谁都可以，但是属性名称不能写错了。

xxxxxxxxxx
hive> load data local inpath '/root/data/person.txt' into table person6 partition(age=20,sex='man');
Loading data to table default.person6 partition (age=20, sex=man)
OK
Time taken: 1.24 seconds
hive> load data local inpath '/root/data/person.txt' into table person6 partition(sex='man',age=30);
Loading data to table default.person6 partition (sex=man,age=30)
OK
Time taken: 1.007 seconds
hive> select * from person6;
OK
1   小明1 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}   20  man
2   小明2 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}   20  man
3   小明3 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}   20  man
4   小明4 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}   20  man
5   小明5 ["lol","movie"] {"beijing":"xisanqi","shanghai":"pudong"}   20  man
6   小明6 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}   20  man
7   小明7 ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}   20  man
8   小明8 ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}   20  man
9   小明9 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}   20  man
1   小明1 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}   30  man
2   小明2 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}   30  man
3   小明3 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}   30  man
4   小明4 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}   30  man
5   小明5 ["lol","movie"] {"beijing":"xisanqi","shanghai":"pudong"}   30  man
6   小明6 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}   30  man
7   小明7 ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}   30  man
8   小明8 ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}   30  man
9   小明9 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}   30  man
Time taken: 0.37 seconds, Fetched: 18 row(s)

load数据数，分区先写谁后写谁都可以，但是分区字段的名称不要写错了。

该行命令共做了三件事情：

1. 在hdfs上创建对应的目录，和创建表时的顺序有关，和load数据时写的分区字段的顺序无关。
2. 将数据文件上传到对应的目录上。
3. 在元数据的数据库实例hive_remote的表PARTITIONS中添加分区信息。