Hive 2.3 decimal精度損失問題

1.準備測試數(shù)據(jù)

????使用如下建表語句，并插入測試數(shù)據(jù)：

CREATE?TABLE?IF?NOT?EXISTS test_decimal(

? ? md5 string,

? ? id int,

? ? ty int,

? ? amount decimal(38, 12)

) stored as orc?;

insert into table test_decimal values

('9F99855A44BD41FE592B69E0D36BF3E8', 4591, 2, 188593.210890000000),

('9F99855A44BD41FE592B69E0D36BF3E8', 4592, 2, 177918.123481132049),

('9F99855A44BD41FE592B69E0D36BF3E8', 4593, 2, 10675.087408867951);

2.使用測試sql測試（在2.3.x版本中執(zhí)行的）

????使用測試sql，發(fā)現(xiàn)測試的結果有精度損失：

hive> select id, sum(amount) from test_decimal group by id;

OK

4591 188593.210890000000

4592 177918.123481132049

4593 10675.087408867951

Time taken: 28.013 seconds, Fetched: 3 row(s)


hive>? select id, sum(amount) * -1 from test_decimal group by id;

OK

4591 -188593.2108900000

4592 -177918.1234811320

4593 -10675.0874088680

Time taken: 26.016 seconds, Fetched: 3 row(s)

????通過比較測試結果發(fā)現(xiàn)，在sum函數(shù)之后乘以 -1 導致精度損失了2位。

3.通過分析執(zhí)行計劃查找兩條sql的執(zhí)行計劃的區(qū)別，查找原因（在2.3.x版本中執(zhí)行的）

????直接輸出sum的sql的執(zhí)行計劃：

hive> explain select id, sum(amount) from test_decimal group by id;

OK

STAGE DEPENDENCIES:

? Stage-1 is a root stage

? Stage-0 depends on stages: Stage-1

STAGE PLANS:

? Stage: Stage-1

? ? Map Reduce

? ? ? Map Operator Tree:

? ? ? ? ? TableScan

? ? ? ? ? ? alias: test_decimal

? ? ? ? ? ? Statistics: Num rows: 3 Data size: 708 Basic stats: COMPLETE Column stats: NONE

? ? ? ? ? ? Select Operator

? ? ? ? ? ? ? expressions: id (type: int), amount (type: decimal(38,12))

? ? ? ? ? ? ? outputColumnNames: id, amount

? ? ? ? ? ? ? Statistics: Num rows: 3 Data size: 708 Basic stats: COMPLETE Column stats: NONE

? ? ? ? ? ? ? Group By Operator

? ? ? ? ? ? ? ? aggregations: sum(amount)

? ? ? ? ? ? ? ? keys: id (type: int)

? ? ? ? ? ? ? ? mode: hash

? ? ? ? ? ? ? ? outputColumnNames: _col0, _col1

? ? ? ? ? ? ? ? Statistics: Num rows: 3 Data size: 708 Basic stats: COMPLETE Column stats: NONE

? ? ? ? ? ? ? ? Reduce Output Operator

? ? ? ? ? ? ? ? ? key expressions: _col0 (type: int)

? ? ? ? ? ? ? ? ? sort order: +

? ? ? ? ? ? ? ? ? Map-reduce partition columns: _col0 (type: int)

? ? ? ? ? ? ? ? ? Statistics: Num rows: 3 Data size: 708 Basic stats: COMPLETE Column stats: NONE

? ? ? ? ? ? ? ? ? value expressions: _col1 (type: decimal(38,12))

? ? ? Reduce Operator Tree:

? ? ? ? Group By Operator

? ? ? ? ? aggregations: sum(VALUE._col0)

? ? ? ? ? keys: KEY._col0 (type: int)

? ? ? ? ? mode: mergepartial

? ? ? ? ? outputColumnNames: _col0, _col1

? ? ? ? ? Statistics: Num rows: 1 Data size: 236 Basic stats: COMPLETE Column stats: NONE

? ? ? ? ? File Output Operator

? ? ? ? ? ? compressed: false

? ? ? ? ? ? Statistics: Num rows: 1 Data size: 236 Basic stats: COMPLETE Column stats: NONE

? ? ? ? ? ? table:

? ? ? ? ? ? ? ? input format: org.apache.hadoop.mapred.SequenceFileInputFormat

? ? ? ? ? ? ? ? output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

? ? ? ? ? ? ? ? serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

? Stage: Stage-0

? ? Fetch Operator

? ? ? limit: -1

? ? ? Processor Tree:

? ? ? ? ListSink

Time taken: 0.16 seconds, Fetched: 48 row(s)

sum后乘以 -1 的sql的執(zhí)行計劃：

hive> explain select id, sum(amount)*-1 from test_decimal group by id;

OK

STAGE DEPENDENCIES:

? Stage-1 is a root stage

? Stage-0 depends on stages: Stage-1

STAGE PLANS:

? Stage: Stage-1

? ? Map Reduce

? ? ? Map Operator Tree:

? ? ? ? ? TableScan

? ? ? ? ? ? alias: test_decimal

? ? ? ? ? ? Statistics: Num rows: 3 Data size: 708 Basic stats: COMPLETE Column stats: NONE

? ? ? ? ? ? Select Operator

? ? ? ? ? ? ? expressions: id (type: int), amount (type: decimal(38,12))

? ? ? ? ? ? ? outputColumnNames: id, amount

? ? ? ? ? ? ? Statistics: Num rows: 3 Data size: 708 Basic stats: COMPLETE Column stats: NONE

