hive的分区表
hive的分区表
分区表使用背景：
如果一个表中数据很多，我们查询时就很慢，耗费大量时间，如果要查询其中部分数据该怎么办呢，
这时我们引入分区的概念
可以根据PARTITIONED BY创建分区表，
1.一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下；
2.分区是以伪字段（伪列）的形式在表结构中存在，通过describe tablename（或者desc [formatted ]tablename）命令可以查看到字段存在，
但是该字段不存放实际的数据内容，仅仅是分区的表示。
3.分区建表分为2种，一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录。
另外一种是多分区，表文件夹下出现多文件夹嵌套模式。
CREATE TABLE `emp_1`(
`empno` int,
`ename` string,
`job` string,
`mgr` int,
`hiredate` string,
`sal` double,
`comm` double,
`deptno` int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION
'hdfs://hadoop001:9000/user/hive/warehouse/default.db/emp'
CREATE TABLE `order_partition`(
`order_no` string,
`event_time` string)
PARTITIONED BY (
`event_month` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION
'hdfs://hadoop001:9000/user/hive/warehouse/d6_hive.db/order_partition'（默认）
单分区：
hive> CREATE TABLE order_partition (
> order_number string,
> event_time string
> )
> PARTITIONED BY (event_month string);
OK
将order.txt 文件中的数据加载到order_partition表中（加载数据的用法可参照该地址）
hive> load data local inpath '/home/hadoop/order.txt' overwrite
into table order_partition partition (event_month='2014-05');
hive> select * from order_partition;
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
我们在hive每创建一个分区，mysql元数据里就会有相应的元数据建立，一般会在patition这张表里有显示。
当我们在hive里load数据进入hive以后，HDFS上相应的也会有数据，HDFS的存储目录可以通过在hive上执行
desc formatted tableanme 来查看 location
/user/hive/warehouse/d6_hive.db/order_partition
/user/hive/warehouse/库名.db/表名
如果在建表时不特别指定location 那么以上的目录就是默认目录。
此时查看HDFS上的/user/hive/warehouse/d6_hive.db/order_partition这个目录就能看到2014-05这个分区。
而在这个分区之下就是我们的弹性体数据文件，也就是我们load的文件。
那根据以上的操作我们是不是可以直接在HDFS的/user/hive/warehouse/d6_hive.db/order_partition目录下
直接建分区，然后把要load的文件用put命令上传到在HDFS上新建的分区。根据以往的建表经验，只要把数据放到
HDFS的相应目录上，在hive表中就能查到数据。但是在分区表中这样是行不通的。
解释如下:
创建分区，也就是说在HDFS文件夹目录下会有一个分区目录，
那么我们是不是直接可以在HDFS上创建一个目录，再把数据加载进去呢？
[hadoop@zydatahadoop001 ~]$ hadoop fs -mkdir -p /user/hive/warehouse/order_partition/event_month=2014-06
[hadoop@zydatahadoop001 ~]$ hadoop fs -put /home/hadoop/order.txt /user/hive/warehouse/order_partition/event_month=2014-06
上传完成后查看表order_partition
hive
> select * from order_partition
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: 2.034 seconds, Fetched: 5 row(s)
可以看到并没有看到我们刚刚通过hdfs上传后的数据，
原因是我们将文件上传到了hdfs，hdfs是有了数据，
但hive中的元数据中还没有，在hive中执行如下命令更新
hive>msck repair table order_partition;
还有一种方法就是在元数据MySQL中新增分区
MYSQL>ALTER TABLE order_partition ADD IF NOT EXISTS PARTITION (event_month='2014-06') ;
hive>ALTER TABLE order_partition ADD IF NOT EXISTS PARTITION (event_month='2014-06') ;（hive里执行也可以）
再次查看数据
hive> select * from order_partition;
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
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
此时可以看到，在刷新以后，06分区的数据就出来了。
以上讲的是单分区还有多分区，在hive表中体现的是又多了分区字段，在HDFS上则体现在目录上，
多级分区和单分区在建表的时候主要就是在patition by后加分区的字段的个数。单分区只加一个，
多分区加要求的分区字段大于1个。在HDFS上每个分区都是一级目录，目录级别根据分区先后确定。
在加载数据的时候，在无论是多级分区还是单分区都需要在load语句最后加上patition (分区字段1=‘value’，分区字段2=‘value’)
例子展示
多分区：
hive> CREATE TABLE order_partition2 (
> order_number string,
> event_time string
> )
> PARTITIONED BY (event_month string, step string);
OK
加载数据:
hive> load data local inpath '/home/hadoop/order.txt' overwrite into table order_multi_partition partition (event_month='2014-05',step=1);
查询：
hive> select * from order_multi_partition;
OK
110703007267488 2014-05-01 06:01:12.334+01 2014-05 1
1
10101043505096 2014-05-01 07:28:12.342+01 2014-05 1
1
10103043509747 2014-05-01 07:50:12.33+01 2014-05 1
1
10103043501575 2014-05-01 09:27:12.33+01 2014-05 1
1
10104043514061 2014-05-01 09:03:12.324+01 2014-05 1
Time taken: 0.228 seconds, Fetched: 5 row(s)
在HDFS上查询文件结构：
[hadoop@zydatahadoop001 ~]$ hdfs dfs -ls /user/hive/warehouse/order_multi_partition/event_month=2014-05
18/01/09
Found 1 items
drwxr-xr-x - hadoop supergroup 0 2018-01-09 22:52 /user/hive/warehouse/order_multi_partition/event_month=2014-05/step=1
单级分区在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 中默认是静态分区，想要使用动态分区，需要设置如下参数，
可以使用临时设置，你也可以写在配置文件（hive-site.xml）里，永久生效。
临时配置如下
开启动态分区（默认为false，不开启）
set hive.exec.dynamic.partition=true;
（开启动态分区）
set hive.exec.dynamic.partition.mode=nonstrict;
（指定动态分区模式，默认为strict，即必须指定至少一个分区为静态分区，
nonstrict模式表示允许所有的分区字段都可以使用动态分区。）
1
2
3
创建员工的动态分区表，分区字段为deptno；
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";
1
2
3
4
5
6
7
8
9
10
11
如果使用静态分区，根据deptno要写多条语句如下：
CREATE TABLE emp_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";
insert into table emp_partition partition(deptno=10)
select empno,ename ,job ,mgr ,hiredate ,salary ,comm
from emp where deptno=10;
insert into table emp_partition partition(deptno=20)
select empno,ename ,job ,mgr ,hiredate ,salary ,comm from emp where deptno=20;
insert into table emp_partition partition(deptno=30)
select empno,ename ,job ,mgr ,hiredate ,salary ,comm from emp where deptno=30;
查询结果：
hive> select * from emp_partition;
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
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
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
我们再来看看使用动态分区的效果并注意他的基本语法与静态分区的区别：
insert into table emp_dynamic_partition partition(deptno)
select empno,ename ,job ,mgr ,hiredate ,salary ,comm, deptno from emp;
一条语句完成
hive> select * from emp_dynamic_partition;
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
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
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
查看HDFS上文件目录结构
[hadoop@zydatahadoop001 ~]$ hdfs dfs -ls /user/hive/warehouse
Found 5 items
drwxr-xr-x - hadoop supergroup 0 2018-01-09 20:30 /user/hive/warehouse/emp
drwxr-xr-x - hadoop supergroup 0 2018-01-10 00:38 /user/hive/warehouse/emp_dynamic_partition
drwxr-xr-x - hadoop supergroup 0 2018-01-10 00:34 /user/hive/warehouse/emp_partition
drwxr-xr-x - hadoop supergroup 0 2018-01-09 22:52 /user/hive/warehouse/order_multi_partition
drwxr-xr-x - hadoop supergroup 0 2018-01-09 22:42 /user/hive/warehouse/order_partition
动态分区时，第一次执行会碰到这个错误动态分区是严格模式，需要设置成非严格模式执行set hive.exec.dynamic.partition.mode=nonstrict
hive (d6_hive)> insert into emp_dynamic_partition1 partition(deptno) select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;
FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict
