hive(四) -- 内(外)部表、分区和分桶表

比眉伴天荒 2024-03-16 11:20 10阅读 0赞

### 内部表和外部表 ###

默认情况下创建的表就是内部表，Hive拥有该表的结构和文件。换句话说，Hive完全管理表（元数据和数据）的生命周期，类似于RDBMS中的表。当你删除内部表时，它会删除数据以及表的元数据。可以使用DESCRIBE FORMATTED tablename,来获取表的元数据描述信息，从中可以看出表的类型。

![a64007b793de08142146b2c7fe25ebc4.png][]

外部表（External table )中的数据不是Hive拥有或管理的，只管理表元数据的生命周期。要创建一个外部表，需要使用**EXTERNAL**语法关键字。删除外部表只会删除元数据，而不会删除实际数据。在Hive外部仍然可以访问实际数据。实际场景中，外部表搭配**location语法指定数据的路径**，可以让数据更安全。

**主要差异**

*  无论内部表还是外部表，Hive都在Hive Metastore中管理表定义、字段类型等元数据信息。
 *  删除内部表时，除了会从Metastore中删除表元数据，还会从HDFS中删除其所有数据文件。
 *  删除外部表时，只会从Metastore中删除表的元数据，并保持HDFS位置中的实际数据不变。

-- 创建内部表 加载数据
    create table t_user_inner(
        id int,
        uname string,
        pwd string,
        sex string,
        age int
    )row format delimited fields terminated by ',';
    load data local inpath '/root/user.txt' into table t_user_inner;
    -- 查看表信息
    desc formatted t_user_inner ;
    -- 创建外部表 加载数据
    create external table t_user_ext(
             id int,
             uname string,
             pwd string,
             sex string,
             age int
    )row format delimited fields terminated by ',';
    load data local inpath '/root/user.txt' into table t_user_ext;
    -- 查看表信息
    desc formatted table t_user_ext;
    
    
    -- 删除内部表  数据被删除了
    drop  table t_user_inner;
    -- 删除外部表  数据并没有被删除
    drop  table t_user_ext;
    
    -- 再次重新创建 t_user_ext 可以直接查询数据
    select * from  t_user_ext;
    
    -- 将t_user_ext 转换为内部表
    alter table t_user_ext set tblproperties('EXTERNAL'='FALSE'); -- 要求KV的大小写
    -- 查询表信息发现 Table Type: MANAGED_TABLE
    desc formatted t_user_ext; 
    --将t_user_ext 转换为外部表
    alter table t_user_ext set tblproperties('EXTERNAL'='true');
    -- 查询表信息发现 Table Type:EXTERNAL_TABLE
    desc formatted t_user_ext;

### 分区表 ###

分区表实际上就是将表中的数据以某种维度进行划分文件夹管理 ,当要查询数据的时候,根据维度直接加载对应文件夹下的数据! 不用加载整张表所有的数据再进行过滤, 从而提升处理数据的效率!

比如在一个学生表中想查询某一个年级的学生,如果不分区,需要在整个数据文件中全表扫描,但是分区后只需要查询对应分区的文件即可.

![ff344f9770a236f1bb3b586d3ca7b68c.png][]

#### 静态分区 ####

所谓静态分区指的是**分区的属性值是由用户在加载数据的时候手动指定的**。

**1.创建单分区表:**

-- 创建学生表 分区字段为年级grade
    CREATE TABLE  t_student (
        sid int,
        sname string
     ) partitioned by(grade int)   -- 指定分区字段
    row format delimited fields terminated by ',';
    -- 注意∶分区字段不能是表中已经存在的字段，因为分区字段最终也会以虚拟字段的形式显示在表结构上。
    select * from t_student;
    +----------------+------------------+------------------+
    | t_student.sid  | t_student.sname  | t_student.grade  |
    +----------------+------------------+------------------+
    +----------------+------------------+------------------+

stu01.txt
    1,zhangsan,1
    2,lisi,1
    3,wangwu,1
    
    stu02.txt
    4,zhaoliu,2
    5,lvqi,2
    6,maba,2
    
    stu03.txt
    7,liuyan,3
    8,tangyan,3
    9,jinlian,3

-- 静态分区需要用户手动加载数据 并指定分区
    load  data local  inpath '/root/stu01.txt' into table t_student partition(grade=1);
    load  data local  inpath '/root/stu02.txt' into table t_student partition(grade=2);
    load  data local  inpath '/root/stu03.txt' into table t_student partition(grade=3);
    -- 查询
    select * from t_student where grade=1;
    +----------------+------------------+------------------+
    | t_student.sid  | t_student.sname  | t_student.grade  |
    +----------------+------------------+------------------+
    | 1              | zhangsan         | 1                |
    | 2              | lisi             | 1                |
    | 3              | wangwu           | 1                |
    +----------------+------------------+------------------+

