如何将S3存储的整数JSON数据在Athena表中展示为三位小数

你无法直接在DDL的原始列定义中把整数转换为三位小数格式，但可以通过生成列（Generated Columns）、实时查询转换或视图封装这几种方式实现需求，以下是具体思路和示例：

1. 使用生成列预定义转换逻辑 #

Athena支持生成列，可在DDL中基于原始整数列定义计算后的新列，后续查询时直接调用该列即可得到三位小数结果。

假设你的JSON数据中存储货币值的整数列名为amount_int（例如1000代表$10.00），示例DDL如下：

CREATE EXTERNAL TABLE IF NOT EXISTS your_table (
  -- 定义其他原始列
  id STRING,
  create_time TIMESTAMP,
  amount_int INT,
  -- 生成列：将整数除以100.0，转换为保留三位小数的decimal类型
  amount_decimal DECIMAL(10,3) GENERATED ALWAYS AS (CAST(amount_int / 100.0 AS DECIMAL(10,3))) STORED
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://your-bucket/data-path/';

• STORED表示该生成列会被物理存储，查询时无需重复计算；
• DECIMAL(10,3)指定总位数10、小数位3，避免数值溢出。

2. 查询时实时转换（无需修改表结构） #

如果不想调整原表结构，可在查询语句中直接完成转换：

SELECT
  amount_int,
  -- 转换为三位小数数值
  CAST(amount_int / 100.0 AS DECIMAL(10,3)) AS amount_decimal,
  -- 带$符号的格式化结果
  CONCAT('$', CAST(amount_int / 100.0 AS DECIMAL(10,3))) AS formatted_amount
FROM your_table;

注意：必须用amount_int / 100.0而非整数除法amount_int / 100，否则会丢失小数位。

3. 创建视图封装转换逻辑 #

若需频繁查询格式化后的数据，可创建视图简化操作：

CREATE VIEW your_currency_view AS
SELECT
  -- 保留所有原始列
  *,
  CAST(amount_int / 100.0 AS DECIMAL(10,3)) AS amount_decimal,
  CONCAT('$', CAST(amount_int / 100.0 AS DECIMAL(10,3))) AS formatted_amount
FROM your_table;

后续查询直接调用视图：

SELECT id, formatted_amount FROM your_currency_view;

补充说明 #

你之前尝试的DECIMAL(4,2)无法满足需求，是因为它定义的是总位数4、小数位2，仅能将1000转换为10.00；而DECIMAL(10,3)的小数位设为3，才能输出10.000的格式。

内容的提问来源于stack exchange，提问作者3136_ Vipul Jain