hive sql分区查询 hive 查询分区表

目录

总结：

分区结构图和原理：

上手案例实操： 

总结：

---

总结：

①创建分区表的时候，指定非表字段的分区字段，使用partitioned by ；②向分区表中插入数据的时候，在表名的后面要加上partition（分区字段名=分区字段值）③如果一个表是分区表，那么该分区表在HDFS上是一个以表名为名的路径，那么同时，该路径下还有分区的路径，以分区字段=分区字段值的形式命名。④使用where来进行分区查询。⑤alter add多个用空格，alter drop 多个用逗号。⑥：分区的目的是提高查询效率

分区结构图和原理：

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

分区表的目的是为了提高查询效率。

上手案例实操： 

1，创建分区表：分区的字段不能是表中的字段
create table stu_par(id int,name string)
partitioned by (month string)  
row format delimited
fields terminated by '\t';

使用下面的命令查看该表分区的字段
0: jdbc:hive2://hadoop108:10000> desc formatted stu_par;

2，向分区表中导入数据：
 load data local inpath '/opt/module/hive/stu.txt' into table stu_par partition(month = '12');

0: jdbc:hive2://hadoop108:10000> select * from stu_par;
OK
+-------------+---------------+----------------+--+
| stu_par.id  | stu_par.name  | stu_par.month  |
+-------------+---------------+----------------+--+
| 1001        | zhangfei      | 12             |
| 1002        | liubei        | 12             |
| 1003        | guanyu        | 12             |
| 1004        | zhaoyun       | 12             |
| 1005        | caocao        | 12             |
| 1006        | zhouyu        | 12             |
+-------------+---------------+----------------+--+
发现分区的字段信息也作为表的字段信息显示出来，所以分区字段得名虚字段。

3，继续执行：
load data local inpath '/opt/module/hive/stu.txt' into table stu_par partition(month = '11');

load data local inpath '/opt/module/hive/stu.txt' into table stu_par partition(month = '10');

4，查看HDFS中的表信息：
0: jdbc:hive2://hadoop108:10000> !sh hadoop fs -ls /user/hive/warehouse/db_hive.db/stu_par
Found 3 items
drwxr-xr-x   - isea supergroup      0 2018-12-01 04:34 /user/hive/warehouse/db_hive.db/stu_par/month=10
drwxr-xr-x   - isea supergroup      0 2018-12-01 04:34 /user/hive/warehouse/db_hive.db/stu_par/month=11
drwxr-xr-x   - isea supergroup      0 2018-12-01 04:30 /user/hive/warehouse/db_hive.db/stu_par/month=12

可以看出分区表会根据分区的字段和分区字段值的形式，组成  字段=字段值 的形式形成一个文件夹，在该
文件夹下存储着分区字段的信息

5，查询某个分区的字段使用where：
0: jdbc:hive2://hadoop108:10000> select * from stu_par where month = 10;
OK
+-------------+---------------+----------------+--+
| stu_par.id  | stu_par.name  | stu_par.month  |
+-------------+---------------+----------------+--+
| 1001        | zhangfei      | 10             |
| 1002        | liubei        | 10             |
| 1003        | guanyu        | 10             |
| 1004        | zhaoyun       | 10             |
| 1005        | caocao        | 10             |
| 1006        | zhouyu        | 10             |
+-------------+---------------+----------------+--+

6，查询表的分区情况：
0: jdbc:hive2://hadoop108:10000> show partitions stu_par;
OK
+------------+--+
| partition  |
+------------+--+
| month=10   |
| month=11   |
| month=12   |
+------------+--+

6，多分区查询可以使用union，要使用MapReduce：
select * from stu_par where month = 10
union
select * from stu_par where month = 11;

+---------+-----------+------------+--+
| _u2.id  | _u2.name  | _u2.month  |
+---------+-----------+------------+--+
| 1001    | zhangfei  | 10         |
| 1001    | zhangfei  | 11         |
| 1002    | liubei    | 10         |
| 1002    | liubei    | 11         |
| 1003    | guanyu    | 10         |
| 1003    | guanyu    | 11         |
| 1004    | zhaoyun   | 10         |
| 1004    | zhaoyun   | 11         |
| 1005    | caocao    | 10         |
| 1005    | caocao    | 11         |
| 1006    | zhouyu    | 10         |
| 1006    | zhouyu    | 11         |
+---------+-----------+------------+--+

7，增加，和删除多个分区：增加空格，删除逗号：
增加分区，多个
alter table stu_par add partition(month = '09') partition(month = '08');
删除分区，多个
alter table stu_par drop partition(month = '09'),partition(month = '08');

8，创建多级分区，在HDFS上，二级分区将会是一级分区的子文件夹：
> load data local inpath '/opt/module/hive/stu.txt' into table stu_par2 partition(month = '12', day = '01');

0: jdbc:hive2://hadoop108:10000> select * from stu_par2;
OK
+--------------+----------------+-----------------+---------------+--+
| stu_par2.id  | stu_par2.name  | stu_par2.month  | stu_par2.day  |
+--------------+----------------+-----------------+---------------+--+
| 1001         | zhangfei       | 12              | 01            |
| 1002         | liubei         | 12              | 01            |
| 1003         | guanyu         | 12              | 01            |
| 1004         | zhaoyun        | 12              | 01            |
| 1005         | caocao         | 12              | 01            |
| 1006         | zhouyu         | 12              | 01            |
+--------------+----------------+-----------------+---------------+--+

增加分区，获取分区信息：
> alter table stu_par2 add partition(month = '12',day = '02');
0: jdbc:hive2://hadoop108:10000> show partitions stu_par2;
OK
+------------------+--+
|    partition     |
+------------------+--+
| month=12/day=01  |
| month=12/day=02  |
+------------------+--+

alter table stu_par2 add partition(month = '12',day = '02');
就是在Metastore中的partition表中添加了一个字段的信息，如下图：

如下图：

总结：

①创建分区表的时候，指定非表字段的分区字段，使用partitioned by ；②向分区表中插入数据的时候，在表名的后面要加上partition（分区字段名=分区字段值）③如果一个表是分区表，那么该分区表在HDFS上是一个以表名为名的路径，那么同时，该路径下还有分区的路径，以分区字段=分区字段值的形式命名。④使用where来进行分区查询。⑤alter add多个用空格，alter drop 多个用逗号；⑥分区的目的是为了提高查询效率