1.Hive分区（很重要）
分区的目的：避免全局扫描，加快查询速度！
思想：分而治之，把大的文件切割划分成一个个小文件，然后操作一个个小文件。
1.2 静态分区（sp)
1.2.1 创建单分区表语法：
CREATE TABLE IF NOT EXISTS dongman (
id int,
name string
) partitioned by(grade int)
row format delimited fields terminated by ',';
-- 分区的字段不要和表的字段相同。相同会报错1,shihao01,1
2,shihao02,1
3,shihao03,1
4,shihao04,1
5,shihao05,1
6,shihao06,2
7,shihao07,2
8,shihao08,2
9,shihao09,3
10,shihao10,3
11,shihao11,3
12,shihao12,4
13,shihao13,4
--载入数据，将相应年纪一次导入
load data local inpath '/usr/local/soft/bigdata30/grade2.txt ' into table dongman partition(grade=2);
1.2.2 静态多分区表语法：
CREATE TABLE IF NOT EXISTS teacher (
tno int,
tname string
) partitioned by(grade int,clazz int)
row format delimited fields terminated by ',';
--注意：前后两个分区的关系为父子关系，也就是grade文件夹下面有多个clazz子文件夹。1,zhangsan01,1,1
2,zhangsan02,1,1
3,zhangsan03,1,2
4,zhangsan04,1,2
5,zhangsan05,1,3
6,zhangsan06,1,3
7,zhangsan07,2,1
8,zhangsan08,2,1
9,zhangsan09,2,2
--载入数据
load data local inpath '/usr/local/soft/bigdata30/teacher1.txt' into table teacher partition(grade=1,clazz=1);1.2.3 分区表查询
select * from students where grade = 1;
// 全表扫描，不推荐，效率低
select count(*) from students;
// 使用where条件进行分区裁剪，避免了全表扫描，效率高
select count(*) from teacher where grade = 1;
// 也可以在where条件中使用非等值判断
select count(*) from teacher where grade<3 and grade>=1;
查看分区
show partitions teacher;
添加分区
alter table dongman add partition (grade=5); alter table teacher add partition (grade=3,clazz=1) location '/user/hive/warehouse/bigdata30.db/teacher/grade=3/clazz=1';
删除分区
alter table t_student drop partition (grade=5);
1.3 动态分区（DP）
静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断。
静态分区的列是在编译时期通过用户传递来决定的,动态分区只有在SQL执行时才能决定。
# 表示开启动态分区
hive> set hive.exec.dynamic.partition=true;
#设置为true表示开启动态分区的功能（默认为false）
--hive.exec.dynamic.partition=true;
# 表示支持的最大的分区数量为1000，可以根据业务自己调整
hive> set hive.exec.max.dynamic.partitions.pernode=1000;
#全局可以创建的最大文件个数（默认值100000）
--hive.exec.max.created.files=100000;
案例： 动态插入学生年级班级信息
--创建外部表
CREATE EXTERNAL TABLE IF NOT EXISTS t_student_w (
sno int,
sname string,
grade int,
clazz int
)
row format delimited fields terminated by ','
location "/bigdata30/teachers";
--创建分区表
CREATE TABLE IF NOT EXISTS t_student_f (
sno int,
sname string
) partitioned by (grade int,clazz int)
row format delimited fields terminated by ',';
数据:
1,lisi01,1,1
2,lisi02,1,1
3,lisi03,1,1
4,lisi04,1,2
5,lisi05,1,2
6,lisi06,2,2
7,lisi07,2,3
8,lisi08,2,3
9,lisi09,3,3
10,lisi10,3,3
11,lisi11,3,4
12,lisi12,4,4
13,lisi13,4,4
14,lisi14,4,5
15,lisi15,4,5
insert overwrite table t_student_f partition (grade,clazz) select * from t_student_w;
优点：不用手动指定了，自动会对数据进行分区
缺点：可能会出现数据倾斜
2、 Hive分桶
2.1 数据分桶原理
Hive采用对列值哈希，然后除以桶的个数取余的方式决定该条记录存放在哪个桶当中。
2.2 数据分桶优势
方便抽样，提高join查询效率
# 分区和分桶的区别 1、在HDFS上的效果区别，分区产生的是一个一个子文件夹，分桶产生的是一个一个文件 2、无论是分区还是分桶，在建表的时候都要指定字段，分区使用partitioned by指定分区字段，分桶使用clustered by指定分桶字段 3、partitioned by指定分区字段的时候，字段后面需要加上类型，而且不能在建表小括号中出现。clustered by指定分桶字段的时候，字段已经出现定义过了，只需要指定字段的名字 4、分区字段最好选择固定类别的，分桶字段最好选择值各不相同的。 5、分桶不是必须要建立在分区之上，可以不进行分区直接分桶
2.3 分桶实例
首先我们需要开启分桶的支持
set hive.enforce.bucketing=true; //依然十分重要，不然无法进行分桶操作！！！！
数据准备（id,name,age）
1,tom,11 2,cat,22 3,dog,33 4,hive,44 5,hbase,55 6,mr,66 7,alice,77 8,scala,88
创建一个普通的表
create table person ( id int, name string, age int ) row format delimited fields terminated by ',';
将数据load到这张表中
load data local inpath '文件在Linux上的绝对路径' into table person;
创建分桶表
create table psn_bucket ( id string, name string, age int, gender String, clazz String ) clustered by(age) into 4 buckets row format delimited fields terminated by ',';
将数据insert到表psn_bucket中
(注意：这里和分区表插入数据有所区别，分区表需要select 和指定分区，而分桶则不需要)
insert into psn_bucket select * from person;
查询数据
linux中使用Hadoop的命令查看
hadoop fs -cat /user/hive/warehouse/bigdata30.db/psn_bucket/*
3、 Hive查询语法(DQL)
3.1 全局排序
-
order by 会对输入做全局排序，因此只有一个reducer，输入规模较大时，需要较长的计算时间
-
使用 order by子句排序 :ASC 升序（默认）| DESC 降序
-
order by放在select语句的结尾
select * from 表名 order by 字段名1[，别名2...];
3.2 局部排序(对reduce内部做排序)
-
sort by 不是全局排序,其在数据进入reducer前完成排序。
-
如果用sort by进行排序，并且设置mapred.reduce.tasks>1,则sort by 只保证每个reducer的输出有序，不保证全局有序。
-
设置reduce个数
set mapreduce.job.reduces=3;
-
查看reduce个数
set mapreduce.job.reduces;
-
排序
select * from 表名 sort by 字段名[,字段名...];
3.3 分区排序(本身没有排序)
distribute by（字段）根据指定的字段将数据分到不同的reducer，分发算法是hash散列。
类似MR中partition,进行分区，结合sort by使用。（注意：distribute by 要在sort by之前）
设置reduce个数 set mapreduce.job.reduce=7;
-
排序
select * from 表名 distribute by 字段名[,字段名...] sort by 字段;
3.4 分区并排序
-
cluster by（字段）除了具有Distribute by的功能外，还会对该字段进行排序 asc desc
-
cluster by = distribute by + sort by 只能默认升序，不能使用倒序
select * from 表名 cluster by 字段名[,字段名...]; select * from 表名 distribute by 字段名[,字段名...] sort by 字段名[,字段名...];
4、Hive内置函数
-- 1.查看系统自带函数 show functions; -- 2.显示自带的函数的用法 desc function xxxx; -- 3.详细显示自带的函数的用法 desc function extended upper;
4.1 内置函数分类
关系操作符：包括 = 、 <> 、 <= 、>=等
算数操作符：包括 + 、 - 、 *、／等
逻辑操作符：包括AND 、 && 、 OR 、 || 等
日期函数：包括from_unixtime(bigint unixtime[, string format])、unix_timestamp()等
条件函数：包括if(boolean testCondition, T valueTrue)等
字符串函数：包括acat(string|binary A, string|binary B…)等
4.2 UDTF Hive中特殊的一个功能
-- UDF 进一出一
-- UDAF 进多出一
-- collect_set()和collect_list()都是对多列转成一行，区别就是list可重复,而set里面是去重的
-- concat_ws(':',collect_set(type)) ':' 表示合并后用什么分隔，collect_set(stage)表示要合并表中的那一列数据
select 字段名,concat_ws(':',collect_set(列名)) as 别名 from 表名 group by id;
-- UDTF 进一出多
-- explode 可以将一组数组的数据变成一列表
select explode(split(列名,"数据的分隔符")) from 表名;
-- lateral view 表生成函数，可以将explode的数据生成一个列表
select id,name,列名 from 表1,lateral view explode(split(表1.列名,"数据的分隔符"))新列名 as 别列名;-- 创建数据库表
create table t_movie1(
id int,
name string,
types string
)
row format delimited fields terminated by ','
lines terminated by '\n';
-- 电影数据 movie1.txt
-- 加载数据到数据库 load data local inpath '/usr/local/soft/bigdata30/movie1.txt' into table t_movie1;
1,这个杀手不太冷静,剧情-动作-犯罪
2,七武海,动作-冒险-剧情
3,打狗棍,动作-传记-剧情-历史-战争
4,琅琊榜,剧情-动作-爱情-武侠-古装
5,霍比特人,动作-奇幻-冒险
-- lateral view 表生成函数，可以将explode的数据生成一个列表
select id,name,type from t_movie1 lateral view explode(split(types,"-")) typetable as type;
-- explode 可以将一组数组的数据变成一列表
select explode(split(types,"-")) from t_movie1;
扫一扫
1241
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
