3.Hive系列之数据定义与操作及函数及压缩与存储

1. DDL数据定义

我们来学习下一些DDL操作

# 创建数据库，存放在HDFS中/user/hive/warehouse路径下
create database blog;
# 显示数据库
show databases;
# 显示数据库信息
desc database blog;
# 显示数据库详细信息
desc database extended blog;
# 切换至当前数据库
use blog;
# 为DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息，可通过extended详细信息查看
alter database blog set dbproperties('c_date'='20230617');
# 删除数据库
drop database blog;

# 假设用户已将student数据上传至HDFS中
# 创建表，因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉
create table if not exists student(id int, name string)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student';

# 查询表的类型
hive> desc formatted student;
Table Type: EXTERNAL_TABLE
# 修改表的类型为内部表，内部表在删除时，HDFS数据同步删除
hive> alter table student set tblproperties('EXTERNAL'='FALSE');
hive> desc formatted student;
Table Type: MANAGED_TABLE

# 重命名表
ALTER TABLE student RENAME TO stu;
# 新增列
alter table stu add columns(sex string);
# 更新列名
alter table stu change column sex sexinfo string;
# 删除表
drop table stu;

2. DML数据操作

语句和Mysql、PgSQL都类似，简单过一遍

# 全表查询
select * from studen;
# 列查询
select name, age from student;
# 列别名
select name AS name1, age age1 from student;
# 常用函数 count max min sum avg等
select avg(score) avg_score from student;
# 限制返回的行数
select * from student limit 5;
# 薪水大于 1000 的所有员工
select * from emp where sal>1000;
# 查找名字中带有 A 的员工信息
select * from emp where ename RLIKE '[A]';
# 每个部门的平均薪水大于 2000 的部门
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
# 左外连接：JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回
select e.empno, e.ename, d.deptno from emp e left join
dept d on e.deptno = d.deptno;
# 按照部门和工资升序排序
select ename, deptno, sal from emp order by deptno, sal;

2.1 每个 Reduce 内部排序（Sort By）

Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，此时可以使用 sort by。Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说不是排序

# 设置 reduce 个数
set mapreduce.job.reduces=3;
# 查看设置 reduce 个数
set mapreduce.job.reduces;
# 根据部门编号降序查看员工信息
select * from emp sort by deptno desc;

2.2 分区（Distribute By）

Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition（自定义分区），进行分区，结合 sort by 使用。
对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distributeby 的效果。

# 先按照部门编号分区，再按照员工编号降序排序
select * from emp distribute by deptno sort by empno desc;
➢ distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后，
余数相同的分到一个区。
➢ Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前

2.3 Cluster By

当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC

# 以下两种写法等价
select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;

2.4 分区表与分桶表

2.4.1 分区表

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

# 创建分区表
create table dept_partition(deptno int, dname string, loc string)
partitioned by (day string) row format delimited fields terminated by '\t';
# 加载数据 分区表加载数据时，必须指定分区
load data local inpath '/opt/module/hive/datas/dept_20200403.log' into table dept_partition partition(day='20200403');
# 查询分区表中数据
select * from dept_partition where day='20200401';
# 增加分区
alter table dept_partition add partition(day='20200404');
# 删除分区
alter table dept_partition drop partition(day='20200404');
# 查看分区表有多少分区
show partitions dept_partition;
# 查看分区表结构
desc formatted dept_partition;

# 二级分区 如果一天的日志数据量也很大，将数据再拆分
create table dept_partition2(deptno int, dname string, loc string) partitioned by (day string, hour string) row format delimited fields terminated by '\t';

2.4.2 动态分区调整

关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据
插入到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)

# 开启动态分区功能（默认 true，开启）
hive.exec.dynamic.partition=true
# 设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为
静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）
hive.exec.dynamic.partition.mode=nonstrict
# 在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000
hive.exec.max.dynamic.partitions=1000
# 在每个执行 MR 的节点上，最大可以创建多少个动态分区 该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错
hive.exec.max.dynamic.partitions.pernode=100
# 整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000
hive.exec.max.created.files=100000
# 当有空分区生成时，是否抛出异常。一般不需要设置。默认 false
hive.error.on.empty.partition=false

2.4.3 分桶表

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理
的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围
划分
分桶是将数据集分解成更容易管理的若干部分的另一个技术
分区针对的是数据的存储路径；分桶针对的是数据文件
分桶规则: Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方
式决定该条记录存放在哪个桶当中

# 创建分桶表
create table stu_buck(id int, name string)
clustered by(id) into 4 buckets
row format delimited fields terminated by '\t';
# 查看表结构
hive (default)> desc formatted stu_buck;
Num Buckets: 4
# 导入数据到分桶表中，load 的方式
load data inpath '/student.txt' into table stu_buck;

分桶表操作需要注意的事项：

