Hive的外部表、内部表、分区表、分桶表

目录

 一、内部表与外部表

1.内部表

        （1）创建内部表

         （2）查看内部表数据存储

        （3）自行指定分隔符

         （4）删除内部表

2.外部表

        1.外部表创建（先有表后有数据）

         2.创建外部表（先有数据后有表）

         3.查看表类型

​编辑         4.表转换

 二、分区表

        （1）基本语法

         （2）构建多分区表

三、分桶表

        （1）创建分桶表

         （2）分桶表数据加载

 （3）分桶表的hash取模原理

---

一、内部表与外部表

        Hive可以创建的表有好几种类型：

        ①内部表（Hive管理表，可持久使用）

        ②外部表（临时链接外部数据）

        ③分桶表

        ④分区表

1.内部表

        内部表（create table table_name ······）

        未被external关键字修饰的即为内部表，即普通表。内部表又称为管理表，内部表数据的存储位置由hive.metastore.warehouse.dir参数决定（默认：/user/hive/warehouse），删除内部表会直接删除元数据（metastore）及存储数据。内部表不适合与其他工具共享数据。

        （1）创建内部表

        create table test1(id int ,name sting);

         （2）查看内部表数据存储

       hadoop fs  -cat  /user/hive/warehive/myhive.db/表名/*

        （3）自行指定分隔符

        列名与数据看似是挤在一起的，但其实列名与数据之间是有分隔符的默认的数据分隔符是：” \001” 是一种特殊字符，是 ASCII 值，键盘是打不出来。

insert into test2 values(1,"zhangsan"),(2,"wangwu"),(3,"lisi");   #表示用\t分隔

         （4）删除内部表

        drop table 表名;

        查看数据是否存在

2.外部表

        外部表（create external table table_name ······location······）

        被external关键字修饰的即外部表，即关联表。外部表是指表数据可以在任何位置，通过location关键字指定。数据存储的不同也代表了这个表在理念上并不是Hive内部管理的，而是可以随意临时链接到外部数据上的。所以删除外部表的时候，仅仅是元数据而不是数据本身。

        1.外部表创建（先有表后有数据）

        （1）在Linux准备一个文件，数据列用\t隔开

        

         （2）将外部数据上传到location目录

        ①查看/tmp目录是否有重复目录

         ②创建外部表

        create external table test_ext1(id int,name string) row format delimited fields terminated by "\t" location "/tmp/test_ext1/" ;

        ③ 上传数据

        hdfs dfs -put test_external.txt /tmp/test_ext1/

         2.创建外部表（先有数据后有表）

        ①创建一个目录并将test_external.txt上传

         ②创建外部表test_ext2

        create external table test_ext2(id int ,name string ) row format delimited fields terminated by "\t" location "/tmp/test_ext2/"

         ③查看数据

         3.查看表类型

        desc formatted 表名;

         4.表转换

        内部表转外部表：alter table 表名 set tblproperties('EXTERNAL'='TRUE');

        外部表转内部表：alter table 表名 set tblproperties('EXTERNAL'='FALSE');    

 二、分区表

        将大的文件分成一个个小的文件，操作的时候直接操作小文件，hive支持多个字段进行分区，多分区带有层级关系，例如公司的季度表，可以按照年分区，然后年里面再使用月分区，月里面再使用日分区。

        （1）基本语法

        create table tablename(字段...) partitioned by (分区列 列类型...) row format delimited fields terminated by " ";

         （2）构建多分区表

        create table tablename(字段...) partitioned by(分区列 列类型，...，...) row fomat delimited fields terminated by ' '；

三、分桶表

        分桶和分区一样，也是一种通过改变表的存储模式，从而完成对表优化的一种调优方式，但和分区不同，分区是将表拆分到不同的子文件夹中进行存储，而分桶是将表拆分到固定数量发不同文件中进行存储。

        分桶表优势：在单值过滤、join、group by能够更加快速便捷

        （1）创建分桶表

        开启分桶的自动优化（自动匹配reduce task数量和桶数量一致）

        set hive.enforce.bucketing=true;

        创建分桶表

        create table tablename(字段...) clustered by (字段) into 3 buckets row format delimited fields terminated by ' \t ';

         （2）分桶表数据加载

        分桶表数据加载（只能使用insert select），因为数据的划分基于分桶列的值进行hash取模来决定。由于load data不会触发mapreduce，也就没有计算过程1（无法执行hash算法，只是简单的移动数据，所以无法用于分桶表数据插入）

        1.创建临时中转表

        2.向临时表load data数据

        3.从中转表进行insert select向分桶表加载数据

         （3）分桶表的hash取模原理

Hash算法是一种数据加密算法，其主要特征：

        ①同样的值被hash加密后的结果是一致的

        比如Hadoop被hash后结果为12345，无论计算多少次，字符串‘Hadoop’结果都是12345

基于如上特征，再辅以3个分桶文件的基础上，将hash的结果基于3取模（除以3取余数），那么可以得到如下结果：

        ①无论什么数据，得到的取模结果均为：0、1、2其中一个。

        ②同样的数据得到的结果一致。