Athena Engine Version2でDecimalにCastした値の挙動を確認してみた

Athenaで小数点以下の計算で精度を保たせる場合、手段の一つとしてDecimalへの型キャストを用いることがあります。例えば、DECIMAL(11,5)、DECIMAL(20, 15) 等。ただ、Decimalで出力したもののDoubleと同等の値になってしまい、意図した結果と大幅に異なることがあります。

小数点以下桁数の多いデータを扱う機会が多く、且つ対処に手を焼いたため覚書としてまとめました。

型変換と結果の値

数値の型変換は意図したものにならないことがあります。

SELECT
    '0.2912000000' as original,
    typeof('0.2912000000') as "varchar",
    typeof(cast('0.2912000000' as DOUBLE)) as "double",
    typeof(cast('0.2912000000' as REAL)) as "real",    
    typeof(cast('0.2912000000' as DECIMAL(20,10))) as "decimal",
    cast('0.2912000000' as DOUBLE) as "varchar_to_double",
    cast('0.2912000000' as REAL) as "varchar_to_real",   
    cast('0.2912000000' as DECIMAL(20,10)) as "varchar_to_decimal";

扱いづらいのはDECIMALにキャスト後の値です。小数点以下を10桁と指定したのに関わらず、REALやDOUBLEと同じ値になっています。

型変換が正常に行われているのか確認する

DECIMALへの型変換にて異常が発生しているのかも、という疑問を再度VARCHARに変換することで解消してみます。

SELECT
    '0.2912000000' as original,
    cast(cast('0.2912000000' as DOUBLE) as VARCHAR) as "varchar_to_double_tovarchar",
    cast(cast('0.2912000000' as REAL) as VARCHAR) as "varchar_to_real_to_varchar",   
    cast(cast('0.2912000000' as DECIMAL(20,10)) as VARCHAR) as "varchar_to_decimal_to_varchar";

型変換を繰り返してみた結果、内部的にはDECIMALとして維持されているものの、出力時にはDOUBLEと同じ扱いになっていることが分かります。

Presto 0.198以降明示的な型指定子のない10進リテラル(1.2など)はDECIMALとして扱われるとあります。しかし、Athenaのエンジンバージョン2はPresto 0.217ベースながらもDECIMALとして値が出力されていません。

For compatibility reasons decimal literals without explicit type specifier (e.g. 1.2) are treated as values of the DOUBLE type by default up to version 0.198. After 0.198 they are parsed as DECIMAL.

Athena エンジンバージョン 2 は Presto 0.217 に基づいています。

計算してみる

単純な型変換がどうなるかは確認できました。次に計算を挟んでみます。

SELECT
    cast('0.2912000000' as DOUBLE) + cast('0.2912000000' as DOUBLE) as "varchar_to_double_calc",
    cast('0.2912000000' as REAL) + cast('0.2912000000' as REAL) as "varchar_to_real_calc",
    cast('0.2912000000' as DECIMAL(20,10)) + cast('0.2912000000' as DECIMAL(20,10)) as "varchar_to_decimal_calc";

計算上は 0.5824 ですが、結果は以下の通り。

次に結果をVARCHARに変換してみます。

SELECT
    cast(cast('0.2912000000' as DOUBLE) + cast('0.2912000000' as DOUBLE) as VARCHAR) as "varchar_to_double_calc_to_varchar",
    cast(cast('0.2912000000' as REAL) + cast('0.2912000000' as REAL) as VARCHAR) as "varchar_to_real_calc_to_varchar",
    cast(cast('0.2912000000' as DECIMAL(20,10)) + cast('0.2912000000' as DECIMAL(20,10)) as VARCHAR) as "varchar_to_decimal_calc_to_varchar";

DOUBLEとREALで小数点以下末尾0を除いて維持できつつ、DECIMALでは小数点以下の桁数も維持されました。

あとがき

Athenaで型をDECIMALにして扱う場合、小数点以下の精度が異なり、結果 SUM 等実行後に大幅なズレが生じることもあります。今後のPrestoエンジンのバージョンアップにて変わる可能性もありますが、精度を求める場合には今回のように結果をVARCHAR型で維持する等の選択肢も検討してみましょう。

参考リンク

• Athena(Presto)とGlue(Spark)で同じクエリを実行した際に異なる値が返る原因