You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by yu feng <ol...@gmail.com> on 2015/09/01 11:07:21 UTC
how to generate time hierarchy base on a timestamp column
Hi all ：
I have use kylin for about one month，but can not find how to generate
time hierarchy based on my timestamp column， I have an HIVE table like this:
CREATE TABLE `sales_table`(
`time` timestamp,
`sales` int)
and I have a timestamp column named time, I want to get result like this :
select date(time) ,hour(time),sum(sales) from sales_table group by
date(time), hour(time);
2015-08-27 16 18388
2015-08-27 17 35301
2015-08-27 18 41884
2015-08-27 19 40638
2015-08-27 20 40294
2015-08-27 21 43753
i.e. you can generate full time hierachy like year/month/day/hour.. when
you have a timestamp column.
I have no idea how to achieve it with kylin and I will appreciate it very
much if someone can help me，Thanks~~
Re: how to generate time hierarchy base on a timestamp column
Posted by yu feng <ol...@gmail.com>.
Yes! It works.. thanks a lot...
2015-09-02 9:03 GMT+08:00 Shi, Shaofeng <sh...@ebay.com>:
> Create a hive view over your fact table, in the view adding column for
> date, hour, etc; Then use the view as fact table in Kylin cube; This is a
> common practice in Kylin, it will give user the flexibility and with minor
> effort;
>
> On 9/1/15, 5:07 PM, "yu feng" <ol...@gmail.com> wrote:
>
> >Hi all ：
> > I have use kylin for about one month，but can not find how to generate
> >time hierarchy based on my timestamp column， I have an HIVE table like
> >this:
> >
> >CREATE TABLE `sales_table`(
> > `time` timestamp,
> > `sales` int)
> >
> >and I have a timestamp column named time, I want to get result like this :
> >
> >select date(time) ,hour(time),sum(sales) from sales_table group by
> >date(time), hour(time);
> >
> >2015-08-27 16 18388
> >2015-08-27 17 35301
> >2015-08-27 18 41884
> >2015-08-27 19 40638
> >2015-08-27 20 40294
> >2015-08-27 21 43753
> >
> >i.e. you can generate full time hierachy like year/month/day/hour.. when
> >you have a timestamp column.
> >
> >I have no idea how to achieve it with kylin and I will appreciate it very
> >much if someone can help me，Thanks~~
>
>
Re: how to generate time hierarchy base on a timestamp column
Posted by "Shi, Shaofeng" <sh...@ebay.com>.
Create a hive view over your fact table, in the view adding column for
date, hour, etc; Then use the view as fact table in Kylin cube; This is a
common practice in Kylin, it will give user the flexibility and with minor
effort;
On 9/1/15, 5:07 PM, "yu feng" <ol...@gmail.com> wrote:
>Hi all ：
> I have use kylin for about one month，but can not find how to generate
>time hierarchy based on my timestamp column， I have an HIVE table like
>this:
>
>CREATE TABLE `sales_table`(
> `time` timestamp,
> `sales` int)
>
>and I have a timestamp column named time, I want to get result like this :
>
>select date(time) ,hour(time),sum(sales) from sales_table group by
>date(time), hour(time);
>
>2015-08-27 16 18388
>2015-08-27 17 35301
>2015-08-27 18 41884
>2015-08-27 19 40638
>2015-08-27 20 40294
>2015-08-27 21 43753
>
>i.e. you can generate full time hierachy like year/month/day/hour.. when
>you have a timestamp column.
>
>I have no idea how to achieve it with kylin and I will appreciate it very
>much if someone can help me，Thanks~~
Re: how to generate time hierarchy base on a timestamp column
Posted by Luke Han <lu...@gmail.com>.
Leverage front-end BI tool, like Tableau, is one option to generate such
hierarchy dimension at run time.
Best Regards!
---------------------
Luke Han
On Tue, Sep 1, 2015 at 5:07 PM, yu feng <ol...@gmail.com> wrote:
> Hi all ：
> I have use kylin for about one month，but can not find how to generate
> time hierarchy based on my timestamp column， I have an HIVE table like
> this:
>
> CREATE TABLE `sales_table`(
> `time` timestamp,
> `sales` int)
>
> and I have a timestamp column named time, I want to get result like this :
>
> select date(time) ,hour(time),sum(sales) from sales_table group by
> date(time), hour(time);
>
> 2015-08-27 16 18388
> 2015-08-27 17 35301
> 2015-08-27 18 41884
> 2015-08-27 19 40638
> 2015-08-27 20 40294
> 2015-08-27 21 43753
>
> i.e. you can generate full time hierachy like year/month/day/hour.. when
> you have a timestamp column.
>
> I have no idea how to achieve it with kylin and I will appreciate it very
> much if someone can help me，Thanks~~
>
Re: how to generate time hierarchy base on a timestamp column
Posted by hongbin ma <ma...@apache.org>.
xiaoyu's advice is a good choice. can you use hive view to solve your
problem?
On Tue, Sep 1, 2015 at 5:38 PM, yu feng <ol...@gmail.com> wrote:
> Thanks very much, you means I have to create a temporary table in hive and
> use this table to create cube in kylin, what's more, I can not drop this
> table because when I want to build incremental cube I need select those
> records from source table and insert into the temporary table ..OR I can
> create new table whose PK is timestamp and I take this table as a dimension
> table. I think this would work fine.
>
> However, I actually hope kylin can support something like keyExpression(you
> can define dimension level with any column expression ) in mondrian when
> create a dimension level rather than only can specify a column.I think time
> is a special dimension and it will more easy to use with this feature..
>
>
> 2015-09-01 17:17 GMT+08:00 Xiaoyu Wang <wa...@jd.com>:
>
> > Hi Feng!
> > You can transform the timestamp column to day,hour two columns in hive
> > before you create cube.
> > Then in cube design. You can add hierarchy dimensions like day,hour.
> > In the sql: select day,hour,sum(sales) from table group by day,hour
> >
> >
> > 在 2015年09月01日 17:07, yu feng 写道:
> >
> >> Hi all ：
> >> I have use kylin for about one month，but can not find how to
> generate
> >> time hierarchy based on my timestamp column， I have an HIVE table like
> >> this:
> >>
> >> CREATE TABLE `sales_table`(
> >> `time` timestamp,
> >> `sales` int)
> >>
> >> and I have a timestamp column named time, I want to get result like
> this :
> >>
> >> select date(time) ,hour(time),sum(sales) from sales_table group by
> >> date(time), hour(time);
> >>
> >> 2015-08-27 16 18388
> >> 2015-08-27 17 35301
> >> 2015-08-27 18 41884
> >> 2015-08-27 19 40638
> >> 2015-08-27 20 40294
> >> 2015-08-27 21 43753
> >>
> >> i.e. you can generate full time hierachy like year/month/day/hour.. when
> >> you have a timestamp column.
> >>
> >> I have no idea how to achieve it with kylin and I will appreciate it
> very
> >> much if someone can help me，Thanks~~
> >>
> >>
>
--
Regards,
*Bin Mahone | 马洪宾*
Apache Kylin: http://kylin.io
Github: https://github.com/binmahone
Re: how to generate time hierarchy base on a timestamp column
Posted by yu feng <ol...@gmail.com>.
Thanks very much, you means I have to create a temporary table in hive and
use this table to create cube in kylin, what's more, I can not drop this
table because when I want to build incremental cube I need select those
records from source table and insert into the temporary table ..OR I can
create new table whose PK is timestamp and I take this table as a dimension
table. I think this would work fine.
However, I actually hope kylin can support something like keyExpression(you
can define dimension level with any column expression ) in mondrian when
create a dimension level rather than only can specify a column.I think time
is a special dimension and it will more easy to use with this feature..
2015-09-01 17:17 GMT+08:00 Xiaoyu Wang <wa...@jd.com>:
> Hi Feng!
> You can transform the timestamp column to day,hour two columns in hive
> before you create cube.
> Then in cube design. You can add hierarchy dimensions like day,hour.
> In the sql: select day,hour,sum(sales) from table group by day,hour
>
>
> 在 2015年09月01日 17:07, yu feng 写道:
>
>> Hi all ：
>> I have use kylin for about one month，but can not find how to generate
>> time hierarchy based on my timestamp column， I have an HIVE table like
>> this:
>>
>> CREATE TABLE `sales_table`(
>> `time` timestamp,
>> `sales` int)
>>
>> and I have a timestamp column named time, I want to get result like this :
>>
>> select date(time) ,hour(time),sum(sales) from sales_table group by
>> date(time), hour(time);
>>
>> 2015-08-27 16 18388
>> 2015-08-27 17 35301
>> 2015-08-27 18 41884
>> 2015-08-27 19 40638
>> 2015-08-27 20 40294
>> 2015-08-27 21 43753
>>
>> i.e. you can generate full time hierachy like year/month/day/hour.. when
>> you have a timestamp column.
>>
>> I have no idea how to achieve it with kylin and I will appreciate it very
>> much if someone can help me，Thanks~~
>>
>>
Re: how to generate time hierarchy base on a timestamp column
Posted by Xiaoyu Wang <wa...@jd.com>.
Hi Feng!
You can transform the timestamp column to day,hour two columns in hive
before you create cube.
Then in cube design. You can add hierarchy dimensions like day,hour.
In the sql: select day,hour,sum(sales) from table group by day,hour
在 2015年09月01日 17:07, yu feng 写道:
> Hi all ：
> I have use kylin for about one month，but can not find how to generate
> time hierarchy based on my timestamp column， I have an HIVE table like this:
>
> CREATE TABLE `sales_table`(
> `time` timestamp,
> `sales` int)
>
> and I have a timestamp column named time, I want to get result like this :
>
> select date(time) ,hour(time),sum(sales) from sales_table group by
> date(time), hour(time);
>
> 2015-08-27 16 18388
> 2015-08-27 17 35301
> 2015-08-27 18 41884
> 2015-08-27 19 40638
> 2015-08-27 20 40294
> 2015-08-27 21 43753
>
> i.e. you can generate full time hierachy like year/month/day/hour.. when
> you have a timestamp column.
>
> I have no idea how to achieve it with kylin and I will appreciate it very
> much if someone can help me，Thanks~~
>