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;