如何生成S3中表的DDL并加载到Redshift，及Redshift表带DDL备份恢复？

一、同时备份表数据（Parquet优先）与DDL到S3 #

1. 导出表数据到S3（Parquet格式） #

使用Redshift原生UNLOAD命令导出Parquet格式数据，这是官方推荐的高效方式：

UNLOAD ('SELECT * FROM your_schema.your_table')
TO 's3://your-bucket/path/to/backup/your_table/'
IAM_ROLE 'arn:aws:iam::123456789012:role/your-redshift-s3-role'
FORMAT PARQUET
PARTITION BY (your_partition_col) -- 可选，按分区导出提升后续恢复效率
ALLOWOVERWRITE;

• 说明：UNLOAD会自动拆分文件，Parquet的列存储压缩特性更适合分析场景，且能保留原表数据类型信息。

2. 导出表DDL到S3 #

直接查询Redshift系统表生成100%准确的DDL，避免第三方库的猜测误差：

SELECT pg_get_ddl('your_schema.your_table'::regclass) AS table_ddl;

将查询结果保存为文本文件（如your_table_ddl.sql），上传到S3对应表的备份目录下（例如s3://your-bucket/path/to/backup/your_table/your_table_ddl.sql），实现数据与DDL的统一存储。

如果需要批量导出多表DDL，可通过遍历系统表实现：

SELECT schemaname, tablename, pg_get_ddl((quote_ident(schemaname) || '.' || quote_ident(tablename))::regclass) AS ddl
FROM pg_tables
WHERE schemaname = 'your_schema' AND tablename IN ('table1', 'table2');

将结果导出为CSV或批量生成SQL文件后，上传至S3对应目录即可。

二、从S3恢复表到Redshift #

1. 重建表结构（从S3的DDL文件） #

• 下载S3上的your_table_ddl.sql文件，在Redshift客户端直接执行DDL语句创建空表。
• 批量恢复场景下，可用AWS CLI批量下载DDL文件：

aws s3 cp s3://your-bucket/path/to/backup/ ./local-ddl/ --recursive --exclude "*" --include "*.sql"

再在Redshift中依次执行这些SQL文件。

2. 加载S3中的Parquet数据到Redshift #

使用Redshift原生COPY命令加载数据，类型匹配精准：

COPY your_schema.your_table
FROM 's3://your-bucket/path/to/backup/your_table/'
IAM_ROLE 'arn:aws:iam::123456789012:role/your-redshift-s3-role'
FORMAT PARQUET;

• 说明：若原表导出时使用了PARTITION BY，COPY会自动识别分区信息，无需额外配置。

三、从S3数据生成DDL的最佳方案 #

如果未提前备份DDL，需从S3的Parquet文件反向生成Redshift兼容DDL，推荐两种可靠方式：

1. 通过Redshift Spectrum临时映射表生成DDL #

• 先创建外部Schema指向S3数据路径：

CREATE EXTERNAL SCHEMA spectrum_schema
FROM DATA CATALOG
DATABASE 'spectrum_db'
IAM_ROLE 'arn:aws:iam::123456789012:role/your-redshift-s3-role'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

CREATE EXTERNAL TABLE spectrum_schema.temp_table
STORED AS PARQUET
LOCATION 's3://your-bucket/path/to/data/';

• 查询系统表获取外部表DDL，再调整为Redshift内部表格式：

SELECT pg_get_ddl('spectrum_schema.temp_table'::regclass);

• 调整要点：移除EXTERNAL、STORED AS PARQUET、LOCATION关键字，按需添加DISTKEY、SORTKEY等内部表存储参数。

2. 使用AWS Glue Crawler自动生成表结构 #

• 配置Glue Crawler扫描S3的Parquet数据路径，Crawler会自动识别列名、数据类型，生成Glue Data Catalog中的表。
• 可直接从Glue Data Catalog导出DDL，或通过Redshift Spectrum引用该表后生成内部表DDL。
• 优势：完全自动化，适合批量处理多文件，类型识别准确率远高于第三方猜测类工具。

四、工具替代建议 #

放弃依赖正则猜测类型的第三方库，优先使用Redshift原生命令和AWS官方服务：

• 备份：UNLOAD+系统表DDL查询，稳定且类型完全匹配。
• 恢复：COPY+DDL执行，官方支持无兼容性问题。
• 反向生成DDL：Spectrum外部表或Glue Crawler，均基于元数据精准识别，避免类型错误。

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