hive数据库hql基础操作02

1.内部表和外部表

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

外部表（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;

2.分区表

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

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

静态分区

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

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,1stu02.txt
4,zhaoliu,2
5,lvqi,2
6,maba,2stu03.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                |
+----------------+------------------+------------------+

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

stu03.txt
7,liuyan,3
8,tangyan,3
9,jinlian,3
10.aaa,4load  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                |
+----------------+------------------+------------------+

创建多分区表

-- 创建学生表 分区字段为年级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,11年级2班
stu0102.txt
3,wangwu,1,22年级1班
stu0201.txt
4,zhangsan,2,1
5,lisi,2,1
6,maba,2,13年级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                  |
+------------------+--------------------+--------------------+--------------------+

动态分区

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

详细来说:静态分区需要我们自己手动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;

分桶表

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

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

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

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

分桶好处:

1. 基于分桶字段查询时，减少全表扫描.

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

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

分桶表的创建

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 ;

桶表抽样

-- 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桶为一组  不够一组 取第三桶的 前百分之50select * from t_person tablesample(bucket 4 out of 12); 24/12 抽取2桶数据      12桶一组 抽取 第一组第4桶 第二组 第4桶 4+12 =16桶