如何在Redshift中按字符串类型时间字段的日期部分对外部表进行分区？

我明白你的困扰——Redshift外部表确实不允许在ALTER TABLE PARTITION语句里直接使用函数来指定分区键，不过有几个实用的解决方案可以帮你实现按mydate字段日期部分分区的需求：

方案1：在Athena层面预定义分区 #

因为你的Redshift外部表是基于Athena数据目录的，我们可以先在Athena中为原表添加分区列并配置分区，之后Redshift就能自动识别这些分区：

1. 首先在Athena中修改foo表的结构，添加一个用于分区的字符串列（比如mydate_date）：

ALTER TABLE foo ADD COLUMNS (mydate_date STRING);

2. 然后手动添加对应分区（或者用MSCK REPAIR TABLE自动加载，前提是S3路径符合分区格式）：

ALTER TABLE foo ADD PARTITION (mydate_date='2021/02/09') LOCATION 's3://my s3 location/foo_2021_02_09/';

3. 回到Redshift，刷新外部表的元数据：

REFRESH TABLE athena_schema.foo;

之后你就可以直接用mydate_date作为分区键进行查询了。

方案2：在Redshift中创建带计算列的视图 #

如果不想修改Athena的表结构，可以在Redshift里创建一个包含日期计算列的视图，实现逻辑上的分区过滤，且Redshift会将过滤条件下推到Athena，保证查询效率：

CREATE VIEW foo_date_filtered AS
SELECT 
    *,
    LEFT(mydate, 10) AS mydate_date -- 提取日期部分作为计算列
FROM athena_schema.foo;

之后查询时直接过滤计算列即可：

SELECT * FROM foo_date_filtered WHERE mydate_date = '2021/02/09';

方案3：重构S3数据结构并创建新的分区外部表 #

如果可以调整S3数据的存储路径，按照Redshift/Athena支持的分区格式（比如s3://my s3 location/mydate_date=2021/02/09/）来组织数据，然后重新创建外部表：

1. 调整S3文件路径为分区格式，将对应日期的数据放到mydate_date=2021/02/09这样的子目录下；
2. 在Redshift中创建带分区列的外部表：

CREATE EXTERNAL TABLE athena_schema.foo_partitioned (
    -- 列出所有45个字段，除了分区列
    mydate STRING,
    field1 INT,
    field2 VARCHAR(100),
    -- ... 其他字段
)
PARTITIONED BY (mydate_date STRING)
LOCATION 's3://my s3 location/'
TABLE PROPERTIES ('skip.header.line.count'='1'); -- 根据你的数据情况调整

3. 加载分区元数据：

MSCK REPAIR TABLE athena_schema.foo_partitioned;

这样创建的表就能直接使用mydate_date作为分区键，高效地按日期过滤数据。

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