hive 通过元数据导出所有表信息
select
db.NAME as db_name,
tb.TBL_NAME as table_name,
case tb.TBL_TYPE when 'MANAGED_TABLE' then '' else 'external' end as table_type,
sds.LOCATION,
sds.INPUT_FORMAT,
sds.OUTPUT_FORMAT,
t1.TBLPROPERTIES,
ser.SLIB as rowFS,
t2.WithSERD,
t3.colums,
t4.part,
tb.SD_ID,tb.TBL_ID,sds.CD_ID,sds.SERDE_ID
from TBLS tb
join metastore.DBS db on tb.DB_ID=db.DB_ID
join metastore.SDS sds on tb.SD_ID = sds.SD_ID
join (
select tp.TBL_ID, group_concat(concat_ws(':',tp.PARAM_KEY,tp.PARAM_VALUE) separator '@#@') as TBLPROPERTIES
from metastore.TABLE_PARAMS tp
group by tp.TBL_ID
) t1 on tb.TBL_ID=t1.TBL_ID
join metastore.serdes ser on ser.SERDE_ID=sds.SERDE_ID
left join (
select spa.SERDE_ID, group_concat(concat_ws(':',spa.PARAM_KEY,spa.PARAM_VALUE) separator '@#@') as WithSERD
from metastore.SERDE_PARAMS spa
group by spa.SERDE_ID
)t2 on t2.SERDE_ID = sds.SERDE_ID
join (
select cv2.CD_ID, group_concat(concat_ws(':',cv2.INTEGER_IDX,cv2.TYPE_NAME,cv2.COLUMN_NAME) separator '@#@') as colums
from metastore.COLUMNS_V2 cv2
group by cv2.CD_ID
)t3 on t3.CD_ID = sds.CD_ID
left join (
select parts.TBL_ID,group_concat(concat_ws(':',parts.PKEY_NAME,parts.PKEY_TYPE) separator '@#@') as part
from metastore.partition_keys parts
group by parts.TBL_ID
)t4 on tb.TBL_ID=t4.TBL_ID;
