Hive 是一个基于 Hadoop 的数据仓库工具，用于在 HDFS 上进行大规模数据存储和管理。它提供了一种类 SQL 的查询语言 HiveQL(Hive Query Language)，允许用户在无需深度掌握 MapReduce` 编程的情况下进行数据分析和查询。
Hive 的底层存储机制即数据文件，拥有 TextFile、ORC(Optimized Row Columnar) 与 Parquet 等多种方式。在 Hive 中虽然提供类似关系型数据库的查询语法，但实际性能相对较低，实际应用中更多的是对于文件流的操作进而实现数据管理。
下面就让我们来了解一下 Hive 的一些相关的概念及知识。
一、DDL操作
1. 新建库
通过 create database 新建 Hive 数据库。
-- 创建数据库
create database <db_name>
2. 新建表
Hive 新建表与常见数据库基本一致，建表配置项参考下表：
| 方法 | 作用 |
|---|---|
| PARTITIONED BY | 用于指定分区列。 |
| ROW FORMAT DELIMITED FIELDS TERMINATED BY | 设置数据文件存储时字段值分割符。 |
| STORED AS | 设置表的类型，可选值：orc, lzo, avro, textfile。 |
-- 新建表
create table <db_name>.<tb_name>(
`<column_name>` <type> COMMENT '<column comment>'
)
-- 表备注
COMMENT 'table comment'
-- 分区字段
PARTITIONED BY (`<column_name>` <type>)
-- 自定义行记录分隔符
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
-- 表类型
STORED AS <type>
二、DML操作
1. 新增数据
若表存在分区在插入时需要指定分区值。
-- 带分区
INSERT INTO <tb_name>
PARTITION(<column_name> = '<value-1>')
VALUES ('<value-1>', '<value-2>', ..., '<value-n>');
没有分区字段的表插入语法与普通数据库无异。
-- 无分区
INSERT INTO <tb_name>
VALUES ('<value-1>', '<value-2>', ..., '<value-n>');
2. 文件插入
Hive 原生支持从文件读取数据插入，如准备如下 txt 文件，其中一行数据即为一条记录，不同值之间的分隔符与建表时指定时的一致即可。
123, alex, 20, china
读取插入语句如下：
-- 默认是 append , 使用 overwrite 则进行覆盖
load data local inpath '/path/test.txt'
overwrite into table <tb_name> ;
三、基本函数
1. 时间函数
(1) from_unixtime
通过 from_unixtime 函数可将时间戳转化为字符串。
SELECT
from_unixtime(unix_timestamp(time1), 'yyyy-MM-dd HH:00:00') AS time2,
COUNT(*) AS num
FROM
tb_user
WHERE
time1 > unix_timestamp('2022-06-01', 'yyyy-MM-dd')
AND
time1 < unix_timestamp('2022-06-30', 'yyyy-MM-dd')
GROUP BY
from_unixtime(unix_timestamp(time1), 'yyyy-MM-dd HH:00:00')
ORDER BY
time2;
(2) unix_timestamp
通过 unix_timestamp 函数可将字符串转化为时间戳。
SELECT
from_unixtime(unix_timestamp(time1), 'yyyy-MM-dd HH:00:00') AS time2,
COUNT(*) AS num
FROM
tb_user
WHERE
time1 > unix_timestamp('2022-06-16 16:00:00', 'yyyy-MM-dd HH:00:00')
AND
time1 < unix_timestamp('2022-06-16 16:00:00', 'yyyy-MM-dd HH:00:00')+ INTERVAL 1 hour;
四、MetaStore
MetaStore 是 Hive 库内部维护的元数据库，通过其即可直接访问 Hive 中表的元数据信息，在最新的 Hive 3 中支持单独部署。
1. 数据查询
(1) 表信息
通过 desc 关键字查询对应数据表的相关信息。
-- 查看建表语句
show create table <db_name>.<tb_name>;
-- 查看表统计信息（未格式化）
desc extended <db_name>.<tb_name>;
-- 查看表统计信息（格式化）
desc formatted <db_name>.<tb_name>;
(2) 分区信息
可通过 partition 关键字指定查询表的特定分区信息。
-- 查看分区结构
show partitions <db_name>.<tb_name>;
-- 查看指定分区下统计信息
desc formatted <db_name>.<tb_name>
partition(<p_name_1> = '<p_value_1>', <p_name_2> = '<p_value_2>');
(3) 字段信息
同理，在 desc 查询表信息时可指定只查看特定列。
-- 查看字段信息
desc formatted <db_name>.<tb_name> <column_name>;
2. 数据分析
通过 analyze 关键字执行分析表，执行分析后将会更新对应表的文件占用与行数等信息。
-- 生成表统计信息(无分区)
analyze table <db_name>.<tb_name> compute statistics;
-- 生成表统计信息(有分区需指定)
analyze table <db_name>.<tb_name> partition(<partition_name>) compute statistics;
-- 生成表统计信息(多分区需全部指定)
analyze table <db_name>.<tb_name> partition(<partition_1>, <partition_2>) compute statistics;
-- 生成表统计信息(指定分区与值)
analyze table <db_name>.<tb_name> partition(<p_1> = <v_1>) compute statistics;
五、MetaData
MetaData 是 Hive 库外部的元数据存储库，通常选择 MySQL 或 PostgreSQL 作为 Hive 外部元数据库。
| 表名 | 描述 |
|---|---|
| DBS | 存储 HIVE 数据库基本信息。 |
| DATABASE_PARAMS | 存储数据库的相关参数。 |
| TBLS | 存储 HIVE 表、视图、索引等信息。 |
| TABLE_PARAMS | 存储表与视图的属性信息。 |
| PARTITIONS | 存储表的分区属性信息。 |
| TBL_PRIVS | 存储表与视图的授权信息。 |
1. DBS
资源表 DBS 存储 Hive 数据库基本信息
select
*
from
DBS
where
NAME = '<db_name>';
2. TBLS
资源表 TBLS 存储 Hive 表、视图与索引等信息。
select
*
from
TBLS
where
TBL_NAME = '<tb_name>';
3. TABLE_PARAMS
资源表 TABLE_PARAMS 存储 Hive 表与视图的属性信息，其中 TBL_ID 由 TBLS 查询。
select
*
from
TABLE_PARAMS
where
TBL_ID = <table_id>;
4. PARTITIONS
资源表 PARTITIONS 存储 Hive 表分区信息，其中 TBL_ID 由 TBLS 查询。
-- 存储分区信息
select
*
from
PARTITIONS
where
TBL_ID = <table_id>;
5. TBL_PRIVS
资源表 TBL_PRIVS 存储 Hive 表与视图的授权信息。
select
*
from
TBL_PRIVS;
6. 组合查询
查看指定带分区的 Hive 表基本元数据信息。
select
d.NAME, t.TBL_NAME, p.PART_NAME, v.PARAM_KEY, v.PARAM_VALUE
from
TBLS t
left join DBS d
on
t.DB_ID = d.DB_ID
left join PARTITIONS p
on
t.TBL_ID = p.TBL_ID
left join PARTITION_PARAMS v
on
p.PART_ID = v.PART_ID
where
d.OWNER_NAME = 'hive'
and d.NAME = '<db_name>'
and t.TBL_NAME = '<tb_name>';
查看指定无分区的 Hive 表基本元数据信息。
select
d.NAME,
t.TBL_NAME,
SUM(case when p.param_key = 'numFiles' then p.param_value else 0 end) as file_num,
SUM(case when p.param_key = 'numRows' then p.param_value else 0 end) as row_num,
SUM(case when p.param_key = 'totalSize' then p.param_value else 0 end) as total_size
from
TABLE_PARAMS p
left join TBLS t
on
p.TBL_ID = t.TBL_ID
left join DBS d
on
t.DB_ID = d.DB_ID
where
d.OWNER_NAME = 'hive'
and d.NAME = '<db_name>'
and t.TBL_NAME = '<tb_name>';
