Hive基础之分区表

最新推荐文章于 2024-05-10 07:38:43 发布

iqifenxia

最新推荐文章于 2024-05-10 07:38:43 发布

阅读量4.8k

点赞数 4

文章标签： vue.js html5 java

原文链接：https://www.fgba.net/forum-53-1.html

版权

我们在做数据挖掘和分析的时候有时候只是针对某一段时间或者某一个月的数据，而我们在创建表的时候是将所有时间的数据都放在一起，这样就会导致我们在进行分析的时候会分析所有的数据，如果数据量很大的时候效率就会很慢，这时候Hive就提供一种特性叫分区表，可以针对一个月的数据进行查询和分析。原来可能要分析100G的数据文件，经过分区后可能只需要分析10G的文件，这样可以大大提升数据分析的效率。下面我们就来详细看一下hive的分区表。

1.分区表定义

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区的所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成更小的数据集。这个在企业中会特别有用，尤其是分区表和外部表相结合，应用特别广泛。在hive中查询时通过WHERE子句中的表达式来选择查询所需要的指定的分区，这样的查询效率会提高很多。还是以emp表为例，分区表的创建脚本如下：

create EXTERNAL table IF NOT EXISTS default.emp_part(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
partitioned by (month string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
hive (default)> create EXTERNAL table IF NOT EXISTS default.emp_part(
              > empno int,
              > ename string,
              > job string,
              > mgr int,
              > hiredate string,
              > sal double,
              > comm double,
              > deptno int
              > )
              > partitioned by (month string)
              > ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
OK
Time taken: .996 seconds
hive (default)> show tables;
OK
tab_name
dept
dept_cats
emp
emp_ext
emp_ext2
emp_part
Time taken: .171 seconds, Fetched: 6 row(s)

查看分区表的信息：

hive (default)> desc formatted emp_part;
OK
col_name        data_type       comment
# col_name              data_type               comment             
                 
empno                   int                                         
ename                   string                                      
job                     string                                      
mgr                     int                                         
hiredate                string                                      
sal                     double                                      
comm                    double                                      
deptno                  int                                         
                 
# Partition Information          
# col_name              data_type               comment             
                 
month                   string                                      
                 
# Detailed Table Information             
Database:               default                  
Owner:                  hive                     
CreateTime:             Thu Feb 14 21:37:29 CST 2019     
LastAccessTime:         UNKNOWN                  
Protect Mode:           None                     
Retention:                                      
Location:               hdfs://node1:8020/user/hive/warehouse/emp_part   
Table Type:             EXTERNAL_TABLE           
Table Parameters:                
        EXTERNAL                TRUE                
        numPartitions                              
        transient_lastDdlTime   1550151449          
                 
# Storage Information            
SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe      
InputFormat:            org.apache.hadoop.mapred.TextInputFormat         
OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat       
Compressed:             No                       
Num Buckets:            -1                       
Bucket Columns:         []                       
Sort Columns:           []                       
Storage Desc Params:             
        field.delim             \t                  
        serialization.format    \t                  
Time taken: .563 seconds, Fetched: 41 row(s)

可以看到其中的分区信息如下：

# Partition Information          
# col_name              data_type               comment             
                 
month                   string

其中分区的字段是month，分区的数据类型是string类型。

2.加载数据到分区表

要加载数据到分区表，只需在原来的加载数据的语句上增加partition关键字，同时指定分区的字段值即可。

hive (default)> load data local inpath '/opt/datas/emp.txt' into table default.emp_part partition (month='201902') ;
Loading data to table default.emp_part partition (month=201902)
Partition default.emp_part{month=201902} stats: [numFiles=1, numRows=, totalSize=659, rawDataSize=]
OK
Time taken: 5.955 seconds

查看hdfs上的文件目录：

hive (default)> dfs -ls /user/hive/warehouse/emp_part;
Found 1 items
drwxrwxrwt   - hive hive           2019-02-14 21:45 /user/hive/warehouse/emp_part/month=201902
hive (default)> dfs -ls /user/hive/warehouse/emp_part/month=201902;
Found 1 items
-rwxrwxrwt   3 hive hive        659 2019-02-14 21:45 /user/hive/warehouse/emp_part/month=201902/emp.txt

可以看到在/user/hive/warehouse/emp_part目录下会多一个month=201902的子目录，而数据文件就存放在该目录下。

3.查询分区数据

直接使用分区字段就可以查询分区中的数据：

hive (default)> select * from emp_part where month = '201902' ;
OK
empno   ename   job     mgr     hiredate        sal     comm    deptno  month
7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    20      201902
7499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   30     201902
7521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   30     201902
7566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    20      201902
7654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  30     201902
7698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    30      201902
7782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    10      201902
7788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    20      201902
7839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    10     201902
7844    TURNER  SALESMAN        7698    1981-9-8        1500.0  .0     30     201902
7876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    20      201902
7900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    30      201902
7902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    20      201902
7934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10      201902
Time taken: 5.537 seconds, Fetched: 14 row(s)

这样我们就可以实现按月或者按日来进行查询数据，在互联网中对网站日志进行分析的时候经常会遇到按日或者按月统计PV值，这个时候使用分区表就会大幅的提高统计效率。

如果要统计一个季度的数据，这可以将三个月的查询结果union在一起，然后得出一个最终结果。

select count(distinct ip) from emp_part where month = '201901'
union
select count(distinct ip) from emp_part where month = '201902'
union
select count(distinct ip) from emp_part where month = '201903';

使用上面的查询语句就可以得出一个季度的统计结果，在企业的应用中，这个是比较常见的。

4.二级分区

前面介绍了只有一个字段的分区，hive还支持二级分区，可以在之前的分区的基础上再增加一个字段。二级分区表的使用脚本如下：

create EXTERNAL table IF NOT EXISTS default.emp_part(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
partitioned by (month string,day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;

load data local inpath '/opt/datas/emp.txt' into table default.emp_part partition (month='201902',day='14') ;

select * from emp_part where month = '201902' and day='14' ;

5.注意事项

在没有加分区字段的表，我们只要将数据文件上传到hdfs上对应的文件夹下就可以查询到数据，但是分区表不行。我们重新建一张分区表并实验一下。

hive (default)> create table IF NOT EXISTS default.dept_part(
              > deptno int,
              > dname string,
              > loc string
              > )
              > partitioned by (day string)
              > ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
OK
Time taken: .313 seconds
hive (default)> show tables;
OK
tab_name
dept
dept_cats
dept_part
emp
emp_ext
emp_ext2
emp_part
Time taken: .088 seconds, Fetched: 7 row(s)
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_part/day=20190214 ;
hive (default)> dfs -put /opt/datas/dept.txt /user/hive/warehouse/dept_part/day=20190214 ;
hive (default)> dfs -ls /user/hive/warehouse/dept_part/day=20190214;
Found 1 items
-rw-r--r--   3 hive hive         82 2019-02-14 22:16 /user/hive/warehouse/dept_part/day=20190214/dept.txt
hive (default)> select * from dept_part;
OK
deptno  dname   loc     day
Time taken: .214 seconds

可以看到，虽然我们把数据文件放到了对应的分区目录下，但是我们在hive中还是查不到数据。原来分区表相关的二手域名拍卖元数据是保持在数据库中的，我们直接上传文件到hdfs中，但是分区表的元数据没有创建，所以我们查询不到数据。我们登录到mysql数据库中查看一下元数据的信息：

[mysql@node1 ~]$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 459
Server version: 5.6.42-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use hive;
Database changed
mysql> select * from PARTITIONS;
+---------+-------------+------------------+--------------+-------+--------+----------------+
| PART_ID | CREATE_TIME | LAST_ACCESS_TIME | PART_NAME    | SD_ID | TBL_ID | LINK_TARGET_ID |
+---------+-------------+------------------+--------------+-------+--------+----------------+
|      79 |  1550151929 |                 | month=201902 |   129 |     48 |           NULL |
+---------+-------------+------------------+--------------+-------+--------+----------------+
1 row in set (.00 sec)

可以看到分区表PARTITIONS中并没有我们后来新增的那个分区信息。如果要在hive中能够正常查询分区表信息就需要在这张表中插入相应的数据。解决这个问题有两种方式：

第一种：使用msck命令

hive (default)> msck repair table dept_part ;
OK
Partitions not in metastore:    dept_part:day=20190214
Repair: Added partition to metastore dept_part:day=20190214
Time taken: .396 seconds, Fetched: 2 row(s)
hive (default)> select * from dept_part;
OK
deptno  dname   loc     day
10      ACCOUNTING      NEW YORK        20190214
20      RESEARCH        DALLAS  20190214
30      SALES   CHICAGO 20190214
40      OPERATIONS      BOSTON  20190214
Time taken: .188 seconds, Fetched: 4 row(s)

可以看到现在能够查询出来相应的数据了。再重新查询一下mysql中的数据：

mysql> select * from PARTITIONS;
+---------+-------------+------------------+--------------+-------+--------+----------------+
| PART_ID | CREATE_TIME | LAST_ACCESS_TIME | PART_NAME    | SD_ID | TBL_ID | LINK_TARGET_ID |
+---------+-------------+------------------+--------------+-------+--------+----------------+
|      79 |  1550151929 |                 | month=201902 |   129 |     48 |           NULL |
|      98 |  1550154626 |                 | day=20190214 |   158 |     57 |           NULL |
+---------+-------------+------------------+--------------+-------+--------+----------------+
2 rows in set (.00 sec)

可以看到分区表中多了一条数据。

第二种：使用alter命令

我们再新建一个分区，在这个分区下导入同样的数据。

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_part/day=20190215 ;
hive (default)> dfs -put /opt/datas/dept.txt /user/hive/warehouse/dept_part/day=20190215 ;
hive (default)> alter table dept_part add partition(day='20190215');
OK
Time taken: .23 seconds
hive (default)> select * from dept_part;
OK
deptno  dname   loc     day
10      ACCOUNTING      NEW YORK        20190214
20      RESEARCH        DALLAS  20190214
30      SALES   CHICAGO 20190214
40      OPERATIONS      BOSTON  20190214
10      ACCOUNTING      NEW YORK        20190215
20      RESEARCH        DALLAS  20190215
30      SALES   CHICAGO 20190215
40      OPERATIONS      BOSTON  20190215
Time taken: .204 seconds, Fetched: 8 row(s)

可以看出，在修复分区信息后能过查询出来8条记录，说明我们新建的分区已经生效。在看一下分区表中的数据：

mysql> select * from PARTITIONS;
+---------+-------------+------------------+--------------+-------+--------+----------------+
| PART_ID | CREATE_TIME | LAST_ACCESS_TIME | PART_NAME    | SD_ID | TBL_ID | LINK_TARGET_ID |
+---------+-------------+------------------+--------------+-------+--------+----------------+
|      79 |  1550151929 |                 | month=201902 |   129 |     48 |           NULL |
|      98 |  1550154626 |                 | day=20190214 |   158 |     57 |           NULL |
|     101 |  1550155039 |                 | day=20190215 |   162 |     57 |           NULL |
+---------+-------------+------------------+--------------+-------+--------+----------------+
3 rows in set (.00 sec)

可以看出分区表中的元数据又多了一条，且其对应的分区名称为day=20190215。

6.查看表的分区信息

使用show partitions命令可以查看分区表中有多少个分区。

hive (default)> show partitions dept_part ;
OK
partition
day=20190214
day=20190215
Time taken: .199 seconds, Fetched: 2 row(s)