Hive分区表简介
简介：
如果一个表中数据很多，我们查询时就很慢，耗费大量时间，如果要查询其中部分数据该怎么办呢，这时我们引入分区的概念。
Hive中的分区表分为两种：静态分区和动态分区。
创新互联建站长期为上1000家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为平利企业提供专业的成都网站制作、网站设计，平利网站改版等技术服务。拥有十年丰富建站经验和众多成功案例,为您定制开发。
1.静态分区:
- 可以根据
PARTITIONED BY创建分区表，一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下。 - 分区是以字段的形式在表结构中存在，通过describe table命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。
- 分区建表分为2种，一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区，表文件夹下出现多文件夹嵌套模式。
单级分区表演示：
# 单分区表创建
hive> create table order_partition(
> ordernumber string,
> eventtime string
> )
> partitioned by (event_month string)
> row format delimited fields terminated by '\t';
OK
Time taken: 0.82 seconds
# 将order.txt 文件中的数据加载到order_partition表中
hive> load data local inpath '/home/hadoop/order.txt' overwrite into table order_partition partition (event_month='2014-05');
Loading data to table default.order_partition partition (event_month=2014-05)
Partition default.order_partition{event_month=2014-05} stats: [numFiles=1, numRows=0, totalSize=208, rawDataSize=0]
OK
Time taken: 1.749 seconds
# 查看order_partition分区数据
hive> select * from order_partition where event_month='2014-05';
OK
10703007267488 2014-05-01 06:01:12.334+01 2014-05
10101043505096 2014-05-01 07:28:12.342+01 2014-05
10103043509747 2014-05-01 07:50:12.33+01 2014-05
10103043501575 2014-05-01 09:27:12.33+01 2014-05
10104043514061 2014-05-01 09:03:12.324+01 2014-05
Time taken: 0.208 seconds, Fetched: 5 row(s)
# 在元数据MySQL中查看
mysql> select * from partitions;
+---------+-------------+------------------+---------------------+-------+--------+
| PART_ID | CREATE_TIME | LAST_ACCESS_TIME | PART_NAME | SD_ID | TBL_ID |
+---------+-------------+------------------+---------------------+-------+--------+
| 1 | 1530498328 | 0 | event_month=2014-05 | 32 | 31 |
+---------+-------------+------------------+---------------------+-------+--------+
1 row in set (0.00 sec)
mysql> select * from partition_key_vals;
+---------+--------------+-------------+
| PART_ID | PART_KEY_VAL | INTEGER_IDX |
+---------+--------------+-------------+
| 1 | 2014-05 | 0 |
+---------+--------------+-------------+
1 row in set (0.00 sec)
# HDFS中查看目录
[hadoop@hadoop000 ~]$ hadoop fs -ls /user/hive/warehouse/order_partition/
Found 1 items
drwxr-xr-x - hadoop supergroup 0 2018-07-02 10:29 /user/hive/warehouse/order_partition/event_month=2014-05注：使用hadoop shell 加载数据也能加载数据，下面进行演示：
创建分区，也就是说在HDFS文件夹目录下会有一个分区目录，那么我们是不是直接可以在HDFS上创建一个目录，再把数据加载进去呢？
# 创建目录并上传文件
[hadoop@hadoop000 ~]$ hadoop fs -mkdir -p /user/hive/warehouse/order_partition/event_month=2014-06
[hadoop@hadoop000 ~]$ hadoop fs -put /home/hadoop/order.txt /user/hive/warehouse/order_partition/event_month=2014-06
[hadoop@hadoop000 ~]$ hadoop fs -ls /user/hive/warehouse/order_partition/
Found 2 items
drwxr-xr-x - hadoop supergroup 0 2018-07-02 10:29 /user/hive/warehouse/order_partition/event_month=2014-05
drwxr-xr-x - hadoop supergroup 0 2018-07-02 10:54 /user/hive/warehouse/order_partition/event_month=2014-06
# 发现分区表中没有数据
hive> select * from order_partition where event_month='2014-06';
OK
Time taken: 0.21 seconds
# 原因是我们将文件上传到了hdfs，hdfs是有了数据，但hive中的元数据中还没有，执行如下命令更新
hive> msck repair table order_partition;
OK
Partitions not in metastore: order_partition:event_month=2014-06
Repair: Added partition to metastore order_partition:event_month=2014-06
Time taken: 0.178 seconds, Fetched: 2 row(s)
# 再次查看分区数据
hive> select * from order_partition where event_month='2014-06';
OK
10703007267488 2014-05-01 06:01:12.334+01 2014-06
10101043505096 2014-05-01 07:28:12.342+01 2014-06
10103043509747 2014-05-01 07:50:12.33+01 2014-06
10103043501575 2014-05-01 09:27:12.33+01 2014-06
10104043514061 2014-05-01 09:03:12.324+01 2014-06
Time taken: 0.257 seconds, Fetched: 5 row(s)
# 查看表分区
hive> show partitions order_partition;
OK
event_month=2014-05
event_month=2014-06
Time taken: 0.164 seconds, Fetched: 2 row(s)注意： msck repair table命令执行后Hive会检测如果HDFS目录下存在 但表的metastore中不存在的partition元信息，更新到metastore中。如果有一张表已经存放好几年了，用这个命令去执行的话 半天都反应不了，所以这个命令太暴力了，生产中不推荐使用。可以用Add partition来添加分区。
[hadoop@hadoop000 ~]$ hadoop fs -mkdir -p /user/hive/warehouse/order_partition/event_month=2014-07
[hadoop@hadoop000 ~]$ hadoop fs -put /home/hadoop/order.txt /user/hive/warehouse/order_partition/event_month=2014-07
[hadoop@hadoop000 ~]$ hadoop fs -ls /user/hive/warehouse/order_partition/
Found 3 items
drwxr-xr-x - hadoop supergroup 0 2018-07-02 10:29 /user/hive/warehouse/order_partition/event_month=2014-05
drwxr-xr-x - hadoop supergroup 0 2018-07-02 10:54 /user/hive/warehouse/order_partition/event_month=2014-06
drwxr-xr-x - hadoop supergroup 0 2018-07-02 11:09 /user/hive/warehouse/order_partition/event_month=2014-07
# 查看新的分区
hive> select * from order_partition where event_month='2014-07';
OK
Time taken: 0.188 seconds
# 添加分区
hive> ALTER TABLE order_partition ADD IF NOT EXISTS PARTITION (event_month='2014-07');
OK
Time taken: 0.22 seconds
# 再次查看
hive> select * from order_partition where event_month='2014-07';
OK
10703007267488 2014-05-01 06:01:12.334+01 2014-07
10101043505096 2014-05-01 07:28:12.342+01 2014-07
10103043509747 2014-05-01 07:50:12.33+01 2014-07
10103043501575 2014-05-01 09:27:12.33+01 2014-07
10104043514061 2014-05-01 09:03:12.324+01 2014-07
Time taken: 0.206 seconds, Fetched: 5 row(s)
hive> show partitions order_partition;
OK
event_month=2014-05
event_month=2014-06
event_month=2014-07
Time taken: 0.151 seconds, Fetched: 3 row(s)多级分区表演示：
# 创建多级分区表
hive> create table order_mulit_partition(
> ordernumber string,
> eventtime string
> )
> partitioned by (event_month string,event_day string)
> row format delimited fields terminated by '\t';
OK
Time taken: 0.133 seconds
# 加载数据
hive> load data local inpath '/home/hadoop/order.txt' overwrite into table order_mulit_partition partition (event_month='2014-05',event_day=01);
# 查看分区
hive> select * from order_mulit_partition where event_month='2014-05' and event_day='01';
OK
10703007267488 2014-05-01 06:01:12.334+01 2014-05 01
10101043505096 2014-05-01 07:28:12.342+01 2014-05 01
10103043509747 2014-05-01 07:50:12.33+01 2014-05 01
10103043501575 2014-05-01 09:27:12.33+01 2014-05 01
10104043514061 2014-05-01 09:03:12.324+01 2014-05 01
hive> show partitions order_mulit_partition;
OK
event_month=2014-05/event_day=01
Time taken: 0.158 seconds, Fetched: 1 row(s)
# HDFS中多级分区的目录结构
[hadoop@hadoop000 ~]$ hadoop fs -ls /user/hive/warehouse/order_mulit_partition/event_month=2014-05
Found 1 items
drwxr-xr-x - hadoop supergroup 0 2018-07-02 11:17 /user/hive/warehouse/order_mulit_partition/event_month=2014-05/event_day=01总结：单级分区在HDFS上文件目录为单级；多分区在HDFS上文件目录为多级。
2.动态分区:
参考：官方文档
- 先看看官方为我们解释的什么是动态分区：
Static Partition (SP) columns静态分区；
Dynamic Partition (DP) columns动态分区。
DP columns are specified the same way as it is for SP columns – in the partition clause. The only difference is that DP columns do not have values, while SP columns do. In the partition clause, we need to specify all partitioning columns, even if all of them are DP columns.
In INSERT ... SELECT ... queries, the dynamic partition columns must be specified last among the columns in the SELECT statement and in the same order in which they appear in the PARTITION() clause.简单总结下区别：
1.DP列的指定方式与SP列相同 - 在分区子句中（ Partition关键字后面），唯一的区别是，DP列没有值，而SP列有值（ Partition关键字后面只有key没有value）
2.在INSERT … SELECT …查询中，必须在SELECT语句中的列中最后指定动态分区列，并按PARTITION（）子句中出现的顺序进行排列
3.所有DP列 - 只允许在非严格模式下使用。 在严格模式下，我们应该抛出一个错误
- 4.如果动态分区和静态分区一起使用，必须是动态分区的字段在前，静态分区的字段在后。
下面举几个例子进行演示：
注意：为了演示动态分区与静态分区的区别 并且对比出静态分区的繁琐，我们先对静态分区进行操作 之后再演示动态分区。
# 创建员工静态分区表
hive> CREATE TABLE emp_static_partition (
> empno int,
> ename string,
> job string,
> mgr int,
> hiredate string,
> salary double,
> comm double
> )
> PARTITIONED BY (deptno int)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
OK
Time taken: 0.198 seconds
# 将emp表里的数据插入静态分区
hive> select * from emp;
OK
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10
Time taken: 0.164 seconds, Fetched: 14 row(s)
# 每个分区都要写一条insert语句
hive> insert into table emp_static_partition partition(deptno=10)
> select empno,ename ,job ,mgr ,hiredate ,salary ,comm from emp where deptno=10;
Query ID = hadoop_20180702100505_f0566585-06b2-4c53-910a-b6a58791fc2d
Total jobs = 3
Launching Job 1 out of 3
...
OK
Time taken: 15.265 seconds
hive> insert into table emp_static_partition partition(deptno=20)
> select empno,ename ,job ,mgr ,hiredate ,salary ,comm from emp where deptno=20;
Query ID = hadoop_20180702100505_f0566585-06b2-4c53-910a-b6a58791fc2d
Total jobs = 3
Launching Job 1 out of 3
...
OK
Time taken: 18.527 seconds
hive> insert into table emp_static_partition partition(deptno=30)
> select empno,ename ,job ,mgr ,hiredate ,salary ,comm from emp where deptno=30;
Query ID = hadoop_20180702100505_f0566585-06b2-4c53-910a-b6a58791fc2d
Total jobs = 3
Launching Job 1 out of 3
...
OK
Time taken: 14.062 seconds
# 查看各分区
hive> select * from emp_static_partition where deptno='10';
OK
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10
Time taken: 0.219 seconds, Fetched: 3 row(s)
hive> select * from emp_static_partition where deptno='20';
OK
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20
Time taken: 0.197 seconds, Fetched: 5 row(s)
hive> select * from emp_static_partition where deptno='30';
OK
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
Time taken: 0.181 seconds, Fetched: 6 row(s)静态分区表有一个非常致命的缺点，每次分区的插入都要单独写insert语句。
下面利用动态分区进行演示
演示前先进行设置：hive 中默认是静态分区，想要使用动态分区，需要设置如下参数，可以使用临时设置，你也可以写在配置文件（hive-site.xml）里，永久生效。临时配置如下
set hive.exec.dynamic.partition=true; --开启动态分区 默认为false，不开启
set hive.exec.dynamic.partition.mode=nonstrict; --指定动态分区模式，默认为strict，即必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区# 创建员工动态分区表，分区字段为deptno
hive> CREATE TABLE emp_dynamic_partition (
> empno int,
> ename string,
> job string,
> mgr int,
> hiredate string,
> salary double,
> comm double
> )
> PARTITIONED BY (deptno int)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
OK
Time taken: 0.165 seconds
# insert一条语句搞定
hive> insert into table emp_dynamic_partition partition(deptno)
> select empno,ename ,job ,mgr ,hiredate ,salary ,comm, deptno from emp;
Query ID = hadoop_20180702100505_f0566585-06b2-4c53-910a-b6a58791fc2d
Total jobs = 3
Launching Job 1 out of 3
...
OK
Time taken: 17.982 seconds
# 查看各分区
hive> show partitions emp_dynamic_partition;
OK
deptno=10
deptno=20
deptno=30
Time taken: 0.176 seconds, Fetched: 3 row(s)
hive> select * from emp_dynamic_partition where deptno='10';
OK
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10
Time taken: 2.662 seconds, Fetched: 3 row(s)
hive> select * from emp_dynamic_partition where deptno='20';
OK
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20
Time taken: 0.178 seconds, Fetched: 5 row(s)
hive> select * from emp_dynamic_partition where deptno='30';
OK
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
Time taken: 0.146 seconds, Fetched: 6 row(s)- 查看HDFS上文件目录结构
[hadoop@hadoop000 ~]$ hadoop fs -ls /user/hive/warehouse Found 6 items drwxr-xr-x - hadoop supergroup 0 2018-06-24 15:38 /user/hive/warehouse/emp drwxr-xr-x - hadoop supergroup 0 2018-07-02 13:55 /user/hive/warehouse/emp_dynamic_partition drwxr-xr-x - hadoop supergroup 0 2018-07-02 13:50 /user/hive/warehouse/emp_static_partition drwxr-xr-x - hadoop supergroup 0 2018-07-02 11:17 /user/hive/warehouse/order_mulit_partition drwxr-xr-x - hadoop supergroup 0 2018-07-02 11:09 /user/hive/warehouse/order_partition drwxr-xr-x - hadoop supergroup 0 2018-06-24 15:35 /user/hive/warehouse/stu [hadoop@hadoop000 ~]$ hadoop fs -ls /user/hive/warehouse/emp_static_partition Found 3 items drwxr-xr-x - hadoop supergroup 0 2018-07-02 13:47 /user/hive/warehouse/emp_static_partition/deptno=10 drwxr-xr-x - hadoop supergroup 0 2018-07-02 13:50 /user/hive/warehouse/emp_static_partition/deptno=20 drwxr-xr-x - hadoop supergroup 0 2018-07-02 13:51 /user/hive/warehouse/emp_static_partition/deptno=30 [hadoop@hadoop000 ~]$ hadoop fs -ls /user/hive/warehouse/emp_dynamic_partition Found 3 items drwxr-xr-x - hadoop supergroup 0 2018-07-02 13:55 /user/hive/warehouse/emp_dynamic_partition/deptno=10 drwxr-xr-x - hadoop supergroup 0 2018-07-02 13:55 /user/hive/warehouse/emp_dynamic_partition/deptno=20 drwxr-xr-x - hadoop supergroup 0 2018-07-02 13:55 /user/hive/warehouse/emp_dynamic_partition/deptno=30
补充：两种分区还可以混合使用 下面做简要了解：
- mixed SP & DP columns（混合使用动态分区和静态分区）
hive> create table student(
> id int,
> name string,
> tel string,
> age int
> )
> row format delimited fields terminated by '\t';
OK
Time taken: 0.125 seconds
hive> insert into student values(1,'zhangsan','18311111111',20),(2,'lisi','18222222222',30),(3,'wangwu','15733333333',40);
Query ID = hadoop_20180702100505_f0566585-06b2-4c53-910a-b6a58791fc2d
Total jobs = 3
Launching Job 1 out of 3
...
OK
Time taken: 15.375 seconds
hive> select * from student;
OK
1 zhangsan 18311111111 20
2 lisi 18222222222 30
3 wangwu 15733333333 40
Time taken: 0.106 seconds, Fetched: 3 row(s)
# 创建混合分区表
hive> create table stu_mixed_partition(
> id int,
> name string,
> tel string
> )
> partitioned by (ds string,age int)
> row format delimited fields terminated by '\t';
OK
Time taken: 0.171 seconds
# 插入数据
hive> insert into stu_mixed_partition partition(ds='2010-03-03',age)
> select id,name,tel,age from student;
Query ID = hadoop_20180702100505_f0566585-06b2-4c53-910a-b6a58791fc2d
Total jobs = 3
Launching Job 1 out of 3
...
OK
Time taken: 18.887 seconds
# 查看分区
hive> show partitions stu_mixed_partition;
OK
ds=2010-03-03/age=20
ds=2010-03-03/age=30
ds=2010-03-03/age=40
hive> select * from stu_mixed_partition where ds='2010-03-03' and age=20;
OK
1 zhangsan 18311111111 2010-03-03 20
Time taken: 0.184 seconds, Fetched: 1 row(s)
hive> select * from stu_mixed_partition where ds='2010-03-03' and age=30;
OK
2 lisi 18222222222 2010-03-03 30
Time taken: 0.188 seconds, Fetched: 1 row(s)
hive> select * from stu_mixed_partition where ds='2010-03-03' and age=40;
OK
3 wangwu 15733333333 2010-03-03 40
Time taken: 0.186 seconds, Fetched: 1 row(s)
# 查看HDFS目录
[hadoop@oradb3 ~]$ hadoop fs -ls /user/hive/warehouse/stu_mixed_partition/ds=2010-03-03
Found 3 items
drwxr-xr-x - hadoop supergroup 0 2018-07-02 14:10 /user/hive/warehouse/stu_mixed_partition/ds=2010-03-03/age=20
drwxr-xr-x - hadoop supergroup 0 2018-07-02 14:10 /user/hive/warehouse/stu_mixed_partition/ds=2010-03-03/age=30
drwxr-xr-x - hadoop supergroup 0 2018-07-02 14:10 /user/hive/warehouse/stu_mixed_partition/ds=2010-03-03/age=40本文标题：Hive分区表简介
网站路径：http://myzitong.com/article/gejgcs.html