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

newdye 2014-12-17

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的样子:



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

 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的数据。

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


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

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

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均存在:

相关推荐

eastnow / 0评论 2017-07-24