hive-1 受控表简介(分区表 桶表)

 

 

1 受控表(managed table)：

 

hive中将创建的表和实际对应hdfs目录结构和文件相对应，如果删除hive中创建的表，对应hdfs的目录和文件

将不复存在，这种表叫做受控表。

 

受控表(managed table)包括内部表、分区表、桶表。

 

 

2 分区表简介：

 

a) 分区表是把数据放在不同的磁盘文件中，hive数据库会对不同分区进行单独管理，优化，

     最终会加快数据查询速度。

     每一年的数据放在不同hive目录下, 业务上也有这种需求。
b) 分区表分区的含义：也是把数据进行划分不同的区，hive中的区是指不同子文件夹中。

c) 分区表创建原因： 100M的学生信息，如果查询班级为1班的学生，在不使用分区表时，
     需要去100M中遍历查询，如果使用分区表，只需要去文件夹名称为1班的hdfs文件中查找即可。

d) 分区字段简介：

     d.1) 分区字段就是文件夹的标识名称，

     d.2) 在正常使用的时候，分区字段是作为正常字段被使用,但是在数据文件中不存在,

            仅作为虚拟列(virtual column)存在

 

e)  分区过多的坏处：
     如果分区过多，hive在扫描时，一级级的扫来扫去，会增加扫描成本， 在运行时，对于map端造成map任务增多。

 

f) 选用哪些字段作为分区呢：
   f.1) 选用平时查询比较频繁的字段，比如 地区 时间查，
   f.2) 分区后产生的文件并不是很多的字段来， 比如 按照姓名 ID来查询，就不能使用分区，否则要产生很多分区

 

g)  操作：

   g.1) 单分区表创建和查询：

 

 

linux内文件为：

/root/test/11/student
内容为：
1,zhangsan
2,lisi
/root/test/12/student1
内容为：
3,wangwu
4,zhaoliu
 

hive创建分区表：
create table student(id int, name string) partitioned by(grade int) row format delimited fields terminated by '\t';


导入数据到hive：
hive (default)>load data local inpath 'test/11/' into table student partition(grade=11); 
hive (default)>load data local inpath 'test/12/' into table student partition(grade=12); 

查询hive表：
hive (default)> select * from student where grade=12;
OK
id      name    grade
3       wangwu  12
4       zhaoliu 12
Time taken: 1.259 seconds
hive (default)> select * from student where grade=11;
OK
id      name    grade
1       zhangsan        11
2       lisi    11
Time taken: 0.218 seconds

 

 

 

分区后hdfs的样子：

 
 

 g.2) 多分区字段插入和查询写法：

create table member(id int, name string) partitioned by(year int, month int) row format delimited fields terminated by '\t';
load data local inpath '/usr/local/data/user4' into table member partition(year=2014, month=1);// hdfs中目录为  2014/1/member
load data local inpath '/usr/local/data/user5' into table member partition(year=2014, month=2);// hdfs中目录为  2014/2/member



hive> select * from member where year=2014 and month=1;
OK  此时索引字段会显示展示出来
1       zhangsan        2014    1
2       lisi    2014    1
3       wangwu  2014    1
4       zhaoliu 2014    1

 

  g.3) 缺陷：

选定分区字段之后， 结果会造成数据偏差特别大，这样整个查询时间受制于分区特别大的，对于整个作业的运行效率是不好的，
比如 淘宝按照用户所在省份来分区， 北京的订单用户要比青海西藏等偏远省的总和还要多很多

 

 

  g.4) 动态分区：

如果不开启动态分区, 在A表分区很多情况下，将A表数据加载到B表同时也需要B表有A表分区结构下,

写法为:

insert into table t8 partition(class="job1",city="beijing") select * from t3 where class="job1" and city="beijing";  
这样有多少分区就需要写多少遍，麻烦。

 

开启动态分区做法如下：
hive>set hive.exec.dynamic.partition=true;
hive>set hive.exec.dynamic.partition.mode=nostrict;

hive>select * from t8 partition(class,city) select keys,class,city from t3;
hive>show partitions t8;  看到t3数据以class,city形式进入到t8中。

 

 

 

 

分区表其余知识补充:

 

1)  分区字段数值应该是正规的不会被转义的：

分区表对分区字段格式有要求,但是对分区字段格式没有要求,

但是,分区字段应该以规范字段,如果是2015/11/12 或者非法字符 ， 或者可能出现乱码的符号 或者中文等来指定分区字段时,hive会自动给转码, 这样你在查询的时候,只能去hdfs目录下找到这个转码后的字段,

粘到Hive命令行写才能查询这个分区字段下的数据,即使这样,数据查询也会出现失误或者精度丢失。

 比如
create table student(id int, name string) partitioned by(day string) row format delimited fields terminated by '\t';

