Hive分区

create database if not exists myhive1;
use myhive1;
drop table if exists student;
create table student(id int, name string, sex string ,age int, department string) row format delimited fields terminated by “,”;
load data local inpath “/home/hadoop/student.txt” into table student;
select * from student;

一、分区
hive表就是hdfs的上的一个目录
hive表中的数据，其实就是对应了HDFS上的一个目录下的数据
概念：对hive表的数据做分区管理

创建分区表
create table student_ptn(id int, name string) partitioned by (age int, department string) row format delimited fields terminated by ",";

添加分区
// 添加一个分区
alter table student_ptn add partition(age=44,department="AA");
// 一次添加多个分区
alter table student_ptn add partition(age=66,department="CC") partition(age=55,department="BB");
// 添加成功。但是分区字段的顺序是在创建分区表的时候决定的
alter table student_ptn add partition(department="DD",age=77); 
// 添加分区时，指定分区的数据存储目录
alter table student_ptn add partition(age=88,department="EE") location "/ptn_input1" partition(age=99,department="FF") location "/ptn_input2";

查询分区
show partitions student_ptn;

修改分区
alter table student_ptn partition(age=44,department="AA") set location "hdfs://hadoop02:9000/ptn_input3";

// 往某个特定的分区导入数据
load data local inpath "/home/hadoop/student.txt" into table student_ptn partition(age=55,department="BB");

load data local inpath "/home/hadoop/student.txt" into table student_ptn;  XXXXX  不能直接往分区表导入数据

// 删除分区
alter table student_ptn drop partition(age=44,department="AA");  
// 删除分区的时候，包括分区的元数据信息和分区的数据及目录
// 清空分区表的时候，分区还在，数据被清空

导入数据，大体上说，总共有三种方式：
1、hadoop fs 
2、load 
3、insert ....select....

// 使用insert....values... 的方法是导入数据，主要用于测试
insert into table student_ptn values (101,"liutao");
insert into table student_ptn partition(age=66,department="CC") values (101,"liutao");

// 使用单重或者多重模式进去插入
from student
insert ... select ....
insert ... select ....

from student 
insert into table student_ptn partition(age=18,department="A") select id,name where age = 18 
insert into table student_ptn partition(age=18,department="B") select id,name where age = 19;

// 验证多重分区的插入是否成功
select id, name from student_ptn where age = 18 and department  = "A";
select id, name from student_ptn where age = 18 and department  = "B";

// 动态分区
set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;

drop table student_ptn;

insert into table student_ptn partition(age=101, department) select id, department, name from student;     XXXXXX
insert into table student_ptn partition(age=202, department) select id, name, department from student;    √√√√√√√ 

insert into table student_ptn(age) select id, name, age from student;

动态分区的效果：

按照动态分区的字段的值做划分，一个值就是一个分区
该studnet表当中的分区字段department有多少个值，就有多少个分区

每个分区就存一个分区字段值的所有数据

department = IS  CS   MA