You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "shuxian6688 (via GitHub)" <gi...@apache.org> on 2023/11/28 09:28:40 UTC
[I] [Bug] Hive外部表读取Orc表结构的decimal数据类型时，数据不一致 [doris]
shuxian6688 opened a new issue, #27701:
URL: https://github.com/apache/doris/issues/27701
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues.
### Version
Doris: doris-2.0.2-rc05-ae923f7
Hive: 2.1
### What's Wrong?
使用hive外部表方式读取hive中表数据，表结构为Orc结构，数据类型为Decimal 类型进行聚合时，发现在doris中精度丢失，然后导致两边数据不一致
### What You Expected?
doris中读取出来的数据一致
### How to Reproduce?
_No response_
### Anything Else?
_No response_
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Hive外部表读取Orc表结构的decimal数据类型时，数据不一致 [doris]
Posted by "Yulei-Yang (via GitHub)" <gi...@apache.org>.
Yulei-Yang commented on issue #27701:
URL: https://github.com/apache/doris/issues/27701#issuecomment-1829789998
I will look into it
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Hive外部表读取Orc表结构的decimal数据类型时，数据不一致 [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #27701:
URL: https://github.com/apache/doris/issues/27701#issuecomment-3287230132
We're closing this issue because it hasn't been updated in a while.
If you'd like to revive this issue, please reopen it and feel free a maintainer to remove the Stale tag!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Hive外部表读取Orc表结构的decimal数据类型时，数据不一致 [doris]
Posted by "shuxian6688 (via GitHub)" <gi...@apache.org>.
shuxian6688 commented on issue #27701:
URL: https://github.com/apache/doris/issues/27701#issuecomment-1835682111
你上面的这个表我测试了，没问题，但是当我使用分区表测试就会出现结果不一致的情况，测试结果发现在doris2.02中得到的聚合结果有时比Hive中的结果小100倍，有时10000倍。但是如果直接聚合没有条件的话,结果又是一致的。 下面是我的测试过程
CREATE TABLE `dw_app.app_acc_mngreport_bu_netprofit_check_monthly_test`(
`busi_quarter` string COMMENT '业务季度',
`plate_code` string COMMENT '板块编码',
`plate_name` string COMMENT '板块名称',
`bu_code` string COMMENT '事业部编码',
`bu_name` string COMMENT '事业部名称',
`dept_code` string COMMENT '部门编码',
`dept_name` string COMMENT '部门名称',
`thismon_erp` decimal(28,4) COMMENT 'ERP毛利'
)
COMMENT ''
PARTITIONED BY (
`busi_month` string COMMENT '业务月度')
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
insert overwrite dw_app.app_acc_mngreport_bu_netprofit_check_monthly_test partition(busi_month)
('2023-Q3','10','MGM（全球移动业务）','11','大中华区罐箱事业部','1112','烟台罐箱部',-884810.0500,'2023-08'),
('2023-Q3','08','MCD（不一样的分销）','19','慎则化工','4305','能源生产运营部', 1404447.0000,'2023-08'),
('2023-Q3','06','MWT (仓配一体化)','46','华南仓储分装事业部','1526','长沙DC部', 898690.8300 ,'2023-08'),
('2023-Q3','10','MGM（全球移动业务）','66','船供事业部','6605','备件部', -82070.1672,'2023-08'),
('2023-Q3','02','MHA（人事行政）','02','演寂书院','02','演寂书院',0.0000,'2023-08'),
('2023-Q3','13','MHS（安全）','61','大中华区安全技术事业部','61','大中华区安全技术事业部',0.0000,'2023-09'),
('2023-Q3','10','MGM（全球移动业务）','11','大中华区罐箱事业部','11','大中华区罐箱事业部',0.0000,'2023-09'),
('2023-Q3','10','MGM（全球移动业务）','66','船供事业部','6606','物料部', 369567.8575,'2023-09'),
('2023-Q3','06','MWT (仓配一体化)','39','华北配送事业部','39','华北配送事业部',0.0000,'2023-09'),
('2023-Q3','05','MGF（货代）','83','开瑞公司','8304','顺和捷亚',7171083.9000,'2023-09'),
('2023-Q4','06','MWT (仓配一体化)','69','华中仓储分装事业部','1518','南京DC部',3830683.2600,'2023-10'),
('2023-Q4','12','MFA（财经）','06','大中华区财务管理部','0623','MCD慎则久帝财务运营部',0.0000,'2023-10'),
('2023-Q4','06','MWT (仓配一体化)','46','华南仓储分装事业部','1526','长沙DC部',757784.4800,'2023-10'),
('2023-Q4','05','MGF（货代）','49','华中海运事业部','1215','南京海运部',592047.6100,'2023-10'),
('2023-Q4','08','MCD（不一样的分销） ','19','慎则化工','1418','慎则市场营销部',0.0000,'2023-10')
下面的聚合结果为：4422730.8700
select sum(thismon_erp) as thismon_erp from dw_app.app_acc_mngreport_bu_netprofit_check_monthly_test where busi_month= '2023-10' and dept_code in ('1518','1215') -- sum = 4422730.8700
CREATE CATALOG jdbc_hive_catalog PROPERTIES (
'type'='hms',
'hive.metastore.uris' = 'thrift://xxxx.xxx.xxx.xx:9083',
'hive.version'='2.1.1'
);
下面的聚合结果为：44227.3087
select sum(thismon_erp) as thismon_erp from jdbc_hive_catalog.dw_app.app_acc_mngreport_bu_netprofit_check_monthly_test where busi_month= '2023-10' and dept_code in ('1518','1215') -- sum = 44227.3087
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Hive外部表读取Orc表结构的decimal数据类型时，数据不一致 [doris]
Posted by "Yulei-Yang (via GitHub)" <gi...@apache.org>.
Yulei-Yang commented on issue #27701:
URL: https://github.com/apache/doris/issues/27701#issuecomment-1833042752
cannot reproduce in latest 2.0 doris。you can verify latest 2.0 with your data or check below case in your current environment:
run it in hive:
CREATE TABLE `orc_d`(
`id` int,
`name` string,
`account` decimal(10,0))
stored as orc;
insert overwrite table orc_d values (1, 'Amy', 10.11),(2, 'Bob', 20.22),(3, 'Carl', 30.33);
and run `select sum(account) from orc_d;` in doris to do a check
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Hive外部表读取Orc表结构的decimal数据类型时，数据不一致 [doris]
Posted by "shuxian6688 (via GitHub)" <gi...@apache.org>.
shuxian6688 commented on issue #27701:
URL: https://github.com/apache/doris/issues/27701#issuecomment-1831130805
CREATE CATALOG jdbc_hive_catalog PROPERTIES (
'type'='hms',
'hive.metastore.uris' = 'thrift://xxxx.xxx.xxx.xx:9083',
'hive.version'='2.1.1'
);
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Hive外部表读取Orc表结构的decimal数据类型时，数据不一致 [doris]
Posted by "morningman (via GitHub)" <gi...@apache.org>.
morningman closed issue #27701: [Bug] Hive外部表读取Orc表结构的decimal数据类型时，数据不一致
URL: https://github.com/apache/doris/issues/27701
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Hive外部表读取Orc表结构的decimal数据类型时，数据不一致 [doris]
Posted by "shuxian6688 (via GitHub)" <gi...@apache.org>.
shuxian6688 commented on issue #27701:
URL: https://github.com/apache/doris/issues/27701#issuecomment-1835712734
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Hive外部表读取Orc表结构的decimal数据类型时，数据不一致 [doris]
Posted by "Yulei-Yang (via GitHub)" <gi...@apache.org>.
Yulei-Yang commented on issue #27701:
URL: https://github.com/apache/doris/issues/27701#issuecomment-1829820704
pls show me the creation stmt of catalog 'jdbc_hive_catalog' @shuxian6688
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Hive外部表读取Orc表结构的decimal数据类型时，数据不一致 [doris]
Posted by "shuxian6688 (via GitHub)" <gi...@apache.org>.
shuxian6688 commented on issue #27701:
URL: https://github.com/apache/doris/issues/27701#issuecomment-1829427129
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Hive外部表读取Orc表结构的decimal数据类型时，数据不一致 [doris]
Posted by "Yulei-Yang (via GitHub)" <gi...@apache.org>.
Yulei-Yang commented on issue #27701:
URL: https://github.com/apache/doris/issues/27701#issuecomment-1840143493
latest v2.0 has already fixed it. Check https://github.com/apache/doris/pull/26548, https://github.com/apache/doris/pull/25977, https://github.com/apache/doris/pull/26633, https://github.com/apache/doris/pull/27872
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org