load data local inpath 'test/11/2.txt' into table student partition(day="2015/11/12");  ----> 出现问题 会被转码

 

2) 查看表的分区字段命令:

hive>show partitions student;

 

3) 查看指定分区下数据:

select * from t2 where day="2015-11-12";

 

4) 添加新分区:

hdfs dfs -mkdir /user/hive/warehouse/student/day=2015-08-09/
hdfs dfs -put install.log /user/hive/warehouse/student/day=2015-08-09/
hive>alter table student add partition(day="2015-08-09");  --->给student表增加分区

或者直接
hive (default)>load data local inpath 'install.log' into table student partition(day="2015-08-09");

 

5) 删除分区:

删除分区: hive>alter table student drop partition(day="2015-08-09"); 
删除分区后那么这个分区下的数据没有了

 

6) 设置分区为不可删除方式:

hive> alter table student partition(day="2015-08-09") enable no_drop;
还原分区为可以删除状态
hive> alter table student partition(day="2015-08-09") disable no_drop;

 

7) 禁止查看某分区下数据:
hive> alter table student partition(day="2015-08-09") enable offline;
此时,只能查别的分区的数据, 如果查询表所有数据也是无法查到的。

恢复查看：
hive> alter table student partition(day="2015-08-09") disable offline;

 

8) 禁止全表扫描:  在工作中数据都很大,设置此方式能防止无谓IO和资源消耗

hive>set hive.mapred.mode=strict | nostrict;#严格模式设置  设置为strict下无法进行全表扫描

or

使用  7)禁止查看某分区下的数据也能实现。

 

 

 

 

 

3 桶表简介：

 

a) 概念： 桶表是对数据进行哈希取值，然后放到不同文件中存储，数据加载到桶表时，会对某字段(这个字段会在创建桶表时通过clustered by(xx)指定)取hash值，然后与桶的数量取模。把数据放到对应的文件中，

Hive会启动一个MapReduce的job来产生数据，该job中reduce任务的数量与桶的数量是一致的。每个reduce任务会产生一个文件

<!--EndFragment-->

b) 使用场景：

适用于： 抽样查询  或者表连接查询
不适用于： 根据业务查询数据(因为数据是按照hash来存放，和业务没有任何关系)

c) 和分区表的异同：

相同点： 都是用于对数据的划分
不同点： 前者是根据业务来进行划分，后者是抛弃业务字段从纯数据角度来划分

d) 操作写法：

创建表
	create table buck(id int, name string) clustered by(id) into 4 buckets; 分成4个桶,使用id和4取模，根据结果不同分到不同文件中存储	
加载数据
	set hive.enforce.bucketing = true; // 启用桶 (默认是不用桶的)	
	insert overwrite table bucket_table select name from stu;  //  会对id进行hash计算然后在将数据放在不同桶中， 分区表中加载数据仅仅是将磁盘数据直接加载到hive中

 

桶表数据必须是从hive表中在通过mr计算后到创建好的桶表里来的,上述流程就是桶表加载数据的过程。

创建桶表 tablesample是固定死的。

 

结果：

 

stu数据：
1       zhangsan
2       lisi
3       wangwu
4       zhaoliu
1       zhangsan
2       lisi
3       wangwu
4       zhaoliu
1       zhangsan
2       lisi
3       wangwu
4       zhaoliu

 

   

  

 

注意：
物理上，每个桶就是表(或分区）目录里的一个文件
一个作业产生的桶(输出文件)和reduce任务个数相同


桶表的抽样查询。 貌似 out of y on id的id是固定写死的。
select * from bucket_table tablesample(bucket 1 out of 4 on id);
tablesample是抽样语句
语法解析：TABLESAMPLE(BUCKET x OUT OF y)
y必须是table总bucket数的倍数或者因子。
hive根据y的大小，决定抽样的比例。
例如，table总共分了64份(桶)，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽取。
例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。

 

 

 

其余三个文件内容在此不再展示 ....

 

 

 

 

 

 

4 外部表简介：

 

只需要指定目录即可， 比较灵活，

外部表   

create external table ext_table(c1 string, c2 string) row format delimited fields terminated by '\t' location '/files';

1 使用关键词 external表面外部表。 '/files' 表示关联 hdfs文件系统根目录下files目录内将文件 hello hello1内的数据
2 location用于指定数据在哪里，只能使用文件夹来指定位置，
3 删除外部表 不会损坏hdfs文件内容

 

创建后数据为：

hive> select * from ext_table;
OK
1,zhangsan      NULL
2,lisi  NULL
3,wangwu        NULL
1,45    NULL
2,56    NULL
3,89    NULL

 

删除后 hdfs/files内文件 hello hell1均存在：