Hive-day3

Hive分区

在大数据中，最常见的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这样每次操作一个个小的文件就会很容易了，同样的道理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照每天或者每小时切分成一个个小的文件，这样去操作小的文件就会容易很多了。

假如现在我们公司一天产生3亿的数据量，那么为了方便管理和查询，就做以下的事情。

1）建立分区（可按照日期，部门等等具体业务分区）

2）分门别类的管理

为什么要分区（面试）？

避免全局扫描，加快查询速率

1.静态分区（SP）

静态分区（SP）static partition–partition by (字段类型)

借助于物理的文件夹分区，实现快速检索的目的。
一般对于查询比较频繁的列设置为分区列。
分区查询的时候直接把对应分区中所有数据放到对应的文件夹中。
查询时哪一个字段查询频率最高就把它作为分区字段

创建单分区表语法：

CREATE TABLE IF NOT EXISTS t_student (
sno int,
sname string
) partitioned by(grade int)
row format delimited fields terminated by ',';
--  分区的字段不要和表的字段相同。相同会报错error10035


1,xiaoyu01,1
2,xiaoyu02,1
3,xiaoyu03,1
4,xiaoyu04,1
5,xiaoyu05,1

6,xiaoyu06,2
7,xiaoyu07,2
8,xiaoyu08,2

9,xiaoyu09,3
10,xiaoyu10,3
11,xiaoyu11,3
12,xiaoyu12,3
13,xiaoyu13,3
14,xiaoyu14,3

15,xiaoyu15,3
16,xiaoyu16,4
17,xiaoyu17,4
18,xiaoyu18,4
19,xiaoyu19,4
20,xiaoyu20,4
21,xiaoyu21,4
-- 载入数据
-- 将相应年级一次导入
load data local inpath '/usr/local/soft/bigdata/j1.txt' into table t_student partition(grade=1);_student partition(grade=1);

-- 演示多拷贝一行上传，分区的列的值是分区的值，不是原来的值
在同一个文件中，后面数值是3但在4的分区里不会报错

静态多分区表语法：

CREATE TABLE IF NOT EXISTS t_teacher (
tno int,
tname string
) partitioned by(grade int,clazz int)
row format delimited fields terminated by ',';

--注意：前后两个分区的关系为父子关系，也就是grade文件夹下面有多个clazz子文件夹。
1,xiaoge01,1,1
2,xiaoge02,1,1

3,xiaoge03,1,2
4,xiaoge04,1,2

5,xiaoge05,1,3
6,xiaoge06,1,3

7,xiaoge07,2,1
8,xiaoge08,2,1

9,xiaoge09,2,2

--载入数据
load data local inpath '/usr/local/soft/bigdata/hivedata/teacher_1.txt' into table t_teacher partition(grade=1,clazz=1);

分区表查询

select * from t_student where grade = 1;

// 全表扫描，不推荐，效率低
select count(*) from students_pt1;

// 使用where条件进行分区裁剪，避免了全表扫描，效率高
select count(*) from students_pt1 where grade = 1;

// 也可以在where条件中使用非等值判断
select count(*) from students_pt1 where grade<3 1 and grade>=1;

查看分区

show partitions t_student;

添加分区

alter table t_student add partition (grade=5);

alter table t_student add partition (grade=5) location '指定数据文件的路径';从

删除分区

alter table t_student drop partition (grade=5);

2.动态分区（DP）

动态分区（DP）dynamic partition
静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断。
详细来说，静态分区的列是在编译时期通过用户传递来决定的；动态分区只有在SQL执行时才能决定。

开启动态分区首先要在hive会话中设置如下的参数

# 表示开启动态分区
hive> set hive.exec.dynamic.partition=true;

# 表示动态分区模式：strict（需要配合静态分区一起使用）、nostrict
# strict： insert into table students_pt partition(dt='anhui',pt) select ......,pt from students;
hive> set hive.exec.dynamic.partition.mode=nonstrict;

===================以下是可选参数======================

# 表示支持的最大的分区数量为1000，可以根据业务自己调整
hive> set hive.exec.max.dynamic.partitions.pernode=1000;

