AWS Athena表未按定义Schema读取S3指定文件问题排查

问题描述 #

在同一S3文件夹s3://pak1/ISB/Office/FFC/test/dummy/下存放了两个结构不同的CSV文件：

• dummy.csv：包含tid[int]、tnm[string]列
• test.csv：包含num[int]、name[string]列

通过以下SQL创建pipeline.test外部表：

CREATE EXTERNAL TABLE IF NOT EXISTS `pipeline`.`test` (`num` int, `name` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES ('field.delim' = ',')
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://pak1/ISB/Office/FFC/test/dummy/'
TBLPROPERTIES ('classification' = 'csv');

执行SELECT * FROM test;时，期望仅返回test.csv的数据，但实际结果包含两个文件的混合数据，格式混乱，更换SerDe后问题仍存在。

原因分析 #

1. Athena外部表的核心特性是基于S3路径而非文件名关联数据：只要LOCATION指向的文件夹下的文件符合表定义的存储格式（这里是CSV），Athena就会读取所有文件，不会区分文件名。
2. 两个文件结构不匹配，用同一个Schema解析时，dummy.csv的列会被强行映射到表的num和name字段，导致数据类型不匹配或内容混乱，最终和test.csv的数据混合返回。

解决方法 #

• 拆分S3路径（推荐）：将dummy.csv和test.csv分别放到不同的子文件夹，比如把test.csv移到s3://pak1/ISB/Office/FFC/test/test_files/，然后重新创建表时将LOCATION指向该子文件夹，或修改现有表的LOCATION。
• 查询时过滤特定文件：临时场景下，可通过$PATH字段过滤文件名，SQL示例：

SELECT * FROM test
WHERE "$PATH" LIKE '%test.csv';

• 使用分区表：如果需要在同一父文件夹下管理不同结构的文件，可创建分区表，将不同结构的文件关联到不同分区，查询时指定分区即可获取对应数据。

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