hive的DDL和DML操作(三)

2023-10-12 19:58:34

一、Hive分区表和分桶表

1.分区表

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

1)创建分区表数据
dept_1.log
10 ACCOUNTING 1700
20 RESEARCH 1800

dept_2.log
30 SALES 1900
40 OPERATIONS 1700

dept_3.log
50 TEST 2000
60 DEV 1900
2)创建分区表

create table dept_par( deptno int, dname string,)
partitioned by (day string)
row format delimited fields terminated by '\t';

partitioned by:指定分区字段为day，类型为string

3)加载数据

在加载数据时不指定分区目录，会在该表下再创建一个默认的分区，将数据存放在该默认分区目录下。如果执行错误，需要将数据文件进行分发，分发给其他节点上。

load data local inpath
'/opt/moudle/hive/data/dept_1.log'
into table dept_par
partition(day='1');

load data local inpath
'/opt/moudle/hive/data/dept_2.log'
into table dept_par
partition(day='2');

load data local inpath
'/opt/moudle/hive/data/dept_3.log'
into table dept_par
partition(day='3');

hive的DDL和DML操作(三)
4）查询单个分区数据

select *from dept_par where day =1;
select *from dept_par where day =2;
select *from dept_par where day =3;

hive的DDL和DML操作(三)
5)多个分区联合查询

#不走MR
 select *from dept_par where day ='1' or day='2';

hive的DDL和DML操作(三)

#union走MR
select * from dept_par where day='1' 
union
select * from dept_par where day='2' 
union
select * from dept_par where day='3';

hive的DDL和DML操作(三)
6)增加分区

alter table dept_par add partition(day='4');

alter table dept_par add partition(day='5') partition(day='6');

hive的DDL和DML操作(三)

7)删除分区

alter table dept_par drop partition (day='6');

alter table dept_par drop partition(day='4'),partition(day='5');

hive的DDL和DML操作(三)

hive的DDL和DML操作(三)
8)查看分区表有多少分区

show partitions dept_par;

hive的DDL和DML操作(三)
9)查看分区表结构

desc formatted dept_par;

hive的DDL和DML操作(三)

2.二级分区

(1)创建表

create table dept_par2(deptno int, dname string, loc string)
partitioned by (day string,hour string)
row format delimited fields terminated by '\t';

(2)加载数据

load data local inpath 
'/opt/moudle/hive/data/dept_1.log'
into table dept_par2
partition(day='20211017',hour='10');

hive的DDL和DML操作(三)

load data local inpath 
'/opt/moudle/hive/data/dept_2.log'
into table dept_par2
partition(day='20211017',hour='11');

hive的DDL和DML操作(三)

load data local inpath 
'/opt/moudle/hive/data/dept_3.log'
into table dept_par2
partition(day='20211017',hour='12');

hive的DDL和DML操作(三)
三条数据都放在dept_par2表的day=20211017分区下
hive的DDL和DML操作(三)
(3)查询二级分区数据

select *from dept_par2 where day='20211017' and hour = '10';

hive的DDL和DML操作(三)
(4)把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

如果直接在表下面创建分区目录，并存放数据，hive端无法获取到数据信息，因为直接创建目录的方式不能在元数据中产生分区信息。因此需要使用以下三种方法

  1. 上传数据后修复 上传数据

创建分区目录，并上传数据
hdfs dfs -mkdir /user/hive/warehouse/test.db/dept_par/day=4
hdfs dfs -put dept_1.log /user/hive/warehouse/test.db/dept_par/day=4

查询数据发现查询不到day=4数据信息

执行修复命令

msck repair table dept_par;

查询信息

select *from dept_par where day ='4';

hive的DDL和DML操作(三)

2)上传数据后添加分区 上传数据

创建分区目录，并上传数据
hdfs dfs -mkdir /user/hive/warehouse/test.db/dept_par/day=5
hdfs dfs -put dept_2.log /user/hive/warehouse/test.db/dept_par/day=5

执行添加分区

alter table dept_par add partition(day='5');

查询数据

select *from dept_par where day='5';

hive的DDL和DML操作(三)

3)创建文件夹后 load 数据到分区 创建目录

创建分区目录
hdfs dfs -mkdir /user/hive/warehouse/test.db/dept_par/day=6

上传数据

load data local inpath
'/opt/moudle/hive/data/dept_3.log' 
into table dept_par
partition(day='6');

查询数据

select *from dept_par where day='6';

hive的DDL和DML操作(三)
(5)动态分区调整

关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据 插入到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过， 使用 Hive 的动态分区，需要进行相应的配置。

1)开启动态分区参数设置

a) 开启动态分区功能（默认 true，开启）

hive.exec.dynamic.partition=true

b) 设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为 静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）

hive.exec.dynamic.partition.mode=nonstrict

c) 在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000

hive.exec.max.dynamic.partitions=1000

d) 在每个执行 MR 的节点上，最大可以创建多少个动态分区。该参数需要根据实际 的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就
需要设置成大于 365，如果使用默认值 100，则会报错。

hive.exec.max.dynamic.partitions.pernode=100

e) 整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000

hive.exec.max.created.files=100000

f）当有空分区生成时，是否抛出异常。一般不需要设置。默认 false

hive.error.on.empty.partition=false
  1. 案例
    需求：将 dept 表中的数据按照地区（loc 字段），插入到目标表 dept_partition 的相应 分区中。

a) 创建目标分区表

create table dept_no_par(dname string, loc string)
partitioned by (deptno int)
row format delimited fields terminated by '\t';

b)设置动态分区

hive3.0环境下，可以不修改参数信息

insert into table dept_no_par
select dname,loc,deptno from dept;

