You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@kylin.apache.org by 梅秋莹 <32...@qq.com> on 2019/11/13 06:01:19 UTC
The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
1. Environment:
kylin 2.6.3
2. Question:
In kylin Insight interface, I input query clause like this ：
select DATE_DIM."DATE", SHOP_DIM.SHOP_NAME,A.DISTRICT_ID,SUM(TOTALFEE) from DW_ERP_BY_SHOPSALE_FACT
inner join DATE_DIM
on
DW_ERP_BY_SHOPSALE_FACT.DATE_KEY = DATE_DIM.DATE_KEY
inner join TIME_DIM
on
DW_ERP_BY_SHOPSALE_FACT.TIME_KEY = TIME_DIM.TIME_KEY
inner join SHOP_DIM
on
DW_ERP_BY_SHOPSALE_FACT.SHOP_KEY=SHOP_DIM.SHOP_KEY
inner join ADMINISTRATIVE_DISTRICT A
on
SHOP_DIM.AREAID=A.DISTRICT_ID
inner join ERP_BY_DEPARTMENT
on
SHOP_DIM.REGION=ERP_BY_DEPARTMENT.ID
GROUP BY DATE_DIM."DATE",SHOP_DIM.SHOP_NAME,A.DISTRICT_ID.
The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
3. Please help me solve the quesiton if you have some solutions, thank you!
Re: 回复： The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
Posted by ShaoFeng Shi <sh...@apache.org>.
Hi Qiuyin,
I see this problem also be reported by another user in another thread. And
Xiaoxiang has replied that currently there is a limitation in Kylin for
Hadoop 3 (as well as HDP 3.1): if the lookup table in Hive enabled the ACID
feature, Kylin couldn't load that lookup table into Kylin, which caused the
query problem. You may need to wait a while for the resolution, or you can
try to disable ACID for that table.
Best regards,
Shaofeng Shi 史少锋
Apache Kylin PMC
Email: shaofengshi@apache.org
Apache Kylin FAQ: https://kylin.apache.org/docs/gettingstarted/faq.html
Join Kylin user mail group: user-subscribe@kylin.apache.org
Join Kylin dev mail group: dev-subscribe@kylin.apache.org
梅秋莹 <32...@qq.com> 于2019年11月14日周四 下午3:24写道：
> Thank you for your reply!
>
> Our installation environment is HDP 3.1.0.0. And the dimension
> tables also can be updated. Is it this model and environment induce the
> problem? I will appreciate it very much if you konw the solution.
>
>
> Best wishes,
>
> Qiuying Mei
>
> ------------------ 原始邮件 ------------------
> *发件人:* "Xiaoxiang Yu"<xi...@kyligence.io>;
> *发送时间:* 2019年11月14日(星期四) 下午3:02
> *收件人:* "user@kylin.apache.org"<us...@kylin.apache.org>;"梅秋莹"<
> 3281438086@qq.com>;
> *主题:* Re: 回复： The results return some fields empty. But when I tried in
> hive with the same query, I got what I wanted to get.
>
> Hi friend,
>
> Do you deploy you Kylin on Hadoop3 env like HDP3? And are your
> dimension table an ACID transaction table?
>
>
>
> ----------------
>
> Best wishes,
>
> Xiaoxiang Yu
>
>
>
>
>
> *发件人**: *梅秋莹 <32...@qq.com>
> *答复**: *"user@kylin.apache.org" <us...@kylin.apache.org>
> *日期**: *2019年11月13日 星期三 19:19
> *收件人**: *user <us...@kylin.apache.org>
> *主题**: *回复： The results return some fields empty. But when I tried in
> hive with the same query, I got what I wanted to get.
>
>
>
> Firstly, thank you for your reply!
>
>
>
> In my cube, I have set SHOP_NAME as a derived dimension. And I also found
> that all normal dimensions can return correctly, but not all derived
> dimensions returned results.
>
>
>
>
>
>
>
> ------------------ 原始邮件 ------------------
>
> *发件人:* "Yaqian Zhang"<Ya...@126.com>;
>
> *发送时间:* 2019年11月13日(星期三) 晚上6:39
>
> *收件人:* "user"<us...@kylin.apache.org>;
>
> *主题:* Re: The results return some fields empty. But when I tried in hive
> with the same query, I got what I wanted to get.
>
>
>
> Hi:
>
> Did you add SHOP_NAME to the dimension when you create cube? If not, kylin
> will not save the details of the original table when building the cube, and
> will return null when querying the column.
>
> > 在 2019年11月13日，14:01，梅秋莹 <32...@qq.com> 写道：
> >
> > 1. Environment:
> > kylin 2.6.3
> >
> > 2. Question:
> >
> > In kylin Insight interface, I input query clause like this ：
> >
> > select DATE_DIM."DATE", SHOP_DIM.SHOP_NAME,A.DISTRICT_ID,SUM(TOTALFEE)
> from DW_ERP_BY_SHOPSALE_FACT
> >
> > inner join DATE_DIM
> >
> > on
> >
> > DW_ERP_BY_SHOPSALE_FACT.DATE_KEY = DATE_DIM.DATE_KEY
> >
> > inner join TIME_DIM
> >
> > on
> >
> > DW_ERP_BY_SHOPSALE_FACT.TIME_KEY = TIME_DIM.TIME_KEY
> >
> > inner join SHOP_DIM
> >
> > on
> >
> > DW_ERP_BY_SHOPSALE_FACT.SHOP_KEY=SHOP_DIM.SHOP_KEY
> >
> > inner join ADMINISTRATIVE_DISTRICT A
> >
> > on
> >
> > SHOP_DIM.AREAID=A.DISTRICT_ID
> >
> > inner join ERP_BY_DEPARTMENT
> >
> > on
> >
> > SHOP_DIM.REGION=ERP_BY_DEPARTMENT.ID
> >
> > GROUP BY DATE_DIM."DATE",SHOP_DIM.SHOP_NAME,A.DISTRICT_ID.
> >
> > The results return some fields empty. But when I tried in hive with the
> same query, I got what I wanted to get.
> >
> > 3. Please help me solve the quesiton if you have some solutions, thank
> you!
> >
> > <2C...@37EB5831.2F9CCB5D.jpg>
> >
> >
> > <2E...@AD604224.2F9CCB5D.jpg>
> >
> >
>
回复： 回复： The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
Posted by 梅秋莹 <32...@qq.com>.
Thank you for your reply!
Our installation environment is HDP 3.1.0.0. And the dimension tables also can be updated. Is it this model and environment induce the problem? I will appreciate it very much if you konw the solution.
Best wishes,
Qiuying Mei
------------------ 原始邮件 ------------------
发件人: "Xiaoxiang Yu"<xiaoxiang.yu@kyligence.io>;
发送时间: 2019年11月14日(星期四) 下午3:02
收件人: "user@kylin.apache.org"<user@kylin.apache.org>;"梅秋莹"<3281438086@qq.com>;
主题: Re: 回复： The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
Hi friend,
Do you deploy you Kylin on Hadoop3 env like HDP3? And are your dimension table an ACID transaction table?
----------------
Best wishes,
Xiaoxiang Yu
发件人: 梅秋莹 <3281438086@qq.com>
答复: "user@kylin.apache.org" <user@kylin.apache.org>
日期: 2019年11月13日 星期三 19:19
收件人: user <user@kylin.apache.org>
主题: 回复： The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
Firstly, thank you for your reply!
In my cube, I have set SHOP_NAME as a derived dimension. And I also found that all normal dimensions can return correctly, but not all derived dimensions returned results.
------------------ 原始邮件 ------------------
发件人: "Yaqian Zhang"<Yaqian_Zhang@126.com>;
发送时间: 2019年11月13日(星期三) 晚上6:39
收件人: "user"<user@kylin.apache.org>;
主题: Re: The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
Hi:
Did you add SHOP_NAME to the dimension when you create cube? If not, kylin will not save the details of the original table when building the cube, and will return null when querying the column.
> 在 2019年11月13日，14:01，梅秋莹 <3281438086@qq.com> 写道：
>
> 1. Environment:
> kylin 2.6.3
>
> 2. Question:
>
> In kylin Insight interface, I input query clause like this ：
>
> select DATE_DIM."DATE", SHOP_DIM.SHOP_NAME,A.DISTRICT_ID,SUM(TOTALFEE) from DW_ERP_BY_SHOPSALE_FACT
>
> inner join DATE_DIM
>
> on
>
> DW_ERP_BY_SHOPSALE_FACT.DATE_KEY = DATE_DIM.DATE_KEY
>
> inner join TIME_DIM
>
> on
>
> DW_ERP_BY_SHOPSALE_FACT.TIME_KEY = TIME_DIM.TIME_KEY
>
> inner join SHOP_DIM
>
> on
>
> DW_ERP_BY_SHOPSALE_FACT.SHOP_KEY=SHOP_DIM.SHOP_KEY
>
> inner join ADMINISTRATIVE_DISTRICT A
>
> on
>
> SHOP_DIM.AREAID=A.DISTRICT_ID
>
> inner join ERP_BY_DEPARTMENT
>
> on
>
> SHOP_DIM.REGION=ERP_BY_DEPARTMENT.ID
>
> GROUP BY DATE_DIM."DATE",SHOP_DIM.SHOP_NAME,A.DISTRICT_ID.
>
> The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
>
> 3. Please help me solve the quesiton if you have some solutions, thank you!
>
> <2CFFC910@37EB5831.2F9CCB5D.jpg>
>
>
> <2E01C711@AD604224.2F9CCB5D.jpg>
>
>
Re: 回复： The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
Posted by Xiaoxiang Yu <xi...@kyligence.io>.
Hi friend,
Do you deploy you Kylin on Hadoop3 env like HDP3? And are your dimension table an ACID transaction table?
----------------
Best wishes,
Xiaoxiang Yu
发件人: 梅秋莹 <32...@qq.com>
答复: "user@kylin.apache.org" <us...@kylin.apache.org>
日期: 2019年11月13日 星期三 19:19
收件人: user <us...@kylin.apache.org>
主题: 回复： The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
Firstly, thank you for your reply!
In my cube, I have set SHOP_NAME as a derived dimension. And I also found that all normal dimensions can return correctly, but not all derived dimensions returned results.
[cid:image001.png@01D59AFC.808AE500]
------------------ 原始邮件 ------------------
发件人: "Yaqian Zhang"<Ya...@126.com>;
发送时间: 2019年11月13日(星期三) 晚上6:39
收件人: "user"<us...@kylin.apache.org>;
主题: Re: The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
Hi:
Did you add SHOP_NAME to the dimension when you create cube? If not, kylin will not save the details of the original table when building the cube, and will return null when querying the column.
> 在 2019年11月13日，14:01，梅秋莹 <32...@qq.com> 写道：
>
> 1. Environment:
> kylin 2.6.3
>
> 2. Question:
>
> In kylin Insight interface, I input query clause like this ：
>
> select DATE_DIM."DATE", SHOP_DIM.SHOP_NAME,A.DISTRICT_ID,SUM(TOTALFEE) from DW_ERP_BY_SHOPSALE_FACT
>
> inner join DATE_DIM
>
> on
>
> DW_ERP_BY_SHOPSALE_FACT.DATE_KEY = DATE_DIM.DATE_KEY
>
> inner join TIME_DIM
>
> on
>
> DW_ERP_BY_SHOPSALE_FACT.TIME_KEY = TIME_DIM.TIME_KEY
>
> inner join SHOP_DIM
>
> on
>
> DW_ERP_BY_SHOPSALE_FACT.SHOP_KEY=SHOP_DIM.SHOP_KEY
>
> inner join ADMINISTRATIVE_DISTRICT A
>
> on
>
> SHOP_DIM.AREAID=A.DISTRICT_ID
>
> inner join ERP_BY_DEPARTMENT
>
> on
>
> SHOP_DIM.REGION=ERP_BY_DEPARTMENT.ID
>
> GROUP BY DATE_DIM."DATE",SHOP_DIM.SHOP_NAME,A.DISTRICT_ID.
>
> The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
>
> 3. Please help me solve the quesiton if you have some solutions, thank you!
>
> <2C...@37EB5831.2F9CCB5D.jpg>
>
>
> <2E...@AD604224.2F9CCB5D.jpg>
>
>
回复： The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
Posted by 梅秋莹 <32...@qq.com>.
Firstly, thank you for your reply!
In my cube, I have set SHOP_NAME as a derived dimension. And I also found that all normal dimensions can return correctly, but not all derived dimensions returned results.
------------------ 原始邮件 ------------------
发件人: "Yaqian Zhang"<Yaqian_Zhang@126.com>;
发送时间: 2019年11月13日(星期三) 晚上6:39
收件人: "user"<user@kylin.apache.org>;
主题: Re: The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
Hi:
Did you add SHOP_NAME to the dimension when you create cube? If not, kylin will not save the details of the original table when building the cube, and will return null when querying the column.
> 在 2019年11月13日，14:01，梅秋莹 <3281438086@qq.com> 写道：
>
> 1. Environment:
> kylin 2.6.3
>
> 2. Question:
>
> In kylin Insight interface, I input query clause like this ：
>
> select DATE_DIM."DATE", SHOP_DIM.SHOP_NAME,A.DISTRICT_ID,SUM(TOTALFEE) from DW_ERP_BY_SHOPSALE_FACT
>
> inner join DATE_DIM
>
> on
>
> DW_ERP_BY_SHOPSALE_FACT.DATE_KEY = DATE_DIM.DATE_KEY
>
> inner join TIME_DIM
>
> on
>
> DW_ERP_BY_SHOPSALE_FACT.TIME_KEY = TIME_DIM.TIME_KEY
>
> inner join SHOP_DIM
>
> on
>
> DW_ERP_BY_SHOPSALE_FACT.SHOP_KEY=SHOP_DIM.SHOP_KEY
>
> inner join ADMINISTRATIVE_DISTRICT A
>
> on
>
> SHOP_DIM.AREAID=A.DISTRICT_ID
>
> inner join ERP_BY_DEPARTMENT
>
> on
>
> SHOP_DIM.REGION=ERP_BY_DEPARTMENT.ID
>
> GROUP BY DATE_DIM."DATE",SHOP_DIM.SHOP_NAME,A.DISTRICT_ID.
>
> The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
>
> 3. Please help me solve the quesiton if you have some solutions, thank you!
>
> <2CFFC910@37EB5831.2F9CCB5D.jpg>
>
>
> <2E01C711@AD604224.2F9CCB5D.jpg>
>
>
Re: The results return some fields empty. But when I tried in hive
with the same query, I got what I wanted to get.
Posted by Yaqian Zhang <Ya...@126.com>.
Hi:
Did you add SHOP_NAME to the dimension when you create cube? If not, kylin will not save the details of the original table when building the cube, and will return null when querying the column.
> 在 2019年11月13日，14:01，梅秋莹 <32...@qq.com> 写道：
>
> 1. Environment:
> kylin 2.6.3
>
> 2. Question:
>
> In kylin Insight interface, I input query clause like this ：
>
> select DATE_DIM."DATE", SHOP_DIM.SHOP_NAME,A.DISTRICT_ID,SUM(TOTALFEE) from DW_ERP_BY_SHOPSALE_FACT
>
> inner join DATE_DIM
>
> on
>
> DW_ERP_BY_SHOPSALE_FACT.DATE_KEY = DATE_DIM.DATE_KEY
>
> inner join TIME_DIM
>
> on
>
> DW_ERP_BY_SHOPSALE_FACT.TIME_KEY = TIME_DIM.TIME_KEY
>
> inner join SHOP_DIM
>
> on
>
> DW_ERP_BY_SHOPSALE_FACT.SHOP_KEY=SHOP_DIM.SHOP_KEY
>
> inner join ADMINISTRATIVE_DISTRICT A
>
> on
>
> SHOP_DIM.AREAID=A.DISTRICT_ID
>
> inner join ERP_BY_DEPARTMENT
>
> on
>
> SHOP_DIM.REGION=ERP_BY_DEPARTMENT.ID
>
> GROUP BY DATE_DIM."DATE",SHOP_DIM.SHOP_NAME,A.DISTRICT_ID.
>
> The results return some fields empty. But when I tried in hive with the same query, I got what I wanted to get.
>
> 3. Please help me solve the quesiton if you have some solutions, thank you!
>
> <2C...@37EB5831.2F9CCB5D.jpg>
>
>
> <2E...@AD604224.2F9CCB5D.jpg>
>
>