Hive的分区表分为动态分区和静态分区,分区表的使用能够为巨量表查询性能的提高提供帮助。
静态分区在数据载入前需要事先将分区建好,使用起来稍显复杂,而动态表可以根据数据自动建立分区,但同时花费了巨大的性能代价。如果分区是可以确定的话,一定不要用动态分区,动态分区的值是在reduce运行阶段确定的;也就是会把所有的记录distribute by。 可想而知表记录非常大的话,只有一个reduce去处理,那简直是疯狂的。如果这个值唯一或者事先已经知道,比如按天分区(i_date=20151105)那就用静态分区吧。静态分区在编译阶段已经确定,不需要reduce处理。所以,在分区表的使用上,一般建议使用静态分区。
partition必须在表定义时创建。
1、单分区建表语句
CREATE TABLE `base_order_partition`(
`order_id` bigint,
`order_date` int,
`order_mo` int,
`status` tinyint,
`status_desc` varchar(12),
`food_num` int,
`food_amount` float,
`order_amount` float)
PARTITIONED BY (
`order_month` int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '$'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://Master:9000/home/spark/opt/data_dir/hive/warehouse/59store.db/base_order_partition'
TBLPROPERTIES (
'transient_lastDdlTime'='1446600982');
2、双分区建表语句
CREATE TABLE `base_order_partition2`(
`order_id` bigint,
`order_date` int,
`order_mo` int,
`status` tinyint,
`status_desc` varchar(12),
`food_num` int,
`food_amount` float,
`order_amount` float)
PARTITIONED BY (
`order_month` int,
`order_date1` int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '$'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://Master:9000/home/spark/opt/data_dir/hive/warehouse/59store.db/base_order_partition2'
TBLPROPERTIES (
'transient_lastDdlTime'='1446620191');
先以order_month为文件夹,再以order_date1子文件夹区分。
3、添加分区表语法(表已创建,在此基础上添加分区)
单分区表:
alter table base_order_partition add partition (order_month=201501);
alter table base_order_partition add partition (order_month=201502);
alter table base_order_partition add partition (order_month=201503);
alter table base_order_partition add partition (order_month=201504);
alter table base_order_partition add partition (order_month=201505);
alter table base_order_partition add partition (order_month=201506);
alter table base_order_partition add partition (order_month=201507);
alter table base_order_partition add partition (order_month=201508);
alter table base_order_partition add partition (order_month=201509);
alter table base_order_partition add partition (order_month=201510);
alter table base_order_partition add partition (order_month=201511);
alter table base_order_partition add partition (order_month=201512);
双分区表:
alter table base_order_partition2 add partition (order_month=201510,order_date1=20151001);
alter table base_order_partition2 add partition (order_month=201510,order_date1=20151002);
alter table base_order_partition2 add partition (order_month=201510,order_date1=20151003);
alter table base_order_partition2 add partition (order_month=