-- 表和数据库信息
select
d.NAME DB_NAME,d.DB_LOCATION_URI
,t.TBL_ID,t.CREATE_TIME,t.DB_ID,OWNER,t.SD_ID,TBL_TYPE,TBL_NAME
from TBLS t
left join DBS d on t.DB_ID =d.DB_ID
where 1=1
and TBL_NAME like '%表名称%'
order by t.TBL_ID
;
-- 序列化信息
select
t.TBL_ID,t.CREATE_TIME,t.DB_ID,OWNER,t.SD_ID,TBL_TYPE,TBL_NAME
,INPUT_FORMAT,IS_COMPRESSED,OUTPUT_FORMAT,LOCATION
,SLIB
from TBLS t
left join SDS sd on t.SD_ID =sd.SD_ID
left join SERDES se on sd.SERDE_ID =se.SERDE_ID
where 1=1
and TBL_NAME like '%表名称%'
order by t.TBL_ID
;
-- 表参数!!!
select
t.TBL_ID,t.CREATE_TIME,t.DB_ID,OWNER,t.SD_ID,TBL_TYPE,TBL_NAME
,PARAM_KEY,PARAM_VALUE
from TBLS t
left join TABLE_PARAMS pr on t.TBL_ID =pr.TBL_ID
where 1=1
and TBL_NAME like '%表名称%'
order by t.TBL_ID,PARAM_KEY
;
-- Hive 表结构!!!
select * from (
select
t.TBL_ID,t.CREATE_TIME,t.DB_ID,OWNER,t.SD_ID,TBL_TYPE,TBL_NAME
,col.COLUMN_NAME ,col.TYPE_NAME
from TBLS t
inner join SDS sd on sd.SD_ID =t.SD_ID
inner join COLUMNS_V2 col on col.CD_ID =sd.CD_ID
union all
select
t.TBL_ID,t.CREATE_TIME,t.DB_ID,OWNER,t.SD_ID,TBL_TYPE,TBL_NAME
,pk.PKEY_NAME COLUMN_NAME,pk.PKEY_TYPE TYPE_NAME
from TBLS t
inner join PARTITION_KEYS pk on pk.TBL_ID =t.TBL_ID
) tmp
where 1=1
and TBL_NAME like '%表名称%'
order by TBL_ID
;
hive metastore常用SQL
于 2023-07-19 12:00:09 首次发布