![a8e93049419e0af1af17f6caec845540.png][]

**注意:**文件中的数据放入到哪个分区下就属于当前分区的数据,即使数据有误,也会按照当前分区处理

stu03.txt
    7,liuyan,3
    8,tangyan,3
    9,jinlian,3
    10.aaa,4
    
    load  data local  inpath '/root/stu03.txt' overwrite into table t_student partition(grade=3);
    
    select * from t_student where grade=3;
    -- 最后一条记录虽然写的是4 但是 放到了年级3分区下 效果也是年级3
    +----------------+------------------+------------------+
    | t_student.sid  | t_student.sname  | t_student.grade  |
    +----------------+------------------+------------------+
    | 7              | liuyan           | 3                |
    | 8              | tangyan          | 3                |
    | 9              | jinlian          | 3                |
    | 10             | aaa              | 3                |
    +----------------+------------------+------------------+

**2.创建多分区表**

-- 创建学生表 分区字段为年级grade 班级clazz
    CREATE TABLE  t_student02 (
        sid int,
        sname string
     ) partitioned by(grade int,clazz int)   -- 指定分区字段
    row format delimited fields terminated by ',';

1年级1班
    stu0101.txt  
    1,zhangsan,1,1
    2,lisi,1,1
    
    1年级2班
    stu0102.txt
    3,wangwu,1,2
    
    2年级1班
    stu0201.txt
    4,zhangsan,2,1
    5,lisi,2,1
    6,maba,2,1
    
    3年级1班
    stu0301.txt
    7,liuyan,3,1
    8,tangyan,3,1
    3年级2班
    9,dalang,3,2
    10,jinlian,3,2

load  data local  inpath '/root/stu0101.txt' into table t_student02 partition(grade=1,clazz=1);
    load  data local  inpath '/root/stu0102.txt' into table t_student02 partition(grade=1,clazz=2);
    load  data local  inpath '/root/stu0201.txt' into table t_student02 partition(grade=2,clazz=1);
    load  data local  inpath '/root/stu0301.txt' into table t_student02 partition(grade=3,clazz=1);
    load  data local  inpath '/root/stu0302.txt' into table t_student02 partition(grade=3,clazz=2);
    
    select * from t_student02 where grade=1 and clazz=2;
    +------------------+--------------------+--------------------+--------------------+
    | t_student02.sid  | t_student02.sname  | t_student02.grade  | t_student02.clazz  |
    +------------------+--------------------+--------------------+--------------------+
    | 7                | liuyan             | 3                  | 1                  |
    | 8                | tangyan            | 3                  | 1                  |
    +------------------+--------------------+--------------------+--------------------+

![afcb877d1a5e1232b21c99608327b64a.png][]

注意:我们既然建立了分区,就要保证分区查询的的命中率,查询尽量使用设置的分区字段去查询.分区虽然避免了全表扫描,但是也可能会产生大量的小文件,有利有弊.

**3.分区其他操作(了解)**

-- 查看分区 
    show partitions t_student02;
    -- 添加分区
    alter table t_student02 add  partition (grade=4,clazz=1);
    -- 删除分区
    alter table t_student02 drop  partition (grade=4,clazz=1);

#### 动态分区 ####

静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断.

详细来说:静态分区需要我们自己手动load并指定分区,如果数据很多,那么是麻烦了.而动态分区指的是分区的字段值是基于查询结果（参数位置）自动推断出来的。核心语法就是insert+seclect。

开启动态分区首先要在hive会话中设置如下的参数

-- 临时设置 重新连接需要重新设置
    set hive.exec.dynamic.partition=true; 
    set hive.exec.dynamic.partition.mode=nonstrict;

其余参数配置如下:

设置为true表示开启动态分区的功能（默认为false） 
    --hive.exec.dynamic.partition=true; 
    
    设置为nonstrict，表示允许所有分区都是动态的（默认为strict） 严格模式至少有一个静态分区
    -- hive.exec.dynamic.partition.mode=nonstrict; 
    
    每个mapper或reducer可以创建的最大动态分区个数(默认为100) 
    比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，
    如果使用默认 值100，则会报错 
    --hive.exec.max.dynamic.partition.pernode=100; 
    
    一个动态分区创建可以创建的最大动态分区个数（默认值1000） 
    --hive.exec.max.dynamic.partitions=1000; 
    
    全局可以创建的最大文件个数（默认值100000） 
    --hive.exec.max.created.files=100000; 
    
    当有空分区产生时，是否抛出异常（默认false） 
    -- hive.error.on.empty.partition=false;

**操作步骤**

1.  创建文件并上传
2.  创建外部表指向文件(相当于临时表)
3.  创建动态分区表
4.  查询外部表将数据动态存入分区表中