? ? ? ? ? ? ? Group By Operator

? ? ? ? ? ? ? ? aggregations: sum(amount)

? ? ? ? ? ? ? ? keys: id (type: int)

? ? ? ? ? ? ? ? mode: hash

? ? ? ? ? ? ? ? outputColumnNames: _col0, _col1

? ? ? ? ? ? ? ? Statistics: Num rows: 3 Data size: 708 Basic stats: COMPLETE Column stats: NONE

? ? ? ? ? ? ? ? Reduce Output Operator

? ? ? ? ? ? ? ? ? key expressions: _col0 (type: int)

? ? ? ? ? ? ? ? ? sort order: +

? ? ? ? ? ? ? ? ? Map-reduce partition columns: _col0 (type: int)

? ? ? ? ? ? ? ? ? Statistics: Num rows: 3 Data size: 708 Basic stats: COMPLETE Column stats: NONE

? ? ? ? ? ? ? ? ? value expressions: _col1 (type: decimal(38,12))

? ? ? Reduce Operator Tree:

? ? ? ? Group By Operator

? ? ? ? ? aggregations: sum(VALUE._col0)

? ? ? ? ? keys: KEY._col0 (type: int)

? ? ? ? ? mode: mergepartial

? ? ? ? ? outputColumnNames: _col0, _col1

? ? ? ? ? Statistics: Num rows: 1 Data size: 236 Basic stats: COMPLETE Column stats: NONE

? ? ? ? ? Select Operator

? ? ? ? ? ? expressions: _col0 (type: int), (_col1 * -1) (type: decimal(38,10))

? ? ? ? ? ? outputColumnNames: _col0, _col1

? ? ? ? ? ? Statistics: Num rows: 1 Data size: 236 Basic stats: COMPLETE Column stats: NONE

? ? ? ? ? ? File Output Operator

? ? ? ? ? ? ? compressed: false

? ? ? ? ? ? ? Statistics: Num rows: 1 Data size: 236 Basic stats: COMPLETE Column stats: NONE

? ? ? ? ? ? ? table:

? ? ? ? ? ? ? ? ? input format: org.apache.hadoop.mapred.SequenceFileInputFormat

? ? ? ? ? ? ? ? ? output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

? ? ? ? ? ? ? ? ? serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

? Stage: Stage-0

? ? Fetch Operator

? ? ? limit: -1

? ? ? Processor Tree:

? ? ? ? ListSink

Time taken: 4.656 seconds, Fetched: 52 row(s)

????通過查看兩條sql（兩條sql的差別是在sum函數(shù)后面有沒有乘以 -1）的執(zhí)行計劃發(fā)現(xiàn)，直接輸出sum的結果是 （value expressions: _col1 (type:decimal(38,12))）類型，輸出乘以 -1 的結果是 （ expressions: _col0 (type: int), (_col1 * -1) (type:decimal(38,10)) ）類型，說明乘以 -1 之后，精度損失了2位。

4.分析源碼，找原因

????通過分析sum后乘以 -1 的代碼，其中關鍵的代碼如下：

????2.3.x 版本的GenericUDFOPMultiply 類的關鍵代碼


????prec1 和 scale1 代表的是 decimal（38,12）

????prec2 和 scale2 代表的是 -1被轉換成decimal后的類型為 decimal（1， 0）

????其中 adjustPrecScale()方法的代碼在其父類GenericUDFBaseNumeric中，代碼如下：


????decimal支持的最大精度為38，而通過上面的計算，發(fā)現(xiàn)精度precision字段的值已經達到了40，超過的最大精度，因此，需要重新計算精度，計算后的結果是，將小數(shù)的精度減少了2位為10，精度使用最大精度值38。

????在hive2.3.x中，算術運算的精度的計算公式如下：


????至此，精度損失的原因已經找到，是因為乘法運算，將兩個精度進行相加后再加1，超出了最大精度，重新計算精度時，將小數(shù)位的精度改成了10導致的。

5.解決方案

1）針對這個乘以 -1 的操作，可以改成使用單目運算負號 - 的方式，將負號 - 加到sum前即可。

2）降低建表語句中decimal類型的精度字段的值，根據(jù)上面計算精度的表算出一個滿足需要的最小精度值。

6.為啥相同的sql在1.2.x版本中結果就沒有損失精度

????1.2.x 版本的GenericUDFOPMultiply 類的關鍵代碼

public class GenericUDFOPMultiply extends GenericUDFBaseNumeric {

.....

? @Override

? protected DecimalTypeInfo deriveResultDecimalTypeInfo(int prec1, int scale1, int prec2, int scale2) {

? ? int scale = Math.min(HiveDecimal.MAX_SCALE, scale1 + scale2 );

? ? int prec = Math.min(HiveDecimal.MAX_PRECISION, prec1 + prec2 + 1);

? ? return TypeInfoFactory.getDecimalTypeInfo(prec, scale);

? }

}

????其中 HiveDecimal.MAX_SCALE 和 HiveDecimal.MAX_PRECISION 的值都是38。

????從上面的關鍵代碼中可以看到，在1.2.x中，沒有重新校準精度的地方，而是使用簡單粗暴的方式，各自計算precision和scale的精度，這就會導致在真實數(shù)據(jù)很大的時候，計算出來的值的精度達不到預期，也就是會不準確。，

?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
【社區(qū)內容提示】社區(qū)部分內容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內容（如有圖片或視頻亦包括在內）由作者上傳并發(fā)布，文章內容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內容

友情鏈接更多精彩內容