Hive基础知识（二）--分区表

二、表分区

1、创建分区表

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
[COMMENT table_comment]
PARTITIONED BY (col_name1 data_type, col_name2 data_type [COMMENT col_comment], ...)
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)];

• 建表

# 建表
use db_hive;
drop table if exists tbl_partitioned;
create table tbl_partitioned(
id int,
name string
)
partitioned by (class int)
stored as textfile
;

• 表详情
  
• 插入数据

-- 在hive中不推荐单条数据插入，会产生很多小文件，这里演示使用
insert into tbl_partitioned partition(class=8) values(10001,'zhangsan');
insert into tbl_partitioned partition(class=7)
values(10002,'lisi');

• 查看目录

hadoop fs -ls /user/hive/warehouse/db_hive.db/tbl_partitioned/
hadoop fs -ls /user/hive/warehouse/db_hive.db/tbl_partitioned/class=7
hadoop fs -cat /user/hive/warehouse/db_hive.db/tbl_partitioned/class=7/000000_0

2、静态分区

数据导入时，需要手动指定目标分区

-- insert
INSERT OVERWRITE | INTO TABLE tablename1
PARTITION (partcol1=val1, partcol2=val2 ...)
[IF NOT EXISTS] select_statement1 FROM from_statement;
-- load
LOAD DATA  [LOCAL]  INPATH  'filepath'  [OVERWRITE]  INTO TABLE  tablename
PARTITION (partcol1=val1, partcol2=val2 ...)

insert into tbl_partitioned partition(class=8) values (10003,'quqi');

3、动态分区

使用动态分区，首先要开启动态分区的支持

--默认：false
set hive.exec.dynamic.partition=true;
--默认：strict（至少有一个分区列是静态分区）
set hive.exec.dynamic.partition.mode=nostrict;
--其它参数设置，可选
set hive.exec.max.dynamic.partitions.pernode; --每一个执行mr节点上，允许创建的动态分区的最大数量(100)
set hive.exec.max.dynamic.partitions;  --所有执行mr节点上，允许创建的所有动态分区的最大数量(1000)
set hive.exec.max.created.files;  --所有的mr job允许创建的文件的最大数量(100000)
hive.error.on.empty.partition; --当有空分区生成时,是否抛出异常(false)

插入语法：

-- insert
INSERT (OVERWRITE | INTO) TABLE <table_name>
PARTITION ([<spk>=<value>, ..., ] <dpk>, [..., <dpk>])
SELECT  <select_expression>, <select_expression>, ...  FROM <table_name>;

--开启动态分区配置
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nostrict;

--进行动态分区数据插入
insert into table tbl_partitioned
partition (class)
select * from tb2;

4、分区操作

• 查看分区

show partitions tbl_partitioned;

• 添加分区

alter table tbl_partitioned add partition(class=1);

• 重命名分区

alter table tbl_partitioned partition(class=1) rename to partition(class=2);

• 移动分区

-- 将tbl_partitioned 的分区class=8移动到tb2中
alter table tb2 exchange partition (class=8)
with table tbl_partitioned ;

• 删除分区

alter table tbl_partitioned drop partition(class=2);