hive元数据操作
1.查看hive从超过5000分区的表
select dbs.name, tbls.TBL_NAME, count(1) as part_count from dbs, tbls, partitions where dbs.DB_ID = tbls.DB_ID and tbls.TBL_ID = partitions.TBL_ID group by dbs.name, tbls.TBL_NAME having count(1) > 5000 order by part_count desc;
2.查看hive表信息
select dbs.name,tbls.tbl_name,tbls.tbl_type,sds.location,serdes.slib as serde,partition_keys.pkey_name from dbs,sds,serdes,tbls left join partition_keys on tbls.tbl_id=partition_keys.tbl_id where dbs.db_id=tbls.db_id and tbls.sd_id=sds.sd_id and sds.serde_id=serdes.serde_id;
3.查看sds表数量级（此表数据量一般较大，会出现瓶颈，超过100w需注意）
select count(*) from sds;
4.查询某表的分区
SELECT p.* from PARTITIONS p
JOIN TBLS t
ON t.TBL_ID=p.TBL_ID
WHERE t.TBL_NAME='table'
AND PART_NAME like '%pt=xxxxx%';5.查询指定库中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%';6.查询指定库中的分区表
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';7.查询指定库的非分区表
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
) ;8.查看所有库信息
select db_id,name,owner_name from metastore.dbs;
9.查看指定库中所有表信息
select db_id,tbl_id,tbl_name,owner,tbl_type,create_time from metastore.tbls where db_id=51;
10.查看表的参数信息
select * from table_params tp where tbl_id=36;
11.查看指定表的所有字段信息
select cd_id,column_name,type_name,comment from metastore.columns_v2 where CD_ID=50;
12.查询使用某一个字段的表
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%'
