【TD迁移系列】TD常用字典视图在GaussDB(DWS)上实现

TD有一些常用的字典视图，可能会在迁移TD的过程中脚本使用到，而且客户会更习惯使用TD的字典表，在过渡过程中建一个TD的视图字典表能够让客户更加容易上手GaussDB(DWS).附件有代码，可以直接拿去部署，但需要建一个dbc schema，同时赋予到public的角色。

TD的字典表除了迁移所需以外，平常需要一些信息的时候，GaussDB(DWS)目前版本的字段提供的信息还不够全面。比如字段信息，很多情况下需要拿到表的schema名称，而all_table_columns等字典视图都没有schema信息。此处新增的dbc.columnsv有一般字段的信息汇总，能方便使用人员。

TD主要的字典视图有：

实现方式：

主要使用现有的DWS字典，查看TD字典所需的内容信息怎么样获取，然后再通过多表关联获取全部信息。类似DBC.TABELESV里面有很多是TD自己的信息，而且那些信息在GaussDB(DWS)无法获取，因此在视图中直接不显示。同时增加了DWS常用的字段，例如oid，分布列，分布方式等。

实现代码示例如下：

drop view dbc.tablesv;
create or replace view dbc.tablesv
as 
select pn.nspname as databasename
,case when pc.relkind = 'f' then 'ft'  when pc.relkind = 'r' then 't' else pc.relkind::varchar(10) end as tablekind  --teradata tablekind when is 'f' means 'function'
--,case when (relkind = 'r' and getdistributekey(pc.oid) is not null) or relkind = 'f' then pg_get_tabledef(pc.oid) when relkind = 'v' then pg_get_viewdef(pc.oid) else '' end as RequestText
,pg_get_userbyid(pc.relowner) as CreatorName
,description as CommentString
,po.ctime as CreateTimestamp
,po.mtime as LastAlterTimeStamp
,case when pc.relkind = 'r' then array_length(string_to_array(getdistributekey(pc.oid),','),1) else 0 end as PIColumnCount
,ppt.partcnt as PartitioningLevels
,pn.oid as SchemaOid
,pc.oid as TableOid
,case when pc.relkind = 'r' then getdistributekey(pc.oid) else null end as DistributeKey
,replace(reloptions[1],'orientation=','') orientation
,replace(reloptions[array_upper(reloptions,1)],'compression=','') as compression
from pg_class pc
inner join pg_namespace pn
on pc.relnamespace = pn.oid
and pc.relkind in ('r','f','v')
and pc.oid > 16384
and pn.oid not in (100)
left join pg_description pd
on pc.oid = pd.objoid
and objsubid = 0
left join pg_object po
on pc.oid = po.object_oid
left join (select parentid,count(*) partcnt from pg_partition where parttype = 'p' group by 1) ppt
on pc.oid = ppt.parentid
;

查询结果如下：

dbc.tablesizev，所有表的大小情况信息

CREATE OR REPLACE VIEW dbc.tablesizev
AS
select databasename
,tablename
,(regexp_split_to_array(tbl_dis,'[\,\(\)]+'))[4]::bigint as vprocname
,(regexp_split_to_array(tbl_dis,'[\,\(\)]+'))[5]::bigint as currentperm
from (
select nspname as databasename
,relname as tablename
,table_distribution(nspname,relname)::text as tbl_dis
from pg_class a
inner join pg_namespace b
on a.relnamespace = b.oid
and a.relkind = 'r'
and b.oid not in (100)
)


/**td的倾斜算法**/
select databasename,tablename,sum(currentperm)/1024/1024 cur_mb,100-(avg(currentperm)/nullif(max(currentperm),0))*100 skewness_factor
from (
select databasename
,tablename
,(regexp_split_to_array(tbl_dis,'[\,\(\)]+'))[4]::bigint as vprocname
,(regexp_split_to_array(tbl_dis,'[\,\(\)]+'))[5]::bigint as currentperm
from (
select nspname as databasename
,relname as tablename
,table_distribution(nspname,relname)::text as tbl_dis
from pg_class a
inner join pg_namespace b
on a.relnamespace = b.oid
and a.relkind = 'r'
and b.oid not in (100)
)
) 
where databasename = 'public' group by 1,2

其他代码此处不一一展示，附件在2021-04-13更新过，修改了dbc.columnsv与dbc.tablesv的一些问题。