-----------+---------------------------------+
2.6 PARTITIONS PARTITION_KEY 和 PARTITION_KEY_VALS (分区)
PARTITION_KEYS
描述:
PARTITION_KEYS 保存了所有分区表用于分区的字段
例子:
通过PARTITION_KEYS查看acorn_3g.user_act的分区信息，可看到该表是一个分区表，分区字段为log_date,其中INTEGER_IDX为分区字段的序号，和分区字段一一对应
mysql> desc PARTITION_KEYS;
+--------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| TBL_ID | bigint(20) | NO | PRI | NULL | |
| PKEY_COMMENT | varchar(4000) | YES | | NULL | |
| PKEY_NAME | varchar(128) | NO | PRI | NULL | |
| PKEY_TYPE | varchar(767) | NO | | NULL | |
| INTEGER_IDX | int(11) | NO | | NULL | |
+--------------+---------------+------+-----+---------+-------+
mysql> select * FROM PARTITION_KEYS WHERE TBL_ID=41231;
+--------+--------------+-----------+-----------+-------------+
| TBL_ID | PKEY_COMMENT | PKEY_NAME | PKEY_TYPE | INTEGER_IDX |
+--------+--------------+-----------+-----------+-------------+
| 41231 | NULL | log_date | string | 0 |
+--------+--------------+-----------+-----------+-------------+
PARTITIONS
描述:
PARTITIONS存储了Hive数据仓库总所有的分区信息，每个分区由PART_ID标识，其中TBL_ID为隶属的Table,SD_ID为隶属的SDS(见2.3)
例子:
通过PARTITIONS表查看acorn_3g.user_act表的分区信息，如PART_ID为168301，名字为log_date=2013-03-01,SD_ID为231621
mysql> desc PARTITIONS;
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| PART_ID | bigint(20) | NO | PRI | NULL | |
| CREATE_TIME | int(11) | NO | | NULL | |
| LAST_ACCESS_TIME | int(11) | NO | | NULL | |
| PART_NAME | varchar(767) | YES | MUL | NULL | |
| SD_ID | bigint(20) | YES | MUL | NULL | |
| TBL_ID | bigint(20) | YES | MUL | NULL | |
+------------------+--------------+------+-----+---------+-------+
mysql> select * FROM PARTITIONS WHERE TBL_ID=41231 order by PART_NAME limit 5;
+---------+-------------+------------------+---------------------+--------+--------+
| PART_ID | CREATE_TIME | LAST_ACCESS_TIME | PART_NAME | SD_ID | TBL_ID |
+---------+-------------+------------------+---------------------+--------+--------+
| 168301 | 1366259946 | 0 | log_date=2013-03-01 | 231621 | 41231 |
| 168321 | 1366260063 | 0 | log_date=2013-03-02 | 231641 | 41231 |
| 168331 | 1366260176 | 0 | log_date=2013-03-03 | 231651 | 41231 |
| 168346 | 1366260298 | 0 | log_date=2013-03-04 | 231666 | 41231 |
| 168361 | 1366260398 | 0 | log_date=2013-03-05 | 231681 | 41231 |
+---------+-------------+------------------+---------------------+--------+--------+
PARTITION_KEY_VALS
描述：
PARTITION_KEY_VALS 存储了PARTITION_KEY中描述的分区字段的值，通常配合PARTITIONS 和PARTITION_KEYS表使用。
例子:
查看分区PART_ID=168301各分区字段的值，其中PARTITION_KEY_VALS存储了所有分区字段序号(INTEGER_IDX)和值(PART_KEY_VAL)间的对应关系。本例中acorn_3g.user_act PART_ID=168301分区log_date字段的值为‘2013-03-01’
mysql> select pk.PKEY_NAME,pk.PKEY_TYPE,pk.INTEGER_IDX,pkv.PART_KEY_VAL from PARTITION_KEYS pk,PARTITION_KEY_VALS pkv
where pk.INTEGER_IDX=pkv.INTEGER_IDX and pk.TBL_ID=41231 and pkv.PART_ID=168301;
+-----------+-----------+-------------+--------------+
| PKEY_NAME | PK