[Mondrian] Kylin Top N with mondrian

Julian Hyde julianhyde at gmail.com
Wed Sep 28 13:26:14 EDT 2016


LIMIT isn’t in the SQL standard, but the standard has a FETCH clause. Every database I know supports either LIMIT or FETCH. So, for practical purposes, LIMIT is in the standard, but in some dialects it is called FETCH.

E.g. Postgres supports both: https://www.postgresql.org/docs/8.4/static/sql-select.html#SQL-LIMIT <https://www.postgresql.org/docs/8.4/static/sql-select.html#SQL-LIMIT> 

Julian


> On Sep 28, 2016, at 7:31 AM, Luc Boudreau <lucboudreau at gmail.com> wrote:
> 
> To implement Top, we use the JDBC method java.sql.Stetement.setMaxRows(). The LIMIT keyword is not standard ANSI SQL, so we do not use it. 
> 
> If Kylin returns more rows than expected, it must be because setMaxRows is not implemented properly in the Kylin driver.
> 
> On Wed, Sep 28, 2016 at 5:40 AM, Jian Zhong <zhongjian at apache.org <mailto:zhongjian at apache.org>> wrote:
> Hi all,
> 
> I'm trying to use Apache Kylin with Mondrian.
> 
> everything works fine before I meet TOP_N in kylin.I did some research but cannot fix that.
> 
> Hope someone give me a guide.
> 
> 
> Say we have table KYLIN_SALES, and dimension SELLER_ID, measure column PRICE (sum), And I put SELLER_ID as <Attribute> in <Dimension>. the full Query SQL should be like this "SELECT SUM(PRICE),SELLER_ID from KYLIN_SALES group by SELLER_ID "
> by default, Mondrian will generate sql like SQL1(attached below)  get all SELLER_ID info first. it's fine before meet TOP_N in Kylin
> 
> If SELLER_ID is a extra highCardinality column, and user only want to know TOP 10 SUM(PRICE)  Sellers, Kylin will not store all distinct SELLER_ID in cuboid by default.
> So Kylin will not support SQL1 , 
> But full sql SQL2 will be supported.
> 
> Is there any way to avoid MONDRIAN send SQL1 , but send SQL2 directly? 
> 
> 
> SQL1:"SELECT SELLER_ID FROM KYLIN_SALES GROUP BY SELLER_ID"
> SQL2:"SELECT SUM(PRICE),SELLER_ID from KYLIN_SALES group by SELLER_ID order by SUM(PRICE) limit 10"
> 
> About what's TopN in Kylin, see here
> http://kylin.apache.org/blog/2016/03/19/approximate-topn-measure/ <http://kylin.apache.org/blog/2016/03/19/approximate-topn-measure/>
> 
> 
> Thank you!
> Best Regards
> 
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
> http://lists.pentaho.org/mailman/listinfo/mondrian <http://lists.pentaho.org/mailman/listinfo/mondrian>
> 
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20160928/6ee5bf72/attachment.html 


More information about the Mondrian mailing list