HIVE-分区表详解以及实例

转自https://www.cnblogs.com/kouryoushine/p/7801924.html https://blog.csdn.net/ytp552200ytp/article/details/90697122

什么是分区表？

分区表就是在系统上建立文件夹，把分类数据放在不同文件夹下面，加快查询速度。

关键点1：partitioned by (dt String,country string); 创建表格时，指明了这是一个分区表。将建立双层目录，第一次目录的名字是dt，第二次目录名字是country 。
PARTITIONED BY子句中定义列，是表中正式的列，成为分区列。但是数据文件中并没有这些值，仅代表目录。

关键点2： partition (dt=‘2001-01-01’,country=‘GB’); 上传数据时，把数据分别上传到不同分区中。也就是分别放在不同的子目录下。

理解分区就是文件夹分而治之，查询的时候可以当作列名来显示查询的范围。

实例操作

1、创建分区表

创建一个分区表，分区列为dt和country

hive> create table logs(ts bigint,line string)
    > partitioned by (dt string,country string);

注意：分区字段不在创建表的字段中定义，而是通过partitioned by中定义，是表中正式的列

2、给分区表添加数据

hive> load data local inpath '/root/hive/partitions/file1' into table logs
    > partition (dt='2001-01-01',country='GB');

上面语句的效果是在hdfs系统上建立了一个层级目录
　-logs
　
　　　　-dt=2001-01-01
　　　　
　　　　-country=GB
　　　　

继续给分区表添加数据

我们继续执行下面语句，先看一下什么效果
hive>  load data local inpath '/root/hive/partitions/file2' into table logs
    > partition (dt='2001-01-01',country='GB');
Loading data to table default.logs partition (dt=2001-01-01, country=GB)
OK
Time taken: 1.379 seconds
hive>  load data local inpath '/root/hive/partitions/file3' into table logs
    > partition (dt='2001-01-01',country='US');
Loading data to table default.logs partition (dt=2001-01-01, country=US)
OK
Time taken: 1.307 seconds
hive>  load data local inpath '/root/hive/partitions/file4' into table logs
    > partition (dt='2001-01-02',country='GB');
Loading data to table default.logs partition (dt=2001-01-02, country=GB)
OK
Time taken: 1.253 seconds
hive>  load data local inpath '/root/hive/partitions/file5' into table logs
    > partition (dt='2001-01-02',country='US');
Loading data to table default.logs partition (dt=2001-01-02, country=US)
OK
Time taken: 1.07 seconds
hive>  load data local inpath '/root/hive/partitions/file6' into table logs
    > partition (dt='2001-01-02',country='US');
Loading data to table default.logs partition (dt=2001-01-02, country=US)
OK
Time taken: 1.227 seconds

到HDFS上查看，发现建立了下面层级目录
├── dt=2001-01-01/
│ ├── country=GB/
│ │ ├── file1
│ │ └── file2
│ └── country=US/
│ └── file3
└── dt=2001-01-02/
├── country=GB/
│ └── file4
└── country=US/
├── file5
└── file6

3、分区表的查询

条件限定了country='GB’目录所以只有file1,2,4的内容输出

hive> select ts,dt,line 
    > from logs
    > where country='GB';
OK
1    2001-01-01    Log line 1
2    2001-01-01    Log line 2
4    2001-01-02    Log line 4

现在只查看dt=2001-01-02目录下country=US的文件夹下的数据。

hive> select ts,dt,line
> from logs    
> where dt='2001-01-02'     
> and country='US';
OK
5	2001-01-02	Log line 5
6	2001-01-02	Log line 6

注意：查询的时候可以把分区列当作列名来显示查询的范围。

4、分区表的复制

4.1复制表结构到新表

hive (default)> create table new_table like old_table;
OK
Time taken: 0.099 seconds
hive (default)> desc new_table;
OK
col_name        data_type       comment
age                     bigint                                      
height                  string                                      
weight                  string                                      
p_month                 int                                         
p_day                   int                                         
p_hour                  int                                         
                 
# Partition Information          
# col_name              data_type               comment             
                 
p_month                 int                                         
p_day                   int                                         
p_hour                  int                                         
Time taken: 0.093 seconds, Fetched: 13 row(s)
hive (default)> select * from new_table limit 10;
OK
new_table.age   new_table.height        new_table.weight        new_table.p_month       new_table.p_day new_table.p_hour
Time taken: 0.566 seconds
hive (default)> show partitions new_table;
OK
partition
Time taken: 0.063 seconds
p_month=201609/p_day=20160908/p_hour=2016090800
Time taken: 0.202 seconds, Fetched: 1 row(s)

4.1 将使用命令hadoop fs -cp旧表数据复制到新表的hdfs目录下：

[hadoop@node1 ~]$ hadoop fs -cp /user/hive/warehouse/old_table/*  /user/hive/warehouse/new_table/

4.2 使用命令 MSCK REPAIR TABLE new_table刷新原数据信息：

hive (default)> MSCK REPAIR TABLE new_table;
OK
Partitions not in metastore:    new_table:p_month=201609/p_day=20160908/p_hour=2016090800
Repair: Added partition to metastore new_table:p_month=201609/p_day=20160908/p_hour=2016090800
Time taken: 0.447 seconds, Fetched: 2 row(s)

4.3 使用sql复制表

insert overwrite table new_table partition (p_month,p_day,p_hour) select * from old_table

4.4 查询可看到new_table的数据：

hive (default)> select * from new_table;
OK
new_table.age   new_table.height        new_table.weight        new_table.p_month       new_table.p_day new_table.p_hour
25      170     70      201609  20160908        2016090800
24      175     65      201609  20160908        2016090800
27      180     80      201609  20160908        2016090800
Time taken: 0.5 seconds, Fetched: 3 row(s)

其他sql语句

①查看创表语句

show create table   表名

②删表语句

drop table 表名