AWS Athena定义decimal(9,2)字段后查询报错求助

问题根源 #

使用org.apache.hadoop.hive.serde2.OpenCSVSerde时，该SerDe默认会将CSV中所有值以字符串形式读取。当表字段定义为decimal(9,2)时，Athena尝试将字符串直接转换为HiveDecimal类型，若没有明确的类型映射规则，就会触发String cannot be cast to HiveDecimal的转换异常。

解决方案 #

方案一：替换为LazySimpleSerDe（推荐） #

LazySimpleSerDe是Athena原生支持的SerDe，对数值类型的转换兼容性更好，适合CSV格式数据：

CREATE EXTERNAL TABLE IF NOT EXISTS `MY_DB`.`MY_NEW_TABLE` (
  `event_id` bigint
  ,`time_stamp` string
  ,`amount` decimal(9,2)
)
PARTITIONED BY (
  `year` smallint
  ,`month` tinyint
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
ESCAPED BY '\\'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION 's3://<BUCKETNAME>/<SUBFOLDER>/'
TBLPROPERTIES (
    'skip.header.line.count' = '1'
);

无需指定额外SerDe类，直接通过DELIMITED配置项定义CSV的分隔符、转义符、引号符等规则即可。

方案二：给OpenCSVSerDe添加类型映射属性 #

若必须使用OpenCSVSerDe，可在SERDEPROPERTIES中添加columns.types属性，明确指定每个字段的类型，强制SerDe解析时完成类型转换：

CREATE EXTERNAL TABLE IF NOT EXISTS `MY_DB`.`MY_NEW_TABLE` (
  `event_id` bigint
  ,`time_stamp` string
  ,`amount` decimal(9,2)
)
PARTITIONED BY (
  `year` smallint
  ,`month` tinyint
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  'separatorChar' = ','
  ,'quoteChar' = '"'
  ,'escapeChar' = '\\'
  ,'serialization.null.format' = ''
  ,'columns.types' = 'bigint,string,decimal(9,2)'
)
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://<BUCKETNAME>/<SUBFOLDER>/'
TBLPROPERTIES (
    'classification' = 'csv'
    ,'skip.header.line.count' = '1'
);

columns.types的值需与表字段顺序一一对应，指定每个字段的目标类型。

额外检查项 #

确保CSV文件中amount字段格式符合要求：

• 无千分位分隔符（如1,234.56需先清理为1234.56）
• 无多余空格、货币符号等非数字字符
• 空值统一使用空字符串（与serialization.null.format配置一致）

修改DDL后，执行分区同步命令（若为分区表）：

MSCK REPAIR TABLE MY_DB.MY_NEW_TABLE;

再执行SELECT查询验证结果。

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