sirthisman
59 天前
尝试自己写个 SQL 拼一下：
--拼 Starrocks 建表语句
select
'CREATE TABLE IF NOT EXISTS '||table_schema||'.'||table_name||' (
' tb
,string_agg(column_all,',
' order by ordinal_position)||'
)
' cb
,'COMMENT '||'"'||table_comment||'"'
,分桶键
from(
select
c.column_name||' '||case when c.data_type in('character','character varying','text') then 'string'
when c.data_type in('numeric','real') then 'decimal'||(case when COALESCE(character_maximum_length,numeric_precision) is not null and numeric_scale>0
then '('||COALESCE(character_maximum_length,numeric_precision)||','||numeric_scale ||')'
else '' end)
when c.data_type like 'time%' then 'datetime'
when c.data_type like 'double%' then 'double'
else c.data_type end||' comment '||'"'||c.column_name||'"' column_all
,t.table_schema
,t.table_name
,a.table_comment
,c.ordinal_position
,c.column_name
,c.data_type
,COALESCE ( character_maximum_length, numeric_precision, - 1 ) AS LENGTHTYPE
,numeric_scale
,a.column_comment
,分桶键
from information_schema.tables t
left join information_schema.COLUMNS c on t.table_name = c.table_name and t.table_schema=c.table_schema
left join (select
b.nspname,
a.relname,
cast( obj_description (a.relfilenode, 'pg_class' ) AS VARCHAR ) AS table_comment,
d.attname,
c.description as column_comment
from pg_class a
left join pg_namespace b on a.relnamespace=b.oid
left join pg_description c on c.objoid = a.oid
left join pg_attribute d on c.objoid=d.attrelid and d.attnum = c.objsubid
left join pg_type o on d.atttypid=o.oid
) a on a.nspname=c.table_schema and a.relname = c.table_name and a.attname=c.column_name
left join (select
table_schema
,table_name
,'DISTRIBUTED BY HASH('||case when ordinal_position=1 then column_name end||') PROPERTIES ("replication_num" = "1");' 分桶键
from information_schema.COLUMNS
where ordinal_position=1
) p on p.table_name = c.table_name and p.table_schema=c.table_schema
where t.table_schema in ('tds'/*,'ext','rds','rpt','udt'*/)
order by t.table_schema
,t.table_name
,c.ordinal_position
) d
group by table_schema
,table_name
,table_comment
,分桶键