Hive MetaStore 元数据库常见操作
hive元数据库中比较重要的一些表的作用,我们可以通过mysql元数据库分析相关字段引用情况,为我们定位和处理问题提供支持。同时可以用来分析现在的数据的存取情况。
元数据主要表及作用
表名 | 作用 |
---|---|
DBS | 存储hive的database信息 |
TBLS | 存储表信息,关联字段DB_ID,SD_ID, |
PARTITIONS | 分区记录,SD_ID, TBL_ID关联 |
COLUMNS_V2 | 存储字段列信息,通过CD_ID与其他表关联 |
CDS | 一个字段CD_ID,与SDS表关联 |
SDS | 存储输入输出format等信息,包括表的format和分区的format。 关联字段CD_ID,SERDE_ID |
PARTITION_KEYS | 存储分区字段,TBL_ID关联 |
VERSION | 版本 |
BUCKETING_COLS | 存储bucket字段信息,通过SD_ID与其他表关联 |
GLOBAL_PRIVS | 全局变量,与表无关 |
PARTITION_KEY_VALS | 分区的值,通过PART_ID关联。 与PARTITION_KEYS共用同一个字段INTEGER_IDX来标示不同的分区字段。 |
PARTITION_PARAMS | 存储某分区相关信息,包括文件数,文件大小,记录条数等。 通过PART_ID关联 |
ROLES | 角色表,和GLOBAL_PRIVS配合,与表无关 |
SEQUENCE_TABLE | 存储sqeuence相关信息,与表无关 |
SERDES | 存储序列化反序列化使用的类 |
SERDE_PARAMS | 序列化反序列化相关信息,通过SERDE_ID关联 |
SORT_COLS | 排序字段,通过SD_ID关联 |
TABLE_PARAMS | 表相关信息,是否外部表,通过TBL_ID关联 |
TBL_PRIVS | 表赋权限相关信息,通过TBL_ID关联 |
VERSION_copy | 版本,通过VER_ID关联 |
1、查询某表的分区
在Spark-sql查询hive表时,会由于元数据中文件与hdfs文件不一致而出现TreeNodeException的异常。比如说,在hive中show partitions时有分区pt=20230101,但是对应HDFS路径下并没有这个子文件夹时,在Spark-sql中就会出现该异常。这时如果需要查询某表的分区,就可以使用如下语句:
SELECT p.* from PARTITIONS p
JOIN TBLS t
ON t.TBL_ID=p.TBL_ID
WHERE t.TBL_NAME='test'
AND PART_NAME like '%pt=20230101%';
2、查询指定库中stored as textfile类型的所有表名
select
d.NAME,
t.TBL_NAME,
s.INPUT_FORMAT,
s.OUTPUT_FORMAT
from TBLS t
join DBS d
join SDS s
where t.DB_ID = d.DB_ID
and t.SD_ID = s.SD_ID
and d.NAME='test'
and s.INPUT_FORMAT like '%TextInputFormat%';
3、查询指定库中的分区表
select
db.NAME,
tb.TBL_NAME,
pk.PKEY_NAME
from TBLS tb
join DBS db
join PARTITION_KEYS pk
where tb.DB_ID = db.DB_ID
and tb.TBL_ID=pk.TBL_ID
and db.NAME='test';
4、查询指定库的非分区表
select
db.NAME,
tb.TBL_NAME
from TBLS tb
join DBS db
where tb.DB_ID = db.DB_ID
and db.NAME='test'
and tb.TBL_ID not in (
select distinct TBL_ID from PARTITION_KEYS
) ;
5、查询指定库中某种存储类型的分区表
select
db.NAME,
tb.TBL_NAME,
pk.PKEY_NAME,
s.INPUT_FORMAT,
s.OUTPUT_FORMAT
from TBLS tb
join DBS db
join PARTITION_KEYS pk
join SDS s
where tb.DB_ID = db.DB_ID
and tb.TBL_ID=pk.TBL_ID
and tb.SD_ID = s.SD_ID
and db.NAME='test'
and s.INPUT_FORMAT like '%TextInputFormat%';
6、查询指定库中某种存储类型的非分区表
select
db.NAME,
tb.TBL_NAME,
s.INPUT_FORMAT,
s.OUTPUT_FORMAT
from TBLS tb
join DBS db
join SDS s
where tb.DB_ID = db.DB_ID
and tb.SD_ID = s.SD_ID
and db.NAME='test'
and s.INPUT_FORMAT like '%TextInputFormat%'
and tb.TBL_ID not in (select distinct TBL_ID from PARTITION_KEYS);
7、查询使用某一个字段的表
SELECT t.table_name,c.column_name FROM information_schema.`TABLES` t
INNER JOIN information_schema.`COLUMNS` c
ON c.TABLE_NAME = t.TABLE_NAME
WHERE
# 查询是否 都有 update_time 字段
c.COLUMN_NAME = 'update_time'
# 查询的数据库
AND t.TABLE_SCHEMA = 'data_exchange'
# 数据库中包含了其他的表, 使用模糊查询
AND t.TABLE_NAME LIKE '%dwd\_\ww\_0000%'