hive分区（partition）简介(2)

9、基于分区的查询的语句：

SELECT day_table.* FROM day_table WHERE day_table.dt>= '2008-08-08';

10、查看分区语句：

hive> show partitions day_hour_table; OK dt=2008-08-08/hour=08 dt=2008-08-08/hour=09 dt=2008-08-09/hour=09

三、总结

1、在 Hive 中，表中的一个 Partition 对应于表下的一个目录，所有的 Partition 的数据都存储在最字集的目录中。

2、总的说来partition就是辅助查询，缩小查询范围，加快数据的检索速度和对数据按照一定的规格和条件进行管理。

——————————————————————————————————————

hive中关于partition的操作：
hive> create table mp (a string) partitioned by (b string, c string);
OK
Time taken: 0.044 seconds
hive> alter table mp add partition (b='1', c='1');
OK
Time taken: 0.079 seconds
hive> alter table mp add partition (b='1', c='2');
OK
Time taken: 0.052 seconds
hive> alter table mp add partition (b='2', c='2');
OK
Time taken: 0.056 seconds
hive> show partitions mp ;
OK
b=1/c=1
b=1/c=2
b=2/c=2
Time taken: 0.046 seconds
hive> explain extended alter table mp drop partition (b='1');
OK
ABSTRACT SYNTAX TREE:
  (TOK_ALTERTABLE_DROPPARTS mp (TOK_PARTSPEC (TOK_PARTVAL b '1')))

STAGE DEPENDENCIES:
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-0
      Drop Table Operator:
        Drop Table
          table: mp

Time taken: 0.048 seconds
hive> alter table mp drop partition (b='1');
FAILED: Error in metadata: table is partitioned but partition spec is not specified or tab: {b=1}
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
hive> show partitions mp ;
OK
b=1/c=1
b=1/c=2
b=2/c=2
Time taken: 0.044 seconds
hive> alter table mp add   partition ( b='1', c = '3') partition ( b='1' , c='4');
OK
Time taken: 0.168 seconds
hive> show partitions mp ;
OK
b=1/c=1
b=1/c=2
b=1/c=3
b=1/c=4
b=2/c=2
b=2/c=3
Time taken: 0.066 seconds
hive>insert overwrite table mp partition (b='1', c='1') select cnt from tmp_et3 ;

hive>alter table mp add columns (newcol string);

location指定目录结构
hive> alter table alter2 add partition (insertdate='2008-01-01') location '2008/01/01';

hive> alter table alter2 add partition (insertdate='2008-01-02') location '2008/01/02';