其余的参数详细配置如下

设置为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：动态插入学生年级班级信息

--创建分区表
CREATE TABLE IF NOT EXISTS t_student_d (
sno int,
sname string
) partitioned by (grade int,clazz int)
row format delimited fields terminated by ',';

--创建外部表
CREATE EXTERNAL TABLE IF NOT EXISTS t_student_e (
sno int,
sname string,
grade int,
clazz int
) 
row format delimited fields terminated by ','
location "/shujia/bigdata19/input/teachers";

数据：

1,xiaoyu01,1,1
2,xiaoyu02,1,1
3,xiaoyu03,1,1
4,xiaoyu04,1,2
5,xiaoyu05,1,2
6,xiaoyu06,2,3
7,xiaoyu07,2,3
8,xiaoyu08,2,3
9,xiaoyu09,3,3
10,xiaoyu10,3,3
11,xiaoyu11,3,3
12,xiaoyu12,3,4
13,xiaoyu13,3,4
14,xiaoyu14,3,4
15,xiaoyu15,3,4
16,xiaoyu16,4,4
17,xiaoyu17,4,4
18,xiaoyu18,4,5
19,xiaoyu19,4,5
20,xiaoyu20,4,5
21,xiaoyu21,4,5

如果静态分区，插入数据必须指定分区的值；如果插入多次就会很麻烦，而且静态分区有可能会产生数据错误问题

-- 会报错 
insert overwrite table t_student_d partition (grade=1) select * from t_student_e where grade=1;

如果使用动态分区，动态分区会根据select的结果自动判断数据应该 load到哪个分区

insert overwrite table t_student_d partition (grade,clazz) select * from t_student_e;

优点：不用手动指定，自动会对数据进行分区

缺点：可能会出现数据倾斜

静态分区和动态分区的区别：

静态分区：先创建分区表，手动指定分区的值，将对应的数据加载到分区目录下

动态分区：先有一个包含了所有数值的原始表，然后创建分区表，将原始表的数据载入到分区表中，会自动根据数据列的值创建对应的分区

Hive分桶

1.业务场景

数据分桶的适用场景：

分区提供了一个隔离数据和优化查询的便利方式，不过并非所有的数据都可形成合理的分区，尤其是需要确定合适大小的分区划分方式
不合理的数据分区划分方式可能导致有的分区数据过多，而某些分区没有什么数据的尴尬情况
分桶是将数据集分解为更容易管理的若干部分的另一种技术。
分桶就是将数据按照字段进行划分，可以将数据按照字段划分到多个文件当中去。

2.数据分桶原理

Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

bucket num = hash_function(bucketing_column) mod num_buckets
列的值做哈希取余决定数据应该存储到哪个桶

3.数据分桶优势

方便抽样

使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便

提高join查询效率

获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

4.分桶实战

首先，分区和分桶是两个不同的概念，很多资料上说需要先分区在分桶，其实不然，分区是对数据进行划分，而分桶是对文件进行划分。
当我们的分区之后，最后的文件还是很大怎么办，就引入了分桶的概念。
将这个比较大的文件再分成若干个小文件进行存储，我们再去查询的时候，在这个小范围的文件中查询就会快很多。
对于hive中的每一张表、分区都可以进一步的进行分桶。
当然，分桶不是说将文件随机进行切分存储，而是有规律的进行存储。在看完下面的例子后进行解释，现在干巴巴的解释也不太好理解。它是由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中。
创建顺序和分区一样，创建的方式不一样。

首先我们需要开启分桶的支持

（依然十分重要，不然无法进行分桶操作！！！！）
set hive.enforce.bucketing=true;

数据准备

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 psn31
(
id int,
name string,
age int
)
row format delimited
fields terminated by ',';

将数据load到这张表中

load data local inpath '文件在Linux上的绝对路径' into table psn31;

创建分桶表

create table psn_bucket
(
id int,
name string,
age int
)
clustered by(age) into 4 buckets
row format delimited
fields terminated by ',';

将数据insert到表psn_bucket中

(注意：这里和分区表插入数据有所区别，分区表需要select 和指定分区，而分桶则不需要)

