Hive分区表与分桶表

目录

分区(Partition)

分桶(Bucket)

问题

---

分区(Partition)

1. 分区主要用于提高性能

分区列的值将表划分为 一个个的文件夹

查询 时语法 使用"分区"列和常规列 类似

查询时Hive 会只从指定分区查询数据，提高查询效率

2.分为静态分区和动态分区

静态分区

创建表
[root@kb131 ~]# vim ./student.txt 

1,小明1,lol-book-movie,beijing:bdqn-nanjing:zhongbo
2,小明2,lol-book-movie,beijing:bdqn-nanjing:zhongbo
3,小明3,lol-book-movie,beijing:bdqn-nanjing:zhongbo
4,小明4,lol-book-movie,beijing:bdqn-nanjing:zhongbo
5,小明5,lol-movie,beijing:bdqn-nanjing:zhongbo
6,小明6,book-movie,beijing:bdqn-nanjing:zhongbo
7,小明7,lol-book,beijing:bdqn-nanjing:zhongbo
8,小明8,lol-book,beijing:bdqn-nanjing:zhongbo
9,小明9,lol-book-movie,beijing:bdqn-nanjing:zhongbo

分区表一
create table student2(
id int,
name string,
likes array<string>,
address map<string, string>
)
partitioned by(age int)
row format delimited fields terminated by ',' 
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n';

导入数据
load data local inpath '/root/student.txt' into table student2 partition(age=20);
load data local inpath '/root/student.txt' into table student2 partition(age=30);

分区表二
create table student3(
id int,
name string,
likes array<string>,
address map<string, string>
)
partitioned by(age int,gender string)
row format delimited fields terminated by ',' 
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n';

load data local inpath '/root/student.txt' into table student3 partition(age=20,gender='boy');
load data local inpath '/root/student.txt' into table student3 partition(age=20,gender='girl');
load data local inpath '/root/student.txt' into table student3 partition(age=30,gender='girl');
load data local inpath '/root/student.txt' into table student3 partition(age=30,gender='boy');

show partitions student3;  查看表中分区信息
show partitions student2;  查看表中分区信息

动态分区

使用动态分区需设定属性

set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nonstrict;

动态分区建表语句和静态分区相同

动态分区插入数据

student2.txt
1,小明1,20,boy,lol-book-movie,beijing:bdqn-nanjing:zhongbo
2,小明2,30,girl,lol-book-movie,beijing:bdqn-nanjing:zhongbo
3,小明3,20,girl,lol-book-movie,beijing:bdqn-nanjing:zhongbo
4,小明4,30,boy,lol-book-movie,beijing:bdqn-nanjing:zhongbo
5,小明5,30,boy,lol-movie,beijing:bdqn-nanjing:zhongbo
6,小明6,19,girl,book-movie,beijing:bdqn-nanjing:zhongbo
7,小明7,19,boy,lol-book,beijing:bdqn-nanjing:zhongbo
8,小明8,20,girl,lol-book,beijing:bdqn-nanjing:zhongbo
9,小明9,19,girl,lol-book-movie,beijing:bdqn-nanjing:zhongbo
~                                                           


create table studentp(
id int,
name string,
age int,
gender string,
likes array<string>,
address map<string,string>
)
row format delimited fields terminated by ',' 
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '
\n';

加载student2.txt数据到studentp
load data local inpath '/root/student2.txt' into table studentp;

注意：一定要开启自动分区 ，否则会报错
set hive.exec.dynamic.partition=true;  开启动态分区
set hive.exec.dynamic.partition.mode=nonstrict; 

创建分区表
create table studentp1(
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by(age int, gender string)      指定分区列
row format delimited fields terminated by ',' 
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n';

将studentp中的数据导入到studentp1分区表中，
注意：分区字段age,gender要放到查询数据的最后
insert into table studentp1 partition(age, gender) 
select id, name, likes, address, age, gender from studentp;

 

分桶(Bucket)

分桶对应于 HDFS 中的文件

更高的查询处理效率

使抽样（sampling）更高效

一般根据"桶列"的哈希函数将数据进行分桶

分桶只有动态分桶

SET hive.enforce.bucketing = true;

定义分桶

CLUSTERED BY (employee_id) INTO 2 BUCKETS

必须使用 INSERT 方式加载数据

create table if not exists employee_id(
name string,
employee_id int,
workplace array<string>,
genderage struct<gender:string,age:int>,
skillsscore map<string,int>,
departtitle map<string,string>
)
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';

load data local inpath '/root/employee_id.txt' overwrite into table employee_id;

create table employee_id_buckets(
name string,
employee_id int,
workplace array<string>,
genderage struct<gender:string,age:int>,
skillsscore map<string,int>,
departtitle map<string,string>
)
clustered by(employee_id) into 2 buckets       //分桶表操作
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';

set map.reduce.tasks=2;
set hive.enforce.bucketing=true;

insert overwrite table employee_id_buckets  select * from employee_id;     数据表中两个分桶数据

select * from employee_id_buckets tablesample(10 percent)s;
select * from employee_id_buckets tablesample(10 rows)s;
select * from employee_id_buckets tablesample(1M)s;
select * from employee_id_buckets tablesample(bucket  out of 30 on employee_id)s;

问题

Hive内部表和外部表概念？区别？最适合的应用场景？

每天采集的ng日志和埋点日志,在存储的时候建议使用外部表，因为日志数据是采集程序实时采集进来的，一旦被误删，恢复起来非常麻烦。而且外部表方便数据的共享。

抽取过来的业务数据，其实用外部表或者内部表问题都不大，就算被误删，恢复起来也是很快的，如果需要对数据内容和元数据进行紧凑的管理, 那还是建议使用内部表

在做统计分析时候用到的中间表，结果表可以使用内部表，因为这些数据不需要共享，使用内部表更为合适。并且很多时候结果分区表我们只需要保留最近3天的数据，用外部表的时候删除分区时无法删除数据。

如何确定一个表是否为临时表？

临时表只对当前session有效，session退出后，表自动删除。

创建的临时表仅仅在当前会话是可见的，数据将会被存储在用户的暂存目录中，并在会话结束时被删除。如果创建临时表的名字与当前数据库下的一个非临时表相同，则在这个会话中使用这个表名字时将会使用的临时表，而不是非临时表，用户在这个会话内将不能使用原表，除非删除或者重命名临时表。

临时表有如下限制：

1.不支持分区字段
2.不支持创建索引

如何知道查询的是表还是视图？

使用descripe命令：

DESCRIBE [FORMATTED] [db_name.]table_name[.complex_col_name ...]

对于视图，它将显示来自视图定义的查询文本。

Hive分区表的作用？静态分区和动态分区的区别？

分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。 

静态分区和动态分区区别

静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断。详细来说，静态分区的列实在编译时期，通过用户传递来决定的；动态分区只有在SQL执行时才能决定。
比如要新建一个增量表（inc）,也不需要回刷数据，只涉及到一天的数据，那么选择静态分区，每天一个分区就好，新建的时候选择pt_dt作为分区键，进行分区设计；

比如要新建一个增量表（inc）,需要回刷数据，例如回刷过去八天数据，那么就需要选择动态分区，还是每天一个分区，新建的时候选择pt_dt作为分区键，进行分区设计；