如何在SQL Athena中获取CSV创建时间以构建数据新鲜度表？

Athena依赖S3存储数据，而S3对象没有原生的“创建时间”字段，通常用最后修改时间等价于创建时间（除非文件被后续修改过）。以下是几种实用的获取方式：

方法1：利用Athena虚拟列 + S3元数据函数 #

对于已创建Athena表的CSV文件，可通过内置虚拟列$path获取文件的S3路径，再用s3_last_modified_date函数提取最后修改时间：

SELECT
  DISTINCT $path AS csv_file_path,
  s3_last_modified_date($path) AS file_last_modified_utc
FROM
  your_database.your_table;

• $path：返回每行数据对应的源文件S3路径，用DISTINCT去重可得到唯一的CSV文件列表
• s3_last_modified_date：返回S3对象的最后修改时间（UTC时区的TIMESTAMP类型）

如果需要转换为本地时区（比如北京时间），可使用CONVERT_TIMEZONE函数：

SELECT
  DISTINCT $path AS csv_file_path,
  CONVERT_TIMEZONE('UTC', 'Asia/Shanghai', s3_last_modified_date($path)) AS file_last_modified_cst
FROM
  your_database.your_table;

方法2：查询Glue分区元数据（适用于分区表） #

如果你的CSV文件是按时间分区存储并创建了分区表，可直接从information_schema.partitions获取分区的创建/访问时间：

SELECT
  partition_key,
  partition_value AS partition_date,
  creation_time AS partition_create_time,
  last_access_time AS partition_last_access_time
FROM
  information_schema.partitions
WHERE
  table_schema = 'your_database'
  AND table_name = 'your_partitioned_table';

这里的creation_time是Glue分区的创建时间，通常和对应CSV文件的上传时间一致，适合批量管理的分区表场景。

方法3：直接扫描S3桶（未建表时使用） #

如果还未创建Athena表，可直接查询S3路径下的CSV文件元数据：

SELECT
  key AS csv_file_path,
  last_modified AS file_last_modified_utc,
  size AS file_size_bytes
FROM
  s3object s3
WHERE
  s3."$path" = 's3://your-bucket/path/to/csvs/'
  AND s3."$extension" = 'csv';

这种方式无需提前建表，直接返回指定路径下所有CSV文件的路径、修改时间和大小。

注意事项 #

• 权限要求：Athena执行角色需要拥有目标S3桶的s3:GetObject和s3:ListBucket权限，以及Glue Data Catalog的访问权限（方法2需要）
• 时间精度：S3的最后修改时间精度为秒级，无法获取更细粒度的时间

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