insert into psn_bucket select id,name,age from psn31;

在HDFS上查看数据

查询数据

我们在linux中使用Hadoop的命令查看一下（与我们猜想的顺序一致）

hadoop fs -cat /user/hive/warehouse/bigdata.db/psn_bucket/*

这里设置的桶的个数是4 数据按照年龄%4 进行放桶(文件)

11%4 == 3 -----> 000003_0

22%4 == 2 -----> 000002_0

33%4 == 1 -----> 000001_0

44%4 == 0 -----> 000000_0

...以此类推

在hive中进行查询

-- tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y)
-- 分桶语句中的分母表示的是数据将会被散列的桶的个数，分子表示将会选择的桶的个数。

-- x表示从哪个bucket开始抽取。
-- 例如，table总bucket数为32，tablesample(bucket 2 out of 2)
-- 表示总共抽取（2/2=）1个bucket的数据，分别为第2个bucket和第（2+2=）4个bucket的数据
-- y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。
-- 例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据

select * from psn_bucket tablesample(bucket 3 out of 2);
随机取值（设置因子，桶的个数/因子）
这里就是取2号桶和4号桶，取2个

select * from psn_bucket tablesample(bucket 2 out of 4);
随机取值（设置因子，桶的个数/因子）
这里就是取2号桶，取一个

select * from psn_bucket tablesample(bucket 2 out of 8);
随机取值（设置倍数，倍数/桶的个数）
这里就是取2号桶 1/2个数据
取出来是一条数据

Hive JDBC

启动hiveserver2

hive --service hiveserver2 &
或者
hiveserver2 &

新建maven项目并添加两个依赖

    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-common</artifactId>
        <version>2.7.6</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-jdbc</artifactId>
        <version>1.2.1</version>
    </dependency>

编写JDBC代码

import java.sql.*;

public class HiveJDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.apache.hive.jdbc.HiveDriver");
        Connection conn = DriverManager.getConnection("jdbc:hive2://master:10000/bigdata17");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("select * from students limit 10");
        while (rs.next()) {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            int age = rs.getInt(3);
            String gender = rs.getString(4);
            String clazz = rs.getString(5);
            System.out.println(id + "," + name + "," + age + "," + gender + "," + clazz);
        }
        rs.close();
        stat.close();
        conn.close();
    }
}

Hive查询语法（DQL）

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT [offset,] rows]

1.全局排序

order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间
使用 order by子句排序 :ASC（ascend）升序（默认）| DESC（descend）降序
order by放在select语句的结尾

select * from 表名 order by 字段名1[，别名2...];

2.局部排序

sort by 不是全局排序,其在数据进入reducer前完成排序。
如果用sort by进行排序，并且设置mapred.reduce.tasks>1,则sort by 只保证每个reducer的输出有序，不保证全局有序。asc,desc
设置reduce个数

set mapreduce.job.reduce=3;
set mapred.reduce.tasks=3;

查看reduce个数

set mapreduce.job.reduce;

排序

select * from 表名 distribute by 字段名[,字段名...];

3.分区排序

distribute by（字段）根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
类似MR中partition,进行分区，结合sort by使用。（注意：distribute by 要在sort by之前）
对于distrbute by 进行测试，一定要多分配reduce进行处理，否则无法看到distribute by的效果。

设置reduce个数

set mapreduce.job.reduce=7;

排序

select * from 表名 distribute by 字段名[,字段名...];

4.分区并排序

cluster by（字段）除了具有Distribute by的功能外，还会对该字段进行排序
cluster by = distribute by + sort by 只能默认升序，不能使用倒序

select * from 表名 sort cluster by 字段名[,字段名...];
select * from 表名 distribute by 字段名[,字段名...] sort by 字段名[,字段名...];

hive几种分区的区别

distribute by name 后面分区是13和24；

必须经过sort by

5.Hive内置函数

官网提供函数的网址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

-- 1.查看系统自带函数
show functions;
-- 2.显示自带的函数的用法
desc function upper;
-- 3.详细显示自带的函数的用法
desc function extended upper;

1.内置函数分类

关系操作符：包括 = 、 <> 、 <= 、>=等

算数操作符：包括 + 、 - 、 *、／等

逻辑操作符：包括AND 、 && 、 OR 、 || 等

复杂类型构造函数：包括map、struct、create_union等

复杂类型操作符：包括A[n]、Map[key]、S.x

数学操作符：包括ln(double a)、sqrt(double a)等

集合操作符：包括size(Array)、sort_array(Array)等

类型转换函数： binary(string|binary)、cast(expr as )

日期函数：包括from_unixtime(bigint unixtime[, string format])、unix_timestamp()等

条件函数：包括if(boolean testCondition, T valueTrue, T valueFalseOrNull)等

字符串函数：包括acat(string|binary A, string|binary B…)等

其他：xpath、get_json_objectscii(string str)、con

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 inpath '/shujia/movie1.txt' into table t_movie1;
1,这个杀手不太冷,剧情-动作-犯罪
2,七武士,动作-冒险-剧情
3,勇敢的心,动作-传记-剧情-历史-战争
4,东邪西毒,剧情-动作-爱情-武侠-古装
5,霍比特人,动作-奇幻-冒险

-- explode  可以将一组数组的数据变成一列表
select  explode(split(types,"-")) from t_movie1;

-- lateral view 表生成函数，可以将explode的数据生成一个列表
select id,name,type from t_movie1 lateral view explode(split(types,"-")) typetable as type;

-- 创建数据库表
create table t_movie2(
id int,
name string,
type string
)
row format delimited fields terminated by ','
lines terminated by '\n';

-- 电影数据 movie2.txt
-- 加载数据到数据库 load data inpath '/shujia/movie2.txt' into table t_movie2;
1,这个杀手不太冷,剧情
1,这个杀手不太冷,动作
1,这个杀手不太冷,犯罪
2,七武士,动作
2,七武士,冒险
2,七武士,剧情
3,勇敢的心,动作
3,勇敢的心,传记
3,勇敢的心,剧情
3,勇敢的心,历史
3,勇敢的心,战争
4,东邪西毒,剧情
4,东邪西毒,动作
4,东邪西毒,爱情
4,东邪西毒,武侠
4,东邪西毒,古装
5,霍比特人,动作
5,霍比特人,奇幻
5,霍比特人,冒险

-- collect_set()和collect_list()都是对列转成行，区别就是list里面可重复而set里面是去重的
-- concat_ws(':',collect_set(type))   ':' 表示你合并后用什么分隔，collect_set(stage)表示要合并表中的那一列数据
select id,concat_ws(':',collect_set(type)) as types from t_movie2 group by id;

3.WordCount案例

数据准备

hello,world
hello,bigdata
like,life
bigdata,good

建表

create table wc
(
line string
)
row format delimited fields terminated by ','

导入数据

load data local inpath '/usr/local/soft/bigdata/wc1.txt' into table wc;

步骤1：先对一行数据进行切分

select split(line,',') from wc;

步骤2：将行转列

select explode(split(line,',')) from wc;

步骤3：将相同的进行分组统计

select w.word,count(*) from (select explode(split(line,',')) as word from wc) w group by w.word;

posted on 2022-09-06 19:54 不想写代码的小玉阅读(24) 评论(0) 编辑收藏举报

刷新页面返回顶部

wqy1027

Hive-day3

Hive分区

为什么要分区（面试）？

1.静态分区（SP）

查询时哪一个字段查询频率最高就把它作为分区字段

2.动态分区（DP）

案例1：动态插入学生年级班级信息

Hive分桶

1.业务场景

2.数据分桶原理

3.数据分桶优势

4.分桶实战

首先我们需要开启分桶的支持

数据准备

创建一个普通的表

Hive JDBC

启动hiveserver2

新建maven项目并添加两个依赖

编写JDBC代码

Hive查询语法（DQL）

1.全局排序

2.局部排序

3.分区排序

4.分区并排序

5.Hive内置函数

1.内置函数分类

2.UDTF hive中特殊的一个功能（进一出多

3.WordCount案例

导航

公告