如何在从S3 Bucket向Redshift表导入数据时存储源文件夹名称

刚好碰到过一模一样的需求，给你分享两个靠谱的解决方案，都是Redshift原生支持的，不用额外写脚本或者工具。

方法1：直接在COPY命令中用元数据列+字符串函数提取 #

Redshift的COPY命令支持读取S3文件的元数据（比如文件路径、大小、修改时间等），其中$PATH会返回文件的完整S3路径。我们可以直接在COPY过程中用字符串函数从路径里截取文件夹名，存入source_id列。

步骤示例： #

1. 先确保你的目标表已经包含source_id列：

CREATE TABLE your_target_table (
  -- 替换成你的实际数据列
  user_id INT,
  order_date DATE,
  amount DECIMAL(10,2),
  -- 用来存储S3文件夹名的列
  source_id VARCHAR(100)
);

2. 执行COPY命令，通过SPLIT_PART从$PATH中提取文件夹名：

COPY your_target_table (user_id, order_date, amount, source_id)
FROM 's3://your-bucket-name/*/*' -- 通配符匹配所有一级文件夹下的文件
IAM_ROLE 'arn:aws:iam::123456789012:role/your-redshift-access-role'
FORMAT AS CSV -- 根据你的文件格式调整，比如PARQUET、JSON等
DELIMITER ',' QUOTE '"' -- 如果是CSV，补充格式参数
-- 核心逻辑：用SPLIT_PART分割路径，取对应位置的文件夹名
COLUMNS (user_id, order_date, amount, source_id AS SPLIT_PART("$PATH", '/', 4));

关键说明： #

• 假设你的S3路径是s3://my-data-bucket/jan-2024/orders.csv，分割后的路径数组是['s3:', '', 'my-data-bucket', 'jan-2024', 'orders.csv']，所以用SPLIT_PART("$PATH", '/', 4)就能拿到jan-2024这个文件夹名。你需要根据自己的路径层级调整最后的数字索引。
• 如果是多层文件夹（比如s3://bucket/region/us-west/orders.csv），要提取us-west的话，索引就是5。

方法2：先加载到临时表再处理（适合复杂路径场景） #

如果你的文件夹层级比较复杂，或者需要对路径做额外的清洗处理，建议先把数据和完整路径加载到临时表，再通过SQL提取文件夹名插入目标表。

步骤示例： #

1. 创建临时表存储原始数据和S3路径：

CREATE TEMP TABLE staging_orders (
  user_id INT,
  order_date DATE,
  amount DECIMAL(10,2),
  full_s3_path VARCHAR(200)
);

2. 把数据和路径导入临时表：

COPY staging_orders (user_id, order_date, amount, full_s3_path)
FROM 's3://your-bucket-name/**/*' -- 用**匹配所有层级的文件夹
IAM_ROLE 'arn:aws:iam::123456789012:role/your-redshift-access-role'
FORMAT AS PARQUET -- 这里用PARQUET举例，根据实际格式调整
COLUMNS (user_id, order_date, amount, full_s3_path AS "$PATH");

3. 处理路径后插入目标表：

INSERT INTO your_target_table (user_id, order_date, amount, source_id)
SELECT
  user_id,
  order_date,
  amount,
  -- 这里可以写更复杂的逻辑，比如提取倒数第二个文件夹
  REVERSE(SPLIT_PART(REVERSE(full_s3_path), '/', 2)) AS source_id
FROM staging_orders;

优势： #

• 可以先查看临时表中的full_s3_path值，验证分割逻辑是否正确，避免直接导入出错
• 支持更复杂的路径处理，比如提取倒数第二层文件夹、过滤特定命名的文件夹等

注意事项 #

• 确保Redshift使用的IAM角色有读取目标S3桶的权限，否则COPY命令会报错
• 测试分割逻辑：可以先导入少量数据到临时表，用SELECT SPLIT_PART(full_s3_path, '/', N) FROM staging_table验证索引是否正确
• 不同文件格式（CSV/PARQUET/JSON）的COPY参数略有不同，但$PATH元数据列的用法是一致的

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