• reduce 的个数设置为-1,让 Job 自行决定需要用多少个 reduce 或者将 reduce 的个
  数设置为大于等于分桶表的桶数
• 从 hdfs 中 load 数据到分桶表中，避免本地文件找不到问题
• 不要使用本地模式

2.5 抽样查询

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

2.6 导入导出操作

# 加载本地文件到hive
load data local inpath '/opt/hive/datas/student.txt' into table default.student;
# 加载HDFS文件到hive
load data inpath '/user/hive/student.txt' into table default.student;
# 加载数据覆盖表中已有的数据
load data inpath '/user/hive/student.txt' overwrite into table default.student;

# 基本模式插入（根据单张表查询结果） insert 不支持插入部分字段
insert overwrite table student_bak select id, name from student;
# 多表（多分区）插入模式（根据多张表查询结果）
from student insert overwrite table student partition(month='202307') select id, name where month='202307' insert overwrite table student partition(month='202308') select id, name where month='202308';
# 根据查询结果创建表
create table if not exists student2 as select id, name from student;
# 通过 Location 指定加载数据路径
create external table if not exists student3(id int, name string) row format delimited fields terminated by '\t' location '/student';
# Import 数据到指定 Hive 表中, 注意：先用 export 导出后，再将数据导入
import table student4 from '/user/hive/warehouse/export/student';

# 将查询的结果导出到本地
insert overwrite local directory '/opt/module/hive/data/export/student' select * from student;
# 将查询的结果格式化导出到本地
insert overwrite local directory '/opt/module/hive/data/export/student1' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;
# 将查询的结果导出到 HDFS 上(没有 local)
insert overwrite directory '/user/student2' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;
# Hadoop 命令导出到本地
dfs -get /user/hive/warehouse/student/student.txt /opt/module/data/export/student3.txt;
# Hive Shell 命令导出
hive -e 'select * from default.student;' /opt/module/hive/data/export/student4.txt;
# Export 导出到 HDFS 上
export table default.student to '/user/hive/warehouse/export/student';

# Truncate 只能删除管理表，不能删除外部表中数据
truncate table student;

3. 函数

# 查看系统自带的函数
show functions;
# 显示自带的函数的用法
desc function upper;
# 详细显示自带的函数的用法
desc function extended upper;
# 如果员工的 comm 为 NULL，则用-1 代替
select comm,nvl(comm, -1) from emp;
# CASE WHEN THEN ELSE END
select dept_id,
 sum(case sex when '男' then 1 else 0 end) male_count,
 sum(case sex when '女' then 1 else 0 end) female_count
from emp_sex group by dept_id;
# 行转列 CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串
SELECT t1.c_b, CONCAT_WS("|",collect_set(t1.name))
FROM (SELECT NAME, CONCAT_WS(',',constellation,blood_type) c_b FROM person_info) t1 GROUP BY t1.c_b
# 列转行 EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
# LATERAL VIEW用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias 解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合
SELECT movie, category_name FROM
movie_info
lateral VIEW explode(split(category,",")) movie_info_tmp AS category_name;

自定义函数

• Hive 自带了一些函数，比如：max/min 等，但是数量有限，自己可以通过自定义 UDF 来方便的扩展。
• 当 Hive 提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
• 根据用户自定义函数类别分为以下三种：
  • UDF（User-Defined-Function）
    一进一出
  • UDAF（User-Defined Aggregation Function）
    聚集函数，多进一出，类似于：count/max/min
  • UDTF（User-Defined Table-Generating Functions）
    一进多出, 如 lateral view explode()
• 实现方式略，自行使用时查找吧

4. 压缩与存储

4.1 创建一个 ZLIB 压缩的 ORC 存储方式

create table log_orc_zlib(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as orc
tblproperties("orc.compress"="ZLIB");

# 查看插入后数据
dfs -du -h /user/hive/warehouse/log_orc_zlib/ ;
2.78 M /user/hive/warehouse/log_orc_none/000000_0

4.2 创建一个 SNAPPY 压缩的 ORC 存储方式

create table log_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as orc
tblproperties("orc.compress"="SNAPPY");

# 查看插入后数据
dfs -du -h /user/hive/warehouse/log_orc_snappy/;
3.75 M /user/hive/warehouse/log_orc_snappy/000000_0

4.3 创建一个 SNAPPY 压缩的 parquet 存储方式

create table log_parquet_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as parquet
tblproperties("parquet.compression"="SNAPPY");

dfs -du -h /user/hive/warehouse/log_parquet_snappy/;
6.39 MB /user/hive/warehouse/ log_parquet_snappy /000000_0

4.4 存储方式和压缩总结

在实际的项目开发当中，hive 表的数据存储格式一般选择：orc 或 parquet。压缩方式一般选择 snappy，lzo