这样运行结果跟下面运行是一样的

直接运行会报错，需要修改参数信息

insert into table dept_no_par partition(deptno)
select dname,loc,deptno from dept;

FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict

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

insert into table dept_no_par partition(deptno)
select dname,loc,deptno from dept;

hive的DDL和DML操作(三)

c)查看目标分区表的分区情况

show partitions dept_no_par;

hive的DDL和DML操作(三)

3.分桶表

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理 的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围 划分。
分桶是将数据集分解成更容易管理的若*分的另一个技术。
分区针对的是数据的存储路径；分桶针对的是数据文件。

(1)创建分桶表

1)数据准备
1001 ss1
1002 ss2
1003 ss3
1004 ss4
1005 ss5
1006 ss6
1007 ss7
1008 ss8
1009 ss9
1010 ss10
1011 ss11
1012 ss12
1013 ss13
1014 ss14
1015 ss15
1016 ss16

2)创建分桶表

create table stu_buck(id int, name string) 
clustered by(id)
into 4 buckets  #分为4个桶
row format delimited fields terminated by '\t';

3)查看表结构

desc formatted stu_buck;

hive的DDL和DML操作(三)
4)导入数据
导入数据前记得将stu.txt文件进行分发，避免报错

load data local inpath '/opt/moudle/hive/data/stu.txt' into table stu_buck;

hive的DDL和DML操作(三)
5）查看分桶表数据

select *from stu_buck;

hive的DDL和DML操作(三)
6）分桶规则
根据结果可知：Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方 式决定该条记录存放在哪个桶当中

(2)分桶表注意事项

1)reduce 的个数设置为-1,让 Job 自行决定需要用多少个reduce 或者将 reduce 的个 数设置为大于等于分桶表的桶数

2)从 hdfs 中 load 数据到分桶表中，避免本地文件找不到问题

3)不要使用本地模式

(3)抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结 果。Hive 可以通过对表进行抽样来满足这个需求。

语法: TABLESAMPLE(BUCKET x OUT OF y)

查询表 stu_buck 中的数据

select * from stu_buck tablesample(bucket 1 out of 4 on id);

注意：x 的值必须小于等于 y 的值

二、函数

1. 系统内置函数

(1)查看系统自带的函数

show functions;

(2)显示自带的函数的用法

desc function upper;

(3)详细显示自带的函数的用法

desc function extended upper;

UDF:一进一出
UDAF：多进多出
UDTF：一进多出
一、多指的是输入数据的行数。

2. 常用内置函数

（1）空字段赋值函数NVL

给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如 果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数 都为 NULL ，则返回 NULL。
1）使用emp表
如果如果员工的 comm 为 NULL，则用-1 代替

select ename,comm,nvl(comm,-1) from emp;

hive的DDL和DML操作(三)
2）如果员工的 comm 为 NULL，则用领导 id 代替

select ename,comm, nvl(comm,mgr) from emp;

hive的DDL和DML操作(三)
(2)CASE WHEN THEN ELSE END
1)准备数据
悟空 A 男
大海 A 男
宋宋 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女
2)创建表并导入数据

create table emp_sex( name string,
dept_id string, sex string)
row format delimited fields terminated by "\t";

load data local inpath '/opt/moudle/hive/data/emp_sex.txt' into table emp_sex;

3)求出不同部门男女各多少人。

select dept_id,
sum(case sex when '男' then 1 else 0 end) man_count,
sum(case sex when '女' then 1 else 0 end) wuman_count
from emp_sex
group by dept_id;

hive的DDL和DML操作(三)

# if函数
select dept_id,
sum(if(sex='男',1,0)) man_count,
sum(if(sex='女',1,0)) wuman_count
from emp_sex
group by dept_id;

hive的DDL和DML操作(三)

(3)行转列（拼接字符串）

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
实际上就是拼接字符串，也可以拼接表中的字段

select concat('a','-','b','-','c');#拼接字符串

hive的DDL和DML操作(三)

select concat(deptno,'-',dname) from dept;#拼接字段

hive的DDL和DML操作(三)

CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。
第一个参数为拼接的字符，后面的参数为需要拼接的字符串或者字符串类型的数组

select concat_WS('-','a','b','c');

hive的DDL和DML操作(三)
注意: CONCAT_WS must be "string or array

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重 汇总，产生 Array 类型字段。
hive的DDL和DML操作(三)

select collect_set(id) from studnet;

hive的DDL和DML操作(三)
COLLECT_LIST(col)：不去重汇总

select collect_set(id,name) from student;

hive的DDL和DML操作(三)

1）数据准备
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A
苍老师 白羊座 B

2)创建表并导入数据

create table person_info
( name string, constellation string, blood_type string)
row format delimited fields terminated by "\t";

load data local inpath '/opt/moudle/hive/data/info.txt' into table person_info;

3）查询数据

SELECT t1.c_b, 
CONCAT_WS("|",collect_set(t1.name))
FROM
(
SELECT
NAME,
CONCAT_WS(',',contellation,blood_type) c_b FROM person_info
) t1
GROUP BY t1.c_b;

(4)列转行

EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。

LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此 基础上可以对拆分后的数据进行聚合。

1)数据准备

《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼 2》 战争,动作,灾难

2)创建表并插入数据

create table movie_info( movie string, category string)
row format delimited fields terminated by "\t";

load data local inpath "/opt/moudle/hive/data/movie.txt" into table movie_info;

3)查询数据

SELECT movie, category_name
FROM
movie_info lateral VIEW
explode(split(category,",")) movie_info_tmp AS category_name;
上一篇：C# l链接Oracle


下一篇：Parencodings