You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by sdangi <sd...@datalenz.com> on 2016/01/16 16:27:00 UTC
TopN Results Differ in Hive and Kylin
I'm hitting an issue and am not able to understand clearly what the root
cause is.
I have customer,time dimension and a transaction fact table. Customer table
has 1MM rows and transaction fact 154MM.
I have to find topN customers by transaction volume for a year. I ran the
following query in Hive for say year 2015.
TXN_AMT in transaction fact table is of type decimal(32,8)
SELECT
CUSTOMER_DIM_T.NM, SUM(TXN_AMT) TOTAL_TXN_AMT
FROM TXN_FCT_T as TXN_FCT_T
INNER JOIN CUSTOMER_DIM_T as CUSTOMER_DIM_T
ON TXN_FCT_T.CST_KEY = CUSTOMER_DIM_T.KEY
INNER JOIN DATE_DIM_T as DATE_DIM_T
ON TXN_FCT_T.TXN_BK_DT_KEY = DATE_DIM_T.DT_KEY
WHERE DATE_DIM_T.CAL_YR=2015
GROUP BY
CUSTOMER_DIM_T.NM
ORDER BY TOTAL_TXN_AMT DESC LIMIT 10;
CUST AMT
====================
A 177070809652.52
B 156918629669.59
C 145634838958.87
D 137781561987.28
E 137470272887.12
F 136782827759.93
G 129986897552.65
H 127105433950.33
I 115152934891.42
J 107505491520.64
The same query if run in Kylin, I get a totally different answer what is
bothering me is as I begin to increase the LIMIT number, the response keeps
on changing. I keep on getting a different set of customers with higher txn
amts.
CUST AMT
====================
K 4014698676
L 2727082526
M 1344354210
N 1216966910
O 554963079.2
P 390827714.4
Q 367123639.6
R 347732036.1
S 313686532
T 311987500.6
Aren't the results first sorted then limited in Kylin?
If I run the query in Kylin filtering on customer A above, the cuboid seems
to have the right aggregated data
SELECT
CUSTOMER_DIM_T.NM, SUM(TXN_AMT) TOTAL_TXN_AMT
FROM TXN_FCT_T as TXN_FCT_T
INNER JOIN CUSTOMER_DIM_T as CUSTOMER_DIM_T
ON TXN_FCT_T.CST_KEY = CUSTOMER_DIM_T.KEY
INNER JOIN DATE_DIM_T as DATE_DIM_T
ON TXN_FCT_T.TXN_BK_DT_KEY = DATE_DIM_T.DT_KEY
WHERE DATE_DIM_T.CAL_YR=2015 and */CUSTOMER_DIM_T.NM='A'/*
GROUP BY
CUSTOMER_DIM_T.NM
ORDER BY TOTAL_TXN_AMT DESC LIMIT 10;
CUST AMT
====================
A 177070809652.52
Any ideas?
Thanks,
--
View this message in context: http://apache-kylin.74782.x6.nabble.com/TopN-Results-Differ-in-Hive-and-Kylin-tp3288.html
Sent from the Apache Kylin mailing list archive at Nabble.com.
Re: TopN Results Differ in Hive and Kylin
Posted by hongbin ma <ma...@apache.org>.
on the dict repeatly loading issue, it is fixed in
http://mail-archives.apache.org/mod_mbox/kylin-commits/201601.mbox/%3Cbb46367444f249adbbf9673727ce3723@git.apache.org%3E
on 2.0 and 2.x-staging.
i'll backport it to 1.x today
On Mon, Jan 18, 2016 at 10:15 AM, ShaoFeng Shi <sh...@apache.org>
wrote:
> Yes the "acceptPartial" may cause confusion and we decided to disable it,
> will roll out in v1.3.
>
> Kylin will introduce the "approximate" TopN measure in the future release
> (maybe v2.1), with which the Top records will be pre-calculated during cube
> build; then Kylin can quickly answer instead of getting all records to
> sort. You can get more information in the JIRA
> https://issues.apache.org/jira/browse/KYLIN-943
>
> 2016-01-17 22:27 GMT+08:00 sdangi <sd...@datalenz.com>:
>
> > I was looking at the commits. 10 days back, 2 files (QueryReqeust and
> > SQLRequest.java) were touched that have fixed the issue for clients using
> > REST API, changing the default acceptPartial to false.
> >
> > I tried with Zeppelin and it works and results from Hive and Zeppelin
> (via
> > REST API) work. However, the response time has now gone really up and
> > queries are taking far longer. Where I should be looking to improve the
> > performance. It seems that as part of HBase scans, the dictionary info
> is
> > repeatedly loaded - can we not read it from the cache?
> >
> >
> > [http-bio-80-exec-6]:[2016-01-17
> >
> >
> 09:27:33,501][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:378)]
> > - Going to load DictionaryInfo from
> >
> >
> /dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
> > 2016-01-17 09:27:33,726 WARN [http-bio-80-exec-6] hdfs.DFSClient:
> > DFSInputStream has been closed already
> > [http-bio-80-exec-6]:[2016-01-17
> >
> >
> 09:27:33,726][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:382)]
> > - Loaded dictionary at
> >
> >
> /dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
> > [http-bio-80-exec-6]:[2016-01-17
> >
> >
> 09:27:35,443][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:378)]
> > - Going to load DictionaryInfo from
> >
> >
> /dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
> > 2016-01-17 09:27:35,669 WARN [http-bio-80-exec-6] hdfs.DFSClient:
> > DFSInputStream has been closed already
> > [http-bio-80-exec-6]:[2016-01-17
> >
> >
> 09:27:35,669][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:382)]
> > - Loaded dictionary at
> >
> >
> /dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
> > [http-bio-80-exec-6]:[2016-01-17
> >
> >
> 09:27:37,469][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:378)]
> > - Going to load DictionaryInfo from
> >
> >
> /dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
> > 2016-01-17 09:27:37,697 WARN [http-bio-80-exec-6] hdfs.DFSClient:
> > DFSInputStream has been closed already
> > [http-bio-80-exec-6]:[2016-01-17
> >
> >
> 09:27:37,697][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:382)]
> > - Loaded dictionary at
> >
> >
> /dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
> >
> > Thanks,
> >
> >
> >
> > --
> > View this message in context:
> >
> http://apache-kylin.74782.x6.nabble.com/TopN-Results-Differ-in-Hive-and-Kylin-tp3288p3296.html
> > Sent from the Apache Kylin mailing list archive at Nabble.com.
> >
>
>
>
> --
> Best regards,
>
> Shaofeng Shi
>
--
Regards,
*Bin Mahone | 马洪宾*
Apache Kylin: http://kylin.io
Github: https://github.com/binmahone
Re: TopN Results Differ in Hive and Kylin
Posted by ShaoFeng Shi <sh...@apache.org>.
Yes the "acceptPartial" may cause confusion and we decided to disable it,
will roll out in v1.3.
Kylin will introduce the "approximate" TopN measure in the future release
(maybe v2.1), with which the Top records will be pre-calculated during cube
build; then Kylin can quickly answer instead of getting all records to
sort. You can get more information in the JIRA
https://issues.apache.org/jira/browse/KYLIN-943
2016-01-17 22:27 GMT+08:00 sdangi <sd...@datalenz.com>:
> I was looking at the commits. 10 days back, 2 files (QueryReqeust and
> SQLRequest.java) were touched that have fixed the issue for clients using
> REST API, changing the default acceptPartial to false.
>
> I tried with Zeppelin and it works and results from Hive and Zeppelin (via
> REST API) work. However, the response time has now gone really up and
> queries are taking far longer. Where I should be looking to improve the
> performance. It seems that as part of HBase scans, the dictionary info is
> repeatedly loaded - can we not read it from the cache?
>
>
> [http-bio-80-exec-6]:[2016-01-17
>
> 09:27:33,501][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:378)]
> - Going to load DictionaryInfo from
>
> /dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
> 2016-01-17 09:27:33,726 WARN [http-bio-80-exec-6] hdfs.DFSClient:
> DFSInputStream has been closed already
> [http-bio-80-exec-6]:[2016-01-17
>
> 09:27:33,726][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:382)]
> - Loaded dictionary at
>
> /dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
> [http-bio-80-exec-6]:[2016-01-17
>
> 09:27:35,443][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:378)]
> - Going to load DictionaryInfo from
>
> /dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
> 2016-01-17 09:27:35,669 WARN [http-bio-80-exec-6] hdfs.DFSClient:
> DFSInputStream has been closed already
> [http-bio-80-exec-6]:[2016-01-17
>
> 09:27:35,669][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:382)]
> - Loaded dictionary at
>
> /dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
> [http-bio-80-exec-6]:[2016-01-17
>
> 09:27:37,469][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:378)]
> - Going to load DictionaryInfo from
>
> /dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
> 2016-01-17 09:27:37,697 WARN [http-bio-80-exec-6] hdfs.DFSClient:
> DFSInputStream has been closed already
> [http-bio-80-exec-6]:[2016-01-17
>
> 09:27:37,697][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:382)]
> - Loaded dictionary at
>
> /dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
>
> Thanks,
>
>
>
> --
> View this message in context:
> http://apache-kylin.74782.x6.nabble.com/TopN-Results-Differ-in-Hive-and-Kylin-tp3288p3296.html
> Sent from the Apache Kylin mailing list archive at Nabble.com.
>
--
Best regards,
Shaofeng Shi
Re: TopN Results Differ in Hive and Kylin
Posted by sdangi <sd...@datalenz.com>.
I was looking at the commits. 10 days back, 2 files (QueryReqeust and
SQLRequest.java) were touched that have fixed the issue for clients using
REST API, changing the default acceptPartial to false.
I tried with Zeppelin and it works and results from Hive and Zeppelin (via
REST API) work. However, the response time has now gone really up and
queries are taking far longer. Where I should be looking to improve the
performance. It seems that as part of HBase scans, the dictionary info is
repeatedly loaded - can we not read it from the cache?
[http-bio-80-exec-6]:[2016-01-17
09:27:33,501][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:378)]
- Going to load DictionaryInfo from
/dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
2016-01-17 09:27:33,726 WARN [http-bio-80-exec-6] hdfs.DFSClient:
DFSInputStream has been closed already
[http-bio-80-exec-6]:[2016-01-17
09:27:33,726][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:382)]
- Loaded dictionary at
/dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
[http-bio-80-exec-6]:[2016-01-17
09:27:35,443][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:378)]
- Going to load DictionaryInfo from
/dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
2016-01-17 09:27:35,669 WARN [http-bio-80-exec-6] hdfs.DFSClient:
DFSInputStream has been closed already
[http-bio-80-exec-6]:[2016-01-17
09:27:35,669][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:382)]
- Loaded dictionary at
/dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
[http-bio-80-exec-6]:[2016-01-17
09:27:37,469][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:378)]
- Going to load DictionaryInfo from
/dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
2016-01-17 09:27:37,697 WARN [http-bio-80-exec-6] hdfs.DFSClient:
DFSInputStream has been closed already
[http-bio-80-exec-6]:[2016-01-17
09:27:37,697][DEBUG][org.apache.kylin.dict.DictionaryManager.load(DictionaryManager.java:382)]
- Loaded dictionary at
/dict/KYLIN_DEMO.CUSTOMER_DIM_T/L1_CST_NM/3c9c05aa-c8e9-4bfa-861b-2f3794cb3209.dict
Thanks,
--
View this message in context: http://apache-kylin.74782.x6.nabble.com/TopN-Results-Differ-in-Hive-and-Kylin-tp3288p3296.html
Sent from the Apache Kylin mailing list archive at Nabble.com.
Re: TopN Results Differ in Hive and Kylin
Posted by hongbin ma <ma...@apache.org>.
will "Show All" button provide you with the correct answer?
--
Regards,
*Bin Mahone | 马洪宾*
Apache Kylin: http://kylin.io
Github: https://github.com/binmahone
Re: TopN Results Differ in Hive and Kylin
Posted by sdangi <sd...@datalenz.com>.
>From FAQs
Getting wrong result for the query with order by
By default if you're making queries on the web client, a mode called
"AcceptPartialResults" is enabled, this is a protection mechanism that will
only return part of the results to reduce server overhead. Honestly it might
hurt the correctness of order by queries.
If you're seeking 100% correctness, after running the query you will find a
notification: Note: Current results are partial, please click 'Show All'
button to get all results. Click the "Show All" button to disable the
"AcceptPartialResults" mode, and you'll get a right result.
Notice "AcceptPartialResults" is only enabled by default at web client,
you'll not meet such problems if you're using JDBC, ODBC or standard REST
API.
--
View this message in context: http://apache-kylin.74782.x6.nabble.com/TopN-Results-Differ-in-Hive-and-Kylin-tp3288p3292.html
Sent from the Apache Kylin mailing list archive at Nabble.com.
Re: TopN Results Differ in Hive and Kylin
Posted by sdangi <sd...@datalenz.com>.
Kylin Web UI
--
View this message in context: http://apache-kylin.74782.x6.nabble.com/TopN-Results-Differ-in-Hive-and-Kylin-tp3288p3291.html
Sent from the Apache Kylin mailing list archive at Nabble.com.
Re: TopN Results Differ in Hive and Kylin
Posted by ShaoFeng Shi <sh...@apache.org>.
in which way are you connecting to Kylin, ODBC, JDBC, Rest API or the web
UI?