Date dimension tables don't work well with Druid. Druid works best if you
store the timestamp in Druid's native timestamp column and then use Druid's
native time functions to do date filtering and grouping.
Instead of:
select "datasource"."username_0" as "c0", "dimdate"."Date" as "c1",
count("datasource"."mobilenumber_2") as "m0" from "datasource" as
"datasource", "dimdate" as "dimdate" where "dimdate"."Date" in ('20161208',
'20161209', '20161210', '20161211') and "datasource"."sessiondate_3_DateDim"
= "dimdate"."Date" group by "datasource"."username_0", "dimdate"."Date"
A better query would be:
select "datasource"."username_0" as "c0", FLOOR("datasource"."__time" TO
DAY) as "c1", count("datasource"."mobilenumber_2") as "m0" from
"datasource" as "datasource" where FLOOR("datasource"."__time" TO DAY) IN
(TIMESTAMP '2016-12-08 00:00:00', TIMESTAMP '2016-12-09 00:00:00',
TIMESTAMP '2016-12-10 00:00:00', TIMESTAMP '2016-12-11 00:00:00') group by
"datasource"."username_0", FLOOR("datasource"."__time" TO DAY)
The FLOOR function should get Calcite to generate queries using Druid's
native time functions.
I bet you could also write some cool Calcite rules to make a phantom
"dimdate" table available, and convert joins on that table into application
of Druid native time functions on the base table. That'd allow the first
SQL query to work in an efficient way.
Gian
On Mon, Jan 23, 2017 at 4:33 AM, Sanjay Raja <[email protected]>
wrote:
> Hi,
>
>
>
> For the MDX Below
>
>
>
> With
>
> Member [Measures].[0] as IIF([Measures].[No Of Sessions]>0,1,0)
>
> Member [Measures].[1] as Aggregate({[Session
> Date].[Day].[2016-12-10].Lag(4):[Session
> Date].[Day].[2016-12-10]},[Measures].[0])
>
> Member [Measures].[Repeat Users] as COUNT(FILTER([User].[User].Chi
> ldren,[Measures].[1]>1))
>
> Select { [Measures].[Repeat Users] } on Columns From [Session Date Cube]
> Where { { [Session Date].[Day].[2016-12-10] } }
>
>
>
>
>
> Setup Scenario 1
>
> I have created a Table in Druid called DimDate which basically holds the
> Date Dimension.
>
> The resultant Query and Expected result is fine – however
>
> Mondrian generates the following query which Caclite Executes
>
>
>
> select "dimdate"."QuarterName" as "c0", "dimdate"."Date" as "c1" from
> "dimdate" as "dimdate" group by "dimdate"."QuarterName", "dimdate"."Date"
> order by CASE WHEN "dimdate"."QuarterName" IS NULL THEN 0 ELSE 1 END,
> "dimdate"."QuarterName" ASC;
>
>
>
> This basically times out against DRUID.
>
>
>
> {"queryType":"groupBy","dataSource":"DimDate","granularity":
> "all","dimensions":["QuarterName","Date"],"limitSpec":{"type":"default"},"
> aggregations":[{"type":"longSum","name":"dummy_agg","fieldNa
> me":"dummy_agg"}],"intervals":["2000-01-01T00:00:00.000Z/
> 2021-01-01T00:00:00.000Z"]}
>
>
>
> 1) Why does Sqlline give back an empty result for this.
>
>
>
>
>
> Scenario 2
>
> I tried creating an in-memory Table for the Date Dimension using a
> CustomTableFactory. Here the DimDate values are returned immediately,
> however the inner join Query Fails.
>
> The generated query is
>
>
>
> select "datasource"."username_0" as "c0", "dimdate"."Date" as "c1",
> count("datasource"."mobilenumber_2") as "m0" from "datasource" as
> "datasource", "dimdate" as "dimdate" where "dimdate"."Date" in ('20161208',
> '20161209', '20161210', '20161211') and "datasource"."sessiondate_3_DateDim"
> = "dimdate"."Date" group by "datasource"."username_0", "dimdate"."Date"
>
>
>
> The Error Is
>
>
>
> [mondrian.rolap.agg.SegmentCacheManager$sqlExecutor_1] DEBUG
> mondrian.sql - 6: , failed (java.sql.SQLException: Error while executing
> SQL "select "datasource"."username_0" as "c0", "dimdate"."Date" as "c1",
> count("datasource"."mobilenumber_2") as "m0" from "datasource" as
> "datasource", "dimdate" as "dimdate" where "dimdate"."Date" in ('20161208',
> '20161209', '20161210', '20161211') and "datasource"."sessiondate_3_DateDim"
> = "dimdate"."Date" group by "datasource"."username_0", "dimdate"."Date"":
> org.apache.calcite.interpreter.Bindables$BindableJoin cannot be cast to
> org.apache.calcite.adapter.enumerable.EnumerableRel)
>
> 56092
>
>
>
>
>
> Can someone please help with this
>
>
>
> Regards,
>
> Sanjay
>
>
>
>
>
>
>
>
>
>
