如何更新Athena表并在QuickSight中展示商品价格历史与当前值

核心思路 #

要在不新建表的前提下实现需求，关键是保留历史数据到同一张表中，通过新增时间维度字段区分不同时期的价格，再通过查询逻辑提取历史与当前价格，最后在QuickSight中可视化。

步骤1：调整S3 CSV存储与数据格式 #

不要覆盖原有CSV文件，改为追加式存储历史版本，同时给每条价格记录添加时间标记：

• 每次价格更新时，上传新的CSV文件（文件名可带时间戳，如product_prices_202410.csv），或按时间分区存储到S3路径（如s3://your-bucket/prices/update_month=2024-10/）
• 每个CSV必须包含以下字段：product_id（商品唯一标识）、product_name（商品名称）、price（价格）、update_month（价格对应的月份，如2024-10）

步骤2：配置Glue爬虫与Athena表 #

确保所有历史数据都加载到同一张Athena表中：

• 如果是新建表：配置Glue爬虫指向包含所有历史CSV的S3路径，爬虫会自动识别字段并创建表；若用分区存储，爬虫会自动生成以update_month为分区键的分区表
• 如果是现有表：手动修改表结构添加update_month字段，重新运行爬虫，让它加载所有历史文件的数据到该表

步骤3：用Athena查询提取历史与当前价格 #

通过SQL窗口函数在同一张表中同时获取每个商品的历史价格和最新价格，示例查询：

WITH price_ranked AS (
  SELECT
    product_id,
    product_name,
    price,
    update_month,
    -- 按商品分组，给价格记录按时间倒序排名
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY update_month DESC) AS rank_num
  FROM
    your_athena_table_name
)
SELECT
  product_id,
  product_name,
  update_month,
  price AS historical_price,
  -- 提取每个商品的最新价格作为当前价格
  MAX(CASE WHEN rank_num = 1 THEN price END) OVER (PARTITION BY product_id) AS current_price
FROM
  price_ranked
ORDER BY
  product_id, update_month;

步骤4：在QuickSight中可视化 #

基于上述Athena查询创建数据集，选择合适的组件展示：

• 折线图：X轴设为update_month，Y轴设为historical_price，按product_id分组，直观展示价格随时间的变化趋势
• 对比表格：列包含product_name、update_month、historical_price、current_price，清晰列出各时期价格与当前价格的对比
• 卡片组件：针对单个商品，展示当前价格并附带历史价格趋势小图

步骤5：自动化数据更新 #

• 配置S3事件通知，当新CSV文件上传时自动触发Glue爬虫/ETL任务，确保Athena表及时同步最新数据
• 在QuickSight中设置数据集自动刷新，保证展示内容为最新状态

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