Hive Metastore 查分区大小 批量建表语句

1、查表

select 
a.TBL_NAME as TBL_NAME--,b.LOCATION as LOCATION,d.NAME as NAME
from hive.TBLS a 
leftjoin hive.DBS d
on a.DB_ID=d.DB_ID 
where a.TBL_NAME='exp_mot_datasource_mot_cust_risk_remind_df'--exp_mot_riskinfo_broadcast_remind_no2_dd exp_mot_reits_quot_anml_remind_dd exp_mot_datasource_mot_cust_risk_remind_df dws_mkt_efd_act_event_hs_delivery_dfgroupby a.TBL_NAME,d.NAME

2、查表字段等信息

select-- a.SD_ID as SD_ID,a.TBL_ID as TBL_ID,
a.TBL_NAME as TBL_NAME--,b.LOCATION as LOCATION,c.COLUMN_NAME as COLUMN_NAME,c.TYPE_NAME as TYPE_NAME,c.COMMENTasCOMMENT,d.NAME as NAME,e.PARAM_VALUE as PARAM_VALUE,c.INTEGER_IDX as INTEGER_IDX
,f.PART_NAME as PART_NAME,g.PARAM_VALUE as PARAM_VALUE1
,SUBSTRING_INDEX(f.PART_NAME,'=',1)as PARTITIONED
,concat('create table ',d.NAME,'.',a.TBL_NAME,'(')as text1
,concat(c.COLUMN_NAME,' ',c.TYPE_NAME,' ',(casewhen c.COMMENTisnullthen' 'else concat('comment',' ','''',c.COMMENT,'\'')end))as text2
from metastore.TBLS a 
leftjoin metastore.SDS b 
on a.SD_ID=b.SD_ID
leftjoin metastore.COLUMNS_V2 c 
on b.CD_ID=c.CD_ID 
leftjoin metastore.DBS d
on a.DB_ID=d.DB_ID 
leftjoin metastore.PARTITIONS f 
on a.TBL_ID=f.TBL_ID and f.PART_NAME='etl_date=20241018'leftjoin metastore.TABLE_PARAMS  e 
on a.TBL_ID=e.TBL_ID and e.PARAM_KEY='comment'leftjoin metastore.PARTITION_PARAMS  g 
on f.PART_ID=g.PART_ID and g.PARAM_KEY='numRows'where a.TBL_NAME='exp_mot_datasource_mot_cust_risk_remind_df'--exp_mot_riskinfo_broadcast_remind_no2_dd exp_mot_reits_quot_anml_remind_dd exp_mot_datasource_mot_cust_risk_remind_df dws_mkt_efd_act_event_hs_delivery_dforderby c.INTEGER_IDX asc

3、字段加工

select t.text1 as text1
,GROUP_CONCAT(t.text2 orderby t.INTEGER_IDX asc Separator ',')as names1 
,t.PARAM_VALUE,t.PARTITIONED
,concat(')',(casewhen t.PARAM_VALUE isnullthen' 'else concat(' comment ','''',t.PARAM_VALUE,''' ')end),concat(casewhen t.PARTITIONED isnullthen'kgl'else concat('PARTITIONED BY (etl_date int comment ','\'','数据日期',''')kgl')end))as names2
,concat(') ',casewhen t.PARTITIONED isnullthen'kgl'else concat('PARTITIONED BY (etl_date int comment ','''','数据日期','''',')')end)as names3
from(select-- a.SD_ID as SD_ID,a.TBL_ID as TBL_ID,
a.TBL_NAME as TBL_NAME--,b.LOCATION as LOCATION,c.COLUMN_NAME as COLUMN_NAME,c.TYPE_NAME as TYPE_NAME,c.COMMENTasCOMMENT,d.NAME as NAME,e.PARAM_VALUE as PARAM_VALUE,c.INTEGER_IDX as INTEGER_IDX
,f.PART_NAME as PART_NAME,g.PARAM_VALUE as PARAM_VALUE1
,SUBSTRING_INDEX(f.PART_NAME,'=',1)as PARTITIONED
,concat('create table ',d.NAME,'.',a.TBL_NAME,'(')as text1
,concat(c.COLUMN_NAME,' ',c.TYPE_NAME,' ',(casewhen c.COMMENTisnullthen' 'else concat('comment',' ','''',c.COMMENT,'\'')end))as text2
from metastore.TBLS a 
leftjoin metastore.SDS b 
on a.SD_ID=b.SD_ID
leftjoin metastore.COLUMNS_V2 c 
on b.CD_ID=c.CD_ID 
leftjoin metastore.DBS d
on a.DB_ID=d.DB_ID 
leftjoin metastore.PARTITIONS f 
on a.TBL_ID=f.TBL_ID and f.PART_NAME='etl_date=20241018'leftjoin metastore.TABLE_PARAMS  e 
on a.TBL_ID=e.TBL_ID and e.PARAM_KEY='comment'leftjoin metastore.PARTITION_PARAMS  g 
on f.PART_ID=g.PART_ID and g.PARAM_KEY='numRows'where a.TBL_NAME='exp_mot_datasource_mot_cust_risk_remind_df'--exp_mot_riskinfo_broadcast_remind_no2_dd exp_mot_reits_quot_anml_remind_dd exp_mot_datasource_mot_cust_risk_remind_df dws_mkt_efd_act_event_hs_delivery_dforderby c.INTEGER_IDX asc) t
groupby t.text1,t.PARAM_VALUE,t.PARTITIONED

