有什么不通过写脚本的方式，将 postgreSQL 的建表语句转换为 doris 的建表语句吗？

[sirthisman]

求助，我们要做从 hawq 引擎（建表语句是 postgreSQL 格式）到 Starrocks （建表语句是 doris 格式）数据库的数据迁移。大概数百张表。需要在 Starrocks 中同步对应表结构。请教下各位有什么工具可以批量转换建表语句么？

[smallpigzbl]

chatgpt ，我常用的是丢一段表结构给它，转成 yml 格式给我。。。

[amoia50]

antlr 语法解析，可以外包支持

[hemingwang0902]

@smallpigzbl 正解

[dRider]

语法树转换，我之前做数据平台的是 hive\mysql\doris\Clickhouse 的可视化转换和自动建表，可以参考我之前做的： https://github.com/DLuPan/DorisParser

[zouzou0208]

来：
https://github.com/tobymao/sqlglot

[qsbaq]

直接丢给 ai

[weiiai]

Starrocks 官方有工具，可以去论坛看看

[sirthisman]

尝试自己写个 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
,分桶键

[sirthisman]

https://docs.starrocks.io/zh/docs/integrations/loading_tools/SMT/