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 <table_name> 
PARTITION(<column_name> = '<value-1>') 
VALUES ('<value-1>', '<value-2>', ..., '<value-n>');

没有分区字段的表插入语法与普通数据库无异。

-- 无分区
INSERT INTO <table_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 <table_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. 数据查询

通过 desc 关键字查询 Hive 相关表信息。

(1) 表信息

-- 查看建表语句
show create table <database_name>.<table_name>;

-- 查看表统计信息（未格式化）
desc extended <database_name>.<table_name>;

-- 查看表统计信息（格式化）
desc formatted <database_name>.<table_name>;

(2) 分区信息

-- 查看分区结构
show partitions <database_name>.<table_name>;

-- 查看指定分区下统计信息
desc formatted <database_name>.<table_name> 
partition(<p_name_1> = '<p_value_1>', <p_name_2> = '<p_value_2>');

(3) 字段信息

-- 查看字段信息
desc formatted <database_name>.<table_name> <column_name>;

2. 数据分析

通过 analyze 关键字执行分析表，执行分析后将会更新对应表的文件占用与行数等信息。

-- 生成表统计信息(无分区)
analyze table a_db.tb_test compute statistics;

-- 生成表统计信息(有分区需指定)
analyze table a_db.tb_test2 partition(create_time) compute statistics;

-- 生成表统计信息(多分区需全部指定)
analyze table a_db.tb_test3 partition(year, month) compute statistics;

-- 生成表统计信息(指定分区与值)
analyze table a_db.tb_test3 partition(year='2022', month='11') compute statistics;

五、MetaData

MetaData 是 Hive 库外部的元数据存储库，通常选择 MySQL 或 PostgreSQL 作为 Hive 外部元数据库。

表名	描述
DBS	存储 HIVE 数据库基本信息
TBLS	存储 HIVE 表、视图、索引等信息
TABLE_PARAMS	存储表与视图的属性信息。
PARTITIONS	存储表与视图的属性信息。
TBL_PRIVS	存储表与视图的授权信息。

1. DBS

资源表 DBS 存储 Hive 数据库基本信息

select * from DBS
where NAME = '<database_name>';


-- 存储数据库的相关参数
select * from DATABASE_PARAMS;

2. TBLS

资源表 TBLS 存储 Hive 表、视图、索引等信息。

select * from TBLS
where TBL_NAME = '<table_name>';

3. TABLE_PARAMS

资源表 TABLE_PARAMS 存储 Hive 表与视图的属性信息，其中 table_id 由 TBLS 查询。

select * from TABLE_PARAMS
where TBL_ID = <table_id>;

4. PARTITIONS

资源表 PARTITIONS 存储 Hive 表分区信息，其中 table_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 = '<database_name>'
    and t.TBL_NAME = '<table_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 = '<database_name>'
    and t.TBL_NAME = '<table_name>';