4、select concat(a.text1,a.names1,a.names2) as create_table from(
select t.text1 as text1
,GROUP_CONCAT(t.text2 order by t.INTEGER_IDX asc Separator ‘,’) as names1
,t.PARAM_VALUE,t.PARTITIONED
,concat(‘)’,(case when t.PARAM_VALUE is null then ’ ’ else concat(’ comment ‘,’‘’‘,t.PARAM_VALUE,’‘’ ‘) end),concat(case when t.PARTITIONED is null then ‘kgl’ else concat(‘PARTITIONED BY (etl_date int comment ‘,’’’,‘数据日期’,‘’‘)kgl’) end)) as names2
,concat(‘) ‘,case when t.PARTITIONED is null then ‘kgl’ else concat(‘PARTITIONED BY (etl_date int comment ‘,’’’’,‘数据日期’,‘’‘’,‘)’) end) as names3
from (
select
– a.SD_ID as SD_ID,a.TBL_ID as TBL_ID,
a.TBL_NAME as TBL_NAME–,b.LOCATION as LOCATION
,c.COLUMN_NAME as COLUMN_NAME,c.TYPE_NAME as TYPE_NAME,c.COMMENT as COMMENT
,d.NAME as NAME,e.PARAM_VALUE as PARAM_VALUE,c.INTEGER_IDX as INTEGER_IDX
,f.PART_NAME as PART_NAME,g.PARAM_VALUE as PARAM_VALUE1
,SUBSTRING_INDEX(f.PART_NAME,‘=’,1) as PARTITIONED
,concat(‘create table ‘,d.NAME,’.’,a.TBL_NAME,‘(’) as text1
,concat(c.COLUMN_NAME,’ ‘,c.TYPE_NAME,’ ‘,(case when c.COMMENT is null then ’ ’ else concat(‘comment’,’ ‘,’‘’‘,c.COMMENT,’‘’) end)) as text2
from metastore.TBLS a
left join metastore.SDS b
on a.SD_ID=b.SD_ID
left join metastore.COLUMNS_V2 c
on b.CD_ID=c.CD_ID
left join metastore.DBS d
on a.DB_ID=d.DB_ID
left join metastore.PARTITIONS f
on a.TBL_ID=f.TBL_ID and f.PART_NAME=‘etl_date=20241018’
left join metastore.TABLE_PARAMS e
on a.TBL_ID=e.TBL_ID and e.PARAM_KEY=‘comment’
left join metastore.PARTITION_PARAMS g
on f.PART_ID=g.PART_ID and g.PARAM_KEY=‘numRows’
where a.TBL_NAME=‘exp_mot_datasource_mot_cust_risk_remind_df’ --exp_mot_riskinfo_broadcast_remind_no2_dd exp_mot_reits_quot_anml_remind_dd exp_mot_datasource_mot_cust_risk_remind_df dws_mkt_efd_act_event_hs_delivery_df
order by c.INTEGER_IDX asc
) t
group by t.text1,t.PARAM_VALUE,t.PARTITIONED
) a

输出结果：
create table exp.exp_mot_datasource_mot_cust_risk_remind_df(cust_num string comment ‘客户号’,remd string comment ‘提醒类型’,busi_type_code string comment ‘业务类型代码’,busi_type_name string comment ‘业务类型名称’,remd_cont string comment ‘提醒内容’,remd_times int comment ‘提醒次数’,tech_etl_datatime string comment ‘执行时间’) comment ‘客户风险测评mot推送表’ kgl