Redshift中通过Shell自动化生成表DDL的最优方案问询

有没有类似SHOW CREATE TABLE的原生命令？ #

Redshift本身没有直接支持SHOW CREATE TABLE table_name或SHOW TABLE table_name这种MySQL/PostgreSQL风格的命令，这确实是个小遗憾。不过我们可以通过系统视图和自定义脚本实现类似效果，甚至更适配自动化场景。

替代方案：优化系统视图的使用 #

你提到的v_generate_tbl_ddl其实是官方推荐的生成DDL的核心视图，但可能你没用到它的过滤和参数化能力，导致不符合需求，这里给你几个优化技巧：

1. 用v_generate_tbl_ddl精准生成单表/多表DDL #

这个视图可以直接生成不带DROP语句的CREATE TABLE（包括主键、外键、分布键、排序键等所有属性），只需要过滤指定表：

SELECT ddl
FROM v_generate_tbl_ddl
WHERE schemaname = 'your_schema' AND tablename = 'your_table';

如果要批量生成某 schema 下所有表的DDL，去掉tablename过滤即可。

2. 处理stl_ddltext的冗余问题 #

stl_ddltext会记录所有执行过的DDL（包括DROP），所以直接用会拿到不需要的语句。你可以通过时间排序取最新的CREATE语句：

SELECT listagg(text, '') WITHIN GROUP (ORDER BY sequence) AS create_ddl
FROM stl_ddltext
WHERE dbname = current_database()
  AND schemaname = 'your_schema'
  AND objectname = 'your_table'
  AND text ILIKE 'CREATE TABLE%'
ORDER BY starttime DESC
LIMIT 1;

这个查询会提取目标表最新的CREATE TABLE语句，自动忽略DROP操作。

自动化Shell脚本实现生产→开发同步 #

要实现自动化，你可以写一个Shell脚本，结合psql（Redshift的命令行客户端）来提取DDL，再同步到开发环境：

示例Shell脚本 #

#!/bin/bash

# 生产环境Redshift配置
PROD_DB="prod_db"
PROD_USER="prod_user"
PROD_HOST="prod-redshift.example.com"
PROD_PORT="5439"
PROD_SCHEMA="target_schema"
TARGET_TABLE="your_table"

# 开发环境Redshift配置
DEV_DB="dev_db"
DEV_USER="dev_user"
DEV_HOST="dev-redshift.example.com"
DEV_PORT="5439"

# 1. 从生产环境提取DDL，保存到临时文件
psql -h $PROD_HOST -p $PROD_PORT -U $PROD_USER -d $PROD_DB -t -c "
SELECT ddl FROM v_generate_tbl_ddl WHERE schemaname = '$PROD_SCHEMA' AND tablename = '$TARGET_TABLE';
" > /tmp/table_ddl.sql

# 2. 清理DDL中的多余空行（可选）
sed -i '/^$/d' /tmp/table_ddl.sql

# 3. 在开发环境执行DDL
psql -h $DEV_HOST -p $DEV_PORT -U $DEV_USER -d $DEV_DB -f /tmp/table_ddl.sql

# 4. 清理临时文件
rm /tmp/table_ddl.sql

echo "DDL同步完成：$PROD_SCHEMA.$TARGET_TABLE 从生产同步到开发"

脚本优化点 #

• 可以扩展为批量处理多个表，比如通过循环读取表名列表
• 添加错误处理（比如检查DDL文件是否生成成功）
• 如果需要先删除开发环境的表，可以在执行CREATE前添加DROP语句（但要谨慎操作）
• 结合crontab实现定时自动同步

额外注意事项 #

• 确保生产环境的用户有访问v_generate_tbl_ddl或stl_ddltext的权限（通常需要SUPERUSER或赋予相关视图的SELECT权限）
• 如果表包含外部表（比如S3外部表），v_generate_tbl_ddl也会生成对应的CREATE EXTERNAL TABLE语句，适配性很强
• 对比v_generate_tbl_ddl和stl_ddltext：前者生成的是当前表结构的DDL（即使表结构被修改过），后者是历史执行过的DDL，所以优先用v_generate_tbl_ddl更准确

内容的提问来源于stack exchange，提问作者Manu Gupta