创建文件并上传
    
    student.txt  
    
    1,zhangsan,1,1
    2,lisi,1,1
    3,wangwu,1,2
    4,zhangsan,2,1
    5,lisi,2,1
    6,maba,2,1
    7,liuyan,3,1
    8,tangyan,3,1
    9,dalang,3,2
    10,jinlian,3,2
    
    -- 将文件上传到hdfs根目录
    hdfs dfs -put student.txt  /stu

创建外部表指向文件(相当于临时表)  
    create external table t_stu_e(
        sid int,
        sname string,
        grade int,
        clazz int
    )row format delimited fields terminated by ","
    location "/stu";

创建动态分区表
    create  table  t_stu_d(
        sid int,
        sname string
    )partitioned by (grade int,clazz int)
    row format delimited fields terminated by ",";

查询外部表将数据动态存入分区表中
    insert overwrite table t_stu_d partition (grade,clazz) select  *  from t_stu_e ;
    
    select * from t_stu_d;

![8878b5608c908412f44650ee1202b8f4.png][]

### 分桶表 ###

**概述**

分桶表也叫做桶表，叫法源自建表语法中bucket单词，是一种用于优化查询而设计的表类型。

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。不合理的数据分区划分方式可能导致有的分区数据过多，而某些分区没有什么数据的尴尬情况 。分桶是将数据集分解为更容易管理的若干部分的另一种技术。

![b62c4c0469f9b562b56fc6266edf732e.png][]

对Hive(Inceptor)表分桶可以将表中记录按分桶键(字段)的哈希值分散进多个文件中，这些小文件称为桶。桶以文件为单位管理数据!**分区针对的是数据的存储路径；分桶针对的是数据文件。**

**分桶的原理**

Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

bucket num = hash_function(bucketing_column) mod   num_buckets
     分隔编号      哈希方法(分桶字段)              取模   分桶的个数

![e1e3e0fce1bd90772d82e5a4d71017ef.png][]

**分桶的好处**

1.  基于分桶字段查询时，减少全表扫描.
2.  根据join的字段对表进行分桶操作,join时可以提高MR程序效率，减少笛卡尔积数量.

![6a14bf27f8ff0e6f2e1368a7a227dbee.png][]

1.  分桶表数据进行高效抽样.数据量大时,使用抽样数据估计和推断整体特性.

**分桶表的创建**

1.准备person.txt上传到hdfs
    2.创建外部表指向person.txt
    3.创建分桶表
    4.查询外部表将数据加载到分桶表中

person.txt 
    public class Test02 {
        public static void main(String[] args) {
            for (int i = 1; i <= 10000; i++) {
                System.out.println(i + "," + "liuyan" + (new Random().nextInt(10000) + 10000));
            }
        }
    }
    
     hdfs dfs -mkdir /person
    hdfs dfs -put person.txt /person

2.创建外部表指向person.txt
    create external table  t_person_e(
         id int,
         pname string
    ) row format delimited fields terminated by ","
        location "/person";
    
    select  * from t_person_e;

create table  t_person(
        id int,
        pname string
    )clustered by(id) sorted by (pname) into 24 buckets
    row format delimited fields terminated by ",";

insert overwrite table t_person select * from t_person_e ;

![c1b2f5ebac498b18f7fc4d148634de0a.png][]

**桶表抽样**

-- tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 
    -- x表示从哪个bucket开始抽取。
       例如，table总bucket数为32，tablesample(bucket 3 out of 16)
       32 / 16  = 2  代表16桶为一组  抽取 第一组的第3桶  抽取第二组的第3桶 也就是第19桶
    -- y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。
        tablesample(bucket 3 out of 64)
        32/64 = 2分之一      64桶为一组  不够一组 取第三桶的 前百分之50
        
        select * from t_person tablesample(bucket 4 out of 12); 
        24/12 抽取2桶数据      12桶一组 抽取 第一组第4桶 第二组 第4桶 4+12 =16桶

[a64007b793de08142146b2c7fe25ebc4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/578bf9d2edd7460faeeceaa71676814f.png
[ff344f9770a236f1bb3b586d3ca7b68c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/3399f8fc149c421bafe1cc567509091f.png
[a8e93049419e0af1af17f6caec845540.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/51d45a9e7b3947d59b8c92f6bac49380.png
[afcb877d1a5e1232b21c99608327b64a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/03eaa58fdd304c06972f5be5be8fbb84.png
[8878b5608c908412f44650ee1202b8f4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/ecc80657b06e46939ad6d8e128d106c3.png
[b62c4c0469f9b562b56fc6266edf732e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/8af47cddf1414ef0ae829ef396b6910a.png
[e1e3e0fce1bd90772d82e5a4d71017ef.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/d48e9b0da7db403380e4ba87cd1c2b16.png
[6a14bf27f8ff0e6f2e1368a7a227dbee.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/197a44d3ec074b5d9e269061f47b0cde.png
[c1b2f5ebac498b18f7fc4d148634de0a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/e65a62431b9c4ef3ade614055f99f7be.png