如何按列名而非列顺序在AWS Athena中从多CSV建表

你遇到的这个问题很典型——默认的OpenCSVSerDe是按列的位置顺序来匹配表定义的字段，完全忽略CSV里的表头行。所以当不同文件的列顺序不一样时，就会出现像你例子里那样，把size列的值错误映射到name字段的情况。

要实现按列名提取指定字段，你可以使用AWS Athena专门提供的CsvHeaderSerDe，它会读取CSV的表头信息，然后根据列名来匹配表定义中的字段，完全不受列顺序的影响。

正确的CREATE TABLE语句示例 #

CREATE EXTERNAL TABLE `test`(
  `id` string, 
  `name` string
)
ROW FORMAT SERDE 
  'com.amazonaws.athena.serde.CsvHeaderSerDe' 
WITH SERDEPROPERTIES (
  'escapeChar'='\\',
  'quoteChar'='"',
  'separatorChar'=','
)
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://your-bucket/path/to/csvs/'
TBLPROPERTIES (
  'has_encrypted_data'='false',
  'skip.header.line.count'='1' -- 跳过每个CSV的表头行
);

关键说明 #

• SerDe选择：替换原来的OpenCSVSerDe为com.amazonaws.athena.serde.CsvHeaderSerDe，这个SerDe的核心能力就是解析CSV表头并按列名映射字段。
• 跳过表头：必须在TBLPROPERTIES中添加skip.header.line.count='1'，告诉Athena每个CSV文件的第一行是表头，不需要作为数据行读取。
• 字段匹配：只要你的表定义中的字段名和CSV表头中的列名完全一致（注意大小写敏感），不管CSV里的列顺序如何，都会正确提取对应字段的值。

用这个语句创建表后，查询test表就能得到你期望的结果：

+----+---------+
| id | name    |
+----+---------+
| 1  | shirt   |
| 2  | shoes   |
| 5  | shirt   |
| 6  | t-shirt |
+----+---------+

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