SQL中如何用CREATE TABLE AS实现按日期分组聚合及S3分区存储

核心问题原因 #

• 目录结构不符合预期是因为你混淆了分桶和分区的作用：分桶是将同字段值的数据写入同一文件，不会生成层级目录；要实现item一级分区、sales_date二级分区的目录结构，需要将两个字段按顺序加入partitioned_by参数，不需要设置分桶参数。
• 原有SQL逻辑错误：行号过滤逻辑放在了聚合之后，无法实现「每个sales_date最多取N行」的需求，需要先对原始数据按日期分区打行号过滤后再聚合。

修正后的SQL代码 #

CREATE TABLE somedb.mytable
WITH (
  format = 'PARQUET',
  external_location = 's3://somes3path/',
  -- 按item一级分区、sales_date二级分区，顺序对应分区层级
  partitioned_by = ARRAY['item', 'sales_date']
) AS
SELECT
  max(vietnamese) as vietnamese,
  sum(cost) as total_cost,
  array_agg(cost) as costs,
  array_agg(unique_id) as unique_ids,
  -- 分区字段必须放在SELECT列表末尾，顺序和partitioned_by保持一致
  item,
  sales_date
FROM (
  -- 内层先过滤每个sales_date最多取N行，这里N取示例值3，可自行调整
  SELECT 
    *,
    row_number() OVER (PARTITION BY sales_date ORDER BY rand()) as rn
  FROM mytable
  WHERE sales_date BETWEEN '20211002' AND '20211004'
) t
WHERE rn <= 3 -- 对应每个sales_date最多取N行的要求
GROUP BY item, sales_date;

验证说明 #

执行后S3路径下会自动生成符合要求的层级目录结构：
s3://somes3path/item=[商品值]/sales_date=[日期值]/xxx.parquet
每个目录下存储对应item、对应日期的聚合结果parquet文件。

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